实验8-SQL-PL编程基础(II)

第1页共15页实验8游标使用与异常处理姓名：江南学号：0907022105专业：班级：09网络同组人：无实验日期：2011-11-15【实验目的与要求】掌握游标使用基础技术掌握如何使用游标操纵数据【实验内容与步骤】8.1游标的使用:1．隐式游标：（0）创建测试表和加入测试数据droptableemp_Examloyees;createtableemp_Examloyees(EMP_EXAMLOYEE_IDNUMBER(6)primarykey,FIRST_NAMEVARCHAR2(20),LAST_NAMEVARCHAR2(25),EMAILVARCHAR2(25),PHONE_NUMBERVARCHAR2(20),HIRE_DATEDATE,JOB_IDVARCHAR2(10),SALARYNUMBER(8,2),COMMISSION_PCTNUMBER(2,2),DEPARTMENT_IDNUMBER(4));insertintoemp_Examloyees(EMP_EXAMLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID)values(1,'JIA','JENNY',3000,1);insertintoemp_Examloyees(EMP_EXAMLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID)values(2,'guan','rose',4000,3);insertintoemp_Examloyees(EMP_EXAMLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID)values(3,'xu','mike',2000,2);insertintoemp_Examloyees(EMP_EXAMLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID)values(4,'zhang','billy',1000,2);insertintoemp_Examloyees(EMP_EXAMLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID)values(5,'jin1','dede',5000,1);insertintoemp_Examloyees(EMP_EXAMLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID)values(6,'jin2','dede',5000,2);insertintoemp_Examloyees(EMP_EXAMLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID)values(7,'jin3','dede',5000,1);insertintoemp_Examloyees(EMP_EXAMLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID)values(8,'jin4','dede',5000,3);第2页共15页COMMIT;（1）使用隐式游标：以下程序段以sql%rowcount为例展示的是隐式游标的使用方法，请阅读并理解程序，给出测试结果。setserveroutputon;declarev_delete_countnumber(3);begindeletefromemp_Examloyeesewheree.DEPARTMENT_ID=1;v_delete_count:=sql%rowcount;commit;dbms_output.put_line('总共删除数据:'||v_delete_count||'条');end;/给出运行结果：2．显式游标：（1）用显式游标：--此处没有用循环，只能取出由标指针指向的第一条纪录droptableemp_Exam;createtableemp_Exam(idnumber(3),namevarchar2(20),salnumber(8,2));insertintoemp_Examvalues(1,'a',3000);insertintoemp_Examvalues(2,'b',3000);insertintoemp_Examvalues(3,'c',3000);insertintoemp_Examvalues(4,'d',3000);commit;第3页共15页setserveroutputon;declarecursorcisselect*fromemp_Exam;v_all_emp_Examlemp_Exam%rowtype;beginopenc;fetchcintov_all_emp_Examl;dbms_output.put_line(v_all_emp_Examl.id||'--'||v_all_emp_Examl.name||'--'||v_all_emp_Examl.sal);closec;end;/（2）用显式游标循环取值：--显示游标循环取值：declarecursorcisselect*fromemp_Exam;v_all_emp_Examlemp_Exam%rowtype;beginopenc;loopfetchcintov_all_emp_Examl;exitwhenc%notfound;dbms_output.put_line(v_all_emp_Examl.id||'--'||v_all_emp_Examl.name||'--'||v_all_emp_Examl.sal);endloop;closec;end;/给出运行结果：第4页共15页3．游标功能使用综合：--游标的功能展示，这里展示了%isopen,%found,%IsopenDeclaretemp_Examsalscott.emp_Exam.sal%Type;CursormycursorIsSelect*Fromscott.emp_ExameWheree.sal=temp_Examsal;currentrecordmycursor%Rowtype;Begintemp_Examsal:=3000;temp_Examsal:=800;--打开游标Ifmycursor%IsopenThendbms_output.put_line('该游标已经打开了，正在关闭！');Closemycursor;Openmycursor;Elsedbms_output.put_line('游标关闭，正在打开！');Openmycursor;EndIf;--读取数据FetchmycursorIntocurrentrecord;Ifmycursor%FoundThendbms_output.put_line('游标已经取到数据,查询结果是：');dbms_output.put_line(to_char(currentrecord.ename));Elsedbms_output.put_line('没有要查询的结果！');EndIf;--读取记录总条数LoopFetchmycursorIntocurrentrecord;ExitWhenmycursor%Notfound;EndLoop;dbms_output.put_line('查结果总共有：'||mycursor%Rowcount);--关闭游标Ifmycursor%IsopenThendbms_output.put_line('正在关闭游标，程序正常结束！');Closemycursor;EndIf;End;给出运行结果：第5页共15页8.2异常处理:阅读并理解以下程序，掌握异常处理的一般形式，给出运行结果。0．准备测试表和数据：droptablesm_emp_Exam;CREATETABLEsm_emp_Exam(nochar(4),namechar(10),salarynumber(6,2),phonechar(8));--insertTOMINSERTINTOsm_emp_ExamVALUES('001','TOM',999.99,'62543678');第6页共15页commit;--insertTOMagainINSERTINTOsm_emp_ExamVALUES('002','TOM',999.99,'62543678');commit;DELETEFROMsm_emp_ExamWHEREname='TOM';commit;1．异常处理示例：SETSERVEROUTPUTON---namedexceptiontestDECLAREv_nameVARCHAR2(10);n_salNUMBER(8,2);BEGINSELECTname,salaryINTOv_name,n_salFROMsm_emp_ExamWHEREname='TOM';DBMS_OUTPUT.PUT_LINE('TOM:SALARY:'||n_sal);EXCEPTION--注意：当多个exception在exception段中出现，只执行一个，之后不直接跳到end。WHENNO_DATA_FOUNDTHEN第7页共15页DBMS_OUTPUT.PUT_LINE('NOEMP_EXAMLOYEENAMEDTOM');WHENTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE('TOOMANYTOM');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('ERROR!');END;给出运行结果：8.3实验作业参照前面实验和教材，完成以下实验作业，给出源程序和测试结果。1．计算EMP表中所有雇员的所得税总和。假设所得税为累进税率，所得税算法为：工资收入为0－2000为免税；收入2000－3000者，超过2000的部分税率10％；3000－4000者超过3000部分按20％税率计算；4000－5000者超过4000部分按30％税率计算；5000以上收入，超过5000部分按40％税率计算。写出程序源码：DECLARECURSORc1ISSELECTsalFROMemp;sum_xxnumber;BEGINsum_xx:=0;FORr1INc1LOOPIFr1.sal0andr1.sal=2000THENsum_xx:=sum_xx;ELSIFr1.sal2000andr1.sal=3000THEN第8页共15页sum_xx:=sum_xx+(r1.sal-2000)*0.1;ELSIFr1.sal3000andr1.sal=4000THENsum_xx:=sum_xx+(r1.sal-3000)*0.2;ELSIFr1.sal4000andr1.sal=5000THENsum_xx:=sum_xx+(r1.sal-4000)*0.3;ELSEsum_xx:=sum_xx+(r1.sal-5000)*0.4;ENDIF;ENDLOOP;dbms_output.put_line('所有雇员的所得税总和：'||sum_xx);END;给出运行测试结果：2．创建一个PL/SQL块，声明一个游标，从emp表中选择姓名，薪水和雇佣日期。从游标检索每一行，如果雇员的薪水大于50000元，而且雇佣日期在1997年12月31日之前，则显示雇员的具体信息。写出程序源码：DeclareCursormycursorisSelectename,sal,hiredatefromemp;第9页共15页--Merchance_recemp%type;Begindbms_output.put_line('薪水大于4000元，且雇佣日期在1979-12-31之前雇员的信息:');--select*intoMerchance_recfromemp;forrinmycursorloopifr.sal4000andr.hiredate'31