Oracle中 關於資料庫預存程序和儲存函數的使用

預存程序和儲存函數指儲存在資料庫中供所有使用者程式調用的子程式叫預存程序、儲存函數。
預存程序沒有傳回值。儲存函數有傳回值

建立預存程序
用CREATE PROCEDURE命令建立預存程序和儲存函數。

文法：
create [or replace] PROCEDURE過程名(參數列表)
AS
PLSQL子程式體；

預存程序樣本：為指定的職工在原工資的基礎上長10%的工資

/*
為指定的職工在原工資的基礎上長10%的工資，並列印工資前和工資後的工資
*/
SQL> 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;
1 /

Procedure created
--預存程序調用
--方法一
SQL> set serveroutput on
SQL> exec raisesalary(7369);

員工號:7369漲工資前
800漲工資後880

方法二
set serveroutput on
begin
raisesalary(7369);
end;
/

PL/SQL procedure successfully completed

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

建立儲存函數的文法：

CREATE [OR REPLACE] FUNCTION函數名(參數列表)
RETURN 函數實值型別
AS
PLSQL子程式體；

樣本：查詢某職工的年度營收。
SQL> /**/
/*
查詢某職工的總收入
*/
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;
/

Function created

l 函數的調用

SQL> declare
v_sal number;
begin
v_sal:=queryEmpSalary(7934);
dbms_output.put_line('salary is:'|| v_sal);
end;
/

salary is:15600

PL/SQL procedure successfully completed

SQL> begin
dbms_output.put_line('salary is:'|| queryEmpSalary(7934));
end;
/

salary is:15600

PL/SQL procedure successfully completed

觸發器
資料庫觸發器是一個與表相關聯的、儲存的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 塊

樣本1：限制非工作時間向資料庫插入資料
SQL> create or replace
trigger securityEmp
before insert on emp
declare
begin
if to_char(sysdate,'day')in('星期四','星期六','星期日')
or to_number(to_char(sysdate,'hh24'))not between 8 and 18 then
raise_application_error(-20001,'不能在非工作時間插入資料。');
end if;
end;
/

Trigger created

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

觸發語句	:old	:new
Insert	所有欄位都是空(null)	將要插入的資料
Update	更新以前該行的值	更新後的值
delete	刪除以前該行的值	所有欄位都是空(null)

樣本2：確認資料（檢查emp表中sal的修改值不低於原值）
SQL> create or replace trigger checkSal
before update of sal on emp
for each row
declare
begin
if :new.sal<:old.sal then
raise_application_error(-20001,'更新後的薪水比更新前小');
end if;
end;
/

Trigger created
運行後結果：
SQL> update emp set sal=260 where empno=7499;

update emp set sal=260 where empno=7499

ORA-20001: 更新後的薪水比更新前小
ORA-06512: 在 "SCOTT.CHECKSAL", line 4
ORA-04088: 觸發器 'SCOTT.CHECKSAL'執行過程中出錯

觸發器總結
觸發器可用於
• 資料確認
• 實施複雜的安全性檢查
• 做審計，跟蹤表上所做的資料操作等

查詢觸發器、過程及函數
• Select * from user_triggers;
• Select * from user_source;