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