背景
結構化查詢語言(Structured Query Language，簡稱SQL)是用來訪問關係型資料庫一種通用語言，屬於第四代語言（4GL），其執行特點是非過程化，即不用指明執行的具體方法和途徑，而是簡單地調用相應語句來直接取得結果即可。顯然，這種不關注任何實現細節的語言對於開發者來說有著極大的便利。然而，有些複雜的業務流程要求相應的程式來描述，這種情況下4GL就有些無能為力了。PL/SQL的出現正是為了解決這一問題，PL/SQL是一種過程化語言，屬於第三代語言，它與C、 C++、Java等語言一樣關注於處理細節，可以用來實現比較複雜的業務邏輯。
主要特性
PL/SQL編輯器，功能強大——該編輯器具有語法加強、SQL和PL/SQL幫助、對象描述、代碼助手、編譯器提示、PL/SQL完善、代碼內容、代碼分級、瀏覽器按鈕、超連結導航、宏庫等許多智慧型特性，能夠滿足要求性最高的用戶需求。當您需要某個信息時，它將自動出現，至多單擊即可將信息調出。
集成調試器（要求Oracle 7.3.4或更高）——該調試器提供您所需要的全部特性：跳入（Step In）、跳過（Step Over）、跳出（Step Out）、異常時停止運行、斷點、觀察和設定變數、觀察全部堆疊等。基本能夠調試任何程式單元（包括觸發器和Oracle8 對象類型），無需作出任何修改。
PL/SQL完善器——該完善器允許您通過用戶定義的規則對SQL和PL/SQL代碼進行規範化處理。在編譯、保存、打開一個檔案時，代碼將自動被規範化。該特性提高了您編碼的生產力，改善了PL/SQL代碼的可讀性，促進了大規模工作團隊的協作。
SQL 視窗——該視窗允許您輸入任何SQL語句，並以柵格形式對結果進行觀察和編輯，支持按範例查詢模式，以便在某個結果集合中查找特定記錄。另外，還含有歷史快取，您可以輕鬆調用先前執行過的SQL語句。該SQL編輯器提供了同PL/SQL編輯器相同的強大特性。
命令視窗——使用PL/SQL Developer 的命令視窗能夠開發並運行SQL腳本。該視窗具有同SQL*Plus相同的感觀，另外還增加了一個內置的帶語法加強特性的腳本編輯器。這樣，您就可以開發自己的腳本，無需編輯腳本/保存腳本/轉換為SQL*Plus/運行腳本過程，也不用離開PL/SQL Developer集成開發環境。
報告——PL/SQL Developer提供內置的報告功能，您可以根據程式數據或Oracle字典運行報告。PL/SQL Developer本身提供了大量標準報告，而且您還可以方便的創建自定義報告。自定義報告將被保存在報告檔案中，進而包含在報告選單內。這樣，運行您自己經常使用的自定義報告就非常方便。
您可以使用Query Reporter免費軟體工具來運行您的報告，不需要PL/SQL Developer，直接從命令行運行即可。
工程——PL/SQL Developer內置的工程概念可以用來組織您的工作。一個工程包括源檔案集合、資料庫對象、notes和選項。PL/SQL Developer允許您在某些特定的條目集合範圍之內進行工作，而不是在完全的資料庫或架構之內。這樣，如果需要編譯所有工程條目或者將工程從某個位置或資料庫移動到其他位置時，所需工程條目的查找就變得比較簡單，
To-Do條目——您可以在任何SQL或PL/SQL源檔案中使用To-Do條目快速記錄該檔案中那些需要進行的事項。以後能夠從To-Do列表中訪問這些信息，訪問操作可以在對象層或工程層進行。
對象瀏覽器——可配置的樹形瀏覽能夠顯示同PL/SQL開發相關的全部信息，使用該瀏覽器可以獲取對象描述、瀏覽對象定義、創建測試腳本以便調試、使能或禁止觸發器或約束條件、重新編譯不合法對象、查詢或編輯表格、瀏覽數據、在對象源中進行文本查找、拖放對象名到編輯器等。
此外，該對象瀏覽器還可以顯示對象之間的依存關係，您可以遞歸的擴展這些依存對象（如包參考檢查、瀏覽參考表格、圖表類型等）。
性能最佳化——使用PL/SQL Profiler，可以瀏覽每一執行的PL/SQL代碼行的時序信息（Oracle8i或更高），從而最佳化您SQL和PL/SQL的代碼性能。
更進一步，您還可以自動獲取所執行的SQL語句和PL/SQL程式統計信息。該統計信息包括CPU使用情況、塊I/O、記錄I/O、表格掃描、分類等。
HTML指南——Oracle目前支持HTML格式的線上指南。您可以將其集成到PL/SQL Developer工作環境中，以便在編輯、編譯出錯或運行時出錯時提供內容敏感幫助。
非PL/SQL對象——不使用任何SQL，您就可以對表格、序列、符號、庫、目錄、工作、佇列、用戶和角色進行瀏覽、創建和修改行為。PL/SQL Developer提供了一個簡單易用的窗體，只要將信息輸入其中，PL/SQL Developer就將生成相應的SQL，從而創建或轉換對象。
模板列表——PL/SQL Developer的模板列表可用作一個實時的幫助組件，協助您強制實現標準化。只要點擊相應的模板，您就可以向編輯器中插入標準的SQL或PL/SQL代碼，或者從草稿出發來創建一個新程式。
查詢構建器——圖形化查詢構建器簡化了新選擇語句的創建和已有語句的修改過程。只要拖放表格和視窗，為區域列表選擇專欄，基於外部鍵約束定義联合表格即可。
比較用戶對象——對表格定義、視圖、程式單元等作出修改後，將這些修改傳遞給其他資料庫用戶或檢查修改前後的區別將是非常有用的。這也許是一個其他的開發環境，如測試環境或製作環境等。而比較用戶對象功能則允許您對所選對象進行比較，將不同點可視化，並運行或保存套用必要變動的SQL腳本。
導出用戶對象——該工具可以導出用戶所選對象的DDL（數據定義語言）語句。您可以方便的為其他用戶重新創建對象，也可以保存檔案作為備份。
工具——PL/SQL Developer為簡化日常開發專門提供了幾種工具。使用這些工具，您可以重新編譯全部不合法對象、查找資料庫源中文本、導入或導出表格、生成測試數據、導出文本檔案、監控dbms_alert和dbms_pipe事件、瀏覽會話信息等。
授權——大多數開發環境中，您不希望所有資料庫都具備PL/SQL Developer的全部功能性。例如，資料庫開發中您可以允許PL/SQL Developer的全部功能性，而資料庫測試中您可以僅允許數據查詢/編輯和對象瀏覽功能，而資料庫製作中您甚至根本不希望PL/SQL Developer訪問。利用PL/SQL Developer授權功能，您可以方便的定義特定用戶或規則所允許使用的功能。
外掛程式擴展——可以通過外掛程式對PL/SQL Developer功能進行擴展。Add-ons頁面提供外掛程式可以免費下載。Allround Automations或其他用戶均可提供外掛程式（如版本控制外掛程式或plsqldoc外掛程式）。如果您具備創建DLL的程式語言，您還可以自己編寫外掛程式。
多執行緒IDE——PL/SQL Developer是一個多執行緒IDE。這樣，當SQL查詢、PL/SQL程式、調試會話等正在運行時，您依然可以繼續工作。而且，該多執行緒IDE還意味著出現編程錯誤時不會中止：您在任何時間都可以中斷執行或保存您的工作。
易於安裝——不同於SQL*Net，無需中間件，也無需資料庫對象安裝。只需點擊安裝程式按鈕，您就可以開始安裝從而使用軟體了。
過程與函式
PL/SQL中的過程和函式與其他語言的過程和函式一樣，都是為了執行一定的任務而組合在一起的語句。過程無返回值，函式有返回值。其語法結構為：
過程：Create or replace procedure procname(參數列表) as PL/SQL語句塊
函式：Create or replace function funcname(參數列表) return 返回值 as PL/SQL語句塊
為便於理解，舉例如下：
問題：假設有一張表t1，有f1和f2兩個欄位，f1為number類型，f2為varchar2類型，要往t1里寫兩條記錄，內容自定。
Create or replace procedure test_procedure as
V_f11 number :=1; /*聲明變數並賦初值*/
V_f12 number :=2;
V_f21 varchar2(20) :="first";
V_f22 varchar2(20) :="second";
Begin
Insert into t1 values (V_f11, V_f21);
Insert into t1 values (V_f12, V_f22);
End test_procedure; /*test_procedure可以省略*/
至此，test_procedure存儲過程已經完成，經過編譯後就可以在其他PL/SQL塊或者過程中調用了。函式與過程具有很大的相似性，此處不再詳述。
游標
游標的定義為：用游標來指代一個DML SQL操作返回的結果集。即當一個對資料庫的查詢操作返回一組結果集時，用游標來標註這組結果集，以後通過對游標的操作來獲取結果集中的數據信息。這裡特別提出遊標的概念，是因為它在PL/SQL的編程中非常的重要。定義游標的語法結構如下：
cursor cursor_name is SQL語句;
在本文第一段代碼中有一句話如下：
cursor c_emp is select * from employee where emp_id=3;
其含義是定義一個游標c_emp，代表employee表中所有emp_id欄位為3的結果集。當需要操作該結果集時，必須完成三步：打開游標、使用fetch語句將游標里的數據取出、關閉游標。
游標用來處理從資料庫中檢索的多行記錄（使用SELECT語句）。利用游標，程式可以逐個地處理和遍歷一次檢索返回的整個記錄集。
為了處理SQL語句，Oracle將在記憶體中分配一個區域，這就是上下文區。這個區包含了已經處理完的行數、指向被分析語句的指針，整個區是查詢語句返回的數據行集。游標就是指向上下文區句柄或指針。
顯式游標
顯示游標被用於處理返回多行數據的SELECT 語句，游標名通過CURSOR….IS 語句顯示地賦給SELECT 語句。
在PL/SQL中處理顯示游標所必需的四個步驟：
1）聲明游標；CURSOR cursor_name IS select_statement
2）為查詢打開游標；OPEN cursor_name
3）取得結果放入PL/SQL變數中；
FETCH cursor_name INTO list_of_variables;
FETCH cursor_name INTO PL/SQL_record;
4）關閉游標。CLOSE cursor_name
注意：在聲明游標時，select_statement不能包含INTO子句。當使用顯示游標時，INTO子句是FETCH語句的一部分。
隱式游標
所有的隱式游標都被假設為只返回一條記錄。
使用隱式游標時，用戶無需進行聲明、打開及關閉。PL/SQL隱含地打開、處理，然後關掉游標。
例如：
…….
SELECT studentNo,studentName
INTO curStudentNo,curStudentName
FROM StudentRecord
WHERE name=’gg’;
上述游標自動打開，並把相關值賦給對應變數，然後關閉。執行完後，PL/SQL變數curStudentNo,curStudentName中已經有了值。
PL/SQL的變數
就像其他的程式語言一樣，變數是在程式中出現最頻繁的名詞，在PL/SQL中的學習中首先需要了解變數的一些基本概念和使用方法。
PL/SQL程式包括了四個部分，在四個部分中，聲明部分主要用來聲明 變數並且初始化變數，在執行部分可以為變數賦新值，或者在 表達式中 引用變數的值，在 異常處理部分同樣可以按執行部分的方法使用變數。另外，在PL/SQL程式使用時可以通過參數 變數把值傳遞到PL/SQL塊中，也可以通過輸出變數或者參數變數將值傳出PL/SQL塊。
在定義變數、 常量 標識符時需要注意下面的一些基本規則：
⒈定義的 標識符名稱應該遵循命名規則，在後面將會提到主要的命名規則；
⒉在聲明 常量和 變數的時候可以為其設定初始化值，也可以強制設定not null；
⒊可以使用 賦值運算符（：=）或DEFAULT 保留字來初始化 標識符，為標識符賦初始值；
⒋在聲明 標識符時，每行只能聲明一個標識符。
在PL/SQL中主要使用下面三種類型的 變數（或者 常量）：
⒈簡單 變數；
⒉複合（組合） 變數；
⒊ 外部變數。
三種 變數分別用於存放不同特性的數據。
PL/SQL的基本語法
在寫PL/SQL語句時，必須遵循一些基本的語法，下面是PL/SQL程式代碼的基本語法要求：
⒈語句可以寫在多行，就像SQL語句一樣；
⒉各個關鍵字、欄位名稱等等，通過空格分隔；
⒊每條語句必須以分號結束，包括PL/SQL結束部分的END關鍵字後面也需要分號；
⒋ 標識符需要遵循相應的命名規定；
⑴名稱最多可以包含30個字元；
⑵不能直接使用 保留字，如果需要，需要使用雙引號括起來；
⑶第一個 字元必須以字母開始；
⑷不要用資料庫的表或者科學計數法表示；
還有一些語法相關的規則：
⒈在PL/SQL程式中出現的字元值和日期值必須用單引號括起；
⒉數字值可以使用簡單數字或者科學計數法表示；
⒊在程式中最好養成添加注釋的習慣，使用注釋可以使程式更清晰，使開發者或者其他人員能夠很快的理解程式的含義和思路。在程式中添加注釋可以採用：
⑴/*和*/之間的多行注釋；
⑵以--開始的單行注釋。
過程，函式與包
過程:執行特定操作
函式:用於返回特定數據
1過程
語法:create [orreplace] procedure procedure_name(argument1 [model]datatype1,argment2 [mode2],...)
is [as]
pl/sql block;
1.建立過程:不帶任何參數
create or replaceprocecdure out_time
is
begin
dbms_output.put_line(systimestemp);
end;
2.調用過程
set serveroutputon
exec out_time
set serveroutputon
call out_time();
3.建立過程:帶有IN參數
create or replaceprocedure add_employee
(eno number,namevarchar2,sal number,job varchar2 default 'clerk',dno number)
is
e_integrityexception;
pragmaexception_init(e_integrity,-2291);
begin
insert intoimp(empno,ename,sal,job,deptno) valres(eno,name,sal,job,dno);
exception
whendup_val_on_index then
raise_application_error(-20000,'雇員號不能重複');
whene_integrity then
raise_application_error(-20001,'部門不存在');
end;
exec add_employee(1111,'clark',2000,'manager',10)
4.建立過程:帶有OUT參數
create or replaceprocedure qry_employee
(eno number,name outvarchar2,salary out number)
is
begin
selectename,sal into name,salary from emp where empno=eno;
exception
whenno_date_found then
raise_application_error(-20000,'該雇員不存在');
end;
當在應用程式中調用該過程時,必須要定義變數接受輸出參數的數據
sql>var name varchar2(10)
var salary number
exec qry_employee(7788,:name,:salary)
print name salary
5.建立過程:帶有INOUT參數(輸入輸出參數)
create or replaceprocedure compute
(num1 in outnumber,num2 in out number)
is
v1number;
v2number;
begin
v1:num1/num2;
v2:mod(num1,num2);
num1:=v1;
num2:=v2;
end;
sql>var n1 number
var n2 number
exec :n1:=100
exec :n2:=30
exec ecmpute(:n1,:n2)
print n1 n2
6.為參數傳遞變數和數據
位置傳遞,名稱傳遞,組合傳遞三種
1.位置傳遞:在調用子程式時按照參數定義的順序為參數指定相應的變數或數值
exec add_dept(40,'sales','new york');
exec add_dept(10);
2.名稱傳遞:在調用子程式時指定參數名,並使用關聯符號=>為其提供相應的數值或變數
execadd_dept(dname=>'sales',dno=>50);
exec add_dept(dno=>30);
3.組合傳遞:同時使用位置傳遞和名稱傳遞
exec add_dept(50,loc=>'new york');
execadd_dept(60,dname=>'sales',loc=>'newyork');
7.查看過程原代碼
oracle會將過程名,原始碼以及其執行代碼存放到數據字典中.執行時直接按照其執行代碼執行
可查詢數據字典(user_source)
select textfrom user_source where;
刪除過程
dropprocedure add_dept;
2函式
用於返回特定函式
語法:create [orreplace] function function_name
(argument1 [mode1] datatype1,
argument2 [mode2] datatype2,
.....)
returndatatype --函式頭部必須要帶有RETURN子句,至少要包含一條RETURN語句
is|as pl/sql block;
1.建立函式:比帶任何參數
create or replacefunction get_user
return varchar2
is
v_uservarchar2(100);
begin
selectusername into v_user from user_users;
returnv_user;
end;
2.使用變數接受函式返回值
sql>var v1 varchar2(100)
exec :v1:=get_user
print v1
在SQL語句中直接調用函式
selectget_user from d l;
使用DBMS_OUTPUT調用函式
setserveroutput on
execdbms_output.put_line('當前資料庫用戶:'||ger_user)
3.建立函式:帶有IN參數
create orreplace function get_sal(name in varchar2)
returnnumber
as
v_sal emp.sal%type;
begin
select sal into v_sal from emp where upper(ename)=upper(name);
return v_sal;
exception
when no_data_found then
raise_application_error(-20000,'該雇員不存在');
end;
4.建立函式:帶有out參數
create or replacefunction get_info(name varchar2,title out varchar2)
return varchar2
as
deptnamedept.dname%type;
begin
selecta.job,b.dname into title,deptname from emp a,dept b anda.deptno=b.deptno
andupper(a.ename)=upper(name);
returndeptname
exception
whenno_data_found then
raise_application_error(-20000,'該雇員不存在');
end;
sql>var job varchar2(20)
var dname varchar2(20)
exec :dname:=get_info('scott',:job)
print danme job
5.建立函式:帶有INOUT參數
create or replacefunction result(num1 number,num2 in out number)
return number
as
v_resultnumber(6);
v_remaindernumber;
begin
v_result:=num1/num2;
v_remainder:=mod(num1,num2);
num2:=v_remainder;
returnv_result;
exception
whenzero_divide then
raise_application_error(-20000,'不能除0');
end;
sql>var result1 number
var result2 number
exec :result2:=30
exec :result1:=result(100,:result2)
print result result2
6.函式調用限制
SQL語句中只能調用存儲函式(伺服器端),而不能調用客戶端的函式
SQL只能調用帶有輸入參數,不能帶有輸出,輸入輸出函式
SQL不能使用PL/SQL的特有數據類型(boolean,table,record等)
SQL語句中調用的函式不能包含INSERT,UPDATE和DELETE語句
7.查看函式院原始碼
oracle會將函式名及其原始碼信息存放到數據字典中user_source
set pagesize 40
select text fromuser_source where;
8.刪除函式
drop functionresult;
3管理子程式
1.列出當前用戶的子程式
數據字典視圖USER_OBJECTS用於顯示當前用戶所包含的所有對象.(表,視圖,索引,過程,函式,包)
sql>col object_name format a20
select object_name,created,status from user_objects whereobject_type in ('procedure','function')
2.列出子程式原始碼
select text fromuser_source where;
3.列出子程式編譯錯誤
使用SHOWERRORS命令確定錯誤原因和位置
show errorsprocedure raise_salary
使用數據字典視圖USER_ERRORS確定錯誤原因和位置
col text formata50
selectline||'/'||position as "line/col",text error from user_errors wherename="raise_salary";
4.列出對象依賴關係
使用數據字典視圖USER_DEPENDENCIES確定直接依賴關係
select name,typefrom user_dependencies where referenced_name="emp";
使用工具視圖DEPTREE和IDEPTREE確定直接依賴和間接依賴關係
先運行SQL腳本UTLDTREE.SQL來建立這兩個視圖和過程DEPTREE_FILL,然後調用DEPTREE_FILL填充這兩個視圖
sql>@%oracle_home%\rdbms\admin\utldtree
exec deptree_fill('TABLE','scott','emp')
執行後會將直接或間接依賴於SCOTT.EMP表的所有對象填充到視圖DEPTREE和IDEPTREE中.
select nested_level,name,type from deptree;
select * from ideptree
5.重新編譯子程式
當修改了被引用對象的結構時,就會將相關依賴對象轉變為無效(INVALID)狀態。
alter table emp addremark varchar2(10);
selectobject_name,object_type from user_objects wherestatus="invalid";
為了避免子程式的運行錯誤,應該重新編譯這些存儲對象
alter procedureadd_employee compile;
alter view dept10compile;
alter functionget_info compile;
開發包
包用於邏輯組合相關的PL/SQL類型,項和子程式,由包規範和包體組成
1.建立包規範:包規範是包與應用程式之間的接口,用於定義包的公用組件,包括常量,變數,游標,過程,函式等
create [or replace]package package_name
is|as
p lic type and item declarations
s program specificationsend package_name;
create or replacepackage emp_package is
g_deptnonumber(3):=30;
procedureadd_employee(eno number,name varchar2,salary number,dno numberdefault g_deptno);
procedurefire_employee(eno number);
functionget_sal(eno number) return number;
end emp_package;
2.建立包體:用於實現包規範所定義的過程和函式
create [or replace]package body package_name
is|as
private type and item declarations
s program bodies
endpackage_name;
create or repalce package body emp_package is
functionvalidate_deptno(v_deptno number)
return boolean
is
v_temp int;
begin
select 1 into v_temp from dept where deptno=v_deptno;
return tr;
exception
when no_date_found then
return false;
end;
procedure add_employee(eno number,name varchar2,salary number,dnonumber default g_deptno)
is
begin
if validate_deptno(dno) then
insert into emp(empno,ename,sal,deptno)vals(eno,name,salsry,dno);
else
raise_application_error(-20010,'不存在該部門');
end if;
exception
when dup_val_on_index then
raise_application_error(-20012,'該雇員已存在');
end;
procedure fire_employee(eno number) is
begin
delete from emp where empno=eno;
if sql%notfound then
raise_application_error(-20012,'該雇員不存在');
end if;
end;
functionget_sal(eno number) return number
is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=eno;
return v_sal;
exception
whenno_data_found then
raise_application_error(-20012,'該雇員不存在');
end;
end emp_package;
3.調用包組件
3.1在同一個包內調用包組件
create or replacepackage body emp_package is
procedure add_employee(eno number,name va har2,salary number,dnonumber default g_deptno)
is
begin
ifvalidate_deptno(dno) then
insert into emp(empno,ename,sal,deptno)vals(eno,name,salary,dno);
else
raise_application_error(-20010,'該部門不存在')
end if;
exception
when dup_val_on_index then
raise_application_error(-20011,'該雇員已存在')
end;
.........
3.2調用包公用變數
execemp_package.g_deptno:=20
3.3調用包公用過程
execemp_package.add_employee(1111,'mary',2000)
3.4調用包公用函式
var salarynumber
exec:salary:=emp_package.get_sal(7788)
print salary
3.5以其他用戶身份調用包公用組件
connsystem/manager
execscott.emp_package.add_employee(1115,'scott',1200)
execscott.emp_package.fire_employee(1115)
3.6調用遠程資料庫包的公用組件
execemp_package.add_employee@orasrv(1116,'scott',1200)
4.查看原始碼:存放在數據字典USER_SCOURCE中
select text fromuser_source where and type="package";
5.刪除包
drop packageemp_package;
6.使用包重載
重載(overload)是指多個具有相同名稱的子程式
1.建立包規範
同名的過程和函式必須具有不同的輸入參數,同名函式返回值的數據類型必須完全相同
create or replacepackage overload is
functionget_sal(eno number) return number;
functionget_sal(name varchar2) return number;
procedurefile_employee(eno number);
procedurefile_employee(name varchar2);
end;
2.建立包體
必須要給不同的重載過程和重載函式提供不同的實現代碼
create or replacepackage body overload is
function get_sal(eno number) return number
is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=eno;
return v_sal;
exception
when no_data_found then
raise_application_error(-20020,'該雇員不存在');
end;
function get_sal(name varchar2) return number
is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where upper(ename)=upper(name);
return v_sal;
exception
when no_data_found then
raise_application_error(-20020,'該雇員不存在');
end;
procedure fire_employee(eno number) is
begin
delete from emp where empno=no;
if sql%notfound then
raise_application_error(-20020,'該雇員不存在');
end if;
end;
procedurefire_employee(name varchar2) is
begin
delete from emp where upper(ename)=upper(name);
if sql%notfound then
raise_application_error(-20020,'該雇員不存在');
end if;
end;
end;
3.調用重載過程和重載函式
var sal1 number
var sal2 number
exec:sal1:=overload.get_sal('scott')
exec:sal2:=overload.get_sal(7685)
execoverload.fire_employee(7369)
execoverload.fire_employee('scott')
7.使用包構造過程
類似於高級語言中的構造函式和構造方法
1.建立包規範
包的構造過程用於初始化包的全局變數.
create or replacepackage emp_package is
minsalnumber(6,2);
maxsalnumber(6,2);
procedureadd_employee(eno number,name varchar2,salary number,dnonumber);
procedureupd_sal(eno number,salary number);
procedureupd_sal(name varchar2,salary number);
end;
2.建立包體
包的構造過程沒有任何名稱,它是實現了包的其他過程後,以BEGIN開始,END結束的部分
create or replacepackage body emp_package is
procedureadd_employee(eno number,name varchar2,salary number,dno number)
is
begin
if salarybetween minsal and maxsal then
insert into emp (empno,ename,sal,deptno)vals(eno,name,salary,dno);
else
raise_application_error(-20001,'工資不在範圍內');
end if;
exception
when dup_val_on_index then
raise_application_error(-20002,'該雇員已經存在');
end;
procedureupd_sal(eno number,salary number) is
begin
if salary between minsal and maxsal then
update emp set sal=salary where empno =eno;
if sql%notfound then
raise_application_error(-20003,'不存在雇員號');
end if;
else
raise_application_errpr(-20001,'工資不在範圍內');
end if;
end;
procedure upd_sal(name varchar2,salary number) is
begin
if salary between minsal and maxsal then
update emp set sal=salary where upper(ename)=upper(name);
if sql%notfound then
raise_application_error(-20004,'不存在該雇員名');
end if;
else
raise_application_error(-20001,'工資不在範圍內');
end if;
end;
begin
selectmi(sal),max(sal) into minsal,maxsal from emp ;
end;
調用包公用組件:構造過程只調用一次
execemp_package.add_employee(1111,'mary',3000,20)
execemp_package.upd_sal('mary',2000)
8.使用純度級別
在SQL中引用包的公用函式，該公用函式不能包含DML語句(insert,update,delete),也不能讀寫遠程包的變數
為了對包的公用函式加以限制,在定義包規範時,可以使用純度級別(purity level)限制公用函式
語法:pragmarestrict_references (function_name,wnds[,wnps][,rnds][,rnps]);
wnds:用於限制函式不能修改資料庫數據(禁止DML)
wnps:用於限制函式不能修改包變數(不能給包變數賦值)
rnds:用於限制函式不能讀取資料庫數據(禁止SELECT操作)
rnps:用於限制函式不能讀取包變數(不能將包變數賦值給其他變數)
1.建立包規範
create or replacepackage purity is
minsalnumber(6,2);
maxsalnumber(6,2);
functionmax_sal return number;
functionmin_sal return number;
pragmarestrict_references(max_sal,wnps);--不能修改
pragmarestrict_references(min_sal,wnps);
end;
2.建立包體
create or replacepackage body purity is
function max_sal return number
is
begin
return maxsal;
end;
functionmin_sal return number
is
begin
return minsal;
end;
begin
select min(sal),max(sal) into minsal,maxsal from emp;
end;
3.調用包的公用函式
var minsal number
var maxsal number
exec :minsal:=purity.minsal()
exec :maxsal:=purity.maxsal()
print minsal maxsal
Example
DECLARE
salary emp.sal%TYPE := 0;
mgr_num emp.mgr%TYPE;
last_name emp.ename%TYPE;
starting_empno emp.empno%TYPE := 7499;
BEGIN
SELECT mgr INTO mgr_num FROM emp
WHERE empno = starting_empno;
WHILE salary <= 2500 LOOP
SELECT sal,mgr,ename INTO salary,mgr_num,last_name
FROM emp WHERE empno = mgr_num;
END LOOP;
INSERT INTO temp VALUES (NULL,salary,last_name);
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO temp VALUES (NULL,NULL,'Not found');
COMMIT;
END;
/*Please View The Example Code Reference*/
