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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.