总结下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;