Oracle學習（12）：存儲過程，函數和觸發器

存儲過程和存儲函數

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 replace
procedure queryEmpInfo(eno in number,
                                         pename out varchar2,
                                         psal   out number,
                                         pjob   out varchar2)
 */
	@Test
	public 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 param
			call.setInt(1, 7839);
			//declare out parameter
			call.registerOutParameter(2, OracleTypes.VARCHAR);
			call.registerOutParameter(3, OracleTypes.NUMBER);
			call.registerOutParameter(4, OracleTypes.VARCHAR);
			//run
			call.execute();
			//get returned values
			String 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 replace
function queryEmpIncome(eno in number)
return number	
 */
	@Test
	public 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);
		}
	}


	@Test
	public 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;