oracle游标循环的嵌套

时间:2021-08-24 11:47:11

完成批量修改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('','') and klstatus='') 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('','') and klstatus='';
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;