大型数据库技术-实验四PLSQL编程

大型数据库技术-实验四PLSQL编程实验内容：(1)记录执行命令和操作过程中遇到的问题及解决方法，注意从原理上解释原因。(2)记录利用SQL*Plus或PL/SQLDeveloper编写、执行PL/SQL程序的命令。(3)记录执行命令和操作过程中遇到的问题及解决方法，注意从原理上解释原因。(4)记录利用企业管理器管理存储过程、存储函数、触发器的方法(5)记录利用SQL*Plus和PL/SQLDeveloper管理存储过程、存储函数、触发器的命令。实验步骤与调试过程：1．在SQL*Plus中使用PL/SQL块处理2．无参数的存储过程3．带输入参数的存储过程4．带输入输出的存储过程5．用Function查询出EMP中给定职工号的工资6．用异常处理完善程序7.创建存储过程，实现功能为：删除scott.emp表中任意给定职工号的职工记录8.创建函数，实现功能为：在scott.emp表和scott.dept表中查询出任意给定职工号的职工姓名及职工所在部门的名称9.创建触发器，实现更新dept表中的deptno值，级联更新emp表中相应值10.对存储过程、函数及触发器实现查看、修改、删除等基本操作实验结果：第1页共13页第2页共13页第3页共13页第4页共13页疑难小结：1、ORA-12541:TNS:没有监听器原因:没有启动监听器或者监听器损坏。如果是前者，使用命令netstartOracleOraHome81TNSListener(名字可能有出入)即可;如果是后者，则使用"Net8ConfigurationAssistant"工具向导之"监听程序配置"增加一个监听器即可(基本不用写任何信息，一路OK。在添加之前可能需要把所有的监听器先删除!)2、ORA-12500:TNS:监听程序无法启动专用服务器进程或ORA-12560:TNS:协议适配器错误原因:ORACLE的数据库服务没有启动。使用命令netstartORACLESERVICEORADB(ORADB为数据库名字)即可。如果仍没有解决，请继续向下看。3、如果数据库服务启动失败，则很有可能是其注册表项值损坏，最好的做法是以下两步:1)ORADIM-DELETE-SIDoradb删除数据库服务项2)ORADIM-NEW-SIDoradb新增数据库服务项注:这个过程中如果出错，就重启计算机!第5页共13页主要实现方法和过程（程序清单）：1．在SQL*Plus中使用PL/SQL块处理EMP表中职工号7788的职工，如果工资小于3000那么把工资更改为3000：SQL>DECLAREXNUMBER(7,2);BEGINSELECTsalINTOxFROMempWHEREempno=7788;IFx<3000THENUPDATEempSETsal=3000WHEREempno=7788;ENDIF;END;如果想运行缓冲区的内容，那么可以用RUN命令或者/命令。请参阅第一章有关内容。2．无参数的存储过程CREATEORREPLACEPROCEDUREproc_executionISBEGINUPDATEEMPSETENAME=‘yourname’whereEMPNO=9010;ENDproc_execution;存储过程的在SQL*Plus中运行SQL>EXECUTEproc_execution第6页共13页

3．带输入参数的存储过程：解雇给定职工号的职工，并调用proc_execution：SQL>CREATEORREPLACEPROCEDUREfire_emp(v_emp_noINemp.empno%type)ISBEGINproc_execution;DELETEFROMEMPWHEREempno=v_emp_no;ENDfire_emp;/Procedurecreated.SQL>EXECUTfire_emp(7654)PL/SQLproceduresuccessfullycompleted.存储过程删除了职工号7654的职工。4．带输入输出的存储过程查询EMP中给定职工号的姓名、工资和佣金。SQL>CREATEORREPLACEPROCEDUREquery_emp(v_emp_noINemp.empno%type,v_emp_nameOUTemp.ename%type,v_emp_salOUTemp.sal%type,第7页共13页

v_emp_commOUTm%type)ISBEGINSELECTename,sal,commINTOv_emp_name,v_emp_sal,v_emp_commFROMEMPWHEREempno=v_emp_no;ENDquery_emp;/Procedurecreated.SQL>VARIABLEemp_namevarchar2(15);SQL>VARIABLEemp_salnumber;SQL>VARIABLEemp_commnumber;SQL>EXECUTEquery_emp(7654,:emp_name,:emp_sal,:emp_comm);PL/SQLproceduresuccessfullycompleted.SQL>PRINTemp_name;EMP_NAME-------------------MARTIN第8页共13页

5．用Function查询出EMP中给定职工号的工资：SQL>CREATEORREPLACEFUNCTIONget_sal(v_emp_noINemp.empno%type)RETURNnumberISV_emp_salemp.sal%type:=0;BEGINSELECTsalINTOv_emp_salFROMEMPWHEREempno=v_emp_no;RETURN(v_emp_sal);ENDget_sal;/Procedurecreated.SQL>VARIABLEemp_salnumberSQL>EXECUTE:emp_sal:=get_sal(7654)PL/SQLproceduresuccessfullycompleted.SQL>PRINTemp_salEMP_SAL-------------------第9页共13页

12506．用异常处理完善程序如例3中：解雇给定职工号的职工，并调用proc_execution：SQL>CREATEORREPLACEPROCEDUREfire_emp(v_emp_noINemp.empno%type)ISBEGINproc_execution;DELETEFROMEMPWHEREempno=v_emp_no;ENDfire_emp;Procedurecreated.SQL>EXECUTfire_emp(7654)如果职工号7654的职工不存在则出错。为了避免出错我们使用了EXCEPTION语句。SQL>CREATEORREPLACEPROCEDUREfire_emp(v_emp_noINemp.empno%type)IS第10页共13页

BEGINproc_execution;DELETEFROMEMPWHEREempno=v_emp_no;IFSQL%NOTFOUNDTHENRAISE_APPLICATION_ERROR(-20202,'Employeedoesnotexists.');ENDIF;ENDfire_emp;Procedurecreated.SQL>EXECUTfire_emp(7654)就不会出错了。7创建存储过程，实现功能为：删除scott.emp表中任意给定职工号的职工记录；CREATEORREPLACEPROCEDUREDELETE_EMP(IDINDEPT.DEPTNO%TYPE)ISBEGINDELETEFROMDEPTWHEREDEPTNO=ID;ENDDELETE_EMP;8创建函数，实现功能为：在scott.emp表和scott.dept表中查询出任意给定职工号的职工姓名及职工所在部门的名称。CREATEORREPLACEFUNCTIONSELECT_EMP(V_EMP_NOINEMP.EMPNO%TYPE,V_EMP_NAMEOUTEMP.ENAME%TYPE)第11页共13页

RETURNDEPT.DNAME%TYPEASV_DEPT_NAMEDEPT.DNAME%TYPE;BEGINSELECTEMP.ENAME,DEPT.DNAMEINTOv_emp_name,v_dept_nameFROMEMP,DEPTWHEREEMP.deptno=DEPT.DEPTNOANDEMPNO=v_emp_no;RETURNv_dept_name;ENDSELECT_EMP;9创建触发器，实现更新dept表中的deptno值，级联更新emp表中相应值。CREATEORREPLACETRIGGERUPDATE_DEPT_TO_EMPAFTERUPDATEONDEPTFOREACHROWBEGINIFupdatingTHENUPDATEempSETdeptno=:new.deptnoWHEREdeptno=:old.deptno;ENDIF;ENDUPDATE_DEPT_TO_EMP;第12页共13页

10对存储过程、