存储过程、视图、事务基础

简介：视图用于延伸表的边界，索引用于提高数据库查找效率，存储过程用于分装业务逻辑，事务用于提升数据库应用的健壮性。

【存储过程】【*】

　　1）创建存储过程

　　　　create [or replace] procedure pro_name [(参数1,参数2,参数3…)]
　　　　　　　　　　　　　　　　　　　　--参数模式：in / out / in out；注意：in模式可以设定默认值,但是必须放参数末尾，比如：v_sum in int,v_i in int default 1
　　　　is|as
　　　　begin
　　　　　　plsql语句;
　　　　[exception]
　　　　　　[异常处理;]
　　　　end [pro_name];

　　2）调用存储过程
　　　　①命令窗口调用

　　　　exec pro_name(值1,值2...)；

　　　　②pl/sql窗口

　　　　begin
　　　　　--传参方式
　　　　　--参数定义顺序传递
　　　　　　pro_name(值1,值2...);
　　　　　--指定参数传递
　　　　　　pro_name(参数2=>值 ,参数3=>值,参数1=>值...);
　　　　　--混合传递
　　　　　　pro_name(值1,参数3=>值,参数2=>值...);
　　　　end;

　　3）删除存储过程

　　　　exec pro_name(值1,值2...);

　　示例1：创建存储过程，添加数据到salgrade表

　　select * from salgrade order by grade;
　　create or replace procedure pro_insert_salgrade(p_grade in number,p_losal in number,p_hisal in number)
　　is
　　begin
　　　　insert into salgrade values(p_grade,p_losal,P_hisal);
　　　　dbms_output.put_line('数据添加成功！');
　　　　commit;
　　end;

　　begin
　　　　pro_insert_salgrade(6,4001,4500);
　　　　pro_insert_salgrade(p_losal=>5001,p_grade=>7,p_hisal=>6000);
　　　　pro_insert_salgrade(8,p_hisal=>7000,p_losal=>6001);
　　end;

　　示例2：计算指定部门的平均工资

　　CREATE OR REPLACE PROCEDURE PRO_AVG_SAL(P_DEPTNO IN NUMBER,P_SAL OUT NUMBER)
　　AS
　　BEGIN
　　　　SELECT ROUND(AVG(SAL),2) INTO P_SAL FROM EMP WHERE DEPTNO = P_DEPTNO;
　　EXCEPTION
　　　　WHEN NO_DATA_FOUND THEN
　　　　P_SAL := 0;
　　END;

　　DECLARE
　　　　V_DEPTNO NUMBER := 10;
　　　　v_SAL NUMBER;
　　BEGIN
　　　　PRO_AVG_SAL(V_DEPTNO,V_SAL);
　　　　DBMS_OUTPUT.put_line(V_DEPTNO||'号部门平均工资:'|| v_SAL);
　　END;

　　示例3：计算low到high累加的和，使用存储过程进行改造[out模式]

　　create or replace procedure compute_sum(p_low in int,p_high in int,p_sum out int)
　　is
　　　　v_sum int := 0 ;
　　begin
　　　　for v_i in p_low..p_high loop
　　　　　　v_sum := v_sum + v_i;
　　　　end loop;
　　　　p_sum := v_sum;
　　end compute_sum;

　　declare
　　　　v_sum int := 0;
　　begin
　　　　compute_sum(20,50,v_sum);
　　　　dbms_output.put_line ( '20..50累加的和为:'|| v_sum);
　　　　v_sum:=0;
　　　　compute_sum(200,500,v_sum);
　　　　dbms_output.put_line('200..500累加的和为:'|| v_sum);
　　end;

【视图】

　　概念：视图是一个虚拟表，由存储的查询构成，可以将它的输出看成一个表。但是视图并不在数据库中存储数据值，其数据来自定义视图的查询语句所引用的表，数据库只在数据字典中存储视图的定义信息

　　语法结构：
　　CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name
　　[(alias[, alias]...)] --为视图产生的列定义的别名
　　AS select语句
　　[WITH CHECK OPTION [CONSTRAINT constraint]] --必须满足的约束
　　[WITH READ ONLY] --只读视图

　　简单视图，可读可操作

    create or replace view v_emp
    as select empno,ename,job,deptno from emp;

    select * from v_emp;

　　只读视图

　　create or replace view v_dept
　　as select * from dept with read only;

　　select * from v_dept;

　　复杂视图

--结合聚合函数
　　create or replace view v_emp2
　　as select deptno,count(*) ecount,max(sal) maxsal,min(sal) minsal,sum(sal) sumsal,avg(sal) avgsal from emp group by deptno;

　　select * from v_emp2;
　　select * from v_emp2 order by deptno;
　　select * from v_emp2 where deptno=20;

--三表连接查询
　　create or replace view v_emp3
　　as select e.empno,e.ename,e.job,e.hiredate,d.deptno,d.loc,s.grade
　　from emp e join dept d on e.deptno=d.deptno
　　join salgrade s on e.sal between s.losal and s.hisal
　　order by s.grade;

　　select * from v_emp3;

　　查看用户视图定义信息
　　　　select * from user_views;

　　重新编译视图
　　　　alter view v_emp compile;

　　删除视图
　　　　drop view v_name;

【事务基础】[*]

概念：事务是指代表一个业务边界（业务逻辑的多条语句组成）

特性ACID：原子性、一致性、隔离性、持久性
操作：提交commit、回滚rollback、保存点savepoint

--创建一个表account，用于接下来的操作
    create table account(
        acc_id int,
        acc_name varchar2(30),
        acc_balance int constraint ck_account_balance check(acc_balance>=0)
    );    

　　插入两条测试数据

    insert into account values (10,'PEPPA',1000);
    insert into account values (20,'SUZY',1000);
    -- 提交事务
    commit;
    select * from account;

　　转账操作

    DECLARE
        BALANCE_EXCEPTION EXCEPTION;  --定义异常变量
        PRAGMA EXCEPTION_INIT(BALANCE_EXCEPTION,-02290);  --关联异常
        v_money number(10,2) := 500;
    BEGIN
        UPDATE ACCOUNT SET ACC_BALANCE = ACC_BALANCE + v_money WHERE ACC_NAME='SUZY';
        UPDATE ACCOUNT SET ACC_BALANCE = ACC_BALANCE - v_money WHERE ACC_NAME='PEPPA';
        -- 如果没有异常，转账成功，提交事务
        COMMIT;
        dbms_output.put_line('转账成功');
    EXCEPTION -- 自定义异常发生，转账失败，回滚事务
        WHEN BALANCE_EXCEPTION THEN
            DBMS_OUTPUT.put_line('转账失败');
            ROLLBACK;
    END;            

　　 事务保存点

DECLARE
　　BALANCEEXCEPTION EXCEPTION;
　　PRAGMA EXCEPTION_INIT(BALANCEEXCEPTION,-02290);
　　v_money number(10,2) := 5000;
BEGIN
　　INSERT INTO ACCOUNT VALUES (30,'EMILY',1000);
　　INSERT INTO ACCOUNT VALUES (40,'PEDRO',1000);
　　-- 定义一个事务保存点（保存点可以定义多个）
　　SAVEPOINT INSERT_POINT;
　　-- 再次转账
　　UPDATE ACCOUNT SET ACC_BALANCE = ACC_BALANCE + v_money WHERE ACC_NAME='SUZY';
　　UPDATE ACCOUNT SET ACC_BALANCE = ACC_BALANCE - v_money WHERE ACC_NAME='PEPPA';
　　-- 如果没有异常，转账成功，提交事务
　　COMMIT;
EXCEPTION
　　WHEN BALANCEEXCEPTION THEN
　　　　DBMS_OUTPUT.put_line('转账失败');
　　-- 回滚事务到指定保存点
　　　　ROLLBACK TO INSERT_POINT;
　　-- 提交其它业务操作（保存点之前的业务）
　　　　COMMIT;
　　　　dbms_output.put_line('入户成功');
END;