[Oracle10G_R2]01.SQL 和 PL/SQL 特性
?
??? 透明數(shù)據(jù)加密和 XQuery支持是Oracle數(shù)據(jù)庫10g第2版中與SQL相關(guān)的兩個(gè)最突出的新特性，但還有其它新特性。
?
??? 這一部分涉及：
?
??? ● 透明數(shù)據(jù)加密
??? ● XML 查詢
??? ● 增強(qiáng)的 COMMIT
??? ● 錯誤事件記錄子句
??? ● WRAP 程序包
??? ● 條件編譯
??? ● 無限制的 DBMS 輸出
?
?
透明數(shù)據(jù)加密
?
??? 對于加密，許多用戶深感矛盾：他們既感興趣，又因意識密鑰管理的復(fù)雜性而感到慎重，如果處理不當(dāng)，則會導(dǎo)致設(shè)置的效率低下。加密和解密值還會帶來相關(guān)的性能開銷，這使得大部分應(yīng)用程序架構(gòu)師不太樂于接受該過程。結(jié)果是，很多系統(tǒng)設(shè)計(jì)根本沒有加密，只是構(gòu)筑了強(qiáng)大的外圍防護(hù)，如強(qiáng)大的口令和適當(dāng)?shù)氖跈?quán)方案。
?
??? 但是，請想象一下如果整個(gè)服務(wù)器被盜了，甚至只是磁盤被盜，這些磁盤可以裝配在具有相同操作系統(tǒng)的服務(wù)器上，然后其中的數(shù)據(jù)將被銷毀殆盡。或者有一個(gè)的 DBA 品行不端，在日常業(yè)務(wù)活動中惡意突破了外圍防護(hù)，然后將您所有重要的客戶信息洗劫一空。在這兩種情況下，如果所涉及的商業(yè)機(jī)構(gòu)是在加利福尼亞州（可能不久之后在美國的其他州），它們在法律上有責(zé)任將安全漏洞的情況通知給所有受到影響的客戶。
??? 在上述罕見（但確是事實(shí)）的情況中，認(rèn)證方案沒有實(shí)際意義。這就是為什么對于那些將安全作為頭等大事的機(jī)構(gòu)而言，透明數(shù)據(jù)加密(TDE)是一個(gè)如此有用的特性；它支持加密，同時(shí)將密鑰管理的復(fù)雜性交給數(shù)據(jù)庫引擎來處理。同時(shí)，它允許DBA在不必實(shí)際看到數(shù)據(jù)的情況下管理數(shù)據(jù)庫表。
?
??? 在Oracle數(shù)據(jù)庫10g第2版中使用TDE時(shí)，可以隨時(shí)地對表中的一列或多列進(jìn)行加密；只需將列定義為加密形式即可，不用編寫代碼。請記住，加密需要使用密鑰和算法對輸入值進(jìn)行加密。TDE 為特定的表生成單獨(dú)的密鑰。這種方法方便了密鑰管理卻也更易被他們竊取，所以數(shù)據(jù)庫提供了另一種密鑰——萬能密鑰——它可以在數(shù)據(jù)庫級別上設(shè)置。表密鑰是利用萬能密鑰進(jìn)行加密的，要獲得表密鑰就需要這個(gè)萬能密鑰。因此，對列進(jìn)行解密時(shí)需要萬能密鑰和表密鑰。（有關(guān)常規(guī)加密以及在 Oracle 中使用供應(yīng)程序包的進(jìn)一步探討，請參見Oracle雜志專欄“為您的數(shù)據(jù)資產(chǎn)加密”。）
?
??? 萬能密鑰存儲在數(shù)據(jù)庫外一個(gè)稱為“錢夾”的地方——默認(rèn)位置在 $ORACLE_BASE/admin/$ORACLE_SID/wallet。在概念上，它類似于下圖。
?
?
??? 在配置 TDE 之后——或者更明確地說是配置了錢夾和萬能密鑰之后——您可以使用它來保護(hù)數(shù)據(jù)值。要為表的一列加密，需要使用以下SQL：
?
create table accounts
(
acc_no?????? number?????? not null,
first_name?? varchar2(30) not null,
last_name??? varchar2(30) not null,
SSN????????? varchar2(9)???????????? ENCRYPT USING 'AES128',
acc_type???? varchar2(1)? not null,
folio_id???? number????????????????? ENCRYPT USING 'AES128',
sub_acc_type varchar2(30),
acc_open_dt? date???????? not null,
acc_mod_dt?? date,
acc_mgr_id?? number
)
?
??? 在這里，您在列 SSN 和 FOLIO_ID 上使用了 TDE，它們現(xiàn)在以加密方式存儲在表本身。但是，當(dāng)用戶從表中選擇時(shí)，她看到以明文表示的數(shù)據(jù)，因?yàn)樵跈z索過程中已經(jīng)完成了解密。如果磁盤被盜，則包含在表段中的信息仍然保持加密狀態(tài)。盜竊者需要表密鑰才能看到加密的值，但是要獲得表密鑰，他需要萬能密鑰，而萬能密鑰存儲在外部，因此無法獲得。
?
??? 注意列 SSN 和 FOLIO_ID 后面的子句，這些子句指定 ENCRYPT 使用 128 位高級加密標(biāo)準(zhǔn)。
?
??? 數(shù)據(jù)庫擁有預(yù)先配置的錢夾。要設(shè)置錢夾口令，可使用命令：
?
alter system set encryption key authenticated BY "topSecret";
?
??? 如果還未創(chuàng)建錢夾，該命令將先創(chuàng)建錢夾，然后將口令設(shè)置為“topSecret”（區(qū)分大小寫）。然后您就可以開始在表的創(chuàng)建和更改期間將加密用于列定義。
?
為外部表加密
?
??? 在以上示例中，我使用散列表為列加密。您還可以在外部表上使用 TDE。例如，如果您希望生成一個(gè)包含 ACCOUNTS 的數(shù)據(jù)的轉(zhuǎn)儲文件，以便發(fā)送到不同的地點(diǎn)，則可以使用簡單的 ENCRYPT 子句。
?
create table account_ext
organization external
(
type oracle_datapump
default directory dump_dir
location ('accounts_1_ext.dmp',
'accounts_2_ext.dmp',
'accounts_3_ext.dmp',
'accounts_4_ext.dmp')
)
parallel 4
as
select
ACC_NO,
FIRST_NAME,
LAST_NAME,
SSN?????????? ENCRYPT IDENTIFIED BY "topSecret",
ACC_TYPE,
FOLIO_ID????? ENCRYPT IDENTIFIED BY "topSecret",
SUB_ACC_TYPE,
ACC_OPEN_DT,
ACC_MOD_DT
from accounts;
?
??? 在文件 accounts_*_ext.dmp 中，SSN 和 FOLIO_ID 的值不會是明文，而是加密形式。如果您希望使用這些文件作為外部表，則必須提供 topSecret 作為口令以讀取這些文件。
在這里您可以看到，TDE 是訪問控制的理想補(bǔ)充（而不是替代）。
?
在 SQL 中查詢 XML
?
??? 長期以來，對于很多包含大量字符內(nèi)容的應(yīng)用程序的數(shù)據(jù)類型而言，XML 已成為事實(shí)上的標(biāo)準(zhǔn)。最近它也已成為其他應(yīng)用程序的的存儲方法，而不僅僅限于大量的內(nèi)容。
?
??? Oracle 從 Oracle9i 數(shù)據(jù)庫開始就提供 XML 與數(shù)據(jù)庫的集成。在該版本中，您可以使用很多不同的方法來查詢 XML 內(nèi)容。在 Oracle 數(shù)據(jù)庫 10g 第 2 版中，新的 XQuery 和 XMLTable 函數(shù)使查詢 XML 內(nèi)容變得更容易。（注意：本文將不全面討論 XQuery 的規(guī)范，有關(guān)背景知識，請閱讀 Oracle 雜志文章“XQuery：一種新的搜索方法”。）
?
XQuery
?
??? 首先，讓我們來看這兩種方法中較簡單的一種：XQuery。請看下面的示例：
?
SQL> xquery
2???? for $var1 in (1,2,3,4,5,6,7,8,9)
3???? let $var2 := $var1 + 1
4???? where $var2 < 6
5???? order by $var2 descending
6??? return $var2
? 7 /
?
Result Sequence
------------------
5
4
3
2
?
??? 新的 SQL 命令 xquery 表示一個(gè) XQuery 命令。請注意該命令：新語法模仿了 FOR ...IN ... 內(nèi)嵌視圖，該視圖是在 Oracle9i 數(shù)據(jù)庫中推出的。
?
??? XQuery 的一般結(jié)構(gòu)由縮略語 FLOWR（發(fā)音為“flower”）來描述，它代表 FOR、LET、ORDER BY、WHERE 和 RETURN。在以上的示例中，我們看到第 2 行定義了數(shù)據(jù)的來源，即從 1 到 9 的一系列數(shù)字。它可以是任何來源 — 一組標(biāo)量值或者 XML 數(shù)據(jù)的一個(gè)元素，由 FOR 子句指定。該行還指定一個(gè)變量來存取這些值 (var1)。在第 3 行中，另一個(gè)變量 var2 擁有的值是 var1 加 1，由 LET 子句指定。
?
??? 對于所有這些返回值，我們只關(guān)心 6 以下的值，這是由子句 WHERE 指定的。然后我們根據(jù) var2 的值以降序方式對結(jié)果集排序，如第 6 行中的 ORDER BY 子句所示。最后，利用 RETURN 子句將值返回給用戶。
?
??? 如果將該語法與常規(guī) SQL 語法相比較，則 RETURN、FOR、WHERE 和 ORDER BY 類似于 SELECT、FROM、WHERE 和 ORDER BY。LET 子句沒有對應(yīng)的 SQL 語句，但它可以在其他子句中指定。
?
??? 讓我們來看這種功能強(qiáng)大的新工具的一個(gè)實(shí)際應(yīng)用示例。首先，創(chuàng)建一個(gè)表，用于保存與一個(gè)帳戶持有者間的詳細(xì)通信信息。
?
create table acc_comm_log
(
acc_no number,
comm_details xmltype
);
?
??? 現(xiàn)在，向其中插入一些記錄。
?
insert into acc_comm_log
values
(
?? 1,
xmltype(
'<CommRecord>
<CommType>EMAIL</CommType>
<CommDate>3/11/2005</CommDate>
<CommText>Dear Mr Smith</CommText>
</CommRecord>')
)
/
?
insert into acc_comm_log
values
(
?? 2,
xmltype(
'<CommRecord>
<CommType>LETTER</CommType>
<CommDate>3/12/2005</CommDate>
<CommText>Dear Mr Jackson</CommText>
</CommRecord>')
);
?
insert into acc_comm_log
values
(
?? 3,
xmltype(
'<CommRecord>
<CommType>PHONE</CommType>
<CommDate>3/10/2005</CommDate>
<CommText>Dear Ms Potter</CommText>
</CommRecord>')
);
?
??? 現(xiàn)在您可以看到表中的記錄：
?
SQL> l
1 select acc_no,
2?????? XMLQuery(
3??????? 'for $i in /CommRecord
4???????? where $i/CommType != "EMAIL"
5???????? order by $i/CommType
6???????? return $i/CommDate'
7?????? passing by value COMM_DETAILS
8?????? returning content) XDetails
9 from acc_comm_log
10 /
?
ACC_NO XDETAILS
---------- ------------------------------
???????? 1
2 <CommDate>3/12/2005</CommDate>
3 <CommDate>3/10/2005</CommDate>
??
XMLTable
?
??? 另一個(gè)函數(shù) XMLTable 用于類似的目的，但是它象常規(guī)的 SQL 查詢一樣返回列。以下是其運(yùn)行情況。
1? select t.column_value
2? from acc_comm_log a,
3???????? xmltable (
4??????????? 'for $root in $date
5??????????? where $root/CommRecord/CommType!="EMAIL"
6??????????? return $root/CommRecord/CommDate/text()'
7?????????????? passing a.comm_details as "date"
8*??????? ) t
SQL> /
?
COLUMN_VALUE
---------------------
3/12/2005
3/10/2005
?
??? 此示例演示了如何將常規(guī)的 SQL 語句用于 XML 查詢所返回的 XML 表。查詢按照非常結(jié)構(gòu)化的 FLOWR 模式來指定命令。
?
XQuery 與 XMLTable 的對比
??? 既然您已經(jīng)了解了在常規(guī) SQL 查詢中使用 XML 的兩種方法，就讓我們來看這二種方法適用的情形。
?
??? 第一種方法 XQuery 允許您獲取 XMLType 形式的數(shù)據(jù)，在任何支持它的程序或應(yīng)用程序中都可以將其作為 XML 來處理。在您所看到的示例中，帳戶數(shù)據(jù)的結(jié)果輸出是 XML 格式，而您可以使用任何工具（不必是關(guān)系型工具）來處理和顯示這些數(shù)據(jù)。第二種方法 XMLTable 結(jié)合了常規(guī) SQL 和 XML 的功能。帳戶數(shù)據(jù)的結(jié)果輸出不是 XML 格式，而是關(guān)系型數(shù)據(jù)。
?
??? 注意兩個(gè)案例中的源代碼都是 XML，但是 XQuery 使用 XMLType 來表示 XML 格式的數(shù)據(jù)，而 XMLTable 將其表示為關(guān)系表，可以像常規(guī)表一樣進(jìn)行處理。這種功能非常適用于要輸出表的現(xiàn)有程序，它引入了 XML 的特性。
?
??? XML 在預(yù)先不太了解確切的數(shù)據(jù)結(jié)構(gòu)的場合中非常有用。在以上示例中，根據(jù)不同模式，通信記錄也不相同。如果是電子郵件，則屬性可能是接收方的電子郵件地址、回復(fù)地址、任何復(fù)本（cc:、bcc: 等等）、消息的文本等等。如果是電話呼叫，則屬性是所呼叫的電話號碼、號碼的類型（家庭、工作、移動電話等等）、應(yīng)答者、留下的語音郵件等等。如果您要設(shè)計(jì)一個(gè)包含所有可能的屬性類型的表，則它會包括很多列，并且極其冗長，造成讀取困難。但是，如果您只有一個(gè) XMLType 列，則可以將所有內(nèi)容填在那里,但仍然保持通信類型的獨(dú)特屬性。查詢?nèi)匀豢梢允褂煤唵蔚?SQL 接口，使應(yīng)用程序的開發(fā)變得輕而易舉。
?
??? 有關(guān) Oracle 的 XQuery 實(shí)施的更多信息，請?jiān)L問 OTN 上的 Oracle XQuery 頁。
增強(qiáng)的 COMMIT
?
??? 當(dāng)提交會話時(shí)，將刷新重做日志緩沖區(qū)，將其內(nèi)容存儲到磁盤上的聯(lián)機(jī)重做日志中。此過程確保在對數(shù)據(jù)庫進(jìn)行恢復(fù)操作時(shí)，可以根據(jù)需要利用重做日志中回放事務(wù)處理。
?
??? 但是有時(shí)您可能想對一部分受保證的恢復(fù)能力進(jìn)行調(diào)整，以獲得更好的性能。利用 Oracle 數(shù)據(jù)庫 10g 第 2 版，您現(xiàn)在可以控制如何將重做流寫入到聯(lián)機(jī)日志文件。您可以在執(zhí)行提交語句時(shí)控制這種行為，或者只需更改數(shù)據(jù)庫的默認(rèn)行為即可。
?
??? 讓我們來看提交語句是工作過程。在事務(wù)處理后，當(dāng)執(zhí)行 COMMIT 時(shí)，可以附帶一個(gè)子句：
?
COMMIT WRITE <option>
?
??? 其中 <option> 是影響重做流的部分。選項(xiàng) WAIT 是默認(rèn)行為。例如，您可以執(zhí)行：
?
COMMIT WRITE WAIT;
?
??? 此命令與 COMMIT 本身的效果相同。在重做流寫入到聯(lián)機(jī)重做日志文件之前，提交命令不會將控制權(quán)交還給用戶。如果您不希望等待，則可以執(zhí)行：
?
COMMIT WRITE NOWAIT;
?
??? 這樣，控制權(quán)立即返還給會話，甚至是在將重做流寫入聯(lián)機(jī)重做日志之前。
?
??? 當(dāng)執(zhí)行提交命令時(shí)，日志寫入器進(jìn)程將重做流寫入到聯(lián)機(jī)重做日志。如果您正在進(jìn)行一系列事務(wù)處理（如在批處理環(huán)境中），則可能不希望如此頻繁地進(jìn)行提交。當(dāng)然，最好的操作過程是更改應(yīng)用程序以減少提交數(shù)量；但這可能說起來容易做起來難。在這種情況下，您只需執(zhí)行以下的提交語句：
?
COMMIT WRITE BATCH;
?
??? 此命令將以批量方式將重做流寫入到日志文件中，而不是每次提交都執(zhí)行寫操作。在頻繁提交的環(huán)境中，您可以使用這種技術(shù)來減少日志緩沖區(qū)刷新。如果您希望立即寫入日志緩沖區(qū)，則可以執(zhí)行：
?
COMMIT WRITE IMMEDIATE;
?
??? 如果您數(shù)據(jù)庫默認(rèn)使用某種特定的提交行為，則可以執(zhí)行以下語句。
?
ALTER SYSTEM SET COMMIT_WRITE = NOWAIT;
?
??? 此命令將使數(shù)據(jù)庫默認(rèn)使用這一行為。您還可以使其成為會話級默認(rèn)行為：
?
ALTER SESSION SET COMMIT_WORK = NOWAIT;
?
??? 對于任何參數(shù)，如果完成設(shè)置后，則該參數(shù)在系統(tǒng)級執(zhí)行。如果有會話級的設(shè)置，則會話級設(shè)置優(yōu)先，而最后如果 COMMIT 語句后面有子句，則該子句優(yōu)先。
?
??? 此選項(xiàng)不可用于分布式事務(wù)處理。
記錄錯誤并繼續(xù)運(yùn)行：錯誤事件記錄子句
?
??? 假設(shè)您正試圖將表 ACCOUNTS_NY 的記錄插入到表 ACCOUNTS 中。表 ACCOUNTS 在 ACC_NO 列上有一個(gè)主鍵。可能 ACCOUNTS_NY 中的某些行與該主鍵沖突。嘗試使用一個(gè)常規(guī)的插入語句：
SQL> insert into accounts
2? select * from accounts_ny;
insert into accounts
*
ERROR at line 1:
ORA-00001:unique constraint (ARUP.PK_ACCOUNTS) violated
?
??? 表 ACCOUNTS_NY 中的記錄均未被裝載。現(xiàn)在，將錯誤事件記錄項(xiàng)打開，嘗試同樣的操作。首先，您需要創(chuàng)建一個(gè)表來保存由 DML 語句所拒絕的記錄。調(diào)用該表 ERR_ACCOUNTS。
exec dbms_errlog.CREATE_ERROR_LOG ('ACCOUNTS','ERR_ACCOUNTS')
?
??? 接下來，執(zhí)行前面的語句，并加入事件記錄子句。
SQL> insert into accounts
2? select * from accounts_ny
3? log errors into err_accounts
4? reject limit 200
5? /
?
6 rows created.
?
??? 注意，表 ACCOUNTS_NY 包含 10 行，但只有六行被插入；其他四行由于某種錯誤而被拒絕。要找出錯誤是什么，可查詢 ERR_ACCOUNTS 表。
SQL> select ORA_ERR_NUMBER$, ORA_ERR_MESG$, ACC_NO
2? from err_accounts;
?
ORA_ERR_NUMBER$?? ORA_ERR_MESG$?????????????????????????????????????? ACC_NO
---------------? --------------------------------------------------? ------
1? ORA-00001:unique constraint (ARUP.PK_ACCOUNTS) vi? 9997
olated
1? ORA-00001:unique constraint (ARUP.PK_ACCOUNTS)vi? 9998
olated
1? ORA-00001:unique constraint (ARUP.PK_ACCOUNTS) vi 9999
olated
1? ORA-00001:unique constraint (ARUP.PK_ACCOUNTS) vi 10000
olated
?
??? 請注意列 ORA_ERR_NUMBER$，它顯示在 DML 語句執(zhí)行期間所遇到的 Oracle 錯誤號，還有 ORA_ERR_MESG$，它顯示錯誤消息。在本例中，您可以看到四條記錄被丟棄是因?yàn)樗鼈兣c主鍵約束 PK_ACCOUNTS 相沖突。該表還捕獲表 ACCOUNTS 的所有列，包括列 ACC_NO。查看被拒絕的記錄，注意這些帳號已經(jīng)在表中存在，因此這些記錄由于 ORA-00001 錯誤而被拒絕。如果沒有錯誤事件記錄子句，則整個(gè)語句將會出錯，不會拒絕任務(wù)記錄。通過這個(gè)子句，只有無效的記錄被拒絕；其他所有記錄均得以接受。
從源頭保護(hù)代碼：WRAP 程序包
?
??? PL/SQL 程序單元經(jīng)常包含關(guān)于公司流程和商業(yè)秘密的非常敏感和機(jī)密的信息，這使得它們與表相類似，成為受保護(hù)的實(shí)體組。為防止未經(jīng)授權(quán)而查看源代碼的情況，我們要經(jīng)常使用 wrap 命令行實(shí)用程序，這將使程序變得很混亂。
?
??? 只有在創(chuàng)建 PL/SQL 腳本后才能調(diào)用 wrap；該實(shí)用程序?qū)⑤斎氲拿魑拇虬鼮橐粋€(gè)文件。但是，在某些情況下，您可能希望在 PL/SQL 代碼中動態(tài)生成包裝。在這種情況下，因?yàn)檫€不存在源文件，不能調(diào)用 wrap 實(shí)用程序。
?
??? 由于 Oracle 數(shù)據(jù)庫 10g 第 2 版提供了一個(gè)供應(yīng)程序包，您可以使用它創(chuàng)建代碼，并進(jìn)行打包。該程序包補(bǔ)充（而不是替代）了 wrap 實(shí)用程序。而后者仍然適合于希望使用命令行來快速打包大量源文件的情況。
?
??? 例如，假設(shè)您希望以打包形式創(chuàng)建簡單的過程 p1。
create or replace procedure p1 as
begin
null;
end;
?
??? 在 PL/SQL 單元中，您可以使用以下命令以打包方式動態(tài)地創(chuàng)建這一過程：
begin
dbms_ddl.create_wrapped
('create or replace procedure p1 as begin null; end;')
end;
/
?
??? 現(xiàn)在您希望確認(rèn)打包過程。您可以從字典中選擇源文本。
SQL> select text from user_source where name = 'P1';
?
Text
-----------------------------------------------------------------
procedure p1 wrapped
a000000
369
abcd
abcd
... and so on ...
?
??? 第一行 procedure p1 wrapped 是確認(rèn)以打包方式創(chuàng)建過程。如果您利用 DBMS_METADATA.GET_DDL() 函數(shù)來獲取該過程的 DDL，則仍然會看到源代碼已被打包。
?
??? 有時(shí)您可能會有略微不同的需求；例如，您可能要生成 PL/SQL 代碼，但不想創(chuàng)建過程。在這種情況下，您可以將其保存在一個(gè)文件或表中，以便以后執(zhí)行。但是因?yàn)橐陨戏椒▌?chuàng)建了過程，所以該方法在這里行不通。所以您需要在程序包中調(diào)用另一個(gè)函數(shù)：
SQL> select dbms_ddl.wrap
2????? ('create or replace procedure p1 as begin null; end;')
3? from dual
4 /
?
DBMS_DDL.WRAP('CREATEORREPLACEPROCEDUREP1ASBEGINNULL;END;')
----------------------------------------------------------------------
create or replace procedure p1 wrapped
a000000
369
abcd
abcd
... and so on ...
?
??? WRAP 函數(shù)的輸出是一個(gè)可傳遞的參數(shù)，它代表著 PL/SQL 代碼的打包輸出結(jié)果。該參數(shù)可以保存在純文件文件或表中，可以在以后執(zhí)行。如果您生成的代碼要在其他地方部署，并且必須要保證代碼的安全性，則這種方法很有用。
?
??? 如果您可以將所存儲代碼的全部文本作為一個(gè) varchar2 數(shù)據(jù)類型（大小限制為 32K）來傳遞，則這一方法可以正常工作。如果 PL/SQL 代碼超過 32K，則您必須使用一種略微不同的方法：接受一個(gè)集合變量作為輸入。
?
??? 在這里您可以使用一個(gè)供應(yīng)的數(shù)據(jù)類型：程序包 DBMS_SQL 中的 varchar2。這是一個(gè)集合數(shù)據(jù)類型 (TABLE OF VARCHAR2)，表的每個(gè)單元都接收多達(dá) 32K 的文本；可隨意增加該表所含的單元數(shù)，以滿足您的需要。例如，假設(shè)您必須包裝一個(gè)名為 myproc 的非常長的過程，其定義如下：
create or replace procedure myproc as
l_key VARCHAR2(200);
begin
l_key := 'ARUPNANDA';
end;
?
??? 當(dāng)然，這根本不是一個(gè)非常長的過程；但是為了示范起見，假設(shè)它很長。為了將其創(chuàng)建為打包形式，您要執(zhí)行以下的 PL/SQL 塊：
1? declare
2???? l_input_code??? dbms_sql.varchar2s;
3? begin
4???? l_input_code (1) := 'Array to hold the MYPROC';
5???? l_input_code (2) := 'create or replace procedure myproc as ';
6???? l_input_code (3) := '? l_key VARCHAR2(200);';
7???? l_input_code (4) := 'begin ';
8???? l_input_code (5) := '? l_key := ''ARUPNANDA'';';
9???? l_input_code (6) := 'end;';
10??? l_input_code (7) := 'the end';
11??? sys.dbms_ddl.create_wrapped (
12???????????? ddl???? => l_input_code,
13???????????? lb????? => 2,
14???????????? ub????? => 6
15???? );
16* end;
?
??? 在這里我們定義了一個(gè)變量 l_input_code 來保存輸入的明文代碼。在第 4 行到第 10 行中，我們用要打包的代碼來填充這些行。在本示例中，同樣為了簡單起見，我使用了非常短的行。實(shí)際上，您可能要使用非常長的行，其大小多達(dá) 32KB。同樣，我在數(shù)組中只使用了 7 個(gè)單元；實(shí)際上您可能要使用若干單元來填充全部代碼。
?
??? 第 11 到第 15 行表明我如何調(diào)用該過程，以便將該過程創(chuàng)建為打包形式。在第 12 行中，我將集合作為一個(gè)參數(shù) DDL 來傳遞。但是，在這里暫停一下 — 我已經(jīng)分配了一個(gè)注釋作為數(shù)組的第一個(gè)單元，可能用于文檔。但它不是有效的語法。同樣，我將另一個(gè)注釋分配給數(shù)組的最后一個(gè)單元 (7)，它也不是用于創(chuàng)建過程的有效語法。為了使包裝操作僅僅處理有效的行，我在第 13 和第 14 行中指定了存儲我們代碼的集合的最低 (2) 和最高 (6) 的單元。參數(shù) LB 表示數(shù)組的下界，在本示例中是 2，而 HB 是上界 (6)。
?
??? 使用這種方法，現(xiàn)在可以從您的 PL/SQL 代碼中以打包方式創(chuàng)建任意大小的過程。
PL/SQL 中的條件編譯：一次編寫，多次執(zhí)行
?
??? 你們中很多人曾經(jīng)使用過 C 語言，它支持編譯器指令的概念。在 C 程序中，根據(jù)相關(guān)編譯器的版本，特定變量的值有可能不同。
?
??? 在 Oracle 數(shù)據(jù)庫 10g 第 2 版中，PL/SQL 有一個(gè)類似的特性：現(xiàn)在可以提供預(yù)處理器指令，它們在編譯期間而不是在運(yùn)行時(shí)進(jìn)行求值。例如，讓我們創(chuàng)建一個(gè)非常簡單的返回字符串的函數(shù)。
1? create or replace function myfunc
2? return varchar2
3? as
4? begin
5??? $if $$ppval $then
6????? return 'PPVAL was TRUE';
7??? $else
8????? return 'PPVAL was FALSE';
9??? $end
10* end;
?
??? 注意第 5 行，您已經(jīng)使用預(yù)處理器指令為變量 ppval 求值。因?yàn)?ppval 是一個(gè)預(yù)處理器變量，而不是常規(guī)的 PL/SQL 變量，所以使用 $$ 標(biāo)志來指定它。同樣，為了編譯器能分辨自己只需在編譯期間處理這些行，你要用特殊的 $ 標(biāo)志來指定求值項(xiàng)，例如用 $if 代替 if。現(xiàn)在，利用變量 ppval 的不同值來編譯這個(gè)函數(shù)。
SQL> alter session set plsql_ccflags = 'PPVAL:TRUE';
?
Session altered.
?
??? 現(xiàn)在編譯該函數(shù)并執(zhí)行它。
SQL> alter function myfunc compile;
?
Function altered.
?
SQL> select myfunc from dual;
?
MYFUNC
-------------------------------------
PPVAL was TRUE
?
??? 在編譯期間 ppval 的值被設(shè)為 false。現(xiàn)在更改該變量的值并重新執(zhí)行該函數(shù)。
SQL> alter session set plsql_ccflags = 'PPVAL:FALSE';
?
Session altered.
?
SQL> select myfunc from dual;
?
MYFUNC
---------------------------------------------------------
PPVAL was TRUE
?
??? 雖然這里 ppval 的值在會話中是 FALSE，但函數(shù)沒有采用它；而是采用了在編譯期間所設(shè)置的值。現(xiàn)在，重新編譯該函數(shù)并執(zhí)行它。
SQL> alter function myfunc compile;
?
Function altered.
?
SQL> select myfunc from dual;
?
MYFUNC
---------------------------------------------------
PPVAL was FALSE
?
??? 在編譯期間，ppval 的值是 FALSE，而這就是所返回的值。
?
??? 那么您如何利用這個(gè)特性呢？有幾種可能性 — 例如，您可以將它用作一個(gè)調(diào)試標(biāo)志來顯示更多的消息，或者可以編寫一個(gè)程序，這個(gè)程序在各個(gè)平臺上進(jìn)行不同的編譯。因?yàn)榍笾凳窃诰幾g期間而不是在運(yùn)行時(shí)間內(nèi)完成的，運(yùn)行時(shí)效率得到顯著增強(qiáng)。
?
??? 當(dāng)您擁有相同的預(yù)處理器標(biāo)志（在所有將要編譯的函數(shù)中引用該標(biāo)志）時(shí)，以上示例運(yùn)行正常。但是如果您的每段代碼具有不同的標(biāo)志，情況會怎樣？例如，函數(shù) calculate_interest 可能將標(biāo)志 ACTIVE_STATUS_ONLY 設(shè)為 TRUE，而函數(shù) apply_interest 可能將標(biāo)志 FOREIGN_ACCOUNTS 設(shè)為 FALSE。為了利用相應(yīng)的標(biāo)志來編譯這些函數(shù)，您可以執(zhí)行：
alter function calculate_interest compile
plsql_ccflags = 'ACTIVE_STATUS_ONLY:TRUE'
reuse settings;
alter function apply_interest compile
plsql_ccflags = FOREIGN_ACCOUNTS:TRUE'
reuse settings;
?
??? 注意，這些設(shè)置都不是會話級的。子句 reuse settings 確保在以后重新編譯函數(shù)時(shí)使用相同的編譯器指令。
?
??? 讓我們來看這個(gè)新特性的另一個(gè)變體。除了條件變量的定義之外，您還可以在條件編譯中檢查程序包的靜態(tài)常量。例如，假設(shè)您希望基于一個(gè)布爾型打包常數(shù)來控制 PL/SQL 過程的調(diào)試輸出。首先創(chuàng)建程序包
create or replace package debug_pkg
is
debug_flag constant boolean := FALSE;
end;
?
??? debug_flag 是在代碼中確定條件邏輯的常數(shù)。現(xiàn)在您可以將代碼嵌入程序包，如下所示：
create or replace procedure myproc
as
begin
$if debug_pkg.debug_flag $then
dbms_output.put_line ('Debug=T');
$else
dbms_output.put_line ('Debug=F');
$end
end;
?
??? 注意，打包的常量被直接引用，沒有任何 $ 符號。在本案例中，不需要設(shè)置任何會話級或系統(tǒng)級的條件編譯參數(shù)。在編譯函數(shù)時(shí)，您也不需要傳遞任何額外的子句。要了解具體的工作過程，可執(zhí)行：
SQL> exec myproc
?
Debug=F
?
??? 因?yàn)楝F(xiàn)在 debug_pkg.debug_flag 的值是 FALSE，所以執(zhí)行該過程返回了預(yù)期的“F”。現(xiàn)在，更改常數(shù)值：
create or replace package debug_pkg
is
debug_flag constant boolean := TRUE;
end;
?
??? 然后再次執(zhí)行該過程：
SQL> exec myproc
?
Debug=T
?
??? 該過程獲該常量的值，即預(yù)期的“T”。注意這里有一個(gè)非常重要的區(qū)別 — 您不需要重新編譯過程；將自動獲取對常量的更改！
無限制的 DBMS 輸出
?
??? 還記得類似以下各行的令人討厭的錯誤嗎？
ERROR at line 1:
ORA-20000:ORU-10027:buffer overflow, limit of 1000000 bytes
ORA-06512:at "SYS.DBMS_OUTPUT", line 32
ORA-06512:at "SYS.DBMS_OUTPUT", line 97
ORA-06512:at "SYS.DBMS_OUTPUT", line 112
ORA-06512:at line 2
?
??? 這是由于供應(yīng)程序包 dbms_output 過去能夠處理的最大字符數(shù)量是 1 百萬字節(jié)。在 Oracle 數(shù)據(jù)庫 10g 第 2 版中，該限制已經(jīng)解除：現(xiàn)在最大輸出數(shù)量是不封頂?shù)摹Ｄ恍柰ㄟ^執(zhí)行以下命令，就可以將其設(shè)為“unlimited”
set serveroutput on
?
??? 以上語句的輸出結(jié)果如下：
SQL> show serveroutput
serveroutput ON size 2000 format WORD_WRAPPED
?
??? 注意輸出的最大默認(rèn)值過去是 2000 。在 Oracle 數(shù)據(jù)庫 10g 第 2 版中，該命令顯示以下結(jié)果：
SQL> show serveroutput
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED
?
??? 默認(rèn)值是 UNLIMITED。
?
??? 老版本的另一個(gè)不便之處是 dbms_output 所顯示的行的最大長度。以下是行的長度超過 255 字節(jié)時(shí)的一個(gè)典型錯誤消息。
?
ERROR at line 1:
ORA-20000:ORU-10028:line length overflow, limit of 255 chars per line
ORA-06512:at "SYS.DBMS_OUTPUT", line 35
ORA-06512:at "SYS.DBMS_OUTPUT", line 115
ORA-06512:at line 2
?
??? 在 Oracle 數(shù)據(jù)庫 10g 第 2 版中，行可以具有任意長度。