-- Dynamic SQL and Dynamic Cursor declarecntnumber; beginexecuteimmediate
-- Dynamic SQL and Dynamic Cursor declare cnt number; begin execute immediate
-- Dynamic SQL and Dynamic Cursor
Declare
Cnt number;
Begin
Execute immediate
'Select count (*) from emp'
Into cnt;
Dbms_output.put_line (cnt );
End;
-- Create a stored procedure and execute the insert statement
Create or replace procedure myproc11
(Empid in varchar2, empname in varchar2)
Is
SQL _str varchar (200): = 'insert into emp values (: a,: B )';
Begin
Execute immediate SQL _str using empid, empname;
Commit;
End;
-- There are two parameters in this process. The first parameter indicates the query type: 0-precise query 1-fuzzy query;
-- The second parameter indicates the query condition.
Create or replace procedure myproc12
(Query_type in number, empname in varchar2)
Is
SQL _str varchar (200): = 'select * from emp ';
Cur pck1.myrefcur;
E emp % rowtype;
Begin
If query_type = 0 then
SQL _str: = SQL _str | 'where emp_name =: 1 ';
Else
SQL _str: = SQL _str | 'where emp_name like ''%'' |: 1 | ''% ''';
End if;
Dbms_output.put_line (SQL _str );
Open cur for SQL _str using empname;
Loop
Fetch cur into e;
Exit when cur % notfound;
Dbms_output.put_line (e. emp_id | ',' | e. emp_name );
End loop;
Close cur;
End;
-- Query the total number of records by name.
Create or replace procedure myproc20
(Ename in varchar2)
Is
Cnt number;
Begin
Execute immediate
'Select count (*) from emp where emp_name like '% ''|: n |'' % '''
Into cnt using ename;
Dbms_output.put_line (cnt );
End;
For more information about Oracle, see the Oracle topic page? Tid = 12
,