SQL Server, DB2, Oracle stored procedure dynamic SQL statement Example

Oracle

CREATE OR REPLACE PROCEDURE a_testas    t_sql   VARCHAR2 (a);    T_a     VARCHAR2 ();    T_b     VARCHAR2 ();    T_c     VARCHAR2 ();    T_d     VARCHAR2 (20); BEGIN    t_c   : = ' f ';    T_d   : = ' g ';            --This can be any SQL statement such as insert.    T_sql: = ' SELECT max (a), Max (b) from t1 WHERE C =: tempc OR c =: tempd ';        EXECUTE IMMEDIATE t_sql    into  t_a, T_b  -If no query assignment is required, do not write into the into XXX statement.    Using T_c, T_d  --If you do not need to use a variable, do not write the Using XXX statement.    ; END a_test;/

--a dynamic execution statement with a return cursor. CREATE OR REPLACE PROCEDURE a_test2 (    o_cursor out sys_refcursor) as    t_sql   VARCHAR2 (a);    T_a     VARCHAR2 ();    T_b     VARCHAR2 ();    T_c     VARCHAR2 ();    T_d     VARCHAR2 (20); BEGIN    t_c   : = ' f ';    T_d   : = ' g ';            --This can be any SQL statement such as insert.    T_sql: = ' SELECT * from t1 WHERE c =: tempc OR c =: tempd ';    OPEN  o_cursor for t_sql using    t_c, T_d   -Also if you do not need to use a variable, do not write the Using XXX statement.    END a_test2;/

SQL Server

CREATE PROCEDURE a_testas    DECLARE @t_sql   NVARCHAR (a);  --sql Server dynamic statements are declared as nvarchar types.    DECLARE @t_a     VARCHAR (a);    DECLARE @t_b     VARCHAR (a);    DECLARE @t_c     VARCHAR (a);    DECLARE @t_d     VARCHAR (20); BEGIN    SET @t_c = ' f ';    SET @t_d = ' g ';        --This can be any SQL statement such as insert.    SET @t_sql = ' SELECT @tempA = max (a), @tempB = max (b) from t1 WHERE C = @tempC OR c = @tempD ';        --Do not stitch the string    execute sp_executesql @t_sql behind execute sp_executesql        --if there are no arguments, the following declaration variables and incoming parameters do not need to be written.    --Declare the variable type and the entry and exit parameters. You must finish the line and not wrap it.    N ' @tempA varchar (), @tempB varchar out, @tempC varchar, @tempD varchar (20 ) '    --parameter value.  The order in which the variables are passed in is consistent with the order of the declared variables, @t_a out, @t_b out,    @t_c, @t_d    ; Endgo--sql server returns a cursor like a normal statement, assigning t_sql directly to select * from XXX.

Db2

CREATE PROCEDURE a_test (    v_c           VARCHAR) BEGIN    DECLARE t_sql VARCHAR2 (+);    DECLARE t_a   VARCHAR2 ();    DECLARE t_b   VARCHAR2 ();    DECLARE t_c   VARCHAR2 ();    DECLARE t_d   VARCHAR2 ();    --The t_stmt behind the for is consistent with the following prepare variable, and its type is statement.    DECLARE t_cur CURSOR for t_stmt;    SET T_c = ' f ';    SET t_d = ' g ';        --This can be any SQL statement such as insert.    SET t_sql = ' SELECT max (a), Max (b) from t1 WHERE C =: tempc OR c =: tempd ';        --I'm not here to find another way to replace it, I can only read it using cursors:    PREPARE t_stmt from T_sql;        OPEN t_cur    --If you do not need to use a variable, do not write the Using XXX statement.    USING T_c, t_d    ;        FETCH t_cur into T_a, t_b;    CLOSE t_cur; END

/* The dynamic statement returned with a cursor is the same as a normal statement, stating that the cursor of the return type is declared when the cursor is declared and needs to be inserted before the BEGIN keyword: dynamic RESULT sets 1LANGUAGE sql*/create PROCEDURE a_test2 (    V_c           VARCHAR (()) DYNAMIC RESULT sets 1LANGUAGE sqlbegin    DECLARE t_sql VARCHAR2 (a);    DECLARE t_a   VARCHAR2 ();    DECLARE t_b   VARCHAR2 ();    DECLARE t_c   VARCHAR2 ();    DECLARE t_d   VARCHAR2 ();    --The t_stmt behind the for is consistent with the following prepare variable, and its type is statement.    DECLARE t_cur CURSOR with RETURN for t_stmt;    SET T_c = ' f ';    SET t_d = ' g ';        --This can be any SQL statement such as insert.    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    --If you do not need to use a variable, do not write the Using XXX statement.    USING T_c, T_d    ; END

SQL Server, DB2, Oracle stored procedure dynamic SQL statement Example