oracle 动态sql

Execute immediate dynamic_string
[{bulk collect}into {define_variable[,define_variable]…|record}]
[using [in|out|in out] bind_argument[,[in|out|in out] bind_argument]…]
[{returning|return} into bind_argument[, bind_argument]…]
Define_variable用于指定存放单行查询结果的变量；

using in bind_argument用于指定存放传递给动态sql值的变量，即在dynamic中存在占位符时使用；

using out bind_argument用于指定存放动态sql返回值的变量。

 

 CREATE OR REPLACE PROCEDURE TESTIS SQLSTR_ VARCHAR2(100); --SQL 动态语句 DATETIME_ DATE; TABLE_NAME_ VARCHAR2(10);BEGIN TABLE_NAME_ := 'DUAL'; SQLSTR_:='Select sysdate　fa from '|| TABLE_NAME_; EXECUTE IMMEDIATE sqlstr_ INTO DateTime_ ; DBMS_OUTPUT.PUT_LINE(DateTime_);END TEST;

 

  PROCEDURE PRO_MXB_TJ(P_TYPE IN VARCHAR2, P_GLDW_LEN IN NUMBER, P_TJTW_LEN IN VARCHAR2, P_TJTW IN VARCHAR2, P_KSRQ IN DATE, P_JSRQ IN DATE, p_err OUT VARCHAR2) AS V_CONTION VARCHAR2(2000); BEGIN BEGIN V_CONTION := 'delete from GLO_TEMP_MXB'; EXECUTE IMMEDIATE V_CONTION; COMMIT; CASE P_TYPE WHEN '2' THEN V_CONTION := 'INSERT INTO GLO_TEMP_MXB (DWMC,TJFW,GXYCS,GXYSZ,TNBCS,TNBSZ,CJCS,CJSZ) SELECT V.DWMC, GXY.*,TNB.TNBCS, TNB.TNBSZ, CJ.CJCS, CJ.CJSZ FROM (SELECT SUBSTR(XQBH, 1, :1) TJFW, SUM(DECODE(STATE, 1, 1, 0)) GXYCS, SUM(DECODE(STATE, 2, 1, 0)) GXYSZ FROM WV_MBTJ A WHERE BSMC = ' || CHR(39) || '高血压' || CHR(39) || ' AND SUBSTR(XQBH, 1, :3) = :2 AND JDRQ BETWEEN :4 AND :5 GROUP BY SUBSTR(XQBH, 1, :1) ) GXY, (SELECT SUBSTR(XQBH, 1, :1) TJFW, SUM(DECODE(STATE, 1, 1, 0)) TNBCS, SUM(DECODE(STATE, 2, 1, 0)) TNBSZ FROM WV_MBTJ WHERE BSMC = ' || CHR(39) || '糖尿病' || CHR(39) || ' AND SUBSTR(XQBH, 1, :3) = :2 AND JDRQ BETWEEN :4 AND :5 GROUP BY SUBSTR(XQBH, 1, :1)) TNB, (SELECT SUBSTR(XQBH, 1, :1) TJFW, SUM(DECODE(STATE, 1, 1, 0)) CJCS, SUM(DECODE(STATE, 2, 1, 0)) CJSZ FROM WV_MBTJ WHERE BSMC = ' || CHR(39) || '残疾' || CHR(39) || ' AND SUBSTR(XQBH, 1, :3) = :2 AND JDRQ BETWEEN :4 AND :5 GROUP BY SUBSTR(XQBH, 1, :1)) CJ, VIEW_DWXX V WHERE GXY.TJFW = V.DWHM AND GXY.TJFW = TNB.TJFW AND GXY.TJFW = CJ.TJFW'; WHEN '1' THEN --服务站 V_CONTION := 'INSERT INTO GLO_TEMP_MXB SELECT V.DWMC, GXY.*,TNB.TNBCS, TNB.TNBSZ, CJ.CJCS, CJ.CJSZ FROM (SELECT SUBSTR(SQBH||GLDW, 1, :1) TJFW, A.GLDW, SUM(DECODE(STATE, 1, 1, 0)) GXYCS, SUM(DECODE(STATE, 2, 1, 0)) GXYSZ FROM WV_MBTJ A WHERE BSMC =' || CHR(39) || '高血压' || CHR(39) || ' AND SUBSTR(SQBH||GLDW, 1, :3) = :2 AND JDRQ BETWEEN :4 AND :5 GROUP BY SUBSTR(SQBH||GLDW, 1, :1), GLDW) GXY, (SELECT SUBSTR(SQBH||GLDW, 1, :1) TJFW, GLDW, SUM(DECODE(STATE, 1, 1, 0)) TNBCS, SUM(DECODE(STATE, 2, 1, 0)) TNBSZ FROM WV_MBTJ WHERE BSMC = ' || CHR(39) || '糖尿病' || CHR(39) || ' AND SUBSTR(SQBH||GLDW, 1, :3) = :2 AND JDRQ BETWEEN :4 AND :5 GROUP BY SUBSTR(SQBH||GLDW, 1, :1), GLDW) TNB, (SELECT SUBSTR(SQBH||GLDW, 1, :1) TJFW, GLDW, SUM(DECODE(STATE, 1, 1, 0)) CJCS, SUM(DECODE(STATE, 2, 1, 0)) CJSZ FROM WV_MBTJ WHERE BSMC = ' || CHR(39) || '残疾' || CHR(39) || ' AND SUBSTR(SQBH||GLDW, 1, :3) = :2 AND JDRQ BETWEEN :4 AND :5 GROUP BY SUBSTR(SQBH||GLDW, 1, :1), GLDW) CJ, VIEW_DWXX V WHERE GXY.TJFW = V.DWBH AND GXY.TJFW = TNB.TJFW AND GXY.TJFW = CJ.TJFW'; END CASE; EXECUTE IMMEDIATE V_CONTION USING P_GLDW_LEN, P_TJTW_LEN, P_TJTW, P_KSRQ, P_JSRQ, P_GLDW_LEN, P_GLDW_LEN, P_TJTW_LEN, P_TJTW, P_KSRQ, P_JSRQ, P_GLDW_LEN, P_GLDW_LEN, P_TJTW_LEN, P_TJTW, P_KSRQ, P_JSRQ, P_GLDW_LEN; COMMIT; END; EXCEPTION WHEN OTHERS THEN p_err := SQLERRM; END PRO_MXB_TJ;