Oracle stored procedure execution can enter SQL statements and return result sets

1. Create a Test table:

CREATE TABLE AAA
(
AA NVARCHAR2 (100),
BB NVARCHAR2 (100),
CC NVARCHAR2 (100),
DD NVARCHAR2 (100)
)

and import 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); --Parsing dynamic SQL statements;
Dbms_sql.define_column (V_cursor, 1, aa,100); --Defining columns
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 and returns 1 if the end is not reached.
Dbms_sql.column_value (v_cursor, 1, AA); --Writes the query result 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 1,1,1,1

3. Create a stored procedure where SQL is the input parameter and the system references the cursor as an output parameter, as follows:

Create or replace procedure MyProc
(
Mysqlval in Varchar2,
Rescur out Sys_refcursor
)
As
BEGIN
Open rescur for Mysqlval;
END;

4. Test the stored procedure

Sqlplus, do the following:

var r refcursor;
EXEC proc2 (' SELECT * from AAA ',: R);
Print R;

The output is normal.

Oracle stored procedure execution can enter SQL statements and return result sets