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||' ； 拼接的过程需要注意校验参数的合法性，增加存储过程的容错性。临时表使用了会话级，存储过程执行完，可以通过查询存储过程获取结果。
