oracle 动态sql

最新推荐文章于 2025-06-10 09:00:00 发布

qin_phoenix

最新推荐文章于 2025-06-10 09:00:00 发布

阅读量934

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle 文章标签： oracle returning insert exception table date

本文链接：https://blog.youkuaiyun.com/qin_phoenix/article/details/5588340

oracle 专栏收录该内容

5 篇文章

订阅专栏

本文介绍如何使用PL/SQL执行立即动态字符串SQL，并通过动态SQL语句处理不同类型的数据，如日期、数字和字符串。示例包括设置变量、使用动态SQL更新和插入数据，以及错误处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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 TEST IS 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;