Oracle dynamic SQL and Dynamic Cursor examples

-- Dynamic SQL and Dynamic Cursor
Declare cnt number; begin execute immediate 'select count (*) from emp' into cnt; dbms_output.put_line (cnt); end; -- execute the insert statement create or replace procedure myproc11 (empid in varchar2, empname in varchar2) is SQL _str varchar (200): = 'insert into emp values (: ,: b) '; begin execute immediate SQL _str using empid, empname; commit; end; -- this process has two parameters. The first parameter indicates the query type: 0-exact 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; Author: beijishiqidu