Oracle子程序

                              子程序

 

1.       PL/SQL命名程序块的定义在PL/SQL程序块的声明部分，用户称之为子程序。

 

2.       Oracle提供了4中类型的子程序：过程，函数，包，触发器。

 

3.       子程序的优点：

（1）       模块性：子程序可以将程序划分成易于管理，定义明确的逻辑模块，它支持自顶向下的设计方法。

（2）       可维护性：简化维护操作。

（3）       可重性：子程序一旦被定义可以用于许多个应用程序。

（4）       安全性：通过设置权限，使数据更安全。

 

 

 

  过程

 

1.       过程是存储在数据库中的一段存储程序，有时也定义为存储过程。当创建过程时，系统会对其进行编译，并将执行代码存储到数据库中。

2.       存储过程的优点：

（1）       性能好

（2）       可重用PL/SQL

（3）       安全性好

（4）       解耦应用程序与物理表结构。

（5）       模块化，维护性好。

 

3.       定义过程语法：

 

create [ or replace]  procedure<procedure_name>

(<arg1 [mode]  datatype>,…………)

is|as

[declaration]

begin

[exception]

end[procedure_name];

 

其中model有三种形式，分别是in,out和in out.

 

4.       用户可以通过两种方式调用过程：

（1）       在SQL>提示符下调用过程，execute过程名(参数……)

（2）       在PL/SQL块中调用过程，过程名（参数……）

 

5.  举例说明：

   

(1)参数模式in举例(in表示输入参数，可不写，默认参数为in模式)

 

create or replace procedure emp_prc

(emp_no in emp.empno%type)

as

  empRecord emp%rowtype;

begin

  select * into empRecord from emp where empno=emp_no;

  dbms_output.put_line(empRecord.ename);

  exception

     when too_many_rows then

     dbms_output.put_line('too many record');

end emp_prc;

 

---执行 execute emp_prc(7369);

 

---------------------结果---------------------------------

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 (2)参数模式out举例

 create or replace procedure get_name

(

  v_empno  emp.empno%type,

  v_ename out emp.ename%type

)

as

cursor c_emp is select empno,ename from emp where

empno=v_empno;

v_emp c_emp%rowtype;

begin

  open c_emp;

  fetch c_emp into v_emp;

  v_ename:=v_emp.ename;

  close c_emp;

end get_name;

--------------------------------

declare

  name emp.ename%type;

begin

  get_name(7369,name);

  dbms_output.put_line(name);

end;

---------------------------------

创建图：

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

执行图：

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

(3)参数模式in out举例

 

create or replace procedure hi_proc

(

mes in out nvarchar2

)

as

begin

  mes:='你好'||mes||'!';

end hi_proc;

 

创建图

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

执行图:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1.    删除过程

 drop  procedure <procedure_name>

                

 

 

函数

 

 

1.    函数和过程都以编译后的形式放在数据库中，函数可以没有参数，也可以有多个参数，并有一个返回值。

 

 

2.    函数和过程的区别

  

过 程	函  数
作为 PL/SQL 语句执行	作为表达式的一部分调用
在规格说明中不包含  RETURN 子句	必须在规格说明中包含 RETURN 子句
不返回任何值	必须返回单个值
可以包含 RETURN 语句，但是与函数不同，它不能用于返回值	必须包含至少一条 RETURN 语句

 

 

3.    创建函数的语法

CREATE [OR REPLACE] FUNCTION

  <function name> [(param1,param2)]

RETURN <datatype>  IS|AS

  [local declarations]

BEGIN

  Executable Statements;

  RETURN result;

EXCEPTION

  Exception handlers;

END;

 

 

4.    举例说明：

例1：

例2：

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 自主事务处理

 

1.自主事务处理

q     主事务处理启动独立事务处理

q     然后主事务处理被暂停

q     自主事务处理子程序内的 SQL 操作

q     然后终止自主事务处理

q     恢复主事务处理

 

2.PRAGMA AUTONOMOUS_TRANSACTION 用于标记子程序为自主事务处理

 

3. 自主事务处理的特征：

q     与主事务处理的状态无关

q     提交或回滚操作不影响主事务处理

q     自主事务处理的结果对其他事务是可见的

q     能够启动其他自主事务处理

 

 

1.    举例说明：

例1：两个过程p1,p2都不为自主事务处理。

 

过程p1:

create or replace procedure p1

as

emp_name emp.ename%type;

begin

    update emp set ename='fxe' where empno=7369;

    select ename into emp_name from emp

     where empno=7788;

    dbms_output.put_line(emp_name);

rollback;

end p1;

 

  过程p2:

create or replace procedure p2

as

begin

  update emp set ename='lxt008' where empno=7788;

  p1;

  rollback;

end p2;

 

 

----执行过程p2 ：execute p2；

结果为：

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 例2：p1属于自主事务处理

  过程p1:

create or replace procedure p1

as

PRAGMA AUTONOMOUS_TRANSACTION;

emp_name emp.ename%type;

begin

    update emp set ename='fxe' where empno=7369;

    select ename into emp_name from emp

     where empno=7788;

    dbms_output.put_line(emp_name);

rollback;

end p1;

 

  过程p2:

create or replace procedure p2

as

begin

  update emp set ename='lxt008' where empno=7788;

  p1;

  rollback;

end p2;

 

 

----执行过程p2 ：execute p2；

结果为：

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

根据例1和例2对比，仔细体会两个例子的区别，并了解自主事务处理