1 概述
oracle的動態(tài)sql是指在語句塊使用execute immediate 執(zhí)行sql語句，sql語句可以使用存儲過程傳的參數(shù)進(jìn)行拼接，本文針對varchar2和number兩種類型的參數(shù)類型，進(jìn)行sql拼接并執(zhí)行娇掏。
2 實例測試
2.1 建表 和插入數(shù)據(jù)
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 存儲過程
功能：輸入日期區(qū)間，銷售數(shù)量滿足上限和下限的產(chǎn)品id
--新建臨時表，用于輸出查詢結(jié)果
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,--結(jié)束時間 YYYY-MM-DD HH24:MI:SS
IN_DOWN_LIMIT NUMBER,--數(shù)量下限
IN_UP_LIMIT NUMBER--數(shù)量上限
) IS
V_DT_SQL VARCHAR(10000);--動態(tài)執(zhí)行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執(zhí)行存儲過程
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;
輸出結(jié)果
image.png
2.若果number類型的參數(shù)傳空绢慢，會報ora-00936:缺失表達(dá)式，可以在存儲過程中增加對參數(shù)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 總結(jié)
oracle 動態(tài)拼接傳入?yún)?shù)，varchar2類型可以使用'''|| IN_START_DTIME || ''' 胰舆，number類型可以使用'||IN_DOWN_LIMIT||' 骚露； 拼接的過程需要注意校驗參數(shù)的合法性，增加存儲過程的容錯性缚窿。臨時表使用了會話級棘幸，存儲過程執(zhí)行完，可以通過查詢存儲過程獲取結(jié)果倦零。
