Oracle深入學習技巧：遊标使用全解

這個文檔幾乎包含了oracle遊标使用的方方面面，全部通過了測試

　　-- 聲明遊标;CURSOR cursor_name IS select_statement

        --For 循環遊标

--(1)定義遊标

--(2)定義遊标變量

--(3)使用for循環來使用這個遊标

declare

--類型定義

cursor c_job

is

select empno,ename,job,sal

from emp

where job='MANAGER';

--定義一個遊标變量v_cinfo c_emp%ROWTYPE ，該類型為遊标c_emp中的一行資料類型

c_row c_job%rowtype;

begin

for c_row in c_job loop

dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);

end loop;

end;

--Fetch遊标

--使用的時候必須要明确的打開和關閉

--定義一個遊标變量

open c_job;

loop

--提取一行資料到c_row

fetch c_job into c_row;

--判讀是否提取到值，沒取到值就退出

--取到值c_job%notfound 是false

--取不到值c_job%notfound 是true

exit when c_job%notfound;

--關閉遊标

close c_job;

--1：任意執行一個update操作，用隐式遊标sql的屬性%found,%notfound,%rowcount,%isopen觀察update語句的執行情況。

update emp set ENAME='ALEARK' WHERE EMPNO=7469;

if sql%isopen then

dbms_output.put_line('Openging');

else

dbms_output.put_line('closing');

end if;

if sql%found then

dbms_output.put_line('遊标指向了有效行');--判斷遊标是否指向有效行

dbms_output.put_line('Sorry');

if sql%notfound then

dbms_output.put_line('Also Sorry');

dbms_output.put_line('Haha');

dbms_output.put_line(sql%rowcount);

exception

when no_data_found then

dbms_output.put_line('Sorry No data');

when too_many_rows then

dbms_output.put_line('Too Many rows');

empNumber emp.EMPNO%TYPE;

empName emp.ENAME%TYPE;

dbms_output.put_line('Cursor is opinging');

dbms_output.put_line('Cursor is Close');

dbms_output.put_line('No Value');

dbms_output.put_line(empNumber);

dbms_output.put_line('-------------');

select EMPNO,ENAME into empNumber,empName from emp where EMPNO=7499;

dbms_output.put_line('Cursor is Closing');

dbms_output.put_line('too many rows');

--2,使用遊标和loop循環來顯示所有部門的名稱

--遊标聲明

cursor csr_dept

--select語句

select DNAME

from Depth;

--指定行指針,這句話應該是指定和csr_dept行類型相同的變量

row_dept csr_dept%rowtype;

--for循環

for row_dept in csr_dept loop

dbms_output.put_line('部門名稱:'||row_dept.DNAME);

--3,使用遊标和while循環來顯示所有部門的的地理位置(用%found屬性)

cursor csr_TestWhile

select LOC

--指定行指針

row_loc csr_TestWhile%rowtype;

--打開遊标

open csr_TestWhile;

--給第一行喂資料

fetch csr_TestWhile into row_loc;

--測試是否有資料，并執行循環

while csr_TestWhile%found loop

dbms_output.put_line('部門地點：'||row_loc.LOC);

--給下一行喂資料

close csr_TestWhile;

select * from emp

--4,接收使用者輸入的部門編号，用for循環和遊标，列印出此部門的所有雇員的所有資訊(使用循環遊标)

--CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;

--定義參數的文法如下:Parameter_name [IN] data_type[{:=|DEFAULT} value]

CURSOR

c_dept(p_deptNo number)

select * from emp where emp.depno=p_deptNo;

r_emp emp%rowtype;

for r_emp in c_dept(20) loop

dbms_output.put_line('員工号：'||r_emp.EMPNO||'員工名：'||r_emp.ENAME||'工資：'||r_emp.SAL);

--5：向遊标傳遞一個工種，顯示此工種的所有雇員的所有資訊(使用參數遊标)

cursor

c_job(p_job nvarchar2)

select * from emp where JOB=p_job;

r_job emp%rowtype;

for r_job in c_job('CLERK') loop

dbms_output.put_line('員工号'||r_job.EMPNO||' '||'員工姓名'||r_job.ENAME);

SELECT * FROM EMP

--6：用更新遊标來為雇員加傭金：(用if實作,建立一個與emp表一摸一樣的emp1表，對emp1表進行修改操作),并将更新前後的資料輸出出來

--http://zheng12tian.iteye.com/blog/815770

create table emp1 as select * from emp;

csr_Update

select * from emp1 for update OF SAL;

empInfo csr_Update%rowtype;

saleInfo emp1.SAL%TYPE;

FOR empInfo IN csr_Update LOOP

IF empInfo.SAL<1500 THEN

saleInfo:=empInfo.SAL*1.2;

elsif empInfo.SAL<2000 THEN

saleInfo:=empInfo.SAL*1.5;

elsif empInfo.SAL<3000 THEN

saleInfo:=empInfo.SAL*2;

END IF;

UPDATE emp1 SET SAL=saleInfo WHERE CURRENT OF csr_Update;

END LOOP;

END;

--7:編寫一個PL/SQL程式塊，對名字以‘A’或‘S’開始的所有雇員按他們的基本薪水(sal)的10%給他們加薪(對emp1表進行修改操作)

csr_AddSal

select * from emp1 where ENAME LIKE 'A%' OR ENAME LIKE 'S%' for update OF SAL;

r_AddSal csr_AddSal%rowtype;

for r_AddSal in csr_AddSal loop

dbms_output.put_line(r_AddSal.ENAME||'原來的工資:'||r_AddSal.SAL);

saleInfo:=r_AddSal.SAL*1.1;

UPDATE emp1 SET SAL=saleInfo WHERE CURRENT OF csr_AddSal;

--8：編寫一個PL/SQL程式塊，對所有的salesman增加傭金(comm)500

csr_AddComm(p_job nvarchar2)

select * from emp1 where JOB=p_job FOR UPDATE OF COMM;

r_AddComm emp1%rowtype;

commInfo emp1.comm%type;

for r_AddComm in csr_AddComm('SALESMAN') LOOP

commInfo:=r_AddComm.COMM+500;

UPDATE EMP1 SET COMM=commInfo where CURRENT OF csr_AddComm;

--9：編寫一個PL/SQL程式塊，以提升2個資格最老的職員為MANAGER(工作時間越長，資格越老)

--(提示：可以定義一個變量作為計數器控制遊标隻提取兩條資料;也可以在聲明遊标的時候把雇員中資格最老的兩個人查出來放到遊标中。)

cursor crs_testComput

select * from emp1 order by HIREDATE desc;

--計數器

top_two number:=2;

r_testComput crs_testComput%rowtype;

open crs_testComput;

FETCH crs_testComput INTO r_testComput;

while top_two>0 loop

dbms_output.put_line('員工姓名：'||r_testComput.ENAME||' 工作時間：'||r_testComput.HIREDATE);

--計速器減一

top_two:=top_two-1;

close crs_testComput;

--10：編寫一個PL/SQL程式塊，對所有雇員按他們的基本薪水(sal)的20%為他們加薪，

--如果增加的薪水大于300就取消加薪(對emp1表進行修改操作，并将更新前後的資料輸出出來)

crs_UpadateSal

select * from emp1 for update of SAL;

r_UpdateSal crs_UpadateSal%rowtype;

salAdd emp1.sal%type;

salInfo emp1.sal%type;

for r_UpdateSal in crs_UpadateSal loop

salAdd:= r_UpdateSal.SAL*0.2;

if salAdd>300 then

salInfo:=r_UpdateSal.SAL;

dbms_output.put_line(r_UpdateSal.ENAME||': 加薪失敗。'||'薪水維持在：'||r_UpdateSal.SAL);

salInfo:=r_UpdateSal.SAL+salAdd;

dbms_output.put_line(r_UpdateSal.ENAME||': 加薪成功.'||'薪水變為：'||salInfo);

update emp1 set SAL=salInfo where current of crs_UpadateSal;

--11:将每位員工工作了多少年零多少月零多少天輸出出來

--近似

--CEIL(n)函數：取大于等于數值n的最小整數

--FLOOR(n)函數：取小于等于數值n的最大整數

--truc的用法 http://publish.it168.com/2005/1028/20051028034101.shtml

crs_WorkDay

select ENAME,HIREDATE, trunc(months_between(sysdate, hiredate) / 12) AS SPANDYEARS,

trunc(mod(months_between(sysdate, hiredate), 12)) AS months,

trunc(mod(mod(sysdate - hiredate, 365), 12)) as days

from emp1;

r_WorkDay crs_WorkDay%rowtype;

for r_WorkDay in crs_WorkDay loop

dbms_output.put_line(r_WorkDay.ENAME||'已經工作了'||r_WorkDay.SPANDYEARS||'年,零'||r_WorkDay.months||'月,零'||r_WorkDay.days||'天');

--12:輸入部門編号，按照下列加薪比例執行(用CASE實作，建立一個emp1表，修改emp1表的資料),并将更新前後的資料輸出出來

-- deptno raise(%)

-- 10 5%

-- 20 10%

-- 30 15%

-- 40 20%

-- 加薪比例以現有的sal為标準

--CASE expr WHEN comparison_expr THEN return_expr

--[, WHEN comparison_expr THEN return_expr]... [ELSE else_expr] END

crs_caseTest

r_caseTest crs_caseTest%rowtype;

for r_caseTest in crs_caseTest loop

case

when r_caseTest.DEPNO=10

THEN salInfo:=r_caseTest.SAL*1.05;

when r_caseTest.DEPNO=20

THEN salInfo:=r_caseTest.SAL*1.1;

when r_caseTest.DEPNO=30

THEN salInfo:=r_caseTest.SAL*1.15;

when r_caseTest.DEPNO=40

THEN salInfo:=r_caseTest.SAL*1.2;

end case;

update emp1 set SAL=salInfo where current of crs_caseTest;

--13:對每位員工的薪水進行判斷，如果該員工薪水高于其所在部門的平均薪水，則将其薪水減50元，輸出更新前後的薪水，員工姓名，所在部門編号。

--AVG([distinct|all] expr) over (analytic_clause)

---作用：

--按照analytic_clause中的規則求分組平均值。

--分析函數文法:

--FUNCTION_NAME(,...)

--OVER

--()

--PARTITION子句

--按照表達式分區(就是分組),如果省略了分區子句,則全部的結果集被看作是一個單一的組

select * from emp1

DECLARE

crs_testAvg

IS

select EMPNO,ENAME,JOB,SAL,DEPNO,AVG(SAL) OVER (PARTITION BY DEPNO ) AS DEP_AVG

FROM EMP1 for update of SAL;

r_testAvg crs_testAvg%rowtype;

for r_testAvg in crs_testAvg loop

if r_testAvg.SAL>r_testAvg.DEP_AVG then

salInfo:=r_testAvg.SAL-50;

update emp1 set SAL=salInfo where current of crs_testAvg;

本文轉自 wws5201985 51CTO部落格，原文連結：http://blog.51cto.com/wws5201985/735614，如需轉載請自行聯系原作者