Oracle-day04 下

標籤：Oracle

三、預存程序
（一）什麼是預存程序
預存程序是被命名的 PL/SQL 塊，儲存於資料庫中，是資料庫物件的一種。應用程式可以調用預存程序，執行相應的邏輯。
預存程序與儲存函數都可以封裝一定的商務邏輯並返回結果，存在區別如下：
1、儲存函數中有傳回值，且必須返回；而預存程序沒有傳回值，可以通過傳出參數返回多個值。
2、儲存函數可以在 select 語句中直接使用，而預存程序不能。過程多數是被應用程式所調用。
3、儲存函數一般都是封裝一個查詢結果，而預存程序一般都封裝一段事務代碼。
（二）預存程序文法結構
建立或修改預存程序的文法如下:

CREATE [ OR REPLACE ] PROCEDURE預存程序名稱（參數名 類型, 參數名 類型, 參數名 類型）IS|AS變數聲明部分;BEGIN邏輯部分[EXCEPTION異常處理部分]END;參數只指定類型，不指定長度過程參數的三種模式：IN 傳入參數（預設）OUT 傳出參數 ，主要用於返回程式運行結果IN OUT 傳入傳出參數

（三）案例
1.建立不帶傳出參數的預存程序：添加業主資訊

-增加業主資訊序列[/align]create sequence seq_owners start with 11;--增加業主資訊預存程序create or replace procedure pro_owners_add(v_name varchar2,v_addressid number,v_housenumber varchar2,v_watermeter varchar2,v_type number)isbegininsert into T_OWNERSvalues( seq_owners.nextval,v_name,v_addressid,v_housenumber,v_watermeter,sysdate,v_type );commit;end;

PL/SQL 中調用預存程序

call pro_owners_add(‘趙偉‘,1,‘999-3‘,‘132-7‘,1);
JDBC 調用預存程序

/*** 增加* @param owners*/public static void add(Owners owners){java.sql.Connection conn=null;java.sql.CallableStatement stmt=null;try {conn=BaseDao.getConnection();stmt=conn.prepareCall("{callpro_owners_add(?,?,?,?,?)}");stmt.setString(1, owners.getName());stmt.setLong(2, owners.getAddressid());stmt.setString(3, owners.getHousenumber());stmt.setString(4, owners.getWatermeter());stmt.setLong(5, owners.getOwnertypeid());stmt.execute();} catch (SQLException e) {e.printStackTrace();}finally {BaseDao.closeAll(null, stmt, conn);}}

2建立帶傳出參數的預存程序
需求：添加業主資訊，傳出參數為新增業主的 ID

-增加業主資訊預存程序[/align]create or replace procedure pro_owners_add(v_name varchar2,v_addressid number,v_housenumber varchar2,v_watermeter varchar2,v_type number,v_id out number)isbeginselect seq_owners.nextval into v_id from dual;insert into T_OWNERSvalues( v_id,v_name,v_addressid,v_housenumber,v_watermeter,sysdate,v_type );commit;end;

PL/SQL 調用該預存程序

declarev_id number;--定義傳出參數的變數beginpro_owners_add(‘王WangWang‘,1,‘922-3‘,‘133-7‘,1,v_id);DBMS_OUTPUT.put_line(‘增加成功,ID:‘||v_id);end;

執行成功後輸出結果：

JDBC 調用預存程序

/*** 增加* @param owners*/public static long add(Owners owners){long id=0;java.sql.Connection conn=null;java.sql.CallableStatement stmt=null;try {conn=BaseDao.getConnection();stmt=conn.prepareCall("{callpro_owners_add(?,?,?,?,?,?)}");stmt.setString(1, owners.getName());stmt.setLong(2, owners.getAddressid());stmt.setString(3, owners.getHousenumber());stmt.setString(4, owners.getWatermeter());stmt.setLong(5, owners.getOwnertypeid());stmt.registerOutParameter(6, OracleTypes.NUMBER);//註冊傳出參數類型stmt.execute();id=stmt.getLong(6);//提取傳出參數} catch (SQLException e) {e.printStackTrace();}finally {BaseDao.closeAll(null, stmt, conn);}return id;}

四、觸發器
（一）什麼是觸發器
資料庫觸發器是一個與表相關聯的、儲存的 PL/SQL 程式。每當一個特定的資料動作陳述式(Insert,update,delete)在指定的表上發出時，Oracle 自動地執行觸發器中定義的語句序列。

觸發器可用於
l 資料確認
l 實施複雜的安全性檢查
l 做審計，跟蹤表上所做的資料操作等
l 資料的備份和同步
觸發器分類
l 前置觸發器（BEFORE）
l 後置觸發器（AFTER）
（二）建立觸發器的文法
文法：

CREATE [or REPLACE] TRIGGER 觸發器名BEFORE | AFTER[DELETE ][[or] INSERT] [[or]UPDATE [OF 列名]]ON 表名[FOR EACH ROW ][WHEN(條件) ]declare……beginPLSQL 塊End ；

FOR EACH ROW 作用是標註此觸發器是行級觸發器
語句級觸發器
在觸發器中觸發語句與偽記錄變數的值

（三）案例
1.前置觸發器
需求：當使用者輸入本月累計表數後，自動計算出本月使用數 。
代碼：

create or replace trigger tri_account_updatenum1beforeupdate of num1on t_accountfor each rowdeclarebegin:new.usenum:=:new.num1-:new.num0;end;

2.後置觸發器
需求：當使用者修改了業主資訊表的資料時記錄修改前與修改後的值

-建立業主名稱修改日誌表:用於記錄業主更改前後的名稱[/align]create table t_owners_log(updatetime date,ownerid number,oldname varchar2(30),newname varchar2(30));--建立後置觸發器，自動記錄業主更改前後日誌create trigger tri_owners_logafterupdate of nameon t_ownersfor each rowdeclarebegininsert into t_owners_logvalues(sysdate,:old.id,:old.name,:new.name);end;

測試：

--更新資料update t_owners set name=‘楊小花‘ where id=3;commit;--查詢日誌表select * from t_owners_log;

五、綜合案例
1.編寫 PL/SQL，用水噸數 12噸，業主類型為 1，計算階梯水費。
思路分析：
水費是實行階梯計算的，我們查詢價格表中業主類型為 1 的水費價格記錄

minnum 為下限值 ，maxnum 為上限值。上邊的記錄的含義是
5 噸以下的價格為 2.45
超過 5 噸不足 10 噸的價格為 3.45
超過 10 噸以上的價格為 4.45

如果噸數為 12。計算如下：

考慮到階梯的層次可能是不確定的，所以我們需要通過遊標查詢出階梯價格記錄，然後計算每一階梯的水費，然後相加。虛擬碼如下：

金額=0迴圈價格表{if( 上限值為空白 或者 總噸數<上限值) -- 最高階梯{//此為最後階梯 ，數量為超過上限值部分的噸數金額=金額+ 價格*（總噸數- 上限值）退出迴圈}else{//此為非最後階梯 ，數量為區間內的噸數金額=金額+ 價格*（上限值- 下限值）}}

語句：

declarev_ownertypeid number;--業主類型 IDv_usenum2 number(10,2);--總噸數v_money number(10,2);--總金額cursor cur_pricetable(v_type number) is select * fromt_pricetable where ownertypeid=v_type;--價格遊標v_pricetable t_pricetable%rowtype;--每階梯價格對象beginv_ownertypeid:=1;v_usenum2:=12;v_money:=0;for v_pricetable in cur_pricetable(v_ownertypeid)loopif v_pricetable.maxnum is null orv_usenum2<=v_pricetable.maxnum then--最後階梯（總噸數-下限值）*價格v_money:=v_money+v_pricetable.price*(v_usenum2-v_pricetable.minnum);exit;else--非最後階梯 (上限值-下限值)* 價格v_money:=v_money+v_pricetable.price*(v_pricetable.maxnum-v_pricetable.minnum);end if;end loop;DBMS_OUTPUT.put_line(‘階梯水費金額：‘||v_money);end;

2.儲存函數綜合案例：建立計算階梯水費的函數，參數為業主類型、噸數。

create or replace function fn_calmoney(v_ownertypeidnumber,v_usenum2 number)return numberisv_pricetable t_pricetable%rowtype;--價格行對象v_money number(10,2);--金額cursor cur_pricetable(v_type number) is select * fromt_pricetable where ownertypeid=v_type order by minnum;--定義遊標beginv_money:=0;--金額for v_pricetable in cur_pricetable(v_ownertypeid)loop--計算階梯水費--如果水費小於最大值，或最大值為 null 表示此階梯為最後一個階梯，--價格*（總噸數-此階梯下限值）if v_usenum2<= v_pricetable.maxnum orv_pricetable.maxnum is null thenv_money:=v_money+ v_pricetable.price* ( v_usenum2 -v_pricetable.minnum);exit;else -- 價格*（此階梯上限值-此階梯下限值）v_money:=v_money+ v_pricetable.price*(v_pricetable.maxnum-v_pricetable.minnum );end if;end loop;return v_money;end;測試此函數：select fn_calmoney(1,12) from dual;

3.觸發器綜合案例：當使用者輸入本月累計數後，自動計算階梯水費。

create or replace trigger tri_account_updatenum1beforeupdate of num1on t_accountfor each rowdeclarev_usenum2 number(10,2);--噸數begin--使用數賦值:new.usenum:=:new.num1-:new.num0;v_usenum2:= round( :new.usenum/1000,3);--計算噸數:new.money:=fn_calmoney(:new.ownertype,v_usenum2);--對金額列賦值end ;

修改某記錄，觀察結果。
4.預存程序綜合案例。
需求：增加業主資訊時，同時在賬務表（account）增加一條記錄，年份與月份為當前日期的年月，初始值（num0）為 0,其它欄位資訊（地區）與 t_owners表一致
痛點分析：

1. 如何取得年和月 用 to_char()函數
2. 如何取得地區 ID 參數中沒有直接提供地區 ID，我們可以通過 addressid
   到 address 表查詢
   建立預存程序語句：

create or replace procedure pro_owners_add()v_name varchar2,v_addressid number,v_housenumber varchar2,v_watermeter varchar2,v_type number,v_ownersuuid out numberisv_area number;--地區編號v_year char(4);--年份v_month char(2);--月份begin--提取序列值到變數select seq_owners.nextval into v_ownersuuid from dual;--根據地址編號查詢地區編號select areaid into v_area from t_address whereid=v_addressid;--年份v_year:=to_char(sysdate ,‘yyyy‘);--月份v_month:=to_char(sysdate,‘mm‘);--增加業主資訊insert into t_ownersvalues( v_ownersuuid,v_name,v_addressid,v_housenumber,v_watermeter,sysdate,v_type );--增加賬務表資訊insert into t_account(id,owneruuid,ownertype,areaid,year,month,num0)values(seq_account.nextval,v_ownersuuid,v_type,v_area,v_year,v_month,0 );commit;exceptionwhen NO_DATA_FOUND thenv_ownersuuid:=-1;rollback;end;

六、總結
（一）知識點總結
（二）上機任務布置

Oracle-day04 下