之前學(xué)習(xí)的PL/SQL塊是匿名的，不能將其存儲(chǔ) target=_blank>存儲(chǔ)到數(shù)據(jù)庫(kù)中。

我們可以命名我們的PL/SQL塊，并為他們確定參數(shù)，存儲(chǔ)在數(shù)據(jù)庫(kù)中。這樣可以從任何數(shù)據(jù)庫(kù)客戶端或者工具引用和運(yùn)行他們，比如SQL*PLUS, Pro*C, JDBC。這些命名的PL/SQL塊成為存儲(chǔ)過程和函數(shù)，他們的集合成為程序包。

優(yōu)點(diǎn)：

1. 可重用性：一旦命名并保存在數(shù)據(jù)庫(kù)中后，任何應(yīng)用都可以

2. 抽象和數(shù)據(jù)隱藏

3. 安全 target=_blank>安全性

過程

存儲(chǔ)過程就是命了名的PL/SQL塊，可以被賦予參數(shù)，存儲(chǔ)在數(shù)據(jù)庫(kù)中，然后由另一個(gè)應(yīng)用或者PL/SQL例程調(diào)用。比如

CREATE PROCEDURE my_proc as

BEGIN

NULL;

END;

/

語法：

CREATE [OR REPLACE] PROCEDURE procedure_name (參數(shù))

IS | AS

[PRAGMA AUTONOMOUS_TRANACTION;] --聲明自主事務(wù)處理。

[本地變量聲明]

BEGIN

執(zhí)行語句部分

[EXCEPTION]

錯(cuò)誤處理部分

END[name];

/

CREATE OR REPLACE PROCEDURE my_proc as -- OR REPLACE

BEGIN

Dbms_output.put_line(‘Hello, world’);

END;

/

執(zhí)行存儲(chǔ)過程

set serveroutput on

begin

my_proc

end;

/

直接執(zhí)行：

execute my_proc

exec my_proc

權(quán)限：

表和視圖具有SELECT, INSERT, UPDATE, DELETE 這樣的特權(quán)，而過程具有EXECUTE特權(quán)。只有將EXECUTE 特權(quán)賦予用戶，用戶才可以運(yùn)行它。而將它賦予PUBLIC用戶，則所有用戶都可以運(yùn)行。

[試驗(yàn)]

創(chuàng)建3個(gè)用戶

conn donny/donny

create user chris identified by chris;

此時(shí)不能連結(jié)數(shù)據(jù)庫(kù)，不能創(chuàng)建過程

grant connect, resource to chris;

create user sean identified by sean;

grant connect, resource to sean;

create user mark identified by mark;

grant connect, resource to mark;

使用mark建立一個(gè)過程

conn mark/mark

create procedure marks_proc as

begin

null;

end;

/

嘗試使用chris用戶執(zhí)行這個(gè)過程：

conn chris/chris

exec mark.marks.proc

授權(quán)：

conn mark/mark

grant execute on marks_proc to chris

conn chris/chris

exec mark.marks_proc

嘗試使用sean用戶執(zhí)行這個(gè)過程：

conn sean/sean

exec mark.marks.proc

將execute 授予public用戶，使得所有用戶都可以執(zhí)行這個(gè)過程

conn mark/mark

grant execute on marks_proc to public;

conn sean/sean

exec mark.marks.proc

參數(shù)：

過程可以進(jìn)行參數(shù)化處理，可以為任何合法的PL/SQL類型，有三種模式：IN, OUT, IN OUT

IN 參數(shù)通過調(diào)用者傳入，只能由過程讀取，不能改變。是默認(rèn)的模式，可以具有默認(rèn)值。

OUT 參數(shù)有過程寫入。用于過程需要向調(diào)用者返回多條信息的時(shí)候。不能是具有默認(rèn)值的變量，也不能是常量，必須向OUT參數(shù)傳遞返回值。

IN OUT 具有兩者的特性，可以讀取和寫入。

IN參數(shù)：

Create table t(n number);

Create or replace

procedure insert_into_t (p in number ) is

begin

insert into t values(p);

end insert_into_t;

/

這個(gè)時(shí)候并沒有執(zhí)行該過程，嘗試執(zhí)行

select * from t;

exec insert_into_t (p=> 100);

select * from t;

例子2

drop table t;

Create table t

(n number,

p varchar2(20));

Create or replace

procedure insert_into_t (

p1 in number,

p2 in number) is

begin

insert into t values(p1,’p1’);

insert into t values(p2,’p2’);

end insert_into_t;

/

這個(gè)時(shí)候并沒有執(zhí)行該過程，嘗試執(zhí)行

select * from t;

exec insert_into_t (p1=> 100, p2=>200);

select * from t;

參數(shù)傳遞方法：

1. 使用名稱表示

exec insert_into_t (p2=> 101, p1=>201);

2. 使用位置表示

exec insert_into_t (102, 202);

3. 使用混合表示

適用于有默認(rèn)值的情況，注意：OUT 和 IN OUT 參數(shù)不能有默認(rèn)值

CREATE OR REPLACE procedure default_values(

P1 varchar2,

P2 varchar2 default ‘Chris’,

P3 varchar2 default ‘Sean’) as

Begin

Dbms_output.put_line(p1);

Dbms_output.put_line(p2);

Dbms_output.put_line(p3);

End default_values;

/

只想傳入1，3參數(shù)：

set serveroutput on

exec default_values(‘Tom’, p3=>’Joel’);

OUT 參數(shù)：

從過程向調(diào)用者返回值：

例子：使用scott.emp表，編寫搜索過程，輸入empno,返回ename , sal

分析：

desc scott.emp

參數(shù)：一個(gè)in, 兩個(gè)out

參數(shù)類型：in number, out emp.ename%type , out emp.sal%type

con scott/tiger

create or replace

procedure emp_lookup(

p_empno in number,

o_ename out emp.ename%type ,

o_sal out emp.sal%type) as

begin

select ename, sal

into o_ename, o_sal

from emp

where empno= p_empno;

exception

when NO_DATA_FOUND then

o_ename := ‘null’;

o_sal := -1;

end;

/

執(zhí)行該過程：

1. 使用匿名PL/SQL

分析：目的是輸出兩個(gè)out參數(shù)的結(jié)果，所以匿名塊中也要定義2個(gè)與out參數(shù)相同的局域變量。

set serveroutput on

declare

l_ename emp.ename%type;

l_sal emp.sal%type;

begin

emp_lookup(7782, l_ename, l_sal);

dbms_output.put_line(‘Ename = ‘ || l_ename);

dbms_output.put_line(‘Sal = ‘ || l_sal);

end;

/

2. 在sql plus 中執(zhí)行

分析：需要使用sql plus 的VARIABLE命令綁定參數(shù)值，即為OUT參數(shù)提供接受返回值的變量。

Variable name varchar2(10);

Variable sal number;

Exec emp_lookup(‘7782’, :name, :sal);

Print name;

Print sal;

Select :name, :sal from dual;

IN OUT 參數(shù)：

可以用來傳入?yún)?shù)，并從存儲(chǔ)過程返回值。

例子：輸入兩個(gè)數(shù)，交換值

思路：通過一個(gè)臨時(shí)變量作為交換過程中的過渡

create or replace

procedure swap(

p1 in out number,

p2 in out number ) as

l_temp number; --局域變量的聲明，相當(dāng)于匿名塊中declare之后的聲明;過程：as~begin

begin

l_temp := p1;

p1 := p2;

p2 := l_temp;

end swap;

/

set serveroutput on

declare

l1 number:=100;

l2 number:=200;

begin

swap(l1,l2);

dbms_output.put_line(‘l1 = ‘|| l1);

dbms_output.put_line(‘l2 = ‘|| l2);

end;

/

關(guān)于自主事務(wù)處理: P197

我們?cè)?jīng)討論過COMMIT 和 ROLLBACK的概念。

建立過程P2：

CREATE OR REPLACE PROCEDURE P2 AS

A varchar2(50);

Begin

Select venadd1 into a from vendor_master where vencode=’V002’;

Dbms_output.put_line(a);

ROLLBACK;

END;

/

建立過程P1，調(diào)用P2：

CREATE OR REPLACE PROCEDURE P1 AS

B varchar2(50);

Begin

Update vendor_master set venadd1=’10 Walls Street ‘ where vencode=’V002’;

P2();

Select venadd1 into b from vendor_master where vencode=’V002’;

Dbms_output.put_line(b);

ROLLBACK;

END;

/

exec p1

說明事務(wù)處理可以跨越過程繼續(xù)執(zhí)行。

為了防止一個(gè)過程影響其他過程，可以將其標(biāo)記為自主的。這樣p2中的rollback將不會(huì)影響p1

CREATE OR REPLACE PROCEDURE P2 AS

A varchar2(50);

PRAGMA AUTONOMOUS_TRANSACTION;

Begin

Select venadd1 into a from vendor_master where vencode=’V002’;

Dbms_output.put_line(a);

ROLLBACK;

END;

/

EXEC P1;　函數(shù)：

與過程相似，遵循了相同的規(guī)則。

參數(shù)傳遞：只能帶有in參數(shù)，不能使用out, in out 參數(shù)

函數(shù)的主要特性是必須返回一個(gè)值。

語法：

CREATE [OR REPLACE] FUNCTION function_name (參數(shù))

RETURN datatype

IS | AS

[PRAGMA AUTONOMOUS_TRANACTION;] --聲明自主事務(wù)處理。

[本地變量聲明]

BEGIN

執(zhí)行語句部分

[EXCEPTION]

錯(cuò)誤處理部分

END[name];

/

返回值：

定義函數(shù)的時(shí)候必須使用RETURN datatype 子句，表示函數(shù)要返回的數(shù)據(jù)類型。

在函數(shù)體中的任何地方，都可以使用 RETURN <表達(dá)式>返回值，類型要和定義中的相同。

CREATE OR REPLACE

FUNCTION my_func

RETURN varchar2

As

Begin

Return ‘HELLO, WORLD’;

END;

/

調(diào)用函數(shù)：

用戶必須要獲取返回值，所以調(diào)用時(shí)，要聲明一個(gè)局域變量：

set serveroutput on

declare

l_str varchar2(100) := null;

begin

l_str := my_func;

dbms_output.put_line(l_str);

end;

/

也可以將函數(shù)用作其他過程以及函數(shù)的IN參數(shù)。

Create or replace

Procedure show_it(p varchar2) as

Begin

Dbms_output.put_line(p);

End;

/

exec show_it(my_func);

練習(xí)：

編寫一個(gè)函數(shù)ITE，實(shí)現(xiàn)：boolean expression ? true_value: false_value

輸入一個(gè)表達(dá)式expression, IF正確，THEN輸出true_value;ELSE，輸出false_value

分析：3個(gè)in 參數(shù)，一個(gè)boolean, 2個(gè)varchar2

create or replace

function ite(

p_expression Boolean,

p_true varchar2,

p_false varchar2)

return varchar2

as

begin

if p_expression then

return p_true;

end if;

return p_false;

end;

/

exec dbms_output.put_line(ite(1=2, ‘Equal’,’Not equal’));

程序包：

是對(duì)PL/SQL類型，過程，函數(shù)，游標(biāo)，異常，變量，常量的封裝。

包括兩部分：規(guī)范和主體

規(guī)范：是程序包的公共接口，

主體：規(guī)范的實(shí)現(xiàn)，以及私有例程、數(shù)據(jù)和變量。

語法：

CREATE OR REPLACE PACKAGE package_name

IS | AS

公用類型或變量常量的聲明;

公用過程或函數(shù)的聲明;

END package_name;

/

CREATE OR REPLACE PACKAGE BODY package_name

IS | AS

私有類型或變量常量的聲明;

公用過程或函數(shù)的實(shí)現(xiàn);

END package_name

規(guī)范：

規(guī)范是程序包的接口，規(guī)范中定義的所有內(nèi)容都可以由調(diào)用者使用(當(dāng)然需要具有EXECUTE特權(quán))，比如規(guī)范中定義的過程函數(shù)可以被執(zhí)行，類型可以被訪問，變量可以被引用。

例子：使用兩個(gè)過程PRINT_ENAME() 和PRINT_SAL()，定義稱為EMPLOYEE_PKG的程序包。

CREATE OR REPLACE

PACKAGE employee_pkg as

Procedure print_ename(p_empno number);

Procedure print_sal(p_empno number);

End;

/

并沒有為過程提供代碼，只是定義了名稱和參數(shù)。

這個(gè)時(shí)候如果試圖使用這個(gè)包，會(huì)報(bào)錯(cuò)

exec employee_pkg.print_ename(1234);

主體：

程序包是過程，函數(shù)的具體實(shí)現(xiàn)部分，實(shí)現(xiàn)規(guī)范中定義的接口。

CREATE OR REPLACE

PACKAGE BODY employee_pkg as

Procedure print_ename(p_empno number) is

L_ename emp.ename%type;

Begin

Select ename into l_ename from emp where empno=p_empno;

Dbms_output.put_line(l_ename);

Exception

When no_data_found then

Dbms_output.put_line(‘Invalid employee number’);

End print_ename;

Procedure print_sal(p_empno number) is

L_sal emp.sal%type;

Begin

Select sal into l_sal from emp where empno=p_empno;

Dbms_output.put_line(l_sal);

Exception

When NO_DATA_FOUND then

Dbms_output.put_line(‘Invalid employee number’);

End print_sal;

End employee_pkg;

/

執(zhí)行：

set serveroutput on

exec employee_pkg.print_ename(1234);

exec employee_pkg.print_ename(7782);

exec employee_pkg.print_sal(7782);

過程和函數(shù)的重載：

在單獨(dú)的程序包中定義的共享相同名稱的兩個(gè)或者多個(gè)過程和函數(shù)。單獨(dú)的過程和函數(shù)必須具有唯一的名稱，但是程序包中可以有條件的共享相同的名稱。

條件：參數(shù)列表在數(shù)量、次序或者參數(shù)類型上有所區(qū)別，比如

procedure foo(p1 varchar2);

procedure foo(p1 number);

procedure foo(p1 varchar2, p2 number);

procedure foo(p1 varchar2, p2 varchar2);

但是：只在參數(shù)名稱、輸入輸出模式、或者返回類型上不同，是不夠的。比如

procedure foo(p1 varchar2);

procedure foo(p2 varchar2);

procedure foo(p1 in varchar2);

procedure foo(p1 in out varchar2);

例如

desc dbms_output

試驗(yàn)：重載SWAP()

之前的例子：

create or replace

procedure swap(

p1 in out number,

p2 in out number ) as

---

l_temp number;

begin

l_temp := p1;

p1 := p2;

p2 := l_temp;

end swap;

/

該例子只能實(shí)現(xiàn)交換數(shù)值，我們將其包裝，并進(jìn)行重載，使其也可以交換varchar2 和 date

(1) 建立規(guī)范

create or replace package utilities as

procedure swap(p1 in out number, p2 in out number);

procedure swap(p1 in out varchar2,p2 in out varchar2);

procedure swap(p1 in out date,p2 in out date);

end utilities;

/

(2) 建立主體

create or replace package body utilities as

procedure swap(p1 in out number, p2 in out number ) as

l_temp number;

begin

dbms_output.put_line(‘Swapping number’);

l_temp := p1;

p1 := p2;

p2 := l_temp;

end swap;

procedure swap(p1 in out varchar2,p2 in out varchar2) as

l_temp varchar2(32767);

begin

dbms_output.put_line(‘Swapping varchar2’);

l_temp := p1;

p1 := p2;

p2 := l_temp;

end swap;

procedure swap(p1 in out date,p2 in out date) as

l_temp date;

begin

dbms_output.put_line(‘Swapping date’);

l_temp := p1;

p1 := p2;

p2 := l_temp;

end swap;

end utilities;

/

(3) 測(cè)試結(jié)果：

set serveroutput on

declare

l_num1 number :=1;

l_num2 number :=2;

l_date1 date:= sysdate;

l_date2 date:= sysdate +1;

begin

utilities.swap(l_num1,l_num2);

dbms_output.put_line(‘l_num1=’|| l_num1);

dbms_output.put_line(‘l_num2=’|| l_num2);

utilities.swap(l_date1,l_date2);

dbms_output.put_line(‘l_date1=’|| l_date1);

dbms_output.put_line(‘l_date2=’|| l_date2);

end;

/

程序包中的游標(biāo)：

在PL/SQL塊中，return 子句是游標(biāo)的可選部分。

在規(guī)范中聲明游標(biāo)的時(shí)候，必須存在與游標(biāo)關(guān)聯(lián)的return 子句，表示從游標(biāo)獲取的數(shù)據(jù)元素類型。

并且游標(biāo)的select 子句只出現(xiàn)在主體中，不出現(xiàn)在規(guī)范中。

Create or replace package cur_pack is

Cursor ord_cur return order_master%rowtype;

Procedure ord_pro (orno varchar2);

End;

/

Create or replace package body cur_pack is

Cursor ord_cur return order_master%rowtype is

Select * from order_master;

Procedure ord_pro (orno varchar2) is

Or_rec order_master%rowtype;

Begin

Open ord_cur;

Loop

Fetch ord_cur into or_rec;

Exit when ord_cur%notfound;

Dbms_output.put_line(‘Return ’|| or_rec.orderno);

End loop;

End ord_pro;

End cur_pack;

/

PRAGMA RESTRICT REFERENCES

P206

限定函數(shù)的純度級(jí)別

P197

WNDS: write no data status

RNDS: read no data status

WNPS: write no package status

RNPS: read no package status

Create or replace package pack_me is

Procedure order_proc(orno varchar2);

Function order_fun (ornos varchar2) return varchar2;

Pragma restrict_references(order_fun, WNDS,RNDS);

END;

/

數(shù)據(jù)字典：

select object_name, object_type

from user_objects

where object_type in (‘PROCEDURE’,’FUNCTION’,’PACKAGE’,’PACKAGE BODY’);

查看用戶代碼：

desc user_source

select text from user_source where name=’INSERT_INTO_T’ order by line;

desc employee_pkg

包裝工具：

將utilities包的主體部分保存在utilities.sql文件中，通過wrap工具加密，只生成oracle認(rèn)識(shí)的格式，保護(hù)智力成果

wrap iname=utilities.sql

生成utilities.plb

規(guī)范部分：

create or replace package utilities as

procedure swap(p1 in out number, p2 in out number);

procedure swap(p1 in out varchar2,p2 in out varchar2);

procedure swap(p1 in out date,p2 in out date);

end utilities;

/

select text from user_source where name='UTILITIES';;