oracle教程07plsql高級課件

PL/SQL高級編程

存儲過程函數的概念開發(fā)一個存儲過程或函數目的是把一個PL／SQL塊存進數據庫中，并在以后重復使用。例如：創(chuàng)建一個記錄用戶名和當前日期的無參數過程CREATEPROCEDURElog_executionISBEGININSERTINTOlog_table(user_id,log_date)VALUES(user,sysdate)；END；/可以在不同的環(huán)境調用同一個存儲過程。注意：語法有區(qū)別●在SQL*Plus中記錄用戶名和當前日期SQL＞EXECUTElog_execution；●從存儲過程FIRE_EMP中記錄用戶名和當前日期CREATEPROCEDUERfire_emp(v_emp_noINemp.empno％TYPE)ISBEGINlog_execution；DELETEFROMempWHEREempno=v_emp_no；END；/存儲過程與應用程序的區(qū)別

開發(fā)存儲過程和函數

1、開發(fā)一個存儲過程和函數的步驟（1）用文本編輯器編寫一個含有CreateProcedure或CreateFunction語句的PL/SQL腳本文件。（如：c:\procedure1.sql）（2）在SQL*Plus中用命令（如：SQL>startc:\procedure1.sql;）編譯腳本文件，調試編譯錯誤。系統(tǒng)就將創(chuàng)建過程或函數的源代碼存入數據字典user_source中。（3）編譯成功后將編譯代碼存入數據字典（4）調用存儲過程和函數，在運行期間調試存儲過程、函數的邏輯錯誤。

創(chuàng)建存儲過程的語法注意：IS后面是一個完整的PL/SQL塊的三部分（參見第六章），可以定義局部變量、游標等，但不能以DECLARE開始。CREATE[ORREPLACE]PROCEDURE過程名[(參數名[IN|OUT|INOUT]數據類型,…)]{IS|AS}[說明部分]BEGIN 語句序列[EXCEPTION出錯處理]END[過程名]；★形式參數可以有三種模式----IN、OUT、INOUT。如果沒有為形式參數指定模式，那么缺省的模式是IN。

類型描述--------------------------------------------------------------------IN(缺省)參數用來從調用環(huán)境中向過程傳遞值OUT參數用來從過程中返回值給調用者INOUT參數既可從調用者向過程中傳遞值，也可以從過程中返回可能改變了的值給調用者局部變量在過程內部存放值例：下面的過程說明了三種模式參數的區(qū)別createorreplaceproceduremodetest(p_inparameterinnumber,p_outparameteroutnumber,p_inoutparameterinoutnumber)isv_localvariablenumber;beginv_localvariable:=p_inparameter;/*正確*/(p_inparameter:=7;錯誤)v_outparameter:=7;/*正確*/(v_localvariable:=p_outparameter;錯誤)v_localvariable:=p_inoutparameter;/*正確*/v_inoutparameter:=7;/*正確*/end;/例：下列存儲過程給某一指定的員工漲指定數量的工資。Createprocedureraise_salary(emp_idinteger,v_increasereal)isbeginupdateempsetsal=sal+v_increasewhereempno=emp_id;commit;end;/例：下列過程根據給定的員工號返回員工的姓名、工資和獎金等信息。CREATEORREPLACEPROCEDUREquery_emp(v_emp_noINemp.empno％TYPE，V_emp_nameOUTemp.ename％TYPE，v_emp_salOUTemp.sal％TYPE，V_emp_commOUTm％TYPE)ISBEGINSELECTename，sal，commINTOv_emp_name，v_emp_sal，v_emp_commFROMempWHEREempno=v_emp_no;ENDquery_emp；/例：利用INOUT模式參數，將一個7位數字的電話號碼轉換成8位數字的電話號碼。CREATEORREPLACEPROCEDUREadd_dash(v_phone_noINOUTVARCHAR2)/*字符型的形式參數不指定長度*/ISBEGINv_phone_no:＝SUBSTR(v_phone_no,l,3)||‘-’||SUBSTR(v_phone_no，4，4)；ENDadd_dash；/創(chuàng)建存儲函數的語法CREATE[ORREPLACE]FUNCTION函數名[（參數名[IN]數據類型...）]RETURN數據類型{IS|AS}[說明部分]BEGIN 語句序列RETURN（表達式） [EXCEPTION例外處理程序]END[函數名]；例：根據員工號獲取該員工工資的查詢CREATEORREPLACEFUNCTIONget_sal(p_emp_noINemp.empno％TYPE)RETURNNUMBERISv_emp_salemp.sal％TYPE:＝0；BEGINSELECTsalINTOv_emp_salFROMempWHEREempno=p_emp_no;RETURN(v_emp_sal)；ExceptionWhenno_data_foundortoo_many_rowsthenDbms_output.put_line('發(fā)生系統(tǒng)錯誤');WhenothersthenDbms_output.put_line(sqlerrm);ENDget_sal；/例：編寫一個函數，計算幾個人的平均工資，并在函數體的循環(huán)過程中輸出結果。CREATEORREPLACEFUNCTIONaverage_sal(v_nINNUMBER(3))RETURNNUMBERISCURSORc_empISSELECTempno，salFROMemp；v_total_salemp.sal％TYPE:=0；v_counternumber;v_emp_noemp.empno%type;BEGINFORr_empINc_empLOOPEXITWHENc_emp％ROWCOUNT＞v_nORc_emp％NOTFOUND；v_total_sal:=v_total_sal+r_emp.sal；v_counter:=c_emp％ROWCOUNT；v_emp_no:=r_emp.empno；DBMS_OUTPUT.PUT_LINE(‘loop=’||v_counter||‘；Empno=’||v_emp_no)；ENDLOOP；RETURN(v_total_sal／v_counter)；ENDaverage_sal；/存儲過程與函數的區(qū)別存儲過程和函數的主要差別有兩個：一是返回值的方法不同二是調用方法不同（1）返回值的方法不同●存儲函數：有零個或多個參數，但不能有OUT參數。函數只返回一個值，靠RETURN子句返回。

●存儲過程：有零個或多個參數，過程不返回值，其返回值是靠OUT參數帶出來的。（2）調用方法不同調用過程的語句可以作為單獨的可執(zhí)行語句在PL/SQL塊中單獨出現。如：過程名（實際參數1，實際參數2...）；函數可以在任何表達式能夠出現的地方被調用，調用函數的語句不能作為可執(zhí)行語句單獨出現在PL/SQL塊中。如：變量名：=函數名（實際參數1，實際參數2...）存儲過程和函數中的出錯處理

存儲過程和函數就是一個PL/SQL塊，所以在過程函數體內應該考慮出錯處理。不管是哪種類型的出錯情況，只要在過程和函數體內考慮了出錯處理的方法，即使在運行過程中出現了錯誤，過程和函數都能成功地執(zhí)行，程序不會被中斷。如果在過程和函數體內忽略了出錯處理，過程和函數執(zhí)行時以交互方式通知用戶出錯，讓用戶自行處理，程序被中斷。例：根據給定的員工號，刪除該員工記錄。CREATEORREPLACEPROCEDUREfire_emp(p_emp_noINemp.empno％TYPE)ISinvalid_employeeEXCEPTION；（定義錯誤）BEGINDELETEFROMempWHEREempno=p_emp_no;IFSQL％NOTFOUNDTHENRAISEinvalid_employee；（觸發(fā)錯誤）ENDIF；COMMITWORK；EXCEPTIONWHENinvalid_employeeTHEN（處理錯誤）ROLLBACKWORK；INSERTINTOexception_table(line_nr,line)VALUES(1,‘Employeedoesnotexist.’)；WHENothersTHENDbms_output.put_line(sqlerrm);ENDfire_emp；/存儲過程和函數的管理

過程和函數的管理命令

例：刪除存儲過程FIRE_EMPSQL>DROPPROCEDUREFIRE_EMP;查看過程和函數的文檔信息

SQL>showerrors;存儲過程和函數的調用和測試

參數傳值一般采用位置對應法向形式參數傳值，要求實際參數與形式參數保持次序、類型、個數一致。例：從SQL*Plus命令中通過位置對應法調用HIRE_EMP過程。SQL>variablev_enamevarchar2(12)；SQL>EXECUTEhire_emp(9999，:v_ename)；注意：●如果形式參數是IN模式的參數，實際參數可以是一個具體的值，或一個有值的變量?！袢绻问絽凳荗UT模式的參數，實際參數必須是一個變量。當調用過程后，此變量就被賦值了。●如果形式參數是INOUT模式的參數，則實際參數必須是一個預先已經賦值的變量。執(zhí)行完過程后，該變量被重新賦值?？梢哉{用系統(tǒng)內置的DBMS_OUTPUT包中的過程輸出此變量的值，測試過程執(zhí)行的結果。

存儲過程和函數的調用方法

各種環(huán)境調用過程和函數的語法和例子

（1）在PL/SQL塊中如何調用●過程的調用例：從PL／SQL塊中調用過程QUERY_EMP。執(zhí)行過程后，輸出變量測試結果。DECLAREV_empnoemp.empno%type:=7654;V_enameemp.ename%type;V_salemp.sal%type;V_commm%type;BeginQuery_emp(v_empno,v_ename,v_sal,v_comm);Dbms_output.put_line(v_ename||''||v_sal||''||v_comm);/*測試結果*/End;/●函數的調用例：從一個PL／SQL塊中調用GET_SAL函數。DECLAREv_empnoNUMBER:＝7654；v_salNUMBER；BEGINv_sal:=get_sal(v_empno)；DBMS_OUTPUT.PUT_LINE(v_sal)；/*測試結果*/END；/在SQL*PLUS中如何調用●過程的調用例：從SQL*PLUS中調用過程QUERY_EMPSETserveroutputON/*激活DBMS_OUTPUT系統(tǒng)包*/ACCEPTp_emp_noPROMPT‘pleaseentertheemployeenumber：’/*接受員工號*/VARIABLEv_emp_nameVARCHAR2（14）；/*定義存放OUT參數輸出結果的變量*/VARIABLEv_emp_salNUMBER；VARIABLEv_emp_commNUMBER；EXECUTEquery_emp(&p_emp_no,：v_emp_name,：v_emp_sal,：v_emp_comm);EXECUTEDBMS_OUTPUT.PUT_LINE(‘Informationforemployee：’||TO_CHAR(&p_emp_no))；EXECUTEDBMS_OUTPUT.PUT_LINE(‘Thenameis：’||：v_emp_name)；EXECUTEDBMS_OUTPUT.PUT_LINE(‘thesalaryis：’||TO_CHAR(：v_emp_sal))；EXECUTEDBMS_OUTPUT.PUT_LINE(‘Thecommissionis：’||TO_CHAR(：v_emp_comm))；●函數的調用函數不能作為一條語句單獨出現，只能出現在別的過程中，作為別的過程的參數。例：調用函數get_salSQL>EXECUTEDBMS_OUTPUT.PUT_LINE('員工號7654的工資是：'||get_sal(7654));或者用SELECT語句查詢函數的結果。SQL>SELECTGET_SAL(7654)FROMDUAL；在PRO*C中如何調用過程例：從一個pro*C程序中調用過程FIRE_EMP。voidrun_fire_emp(){EXECSQLBEGINDECLARESECTION；intempno；EXECSQLENDDECLARESECTION；printf(‘＼nPleaseentertheemployeenumber：’)；scanf(‘％d’，&empno)；EXECSQLfire_emp(:empno)；/*在過程名前面加EXECSQL關鍵字*/return；}存儲過程和函數的安全性

存儲過程、函數及包是數據字典中的對象，因此它們有特殊的數據庫用戶所擁有。其他用戶在被授予了合適的權限以后也可以訪問這些對象。通過區(qū)分過程或函數的擁有者或使用者來進行安全性管理，這與它們存取的數據的安全性管理一樣。過程或函數的擁有者比其使用者有著較高的權限。特別地，擁有者需要具有直接操縱相關表或視圖的權限。而調用者只需要存取過程的權限。例：過程的安全性機制

假定表EMP是用戶personal的私有表，用戶Scott是開發(fā)者，最終用戶Green．現在要求Green只能通過Scott創(chuàng)建的過程HIRE_EMP存取emp表，該過程查詢或插入雇員記錄。在personal用戶環(huán)境下，為Scott用戶授予對EMP表的相應權限。SQL＞GRANTSELECT，INSERT，UPDATE，DELETEONempTOscott；Scott一旦創(chuàng)建完HIRE_EMP過程，給Green用戶授予對該過程的EXECUTE權限SQL＞GRANTEXECUTEONHIRE_empTOGreen；存儲過程和函數的優(yōu)點（1）提高數據的安全性與完整性利用權限來控制那些沒有足夠權限的用戶對數據庫的間接訪問。（2）改善操作性能●只做一次語法分析?！裰苯诱{用編譯代碼?！駵p少訪問數據庫的次數，降低網絡傳輸量（3）節(jié)省存儲量●只在數據庫中存儲代碼的一個備份●通過共享SQL改善管理?！駥^程的修改可以作用于多個應用?！襁^程利用了共享內存資源。（4）模塊化演講完畢，謝謝觀看！OracleERP主要模塊InvoicesARENGBOMWIPBillsand

RoutingsShipmentsECOSWork

OrdersGLAPPlanning(MRP)POMaterialRequirementsEnterprise

DemandVendorPOInterplantPOReceiptsWorkOrdersCustomersJournalEntriesJournalEntriesJournalEntriesVendorInvoicesVendorPaymentsTransportation

InformationBillsItemsOEINVInternalPaymentsCorporateJournalEntriesShipmentsOrdersFAMassAdditionJournalEntriesCOSTCOSTCOSTReceipts財務配銷HRPayroll制造人力資源財務系統(tǒng)模塊:Oracle總帳管理（GL）Oracle應付帳管理（AP）Oracle固定資產管理（FA）Oracle應收帳管理（AR）Oracle現金管理（CE）Oracle項目會計（PA）Oracle財務分析（OFA）分銷系統(tǒng)模塊:Oracle庫存管理（INV）Oracle采購管理（PUR）Oracle銷售定單管理（OE）Oracle銷售&市場管理（SM）Oracle銷售補償管理（SC）Oracle售后服務管理（SR）Oracle銷售分析（OSA）OracleERP模塊清單制造系統(tǒng)模塊:Oracle計劃管理（MPS/MRP）Oracle能力計劃管理（CAP）Oracle制造數據管理（BOM）Oracle車間生產管理（WIP）Oracle成本管理（CST）Oracle項目制造（PJM)Oracle流程制造(OPM)Oracle質量管理（QM)其他系統(tǒng)模塊:Oracle設備管理（EM）Oracle人事管理（HR）Oracle薪金管理（PAYROLL）Oracle系統(tǒng)管理（SYSTEMADMIN）Oracle預警（ALT）Oracle多維數據分析/商業(yè)智能系統(tǒng) （OLAP/BIS）Oracle桌面集成（ADI）OracleERP模塊清單Engineering工程資料管理系統(tǒng)BillofMaterials物料清單及制程管理系統(tǒng)CostManagement成本管理系統(tǒng)OrderEntry訂單管理系統(tǒng)MasterScheduling/MRP生產排程管理系統(tǒng)與物料需求計劃系統(tǒng)WorkInProcess生產管理系統(tǒng)Inventory庫存管理系統(tǒng)Purchasing采購管理系統(tǒng)Personnel人事管理系統(tǒng)Receivable應收賬款管理系統(tǒng)Payable應付賬款管理系統(tǒng)Assets固定資產管理系統(tǒng)GeneralLedger

總帳會計系統(tǒng)Capacity產能規(guī)劃系統(tǒng)OracleApplications37Oracle行業(yè)模型:離散制造供應鏈企業(yè)后臺管理客戶關系管理計劃尋源

制造

市場銷售支持企業(yè)管理返回項目到盈利財務管理到財務報告概念到發(fā)布聯系到解決合同到續(xù)簽預測到計劃采購到付款需求到制造(離散)訂單到收款庫存到履約問題到預防機會到訂單Oracle主要業(yè)務流程DesigntoReleaseForecasttoPlanPlantoScheduleScheduletobuildProcuretoPayOrdertoCashInventorytoFulfillmentCostQuality39概念到發(fā)布返回設計到發(fā)布預測到計劃計劃到排程排程到生產(離散)概念到獲利一級業(yè)務流程40預測到計劃返回供應商協(xié)同到供應商承諾客戶協(xié)同到達成一致預測預測到計劃計劃到排程排程到生產(離散)請求到接收–直接設計到發(fā)布一級業(yè)務流程41采購到支付返回分析到協(xié)議預測到計劃申請到接收–直接申請到接收–間接供應商退貨到貸項通知單供應商退貨到補貨驗收到接收接收到資產供應商發(fā)票到付款補貨信號費用報告到入帳一級業(yè)務流程42需求到制造(離散)返回一級業(yè)務流程返回計劃到排程排程到生產(離散)檢驗到處理設計到發(fā)布按訂單配置到發(fā)運按訂單裝配到發(fā)運一級業(yè)務流程43訂單到收款返回訂單到直發(fā)信貸審批到客戶揀貨到訂單到發(fā)運客戶退貨到退款客戶退貨到退換客戶發(fā)票-現金定制到訂單到發(fā)運裝配到訂單到發(fā)運

銷售線索到訂單訂單到發(fā)運訂單到直接采購發(fā)運收入到過帳訂單到客戶發(fā)票一級業(yè)務流程44庫存到履約返回申請到接收-直接排程到生產(離散)檢驗到處理產品標準成本到庫存再估價訂單到收款接收到庫存準確度補貨信號到補貨子分類帳到過帳倉庫布局到庫存估價計劃到排程一級業(yè)務流程客戶訂單CustomerPO銷售預測Forecast主需求計劃MDS主生產計劃MPS物料需求

計劃MRP請購作業(yè)PR采購作業(yè)PO

收料作業(yè)

Receiving工單開立WIP生產發(fā)料WIPIssue生產控制生產入庫WIPComplete出貨作業(yè)Shipping應付賬款應收賬款發(fā)票作業(yè)成本作業(yè)Oracle整合企業(yè)日常作業(yè)流程可承諾量查核ATP內部領用撿貨單雜項發(fā)料/退貨MiscIssue/Return發(fā)料/出貨Issue入庫異動ReceiptTransaction實際盤點PhysicalCounting標簽與序列號列印TagGeneration盤點過賬交貨預測與再訂購管理ForecastandReorder存貨管制規(guī)則-Min-max-ReorderPoint-Sub-inventoryreplenishment交貨/預定安排庫存評估/報表定義作業(yè)規(guī)定總帳更新定義作業(yè)流程組織/倉庫定義SetupOrganization/Subinventory檢驗Inspect登帳運送Deliver退貨Return供應商Supplier內部Internal成本Cost狀態(tài)Status目錄Catalog分類Category模板Template供應商Supplier內部Internal訂單處理自動請購余額更新調整庫存平衡表循環(huán)盤點CycleCounting-自動排程-手動排程OracleInventory(庫存管理系統(tǒng))庫存控制InventoryControl物料管理MaterialManagement收料Receipt聯接操作制程料品定義Item定義資源Resource選用零配件/料品成本累計成品FinishGood定義資源使用量ResourceRate新產品流程建立替代制程AlternateRouting定義制程Routing材料需求ItemRequirement新產品開發(fā)建立替代性物料清單AlternateBOM工單排程量產日排程實施設計變更單OracleBillofMaterials(物料清單及制程管理系統(tǒng))設計變更單ECO半成品Assembly建立物料清單BOM產品規(guī)格-型號、選項產生工單(或量產計劃)完成品入庫原物料耗用報表-工單成本-期間成本-成本差異-成本衡量-物料耗用-資源耗用組立裝配Assembly(選項)外包反溯扣帳Backflash工單發(fā)放Release發(fā)料Issue裝配完成工單完成WIPComplete制造資源耗用Resource-人工-機器設備-制造費用送貨/交貨外包交貨外包OutsideProcessing安排生產制程物料清單展開計劃工單手開工單銷售訂單OracleWorkInProcess(生產管理系統(tǒng))預測資料ForecastOracleSO客戶訂單其它系統(tǒng)客戶訂單需求報表&查詢主需求計劃MDS需求排程更新MDSUpdate詳細產能計劃物料需求計劃MRP工單WIP請購PRBOM計劃工單計劃采購單現有庫存量OnhandOraclePlanning(MPS/MRP管理系統(tǒng))主生產計劃MPS交易資訊Transaction定義配件成本報告采購收料POReceive定義成本計算規(guī)則成本累計CostRollup成本更新CostUpdate庫存值計算采購料品材料材料管理費用用料清單制程

成本元件-工作項目/活動Project/Active-料品Item-批次Lot-資源單位Uom-總價半成品庫存資源成本采購退貨POReturn公司間移轉OrganizationTransfer移動平均成本更新雜項收發(fā)MiscellaneousReceipt/Issue選擇模擬規(guī)則模擬凍結成本Frozen凍結成本OracleCostManagement(成本管理系統(tǒng))請購單處理收貨

(料)Receipt檢驗Inspect異動處理-收貨-運送-調撥退貨Return應付賬款對帳應計作業(yè)-暫記項目-憑證-月結報價單收集請求報價RFQ(選擇性項目)自動產生訂購單發(fā)放Release定義企業(yè)流程收料控制-核準及安全管制-交貨來源-采購預算管制-異動處理方法內部轉移InterOrgTransfer發(fā)票處理應付賬款成立APInvoice總帳科目更新核準Approve供應商挑選供應商交貨允收AcceptOraclePurchasing(采購管理系統(tǒng))MRP需求存貨狀況OnhandQty在制品(委外)OutProcess手開請購ManualPR開放界面Interface列印/EDI核準Approve各種采購單-標準式Standard-計劃式Planned-合并式Blanket-合約式Contract判退Reject選配件產品訂單輸入OrderEnter發(fā)票開立Invoice出貨管理Shipping退貨管理RMA退貨審核Approve貨品退回Return訂單完成重排程Re-schedule保留Reservation庫存余額更新未交訂單管理定義各項流程次序定義流程元件定義企業(yè)運作流程Workflow-彈定規(guī)則訂單處理流程-授信額度檢查-退貨管理-訂單排程訂單處理流程定義授信額度檢查CreditCheck例外管理Exception訂單保留Hold-自動Auto-手動Manual訂單解除管制HoldRelease訂價Price訂單審核確認客戶基本資料Customer線上查詢庫存余量-允交量ATP-保留量Reservation授信額度檢查CreditCheckOracleOrderEntry(訂單管理系統(tǒng))允交量查詢ATP訂單排程OrderSchedule出貨確認ShipConfirm檢貨Pick銷貨折讓Credit分派/收集預算資料建立預算預算作業(yè)Budget查詢