存儲過程、視圖、事務基礎
簡介：視圖用于延伸表的邊界，索引用于提高數據庫查找效率，存儲過程用于分裝業務邏輯，事務用于提升數據庫應用的健壯性。
【存儲過程】【*】
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;
浙公網安備 33010602011771號