通过Oracle存储过程执行动态SQL输出结果集

好久没用发现有些记不得了，整理记录下

CREATE OR REPLACE PROCEDURE P_TEST_OUTCUR1(O_CUR OUT SYS_REFCURSOR)
AS
 R_ERR1 EXCEPTION;  --自定义异常
 V_SQLS VARCHAR2(2000);   --动态SQL
 V_ERRMSG VARCHAR2(100);   --错误消息
 V_QUERY VARCHAR2(500);   --条件参数
BEGIN
 V_QUERY:='AND A.ENAME='''||'SMITH'||'''';
 IF V_QUERY IS NULL THEN
   RAISE R_ERR1;
 END IF;
 V_SQLS:='SELECT A.EMPNO,A.ENAME,A.JOB,A.SAL FROM EMP A WHERE 1=1 '||V_QUERY;

--执行动态SQL
--EXECUTE IMMEDIATE V_SQLS;
--执行动态SQL将结果集赋给输出游标
OPEN O_CUR FOR V_SQLS;
EXCEPTION
  WHEN R_ERR1 THEN
    OPEN O_CUR FOR SELECT '参数有误' FROM DUAL;
  WHEN OTHERS THEN
    V_ERRMSG:= SUBSTR(SQLERRM,1,100);
    OPEN O_CUR FOR SELECT V_ERRMSG FROM DUAL;
END P_TEST_OUTCUR1;