oracle 存储过程执行动态sql实例

1 概述

oracle的动态sql是指在语句块使用execute immediate 执行sql语句，sql语句可以使用存储过程传的参数进行拼接，本文针对varchar2和number两种类型的参数类型，进行sql拼接并执行。

2 实例测试

2.1 建表 和插入数据

create table order_info(
  order_no varchar2(18) primary key,
  deal_time date,
  amount number(5,2) not null,
  product_id varchar2(18) not null,
  product_num number(3)
);

INSERT INTO order_info VALUES('1',SYSDATE,10,'1',1);
INSERT INTO order_info VALUES('2',SYSDATE,20,'3',1);
INSERT INTO order_info VALUES('3',SYSDATE,20,'1',2);
INSERT INTO order_info VALUES('4',SYSDATE,20,'2',1);
INSERT INTO order_info VALUES('5',SYSDATE,10,'1',1);
INSERT INTO order_info VALUES('6',SYSDATE,10,'1',1);
INSERT INTO order_info VALUES('7',SYSDATE,10,'3',1);
INSERT INTO order_info VALUES('8',SYSDATE,20,'1',2);
INSERT INTO order_info VALUES('9',SYSDATE,20,'2',1);
INSERT INTO order_info VALUES('10',SYSDATE,30,'1',1);

2.2 存储过程

功能：输入日期区间，销售数量满足上限和下限的产品id

--新建临时表，用于输出查询结果
create global temporary table TMP_PRODUCT_ID
(
  product_id VARCHAR2(18),
  NUM   NUMBER(12)
)ON COMMIT PRESERVE ROWS;

--存储过程
CREATE OR REPLACE PROCEDURE P_QUERY_PRODUCT_NO(
  IN_START_DTIME VARCHAR2,--开始时间 YYYY-MM-DD HH24:MI:SS
  IN_END_DTIME VARCHAR2,--结束时间 YYYY-MM-DD HH24:MI:SS
  IN_DOWN_LIMIT NUMBER,--数量下限
  IN_UP_LIMIT NUMBER--数量上限
  ) IS
  V_DT_SQL VARCHAR(10000);--动态执行SQL
BEGIN
    --清空临时表
    EXECUTE IMMEDIATE 'TRUNCATE TABLE TMP_PRODUCT_ID';
    COMMIT;

    V_DT_SQL:='INSERT INTO TMP_PRODUCT_ID(product_id,NUM) SELECT product_id,SUM(product_num)  FROM  order_info';
    V_DT_SQL:= V_DT_SQL|| ' WHERE deal_time BETWEEN to_date('''|| IN_START_DTIME || ''',''yyyy-mm-dd hh24:mi:ss'') AND to_date('''|| IN_END_DTIME || ''',''yyyy-mm-dd hh24:mi:ss'')' ;
    V_DT_SQL := V_DT_SQL|| ' GROUP BY product_id HAVING SUM(product_num)>='||IN_DOWN_LIMIT||' AND SUM(product_num)<='||IN_UP_LIMIT;
    V_DT_SQL := V_DT_SQL||' ORDER BY product_id ';
    DBMS_OUTPUT.PUT_LINE(V_DT_SQL);
    EXECUTE IMMEDIATE V_DT_SQL;
    COMMIT;

END P_QUERY_PRODUCT_NO;

2.3执行存储过程

1.正常传值

call P_QUERY_PRODUCT_NO('2021-03-11 00:00:00','2021-03-11 23:59:59',1,5);
SELECT * FROM TMP_PRODUCT_ID;

输出结果

image.png

2.若果number类型的参数传空，会报ora-00936:缺失表达式，可以在存储过程中增加对参数null值的判断

call P_QUERY_PRODUCT_NO('2021-03-11 00:00:00','2021-03-11 23:59:59',1,null);
SELECT * FROM TMP_PRODUCT_ID;

image.png

3 总结

oracle 动态拼接传入参数，varchar2类型可以使用'''|| IN_START_DTIME || ''' ，number类型可以使用'||IN_DOWN_LIMIT||' ； 拼接的过程需要注意校验参数的合法性，增加存储过程的容错性。临时表使用了会话级，存储过程执行完，可以通过查询存储过程获取结果。