Oracle stored procedure execution can input SQL statements and return result sets, oraclesql

Oracle stored procedure execution can input SQL statements and return result sets, oraclesql

1. Create a test table:

Create table AAA
(
Aa NVARCHAR2 (100 ),
Bb NVARCHAR2 (100 ),
Cc NVARCHAR2 (100 ),
Dd NVARCHAR2 (100)
)

And import the test data insert into aaa values ('1', '1', '1', '1 ');

2. Test Feasibility:

DECLARE
V_cursor NUMBER;
V_stat NUMBER;
Aa VARCHAR (100 );
Bb VARCHAR (100 );
Cc VARCHAR (100 );
Dd VARCHAR (100 );
V_ SQL VARCHAR (200 );
BEGIN
V_ SQL: = 'select * from aaa ';
V_cursor: = dbms_ SQL .open_cursor; -- open the cursor;
Dbms_ SQL .parse (v_cursor, v_ SQL, dbms_ SQL .native); -- parse dynamic SQL statements;
Dbms_ SQL .define_column (v_cursor, 1, aa, 100); -- Define a column
Dbms_ SQL .define_column (v_cursor, 2, bb, 100 );
Dbms_ SQL .define_column (v_cursor, 3, cc, 100 );
Dbms_ SQL .define_column (v_cursor, 4, dd, 100 );
V_stat: = dbms_ SQL .execute (v_cursor); -- execute dynamic SQL statements.
LOOP
Exit when dbms_ SQL .fetch_rows (v_cursor) <= 0; -- fetch_rows moves the cursor in the result set. If the cursor does not reach the end, 1 is returned.
Dbms_ SQL .column_value (v_cursor, 1, aa); -- write the query results of the current row to the column defined above.
Dbms_ SQL .column_value (v_cursor, 2, bb );
Dbms_ SQL .column_value (v_cursor, 3, cc );
Dbms_ SQL .column_value (v_cursor, 4, dd );
Dbms_output.put_line (aa | ';' | bb | ';' | cc | ';' | dd );
End loop;
Dbms_ SQL .close_cursor (v_cursor); -- close the cursor.
END;

Output

3. Create a stored procedure. SQL is the input parameter, and the system reference cursor is the output parameter, as shown below:

Create or replace procedure myproc
(
Mysqlval in varchar2,
Rescur out sys_refcursor
)
As
BEGIN
Open rescur for mysqlval;
END;

4. Test the Stored Procedure

In sqlplus, execute the following:

Var r refcursor;
Exec proc2 ('select * from aaa',: r );
Print r;

The output is normal.