总结下Cursor 各种用法
首先创建一个表
createtableemp ( empnovarchar2(8),enamevarchar2(8),jobvarchar2(8),salvarchar2(8) )
然后插入几行数据 ，就可以运行以下存储过程了 。
--声明游标；CURSORcursor_nameISselect_statement --For循环游标 --（1）定义游标 --（2）定义游标变量 --（3）使用for循环来使用这个游标 SETSERVEROUTPUTON declare --类型定义 cursorc_job is selectempno,ename,job,sal fromemp wherejob='MANAGER'; --定义一个游标变量v_cinfoc_emp%ROWTYPE，该类型为游标c_emp中的一行数据类型 c_rowc_job%rowtype; begin forc_rowinc_jobloop dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal); endloop; end; --运行结果： 111-aaaa-MANAGER-running 333-ccc-MANAGER-swimming 444-ddd-MANAGER-running 1000-fff-MANAGER-swimming 888-hhh-MANAGER-swimming 777-iii-MANAGER-running PL/sqlproceduresuccessfullycompleted --oralce的%rowtype解释：表示该类型为行数据类型，存储的是一行数据，一行数据里可以有多列，类似于表里的一行数据，也可以是游标里的一行数据，如： --vs_row1表%rowtype; --vs_row2游标%rowtype; --dbms_output.put_line的使用方法： setserveroutputon begin DBMS_OUTPUT.PUT_LINE('HELLO,THISISLUOSEA'); END; / --Fetch游标 --使用的时候必须要明确的打开和关闭 declare --类型定义 cursorc_job is selectempno,sal fromemp wherejob='MANAGER'; --定义一个游标变量 c_rowc_job%rowtype; begin openc_job; loop --提取一行数据到c_row fetchc_jobintoc_row; --判读是否提取到值，没取到值就退出 --取到值c_job%notfound是false --取不到值c_job%notfound是true exitwhenc_job%notfound; dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal); endloop; --关闭游标 closec_job; end; --1：任意执行一个update操作，用隐式游标sql的属性%found,%notfound,%rowcount,%isopen观察update语句的执行情况。 begin updateempsetENAME='ALEARK'WHEREEMPNO=888; ifsql%isopenthen dbms_output.put_line('Openging'); else dbms_output.put_line('closing'); endif; ifsql%foundthen dbms_output.put_line('游标指向了有效行');--判断游标是否指向有效行 else dbms_output.put_line('Sorry'); endif; ifsql%notfoundthen dbms_output.put_line('AlsoSorry'); else dbms_output.put_line('Haha'); endif; dbms_output.put_line(sql%rowcount); exception whenno_data_foundthen dbms_output.put_line('SorryNodata'); whentoo_many_rowsthen dbms_output.put_line('TooManyrows'); end; declare empNumberemp.EMPNO%TYPE; empNameemp.ENAME%TYPE; begin ifsql%isopenthen dbms_output.put_line('Cursorisopinging'); else dbms_output.put_line('CursorisClose'); endif; ifsql%notfoundthen dbms_output.put_line('NoValue'); else dbms_output.put_line(empNumber); endif; dbms_output.put_line(sql%rowcount); dbms_output.put_line('-------------'); selectEMPNO,ENAMEintoempNumber,empNamefromempwhereEMPNO=7499; dbms_output.put_line(sql%rowcount); ifsql%isopenthen dbms_output.put_line('Cursorisopinging'); else dbms_output.put_line('CursorisClosing'); endif; ifsql%notfoundthen dbms_output.put_line('NoValue'); else dbms_output.put_line(empNumber); endif; exception whenno_data_foundthen dbms_output.put_line('NoValue'); whentoo_many_rowsthen dbms_output.put_line('toomanyrows'); end; --2,使用游标和loop循环来显示所有部门的名称 --游标声明 declare cursorcsr_dept is --select语句 selectDNAME fromDepth; --指定行指针,这句话应该是指定和csr_dept行类型相同的变量 row_deptcsr_dept%rowtype; begin --for循环 forrow_deptincsr_deptloop dbms_output.put_line('部门名称:'||row_dept.DNAME); endloop; end; --3,使用游标和while循环来显示所有部门的的地理位置（用%found属性） declare --游标声明 cursorcsr_TestWhile is --select语句 selectLOC fromDepth; --指定行指针 row_loccsr_TestWhile%rowtype; begin --打开游标 opencsr_TestWhile; --给第一行喂数据 fetchcsr_TestWhileintorow_loc; --测试是否有数据，并执行循环 whilecsr_TestWhile%foundloop dbms_output.put_line('部门地点：'||row_loc.LOC); --给下一行喂数据 fetchcsr_TestWhileintorow_loc; endloop; closecsr_TestWhile; end; select*fromemp --4,接收用户输入的部门编号，用for循环和游标，打印出此部门的所有雇员的所有信息(使用循环游标) --CURSORcursor_name[(parameter[,parameter],...)]ISselect_statement; --定义参数的语法如下:Parameter_name[IN]data_type[{:=|DEFAULT}value] declare CURSOR c_dept(p_deptNonumber) is select*fromempwhereemp.depno=p_deptNo; r_empemp%rowtype; begin forr_empinc_dept(20)loop dbms_output.put_line('员工号：'||r_emp.EMPNO||'员工名：'||r_emp.ENAME||'工资：'||r_emp.SAL); endloop; end; select*fromemp --5：向游标传递一个工种，显示此工种的所有雇员的所有信息(使用参数游标) declare cursor c_job(p_jobnvarchar2) is select*fromempwhereJOB=p_job; r_jobemp%rowtype; begin forr_jobinc_job('CLERK')loop dbms_output.put_line('员工号'||r_job.EMPNO||''||'员工姓名'||r_job.ENAME); endloop; end; SELECT*FROMEMP --6：用更新游标来为雇员加佣金：(用if实现,创建一个与emp表一摸一样的emp1表，对emp1表进行修改操作),并将更新前后的数据输出出来 --http://zheng12tian.iteye.com/blog/815770 createtableemp1asselect*fromemp; declare cursor csr_Update is select*fromemp1forupdateOFSAL; empInfocsr_Update%rowtype; saleInfoemp1.SAL%TYPE; begin FORempInfoINcsr_UpdateLOOP IFempInfo.SAL<1500THEN saleInfo:=empInfo.SAL*1.2; elsifempInfo.SAL<2000THEN saleInfo:=empInfo.SAL*1.5; elsifempInfo.SAL<3000THEN saleInfo:=empInfo.SAL*2; ENDIF; UPDATEemp1SETSAL=saleInfoWHERECURRENTOFcsr_Update; ENDLOOP; END; --7:编写一个PL/sql程序块，对名字以‘A’或‘S’开始的所有雇员按他们的基本薪水(sal)的10%给他们加薪(对emp1表进行修改操作) declare cursor csr_AddSal is select*fromemp1whereENAMELIKE'A%'ORENAMELIKE'S%'forupdateOFSAL; r_AddSalcsr_AddSal%rowtype; saleInfoemp1.SAL%TYPE; begin forr_AddSalincsr_AddSalloop dbms_output.put_line(r_AddSal.ENAME||'原来的工资:'||r_AddSal.SAL); saleInfo:=r_AddSal.SAL*1.1; UPDATEemp1SETSAL=saleInfoWHERECURRENTOFcsr_AddSal; endloop; end; --8：编写一个PL/sql程序块，对所有的salesman增加佣金(comm)500 declare cursor csr_AddComm(p_jobnvarchar2) is select*fromemp1whereJOB=p_jobFORUPDATEOFCOMM; r_AddCommemp1%rowtype; commInfoemp1.comm%type; begin forr_AddCommincsr_AddComm('SALESMAN')LOOP commInfo:=r_AddComm.COMM+500; UPDATEEMP1SETCOMM=commInfowhereCURRENTOFcsr_AddComm; ENDLOOP; END; --9：编写一个PL/sql程序块，以提升2个资格最老的职员为MANAGER（工作时间越长，资格越老） --（提示：可以定义一个变量作为计数器控制游标只提取两条数据；也可以在声明游标的时候把雇员中资格最老的两个人查出来放到游标中。） declare cursorcrs_testComput is select*fromemp1orderbyHIREDATEasc; --计数器 top_twonumber:=2; r_testComputcrs_testComput%rowtype; begin opencrs_testComput; FETCHcrs_testComputINTOr_testComput; whiletop_two>0loop dbms_output.put_line('员工姓名：'||r_testComput.ENAME||'工作时间：'||r_testComput.HIREDATE); --计速器减一 top_two:=top_two-1; FETCHcrs_testComputINTOr_testComput; endloop; closecrs_testComput; end; --10：编写一个PL/sql程序块，对所有雇员按他们的基本薪水(sal)的20%为他们加薪， --如果增加的薪水大于300就取消加薪(对emp1表进行修改操作，并将更新前后的数据输出出来) declare cursor crs_UpadateSal is select*fromemp1forupdateofSAL; r_UpdateSalcrs_UpadateSal%rowtype; salAddemp1.sal%type; salInfoemp1.sal%type; begin forr_UpdateSalincrs_UpadateSalloop salAdd:=r_UpdateSal.SAL*0.2; ifsalAdd>300then 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||':加薪成功.'||'薪水变为：'||salInfo); endif; updateemp1setSAL=salInfowherecurrentofcrs_UpadateSal; endloop; end; --11:将每位员工工作了多少年零多少月零多少天输出出来 --近似 --CEIL(n)函数：取大于等于数值n的最小整数 --FLOOR(n)函数：取小于等于数值n的最大整数 --truc的用法http://publish.it168.com/2005/1028/20051028034101.shtml declare cursor crs_WorkDay is selectENAME,HIREDATE,trunc(months_between(sysdate,hiredate)/12)ASSPANDYEARS,trunc(mod(months_between(sysdate,hiredate),12))ASmonths,trunc(mod(mod(sysdate-hiredate,365),12))asdays fromemp1; r_WorkDaycrs_WorkDay%rowtype; begin forr_WorkDayincrs_WorkDayloop dbms_output.put_line(r_WorkDay.ENAME||'已经工作了'||r_WorkDay.SPANDYEARS||'年,零'||r_WorkDay.months||'月,零'||r_WorkDay.days||'天'); endloop; end; --12:输入部门编号，按照下列加薪比例执行(用CASE实现，创建一个emp1表，修改emp1表的数据),并将更新前后的数据输出出来 --deptnoraise(%) --105% --2010% --3015% --4020% --加薪比例以现有的sal为标准 --CASEexprWHENcomparison_exprTHENreturn_expr --[,WHENcomparison_exprTHENreturn_expr]...[ELSEelse_expr]END declare cursor crs_caseTest is select*fromemp1forupdateofSAL; r_caseTestcrs_caseTest%rowtype; salInfoemp1.sal%type; begin forr_caseTestincrs_caseTestloop case whenr_caseTest.DEPNO=10 THENsalInfo:=r_caseTest.SAL*1.05; whenr_caseTest.DEPNO=20 THENsalInfo:=r_caseTest.SAL*1.1; whenr_caseTest.DEPNO=30 THENsalInfo:=r_caseTest.SAL*1.15; whenr_caseTest.DEPNO=40 THENsalInfo:=r_caseTest.SAL*1.2; endcase; updateemp1setSAL=salInfowherecurrentofcrs_caseTest; endloop; end; --13:对每位员工的薪水进行判断，如果该员工薪水高于其所在部门的平均薪水，则将其薪水减50元，输出更新前后的薪水，员工姓名，所在部门编号。 --AVG([distinct|all]expr)over(analytic_clause) ---作用： --按照analytic_clause中的规则求分组平均值。 --分析函数语法: --FUNCTION_NAME(<argument>,<argument>...) --OVER --(<Partition-Clause><Order-by-Clause><WindowingClause>) --PARTITION子句 --按照表达式分区(就是分组),如果省略了分区子句,则全部的结果集被看作是一个单一的组 select*fromemp1 DECLARE CURSOR crs_testAvg IS selectEMPNO,ENAME,JOB,SAL,DEPNO,AVG(SAL)OVER(PARTITIONBYDEPNO)ASDEP_AVG FROMEMP1forupdateofSAL; r_testAvgcrs_testAvg%rowtype; salInfoemp1.sal%type; begin forr_testAvgincrs_testAvgloop ifr_testAvg.SAL>r_testAvg.DEP_AVGthen salInfo:=r_testAvg.SAL-50; endif; updateemp1setSAL=salInfowherecurrentofcrs_testAvg; endloop; end;
