--demo1(游標(biāo)基本使用方法)
declare
v_emp emp%rowtype;
--申明游標(biāo)
cursor c_emp is
select * from emp;
begin
--打開(kāi)游標(biāo)
if
not c_emp%isopen then
open c_emp;
end
if;
--遍歷游標(biāo)
loop
fetch c_emp into v_emp;
--將下面這條exit語(yǔ)句放在end loop之上會(huì)使最后一條數(shù)據(jù)出現(xiàn)兩次
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;
--關(guān)閉游標(biāo)
close c_emp;
end;
--游標(biāo)屬性
-- 顯式游標(biāo)屬性 cursor_name%found cursor_name%notfound
-- cursor_name%isopen
cursor_name%rowcount
-- 隱式游標(biāo)屬性 sql%found sql%notfound sql%rowcount
--demo2(變量綁定)
declare
v_deptno emp.deptno%type;
v_emp emp%rowtype;
--變量綁定/申明游標(biāo)
cursor c_emp is
select * from emp where deptno = v_deptno;
begin
v_deptno := 10;
--v_deptno := &deptno;
--打開(kāi)游標(biāo)
if
not c_emp%isopen then
open c_emp;
end
if;
--遍歷游標(biāo)
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;
--關(guān)閉游標(biāo)
close c_emp;
end;
--demo3(參數(shù)化游標(biāo))
declare
v_emp emp%rowtype;
--變量綁定/申明游標(biāo)
cursor c_emp(p_deptno emp.deptno%type) is
select * from emp where deptno = p_deptno;
begin
--打開(kāi)游標(biāo)
if
not c_emp%isopen then
open c_emp(10);
end if;
--遍歷游標(biāo)
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;
--關(guān)閉游標(biāo)
close c_emp;
end;
--游標(biāo)檢索循環(huán)
--demo1(loop)
declare
v_emp emp%rowtype;
--申明游標(biāo)
cursor c_emp is
select * from emp where deptno = 20;
begin
--打開(kāi)游標(biāo)
if
not c_emp%isopen then
open c_emp;
end
if;
--遍歷游標(biāo)
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;
--關(guān)閉游標(biāo)
close c_emp;
end;
--demo2(while)
declare
v_emp emp%rowtype;
--申明游標(biāo)
cursor c_emp is
select * from emp where deptno = 20;
begin
--打開(kāi)游標(biāo)
if
not c_emp%isopen then
open c_emp;
end
if;
--遍歷游標(biāo)
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;
--關(guān)閉游標(biāo)
close c_emp;
end;
--demo3-1(for)
declare
--申明游標(biāo)
cursor c_emp is
select * from emp where deptno = 20;
begin
--遍歷游標(biāo)
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
--遍歷游標(biāo)
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;
--游標(biāo)嵌套
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游標(biāo)
--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;
--動(dòng)態(tài)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;
-- 
學(xué)海無(wú)涯