之前学习的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;