Oracle存储过程和事务

存储过程和事务
一、存储过程
1、什么是存储过程？
存储过程（Stored Procedure ）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
存储过程是一种命名的pl/sql程序块。
有没有参数都可以，但是没有返回值（return）
存储过程保存在数据库中，不可以被sql语句直接执行或调用，只能通过excute命令执行或PL/SQL程序块内部调用。

优点：
1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
2.当对数据库进行复杂操作时(如对多个表进行Update、Insert、select、Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
3.存储过程可以重复使用,可减少数据库开发人员的工作量。
4.安全性高,可设定只有某用户才具有对指定存储过程的使用权

2、创建存储过程
存储过程和普通PL/SQL不同之处：
1)存储过程需要使用关键字procedure关键字
2)创建存储过程不需要declare，而使用create or replace关键字
语法：
create or replace procedure 过程名称 [参数1,参数2,…] is
begin
pl/sql语句;
exception
异常语句;
end;
/

1）创建无参过程。实现向dept表中插入一条数据。
create or replace procedure insertDept is
begin
insert into dept (deptno, dname, loc) values (50, ‘张三’, ‘河南新乡’);
dbms_output.put_line(‘插入成功！’);
end insertDept;
/

调用存储过程
方法1：
execute 存储过程名;

方法2：
begin
存储过程名;
end;

2）创建in参数（输入）过程
例如：创建存储过程，定义3个in变量，然后将这3个变量插入到dept表中。
create or replace procedure insertDeptIn(no in number,
name in varchar2,
address in varchar2) is
begin
insert into dept (deptno, dname, loc) values (no, name, address);
dbms_output.put_line(‘插入成功！’);
end insertDeptIn;
/

调用存储过程
execute insertDeptIn(60,‘李一’,‘河南新乡’);

3）创建out参数（输出）过程
例如：在dept表中，根据输入的编号deptno，查询出部门名称dname和位置loc
create or replace procedure select_dept(no in number,
name out varchar2,
address out varchar2) is
begin
–从dept表中查询出dname和loc的值，赋值给输出变量name和address
select dname, loc into name, address from dept where deptno = no;
end select_dept;
/

调用存储过程
declare
name varchar2(10); --声明变量，姓名
address varchar2(10); --声明变量，地址
begin
–调用存储过程，结果保存到变量name和address中
select_dept(50, name, address);
dbms_output.put_line(‘名称：’ || name || ’ 地址：’ || address);
end;
/

4）创建in out参数（输入输出）过程。可以传入值之后，再输出出来。先输入值，再输出值

例如：创建一个存储过程，定义in out模式。用来计算这个参数的平方。

create or replace procedure jiSuanPingFang(num in out number) is
begin
–计算num的平方之后，再返回给num
num := power(num, 2);
end jiSuanPingFang;
/

调用存储过程
declare
num number;
begin
num := 6;
–调用存储过程
jiSuanPingFang(num);
dbms_output.put_line(‘平方是：’||num);
end;
/

二、事务
1、什么是事务？
　　一组SQL,一个逻辑工作单位，执行时整体修改或者整体回退。
2.事务相关概念
　　1）事务的提交和回滚：commit/rollback
　　2）事务的开始和结束

开始事务：连接到数据库，执行DML、DCL、DDL语句
　　　　结束事务： 1. 执行DDL(例如create table),DCL(例如grant),系统自动执行commit语句

2. 退出/断开数据库的连接自动执行commit语句

3. 进程意外终止，事务自动rollback

3）保存点（savepoint）： 可以在事务的任何地方设置保存点，以便rollback
设置保存点
savepoint t1;
回滚到保存点
rollback to savepoint t1;

4）事务的四个特性ACID :
　　　　1. Atomicity（原子性）: 事务中sql语句不可分割，要么都做，要么都不做
　　　　2. Consistency(一致性) ： 指事务操作前后，数据库中数据是一致的，数据满足业务规则约束（例如账户金额的转出和转入），与原子性对应。
　　　　3. Isolation（隔离性）：多个并发事务可以独立运行，而不能相互干扰，一个事务修改数据未提交前，其他事务看不到它所做的更改。
　　　　4. Durability（持久性）：事务提交后，数据的修改是永久的。
　　5） 死锁：当两个事务相互等待对方释放资源时，就会形成死锁