預存程序和儲存函數和觸發器樣本

標籤：style   blog   http   color   資料   cti   

1、預存程序樣本：為指定的職工在原工資的基礎上長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;    / Procedure created SQL> set serveroutput on SQL> exec raisesalary(7369);

員工號:7369漲工資前

 

800漲工資後880

 

PL/SQL procedure successfully completed

 

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

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 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

 

3、建立觸發器樣本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

 

4、建立觸發器樣本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