写在包里头的一个函数,第一次写这种东西,弄了一个上午也不知到错在哪里,感谢FUNCTION F_JH_REFRESHNEWSPROC() RETURN NVARCHAR2 IS
CLOB_PROC_TXT NVARCHAR2(8000);
V_INSERTSQL NVARCHAR2(500);
V_SELECTSQL NVARCHAR2(500);
CURSOR CURSOR_LOOP IS
SELECT INSERT_PART.SQL_APP INSERTSQL, --SQL 插入部分
SELECT_PART.SQL_APP SELECTSQL --SQL 数据处理部分
FROM AUD_JH_FUN_LIST AS INSERT_PART
LEFT OUTER JOIN AUD_JH_FUN_LIST AS SELECT_PART ON INSERT_PART.FUN_LIST_NO =
SELECT_PART.FUN_LIST_NO
AND SELECT_PART.IS_EXCUTE = '1' -- 存在数据处理SQL
WHERE INSERT_PART.IS_EXCUTE = '2'; -- 存在数据插入SQL
BEGIN
--**主程序块 ******************************--
CLOB_PROC_TXT := 'CREATE OR REPLACE PROCEDURE SP_JH_check_data(NVARCHAR2_Org_No IN NVARCHAR2 ,DATE_StartDate IN DATE,DATE_EndDate IN DATE ) IS BEGIN'; --存储过程头部
OPEN CURSOR_LOOP;
LOOP
FETCH CURSOR_LOOP
INTO V_INSERTSQL, V_SELECTSQL;
CLOB_PROC_TXT := CLOB_PROC_TXT || '; COMMIT; ' || V_INSERTSQL ||
V_SELECTSQL; --循环加入数据处理语句
EXIT WHEN REC.COUNT = 0;
END LOOP;
CLOSE CURSOR_LOOP;
CLOB_PROC_TXT := CLOB_PROC_TXT || 'COMMIT; END SP_JH_check_data;'; --存储过程尾部
RETURN CLOB_PROC_TXT;
END F_JH_REFRESHNEWSPROC;
CLOB_PROC_TXT NVARCHAR2(8000);
V_INSERTSQL NVARCHAR2(500);
V_SELECTSQL NVARCHAR2(500);
CURSOR CURSOR_LOOP IS
SELECT INSERT_PART.SQL_APP INSERTSQL, --SQL 插入部分
SELECT_PART.SQL_APP SELECTSQL --SQL 数据处理部分
FROM AUD_JH_FUN_LIST AS INSERT_PART
LEFT OUTER JOIN AUD_JH_FUN_LIST AS SELECT_PART ON INSERT_PART.FUN_LIST_NO =
SELECT_PART.FUN_LIST_NO
AND SELECT_PART.IS_EXCUTE = '1' -- 存在数据处理SQL
WHERE INSERT_PART.IS_EXCUTE = '2'; -- 存在数据插入SQL
BEGIN
--**主程序块 ******************************--
CLOB_PROC_TXT := 'CREATE OR REPLACE PROCEDURE SP_JH_check_data(NVARCHAR2_Org_No IN NVARCHAR2 ,DATE_StartDate IN DATE,DATE_EndDate IN DATE ) IS BEGIN'; --存储过程头部
OPEN CURSOR_LOOP;
LOOP
FETCH CURSOR_LOOP
INTO V_INSERTSQL, V_SELECTSQL;
CLOB_PROC_TXT := CLOB_PROC_TXT || '; COMMIT; ' || V_INSERTSQL ||
V_SELECTSQL; --循环加入数据处理语句
EXIT WHEN REC.COUNT = 0;
END LOOP;
CLOSE CURSOR_LOOP;
CLOB_PROC_TXT := CLOB_PROC_TXT || 'COMMIT; END SP_JH_check_data;'; --存储过程尾部
RETURN CLOB_PROC_TXT;
END F_JH_REFRESHNEWSPROC;
FUNCTION F_JH_REFRESHNEWSPROC() RETURN CLOB;
END JH_TEST;
oracle QQ群:54775466
期待您的一起探讨
欢迎爱好者入群学习