oracle存储过程游标嵌套

oracle存储过程游标嵌套

创建存储过程，需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系统权限。该权限可由系统管理员授予。创建一个存储过程的基本语句如下：

CREATE [OR REPLACE] PROCEDURE 存储过程名

   [(参数[IN|OUT|IN OUT]数据类型…)]

   {AS|IS}

      [说明部分]

   BEGIN

      可执行部分

   [EXCEPTION

      错误处理部分]

   END [过程名]；

其中：

    可选关键字OR REPLACE表示如果存储过程已经存在，则用新的存储过程覆盖，通常用于存储过程的重建。

    参数部分用于定义多个参数(如果没有参数，就可以省略)。参数有三种形式：IN、OUT和IN OUT。如果没有指明参数的形式，则默认为IN。

    关键字AS也可以写成IS，后跟过程的说明部分，可以在此定义过程的局部变量。

    编写存储过程可以使用任何文本编辑器或直接在SQL *Plus环境下进行，编写好的存储过程必须要在SQL *Plus环境下进行编译，生成编译代码，原代码和编译代码在编译过程中都会被存入数据库。编译成功的存储过程就可以在Oracle环境下进行调用了。

    一个存储过程在不需要时可以删除。删除存储过程的人是过程的创建者或者拥有DROP ANY PROCEDURE系统权限的人。删除存储过程的语法如下：

DROP PROCEDURE 存储过程名；

    如果要重新编译一个存储过程，则只能是过程的创建者或者拥有ALTER ANY PROCEDURE系统权限的人。语法如下：

ALTER PROCEDURE 存储过程名 COMPILE；

    执行(或调用)存储过程的人是过程的创建者或是拥有EXECUTE ANY PROCEDURE系统权限的人或是被拥有者授予EXECUTE权限的人。执行的方法如下：

    方法1：

EXECUTE 模式名.存储过程名[(参数…)];

    方法2：

BEGIN

   模式名.存储过程名 [(参数…)]；

END；

    传递的参数必须与定义的参数类型、个数和顺序一致(如果参数定义了默认值，则调用时可以省略参数)。参数可以是变量、常量或表达式，用法参见下一节。

    如果是调用本账户下的存储过程，则模式名可以省略。要调用其他账户编写的存储过程，则模式名必须要添加。

 

下面是我自己的实验：

注：游标可以“显式”声明，也可以“隐式”声明。“隐式”声明的意思是，不用声明游标，在for循环中直接用游标的record就行。例一是内外层循环游标都是“隐式”声明的情况（内层循环的游标，需要用到外层循环的游标值，所以不能两个游标都在第一个循环外声明）。(一般：游标名是xxx__cursor，游标中的每行记录名是xxx_record 。并且，内层循环要用游标，内层的游标应该要用“隐式”声明，外层游标可用可不用。)

例一：

CREATE OR REPLACE PROCEDUREmain_mx
           AS
            BEGIN
             FOR mainout_recordIN(  SELECT  /*这里的mainout_record直接是游标的每行记录了。另外IN后面如果是select语句，就要加括号，并且select语句结束不用;结尾，IN后面如果是游标名，就不要括号*/ ID,smscontent,allcode,cjr,pretongdaoid,clientid,shr,pretime,cjsj,shstatus,shsj

from t_busi_main_presend

where shstatus in('1','9') and klstatus='2') LOOP
             /* insert into T_BUSI_PRESEND_MX*/
                /*  CURSOR mainmobile_cursor IS
                       SELECT  * FROM TABLE(fn_split(mainout_record.allcode,','));  */
                
                    FOR mainmobile_record IN (SELECT  column_value cv FROM TABLE(fn_split(mainout_record.allcode,',')))  LOOP /*SELECT  column_value cv FROM TABLE(fn_split(mainout_record.allcode,',')，这句话是执行fn_split这个函数，函数功能类似java中的split功能，但是oracle本身没有这个函数，所以要自己写，代码是我在网上拷的，注意因为mainout_record.allcode这个记录集就只有“一列”记录，allcode是外层游标查出来的一个列名，所以前面是selectcolumn_value，后面打印值的时候也是写.column_value：

功能描述：用指定分隔符切割输入的字符串，返回一维数组，每个数组元素为一个子串。

先：

CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);

再：

CREATE OR REPLACE FUNCTION fn_split(p_str IN CLOB, p_delimiter IN VARCHAR2)
 RETURN ty_str_split
IS
  j INT := 0;
  i INT := 1;
  len INT := 0;
  len1 INT := 0;
  str VARCHAR2 (4000);
  str_split ty_str_split := ty_str_split ();
BEGIN
  len := LENGTH (p_str);
  len1 := LENGTH (p_delimiter);

  WHILE j < len
  LOOP
    j := INSTR (p_str, p_delimiter, i);

    IF j = 0
    THEN
        j := len;
        str := SUBSTR (p_str, i);
        str_split.EXTEND;
        str_split (str_split.COUNT) := str;

        IF i >= len
        THEN
          EXIT;
        END IF;
    ELSE
        str := SUBSTR (p_str, i, j - i);
        i := j + len1;
        str_split.EXTEND;
        str_split (str_split.COUNT) := str;
    END IF;
  END LOOP;

  RETURN str_split;
END fn_split;

*/
                         DBMS_OUTPUT.PUT_LINE(mainmobile_record.cv);/*打印结果的时候，直接反键存储过程名-->测试有时是出不来结果的，这时应该"新建"-->"命令窗口"--SQL>set serveroutput on    -->execute main_mx(这是存储过程名)，这样才能打印结果 */
                      END LOOP;
              END LOOP;    
           END;

 

例二（外层for循环游标是“显式”声明）：

CREATE OR REPLACE PROCEDUREmain_mx_1
       as
           CURSOR mainout_cursor IS SELECT ID,smscontent,allcode,cjr,pretongdaoid,clientid,shr,pretime,cjsj,shstatus,shsj from t_busi_main_presend where shstatus in('1','9') and klstatus='2';
            begin
             FOR mainout_record IN  mainout_cursor LOOP
             /*    insert into T_BUSI_PRESEND_MX*/

               /*  CURSOR mainmobile_cursor IS
                       SELECT  * FROM TABLE(fn_split(mainout_record.allcode,','));
                 BEGIN*/
                    FOR mainmobile_record IN (SELECT  column_value cv FROM TABLE(fn_split(mainout_record.allcode,',')))  LOOP
                          DBMS_OUTPUT.PUT_LINE(mainmobile_record.cv);
                      END LOOP;

               END LOOP;

           END;