1 概述

oracle的動(dòng)態(tài)sql是指在語(yǔ)句塊使用execute immediate 執(zhí)行sql語(yǔ)句，sql語(yǔ)句可以使用存儲(chǔ)過(guò)程傳的參數(shù)進(jìn)行拼接，本文針對(duì)varchar2和number兩種類型的參數(shù)類型，進(jìn)行sql拼接并執(zhí)行。

2 實(shí)例測(cè)試

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 存儲(chǔ)過(guò)程

功能：輸入日期區(qū)間，銷售數(shù)量滿足上限和下限的產(chǎn)品id

--新建臨時(shí)表，用于輸出查詢結(jié)果
create global temporary table TMP_PRODUCT_ID
(
  product_id VARCHAR2(18),
  NUM   NUMBER(12)
)ON COMMIT PRESERVE ROWS;

--存儲(chǔ)過(guò)程
CREATE OR REPLACE PROCEDURE P_QUERY_PRODUCT_NO(
  IN_START_DTIME VARCHAR2,--開(kāi)始時(shí)間 YYYY-MM-DD HH24:MI:SS
  IN_END_DTIME VARCHAR2,--結(jié)束時(shí)間 YYYY-MM-DD HH24:MI:SS
  IN_DOWN_LIMIT NUMBER,--數(shù)量下限
  IN_UP_LIMIT NUMBER--數(shù)量上限
  ) IS
  V_DT_SQL VARCHAR(10000);--動(dòng)態(tài)執(zhí)行SQL
BEGIN
    --清空臨時(shí)表
    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í)行存儲(chǔ)過(guò)程

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ù)傳空，會(huì)報(bào)ora-00936:缺失表達(dá)式，可以在存儲(chǔ)過(guò)程中增加對(duì)參數(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 動(dòng)態(tài)拼接傳入?yún)?shù)，varchar2類型可以使用'''|| IN_START_DTIME || ''' ，number類型可以使用'||IN_DOWN_LIMIT||' ； 拼接的過(guò)程需要注意校驗(yàn)參數(shù)的合法性，增加存儲(chǔ)過(guò)程的容錯(cuò)性。臨時(shí)表使用了會(huì)話級(jí)，存儲(chǔ)過(guò)程執(zhí)行完，可以通過(guò)查詢存儲(chǔ)過(guò)程獲取結(jié)果。