SQL server, db2, oracle Stored Procedure dynamic SQL statement example, db2oracle

Source: Internet
Author: User
Tags sql using

SQL server, db2, oracle Stored Procedure dynamic SQL statement example, db2oracle

Oracle

Create or replace procedure a_testAS 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'; -- any SQL statements such as insert can be used here. 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 you do not need to query the value assignment, you do not need to write the INTO XXX statement here. USING t_c, t_d -- if you do not need to use variables, you do not need to write the using xxx statement .; END a_test ;/

-- A dynamic execution statement with a returned cursor. 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'; -- any SQL statement such as insert can be used here. t_ SQL: = 'select * FROM t1 WHERE c =: tempC OR c =: tempd'; OPEN o_cursor FOR t_ SQL USING t_c, t_d -- if you do not need to use variables, you do not need to write the using xxx statement .; END a_test2 ;/


SQL Server
Create procedure a_testAS DECLARE @ t_ SQL NVARCHAR (2000); -- the dynamic SQL server statement must be declared as 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'; -- any SQL statements such as insert can be used here. SET @ t_ SQL = 'select @ tempA = MAX (a), @ tempB = MAX (B) FROM t1 WHERE c = @ tempC OR c = @ tempd '; -- the string EXECUTE SP_EXECUTESQL cannot be spliced behind EXECUTE SP_EXECUTESQL @ t_ SQL -- if no parameter is included, the following declarative variables and input parameters do not need to be written. -- declare the variable type and input and output parameters. A line must be written, and a line break is not allowed ., n' @ tempA VARCHAR (20) OUT, @ tempB VARCHAR (20) OUT, @ tempC VARCHAR (20), @ tempD VARCHAR (20) '-- parameter value. the order of input variables must be the same as that of the declarative variables. @ t_a OUT, @ t_ B OUT, @ t_c, @ t_d; ENDGO -- SQL server returns the same cursor as the normal statement, directly assign t_ SQL to select * from XXX.

Db2

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); -- The t_stmt behind FOR must be consistent with the variable after prepare. Its type is statement. DECLARE t_cur cursor for t_stmt; SET t_c = 'F'; SET t_d = 'G'; -- any SQL statements such as insert can be used here. SET t_ SQL = 'select MAX (a), MAX (B) FROM t1 WHERE c =: tempC OR c =: tempd'; -- I have not found any other alternative here, only the cursor can be used to read: PREPARE t_stmt FROM t_ SQL; OPEN t_cur -- if you do not need to use variables, you do not need to 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 the normal statement. When declaring a cursor, you must declare a return type cursor and insert it before the begin Keyword: dynamic result sets 1 language SQL */create procedure a_test2 (v_c VARCHAR (20) dynamic result sets 1 language sqlbegin 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); -- t_stmt after FOR must be consistent with the variable after prepare below, its type is statement. DECLARE t_cur cursor with return for t_stmt; SET t_c = 'F'; SET t_d = 'G'; -- any SQL statement such as insert can be used here. 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 variables, you do not need to write the using xxx statement. USING t_c, t_d; END


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.