oracle存储过程动态语句

直接上代码

 CREATE OR REPLACE PROCEDURE GETPRICELISTZGY(PARAMID      IN VARCHAR2,                                             RESULT_CURSO OUT SYS_REFCURSOR) IS   W_COUNT NUMBER;   S_SQL   VARCHAR2(2000); BEGIN   SELECT COUNT(1)     INTO W_COUNT     FROM ADMIN_ACCOUNT AW    WHERE AW.SNO = 1      AND AW.ADMIN_USERNAME like '%' || PARAMID || '%';   IF W_COUNT > 0 THEN     S_SQL := 'select * from ADMIN_ACCOUNT AW where AW.SNO = 1 AND AW.ADMIN_USERNAME =:w_id';   ELSE     S_SQL := 'select * from ADMIN_ACCOUNT AW where AW.SNO = 2 AND AW.ADMIN_USERNAME =:w_id';   END IF;   OPEN RESULT_CURSO FOR S_SQL     using PARAMID; --可以将SELECT部分放入V_SQL  EXCEPTION   WHEN OTHERS THEN     ROLLBACK;     RAISE; --RE RAISE WHATEVER EXCEPTION OCCURRED END; END GETPRICELISTZGY;

第二种情况

 CREATE OR REPLACE PROCEDURE mytest(PARAMID      IN VARCHAR2,                                    RESULT_CURSO OUT SYS_REFCURSOR) IS   W_COUNT NUMBER;   S_SQL   VARCHAR2(2000); BEGIN   SELECT COUNT(1)     INTO W_COUNT     FROM ADMIN_ACCOUNT AW    WHERE AW.SNO = 1      AND AW.ADMIN_USERNAME = PARAMID;   IF W_COUNT > 0 THEN     open RESULT_CURSO FOR       select AW.sno,aw.admin_username from ADMIN_ACCOUNT AW where AW.SNO = 1;   ELSE     S_SQL := 'select * from ADMIN_ACCOUNT AW where AW.SNO = 2 AND AW.ADMIN_USERNAME =:w_id';     OPEN RESULT_CURSO FOR S_SQL       using PARAMID; --可以将SELECT部分放入V_SQL    END IF; EXCEPTION   WHEN OTHERS THEN     ROLLBACK;     RAISE; --RE RAISE WHATEVER EXCEPTION OCCURRED END; END mytest;