配置信息中存在着大量的SQL语句块。在此主要讲述语句块的写法和我们在写语句块的过程中须遵循的规则。
系统中的语句块大体可以分为两类:
一、 只是让数据库执行某些功能
这种SQL语句存在于文书关系中新加时的特殊数据操作、编辑时的特殊数据操作、删除时的特殊数据操作、审批点设置中同意的特别操作、不同意的特别操作、更多操作的设定、审批完后的数据处理、管理员修改审批意见时的特殊操作
要想使这种语句块能够在Oracle里正确执行,必须做相应的处理,有以下几个方面:
必须以begin开头,并且以end;结尾;
如果语句块中有变量申明,须把变量声明在最前面;为了区分,我们约定语句块中用到的所有变量均以v_开头;
必须遵照Oracle语法,Oracle用的是PL-SQL。有关PL-SQL和T-SQL的区别请参见附录。
下面给出一个语句块的模板:(嫌疑人新增时的特殊操作)
- declare
- v_bianhao VARCHAR2(20);
- v_Year INT;
- v_MaxYear INT;
- v_MaxNumber INT;
- v_TempStr CHAR(7);
- v_quhao NVARCHAR2(6);
- ErrVal INT;
- v_MingCheng VARCHAR2(2000);
- v_XYR_XingMing VARCHAR2(100);
- v_AnJianID VARCHAR2(100);
- CALLINGPROCASSIGNTEMP1 VARCHAR2(33);
- CALLINGPROCASSIGNTEMP2 VARCHAR2(33);
- BEGIN
- --公安局的区号
- v_quhao := GetSystemValue('quhao');
- /*取当前的年份*/
- SELECT TO_NUMBER(TO_CHAR((SYSDATE), 'YYYY')) INTO v_Year FROM DUAL;
- /*从库中取最大的年份*/
- BEGIN
- FOR REC IN (SELECT NVL(MAX(CAST(SUBSTR(SUBSTR(BianHao,
- LENGTH(BianHao) - 11 + 1,
- 11),
- 0,
- 4) as int)),
- 0) TEMP_ALIAS_0
- FROM XZ_XianYiRen) LOOP
- v_MaxYear := REC.TEMP_ALIAS_0;
- END LOOP;
- END;
- --如果当前年份大于库中年份,则从第一号开始
- IF v_Year > v_MaxYear THEN
- BEGIN
- SELECT 'Y' || v_quhao || CAST(v_Year as char(4)) || '0000001'
- INTO v_bianhao
- FROM DUAL;
- END;
- ELSE
- --否则在原号上累加
- BEGIN
- BEGIN
- FOR REC IN (SELECT NVL(MAX(SUBSTR(BianHao,
- LENGTH(BianHao) - 7 + 1,
- 7)),
- 0) TEMP_ALIAS_0
- FROM XZ_XianYiRen
- WHERE SUBSTR(RTRIM(LTRIM(BianHao)), 0, 11) =
- 'Y' || v_quhao || CAST(v_Year as char(4))) LOOP
- v_MaxNumber := REC.TEMP_ALIAS_0;
- END LOOP;
- END;
- v_TempStr := lpad(v_MaxNumber + 1, 7, '0');
- SELECT 'Y' || v_quhao || CAST(v_Year as char(4)) || v_TempStr
- INTO v_bianhao
- FROM DUAL;
- END;
- END IF;
- UPDATE xz_xianyiren SET bianhao = v_bianhao WHERE IIDD = '[TableIIDD]';
- BEGIN
- FOR REC IN (SELECT XingMing, AnJianID
- FROM xz_xianyiren
- WHERE IIDD = '[TableIIDD]') LOOP
- v_XYR_XingMing := REC.XingMing;
- v_AnJianID := REC.AnJianID;
- END LOOP;
- END;
- BEGIN
- FOR REC IN (SELECT MingCheng FROM GG_AnJian WHERE AnJianID = v_AnJianID) LOOP
- v_MingCheng := REC.MingCheng;
- END LOOP;
- END;
- UPDATE GG_AnJian
- SET MingCheng = v_XYR_XingMing || v_MingCheng
- WHERE AnJianID = v_AnJianID;
- CALLINGPROCASSIGNTEMP1 := '[TableName]';
- CALLINGPROCASSIGNTEMP2 := '[TableIIDD]';
- SET_XZ_OtherUpdate(CALLINGPROCASSIGNTEMP1, CALLINGPROCASSIGNTEMP2);
- END;
一、 让数据库执行某些功能并且返回不定数目的数据集
这种SQL语句块广泛存在于管理数据列中触发变化的SQL语句、默认值,文书关系中
首次读取时的特殊操作,文书浏览、添加、编辑、删除、LOAD权限设置,审批点设置中开始特殊判断、同意进入下一步的通知内容格式、不同意进入下一步通知内容格式、通知内容格式、更多操作的选择值设定、下级【是否同意】默认值、下级【更多操作】默认值、【是否同意】触发变化的SQL语句、【是否同意更多操作】触发变化的SQL语句
同样,这种语句块需要遵循第一种语句块的所有规则。另外,它还有一些特殊的写法:
1、所有返回数据集的SQL语句需要修改为已隐式游标返回数据集:
原来的Select * from CertainTable语法,需要写成open :cur_REC1 for Select * from CertainTable; 其中cur_REC1是游标的名称,转换过程中,游标名称必须以cur_开头,并且在游标的名称前面加冒号,加冒号表明声明了一个隐式的游标。
2、在实际运行过程中,语句块中的某些返回游标可能没有被执行,例如
- If trim(‘[InValue]’) is null then
- Begin
- Open :cur_RCT1 for select ‘1’ from dual;
- End;
- Else
- Begin
- Open :cur_RCT2 for select ‘2’ from dual;
- Open :cur_RCT3 for select ‘3’ from dual;
- End;
- End if;
这种语句块,在运行过程中,最终返回的游标数量是不确定的,而.net在执行oracle语句块返回记录集的时候要求不论程序走哪条路径,返回的记录集的数量是相等的。这就需要我们做一些特别的处理:
只要语句块中需要返回数据集的地方,我们都用一个新的游标来返回,就象上面的语句块一样,每一个游标的名称都是不重复的。但是需要在语句块的最开始的时候先打开所有下面要用到的游标。
改造后的语句块如下:
- Open :cur_RCT1 for select ‘’ from dual;
- Open :cur_RCT2 for select ‘’ from dual;
- Open :cur_RCT3 for select ‘’ from dual;
- If trim(‘[InValue]’) is null then
- Begin
- Open :cur_RCT1 for select ‘1’ from dual;
- End;
- Else
- Begin
- Open :cur_RCT2 for select ‘2’ from dual;
- Open :cur_RCT3 for select ‘3’ from dual;
- End;
- End if;
同一个游标可以被重复打开,并且以最后一次打开时选择出的资料为准。
语句块最开始时统一打开所有游标的时候,打开顺序必须跟后面的游标在语句块中出现的物理顺序一致。例如,上面的语句块在最初打开时就不能先打开cur_RCT2再打开cur_RCT1
3、为了节省资源,在有的比较简单并且结构很清晰的语句块中,可以不遵循第2条所讲述的方法。上面的方法是在逻辑比较复杂的时候不得已而采用的方法。
例如:
- If trim(‘[InValue]’) is null then
- Begin
- Open :cur_RCT1 for select ‘1’ from dual;
- End;
- Else
- Begin
- Open :cur_RCT2 for select ‘2’ from dual;
- End;
- End if;
以上语句块就可以只使用一个游标,并且不用在最开始的时候先打开。可以修改为:
- If trim(‘[InValue]’) is null then
- Begin
- Open :cur_RCT1 for select ‘1’ from dual;
- End;
- Else
- Begin
- Open :cur_RCT1 for select ‘2’ from dual;
- End;
- End if;
4、所有文书的IIDD触发变化的SQL语句的最前端需要增加对退查的特别处理
-----------------退查的处理,所有文书的IIDD触发都需要增加此段
- SP_OnChange_TuiCha('[TableName]','[TableIIDD]','[Obj=Session]BianHao[/Obj]',
- '[Obj=QueryString]AnJianID[/Obj]',v_IsTuiCha);
- if v_IsTuiCha='1' then
- begin
- open :cur_RCT1 for select 'AllControl' as EnableControl from dual;
- end;
- end if;
- ----------------------------------------------------------
IIDD触发变化的SQL语句块模板:(刑事,接受案件登记表)
- declare
- v_isrole varchar2(1);
- v_result varchar2(1000);
- v_XianShiBuMenGongZhang varchar2(10);
- v_XianShiGongAnJuGongZhang varchar2(10);
- v_IsTuiCha varchar2(1) := '0';
- begin
- open :cur_RCT1 for select '' from dual;
- open :cur_RCT2 for select '' from dual;
- open :cur_RCT3 for select '' from dual;
- open :cur_RCT4 for select '' from dual;
- open :cur_RCT5 for select '' from dual;
- open :cur_RCT6 for select '' from dual;
- open :cur_RCT7 for select '' from dual;
- open :cur_RCT8 for select '' from dual;
- -----------------退查的处理,所有文书的IIDD触发都需要增加此段
- SP_OnChange_TuiCha('[TableName]',
- '[TableIIDD]',
- '[Obj=Session]BianHao[/Obj]',
- '[Obj=QueryString]AnJianID[/Obj]',
- v_IsTuiCha);
- if v_IsTuiCha = '1' then
- begin
- open :cur_RCT1 for
- select 'AllControl' as EnableControl from dual;
- end;
- end if;
- ----------------------------------------------------------
- if ('[InValue]' = '0') then
- begin
- open :cur_RCT2 for
- select 'ChuLiJieGuo' as DisableControl from dual;
- end;
- else
- begin
- open :cur_RCT3 for
- select 'SunDun_OnChange(''JieShouLeiXing'');' as command from dual;
- sp_Check_ShenPi_IsRole('[TableName]',
- '[TableIIDD]',
- '[Obj=Session]BianHao[/Obj]',
- v_isrole);
- IF v_isrole = '0' then
- begin
- open :cur_RCT4 for
- select 'AllControl' as DisableControl from dual;
- v_result := '';
- GetColumnValue('[TableName]',
- '[TableIIDD]',
- 'ChuLiJieGuo',
- v_result);
- if (trim(v_result) is null) then
- begin
- open :cur_RCT5 for
- select 'ChuLiJieGuo' as EnableControl from dual;
- end;
- end if;
- end;
- else
- begin
- open :cur_RCT6 for
- select 'ChuLiJieGuo' as DisableControl from dual;
- end;
- end if;
- --vicky,2005-8-8
- --当单位领导审批意见不是"初查"和"转为行政案件",
- --并且局长还没有审批时,编辑页面不显示单位领导的意见和单位领导的签名
- ----------------------------------------------------------------
- begin
- for REC in (select XianShiBuMenGongZhang, XianShiGongAnJuGongZhang
- from TableName
- where iidd = '[InValue]') loop
- v_XianShiBuMenGongZhang := REC.XianShiBuMenGongZhang;
- v_XianShiGongAnJuGongZhang := REC.XianShiGongAnJuGongZhang;
- end loop;
- end;
- if trim(v_XianShiBuMenGongZhang) is null and
- trim(v_XianShiGongAnJuGongZhang) is null then
- begin
- --以上条件为真,表明单位领导审批意见不是"初查"和"转为行政案件",
- --并且局长还没有审批
- --此时的编辑页面不要显示部门领导的意见
- open :cur_RCT7 for
- select 'document.all.LingDaoPiShi1.style.display="none";
- document.all.LingDaoPiShi2.style.display="none";
- document.all.LingDaoPiShi3.style.display="none";' as command
- from dual;
- end;
- end if;
- ----------------------------------------------------------------
- end;
- end if;
- if instr('[Obj=Session]Roles[/Obj]', '000001') > 0 then
- begin
- open :cur_RCT8 for
- select 'AllControl' as EnableControl from dual;
- end;
- end if;
- end;