遊标
聲明 不占記憶體
打開 申請記憶體 多行多列
擷取 每次取一行，
關閉
隐式遊标的屬性:
SQL%ROWCOUNT 成功操作的行的數量
SQL%FOUND 發現複合條件的行傳回TRUE
SQL%NOTFOUND 沒有發現複合條件的行回TRUE
SQL%ISOPEN 遊标打開狀态(boolean)
練習 12:列印隐式遊标屬性
declare
v_count number;
begin
select count(*) into v_count from scott.emp;
dbms_output.put_line(chr(10)||'select return '||sql%rowcount||' rows!');
end;
begin
delete emp;
dbms_output.put_line(chr(10)||sql%rowcount||' rows deleted!');
--rollback;
end;
/
顯式遊标使用流程:
1.聲明 declare
cursor c_name is subquery;
2.打開 open
3.擷取 fetch
4.關閉 close
1.簡單遊标的使用
遊标是在open時使用記憶體的.擷取一次,記憶體中就少一行.
declare
cursor c1 is select ename,sal from scott.emp;
v_ename scott.emp.ename%type;
v_sal scott.emp.sal%type;
begin
open c1 ;
loop
fetch c1 into v_ename,v_sal;
dbms_output.put_line(v_ename||' '||v_sal);
exit when c1%rowcount>5;
end loop;
close c1;
end;
/
declare
cursor c1
is
select employee_id,salary from employees;
v_id employees.employee_id%type;
v_sal employees.salary%type;
begin
open c1;
loop
if c1%notfound then
exit;
end if;
fetch c1 into v_id,v_sal;
dbms_output.put_line('Id is : '||v_id||' '||'salary is : '||v_sal);
end loop;
close c1;
end;
2.使用遊标取表中的所有行所有列
declare
cursor c1 is select * from scott.dept;
v_deptno dept.deptno%type;
v_dname dept.dname%type;
v_loc dept.loc%type;
begin
open c1;
loop
fetch c1 into v_deptno,v_dname,v_loc;
exit when C1%NOTFOUND;
dbms_output.put_line(v_deptno||' '||v_dname||' '||v_loc);
end loop;
close c1;
end;
/
3.遊标for循環:使用for循環來代替loop循環
for循環中的變量根據in關鍵字後面的内容而決定變量的類型
如果in後面是數字 則變量是number類型标量變量
如果in後面是遊标 則變量是record類型複合變量
declare
cursor c1 is select ename,sal from scott.emp;
begin
for r1 in c1 loop
exit when c1%rowcount>5;
dbms_output.put_line(r1.ename||' '||r1.sal);
end loop;
end;
/
4. 使用遊标for循環 可以省略遊标的聲明
但這種遊标for循環不能使用隐式遊标屬性控制 要自己添加計數器
declare
v_num number := 0;
begin
for r1 in (select ename,sal from scott.emp) loop
-- dbms_output.put_line(SQL%rowcount);
v_num := v_num + 1;
exit when v_num > 5;
dbms_output.put_line(r1.ename||' '||r1.sal);
end loop;
end;
/
set serveroutput on
declare
v_number NUMBER:=1;
begin
for r1 in (select * from hr.employees) loop
-- dbms_output.put_line(sql%rowcount);
if v_number > 5 then
exit;
end if;
dbms_output.put_line(r1.last_name);
v_number := v_number+1;
end loop;
end;
/
5. 參數遊标
declare
cursor c1(v_deptno scott.emp.deptno%type)
is
select deptno,ename,sal from scott.emp where deptno=v_deptno;
begin
for r1 in c1 (&deptno) loop
dbms_output.put_line(r1.deptno||' '||r1.ename||' '||r1.sal);
end loop;
end;
/
declare
cursor c1(deptno hr.employees.department_id%type)
is
select last_name,department_id,salary from hr.employees where department_id=deptno;
begin
for emp_rec in c1(&did) loop
dbms_output.put_line('Name: '||emp_rec.last_name||' -- '||'Deptno: '||emp_rec.department_id||' -- '||
'Salary: '||emp_rec.salary);
end loop;
end;
/
練習 1: 基本loop循環+顯式遊标的使用
DECLARE
v_empno employees.employee_id%TYPE;
v_ename employees.last_name%TYPE;
CURSOR emp_cursor IS SELECT employee_id, last_name FROM employees; --聲明
BEGIN
OPEN emp_cursor; --打開
LOOP
FETCH emp_cursor INTO v_empno, v_ename; --擷取
exit when emp_cursor%rowcount>20;
DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_empno)||' '|| v_ename);
END LOOP;
CLOSE emp_cursor; --關閉
END ;
/
練習 2: for循環+顯式遊标的使用
DECLARE
v_empno employees.employee_id%TYPE;
v_ename employees.last_name%TYPE;
CURSOR emp_cursor IS SELECT employee_id, last_name FROM employees;
BEGIN
OPEN emp_cursor;
FOR i IN 1..10 LOOP
FETCH emp_cursor INTO v_empno, v_ename;
DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_empno)||' '|| v_ename);
END LOOP;
CLOSE emp_cursor;
END ;
/
遊标for循環:
declare
cursor emp_cursor is select rownum,employee_id,last_name from employees; --聲明
begin
for emp_record in emp_cursor loop --隐式打開隐式擷取
exit when emp_cursor%rowcount>23;
dbms_output.put_line(emp_record.rownum||' '||
emp_record.employee_id||' '||
emp_record.last_name);
end loop; --隐式關閉
end;
/
省略遊标定義:
begin
for r in (select last_name from employees) loop
dbms_output.put_line(r.last_name);
end loop;
end;
/
進階顯式遊标(帶參數的遊标):
練習 1:通過傳入不同的參數使打開遊标時取到不同的結果集
declare
cursor c1 (p_deptno number,p_job varchar2)
is
select empno,ename
from emp
where deptno=p_deptno
and job=p_job;
begin
Dbms_output.put_line('first fetch cursor!');
for r_c1 in c1(10,'MANAGER') loop --open cursor時傳入不同的實際參數得到不同的遊标上下文!
Dbms_output.put_line(r_c1.empno||' '||r_c1.ename);
end loop;
Dbms_output.put_line('second fetch cursor!');
for r_c1 in c1(20,'MANAGER') loop
Dbms_output.put_line(r_c1.empno||' '||r_c1.ename);
end loop;
Dbms_output.put_line('third fetch cursor!');
for r_c1 in c1(30,'MANAGER') loop
Dbms_output.put_line(r_c1.empno||' '||r_c1.ename);
end loop;
end;
/
練習:擷取每個部門前兩個雇員的資訊
擷取10部門前兩個人的資訊
declare
cursor c1 is select * from scott.emp
where deptno=10;
begin
for r1 in c1 loop
exit when c1%rowcount=3 or c1%notfound;
dbms_output.put_line(r1.ename||' '||r1.deptno);
end loop;
end;
/
使用替代變量取指定部門的前兩個人的資訊
declare
cursor c1 is select * from scott.emp
where deptno=&p_deptno;
begin
for r1 in c1 loop
exit when c1%rowcount=3 or c1%notfound;
dbms_output.put_line(r1.ename||' '||r1.deptno);
end loop;
end;
/
顯示所有部門的工資最高的前兩位員工的資訊；
declare
cursor dept
is
select distinct department_id from employees order by department_id;
cursor emp(deptno number)
is
select employee_id,department_id,last_name，salary from employees
where department_id = deptno order by salary;
begin
for d in dept loop
for e in emp(d.department_id) loop
exit when emp%rowcount > 2 or emp%notfound;
dbms_output.put_line(e.employee_id||' : ' ||e.department_id ||' : '||e.last_name||' : ' || e.salary);
end loop;
end loop;
end;
/
使用進階遊标代替替代變量
declare
cursor c1(p_deptno number) is select * from scott.emp
where deptno=p_deptno;
begin
for r1 in c1(10) loop
exit when c1%rowcount=3 or c1%notfound;
dbms_output.put_line(r1.ename||' '||r1.deptno);
end loop;
for r1 in c1(20) loop
exit when c1%rowcount=3 or c1%notfound;
dbms_output.put_line(r1.ename||' '||r1.deptno);
end loop;
for r1 in c1(30) loop
exit when c1%rowcount=3 or c1%notfound;
dbms_output.put_line(r1.ename||' '||r1.deptno);
end loop;
end;
/
使用循環嵌套簡化上面的代碼
declare
cursor c2 is select distinct deptno from scott.emp;
cursor c1(p_deptno number) is
select * from scott.emp
where deptno=p_deptno;
begin
for r2 in c2 loop
for r1 in c1(r2.deptno) loop
exit when c1%rowcount=3 or c1%notfound;
dbms_output.put_line(r1.ename||' '||r1.deptno);
end loop;
end loop;
end;
/
練習 2:将每個部門工資小于2000的職員工資漲10%
declare
cursor c1 is select deptno from scott.dept;
cursor c2 (p_deptno number,p_job varchar2)
is
select empno,ename,sal
from emp
where deptno=p_deptno
and job=p_job
for update of sal;
begin
for r_c1 in c1 loop
dbms_output.put_line('第'||c1%rowcount||'次擷取遊标c1' || '修改'||r_c1.deptno||'部門職員的工資');
for r_c2 in c2(r_c1.deptno,'CLERK') loop
if r_c2.sal<2000 then
update scott.emp set sal=sal*1.1
where current of c2;
end if;
end loop;
end loop;
end;
/
declare
cursor d1 is select distinct department_id from hr.e;
cursor e1(deptno hr.e.department_id%type)
is
select employee_id,salary from hr.e
where department_id=deptno;
begin
for d_info in d1 loop
for e_info in e1(d_info.department_id) loop
if e_info.salary
update hr.e set salary=salary*1.1 where employee_id=e_info.employee_id;
end if;
end loop;
end loop;
end;
動态遊标：ref遊标
強類型的動态遊标
弱類型的動态遊标
強類型的動态遊标的樣例：
declare
type emp_record_type is record(
empno employees.employee_id%type,
ename employees.last_name%type);
type emp_cursor is REF CURSOR return emp_record_type;
v_employee emp_record_type;
v_empno employees.salary%type := &no;
v_emp_cursor emp_cursor;
begin
if v_empno > 150 then
open v_emp_cursor for
select employee_id,last_name from employees
where employee_id > 150;
else
open v_emp_cursor for
select employee_id,last_name from employees
where employee_id <= 150;
end if;
fetch v_emp_cursor into v_employee;
while v_emp_cursor%found loop
dbms_output.put_line(v_employee.empno||' : '||v_employee.ename);
fetch v_emp_cursor into v_employee;
end loop;
close v_emp_cursor;
end;
弱類型的動态遊标的樣例：
declare
type emp_record_type is record(
empno number,
ename varchar2(100));
type emp_cursor is REF CURSOR;
v_employee emp_record_type;
v_no employees.salary%type := &no;
v_emp_cursor emp_cursor;
v_dname varchar2(50);
begin
if v_no > 150 then
open v_emp_cursor for
select employee_id,last_name from employees
where employee_id > 150;
else
open v_emp_cursor for
select department_name from departments
where department_id <= 150;
end if;
if v_no > 150 then
fetch v_emp_cursor into v_employee;
while v_emp_cursor%found loop
dbms_output.put_line(v_employee.empno||' : '||v_employee.ename);
fetch v_emp_cursor into v_employee;
end loop;
close v_emp_cursor;
else
fetch v_emp_cursor into v_dname;
while v_emp_cursor%found loop
dbms_output.put_line(v_dname);
fetch v_emp_cursor into v_dname;
end loop;
close v_emp_cursor;
end if;
end;
有定義遊标結構(用return定義)的就是強類型 使用時必須按定義去取值 取值數量與定義比對
弱類型沒有定義遊标結構 随SQL傳回結果而建構遊标的結構 特點是靈活 完全依賴于SQL 容易出錯 SQL錯遊标就錯
參照類型(ref cursor 程式間傳遞結果集)
create or replace package ref_package
as
TYPE emp_record_type IS RECORD
(ename VARCHAR2(25),
job VARCHAR2(10),
sal NUMBER(7,2));
TYPE weak_ref_cursor IS REF CURSOR;--弱類型，不規定傳回值
TYPE strong_ref_cursor IS REF CURSOR return emp%rowtype;--強類型，規定傳回值
TYPE strong_ref2_cursor IS REF CURSOR return emp_record_type;--強類型，規定傳回值
end ref_package;
/
弱類型ref測試: 沒有指定遊标結構
create or replace procedure test_ref_weak
(p_deptno emp.deptno%type, p_cursor out ref_package.weak_ref_cursor)
is
begin
case p_deptno
when 10 then
open p_cursor for
select empno,ename,sal,deptno
from emp where deptno=p_deptno;
when 20 then
open p_cursor for
select *
from emp where deptno=p_deptno;
end case;
end;
/
var c refcursor
exec test_ref_weak(10,:c); --傳入不同形式參數，走不同分支，傳回不同結果集！
print c
exec test_ref_weak(20,:c);
print c
*oracle 9i 中定義了系統弱遊标類型 sys_refcursor 可以和任何查詢相關聯
create or replace procedure test_p
( p_deptno number, p_cursor out sys_refcursor)
is
begin
open p_cursor for
select *
from emp
where deptno = p_deptno;
end test_p;
/
create or replace function getemp
return sys_refcursor
as
emp_cursor sys_refcursor;
begin
open emp_cursor for select * from scott.emp;
return emp_cursor;
end;
/
select getemp from dual;
強類型ref測試:查詢結構必須符合遊标傳回值結構，否則報錯：
PLS-00382: expression is of wrong type
create or replace procedure test_ref_strong
(p_deptno emp.deptno%type, p_cursor out ref_package.strong_ref_cursor)
is
begin
open p_cursor for
select *
from emp where deptno=p_deptno;
end test_ref_strong;
/
var c refcursor
exec test_ref_strong(10,:c);
create or replace procedure test_call
is
c_cursor ref_package.strong_ref_cursor;
r_emp emp%rowtype;
begin
test_ref_strong(10,c_cursor);
loop
fetch c_cursor into r_emp;
exit when c_cursor%notfound;
dbms_output.put_line(r_emp.ename);
end loop;
close c_cursor;
end test_call;
/
exec test_call;
強類型ref測試:
create or replace procedure test_ref2_strong
(p_deptno emp.deptno%type, p_cursor out ref_package.strong_ref2_cursor)
is
begin
open p_cursor for
select ename,job,sal
from emp where deptno=p_deptno;
end test_ref2_strong;
/
var c refcursor
exec test_ref2_strong(10,:c);