sqlserver、db2、oracle存儲過程動態sql語句示例

sqlserver、db2、oracle存儲過程動態sql語句示例

Oracle

CREATE OR REPLACE PROCEDURE a_test
AS
    t_sql   VARCHAR2(2000);
    t_a     VARCHAR2(20);
    t_b     VARCHAR2(20);
    t_c     VARCHAR2(20);
    t_d     VARCHAR2(20);
BEGIN
    t_c   := 'f';
    t_d   := 'g';
    
    
    --這裡可為insert 等任何sql語句.
    t_sql := 'SELECT MAX(a), MAX(b) FROM t1 WHERE c = :tempC OR c = :tempD';
    
    EXECUTE IMMEDIATE t_sql
    INTO  t_a, t_b  --如果不需要查詢賦值，這裡不用寫 INTO XXX語句.
    USING t_c, t_d  -- 如果不需要使用變量，不用寫USING XXX語句.
    ;
END a_test;
/

--帶返回游標的動態執行語句.
CREATE OR REPLACE PROCEDURE a_test2
(
    o_cursor OUT SYS_REFCURSOR
)
AS
    t_sql   VARCHAR2(2000);
    t_a     VARCHAR2(20);
    t_b     VARCHAR2(20);
    t_c     VARCHAR2(20);
    t_d     VARCHAR2(20);
BEGIN
    t_c   := 'f';
    t_d   := 'g';
    
    
    --這裡可為insert 等任何sql語句.
    t_sql := 'SELECT * FROM t1 WHERE c = :tempC OR c = :tempD';

    OPEN  o_cursor FOR t_sql
    USING t_c, t_d   -- 同樣如果不需要使用變量，不用寫USING XXX語句.
    ;
END a_test2;
/

CREATE PROCEDURE a_test
AS
    DECLARE @t_sql   NVARCHAR(2000);  --sql server 動態語句要申明為NVARCHAR類型.
    DECLARE @t_a     VARCHAR(20);
    DECLARE @t_b     VARCHAR(20);
    DECLARE @t_c     VARCHAR(20);
    DECLARE @t_d     VARCHAR(20);
BEGIN
    SET @t_c = 'f';
    SET @t_d = 'g';
    
    --這裡可為insert 等任何sql語句.
    SET @t_sql = 'SELECT @tempA = MAX(a), @tempB = MAX(b) FROM t1 WHERE c = @tempC OR c = @tempD';
    
    --不可在EXECUTE SP_EXECUTESQL後邊拼接字符串
    EXECUTE SP_EXECUTESQL @t_sql
    
    --如果不帶參數，以下申明變量及傳入參數都不需要寫.
    --申明變量類型及出入參.必須一行寫完，不能換行.
    ,N'@tempA VARCHAR(20) OUT, @tempB VARCHAR(20) OUT, @tempC VARCHAR(20), @tempD VARCHAR(20)'
    -- 參數值.  傳入變量的順序要與申明變量的順序一致，
    ,@t_a OUT, @t_b OUT, @t_c, @t_d
    ;
END
GO
--sql server返回游標與普通的語句一樣，直接將t_sql賦值成 select * from XXX 即可.

CREATE PROCEDURE a_test
(
    v_c           VARCHAR(20)
)
BEGIN
    DECLARE t_sql VARCHAR2(2000);
    DECLARE t_a   VARCHAR2(20);
    DECLARE t_b   VARCHAR2(20);
    DECLARE t_c   VARCHAR2(20);
    DECLARE t_d   VARCHAR2(20);
    -- FOR 後邊的t_stmt要與下邊的 prepare後的變量一致，其類型為 statement.
    DECLARE t_cur CURSOR FOR t_stmt;

    SET t_c = 'f';
    SET t_d = 'g';
    
    --這裡可為insert 等任何sql語句.
    SET t_sql = 'SELECT MAX(a), MAX(b) FROM t1 WHERE c = :tempC OR c = :tempD';
    
    --我目前在這裡沒找到其它方式來替代，只能使用游標讀取：
    PREPARE t_stmt FROM t_sql;
    
    OPEN t_cur
    --如果不需要使用變量，不用寫USING XXX語句.
    USING t_c, t_d
    ;
    
    FETCH t_cur INTO t_a, t_b;
    CLOSE t_cur;
END

/*
帶游標返回的動態語句與普通語句一樣，只是申明游標時，要申明返回類型的游標
並且在begin關鍵字之前需要插入：
DYNAMIC RESULT SETS 1
LANGUAGE SQL
*/

CREATE PROCEDURE a_test2
(
    v_c           VARCHAR(20)
)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
    DECLARE t_sql VARCHAR2(2000);
    DECLARE t_a   VARCHAR2(20);
    DECLARE t_b   VARCHAR2(20);
    DECLARE t_c   VARCHAR2(20);
    DECLARE t_d   VARCHAR2(20);
    -- FOR 後邊的t_stmt要與下邊的 prepare後的變量一致，其類型為 statement.
    DECLARE t_cur CURSOR WITH RETURN FOR t_stmt;

    SET t_c = 'f';
    SET t_d = 'g';
    
    --這裡可為insert 等任何sql語句.
    SET t_sql = 'SELECT MAX(a), MAX(b) FROM t1 WHERE c = :tempC OR c = :tempD';
    
    PREPARE t_stmt FROM t_sql;
    
    OPEN t_cur
    --如果不需要使用變量，不用寫USING XXX語句.
    USING t_c, t_d
    ;
END