Oracle dynamic SQL returns a single result and a result set repost

Oracle dynamic SQL can be written in two ways: dbms_ SQL or execute immediate. The latter is recommended. The test procedure is as follows:

1. DDL and DML
/*** DDL ***/
Begin
Execute immediate 'drop table temp_1 ';
Execute immediate 'create table temp_1 (name varchar2 (8 ))';
End;
/*** DML ***/
Declare
V_1 varchar2 (8 );
V_2 varchar2 (10 );
STR varchar2 (50 );
Begin
V_1: = 'tester ';
V_2: = 'beijing ';
STR: = 'insert into test (name, address) values (: 1,: 2 )';
Execute immediate STR using v_1, V_2;
Commit;
End;

2. Return a single result
Declare
STR varchar2 (500 );
C_1 varchar2 (10 );
R_1 test % rowtype;
Begin
C_1: = 'tester ';
STR: = 'select * from test where name =: c Where rownum = 1 ';
Execute immediate STR into R_1 using C_1;
Dbms_output.put_line (r_1.name | r_1.address );
End;

3. Returned result set
Create or replace package pkg_test
/* Define the ref cursor type
The return type is not added. The return type is weak. dynamic SQL queries are allowed,
Otherwise, it is strongly typed and cannot be queried using dynamic SQL;
*/
Type myrctype is ref cursor;
-- Function declaration
Function get (intid number) return myrctype;
End pkg_test;
/
Create or replace package body pkg_test
-- Function body
Function get (intid number) return myrctype is
RC myrctype; -- defines the ref cursor variable
Sqlstr varchar2 (500 );
Begin
If intid = 0 then
-- The static test directly returns the result using the SELECT statement.
Open RC for select ID, name, sex, address, postcode, birthday from student;
Else
-- Assign a value to a dynamic SQL statement. Use w_id to declare that the variable is obtained from the outside.
Sqlstr: = 'select ID, name, sex, address, postcode, birthday from student where id =: w_id ';
-- Dynamic Test: return results using sqlstr strings and PASS Parameters Using using keywords
Open RC for sqlstr using intid;
End if;
Return RC;
End get;
End pkg_test;
/

This article from the csdn blog, reprinted please indicate the source: http://blog.csdn.net/junmail/archive/2007/01/29/1496768.aspxOracle dynamic SQL returns a single result and result set