过程，函数，程序包_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 REPLACEBEGIN       Dbms_output.put_line(‘Hello, world’);END;/
执行存储过程set serveroutput onbegin       my_procend;/ 直接执行：execute my_procexec my_proc 权限：表和视图具有SELECT, INSERT, UPDATE, DELETE 这样的特权，而过程具有EXECUTE特权。只有将EXECUTE 特权赋予用户，用户才可以运行它。而将它赋予PUBLIC用户，则所有用户都可以运行。 [试验]创建3个用户conn donny/donnycreate 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/chrisexec mark.marks.proc 授权：conn mark/mark grant execute on marks_proc to chris
conn chris/chrisexec mark.marks_proc 尝试使用sean用户执行这个过程：conn sean/seanexec mark.marks.proc 将execute 授予public用户，使得所有用户都可以执行这个过程conn mark/markgrant execute on marks_proc to public; conn sean/seanexec 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 ) isbegin       insert into t values(p);end insert_into_t;/ 这个时候并没有执行该过程，尝试执行select * from t;exec insert_into_t (p=> 100);select * from t; 例子2drop table t;Create table t(n number,p varchar2(20));
Create or replace procedure insert_into_t (p1 in number, p2 in number) isbegin       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’) asBegin       Dbms_output.put_line(p1);       Dbms_output.put_line(p2);       Dbms_output.put_line(p3);End default_values;/
只想传入1，3参数：set serveroutput onexec 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 replaceprocedure emp_lookup(       p_empno in number,       o_ename out emp.ename%type , o_sal      out emp.sal%type) asbegin       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 ) asl_temp number;             --局域变量的声明，相当于匿名块中declare之后的声明；过程：as~beginbegin              l_temp := p1;       p1 := p2;       p2 := l_temp;end swap;/
set serveroutput ondeclare       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 ( 上一篇：DBMS_JOB程序包用法人气:421
下一篇：关于游标的一点发现人气:381