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

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