Oracle 事务、过程和函数

                                             Oracle 事务、过程和函数
事务的定义与操作
  1.事务的特点 ACID：
  2.原子性(atomicity)：组成事务处理的语句形成了一个逻辑单元，不能只执行其中的一部分。 
  3.一致性(consistency)：在事务处理执行前后，数据库是一致的(数据库数据完整性约束)。 
  4.隔离性(isolcation)：一个事务处理对另一个事务处理的影响。    
  5.持续性(durability)：事务处理的效果能够被永久保存下来 。

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


Savepoint保存点 (例子）
        select * from emp
        savepoint A;
        insert into emp e (e.empno,e.ename,e.job,e.mgr) values (9999,'xiaoming','clerk',7902)
        savepoint B
        delete from emp where emp.empno=9999
        savepoint C
        rollback to C
        rollback to B
        rollback to A

提交或回滚前的数据状态(在没有上锁的情况下)
   1.改变前的数据状态是可以恢复的
   2.执行 DML 操作的用户可以通过 SELECT 语句查询之前的修正
   3.其他用户不能看到当前用户所做的改变，直到当前用户结束事务。
提交后的数据状态
   1.数据的改变已经被保存到数据库中。
   2.改变前的数据已经丢失。
   3.所有用户可以看到结果。
   4.锁被释放， 其他用户可以操作涉及到的数据。
   5.所有保存点被释放。
**************************************************************
                    存储过程
什么是存储过程？
       :存储过程一般用于执行一个指定的操作，可以将常用的特定操作封装成过程。
不接收参数的过程:(例子)
create or replace procedure P1 as
begin
  dbms_output.put_line('Current date is:' ||
                       to_char(sysdate, 'yyyy-mm-dd'));
end;
begin
  p1();
end;
结果：Current date is:2018-03-07

接收输入类型的参数:(例子)
1.
create or replace procedure addyonghu(stu_id   in number,
                                        stu_name in nvarchar2,
                                        class_id in number,
                                        sex      in nvarchar2,
                                        email    in nvarchar2,
                                        address  in nvarchar2) as
  begin
    insert into student
    values
      (stu_id, stu_name, class_id, sex, email, address);
  end;
call addyonghu(1012,'邵梦佳',2,'女','smj@163.com','河南巩义');
2.
create or replace procedure getemailbyid(stuid in student.stu_id%type) 
as
  v_email student.email%type;
begin
  select s.email into v_email from student s where s.stu_id = stuid;
  dbms_output.put_line('id号为' || stuid || '的邮箱为:' || v_email);
exception
  when no_data_found then
    dbms_output.put_line('根据id号' || stuid || '找不到该学员');
end;
call getemailbyid(1012);

接收输出类型的参数:(例子)
create or replace procedure getclassinfor(v_id        in student.stu_id%type,
                                          class_infor out class%rowtype) as
  class_infor1 class%rowtype;
begin
  select *
    into class_infor
    from class
   where class.class_id =
         (select s.class_id from student s where s.stu_id = v_id);
  class_infor1 := class_infor;
end;

declare
  class_information class%rowtype;
begin
  getclassinfor(1002, class_information);
  dbms_output.put_line(class_information.class_id ||
                       class_information.class_name ||
                       class_information.class_teacher);
end;

in out参数:(例子)
create or replace procedure studentinfo(student_information in out student%rowtype) as
begin
  select *
    into student_information
    from student stu
   where stu.stu_id = student_information.stu_id;
end;
     
 select * from student  
 
declare
  student_infor student%rowtype;
begin
  student_infor.stu_id := '1003';
  studentinfo(student_infor);
  dbms_output.put_line(student_infor.stu_id || student_infor.stu_name ||
                       student_infor.class_id || student_infor.sex ||
                       student_infor.email || student_infor.address);
end;

为形参传递变量和数据可以采用
   位置传递
       1.按位置传递是指在调用时按参数的排列顺序依次写出实参的名称，将形参与实参关联起来进行传递
       2.在这种方法中，形参与实参的名称是相互独立、没有关系的，次序才重要
       3.它比按名称传递方法在书写上简单，但如果更新了一个过程的形参的次序，则对应该过程的所有调用都必须进行相应的更新，所以会增加维护应用程序的难度

   名称传递
       1.按名称传递是指在调用时按照形参与实参的名称写出实参所对应的形参，将形参与实参关联起来进行传递
       2.在这种方法中，形参与实参的名称是相互独立、没有关系的，名称的对应关系很重要，但次序不重要
       3.名称传递在调用子程序时指定参数名，并使用关联符号“=>”为其提供相应的数值或变量

   组合传递
       1.可以将按位置传递、按名称传递两种方法在同一调用中混合使用
       2.但前面的实参必须使用按位置传递方法，而后面其余的实参则可以使用按名称传递的方法
********************************************************************
函数
        :函数用于返回特定数据，如果在应用程序中经常需要通过执行SQL语句来返回特定数据，则可以基于这些操作创建特定的函数
          函数和过程的结构类似，但必须有一个RETURN子句，用于返回函数值。函数说明要指定函数名、结果值的类型，以及参数类型等。
无参函数示例:(例子)
create or replace function fun_getchar return nvarchar2
 as
 begin
   return '俊成你好';
end;

select fun_getchar from dual
有参函数示例:(例子)
create or replace function fun(v_stu_id number ) return nvarchar2
 as 
 v_stu_name student.stu_name%type;
 begin
   select s.stu_name into v_stu_name from student s where s.stu_id=v_stu_id;
   return v_stu_name;
end; 

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