-- 聲明游標(biāo)；CURSOR cursor_name IS select_statement

--For 循環(huán)游標(biāo) --（1）定義游標(biāo) --（2）定義游標(biāo)變量 --（3）使用for循環(huán)來使用這個游標(biāo) declare ?????? --類型定義 ?????? cursor c_job ?????? is ?????? select empno,ename,job,sal ?????? from emp ?????? where job='MANAGER'; ?????? --定義一個游標(biāo)變量v_cinfo c_emp%ROWTYPE ，該類型為游標(biāo)c_emp中的一行數(shù)據(jù)類型 ?????? 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游標(biāo) --使用的時候必須要明確的打開和關(guān)閉

declare ?????? --類型定義 ?????? cursor c_job ?????? is ?????? select empno,ename,job,sal ?????? from emp ?????? where job='MANAGER'; ?????? --定義一個游標(biāo)變量 ?????? c_row c_job%rowtype; begin ?????? open c_job; ???????? loop ?????????? --提取一行數(shù)據(jù)到c_row ?????????? fetch c_job into c_row; ?????????? --判讀是否提取到值，沒取到值就退出 ?????????? --取到值c_job%notfound 是false ?????????? --取不到值c_job%notfound 是true ?????????? exit when c_job%notfound; ??????????? dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal); ???????? end loop; ?????? --關(guān)閉游標(biāo) ????? close c_job; end;

--1：任意執(zhí)行一個update操作，用隱式游標(biāo)sql的屬性%found,%notfound,%rowcount,%isopen觀察update語句的執(zhí)行情況。 ?????? begin ???????? 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('游標(biāo)指向了有效行');--判斷游標(biāo)是否指向有效行 ??????????? else ????????????? dbms_output.put_line('Sorry'); ????????????? end if; ????????????? if sql%notfound then ??????????????? dbms_output.put_line('Also Sorry'); ??????????????? else ????????????????? dbms_output.put_line('Haha'); ????????????????? end if; ?????????????????? 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'); ???????????????????????? end; declare ?????? empNumber emp.EMPNO%TYPE; ?????? empName emp.ENAME%TYPE; ?????? begin ???????? if sql%isopen then ?????????? dbms_output.put_line('Cursor is opinging'); ?????????? else ???????????? dbms_output.put_line('Cursor is Close'); ???????????? end if; ???????????? if sql%notfound then ?????????????? dbms_output.put_line('No Value'); ?????????????? else ???????????????? dbms_output.put_line(empNumber); ???????????????? end if; ???????????????? dbms_output.put_line(sql%rowcount); ???????????????? dbms_output.put_line('-------------'); ???????????????? ???????????????? select EMPNO,ENAME into? empNumber,empName from emp where EMPNO=7499; ???????????????? dbms_output.put_line(sql%rowcount); ???????????????? ??????????????? if sql%isopen then ??????????????? dbms_output.put_line('Cursor is opinging'); ??????????????? else ??????????????? dbms_output.put_line('Cursor is Closing'); ??????????????? end if; ???????????????? if sql%notfound then ???????????????? dbms_output.put_line('No Value'); ???????????????? else ???????????????? dbms_output.put_line(empNumber); ???????????????? end if; ???????????????? exception ?????????????????? when no_data_found then ???????????????????? dbms_output.put_line('No Value'); ???????????????????? when too_many_rows then ?????????????????????? dbms_output.put_line('too many rows'); ?????????????????????? end; ?????????????????? ???????????????? ?????? --2,使用游標(biāo)和loop循環(huán)來顯示所有部門的名稱 --游標(biāo)聲明 declare ?????? cursor csr_dept ?????? is ?????? --select語句 ?????? select DNAME ?????? from Depth; ?????? --指定行指針,這句話應(yīng)該是指定和csr_dept行類型相同的變量 ?????? row_dept csr_dept%rowtype; begin ?????? --for循環(huán) ?????? for row_dept in csr_dept loop ?????????? dbms_output.put_line('部門名稱:'||row_dept.DNAME); ?????? end loop; end;

--3,使用游標(biāo)和while循環(huán)來顯示所有部門的的地理位置（用%found屬性） declare ?????? --游標(biāo)聲明 ?????? cursor csr_TestWhile ?????? is ?????? --select語句 ?????? select? LOC ?????? from Depth; ?????? --指定行指針 ?????? row_loc csr_TestWhile%rowtype; begin ? --打開游標(biāo) ?????? open csr_TestWhile; ?????? --給第一行喂數(shù)據(jù) ?????? fetch csr_TestWhile into row_loc; ?????? --測試是否有數(shù)據(jù)，并執(zhí)行循環(huán) ???????? while csr_TestWhile%found loop ?????????? dbms_output.put_line('部門地點：'||row_loc.LOC); ?????????? --給下一行喂數(shù)據(jù) ?????????? fetch csr_TestWhile into row_loc; ???????? end loop; ?????? close csr_TestWhile; end; select * from emp

?

?????? --4,接收用戶輸入的部門編號，用for循環(huán)和游標(biāo)，打印出此部門的所有雇員的所有信息(使用循環(huán)游標(biāo)) --CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement; --定義參數(shù)的語法如下:Parameter_name [IN] data_type[{:=|DEFAULT} value]?

declare ????? CURSOR ????? c_dept(p_deptNo number) ????? is ????? select * from emp where emp.depno=p_deptNo; ????? r_emp emp%rowtype; begin ??????? for r_emp in c_dept(20) loop ??????????? dbms_output.put_line('員工號：'||r_emp.EMPNO||'員工名：'||r_emp.ENAME||'工資：'||r_emp.SAL); ??????? end loop; end; select * from emp?? --5：向游標(biāo)傳遞一個工種，顯示此工種的所有雇員的所有信息(使用參數(shù)游標(biāo)) declare ?????? cursor ?????? c_job(p_job nvarchar2) ?????? is ?????? select * from emp where JOB=p_job; ?????? r_job emp%rowtype; begin ?????? for r_job in c_job('CLERK') loop ?????????? dbms_output.put_line('員工號'||r_job.EMPNO||' '||'員工姓名'||r_job.ENAME); ??????? end loop; end; SELECT * FROM EMP

--6：用更新游標(biāo)來為雇員加傭金：(用if實現(xiàn),創(chuàng)建一個與emp表一摸一樣的emp1表，對emp1表進(jìn)行修改操作),并將更新前后的數(shù)據(jù)輸出出來 --http://zheng12tian.iteye.com/blog/815770 ??????? create table emp1 as select * from emp; ??????? declare ??????? cursor ??????? csr_Update ??????? is ??????? select * from? emp1 for update OF SAL; ??????? empInfo csr_Update%rowtype; ??????? saleInfo? emp1.SAL%TYPE; begin ??? 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表進(jìn)行修改操作) declare ???? cursor ????? csr_AddSal ???? is ????? select * from emp1 where ENAME LIKE 'A%' OR ENAME LIKE 'S%' for update OF SAL; ????? r_AddSal csr_AddSal%rowtype; ????? saleInfo? emp1.SAL%TYPE; begin ????? 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; ????? end loop; end; --8：編寫一個PL/SQL程序塊，對所有的salesman增加傭金(comm)500 declare ????? cursor ????????? csr_AddComm(p_job nvarchar2) ????? is ????????? select * from emp1 where?? JOB=p_job FOR UPDATE OF COMM; ????? r_AddComm? emp1%rowtype; ????? commInfo emp1.comm%type; begin ??? for r_AddComm in csr_AddComm('SALESMAN') LOOP ??????? commInfo:=r_AddComm.COMM+500; ???????? UPDATE EMP1 SET COMM=commInfo where CURRENT OF csr_AddComm; ??? END LOOP; END;

--9：編寫一個PL/SQL程序塊，以提升2個資格最老的職員為MANAGER（工作時間越長，資格越老） --（提示：可以定義一個變量作為計數(shù)器控制游標(biāo)只提取兩條數(shù)據(jù)；也可以在聲明游標(biāo)的時候把雇員中資格最老的兩個人查出來放到游標(biāo)中。） declare ??? cursor crs_testComput ??? is ??? select * from emp1 order by HIREDATE asc; ??? --計數(shù)器 ??? top_two number:=2; ??? r_testComput crs_testComput%rowtype; begin ??? 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; ???????????? FETCH crs_testComput INTO r_testComput; ?????????? end loop; ???? close crs_testComput; end; ???

--10：編寫一個PL/SQL程序塊，對所有雇員按他們的基本薪水(sal)的20%為他們加薪， --如果增加的薪水大于300就取消加薪(對emp1表進(jìn)行修改操作，并將更新前后的數(shù)據(jù)輸出出來) declare ??? cursor ??????? crs_UpadateSal ??? is ??????? select * from emp1 for update of SAL; ??????? r_UpdateSal crs_UpadateSal%rowtype; ??????? salAdd emp1.sal%type; ??????? salInfo emp1.sal%type; begin ??????? 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); ???????????? else ????????????? salInfo:=r_UpdateSal.SAL+salAdd; ????????????? dbms_output.put_line(r_UpdateSal.ENAME||':? 加薪成功.'||'薪水變?yōu)椋?||salInfo); ?????????? end if; ?????????? update emp1 set SAL=salInfo where current of crs_UpadateSal; ??????? end loop; end; ???? --11:將每位員工工作了多少年零多少月零多少天輸出出來?? --近似 ? --CEIL(n)函數(shù)：取大于等于數(shù)值n的最小整數(shù) ? --FLOOR(n)函數(shù)：取小于等于數(shù)值n的最大整數(shù) ? --truc的用法 http://publish.it168.com/2005/1028/20051028034101.shtml declare ? cursor ?? crs_WorkDay ?? is ?? 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; begin ??? for?? r_WorkDay in crs_WorkDay loop ??? dbms_output.put_line(r_WorkDay.ENAME||'已經(jīng)工作了'||r_WorkDay.SPANDYEARS||'年,零'||r_WorkDay.months||'月,零'||r_WorkDay.days||'天'); ??? end loop; end; ? --12:輸入部門編號，按照下列加薪比例執(zhí)行(用CASE實現(xiàn)，創(chuàng)建一個emp1表，修改emp1表的數(shù)據(jù)),并將更新前后的數(shù)據(jù)輸出出來 --? deptno? raise(%) --? 10????? 5% --? 20????? 10% --? 30????? 15% --? 40????? 20% --? 加薪比例以現(xiàn)有的sal為標(biāo)準(zhǔn) --CASE expr WHEN comparison_expr THEN return_expr --[, WHEN comparison_expr THEN return_expr]... [ELSE else_expr] END declare ???? cursor ???????? crs_caseTest ????????? is ????????? select * from emp1 for update of SAL; ????????? r_caseTest crs_caseTest%rowtype; ????????? salInfo emp1.sal%type; ???? begin ???????? 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; ??????? end loop; end;

--13:對每位員工的薪水進(jìn)行判斷，如果該員工薪水高于其所在部門的平均薪水，則將其薪水減50元，輸出更新前后的薪水，員工姓名，所在部門編號。 --AVG([distinct|all] expr) over (analytic_clause) ---作用： --按照analytic_clause中的規(guī)則求分組平均值。 ? --分析函數(shù)語法: ? --FUNCTION_NAME(<argument>,<argument>...) ? --OVER ? --(<Partition-Clause><Order-by-Clause><Windowing Clause>) ???? --PARTITION子句 ???? --按照表達(dá)式分區(qū)(就是分組),如果省略了分區(qū)子句,則全部的結(jié)果集被看作是一個單一的組 ???? select * from emp1 DECLARE ???? CURSOR ???? 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; ???? salInfo emp1.sal%type; ???? begin ???? for r_testAvg in crs_testAvg loop ???? if r_testAvg.SAL>r_testAvg.DEP_AVG then ???? salInfo:=r_testAvg.SAL-50; ???? end if; ???? update emp1 set SAL=salInfo where current of crs_testAvg; ???? end loop; end;

Oracle 游標(biāo)使用全解