Oracle概念：過程、函數、程序包

之前學習的PL/SQL塊是匿名的，不能將其存儲 target=_blank>存儲到數據庫中。

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

優點：

1. 可重用性：一旦命名并保存在數據庫中后，任何應用都可以

2. 抽象和數據隱藏

3. 安全 target=_blank>安全性

過程

存儲過程就是命了名的PL/SQL塊，可以被賦予參數，存儲在數據庫中，然后由另一個應用或者PL/SQL例程調用。比如

CREATE PROCEDURE my_proc as

BEGIN

NULL;

END;

/

語法：

CREATE [OR REPLACE] PROCEDURE procedure_name (參數)

IS | AS

[PRAGMA AUTONOMOUS_TRANACTION;] --聲明自主事務處理。

[本地變量聲明]

BEGIN

執行語句部分

[EXCEPTION]

錯誤處理部分

END[name];

/

CREATE OR REPLACE PROCEDURE my_proc as -- OR REPLACE

BEGIN

Dbms_output.put_line(‘Hello, world’);

END;

/

執行存儲過程

set serveroutput on

begin

my_proc

end;

/

直接執行：

execute my_proc

exec my_proc

權限：

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

[試驗]

創建3個用戶

conn donny/donny

create user chris identified by chris;

此時不能連結數據庫，不能創建過程

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建立一個過程

conn mark/mark

create procedure marks_proc as

begin

null;

end;

/

嘗試使用chris用戶執行這個過程：

conn chris/chris

exec mark.marks.proc

授權：

conn mark/mark

grant execute on marks_proc to chris

conn chris/chris

exec mark.marks_proc

嘗試使用sean用戶執行這個過程：

conn sean/sean

exec mark.marks.proc

將execute 授予public用戶，使得所有用戶都可以執行這個過程

conn mark/mark

grant execute on marks_proc to public;

conn sean/sean

exec mark.marks.proc

參數：

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

IN 參數通過調用者傳入，只能由過程讀取，不能改變。是默認的模式，可以具有默認值。

OUT 參數有過程寫入。用于過程需要向調用者返回多條信息的時候。不能是具有默認值的變量，也不能是常量，必須向OUT參數傳遞返回值。

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

IN參數：

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;

/

這個時候并沒有執行該過程，嘗試執行

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;

/

這個時候并沒有執行該過程，嘗試執行

select * from t;

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

select * from t;

參數傳遞方法：

1. 使用名稱表示

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

2. 使用位置表示

exec insert_into_t (102, 202);

3. 使用混合表示

適用于有默認值的情況，注意：OUT 和 IN OUT 參數不能有默認值

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參數：

set serveroutput on

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

OUT 參數：

從過程向調用者返回值：

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

分析：

desc scott.emp

參數：一個in, 兩個out

參數類型：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;

/

執行該過程：

1. 使用匿名PL/SQL

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

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 中執行

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

Variable name varchar2(10);

Variable sal number;

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

Print name;

Print sal;

Select :name, :sal from dual;

IN OUT 參數：

可以用來傳入參數，并從存儲過程返回值。

例子：輸入兩個數，交換值

思路：通過一個臨時變量作為交換過程中的過渡

create or replace

procedure swap(

p1 in out number,

p2 in out number ) as

l_temp number; --局域變量的聲明，相當于匿名塊中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;

/

關于自主事務處理: P197

我們曾經討論過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，調用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

說明事務處理可以跨越過程繼續執行。

為了防止一個過程影響其他過程，可以將其標記為自主的。這樣p2中的rollback將不會影響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;　函數：

與過程相似，遵循了相同的規則。

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

函數的主要特性是必須返回一個值。

語法：

CREATE [OR REPLACE] FUNCTION function_name (參數)

RETURN datatype

IS | AS

[PRAGMA AUTONOMOUS_TRANACTION;] --聲明自主事務處理。

[本地變量聲明]

BEGIN

執行語句部分

[EXCEPTION]

錯誤處理部分

END[name];

/

返回值：

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

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

CREATE OR REPLACE

FUNCTION my_func

RETURN varchar2

As

Begin

Return ‘HELLO, WORLD’;

END;

/

調用函數：

用戶必須要獲取返回值，所以調用時，要聲明一個局域變量：

set serveroutput on

declare

l_str varchar2(100) := null;

begin

l_str := my_func;

dbms_output.put_line(l_str);

end;

/

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

Create or replace

Procedure show_it(p varchar2) as

Begin

Dbms_output.put_line(p);

End;

/

exec show_it(my_func);

練習：

編寫一個函數ITE，實現：boolean expression ? true_value: false_value

輸入一個表達式expression, IF正確，THEN輸出true_value;ELSE，輸出false_value

分析：3個in 參數，一個boolean, 2個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’));

程序包：

是對PL/SQL類型，過程，函數，游標，異常，變量，常量的封裝。

包括兩部分：規范和主體

規范：是程序包的公共接口，

主體：規范的實現，以及私有例程、數據和變量。

語法：

CREATE OR REPLACE PACKAGE package_name

IS | AS

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

公用過程或函數的聲明;

END package_name;

/

CREATE OR REPLACE PACKAGE BODY package_name

IS | AS

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

公用過程或函數的實現;

END package_name

規范：

規范是程序包的接口，規范中定義的所有內容都可以由調用者使用(當然需要具有EXECUTE特權)，比如規范中定義的過程函數可以被執行，類型可以被訪問，變量可以被引用。

例子：使用兩個過程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;

/

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

這個時候如果試圖使用這個包，會報錯

exec employee_pkg.print_ename(1234);

主體：

程序包是過程，函數的具體實現部分，實現規范中定義的接口。

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;

/

執行：

set serveroutput on

exec employee_pkg.print_ename(1234);

exec employee_pkg.print_ename(7782);

exec employee_pkg.print_sal(7782);

過程和函數的重載：

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

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

procedure foo(p1 varchar2);

procedure foo(p1 number);

procedure foo(p1 varchar2, p2 number);

procedure foo(p1 varchar2, p2 varchar2);

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

procedure foo(p1 varchar2);

procedure foo(p2 varchar2);

procedure foo(p1 in varchar2);

procedure foo(p1 in out varchar2);

例如

desc dbms_output

試驗：重載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;

/

該例子只能實現交換數值，我們將其包裝，并進行重載，使其也可以交換varchar2 和 date

(1) 建立規范

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) 測試結果：

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;

/

程序包中的游標：

在PL/SQL塊中，return 子句是游標的可選部分。

在規范中聲明游標的時候，必須存在與游標關聯的return 子句，表示從游標獲取的數據元素類型。

并且游標的select 子句只出現在主體中，不出現在規范中。

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

限定函數的純度級別

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;

/

數據字典：

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認識的格式，保護智力成果

wrap iname=utilities.sql

生成utilities.plb

規范部分：

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';;