SQL语言艺术.doc

一．接近DBMS核心 例：假设手工输入的文本数据可能包含多个相邻的“空格”，需要一个函数将多个空格替换成一个空格。 create or replace function squeeze(p_string in varchar2) return varchar2 is v_string varchar2(512) :=p_string; len1 number ; len2 number ; begin len1:=length(p_string); v_string :=replace(p_string , . ); len2 :=length(v_string); while(len2len1) loop len1:=len2; v_string := replace(v_string, , ); len2 :=length(v_string); end loop; return v_string; end; ①如果使用函数，始终应首选DBMS自带函数，自带函数在执行时比任何第三方开发的代码更接近数据库核心，相应的其效率也会高出许多 ②代码喜欢SQL内核—离核心越近，它就运行的越快。 二．一次完成多个更新 如果每次更新的是彼此无关的纪录，对一张表连续多次update操作可以接受；否则就应该把它们合并成一个update操作。 update tbl_A set pga_status=0 where pga_status in(1,3) and inv_type =0; update tbl_A set rd_status=0 where rd_status in(1,3) and inv_type =0; 把这两个update合并到一起，几乎不会有损失。 update tbl_A set pga_status=( case pga_status when 1 then 0 when 3 then 0 else pga_status end), rd_status=(case rd_status when 1 then 0 when 3 then 0 else pga_status end) where (pga_status in(1,3) or rd_status in(1,3)) and inv_type =0 有可能的话，用一个语句处理多个更新，尽量减少对同一个表的重复访问 三．简洁的SQL 例：如何把原本属于一个字段的两行数据，以一行数据两个字段的方式检索出来（这两行数据的字段有不同的值）。 第一种方法：使用ORACLE提供的bulk collect子句，一次性将两个值放在数组中 select closure_date bulk collect into dtPerStaArray from tperrslt where fiscal_year - to_char(Param_dtAcc,yyyy) and rslt_period in(1 ||to_char(Param_dtAcc,MM), 9||to_char(Param_dtAcc,MM)) order by rslt_period 这两个日期被分别保存在数组的第一个位置和第二个位置 第二种方法：使用ORACLE提供的decode select max(decode(substr(rslt_period ,1,1),1,closure_date,to_date(14/10/1066),DD/MM/YYYY)), max(decode(substr(rslt_period ,1,1),9,closure_date,to_date(14/10/1066),DD/MM/YYYY)) into dtPerSta,dtPerClosure from tperrslt where fiscal_year - to_char(Param_dtAcc,yyyy) and rslt_period in(1 ||to_char(Param_dtAcc,MM), 9||to_char(Param_dtAcc,MM)) 如果找到需要的纪录，就返回要找的日期；否则就返回一个在任何情况下都远比我们所需要日期要早的日期 四．SQL的进攻式编程 一般的建议是进行防御式编程，在开始处理之前先检查所有参数的合法性。但实际上对数据库编程而言，尽量同时作几件事情的进攻式编程有切实的优势。 例：信用卡付款处理需要进行一连串的检查。例如，检查提交的客户身份和卡号是否有效；信用卡是否过期；当前的支付额是否超过了信用额度。通过了所有的检查，支付才能继续。每当其中一个检查所要求的条件不符时就产生异常， 1．传