Oracle自定义函数，存储过程和事务处理

自定义函数和存储过程

Oracle中支持用户自定义函数以能够处理更加复杂的数据业务；在Oracle数据库系统中函数和存储过程都是预编译的PLSQL代码块的封装，它们具有高效性及重用性。

在Oracle中函数必须有一个返回值，存储过程没有返回值；函数和存储过程都支持参数定义从而接收外部调用的数据传递到函数或存储过程中。

自定义函数——无参函数

自定义函数——有参函数（输入型参数，输出型参数，输入输出型参数）

函数定义语法结构

create[or replace]function function_name [()]

return date_type is/as

begin

return data;

end[function_name]

无参函数

create or replace function fun_sayhello return varchar2

is

begin

return’你好’;

end fun_sayhello;

PL SQL块中调用无参数存储过程

begin

dbms_output.put_line( fun_sayhello );

end;

普通SQL语句中调用存储过程

Update information set info = fun_sayhello();

有参函数

IN 输入类型，只接收调用时输入，此类型可以省略

OUT 输出类型，不接收调用输入，此类型函数返回后必须使用变量接收

IN OUT 输入输出型，可接收输入也可以输出，函数内可对此类型赋值

IN类型案例

create or replace function fun_hello(nameIN varchar2)

return varchar2 is

begin

name:=’你好’||name;

return name;

end;

begin

dbms_output.put_line(fun_hello(‘张三丰’));

end;

OUT类型案例

create or replace function fun_hello(name OUT varchar2)

return varchar2 is

begin

name:=’你好’||name;

return ‘已经说你好’;

end;

declare

name varchar2(24);

begin

dbms_output.put_line(fun_hello(name)); --输出返回值

dbms_output.put_line(name);--输出out参数值

end;

IN OUT类型案例

create or replace function f_one(name IN OUT varchar2)

return varchar2 is

begin

name:=name||’king’;

return’你好’||name;

end;

declare

name varchar2(24);

begin

dbms_output.put_line(f_one(name)); --输出函数返回值

dbms_output.put_line(name); --输出in out参数变量值

end;

函数实践

编写一个用户自定义函数带有一个in类型的值类型参数用来表示年龄和一个带out类型的varchar2类型参数，判断年龄是否大于等于18，如果大于等于18则为输出参数赋值为“成年人”并返回字符串“大于等于18岁”否则赋值为“未成年”并返回字符串“小于18岁”。编写PL/SQL块调用测试此函数。

create or replace function fun_adult(age IN number)

return string as

message string(64):='是成年人';

message2 string(31):='是未成年人';

begin

  if age >= 18 then

          return message;

      else

          return message2;

   end if;

end fun_adult;

declare

    res number:=17;

begin

    dbms_output.put_line(fun_adult(res));

end;

存储过程

l Oracle中存储过程与函数的最大区别在于函数必须有返回值，而存储过程没有返回值。

l 存储过程在Oracle数据库中使用procedure关键字定义。

l 存储过程与函数一样也支持IN、OUT、IN OUT三种形式的参数。

存储过程定义

create or replace procedure proc_name[(参数列表...)]is/as

begin

PL/SQL语句块......

end[proc_name];

触发器

触发器(trigger)在Oracle中是功能强大的功能代码执行单元，定义格式通常像存储过程和函数，较函数或存储过程复杂；不同的是触发器不允许用户显示调用也不带返回值和参数，它是在满足某种条件时自动触发执行的。触发器通常在需要时由专门数据库开发人员或DBA开发制定。

其一 是约束数据功能（触发器是一种复杂的约束定义）；

其二 是根据触发动作完成复杂的业务数据处理和记录。

DML触发器，最常用的触发器，通常在执行insert、delete和update时自动触发

instead of触发器，建立在视图上的触发器对象，不提倡使用

DDL触发器，当发生CREATE、ALTER、DROP、TRUNCATE命令时触发此类型触发器

DB触发器，当数据库系统发生Startup、Shutdown、Logon、Logoff时触发DB触发器

启用、禁用、删除触发器

ALTER TRIGGER trig_emp_delete DISABLE;

ALTER TRIGGER trig_emp_delete ENABLE;

DROP TRIGGER trig_emp_delete;

数据库事务

数据库事务(Database Transaction)，是指作为独立的逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。

原子性

事务必须是原子工作单元，是不可分割的；对于其数据修改，要么全都执行，要么全都不执行

隔离性

由并发事务所作的修改必须与任何其它并发事务所作的修改互相隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据（事务间是不透明的）。这称为隔离性

一致性

交易双方修改后的数据保持一致。

持久性

事务在执行完成之后，对于系统的影响是永久性的。事务中所做的任何修改即使出现致命的系统故障也将一直保持

事务执行控制

commit 提交事务处理

rollback 撤销/回滚事务处理

savepoint 事务保存点

rollback to savepoint 回滚到事务保存点