--demo1(游标基本使用方法)
declare
v_emp emp%rowtype;
--申明游标
cursor c_emp is
select * from emp;
begin
--打开游标
if
not c_emp%isopen then
open c_emp;
end
if;
--遍历游标
loop
fetch c_emp into v_emp;
--将下面这条exit语句放在end loop之上会使最后一条数据出现两次
exit
when c_emp%notfound;
dbms_output.put_line('no: ' || v_emp.empno);
dbms_output.put_line('name: ' || v_emp.ename);
dbms_output.put_line('job: ' || v_emp.job);
dbms_output.put_line('sal: ' || v_emp.sal);
dbms_output.put_line('*****************************');
end
loop;
--关闭游标
close c_emp;
end;
--游标属性
-- 显式游标属性 cursor_name%found cursor_name%notfound
-- cursor_name%isopen
cursor_name%rowcount
-- 隐式游标属性 sql%found sql%notfound sql%rowcount
--demo2(变量绑定)
declare
v_deptno emp.deptno%type;
v_emp emp%rowtype;
--变量绑定/申明游标
cursor c_emp is
select * from emp where deptno = v_deptno;
begin
v_deptno := 10;
--v_deptno := &deptno;
--打开游标
if
not c_emp%isopen then
open c_emp;
end
if;
--遍历游标
loop
fetch
c_emp into v_emp;
exit when c_emp%notfound;
dbms_output.put_line('no: ' || v_emp.empno);
dbms_output.put_line('name: ' || v_emp.ename);
dbms_output.put_line('job: ' || v_emp.job);
dbms_output.put_line('sal: ' || v_emp.sal);
dbms_output.put_line('*****************************');
end
loop;
--关闭游标
close c_emp;
end;
--demo3(参数化游标)
declare
v_emp emp%rowtype;
--变量绑定/申明游标
cursor c_emp(p_deptno emp.deptno%type) is
select * from emp where deptno = p_deptno;
begin
--打开游标
if
not c_emp%isopen then
open c_emp(10);
end if;
--遍历游标
loop
fetch c_emp into v_emp;
exit when c_emp%notfound;
dbms_output.put_line('no: ' || v_emp.empno);
dbms_output.put_line('name: ' || v_emp.ename);
dbms_output.put_line('job: ' || v_emp.job);
dbms_output.put_line('sal: ' || v_emp.sal);
dbms_output.put_line('*****************************');
end
loop;
--关闭游标
close c_emp;
end;
--游标检索循环
--demo1(loop)
declare
v_emp emp%rowtype;
--申明游标
cursor c_emp is
select * from emp where deptno = 20;
begin
--打开游标
if
not c_emp%isopen then
open c_emp;
end
if;
--遍历游标
loop
fetch c_emp into v_emp;
exit when c_emp%notfound;
dbms_output.put_line('no: ' || v_emp.empno);
dbms_output.put_line('name: ' || v_emp.ename);
dbms_output.put_line('job: ' || v_emp.job);
dbms_output.put_line('sal: ' || v_emp.sal);
dbms_output.put_line('*****************************');
end
loop;
--关闭游标
close c_emp;
end;
--demo2(while)
declare
v_emp emp%rowtype;
--申明游标
cursor c_emp is
select * from emp where deptno = 20;
begin
--打开游标
if
not c_emp%isopen then
open c_emp;
end
if;
--遍历游标
while c_emp%found loop
dbms_output.put_line('no: ' || v_emp.empno);
dbms_output.put_line('name: ' || v_emp.ename);
dbms_output.put_line('job: ' || v_emp.job);
dbms_output.put_line('sal: ' || v_emp.sal);
dbms_output.put_line('*****************************');
fetch c_emp into v_emp;
end
loop;
--关闭游标
close c_emp;
end;
--demo3-1(for)
declare
--申明游标
cursor c_emp is
select * from emp where deptno = 20;
begin
--遍历游标
for
v_emp in c_emp loop
dbms_output.put_line('no: ' || v_emp.empno);
dbms_output.put_line('name: ' || v_emp.ename);
dbms_output.put_line('job: ' || v_emp.job);
dbms_output.put_line('sal: ' || v_emp.sal);
dbms_output.put_line('*****************************');
end
loop;
end;
--demo3-2(for)
begin
--遍历游标
for
v_emp in (select *
from emp
where deptno = 20) loop
dbms_output.put_line('no: ' || v_emp.empno);
dbms_output.put_line('name: ' || v_emp.ename);
dbms_output.put_line('job: ' || v_emp.job);
dbms_output.put_line('sal: ' || v_emp.sal);
dbms_output.put_line('*****************************');
end
loop;
end;
--游标嵌套
declare
v_deptinfo dept%rowtype;
v_empinfo emp%rowtype;
type c_dept is ref cursor;
v_dept c_dept;
type c_emp is ref cursor;
v_emp c_emp;
begin
open v_dept for
select * from dept;
loop
fetch v_dept into v_deptinfo;
exit when v_dept%notfound;
dbms_output.put_line('deptno: ' || v_deptinfo.deptno
|| 'deptname: ' ||
v_deptinfo.dname);
open v_emp for
select * from emp where deptno = v_deptinfo.deptno;
loop
fetch v_emp into v_empinfo;
exit when v_emp%notfound;
dbms_output.put_line('empno: ' || v_empinfo.empno
|| 'ename: ' ||
v_empinfo.ename);
end loop;
close v_emp;
end
loop;
close v_dept;
end;
--select for update游标
--demo
declare
v_emp emp%rowtype;
cursor c_emp is
select * from emp
for update of sal,comm;
/* cursor c_emp is
select * from emp
for update;*/
begin
if
not c_emp%isopen then
open c_emp;
end
if;
loop
fetch c_emp into v_emp;
exit when c_emp%notfound;
update emp set sal = sal - 1000
where current of c_emp;
end
loop;
commit;
close c_emp;
end;
--动态SQL
--demo
create or replace procedure proc_execsql
is
sql_str varchar2(1000);
begin
sql_str := 'create table bak_emp as select * from emp';
execute immediate sql_str;
end;
begin
proc_execsql;
end;
grant create any table to scott;
--demo
create or replace procedure proc_execsql
is
sql_str varchar2(1000);
begin
for
v_table in (select table_name from user_tables) loop
sql_str := 'create table bak_'||v_table.table_name
|| ' as select * from ' ||
v_table.table_name;
execute immediate sql_str;
end
loop;
end;
begin
proc_execsql;
end;
--demo
create or replace procedure proc_createproc
(p_table varchar2)
is
p_sql_str varchar2(1000) := '';
tl_sql_str varchar2(1000) := '';
iv_sql_str varchar2(1000) := '';
sql_str varchar2(1000) := '';
begin
for
v_table in ( select COLUMN_NAME
from user_tab_columns
where TABLE_NAME = p_table)
loop
p_sql_str := p_sql_str || 'p_' || v_table.COLUMN_NAME ||' '
|| p_table ||'.' ||
v_table.COLUMN_NAME
|| '%type,';
tl_sql_str := tl_sql_str || v_table.COLUMN_NAME ||',';
iv_sql_str := iv_sql_str || 'p_'||v_table.COLUMN_NAME || ',';
end
loop;
p_sql_str := substr(p_sql_str,1,length(p_sql_str)-1);
tl_sql_str := substr(tl_sql_str,1,length(tl_sql_str)-1);
iv_sql_str
:= substr(iv_sql_str,1,length(iv_sql_str)-1);
sql_str := 'create or replace procedure proc_i_' || p_table
|| '(' || p_sql_str || ')'
|| 'is '
|| 'begin '
|| ' insert into ' || p_table || '(' || tl_sql_str
|| ')'
|| ' values(' || iv_sql_str || '); '
|| 'end;';
dbms_output.put_line(sql_str);
execute immediate sql_str;
end;
grant create any procedure to scott;
-- 
学海无涯