Oracle中SQL语句块的编写方法

构配置管理 

配置信息中存在着大量的SQL语句块。在此主要讲述语句块的写法和我们在写语句块的过程中须遵循的规则。

系统中的语句块大体可以分为两类：

一、        只是让数据库执行某些功能

这种SQL语句存在于文书关系中新加时的特殊数据操作、编辑时的特殊数据操作、删除时的特殊数据操作、审批点设置中同意的特别操作、不同意的特别操作、更多操作的设定、审批完后的数据处理、管理员修改审批意见时的特殊操作  

要想使这种语句块能够在Oracle里正确执行，必须做相应的处理，有以下几个方面：

必须以begin开头，并且以end;结尾； 

如果语句块中有变量申明，须把变量声明在最前面；为了区分，我们约定语句块中用到的所有变量均以v_开头；

必须遵照Oracle语法，Oracle用的是PL-SQL。有关PL-SQL和T-SQL的区别请参见附录。

下面给出一个语句块的模板：（嫌疑人新增时的特殊操作）

sql 代码

1. declare  
2.   v_bianhao              VARCHAR2(20);   
3.   v_Year                 INT;   
4.   v_MaxYear              INT;   
5.   v_MaxNumber            INT;   
6.   v_TempStr              CHAR(7);   
7.   v_quhao                NVARCHAR2(6);   
8.   ErrVal                 INT;   
9.   v_MingCheng            VARCHAR2(2000);   
10.   v_XYR_XingMing         VARCHAR2(100);   
11.   v_AnJianID             VARCHAR2(100);   
12.   CALLINGPROCASSIGNTEMP1 VARCHAR2(33);   
13.   CALLINGPROCASSIGNTEMP2 VARCHAR2(33);   
14. BEGIN  
15.   --公安局的区号    
16.   v_quhao := GetSystemValue('quhao');   
17.   
18.  /*取当前的年份*/   
19.   SELECT TO_NUMBER(TO_CHAR((SYSDATE), 'YYYY')) INTO v_Year FROM DUAL;   
20.      
21.  /*从库中取最大的年份*/   
22.   BEGIN  
23.     FOR REC IN (SELECT NVL(MAX(CAST(SUBSTR(SUBSTR(BianHao,   
24.                                                   LENGTH(BianHao) - 11 + 1,   
25.                                                   11),   
26.                                            0,   
27.                                            4) as int)),   
28.                            0) TEMP_ALIAS_0   
29.                   FROM XZ_XianYiRen) LOOP   
30.       v_MaxYear := REC.TEMP_ALIAS_0;   
31.     END LOOP;   
32.   END;   
33.   
34.  --如果当前年份大于库中年份，则从第一号开始   
35.   IF v_Year > v_MaxYear THEN  
36.     BEGIN  
37.        
38.       SELECT 'Y' || v_quhao || CAST(v_Year as char(4)) || '0000001'   
39.         INTO v_bianhao   
40.         FROM DUAL;   
41.     END;   
42.   ELSE  
43.      
44. --否则在原号上累加      
45.     BEGIN  
46.       BEGIN  
47.         FOR REC IN (SELECT NVL(MAX(SUBSTR(BianHao,   
48.                                           LENGTH(BianHao) - 7 + 1,   
49.                                           7)),   
50.                                0) TEMP_ALIAS_0   
51.                       FROM XZ_XianYiRen   
52.                      WHERE SUBSTR(RTRIM(LTRIM(BianHao)), 0, 11) =   
53.                            'Y' || v_quhao || CAST(v_Year as char(4))) LOOP   
54.           v_MaxNumber := REC.TEMP_ALIAS_0;   
55.         END LOOP;   
56.       END;   
57.       v_TempStr := lpad(v_MaxNumber + 1, 7, '0');   
58.        
59.       SELECT 'Y' || v_quhao || CAST(v_Year as char(4)) || v_TempStr   
60.         INTO v_bianhao   
61.         FROM DUAL;   
62.     END;   
63.   END IF;   
64.   
65.   UPDATE xz_xianyiren SET bianhao = v_bianhao WHERE IIDD = '[TableIIDD]';   
66.   
67.   BEGIN  
68.     FOR REC IN (SELECT XingMing, AnJianID   
69.                   FROM xz_xianyiren   
70.                  WHERE IIDD = '[TableIIDD]') LOOP   
71.       v_XYR_XingMing := REC.XingMing;   
72.       v_AnJianID     := REC.AnJianID;   
73.     END LOOP;   
74.   END;   
75.   
76.   BEGIN  
77.     FOR REC IN (SELECT MingCheng FROM GG_AnJian WHERE AnJianID = v_AnJianID) LOOP   
78.       v_MingCheng := REC.MingCheng;   
79.     END LOOP;   
80.   END;   
81.   
82.   UPDATE GG_AnJian   
83.      SET MingCheng = v_XYR_XingMing || v_MingCheng   
84.    WHERE AnJianID = v_AnJianID;   
85.   
86.   CALLINGPROCASSIGNTEMP1 := '[TableName]';   
87.   CALLINGPROCASSIGNTEMP2 := '[TableIIDD]';   
88.   SET_XZ_OtherUpdate(CALLINGPROCASSIGNTEMP1, CALLINGPROCASSIGNTEMP2);   
89. END;   

一、        让数据库执行某些功能并且返回不定数目的数据集

这种SQL语句块广泛存在于管理数据列中触发变化的SQL语句、默认值，文书关系中

首次读取时的特殊操作，文书浏览、添加、编辑、删除、LOAD权限设置，审批点设置中开始特殊判断、同意进入下一步的通知内容格式、不同意进入下一步通知内容格式、通知内容格式、更多操作的选择值设定、下级【是否同意】默认值、下级【更多操作】默认值、【是否同意】触发变化的SQL语句、【是否同意更多操作】触发变化的SQL语句

    同样，这种语句块需要遵循第一种语句块的所有规则。另外，它还有一些特殊的写法：

1、所有返回数据集的SQL语句需要修改为已隐式游标返回数据集：

原来的Select * from CertainTable语法，需要写成open :cur_REC1 for Select * from CertainTable; 其中cur_REC1是游标的名称，转换过程中，游标名称必须以cur_开头，并且在游标的名称前面加冒号,加冒号表明声明了一个隐式的游标。

2、在实际运行过程中，语句块中的某些返回游标可能没有被执行，例如

sql 代码

1. If  trim(‘[InValue]’) is null then  
2. Begin  
3.     Open :cur_RCT1 for select ‘1’ from dual;   
4. End;   
5. Else  
6. Begin  
7. Open :cur_RCT2 for select ‘2’ from dual;   
8. Open :cur_RCT3 for select ‘3’ from dual;   
9. End;   
10. End if;   

这种语句块，在运行过程中，最终返回的游标数量是不确定的，而.net在执行oracle语句块返回记录集的时候要求不论程序走哪条路径，返回的记录集的数量是相等的。这就需要我们做一些特别的处理：

只要语句块中需要返回数据集的地方，我们都用一个新的游标来返回，就象上面的语句块一样，每一个游标的名称都是不重复的。但是需要在语句块的最开始的时候先打开所有下面要用到的游标。

改造后的语句块如下：

sql 代码

1. Open :cur_RCT1 for select ‘’ from dual;   
2. Open :cur_RCT2 for select ‘’ from dual;   
3. Open :cur_RCT3 for select ‘’ from dual;   
4. If  trim(‘[InValue]’) is null then  
5. Begin  
6.     Open :cur_RCT1 for select ‘1’ from dual;   
7. End;   
8. Else  
9. Begin  
10. Open :cur_RCT2 for select ‘2’ from dual;   
11. Open :cur_RCT3 for select ‘3’ from dual;   
12. End;   
13. End if;   

 

    同一个游标可以被重复打开，并且以最后一次打开时选择出的资料为准。

    语句块最开始时统一打开所有游标的时候，打开顺序必须跟后面的游标在语句块中出现的物理顺序一致。例如，上面的语句块在最初打开时就不能先打开cur_RCT2再打开cur_RCT1

3、为了节省资源，在有的比较简单并且结构很清晰的语句块中，可以不遵循第2条所讲述的方法。上面的方法是在逻辑比较复杂的时候不得已而采用的方法。

例如：

sql 代码

1. If  trim(‘[InValue]’) is null then  
2. Begin  
3.     Open :cur_RCT1 for select ‘1’ from dual;   
4. End;   
5. Else  
6. Begin  
7. Open :cur_RCT2 for select ‘2’ from dual;   
8. End;   
9. End if;   

 

以上语句块就可以只使用一个游标，并且不用在最开始的时候先打开。可以修改为：

sql 代码

1. If  trim(‘[InValue]’) is null then  
2. Begin  
3.     Open :cur_RCT1 for select ‘1’ from dual;   
4. End;   
5. Else  
6. Begin  
7. Open :cur_RCT1 for select ‘2’ from dual;   
8. End;   
9. End if;   
10.   

 

4、所有文书的IIDD触发变化的SQL语句的最前端需要增加对退查的特别处理

-----------------退查的处理，所有文书的IIDD触发都需要增加此段

sql 代码

1. SP_OnChange_TuiCha('[TableName]','[TableIIDD]','[Obj=Session]BianHao[/Obj]',   
2. '[Obj=QueryString]AnJianID[/Obj]',v_IsTuiCha);   
3. if v_IsTuiCha='1' then  
4. begin  
5.     open :cur_RCT1 for select 'AllControl' as EnableControl from dual;   
6. end;   
7. end if;   
8. ----------------------------------------------------------   
9.   

 

IIDD触发变化的SQL语句块模板：(刑事，接受案件登记表)

sql 代码

1. declare  
2.   v_isrole                   varchar2(1);   
3.   v_result                   varchar2(1000);   
4.   v_XianShiBuMenGongZhang    varchar2(10);   
5.   v_XianShiGongAnJuGongZhang varchar2(10);   
6.   v_IsTuiCha                 varchar2(1) := '0';   
7. begin  
8.   open :cur_RCT1 for select '' from dual;   
9.   open :cur_RCT2 for select '' from dual;   
10.   open :cur_RCT3 for select '' from dual;   
11.   open :cur_RCT4 for select '' from dual;   
12.   open :cur_RCT5 for select '' from dual;   
13.   open :cur_RCT6 for select '' from dual;   
14.   open :cur_RCT7 for select '' from dual;   
15.   open :cur_RCT8 for select '' from dual;   
16.   
17.   -----------------退查的处理，所有文书的IIDD触发都需要增加此段   
18.   SP_OnChange_TuiCha('[TableName]',   
19.                      '[TableIIDD]',   
20.                      '[Obj=Session]BianHao[/Obj]',   
21.                      '[Obj=QueryString]AnJianID[/Obj]',   
22.                      v_IsTuiCha);   
23.   if v_IsTuiCha = '1' then  
24.     begin  
25.       open :cur_RCT1 for  
26.         select 'AllControl' as EnableControl from dual;   
27.     end;   
28.   end if;   
29.   ----------------------------------------------------------   
30.   
31.   if ('[InValue]' = '0') then  
32.     begin  
33.       open :cur_RCT2 for  
34.         select 'ChuLiJieGuo' as DisableControl from dual;   
35.     end;   
36.   else  
37.     begin  
38.       open :cur_RCT3 for  
39.         select 'SunDun_OnChange(''JieShouLeiXing'');' as command from dual;   
40.       sp_Check_ShenPi_IsRole('[TableName]',   
41.                              '[TableIIDD]',   
42.                              '[Obj=Session]BianHao[/Obj]',   
43.                              v_isrole);   
44.       IF v_isrole = '0' then  
45.         begin  
46.           open :cur_RCT4 for  
47.             select 'AllControl' as DisableControl from dual;   
48.           v_result := '';   
49.           GetColumnValue('[TableName]',   
50.                          '[TableIIDD]',   
51.                          'ChuLiJieGuo',   
52.                          v_result);   
53.           if (trim(v_result) is null) then  
54.             begin  
55.               open :cur_RCT5 for  
56.                 select 'ChuLiJieGuo' as EnableControl from dual;   
57.             end;   
58.           end if;   
59.         end;   
60.       else  
61.         begin  
62.           open :cur_RCT6 for  
63.             select 'ChuLiJieGuo' as DisableControl from dual;   
64.         end;   
65.       end if;   
66.        
67.       --vicky,2005-8-8   
68.       --当单位领导审批意见不是"初查"和"转为行政案件"，   
69.       --并且局长还没有审批时，编辑页面不显示单位领导的意见和单位领导的签名   
70.       ----------------------------------------------------------------   
71.       begin  
72.         for REC in (select XianShiBuMenGongZhang, XianShiGongAnJuGongZhang   
73.                       from TableName   
74.                      where iidd = '[InValue]') loop   
75.           v_XianShiBuMenGongZhang    := REC.XianShiBuMenGongZhang;   
76.           v_XianShiGongAnJuGongZhang := REC.XianShiGongAnJuGongZhang;   
77.         end loop;   
78.       end;   
79.        
80.       if trim(v_XianShiBuMenGongZhang) is null and  
81.          trim(v_XianShiGongAnJuGongZhang) is null then  
82.         begin  
83.           --以上条件为真，表明单位领导审批意见不是"初查"和"转为行政案件"，   
84.           --并且局长还没有审批   
85.           --此时的编辑页面不要显示部门领导的意见   
86.           open :cur_RCT7 for  
87.             select 'document.all.LingDaoPiShi1.style.display="none";   
88.                         document.all.LingDaoPiShi2.style.display="none";   
89.                         document.all.LingDaoPiShi3.style.display="none";' as command   
90.               from dual;   
91.         end;   
92.       end if;   
93.       ----------------------------------------------------------------   
94.     end;   
95.   end if;   
96.   
97.   if instr('[Obj=Session]Roles[/Obj]', '000001') > 0 then  
98.     begin  
99.       open :cur_RCT8 for  
100.         select 'AllControl' as EnableControl from dual;   
101.     end;   
102.   end if;   
103.   
104. end;