Oracle學習筆記(十)

標籤：更改   tput   rollback   執行   read   交易隔離等級   resid   返回   集合   

游標（遊標）概念引入
就是一個結果集(查詢或者其他動作返回的結果是多個時使用)
定義一個游標
cursor c1 is select ename from emp；

從游標中取值
開啟游標：
--open c1;(開啟游標執行查詢)
關閉游標:
--close c1;(關閉遊標釋放資源)
取一行游標的值:
--fetch c1 into pename;(取一行到變數中)

fetch的作用:
--把當前指標指向的記錄返回
--將指標指向下一條記錄

使用游標查詢員工姓名和工資,並列印
declare
--定義一個游標
cursor cemp is select ename,sal from emp;
--為游標定義對應的變數
pename emp.ename%type;
psal emp.sal%type;
begin
--開啟游標
open cemp;

loop
--取一條記錄
fetch cemp into pename,psal;

--思考:1、迴圈什麼時候退出? 2、fetch不一定能取到記錄
--exit when 沒有取到記錄;

exit when cemp%notfound;

--列印
dbms_output.put_line(pename||‘的薪水是‘||psal);

end loop;
--關閉游標
close cemp;
end;

1、游標的屬性:
%found 取到值就是found
%notfound 取不到是這個

樣本:給員工漲工資(根據職位漲工資)
總裁漲1000
經理漲800
其他員工漲400
更改表中的job為empjob,

--漲工資之前的薪水
select ename,job,sal from emp;

set serveroutput on
declare
--定義游標代表給哪些員工漲工資
--alter table ‘SCOTT‘ ‘EMP‘ rename column ‘JOB‘ to empjob
--定義一個游標
cursor cemp is select empno,empjob from emp;
--為游標定義對應的變數
pempno emp.empno%type;
pjob emp.empjob%type;
begin
rollback;
--開啟游標
open cemp;

loop
--取一條記錄
fetch cemp into pempno,pjob;
exit when cemp%notfound;

--判斷員工的職位
if pjob = ‘PRESIDENT‘ then update emp set sal=sal+1000 where empno=pempno;
elsif pjob=‘MANAGER‘ then update emp set sal=sal+800 where empno=pempno;
else update emp set sal=sal+400 where empno=pempno;
end if;
end loop;
--關閉游標
close cemp;

--對於oracle,預設的交易隔離等級是read committee
--事務的ACID(原子性/一致性/隔離性/持久性)
commit;
DBMS_OUTPUT.PUT_LINE(‘漲工資完成‘);
end;

1、游標的屬性;
%found(取到值為true)
%notfound(取不到值為true)
%isopen(判斷游標是否開啟，因為oracle資料庫預設一個會話中開啟的游標數是有限制的300個)
%rowcount(影響的行數，即已經被提取的行數，並非集合中的總行數)。

案例1:%isopen用法

set serveroutput on
declare
--定義一個游標
cursor cemp is select empno,empjob from emp;
--為游標定義對應的變數
pempno emp.empno%type;
pjob emp.empjob%type;
begin
--開啟游標
open cemp;

if cemp%isopen then
DBMS_OUTPUT.PUT_LINE(‘游標已經開啟‘);
else
DBMS_OUTPUT.PUT_LINE(‘游標沒有開啟‘);

end if;
--關閉游標
close cemp;
end;

案例2:%rowcount用法

set serveroutput on
declare
--定義一個游標
cursor cemp is select empno,empjob from emp;
--為游標定義對應的變數
pempno emp.empno%type;
pjob emp.empjob%type;
begin
--開啟游標
open cemp;
loop
--取出一條記錄
fetch cemp into pempno,pjob;
exit when cemp%notfound;

--列印rowcount的值
DBMS_OUTPUT.PUT_LINE(‘rowcount‘||cemp%rowcount);

end loop;
--關閉游標
close cemp;
end;

2、游標的限制，預設情況下，orcle資料庫只允許在同一個會話中，開啟300個游標
--切換到管理員
conn sys/1234 as sysdba
--查看參數
show parameter cursor

修改游標數量的限制
alter system set open_cursors=400 scope=both;

scope的取值有三個
both(後兩個同時更改),memory,spfile(資料庫需要重啟)

3、帶參數的游標
--查詢某個部門中員工的姓名
set serveroutput on
declare
---定義帶參數的游標
cursor cemp(dno number) is select ename from emp where deptno=dno;
pename emp.ename%type;

begin
--開啟游標
open cemp(10);
loop
--取出一條記錄
fetch cemp into pename;
exit when cemp%notfound;

DBMS_OUTPUT.PUT_LINE(pename);

end loop;
--關閉游標
close cemp;
end;

***********游標是SQL中代替集合(數組)使用的參數.**********

Oracle學習筆記(十)