Oracle 事务、存储过程和函数

本小白又来了,已经学到了Oracle数据库中的事务这一节了。下面给大家分享一下我的一点经验，望各位大神多多指教。

一、事务
事务这一概念在本小白看来就是一堆的概念需要自己在编写事务的代码过程中自身去慢慢体会和理解。

事务的特点 ACID：

原子性(atomicity)：组成事务处理的语句形成了一个逻辑单元，不能只执行其中的一部分。
一致性(consistency)：在事务处理执行前后，数据库是一致的(数据库数据完整性约束)。
隔离性(isolcation)：一个事务处理对另一个事务处理的影响。
持续性(durability)：事务处理的效果能够被永久保存下来 。
一个事务只会有一个结果：要么成功、要么失败。

事务的开始DML语句的执行即开始一个事务。
以下情况之一为事务的结束：
1. 显式的结束：执行了commit或是rollback;
2. 隐式的提交：执行了DDL,DCL语句，或是exit退出。
3. 隐式的回滚：系统异常关闭，死机，断电。

要在事务中设置保存点用来回转到以避免错误操作之后数据无法被恢复。

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

1. 改变前的数据状态是可以恢复的
2. 执行 DML 操作的用户可以通过 SELECT 语句查询之前的修正
3. 其他用户不能看到当前用户所做的改变，直到当前用户结束事务。
4. DML语句所涉及到的行被锁定， 其他用户不能操作。

提交后的数据状态

1. 数据的改变已经被保存到数据库中。
2. 改变前的数据已经丢失。
3. 所有用户可以看到结果。
4. 锁被释放， 其他用户可以操作涉及到的数据。
5. 所有保存点被释放。

Oracle中事务的隔离级别

1. 对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:
   1）脏读: 对于两个事务T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的.
   2）不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了.
   3）幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行.
2. 数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题.
3. 一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱.

二、存储过程

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

创建存储过程

CREATE [OR REPLACE] PROCEDURE procedure_name
(argument1 [mode1] datatype1, argument2 [mode2] datatype2, ...)
IS [AS]
声明部分
BEGIN
执行部分
EXCEPTION
异常处理部分
END;

参数类型：

in为输入类型的参数 out为输出类型的参数。

存储过程调用

--方法一：
set serveroutput on
begin
 raisesalary(7369);
end;

--方法二(sqlplus)：
exec raisesalary(7369);

--方法三：
call raisesalary(7369);

不接收参数的过程

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');

接收输入类型的参数

创建过程：带有IN参数
根据输入的员工编号输出该员工的工资

create or replace procedure 
pro_query_emp(v_no in emp.empno%type)
as
v_sal emp.sal%type;
begin  
    select sal into v_sal from emp where empno=v_no;
    dbms_output.put_line('该员工薪水为：'||v_sal);
    exception
         when no_data_found then
                dbms_output.put_line('找不到该员工！');
end;

接收输出类型的参数

create or replace procedure 
pro_query_emp(v_no in number, out_sal out number)
as
begin  
    select sal into out_sal from emp where empno=v_no;
    exception
         when no_data_found then
                dbms_output.put_line('找不到该员工！');
end;

调用带有OUT参数 的过程
必须定义变量接收输出参数的数据

declare
    v_no emp.empno%type;
    v_sal emp.sal%type;
begin
    v_no:=&no;
    pro_emp_sal(v_no,v_sal);
    dbms_output.put_line('薪水是：'||v_sal);
end;

创建过程：带有IN OUT参数
IN OUT参数也称为输入输出参数，当使用这种参数时，在调用过程之前需要通过变量给该种参数传递数据，调用结束后，Oracle会通过该变量将过程结果传递给应用

create or replace procedure pro_testinout
(param_num in out number)
as
begin
    select sal into param_num from emp 
            where empno=param_num;
end;

PS:在存储过程中没有怎么学到参数传递的精髓，只是提到了位置传参、名称传参、组合传参三种传递方式。其具体方法如何巧妙使用还未掌握，还望各位大神指教，不尽感激。

三、函数

函数用于返回特定数据，如果在应用程序中经常需要通过执行SQL语句来返回特定数据，则可以基于这些操作创建特定的函数
函数和过程的结构类似，但必须有一个RETURN子句，用于返回函数值。函数说明要指定函数名、结果值的类型，以及参数类型等。

语法如下：

 CREATE [OR REPLACE] FUNCTION function_name
   (argument1 [model] datatype1,
    argument2 [mode2] datatype2,
...)
RETURN datatype
 IS|AS
   声明部分
  BEGIN
     执行部分
 EXCEPTION 
  异常处理部分
END;

由于函数有且只有一个返回值，所以在我眼中函数只是特殊的存储过程，在此只对其与存储过程进行一系列的对比。

过程与函数有许多相同的功能及特性：
1. 都使用IN模式的参数传入数据、OUT模式的参数返回数据
2. 输入参数都可以接收默认值，都可以传值
3. 调用时的实参都可以使用位置表示法或名称表示法
4. 都有声明部分、执行部分和异常处理部分
一般而言，如果需要返回多个值或不返回值，就使用过程如果只需要返回一个值，就使用函数虽然函数带OUT模式的参数也能返回多个值，但是一般都认为这种方法属于不好的编程习惯或风格过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值。

注：以上代码中的emp、dept表均为Oracle中测试用户scott的表。