oracle游标循环的嵌套

旧城等待， 2021-09-30 14:08 625阅读 0赞

完成批量修改user\_tables中的所有表的栏位名(从MS SQL导入过来，发现大小写问题，造成很多麻烦)  
存储过程见下：

-- Created on 2012/3/14 by FREE
    declare
      -- Local variables here
      Cursor tbl_cur is select table_name from user_tables;
      --col_cur Cursor;
      i integer;
      tbl_name varchar2(50);
      col_name varchar2(50);
    begin
      -- Test statements here
      for tbl_name in tbl_cur LOOP
          for col_cur in (select COLUMN_NAME from all_tab_columns where table_name=tbl_name.table_name) LOOP
              dbms_output.put_line('alter table '||tbl_name.table_name||' rename column "'||col_cur.column_name||'" to '||col_cur.column_name);
          end LOOP;
      end LOOP;
    end;

(从以下转载文章中收获很多，谢谢作者。转自：http://hi.baidu.com/wang90627/blog/item/394e7d019d57329ce850cd5d.html)  
创建存储过程，需要有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;

转载于:https://www.cnblogs.com/coprince/p/3443216.html