大型數(shù)據(jù)庫(kù)技術(shù)-實(shí)驗(yàn)四PLSQL編程

大型數(shù)據(jù)庫(kù)技術(shù)-實(shí)驗(yàn)四PLSQL編程實(shí)驗(yàn)內(nèi)容：(1)記錄執(zhí)行命令和操作過(guò)程中遇到的問(wèn)題及解決方法，注意從原理上解釋原因。(2)記錄利用SQL*Plus或PL/SQLDeveloper編寫(xiě)、執(zhí)行PL/SQL程序的命令。(3)記錄執(zhí)行命令和操作過(guò)程中遇到的問(wèn)題及解決方法，注意從原理上解釋原因。(4)記錄利用企業(yè)管理器管理存儲(chǔ)過(guò)程、存儲(chǔ)函數(shù)、觸發(fā)器的方法(5)記錄利用SQL*Plus和PL/SQLDeveloper管理存儲(chǔ)過(guò)程、存儲(chǔ)函數(shù)、觸發(fā)器的命令。實(shí)驗(yàn)步驟與調(diào)試過(guò)程：1．在SQL*Plus中使用PL/SQL塊處理2．無(wú)參數(shù)的存儲(chǔ)過(guò)程3．帶輸入?yún)?shù)的存儲(chǔ)過(guò)程4．帶輸入輸出的存儲(chǔ)過(guò)程5．用Function查詢出EMP中給定職工號(hào)的工資6．用異常處理完善程序7.創(chuàng)建存儲(chǔ)過(guò)程，實(shí)現(xiàn)功能為：刪除scott.emp表中任意給定職工號(hào)的職工記錄8.創(chuàng)建函數(shù)，實(shí)現(xiàn)功能為：在scott.emp表和scott.dept表中查詢出任意給定職工號(hào)的職工姓名及職工所在部門(mén)的名稱9.創(chuàng)建觸發(fā)器，實(shí)現(xiàn)更新dept表中的deptno值，級(jí)聯(lián)更新emp表中相應(yīng)值10.對(duì)存儲(chǔ)過(guò)程、函數(shù)及觸發(fā)器實(shí)現(xiàn)查看、修改、刪除等基本操作實(shí)驗(yàn)結(jié)果：第1頁(yè)共13頁(yè)第2頁(yè)共13頁(yè)第3頁(yè)共13頁(yè)第4頁(yè)共13頁(yè)疑難小結(jié)：1、ORA-12541:TNS:沒(méi)有監(jiān)聽(tīng)器原因:沒(méi)有啟動(dòng)監(jiān)聽(tīng)器或者監(jiān)聽(tīng)器損壞。如果是前者，使用命令netstartOracleOraHome81TNSListener(名字可能有出入)即可;如果是后者，則使用"Net8ConfigurationAssistant"工具向?qū)е?監(jiān)聽(tīng)程序配置"增加一個(gè)監(jiān)聽(tīng)器即可(基本不用寫(xiě)任何信息，一路OK。在添加之前可能需要把所有的監(jiān)聽(tīng)器先刪除!)2、ORA-12500:TNS:監(jiān)聽(tīng)程序無(wú)法啟動(dòng)專用服務(wù)器進(jìn)程或ORA-12560:TNS:協(xié)議適配器錯(cuò)誤原因:ORACLE的數(shù)據(jù)庫(kù)服務(wù)沒(méi)有啟動(dòng)。使用命令netstartORACLESERVICEORADB(ORADB為數(shù)據(jù)庫(kù)名字)即可。如果仍沒(méi)有解決，請(qǐng)繼續(xù)向下看。3、如果數(shù)據(jù)庫(kù)服務(wù)啟動(dòng)失敗，則很有可能是其注冊(cè)表項(xiàng)值損壞，最好的做法是以下兩步:1)ORADIM-DELETE-SIDoradb刪除數(shù)據(jù)庫(kù)服務(wù)項(xiàng)2)ORADIM-NEW-SIDoradb新增數(shù)據(jù)庫(kù)服務(wù)項(xiàng)注:這個(gè)過(guò)程中如果出錯(cuò)，就重啟計(jì)算機(jī)!第5頁(yè)共13頁(yè)主要實(shí)現(xiàn)方法和過(guò)程（程序清單）：1．在SQL*Plus中使用PL/SQL塊處理EMP表中職工號(hào)7788的職工，如果工資小于3000那么把工資更改為3000：SQL>DECLAREXNUMBER(7,2);BEGINSELECTsalINTOxFROMempWHEREempno=7788;IFx<3000THENUPDATEempSETsal=3000WHEREempno=7788;ENDIF;END;如果想運(yùn)行緩沖區(qū)的內(nèi)容，那么可以用RUN命令或者/命令。請(qǐng)參閱第一章有關(guān)內(nèi)容。2．無(wú)參數(shù)的存儲(chǔ)過(guò)程CREATEORREPLACEPROCEDUREproc_executionISBEGINUPDATEEMPSETENAME=‘yourname’whereEMPNO=9010;ENDproc_execution;存儲(chǔ)過(guò)程的在SQL*Plus中運(yùn)行SQL>EXECUTEproc_execution第6頁(yè)共13頁(yè)

3．帶輸入?yún)?shù)的存儲(chǔ)過(guò)程：解雇給定職工號(hào)的職工，并調(diào)用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.存儲(chǔ)過(guò)程刪除了職工號(hào)7654的職工。4．帶輸入輸出的存儲(chǔ)過(guò)程查詢EMP中給定職工號(hào)的姓名、工資和傭金。SQL>CREATEORREPLACEPROCEDUREquery_emp(v_emp_noINemp.empno%type,v_emp_nameOUTemp.ename%type,v_emp_salOUTemp.sal%type,第7頁(yè)共13頁(yè)

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頁(yè)共13頁(yè)

5．用Function查詢出EMP中給定職工號(hào)的工資：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頁(yè)共13頁(yè)

12506．用異常處理完善程序如例3中：解雇給定職工號(hào)的職工，并調(diào)用proc_execution：SQL>CREATEORREPLACEPROCEDUREfire_emp(v_emp_noINemp.empno%type)ISBEGINproc_execution;DELETEFROMEMPWHEREempno=v_emp_no;ENDfire_emp;Procedurecreated.SQL>EXECUTfire_emp(7654)如果職工號(hào)7654的職工不存在則出錯(cuò)。為了避免出錯(cuò)我們使用了EXCEPTION語(yǔ)句。SQL>CREATEORREPLACEPROCEDUREfire_emp(v_emp_noINemp.empno%type)IS第10頁(yè)共13頁(yè)

BEGINproc_execution;DELETEFROMEMPWHEREempno=v_emp_no;IFSQL%NOTFOUNDTHENRAISE_APPLICATION_ERROR(-20202,'Employeedoesnotexists.');ENDIF;ENDfire_emp;Procedurecreated.SQL>EXECUTfire_emp(7654)就不會(huì)出錯(cuò)了。7創(chuàng)建存儲(chǔ)過(guò)程，實(shí)現(xiàn)功能為：刪除scott.emp表中任意給定職工號(hào)的職工記錄；CREATEORREPLACEPROCEDUREDELETE_EMP(IDINDEPT.DEPTNO%TYPE)ISBEGINDELETEFROMDEPTWHEREDEPTNO=ID;ENDDELETE_EMP;8創(chuàng)建函數(shù)，實(shí)現(xiàn)功能為：在scott.emp表和scott.dept表中查詢出任意給定職工號(hào)的職工姓名及職工所在部門(mén)的名稱。CREATEORREPLACEFUNCTIONSELECT_EMP(V_EMP_NOINEMP.EMPNO%TYPE,V_EMP_NAMEOUTEMP.ENAME%TYPE)第11頁(yè)共13頁(yè)

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創(chuàng)建觸發(fā)器，實(shí)現(xiàn)更新dept表中的deptno值，級(jí)聯(lián)更新emp表中相應(yīng)值。CREATEORREPLACETRIGGERUPDATE_DEPT_TO_EMPAFTERUPDATEONDEPTFOREACHROWBEGINIFupdatingTHENUPDATEempSETdeptno=:new.deptnoWHEREdeptno=:old.deptno;ENDIF;ENDUPDATE_DEPT_TO_EMP;第12頁(yè)共13頁(yè)

10對(duì)存儲(chǔ)過(guò)程、