Oracle學習（12）：預存程序，函數和觸發器，oracle預存程序

Oracle學習（12）：預存程序，函數和觸發器，oracle預存程序
預存程序和儲存函數
l儲存在資料庫中供所有使用者程式調用的子程式叫預存程序、儲存函數。
注意：預存程序與儲存函式宣告變數時，用的是as   而不是declare預存程序與儲存函數區別預存程序不帶有返回值，儲存函數有返回值

預存程序建立預存程序l用CREATE PROCEDURE命令建立預存程序
l文法：

create [or replace] PROCEDURE過程名(參數列表) 

AS

        PLSQL子程式體；

建立預存程序簡單樣本/*
第一個預存程序：Hello World


調用預存程序：
1. exec sayHello();
2. begin
      sayHello();
   end;
   /


*/
create or replace procedure sayHello
as
  --變數說明
begin


  dbms_output.put_line('Hello World');


end;
/

調用預存程序
方法一

set serveroutput on

begin

 raisesalary(7369);

end;

/

方法二

set serveroutput on

exec raisesalary(7369);

預存程序（漲工資執行個體）執行個體一
為指定的職工在原工資的基礎上長10%的工資，並列印漲工資前和漲工資後的工資

代碼：**************************************************************************************************
/*
為指定的職工在原工資的基礎上長10%的工資,並列印漲工資前和漲工資後的工資


可能用到的sql語句
update emp set sal = sal * 1.1 where empno = empid;


*/


create or replace procedure raiseSalary(empid in number)
as
  pSal emp.sal%type; --儲存員工當前工資
begin
  --查詢該員工的工資
  select sal into pSal from emp where empno=empid;
  --給該員工漲工資
  update emp set sal = sal * 1.1 where empno = empid;
  
  --列印漲工資前後的工資
  dbms_output.put_line('員工號:' || empid || ' 漲工資前:' || psal || ' 漲工資後' || psal * 1.1);
end;
/

**************************************************************************************************

執行個體二為指定員工增加指定額度的工資(傳遞多個參數)

代碼：**************************************************************************************************
create or replace procedure raiseSalary2(empid in number, rate in NUMBER)
as
  pSal emp.sal%type; --儲存員工當前工資
begin
  --查詢該員工的工資
  select sal into pSal from emp where empno=empid;
  --給該員工漲工資
  update emp set sal = sal * rate where empno = empid;
  
  --列印漲工資前後的工資
  dbms_output.put_line('員工號:' || empid || ' 漲工資前:' || psal || ' 漲工資後' || psal * rate);
end;
/
**************************************************************************************************


儲存函數l函數（Function）為一命名的儲存程式，可帶參數，並返回一計算值。函數和過程的結構類似，但必須有一個RETURN子句，用於返回函數值。函數說明要指定函數名、結果值的類型，以及參數類型等。
l建立儲存函數的文法：l

CREATE [OR REPLACE] FUNCTION函數名(參數列表)

 RETURN  函數值類型

AS

PLSQL子程式體；

儲存函數樣本樣本：查詢某職工的年度營收。

代碼：
****************************************************************************
/*
查詢某職工的總收入。
*/


create or replace function queryEmpSalary(empid in number)
  RETURN NUMBER
as
  pSal number; --定義變數儲存員工的工資
  pComm number; --定義變數儲存員工的獎金
begin
  select sal,comm into pSal, pcomm from emp where empno = empid;
  return psal*12+ nvl(pcomm,0);
end;
/


****************************************************************************


儲存函數的調用調用一

declare

  v_salnumber;

begin

  v_sal:=queryEmpSalary(7934);

  dbms_output.put_line('salary is:' ||v_sal);

end;

/

調用二

 begin

   dbms_output.put_line('salaryis:' ||queryEmpSalary(7934));

 end;

過程和函數中的in和out
l一般來講，過程和函數的區別在於函數可以有一個返回值；而過程沒有返回值。
l但過程和函數都可以通過out指定一個或多個輸出參數。我們可以利用out參數，在過程和函數中實現返回多個值。



帶out函數的預存程序樣本

/*
out參數：查詢某個員工的姓名，月薪和職位
*/
create or replace procedure queryEmpInfo(eno in number,
                                         pename out varchar2,
                                         psal   out number,
                                         pjob   out varchar2)
as
begin
  select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;


end;
/

在out參數中使用遊標l首先申明包結構


l然後建立包體

在Java語言中訪問遊標類型的out參數


代碼實現：

package demo.test;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.ResultSet;import oracle.jdbc.OracleTypes;import oracle.jdbc.OracleCallableStatement;import org.junit.Test;import demo.utils.JDBCUtills;/* * Statement < PreparedStatement < CallableStatement */public class TestOracle {/* * create or replaceprocedure queryEmpInfo(eno in number,                                         pename out varchar2,                                         psal   out number,                                         pjob   out varchar2) */@Testpublic void testProcedure(){    //預存程序測試案例//{call procedure-name(??/)}String sql = "{call queryEmpInfo(?,?,?,?)}";Connection conn = null;CallableStatement call = null;try{conn = JDBCUtills.getConnection();call = conn.prepareCall(sql);//Set value to paramcall.setInt(1, 7839);//declare out parametercall.registerOutParameter(2, OracleTypes.VARCHAR);call.registerOutParameter(3, OracleTypes.NUMBER);call.registerOutParameter(4, OracleTypes.VARCHAR);//runcall.execute();//get returned valuesString name = call.getString(2);double sal = call.getDouble(3);String job = call.getString(4);System.out.println(name);System.out.println(sal);System.out.println(job);}catch(Exception ex){ex.printStackTrace();}finally{JDBCUtills.release(conn, call, null);}}/* * create or replacefunction queryEmpIncome(eno in number)return number */@Testpublic void testFunction(){     //儲存函數測試案例//{?=call procedure-name(??/)}String sql = "{?= call queryEmpIncome(?)}";Connection conn = null;CallableStatement call = null;try{conn = JDBCUtills.getConnection();call = conn.prepareCall(sql);call.registerOutParameter(1, OracleTypes.NUMBER);call.setInt(2, 7839);call.execute();double income = call.getDouble(1);System.out.println(income);}catch(Exception ex){ex.printStackTrace();}finally{JDBCUtills.release(conn, call, null);}}@Testpublic void testCursor(){    //訪問遊標測試案例String sql = "{call MYPACKAGE.queryEmpList(?,?)}";   //注意此句要有{}Connection conn = null;CallableStatement call = null;ResultSet rs = null;try{conn = JDBCUtills.getConnection();call = conn.prepareCall(sql);call.setInt(1, 20);call.registerOutParameter(2, OracleTypes.CURSOR);call.execute();rs = ((OracleCallableStatement)call).getCursor(2);while(rs.next()){String name = rs.getString("ename");double sal = rs.getDouble("sal");System.out.println(name+ "   " + sal);}}catch(Exception ex){ex.printStackTrace();}finally{JDBCUtills.release(conn, call, rs);}}}

在Java語言中調用在Java語言中調用預存程序

在Java語言中調用儲存函數

什麼時候用預存程序/儲存函數？
原則：如果只有一個返回值，用儲存函數；否則，就用預存程序。





觸發器觸發器定義資料庫觸發器是一個與表相關聯的、儲存的PL/SQL程式。每當一個特定的資料動作陳述式(Insert,update,delete)在指定的表上發出時，Oracle自動地執行觸發器中定義的語句序列。
觸發器的類型語句級觸發器•在指定的動作陳述式操作之前或之後執行一次，不管這條語句影響了多少行 。
行級觸發器（FOR EACH ROW）•觸發語句作用的每一條記錄都被觸發。在行級觸發器中使用old和new偽記錄變數, 識別值的狀態。


建立觸發器

  CREATE [or REPLACE] TRIGGER 觸發器名

   {BEFORE | AFTER}

   {DELETE | INSERT | UPDATE [OF列名]}

   ON  表名

   [FOR EACH ROW [WHEN(條件) ] ]

   PLSQL塊

觸發器簡單一實例
/*
第一個觸發器:對update
*/
create or replace trigger sayHello
after update
on emp
begin


  dbms_output.put_line('Hello World');


end;
/

行級觸發器觸發語句與偽記錄變數的值




觸發器應用情境執行個體情境一（語句觸發器）/*
觸發器應用情境一：實現複雜的安全性檢查


限制非工作時間向資料庫emp插入資料


1. 周末：星期六  星期日  to_char(sysdate,'day')
2. <9 or >18點  to_number(to_char(sysdate,'hh24'))


*/
create or replace trigger securityEmp
before insert 
on emp
begin
  if to_char(sysdate,'day') in ('星期三','星期六','星期日')
   or to_number(to_char(sysdate,'hh24')) not between 9 and 18 then    
      raise_application_error(-20001,'不能在非工作時間插入資料');


  end if;
end;
/


情境二（行級觸發器）
/*
觸發器應用情境二：確認資料


漲工資不能越漲少


行級觸發器的兩個偽記錄變數   :old , :new
*/
create or replace trigger checksal
before update
on emp
for each row
begin
  
   if :old.sal > :new.sal then 
    raise_application_error(-20002,'漲後的工資不能少於漲前的。漲後:'||:new.sal||'  漲前：'||:old.sal);
   
   end if;


end;
/




觸發器練習限制每個部門只招聘5名職工，超過計劃則報出錯誤資訊

*****************************************************************************
/*
練習：限制每個部門只招聘5名職工，超過計劃則報出錯誤資訊
*/
create or replace trigger limitEmpCount
before insert on emp
for each row
declare
  pCount number;-- 儲存每個部門的員工數
begin
  select count(*) into pcount from emp where deptno=:new.deptno;
  if pcount > 5 then raise_application_error(-20004,'部門:' || :new.deptno || ' 員工已有5人');
  end if;
end;