Oracle概念:过程、函数、程序包(上)

日期: 2009-06-11 作者:Arraygrassbell 来源:TechTarget中国 英文

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

我们一直都在努力坚持原创.......请不要一声不吭,就悄悄拿走。

我原创,你原创,我们的内容世界才会更加精彩!

【所有原创内容版权均属TechTarget,欢迎大家转发分享。但未经授权,严禁任何媒体(平面媒体、网络媒体、自媒体等)以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

电子邮件地址不会被公开。 必填项已用*标注

敬请读者发表评论,本站保留删除与本文无关和不雅评论的权力。

相关推荐