过程，函数，程序包
之前学习的 PL/SQL 块是匿名的，不能将其存储到数据库中。
我们可以命名我们的 PL/SQL 块，并为他们确定参数，存储在数据库中。这样可以从任何数据库客户端或者工具引用和运行他们，比如 SQLPLUS, ProC, 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 ( <span style="FONT-FAMILY: 宋体; mso</font>