Oracle动态游标

(一)强类型动态游标

1 定义

强类型动态游标是指，在游标使用之前，虽未指定游标的查询定义，但是游标的类型已经确定。

2 语法

type 游标类型 is ref cursor return 记录类型

3 应用场景、

用户想在表dept中查看Beijing这一城市的具体信息。如果表中没有Beijing的记录信息，则用户希望能够查看所有城市的信息。

程序：

begin

declare

type ref_deptrow is ref cursor return dept%rowtype; /*强类型动态游标声明*/

c_count number;

deptrow dept%rowtype;

cur_deptrow ref_deptrow;

begin

select count(*) into c_count from dept where loc = 'Beijing';

if c_count = 0 then

open cur_deptrow for select * from dept;

else

open cur_deptrow for select * from dept where loc = 'Beijing';

end if;

fetch cur_deptrow into deptrow;

while cur_deptrow%found loop

dbms_output.put_line(deptrow.deptno || ':' || deptrow.dname || ':' || deptrow.loc);

fetch cur_deptrow into deptrow;

end loop;

end;

end;

/

运行结果：

10:ACCOUNTING:NEW YORK

20:RESEARCH:DALLAS

30:SALES:CHICAGO

40:OPERATIONS:BOSTON

PL/SQL procedure successfully completed.

(三)弱类型动态游标

1 定义

弱类型动态游标指的是在游标使用之前，游标的类型无法具体确定，要等到程序执行时，才能确定游标的类型。与强类型动态游标相比，弱类型动态游标没有返回类型。

2 语法

type 游标类型 is ref cursor

3 应用场景

用户想要在表emp查询deptno为40的部门信息，若查不到则想在表dept中继续查询。

程序：

begin

declare

type ref_tablerow is ref cursor;

c_count number;

emprow emp%rowtype;

deptrow dept%rowtype;

cur_tablerow ref_tablerow;

begin

select count(*) into c_count from emp where deptno = 40;

if c_count = 0 then

open cur_tablerow for select * from dept where deptno = 40;

fetch cur_tablerow into deptrow;

while cur_tablerow%found loop

dbms_output.put_line(deptrow.deptno || ':' ||

deptrow.dname || ':' ||

deptrow.loc);

fetch cur_tablerow into deptrow;

end loop;

else

open cur_tablerow for select * from emp where deptno = 40;

fetch cur_tablerow into emprow;

while cur_tablerow%found loop

dbms_output.put_line(emprow.deptno || ':' ||

emprow.empno || ':' ||

emprow.ename);

fetch cur_tablerow into deptrow;

end loop;

end if;

end;

end;

/

运行结果：

40:OPERATIONS:BOSTON

PL/SQL procedure successfully completed.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29485627/viewspace-1248672/，如需转载，请注明出处，否则将追究法律责任。

说明：下面的存储过程在Oracle817下全部通过测试，编译和运行均是正确的

一、最简单的一个动态游标：

CREATE OR REPLACE PROCEDURE test_cur

is

strSql1 varchar(1000);

TYPE TCUR IS REF CURSOR;

CUR TCUR;

AC_WHERE VARCHAR2(100);

AC VARCHAR2(100);

BEGIN

AC_WHERE := '(52228,52230)';

OPEN CUR FOR 'SELECT bill_id FROM bill_main WHERE bill_id IN '|| AC_WHERE;

LOOP

FETCH CUR INTO AC;

EXIT WHEN CUR%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(AC);

END LOOP;

CLOSE CUR;

end test_cur;

二、动态游标中使用动态的SQL语句并执行：

CREATE OR REPLACE PROCEDURE test_cur

(

p_orgid_wins string

)

is

strSql1 varchar2(1000);

TYPE My_CurType IS REF CURSOR;

CUR_1 My_CurType;--指示CUR_1的类型为My_CurType，而My_CurType是游标类型

AC_WHERE VARCHAR2(100);

AC VARCHAR2(100);

BEGIN

AC_WHERE := '(52228,52230)';

OPEN CUR_1 FOR 'SELECT bill_id FROM bill_main WHERE bill_id IN '|| AC_WHERE;--打开动态游标

LOOP

FETCH CUR_1 INTO AC;

EXIT WHEN CUR_1%NOTFOUND;

strSql1:='delete bill_main where bill_id='||AC;

DBMS_OUTPUT.PUT_LINE(strSql1);

DBMS_OUTPUT.PUT_LINE(AC);

execute immediate strSql1;--执行一个动态的SQL语句

commit;

END LOOP;

CLOSE CUR_1;

end test_cur;

三、动态游标中执行动态DQL语句：

CREATE OR REPLACE PROCEDURE test_cur

(

p_orgid_wins string

)

is

strSql1 varchar2(1000);

strSql2 varchar2(1000);

TYPE My_CurType IS REF CURSOR;

CUR_1 My_CurType;--指示CUR_1的类型为My_CurType，而My_CurType是游标类型

AC_WHERE VARCHAR2(100);

t_to_orgid number;

t_bill_id number;

BEGIN

AC_WHERE := '(98978,98980)';

strSql1:='SELECT bill_id,to_orgid FROM bill_main WHERE bill_id IN '|| AC_WHERE;

DBMS_OUTPUT.PUT_LINE(strSql1);

OPEN CUR_1 FOR strSql1;--打开动态游标

LOOP

FETCH CUR_1 INTO t_bill_id,t_to_orgid;

EXIT WHEN CUR_1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('t_to_orgid='||t_to_orgid);

strSql2:='delete bill_main where bill_id='||t_bill_id;

strSql2:=strSql2|| 'and start_no='||'16506';

DBMS_OUTPUT.PUT_LINE(strSql2);

DBMS_OUTPUT.PUT_LINE(t_bill_id);

execute immediate strSql1;--执行一个动态的SQL语句

commit;

END LOOP;

CLOSE CUR_1;

end test_cur;

表结构：

表数据：

期望结果：

虽然这种结果可以通过MySQL中的GROUP_CONCAT函数实现，但是此函数有字节长度限制

SELECT ROW_ID, CAST(CONCAT('[', GROUP_CONCAT('{\'FIELD_ID\'', ' : ', IF(INSTR(VALUE, '[{') = 0, CONCAT('\'', VALUE, '\''), VALUE), '}'), ']') AS BINARY) FROM T_TASK_DATA GROUP BY ROW_ID

DELIMITER $$

USE `mpp_master_data`$$

DROP PROCEDURE IF EXISTS `SP_QUERY_TASK_DATA`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_QUERY_TASK_DATA`()

BEGIN

DECLARE V_FIG INT DEFAULT 0;

DECLARE V_ROW_ID INT;

DECLARE V_FIELD_ID INT;

DECLARE V_FIELD_VALUE VARCHAR(10240);

DECLARE V_CRS_TASK_DATA CURSOR FOR SELECT DISTINCT ROW_ID AS V_ROW_ID FROM T_TASK_DATA LIMIT 0, 200;

DECLARE V_CRS_FIELD_DATA CURSOR FOR SELECT FIELD_ID AS V_FIELD_ID, VALUE AS V_FIELD_VALUE FROM T_TASK_DATA WHERE ROW_ID=V_ROW_ID ORDER BY FIELD_ID ASC;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_FIG = 1;

-- 创建临时表

DROP TABLE IF EXISTS TMP_TASK_ROW_DATA;

CREATE TEMPORARY TABLE TMP_TASK_ROW_DATA(ROW_ID INT(10), VALUE VARCHAR(10240), PRIMARY KEY (`ROW_ID`));

-- 打开第一层游标，进行遍历

OPEN V_CRS_TASK_DATA;

ROW_LOOP: LOOP

FETCH V_CRS_TASK_DATA INTO V_ROW_ID;

IF V_FIG = 1 THEN

LEAVE ROW_LOOP;

END IF;

SET @V_VALUE = '';

-- 打开第二层游标，进程遍历

OPEN V_CRS_FIELD_DATA;

FIELD_LOOP: LOOP

FETCH V_CRS_FIELD_DATA INTO V_FIELD_ID, V_FIELD_VALUE;

IF V_FIG = 1 THEN

LEAVE FIELD_LOOP;

END IF;

SET @V_VALUE = CONCAT(@V_VALUE, CONCAT(',{"FIELD_ID": ', V_FIELD_ID, ', "VALUE": "', IFNULL(V_FIELD_VALUE, '') ,'"}'));

END LOOP FIELD_LOOP;

CLOSE V_CRS_FIELD_DATA;

SET V_FIG = 0;

SET @V_VALUE = SUBSTRING(@V_VALUE, 2);

SET @V_VALUE = REPLACE(@V_VALUE, '"', '#');

SET @V_VALUE = REPLACE(@V_VALUE, '\'', '$');

SET @V_SQL = CONCAT('INSERT INTO TMP_TASK_ROW_DATA(ROW_ID, VALUE) VALUES (', V_ROW_ID, ', \'[', @V_VALUE, ']\');');

-- SELECT V_ROW_ID, @V_VALUE, @V_SQL;

PREPARE MAIN_STMT FROM @V_SQL;

EXECUTE MAIN_STMT;

DEALLOCATE PREPARE MAIN_STMT;

END LOOP ROW_LOOP;

CLOSE V_CRS_TASK_DATA;

SELECT * FROM TMP_TASK_ROW_DATA;

DROP TABLE TMP_TASK_ROW_DATA;

END$$

DELIMITER ;

以上第二个游标属于模拟动态，

第二种方式：

MySQL本身是不支持动态游标的，但可以通过(准备语句+视图+静态游标)的方法来近似实现。

参考：

说明：

游标循环 查询和更新是很慢的一件事情

第一层层循环200

第二层循环最大15

执行拼接的SQL插入数据到临时表

总共耗时21秒左右，两层循环耗时16秒左右