第五章上机课使用PLSQL

老二牛车教育《Oracle数据库应用》上机课使用PLSQL目标：理解逻辑比较的定义能使用各种控制结构能编写错误处理程序第一部分：指导本阶段重点介绍编写程序以进行逻辑比较，使用各种控制结构以及编写错误处理代码的方法。阶段一：使用条件控制需求说明：由于工程取得巨大成功，GITM公司决定向职员发放奖金。奖金根据职员所在部门的代码来计算。如果部门代码为“DP01”，奖金为2000元；部门代码为“DP02”，奖金为1700元；部门代码为“DP03”，奖金为1500元，请设计解决方案。实现思路：条件控制根据指定的条件执行一连串语句。单个条件可以使用IF-THEN语句来指定。条件控制允许使用IF-THEN-ELSIF语句给出多个条件语句。该公司想要根据职员所在的部门代码来更新职员的工资记录。要计算工资，首先需要检查部门代码。要检查部门代码，可以使用IF-THEN-ELSIF语句。此程序声明VARCHAR2类型的DPTCODE和EMP-CODE以及NUMBER类型的SALARY。使用IF-THEN-ELSIF条件语句来验证职员所在的部门。SELECT语句从SALARY-RECORDS表中检索DEPTCODE、EMPCODE和EMPSAL，并将值存储在变量DPTCODE、EMP-CODE和SALARY中。IF语句比较DPTCODE中的值，然后更新SALARY-RECORDS表中的EMPSAL。参考步骤：（1）启动“SQLPlus”。（2）在SQL提示中输入“SETSERVEROUTPUTON”。（3）在SQLPlus中输入下列代码。DECLAREDPTCODEVARCHAR2(15);EMP_CODEVARCHAR2(10);SALARYNUMBER;BEGINSELECTDEPTCODE,EMPCODE,EMPSALINTODPTCODE,EMP_CODE,SALARYFROMSALARY_RECORDSWHEREEMPCODE='&EMPID'FORUPDATEOFEMPSAL;IFDPTCODE='DP01'THENUPDATESALARY_RECORDSSETEMPSAL=SALARY+2000WHEREEMPCODE=EMP_CODE;ELSIFDPTCODE='DP02'THENUPDATESALARY_RECORDSSETEMPSAL=SALARY+1700老二牛车教育《Oracle数据库应用》上机课WHEREEMPCODE=EMP_CODE;ELSIFDPTCODE='DP03'THENUPDATESALARY_RECORDSSETEMPSAL=SALARY+1500WHEREEMPCODE=EMP_CODE;ENDIF;COMMIT;END;/此代码提示用户输入部门代码。代码将显示消息“PL/SQL过程已成功完成”。使用SELECT语句可以检索更新以后的行。（4）在SQLPlus中输入SELECT*FROMSALARY-RECORDS。更新以后的表如图5.1所示。EMPNOEMPCODEDEPTCODEENAMEWORKING_DAYSEMPSAL------------------------------------------------------------------------E001BH01DP02JohnSmith3013700E002BH02DP02Jane2515000E003BH03DP01Harry3012000E004BH03DP03Joe3020000E005BH03DP02Michael3018000E006BH05DP01JackAranck3018000E007BH05DP03George3018000阶段二：使用循环控制需求说明：请编写一个程序，用以接受用户输入的学生编号和课程。在course-details表中详细信息。在course-details表中插入由用户分5次输入的详细信息。每次在表中插入记录时，学生编号都应增加1。实现思路：循环控制用于多次执行一连串语句。循环控制包括LOOP和EXIT语句。此控制可以使用EXIT语句无条件退出循环。EXITWHEN语句用于根据条件结束循环。循环控制包括LOOP、WHILE和FOR循环。WHILE循环将条件与一连串语句关联在一起。FOR循环计算条件的值并在指定的整数范围内进行循环。此代码声明%ROWTYPE类型的courserec变量和NUMBER数据类型的counter变量，并接受用户输入的学生编号和课程。记录存储在变量courserec中。INSERT语句在course_details表中插入记录。WHILE循环会运行，直到计数器的值等于5为止。每当WHILE循环中的INSERT语句在表中插入记录时，学生ID都会增加1。执行查询前，表中共包含6条记录。成功执行代码后，学生ID将增加，直到sid等于11。参考步骤：（1）启动“SQLPlus”。（2）在SQLPlus中输入下列代码。老二牛车教育《Oracle数据库应用》上机课DECLAREcoursereccourse_details%ROWTYPE;counterNUMBER:=0;BEGINcourserec.sid:=&student_id;courserec.course:='&Course_name';WHILEcounter12LOOPINSERTINTOcourse_detailsVALUES(courserec.sid,courserec.course);counter:=counter+1;courserec.sid:=courserec.sid+1;ENDLOOP;END;/代码将显示消息“已成功完成PL/SQL过程”。使用SELECT语句可用于显示course_details中的记录。（3）在SQLPlus中输入SELECT*FROMcourse_details。输出结果如图5.2所示。SIDCOURSE-------------------------1cs2cs3cs4cs5cs6cs7cs8cs9cs10cs11cs12cs阶段三：使用顺序控制需求说明：公司会计想要在月末结算职员的工资。会计需要将奖金加到职员工资当中。奖金是根据部门代码和职员的工作天数来计算的。如果部门代码为DP01，奖金为工资的25%，否则奖金将为工资的10%。在计算工资前，代码应该先检查工作天数是否多于29天。如果工作天数少于29天，则显示消息“工作天数少于29”，请给出解决方案。实现思路：老二牛车教育《Oracle数据库应用》上机课顺序控制按指定的顺序执行多个语句。顺序控制可用于无条件分支。此外，顺序控制还包括GOTO语句和NULL语句。GOTO语句将控制权转到指定的标记语句或语句块中，而NULL语句将控制权转到下一个语句。此代码将变量workdays、salary和bonus声明为NUMBER数据类型，并将变量dept声明为VARCHAR2数据类型。以下SELECT语句从SALARY-RECORDS表中检索working-days、EMPSAL和DEPTCODE。从表中检索到的数据存储在变量workdays、SALARY和DEPT中。SELECTworking-days，EMPSAL，DEPTCODEINTOworkdays，SALARY，DEPTFROMsalary-recordsWHEREempcode=‘&empid’IF语句检查职员的工作天数。。如果工作天数少于29天，代码将显示消息“工作天数少于29”。如果工作天数多于29天，则GOTO语句将控制权转给calc-raise标签。calc-raise标签后面的IF语句检查dept是否等于“DP01”，并计算和显示奖金。参考步骤：（1）启动“SQLPlus”。（2）在SQLPlus中输入下列代码。DECLAREworkdaysNUMBER;salaryNUMBER;bonusNUMBER;deptVARCHAR2(10);BEGINSELECTworking_days,EMPSAL,DEPTCODEINTOworkdays,SALARY,DEPTFROMsalary_recordsWHEREempcode='&empid';IFWORKDAYS=29THENGOTOcalc_raise;ELSEDBMS_OUTPUT..PUT_LINE('工作天数少于29天');ENDIF;calc_raiseIFdept='DP01'THENbonus:=salary*0.25;DBMS_OUTPUT.PUT_LINE(BONUS);ELSEbonus:=salary*0.10;DBMS_OUTPUT..PUT_LINE(BONUS);ENDIF;END;/输出结果如图5.3所示。老二牛车教育《Oracle数据库应用》上机课阶段四：使用预定义异常需求说明：公司通过employees表维护职员记录。此表包含职员的信息，如职员编号、职员姓名和职员职称等。要求编写一段PL/SQL代码，用以接受职员编号并检索职员姓名。将职员姓名存储在变量empname中。如果代码引发VALUE_ERROR异常，则向用户显示错误消息。实现思路：Oracle提供了一些预定义异常，如NO-DATA-FOUND、PROGRAM-ERROR、TOO-MANY-ROWS和ZERO-DIVIDE。预定义异常在运行时引发。在此代码中将变量empname声明为VARCHAR2(5)。SELECT接受用户输入的employee-number，并从职员表中检索ENAME。ENAME的值存储在变量empname中。如果ENAME中值的长度大于变量ename的长度，那么代码就会引发VALUE_ERROR异常。参考步骤：（1）启动“SQLPlus”。（2）在SQLPlus中输入下列代码。DECLAREempnameVARCHAR2(5);BEGINSELECTENAMEINTOempnameFROMemployeeWHEREempno='&Employee_Number';DBMS_OUTPUT..PUT_LINE('职员姓名：'||empname);EXCEPTIONWHENVALUE_ERRORTHENDBMS_OUTPUT.PUT_LINE('要存储在变量中的值过大')END;/此代码将提示用户输入employee-number。职员编号有效时的输出结果如图5.4所示。值无效时的输出结果如图5.5所示。老二牛车教育《Oracle数据库应用》上机课阶段五：引发应用程序错误需求说明：要求编写一个程序，用以接受用户输入的职员编号、工资和部门代码。如果部门代码为“DP01”且工资高于10000元，则更新职员的工资。如果工资低于10000元，则显示消息“工资低于10000元”。实现思路：过程RAISE-APPLICATION-ERROR应用在PL/SQL程序中返回用户自定义的错误消息。此代码接受用户输入的职员编号、工资和部门代码。IF语句检查部门代码是否为“DP01”。如果部门代码不是DP01，代码将显示消息“部门代码无效”；如果部门代码是DP01，IF语句将检查工资是否高于10000元。如果符合条件，则UPDATE语句将更新改职员编号的工资。如果工资低于10000元，代码将引发异常userexpt并向用户显示错误消息。参考步骤：（1）启动“SQLPlus”。（2）在SQLPlus中输入下列代码。DECLAREemp_noVARCHAR2(5);salNUMBER;deptcodeVARCHAR2(5);userexptEXCEPTION;BEGINdeptcode:='&deptcode';emp_no:='&Employee_code';sal:=&salary;IFdeptcode!='DP01'THENDBMS_OUTPUT..PUT_LINE('部门代码无效。');ELSEIFsal12000THENUPDATEsalary_recordsSETempsal=sal