`

Oracle中SQL语句块的编写方法

SQLOracle数据结构配置管理 
阅读更多

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

<o:p> </o:p>

系统中的语句块大体可以分为两类：<o:p></o:p>

一、        只是让数据库执行某些功能<o:p></o:p>

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

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

必须以begin开头，并且以end;结尾； <o:p></o:p>

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

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

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

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;   

一、        让数据库执行某些功能并且返回不定数目的数据集<o:p></o:p>

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

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

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

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

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

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

<o:p>
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;   
</o:p>

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

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

改造后的语句块如下：

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;   

 

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

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

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

例如：<o:p></o:p>

<o:p>
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语句的最前端需要增加对退查的特别处理<o:p></o:p>

-----------------退查的处理，所有文书的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;   

<o:p></o:p>

<o:p></o:p></o:p>
分享到：
| for循环的效率疑问
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    Oracle Sql语句转换成Mysql Sql语句

    OracleSqlConvert4MysqlSqlTool.java这个源码工具，根据描述，应该是实现了自动读取Oracle SQL语句，分析其结构，并根据MySQL的语法规则进行转换，然后将转换后的SQL语句保存到指定的目标文件中。这个工具简化了手动...

    oracle的SQL语句的一些经验总结

    以下是对"Oracle的SQL语句的一些经验总结"中可能涉及的关键知识点的详细阐述： 1. **基本查询**：SQL的基础是SELECT语句，用于从表中提取数据。例如，`SELECT column1, column2 FROM table_name;`用于获取指定列的...

    ORACLE数据库SQL语句编写优化总结

    下面将对ORACLE数据库SQL语句编写优化进行详细的总结。 首先，选择适合的ORACLE优化器至关重要。ORACLE提供了三种优化器模式：RULE（基于规则）、COST（基于成本）和CHOOSE（选择性）。在实际应用中，数据库管理员...

    ORACLE数据库SQL语句编写优化总结.rar

    这份"ORACLE数据库SQL语句编写优化总结"文档很可能是对如何提高SQL查询性能、减少资源消耗以及提升系统整体效率的详细阐述。以下是根据标题和描述推测的一些关键知识点： 1. **查询优化基础**： - **索引的使用**...

    Oracle高效SQL语句原则

    Oracle 高效 SQL 语句原则是指在编写 Oracle 数据库 SQL 语句时需要遵循的一些基本原则，以便提高 SQL 语句的执行效率，减少数据库服务器的负载，提高应用程序的性能。下面是 Oracle 高效 SQL 语句原则的详细介绍： ...

    Oracle基本sql语句

    在Oracle数据库中，SQL语句是与数据库进行交互的主要方式。本文将总结一些Oracle基本SQL语句，帮助用户快速复习并掌握其用法。 首先，了解SQL（结构化查询语言）的基础功能是十分必要的。SQL由一系列命令组成，这些...

    sqlserver自动生成sql语句工具sqlserver转oracle

    在压缩包子文件的文件名"ms转sql语句.exe"中，我们可以推测这可能是一个用于将SQL Server语句转换为Oracle兼容格式的可执行程序。这样的工具通常会提供用户友好的界面，让用户导入SQL Server的数据库元数据，然后...

    ORACLE数据库SQL语句编写优化.pdf

    ORACLE数据库SQL语句编写优化

    oracle利用批处理文件执行SQL语句，bat连接oracle数据库并执行语句

    在这个场景中，我们可以利用批处理文件来简化Oracle数据库的操作，如删除用户、创建用户、创建表和触发器，以及执行其他SQL语句。 首先，我们需要了解如何在批处理文件中连接Oracle数据库。这通常通过Oracle的...

    Oracle SQL编写规范

    Oracle SQL编写规范是指在编写SQL语句时的一些基本原则和方法，旨在提高SQL语句的执行效率和可读性。以下是Oracle SQL编写规范的详细解释： 1. 尽量用索引，特别在从大量数据中返回小部分数据时 使用索引可以大大...

    Oracle经典SQL语句

    在Oracle中，基本的SQL语句包括SELECT、INSERT、UPDATE、DELETE，分别用于查询、插入、更新和删除数据。 2. **SELECT语句**：这是最常用的SQL语句，用于从表中检索数据。它支持各种运算符（如=, , &gt;, &lt;, BETWEEN等...

    oracle常用sql语句大全 注释完整 放心使用

    本资料集包含了Oracle数据库中常用SQL语句的大全，注释完整，非常适合初学者和有经验的开发者参考使用。 1. **数据查询（SELECT语句）**： - `SELECT * FROM table_name;`：查询表table_name中的所有列。 - `...

    oracle的sql语句和语法

    本资料将重点介绍Oracle SQL语句和语法，适合初学者入门学习。 1. **SQL基础** - **SELECT语句**：用于从数据库中检索数据，是最常用的SQL语句。可以指定列名、表名、条件等，例如`SELECT column1, column2 FROM ...

    ORACLE数据库SQL语句编写优化总结.zip

    本资料“ORACLE数据库SQL语句编写优化总结”深入探讨了如何有效地编写和优化SQL语句，以达到最佳的查询效率。 1. **理解执行计划** - **执行计划**是Oracle解析SQL语句后确定的数据获取策略，了解执行计划可以帮助...

    oracle常用监控SQL语句集合

    "oracle常用监控SQL语句集合"提供了这样的工具集，旨在帮助用户解决在编写Oracle SQL语句时遇到的问题。 1. **V$视图**： Oracle提供了大量的动态性能视图（如V$SESSION, V$INSTANCE, V$PROCESS等），它们提供了...

    ORACLE__SQL语句教学

    Oracle SQL语句是数据库管理与查询的核心工具，用于从Oracle数据库中检索、更新和操作数据。本教程将介绍SQL SELECT语句的基本概念和用法，帮助你理解和掌握如何在Oracle环境中执行SQL查询。 首先，SQL SELECT语句...

    ORACLE的SQL语句脚本

    本文将深入探讨"ORACLE的SQL语句脚本"，特别是涉及的PL/SQL语言，以及如何利用这些脚本来创建表、索引和视图。 首先，我们来理解SQL（Structured Query Language），它是用于管理和操作数据库的标准语言。在Oracle...

    oracle常用SQL语句下载

    Oracle SQL是用于管理和操作Oracle数据库的强大工具，它包含多种用于数据查询、操作、定义和控制的语句。本文将深入探讨Oracle SQL中的常见查询和计算功能。 首先，SQL（Structured Query Language，结构化查询语言...

Magicbox
Global site tag (gtag.js) - Google Analytics