第23章 使用子程序和包

1、 掌握存儲(chǔ)子程序與數(shù)據(jù)字典的交互方式及如何從SQL語句中調(diào)用存儲(chǔ)子程序了解存儲(chǔ)子程序和局部子程序的區(qū)別掌握存儲(chǔ)子程序在Oracle8i和Oracle9i中的新特性。l子程序和包可以存儲(chǔ)在數(shù)據(jù)字典中（子程序是用命令CREATE OR REPLACE創(chuàng)建）l可以從其他PL/SQL語句塊中調(diào)用已創(chuàng)建的子程序。l子程序可以在語句塊的聲明段定義，以這種方式定義的子程序叫做局部子程序。l包則必須存儲(chǔ)在數(shù)據(jù)字典中，而不能是局部的。l當(dāng)使用命令CREATE OR REPLACE創(chuàng)建子程序時(shí)，該子程序就被存儲(chǔ)在數(shù)據(jù)字典中。l子程序的信息可以通過各種數(shù)據(jù)字典視圖來訪問。l局部子程序:是一個(gè)在PL/SQL語句塊的聲

2、明段中聲明的子程序。l子函數(shù)只在其聲明的語句塊中可見，其作用域從聲明點(diǎn)開始到該語句塊結(jié)束為止。其他語句塊不能調(diào)用該函數(shù)，因?yàn)樵摵瘮?shù)對(duì)其他語句塊來說是不可見的。l局部子程序也可以聲明為存儲(chǔ)子程序聲明段的一部分l局部子程序的位置：任何局部子程序都必須在聲明段的最后進(jìn)行聲明l預(yù)先聲明 ：局部PL/SQL子程序的名稱是標(biāo)識(shí)符，必須在被引用前聲明。l局部子程序也可以重載存儲(chǔ)子程序局部子程序該類子程序以編譯后生成的偽代碼形式p-code存儲(chǔ)在數(shù)據(jù)庫中。當(dāng)調(diào)用該類子程序時(shí)，不需進(jìn)行編譯即可運(yùn)行局部子程序被編譯為包含它的語句塊的一部分。如果其所在語句塊是匿名塊并需要多次運(yùn)行時(shí)，則該子程序就必須每次進(jìn)行編譯存儲(chǔ)

3、子程序可以從對(duì)該子程序具有EXECUTE特權(quán)的用戶所提交的任何語句塊中調(diào)用局部子程序只能從包含子程序的語句塊中調(diào)用由于存儲(chǔ)子程序與調(diào)用塊的相互隔離，調(diào)用塊具有代碼少，易于理解的特點(diǎn)。除此之外，子程序和調(diào)用塊還可以分開維護(hù)局部子程序和調(diào)用塊同處于一個(gè)塊內(nèi)，所以容易引起混淆。如果修改了調(diào)用塊的話，則該塊調(diào)用的子程序作為所屬塊的一部分也要重新編譯可以使用DBMS_SHARED_POOL.KEEP包過程來把編譯后p-code代碼存儲(chǔ)在共享池中。這種方式可以改善程序性能局部子程序自身不能存儲(chǔ)在共享池中獨(dú)立的存儲(chǔ)子程序不能進(jìn)行重載，但同一包內(nèi)的子程序可以重載同一塊中的局部子程序可以重載優(yōu)勢(shì)：l可以由多個(gè)數(shù)

4、據(jù)庫用戶共享隱含影響：l存儲(chǔ)子程序間的依賴性l包狀態(tài)的處理方法l運(yùn)行存儲(chǔ)子程序l包所需要的特權(quán)l(xiāng)依賴：數(shù)據(jù)字典記錄了存儲(chǔ)過程或函數(shù)所引用的所有Oracle對(duì)象。該過程或函數(shù)就依賴于這些存儲(chǔ)的對(duì)象。l自動(dòng)重編譯:如果一個(gè)依賴對(duì)象失效，PL/SQL引擎將在該對(duì)象再次被調(diào)用時(shí)對(duì)其重新進(jìn)行編譯。 l包的依賴性：包體的變化不會(huì)導(dǎo)致修改包頭。因此，其他依賴于該包頭的對(duì)象也不需要進(jìn)行重新編譯。如果該包頭有變化，則會(huì)使包體自動(dòng)失效，這是因?yàn)樵摪w依賴于包頭 。l如何確定無效 :通過數(shù)據(jù)字典在不斷地跟蹤對(duì)象間依賴關(guān) l當(dāng)?shù)谝淮螌?shí)例化一個(gè)包時(shí)，將從磁盤中讀入該包的偽代碼并將其放入系統(tǒng)全局工作區(qū)SGA的共享池中。l

5、包的運(yùn)行時(shí)狀態(tài)，即打包的變量和游標(biāo)，則存放在用戶全局區(qū)（UGA）的會(huì)話存儲(chǔ)區(qū)中。這就保證了每個(gè)會(huì)話都將有其運(yùn)行時(shí)狀態(tài)的副本。l可串行復(fù)用包和非串行復(fù)用包：PL/SQL 2.3版及更高版本允許程序員對(duì)包做可串行復(fù)用標(biāo)志。 可串行復(fù)用包非串行復(fù)用包運(yùn)行時(shí)狀態(tài)保存在SGA中，每次數(shù)據(jù)庫調(diào)用后都將該運(yùn)行時(shí)狀態(tài)釋放運(yùn)行時(shí)狀態(tài)保存在UGA中，其生存期與數(shù)據(jù)庫會(huì)話相同所用的最大內(nèi)存與同時(shí)使用該包的用戶數(shù)成正比所用的最大內(nèi)存與當(dāng)前登錄的用戶數(shù)成正比l存儲(chǔ)子程序和包都是數(shù)據(jù)庫字典中的對(duì)象，因而，它們屬于特殊的數(shù)據(jù)庫用戶或模式。如果用戶被授予了正確的特權(quán)，則它們就可以訪問這些對(duì)象。l當(dāng)創(chuàng)建一個(gè)存儲(chǔ)對(duì)象時(shí)，考慮到子

6、程序內(nèi)部的訪問可用性，特權(quán)和角色也開始起作用。l為了能夠?qū)Ρ磉M(jìn)行訪問，必須使用SELECT，INSERT，UPDATE和DELETE對(duì)象特權(quán)。GRANT語句把這些特權(quán)賦予數(shù)據(jù)庫用戶或角色。對(duì)于存儲(chǔ)子程序和包來說，相關(guān)的特權(quán)是EXECUTE。 emp EmployeeRaisedSalary 結(jié)果將被保存在UserA. Temp_table 圖 23-13 UserA 擁有的數(shù)據(jù)庫對(duì)象 Raise_Salary Temp_table UserA UserB emp EmployeeRaisedSalary 結(jié)果將保存在 UserA.temp_table 圖 23-14 UserA 和 UserB

7、 所擁有的 temp_table Raise_Salary temp_table UserA UserB UserB.temp_table 將不會(huì)被修改 temp_table l子程序使用顯式地（而不是通過角色）授權(quán)其所有者的特權(quán)而執(zhí)行。 l為了使通過角色授權(quán)的特權(quán)作用在存儲(chǔ)子程序和觸發(fā)器內(nèi)被使用，每次運(yùn)行過程時(shí)，必須對(duì)該特權(quán)進(jìn)行檢查。 lOracle8i提供了不同的外部引用解決方案。在調(diào)用者的權(quán)限子程序中，外部引用是通過調(diào)用者而不是所有者的特權(quán)設(shè)置而執(zhí)行的。調(diào)用者的權(quán)限程序是通過使用AUTHID子句而創(chuàng)建，該語句只適用于獨(dú)立子程序、包說明和對(duì)象類型說明。l在包內(nèi)部或?qū)ο箢愋椭械莫?dú)立子程序必須

8、都是調(diào)用者子程序或都是定義者子程序，而不能混合。 AUTHID的語法如下：的語法如下：CREATE OR REPLACE FUNCTION function_nameparameter_list RETURN return_typeAUTHID CURRENT_USER | DEFINER IS | ASfunction_body;CREATE OR REPLACE PROCEDURE procedure_nameparameter_listAUTHID CURRENT_USER | DEFINER IS | ASfunction_body;CREATE OR REPLACE PACKAGE

9、package_spec_nameAUTHID CURRENT_USER | DEFINER IS | ASpackage_spec;CREATE OR REPLACE TYPE type_nameAUTHID CURRENT_USER | DEFINER IS | AS OBJECTtype_spec;lPL/SQL 2.1版（Oracle 7的7.1版）及更高版本從SQL語句中調(diào)用子程序 l函數(shù)的使用受純度等級(jí)限制l從過程化語句中調(diào)用一個(gè)函數(shù)時(shí)，如果該函數(shù)有形參的話，可以使用其缺省值。然而，如果從SQL語句調(diào)用函數(shù)時(shí)，必須指定所有參數(shù)。l必須使用定位符（Positinal Notation

10、）,而不能使用命名符（Name Notation）。 lOracle8i提供了TRUST關(guān)鍵字，用來代替或補(bǔ)充純度等級(jí)參數(shù)。l從Oracle8i起，從DML語句中調(diào)用的函數(shù)既不可以從正在被該DML語句修改的數(shù)據(jù)庫表讀取數(shù)據(jù)，也不能對(duì)其進(jìn)行修改，但該函數(shù)可以更新其他表。 l共享池鎖定l包體長度的限制 l優(yōu)化參數(shù)lDBMS_SHARED_POOL包允許程序員把一個(gè)對(duì)象鎖定在共享池中。l當(dāng)該對(duì)象被鎖定后，除非由程序申請(qǐng)對(duì)其清除，否則無論共享池有多滿，也不管是否有程序訪問該對(duì)象，該對(duì)象將常駐在共享池中。l這種處理方法有利于提高程序的運(yùn)行效率，因?yàn)閺南到y(tǒng)的磁盤重新裝入對(duì)象要進(jìn)行大量讀寫操作。鎖定對(duì)象還有

11、助于最大限度地減少共享池的碎片。 l編譯器對(duì)包體長度的限制如下 ：lDiana樹中的節(jié)點(diǎn)數(shù)。在Oracle8i之前的版本中，Diana節(jié)點(diǎn)的最大數(shù)目是32K，Oracle8i以上版本將包體和類型體的限制擴(kuò)充到了64兆字節(jié)的容量。l編譯器生成的臨時(shí)中間變量的容量最多21K字節(jié)。l入口點(diǎn)的數(shù)量。一個(gè)包體最多可以有32K個(gè)入口點(diǎn)，入口點(diǎn)可以是過程或函數(shù)。l字符串的數(shù)量。PL/SQL對(duì)字符串的限制單位是232。 l使用DETERMINISTIC和PARALLEL_ENABLE關(guān)鍵字，PL/SQL編譯優(yōu)化器將會(huì)對(duì)調(diào)用PL/SQL函數(shù)進(jìn)行優(yōu)化。 l該關(guān)鍵字要放在函數(shù)的返回類型和IS或AS子句之間 。CRE

12、ATE OR REPLACE FUNCTION function_nameparameter_listRETURN return_typeDETERMINISTICPARALLEL_ENABLEIS|ASfunction_body;l通過訪問各種數(shù)據(jù)庫字典視圖來查詢子程序的信息。user_objects視圖包含了當(dāng)前用戶擁有的所有對(duì)象的信息。user_source視圖包含對(duì)象的源程序代碼。user_errors視圖則包含編譯錯(cuò)誤的信息。l預(yù)先聲明可以解決函數(shù)之間相互調(diào)用的問題。l子程序引用其他數(shù)據(jù)庫對(duì)象時(shí)，該子程序依賴于那個(gè)對(duì)象。如果修改了被引用對(duì)象，那么依賴對(duì)象的狀態(tài)將更改為無效，指出需要重新編譯。l在PL/