oracle过程，函数，程序包

过程，函数，程序包

之前学习的PL/SQL块是匿名的，不能将其存储到数据库中。
我们可以命名我们的PL/SQL块，并为他们确定参数，存储在数据库中。这样可以从任何数据库客户端或者工具引用和运行他们，比如SQL*PLUS, Pro*C, JDBC。这些命名的PL/SQL块成为存储过程和函数，他们的集合成为程序包。

优点：
1． 可重用性：一旦命名并保存在数据库中后，任何应用都可以
2． 抽象和数据隐藏
3． 安全性

过程
存储过程就是命了名的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';