第23章 使用子程序和包

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

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

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

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

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

6、程序內(nèi)部的訪(fǎng)問(wèn)可用性，特權(quán)和角色也開(kāi)始起作用。l為了能夠?qū)Ρ磉M(jìn)行訪(fǎng)問(wèn)，必須使用SELECT，INSERT，UPDATE和DELETE對(duì)象特權(quán)。GRANT語(yǔ)句把這些特權(quán)賦予數(shù)據(jù)庫(kù)用戶(hù)或角色。對(duì)于存儲(chǔ)子程序和包來(lái)說(shuō)，相關(guān)的特權(quán)是EXECUTE。 emp EmployeeRaisedSalary 結(jié)果將被保存在UserA. Temp_table 圖 23-13 UserA 擁有的數(shù)據(jù)庫(kù)對(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子程序使用顯式地（而不是通過(guò)角色）授權(quán)其所有者的特權(quán)而執(zhí)行。 l為了使通過(guò)角色授權(quán)的特權(quán)作用在存儲(chǔ)子程序和觸發(fā)器內(nèi)被使用，每次運(yùn)行過(guò)程時(shí)，必須對(duì)該特權(quán)進(jìn)行檢查。 lOracle8i提供了不同的外部引用解決方案。在調(diào)用者的權(quán)限子程序中，外部引用是通過(guò)調(diào)用者而不是所有者的特權(quán)設(shè)置而執(zhí)行的。調(diào)用者的權(quán)限程序是通過(guò)使用AUTHID子句而創(chuàng)建，該語(yǔ)句只適用于獨(dú)立子程序、包說(shuō)明和對(duì)象類(lèi)型說(shuō)明。l在包內(nèi)部或?qū)ο箢?lèi)型中的獨(dú)立子程序必須

8、都是調(diào)用者子程序或都是定義者子程序，而不能混合。 AUTHID的語(yǔ)法如下：的語(yǔ)法如下：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語(yǔ)句中調(diào)用子程序 l函數(shù)的使用受純度等級(jí)限制l從過(guò)程化語(yǔ)句中調(diào)用一個(gè)函數(shù)時(shí)，如果該函數(shù)有形參的話(huà)，可以使用其缺省值。然而，如果從SQL語(yǔ)句調(diào)用函數(shù)時(shí)，必須指定所有參數(shù)。l必須使用定位符（Positinal Notation

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

11、助于最大限度地減少共享池的碎片。 l編譯器對(duì)包體長(zhǎng)度的限制如下 ：lDiana樹(shù)中的節(jié)點(diǎn)數(shù)。在Oracle8i之前的版本中，Diana節(jié)點(diǎn)的最大數(shù)目是32K，Oracle8i以上版本將包體和類(lèi)型體的限制擴(kuò)充到了64兆字節(jié)的容量。l編譯器生成的臨時(shí)中間變量的容量最多21K字節(jié)。l入口點(diǎn)的數(shù)量。一個(gè)包體最多可以有32K個(gè)入口點(diǎn)，入口點(diǎn)可以是過(guò)程或函數(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ù)的返回類(lèi)型和IS或AS子句之間 。CRE

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