oracle定义动态游标,oracle存储过程定义游标用动态sql

CREATE OR REPLACE PROCEDURE PROC_AUTHORIZESAVE(

TABLENAME IN VARCHAR2 )

AS

V_MANDATETYPE VARCHAR2(2);

V_ID VARCHAR2(1000);

V_MANDATESCOPE CLOB;

AV_SPLIT VARCHAR2(2) := ';';

V_LENGTH NUMBER;

V_START NUMBER;

V_INDEX NUMBER;

V_STR VARCHAR2(1000);

V_SQL VARCHAR2(1000);--动态sql

BEGIN

DECLARE

MYCUSOR SYS_REFCURSOR;--定义游标

BEGIN

V_SQL:='DELETE FROM TLK_P_'||TABLENAME||'_mds';

EXECUTE IMMEDIATE(V_SQL);

V_SQL:='SELECT ID ,ITEM_MANDATESCOPE FROM tlk_P_'||TABLENAME||' where item_MANDATESCOPE is not null';

OPEN MYCUSOR FOR V_SQL;--用动态sql定义游标

LOOP

FETCH MYCUSOR INTO V_ID,V_MANDATESCOPE;

--游标取不到数据则退出

EXIT

WHEN MYCUSOR%NOTFOUND;

V_MANDATESCOPE := REPLACE(V_MANDATESCOPE,',',';'