Oracle 事物 过程和函数

事物

事务的特点ACID：

原子性(atomicity)：组成事务处理的语句形成了一个逻辑单元，不能只执行其中的一部分。

一致性(consistency)：在事务处理执行前后，数据库是一致的(数据库数据完整性约束)。

隔离性(isolcation)：一个事务处理对另一个事务处理的影响。

持续性(durability)：事务处理的效果能够被永久保存下来。

一个事务只会有一个结果：要么成功、要么失败。

Oracle中的事务

事务的开始DML语句的执行即开始一个事务。

以下情况之一为事务的结束：

显式的结束：执行了commit或是rollback;

隐式的提交：执行了DDL,DCL语句，或是exit退出。

隐式的回滚：系统异常关闭，死机，断电。

保存点的创建和回滚

使用 SAVEPOINT语句在当前事务中创建保存点。

使用 ROLLBACK TOSAVEPOINT语句回滚到创建的保存点。

UPDATE...
SAVEPOINT update_done;
Savepoint created.
INSERT...
ROLLBACK TO update_done;
Rollback complete.

提交或回滚前的数据状态：

改变前的数据状态是可以恢复的

执行 DML 操作的用户可以通过 SELECT语句查询之前的修正

其他用户不能看到当前用户所做的改变，直到当前用户结束事务。

DML语句所涉及到的行被锁定， 其他用户不能操作。

提交后的数据状态：

数据的改变已经被保存到数据库中。

改变前的数据已经丢失。

所有用户可以看到结果。

锁被释放，其他用户可以操作涉及到的数据。

所有保存点被释放。

Oracle中事务的隔离级别

对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:

脏读:对于两个事务T1, T2, T1 读取了已经被T2 更新但还没有被提交的字段.之后, 若T2 回滚,T1读取的内容就是临时且无效的.

不可重复读:对于两个事务T1, T2, T1 读取了一个字段,然后 T2更新了该字段.之后, T1再次读取同一个字段,值就不同了.

幻读:对于两个事务T1, T2, T1 从一个表中读取了一个字段,然后 T2 在该表中插入了一些新的行.之后, 如果T1 再次读取同一个表,就会多出几行.

数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题.

一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱

数据库提供的4 种事务隔离级别:

Oracle支持的 2 种事务隔离级别：READ COMMITED, SERIALIZABLE. Oracle 默认的事务隔离级别为: READ COMMITED

Oracle同时提供了一个Read only的隔离级别。

Mysql支持 4 中事务隔离级别. Mysql默认的事务隔离级别为: REPEATABLE READ

存储过程

存储过程一般用于执行一个指定的操作，可以将常用的特定操作封装成过程。

创建存储过程

CREATE [OR REPLACE] PROCEDURE procedure_name

(argument1 [mode1]datatype1, argument2 [mode2] datatype2, ...)

IS [AS]

声明部分

BEGIN

执行部分

EXCEPTION

异常处理部分

END;

参数类型：in为输入类型参数     out为输出类型参数
不接收参数的过程：

create or replace procedure P1
as
begin
           dbms_output.put_line('Current date is:'||to_char(sysdate,'yyyy-mm-dd'));
end;
--调用方法1(sqlplus)
set serveroutput on;
exec P1();
--调用方法2
set serveroutput on;
begin
  p1();
end;

接收输入类型的参数：

当为过程定义参数时，如果不指定参数模式，则默认为输入参数

create or replace procedure pro2(p_id in varchar2,p_name in varchar2)
as
begin
   insert into person values(p_id,p_name);
end;
--调用
set serveroutput on;
exec pro2('P100','Marray');

使用过程时多参传递：

为形参传递变量和数据可以采用

位置传递

名称传递

组合传递

在调用过程时为参数传递变量和数据：

按位置传递

按位置传递是指在调用时按参数的排列顺序依次写出实参的名称，将形参与实参关联起来进行传递

在这种方法中，形参与实参的名称是相互独立、没有关系的，次序才重要

它比按名称传递方法在书写上简单，但如果更新了一个过程的形参的次序，则对应该过程的所有调用都必须进行相应的更新，所以会增加维护应用程序

按名称传递

按名称传递是指在调用时按照形参与实参的名称写出实参所对应的形参，将形参与实参关联起来进行传递

在这种方法中，形参与实参的名称是相互独立、没有关系的，名称的对应关系很重要，但次序不重要

名称传递在调用子程序时指定参数名，并使用关联符号“=>”为其提供相应的数值或变量

组合传递

可以将按位置传递、按名称传递两种方法在同一调用中混合使用

但前面的实参必须使用按位置传递方法，而后面其余的实参则可以使用按名称传递的方法

函数

函数用于返回特定数据，如果在应用程序中经常需要通过执行SQL语句来返回特定数据，则可以基于这些操作创建特定的函数

函数和过程的结构类似，但必须有一个RETURN子句，用于返回函数值。函数说明要指定函数名、结果值的类型，以及参数类型等。

创建函数：

当创建函数时，通过使用输入参数，可以将应用的数据传递到函数中，最终通过执行函数可以将结果返回到应用程序中

当定义参数时，如果不指定参数模式，则默认为输入参数

create or replace function fun_getrandom return number
as
v_num number;
begin
  	v_num:=floor(dbms_random.value(1,10));
  	return v_num;
end;
declare
	num number;
begin
  	num:=fun_getrandom();
  	dbms_output.put_line(num);
end;

无参函数示例：

返回字符串

create or replace function f1
return varchar2
as
begin
  return 'hello';
end;
--调用
select f1() from dual;

有参函数示例：

根据员工编号，返回员工姓名

create or replace function f2
(
v_empno number
)
return varchar2
as 
v_ename emp.ename%type;
begin
select ename into v_ename from emp where empno=v_empno;
return v_ename;
end;
--调用
select f1(7369) from dual;

过程与函数的比较：

过程与函数有许多相同的功能及特性

都使用IN模式的参数传入数据、OUT模式的参数返回数据

输入参数都可以接收默认值，都可以传值

调用时的实参都可以使用位置表示法或名称表示法

都有声明部分、执行部分和异常处理部分

一般而言，如果需要返回多个值或不返回值，就使用过程

如果只需要返回一个值，就使用函数

虽然函数带OUT模式的参数也能返回多个值，但是一般都认为这种方法属于不好的编程习惯或风格

过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值