使用SQL& Perl一起 - 哪些应该用于常用功能？ [英] Using SQL & Perl together - Which should be used for common functions?
问题描述
我没有发现任何这个问题，但如果有一个或多个，我很抱歉 - 请评论与链接。
问题是基本的，可能是答案。如果我使用Perl来执行&对数据库采取行动，哪些大道（Perl与SQL）应该在涉及共享函数时加重负担？
像 - 这个SQL语句加载了可以用Perl复制的大小写块和其他操作。那么，如果可以在Perl中实现相同的逻辑，是否值得重写呢？什么样的条件影响决定去承担一个系统的负担并将它放在另一个系统上？ 我会从性能角度来看这个，一个重复使用的视角。 如果您尝试双方，您可能会发现一个比另一个快得多 - 这将是一个很好的指标，如果您将在多个地方重复使用某些查询，那么您会希望将查询中的大部分业务逻辑尽可能地包含在查询中，所以您不需要在GUI中复制它。 （我必须说，尽管不是您的问题的严格组成部分，但大部分情况逻辑看起来像是可以的在架构中做一个好的模型，并用普通的连接替换一些关联表）。 I didn't find any dupes of this question, but if there is one or more, I'm sorry - please comment with link. The question is basic, as probably is the answer. If I'm using Perl to execute & act upon a database, which avenue (Perl vs. SQL) should I place the burden on when shared functions are involved? Functions like - This SQL statement is loaded with case blocks and other operations that can be replicated with Perl. So, if the same logic could be implemented in Perl, is it worth re-writing? What conditions influence the decision to take the burden of one system and place it on the other?
i would look at this from a performance perspective, and a re-use perspective. If you try it on both sides, you may find one to be much faster than the other - that would be a good indicator for which to prefer. If you will re-use some query in more than one place, then you would want to incorporate much of the business logic in the query as possible, so you do not need to replicate that in the GUI. (and i have to say, although not strictly part of your question, that much of that case logic looks like you could do a good model in the schema and replace the case with a normal join to some associative tables) 这篇关于使用SQL& Perl一起 - 哪些应该用于常用功能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！ LEN例如， IF / ELSE ， CONCAT 以及更多以及算术函数这两个系统都是共同的。
SELECT DISTINCT s.id stu_id，
stu_id.fullname stu_name，
p.major1 major，
p.minor1 minor，
s.reg_hrs，
NVL（st.cum_earn_hrs，0）ttl_hrs，
p.adv_id curr_adv_id，
adv_id.fullname curr_adv_name，
CASE WHEN（p.adv_id<> 35808 AND p.major1 ='NS'）THEN（1165）
WHEN （p.adv_id = 35808 AND p.major1 ='NS'）THEN（35808）
WHEN（p.adv_id = 9179 AND p.major1 ='DART'）THEN（9179）
WHEN（p .minor1 IN（'RT'，'RESP'）AND st.cum_earn_hrs> = 24）THEN（70897）
当（p.major1 IN（'CA'，'CB'））THEN（24702）
当（（p.major1 IN（'CDSC'，'CDSD'））THEN（52125）
（p.major1 ='CC'）THEN（73837）
WHEN（p.major1 ='CCRE'）THEN（1133）
WHEN（（p.adv_id IN（SELECT DISTINCT id FROM fac_rec WHERE stat ='I'））
OR（st.cum_earn_hrs < 24 AND（p.adv_id || p.major1）IN（SELECT DISTINCT（id || major）FROM adv_detail WHERE stat ='A'AND max_stu> 0 AND min_hrs> = 24））
OR（s .id NOT IN（SELECT DISTINCT stu.id FROM stu_acad_rec stu，sess_info si WHERE stu.yr = si.prev_yr AND stu.sess = si.prev_sess AND stu.reg_hrs> 0 AND stu.reg_stat IN（'C'，' R'）AND stu.prog ='UNDG'））
OR（（p.adv_id || p.major1）IN（SELECT DISTINCT（id || major）FROM adv_detail WHERE stat<>'A' OR max_stu <= 0））
OR（（p.adv_id || p.major1）NOT IN（SELECT DISTINCT（id || major）FROM adv_detail WHERE stat ='A'AND max_stu> 0）） ）
THEN（9238）
ELSE（p.adv_id）
END new_adv_id，
CASE WHEN（p.adv_id<> 35808 AND p.major1 ='NS'） THEN（'Deborah'）$ （''Veronica'）
当（p.adv_id = 9179 AND p.major1 ='DART'）THEN（'Stella' ）
当（p.minor1 IN（'RT'，'RESP'）and st.cum_earn_hrs> = 24）THEN（'Lisa'）
当（p.major1 IN（'CDSC'， ''CDS''））THEN（'Joanne'）
当（p.major1 IN（'CA'，'CB'））THEN（'Barbara'）
当（p.minor1 ='NURS' ）然后（'凯伦'）
当（p.major1 ='LEG'）THEN（'南希'）
当（p.major1 ='CC'）THEN（'Albert a'）
WHEN（p.major1 ='CCRE'）THEN（'Naomi'）
WHEN（（p.adv_id IN（SELECT DISTINCT id FROM fac_rec WHERE stat ='I'））
OR（st.cum_earn_hrs< 24 AND（p.adv_id || p.major1）IN（SELECT DISTINCT（id || major）FROM adv_detail WHERE stat ='A'AND max_stu> 0 AND min_hrs> = 24））
OR（s .id NOT IN（SELECT DISTINCT stu.id FROM stu_acad_rec stu，sess_info si WHERE stu.yr = si.prev_yr AND stu.sess = si.prev_sess AND stu.reg_hrs> 0 AND stu.reg_stat IN（'C'，' R'）AND stu.prog ='UNDG'））
OR（（p.adv_id || p.major1）IN（SELECT DISTINCT（id || major）FROM adv_detail WHERE stat<>'A' OR max_stu <= 0））
OR（（p.adv_id || p.major1）NOT IN（SELECT DISTINCT（id || major）FROM adv_detail WHERE stat ='A'AND max_stu> 0）） ）
THEN（'Staff'）
ELSE（adv_id.fullname）
END new_adv_name，
CASE WHEN（p.adv_id<> 35808 AND p.major1 ='NS '） 然后 （ 'NS专业未分配到维罗妮卡去黛比'）
当（p.adv_id = 35808和p.major1 ='NS'）THEN（'NS专业留在Veronica'）
当（p。 （'DART'）THEN（'DART majors stay with Stella'）
WHEN（p.minor1 IN（'RT'，'RESP'）and st.cum_earn_hrs> = 24） THEN（'RT-RESP未成年人去Lisa'）
当（p.major1 IN（'CDSC'，'CDSD'））THEN（'CDSC-CDSD专业人士去Joanne'）
当（'CA'，'CB'））THEN（'CA-CB专业人员去芭芭拉'）
当（p.minor1 ='NURS'）然后（'NURS未成年人去Karen'）
当（p.major1 ='LEG'） THEN（'LEG majors go to Nancy'）
WHEN（p.major1 ='CC'）THEN（'CC专业人士去艾伯塔'）
当（p.major1 ='CCRE'）THEN 'CCRE专业人员转到Naomi'）
当（p.adv_id IN（SELECT DISTINCT id FROM fac_rec WHERE stat ='I'））
THEN（'当前顾问不活跃'）
WHEN （st.cum_earn_hrs< 24 AND（p.adv_id || p.major1）IN（SELECT DISTINCT（id || major）FROM adv_detail WHERE stat ='A'AND max_stu> 0 AND min_hrs> = 24））
THEN（'这个学生的总学分不符合这个专业的顾问要求'）
WHEN（s.id NOT IN（SELECT DISTINCT stu.id FROM stu_acad_rec stu，sess_info si WHERE stu.yr = si.prev_yr AND stu。 sess = si.prev_sess AND stu.reg_hrs> 0 AND stu.reg_stat IN（'C'，'R'）AND stu.prog ='UNDG'））
THEN（'This student did not attend'| |（si.prev_sess || si.prev_yr）
WHEN（（p.adv_id || p.major1）IN（SELECT DISTINCT（id || major）FROM adv_detail WHERE（stat<>'A'or max_stu < = 0）））
THEN（'当前顾问不建议这个专业的学生'）
WHEN（（p.adv_id || p.major1）NOT IN（SELECT DISTINCT（id ||）主要）从adv _detail WHERE stat ='A'和max_stu> 0））
THEN（'当前顾问不建议这个专业的学生'）
ELSE（'Student will stay with current advisor'）
END change_comm
FROM stu_acad_rec s，
prog_enr_rec p，
OUTER stu_stat_rec st，
id_rec stu_id，
id_rec adv_id，
sess_info si
WHERE s.id = p.id
AND s.id = st.id
AND s.id = stu_id.id
AND p.adv_id = adv_id.id
AND s.yr = si.curr_yr
AND s.sess = si.curr_sess
AND s.reg_hrs> 0
AND s.reg_stat IN（'C'，'R'）
AND s.prog ='UNDG'
AND p.prog ='UNDG'
AND st。 prog ='UNDG'
AND s.id NOT IN（3,287,9238,59999）{系统测试使用ID}
INTO TEMP stu_list
WITH NO LOG;
LEN, IF/ELSE, CONCAT and many more as well as arithmetic functions, for example are common to both systems. SELECT DISTINCT s.id stu_id,
stu_id.fullname stu_name,
p.major1 major,
p.minor1 minor,
s.reg_hrs,
NVL(st.cum_earn_hrs,0) ttl_hrs,
p.adv_id curr_adv_id,
adv_id.fullname curr_adv_name,
CASE WHEN (p.adv_id <> 35808 AND p.major1 = 'NS') THEN (1165)
WHEN (p.adv_id = 35808 AND p.major1 = 'NS') THEN (35808)
WHEN (p.adv_id = 9179 AND p.major1 = 'DART') THEN (9179)
WHEN (p.minor1 IN ('RT','RESP') AND st.cum_earn_hrs >= 24) THEN (70897)
WHEN (p.major1 IN ('CDSC','CDSD')) THEN (52125)
WHEN (p.major1 IN ('CA','CB')) THEN (24702)
WHEN (p.minor1 = 'NURS') THEN (51569)
WHEN (p.major1 = 'LEG') THEN (13324)
WHEN (p.major1 = 'CC') THEN (73837)
WHEN (p.major1 = 'CCRE') THEN (1133)
WHEN ((p.adv_id IN (SELECT DISTINCT id FROM fac_rec WHERE stat = 'I'))
OR (st.cum_earn_hrs < 24 AND (p.adv_id||p.major1) IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat = 'A' AND max_stu > 0 AND min_hrs >= 24))
OR (s.id NOT IN (SELECT DISTINCT stu.id FROM stu_acad_rec stu, sess_info si WHERE stu.yr = si.prev_yr AND stu.sess = si.prev_sess AND stu.reg_hrs > 0 AND stu.reg_stat IN ('C','R') AND stu.prog = 'UNDG'))
OR ((p.adv_id||p.major1) IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat <> 'A' OR max_stu <= 0))
OR ((p.adv_id||p.major1) NOT IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat = 'A' AND max_stu > 0)))
THEN (9238)
ELSE (p.adv_id)
END new_adv_id,
CASE WHEN (p.adv_id <> 35808 AND p.major1 = 'NS') THEN ('Deborah')
WHEN (p.adv_id = 35808 AND p.major1 = 'NS') THEN ('Veronica')
WHEN (p.adv_id = 9179 AND p.major1 = 'DART') THEN ('Stella')
WHEN (p.minor1 IN ('RT','RESP') AND st.cum_earn_hrs >= 24) THEN ('Lisa')
WHEN (p.major1 IN ('CDSC','CDSD')) THEN ('Joanne')
WHEN (p.major1 IN ('CA','CB')) THEN ('Barbara')
WHEN (p.minor1 = 'NURS') THEN ('Karen')
WHEN (p.major1 = 'LEG') THEN ('Nancy')
WHEN (p.major1 = 'CC') THEN ('Alberta')
WHEN (p.major1 = 'CCRE') THEN ('Naomi')
WHEN ((p.adv_id IN (SELECT DISTINCT id FROM fac_rec WHERE stat = 'I'))
OR (st.cum_earn_hrs < 24 AND (p.adv_id||p.major1) IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat = 'A' AND max_stu > 0 AND min_hrs >= 24))
OR (s.id NOT IN (SELECT DISTINCT stu.id FROM stu_acad_rec stu, sess_info si WHERE stu.yr = si.prev_yr AND stu.sess = si.prev_sess AND stu.reg_hrs > 0 AND stu.reg_stat IN ('C','R') AND stu.prog = 'UNDG'))
OR ((p.adv_id||p.major1) IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat <> 'A' OR max_stu <= 0))
OR ((p.adv_id||p.major1) NOT IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat = 'A' AND max_stu > 0)))
THEN ('Staff')
ELSE (adv_id.fullname)
END new_adv_name,
CASE WHEN (p.adv_id <> 35808 AND p.major1 = 'NS') THEN ('NS majors not assigned to Veronica go to Debbie')
WHEN (p.adv_id = 35808 AND p.major1 = 'NS') THEN ('NS majors stay with Veronica')
WHEN (p.adv_id = 9179 AND p.major1 = 'DART') THEN ('DART majors stay with Stella')
WHEN (p.minor1 IN ('RT','RESP') AND st.cum_earn_hrs >= 24) THEN ('RT-RESP minors go to Lisa')
WHEN (p.major1 IN ('CDSC','CDSD')) THEN ('CDSC-CDSD majors go to Joanne')
WHEN (p.major1 IN ('CA','CB')) THEN ('CA-CB majors go to Barbara')
WHEN (p.minor1 = 'NURS') THEN ('NURS minors go to Karen')
WHEN (p.major1 = 'LEG') THEN ('LEG majors go to Nancy')
WHEN (p.major1 = 'CC') THEN ('CC majors go to Alberta')
WHEN (p.major1 = 'CCRE') THEN ('CCRE majors go to Naomi')
WHEN (p.adv_id IN (SELECT DISTINCT id FROM fac_rec WHERE stat = 'I'))
THEN ('Current advisor is inactive')
WHEN (st.cum_earn_hrs < 24 AND (p.adv_id||p.major1) IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat = 'A' AND max_stu > 0 AND min_hrs >= 24))
THEN ('Total credits for this student did not meet the advisor reqs for this major')
WHEN (s.id NOT IN (SELECT DISTINCT stu.id FROM stu_acad_rec stu, sess_info si WHERE stu.yr = si.prev_yr AND stu.sess = si.prev_sess AND stu.reg_hrs > 0 AND stu.reg_stat IN ('C','R') AND stu.prog = 'UNDG'))
THEN ('This student did not attend '||si.prev_sess||si.prev_yr)
WHEN ((p.adv_id||p.major1) IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE (stat <> 'A' OR max_stu <= 0)))
THEN ('Current advisor is not advising students with this major')
WHEN ((p.adv_id||p.major1) NOT IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat = 'A' AND max_stu > 0))
THEN ('Current advisor is not advising students with this major')
ELSE ('Student will stay with current advisor')
END change_comm
FROM stu_acad_rec s,
prog_enr_rec p,
OUTER stu_stat_rec st,
id_rec stu_id,
id_rec adv_id,
sess_info si
WHERE s.id = p.id
AND s.id = st.id
AND s.id = stu_id.id
AND p.adv_id = adv_id.id
AND s.yr = si.curr_yr
AND s.sess = si.curr_sess
AND s.reg_hrs > 0
AND s.reg_stat IN ('C','R')
AND s.prog = 'UNDG'
AND p.prog = 'UNDG'
AND st.prog = 'UNDG'
AND s.id NOT IN (3,287,9238,59999) {System test use IDs}
INTO TEMP stu_list
WITH NO LOG;
