管理书籍排行榜,欢乐颂第三季

主頁 > 知識庫 > Oracle中關(guān)于數(shù)據(jù)庫存儲過程和存儲函數(shù)的使用

Oracle中關(guān)于數(shù)據(jù)庫存儲過程和存儲函數(shù)的使用

存儲過程和存儲函數(shù)指存儲在數(shù)據(jù)庫中供所有用戶程序調(diào)用的子程序叫存儲過程、存儲函數(shù)。
存儲過程沒有返回值。存儲函數(shù)有返回值

創(chuàng)建存儲過程
      用CREATE PROCEDURE命令建立存儲過程和存儲函數(shù)。

      語法：
create [or replace] PROCEDURE過程名(參數(shù)列表)
AS
        PLSQL子程序體；

存儲過程示例：為指定的職工在原工資的基礎(chǔ)上長10%的工資

/*
為指定的職工在原工資的基礎(chǔ)上長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
--存儲過程調(diào)用
--方法一
SQL> set serveroutput on
SQL> exec raisesalary(7369);

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

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

PL/SQL procedure successfully completed

      存儲函數(shù)
      函數(shù)（Function）為一命名的存儲程序，可帶參數(shù)，并返回一計算值。函數(shù)和過程的結(jié)構(gòu)類似，但必須有一個RETURN子句，用于返回函數(shù)值。函數(shù)說明要指定函數(shù)名、結(jié)果值的類型，以及參數(shù)類型等。

     建立存儲函數(shù)的語法：

CREATE [OR REPLACE] FUNCTION函數(shù)名(參數(shù)列表)
RETURN 函數(shù)值類型
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   函數(shù)的調(diào)用

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

       觸發(fā)器
       數(shù)據(jù)庫觸發(fā)器是一個與表相關(guān)聯(lián)的、存儲的PL/SQL程序。每當一個特定的數(shù)據(jù)操作語句(Insert,update,delete)在指定的表上發(fā)出時，Oracle自動地執(zhí)行觸發(fā)器中定義的語句序列。

       觸發(fā)器的類型
         語句級觸發(fā)器
        在指定的操作語句操作之前或之后執(zhí)行一次，不管這條語句影響了多少行。

         行級觸發(fā)器（FOR EACH ROW）
        觸發(fā)語句作用的每一條記錄都被觸發(fā)。在行級觸發(fā)器中使用old和new偽記錄變量,識別值的狀態(tài)。

      創(chuàng)建觸發(fā)器
CREATE [or REPLACE] TRIGGER 觸發(fā)器名
   {BEFORE | AFTER}
   {DELETE | INSERT | UPDATE [OF列名]}
   ON 表名
   [FOR EACH ROW [WHEN(條件) ] ]
   PLSQL 塊

       示例1：限制非工作時間向數(shù)據(jù)庫插入數(shù)據(jù)
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,'不能在非工作時間插入數(shù)據(jù)。');
    end if;
   end;
   /

Trigger created

觸發(fā)語句與偽記錄變量的值

觸發(fā)語句	:old	:new
Insert	所有字段都是空(null)	將要插入的數(shù)據(jù)
Update	更新以前該行的值	更新后的值
delete	刪除以前該行的值	所有字段都是空(null)

示例2：確認數(shù)據(jù)（檢查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
運行后結(jié)果：
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: 觸發(fā)器 'SCOTT.CHECKSAL'執(zhí)行過程中出錯

       觸發(fā)器總結(jié)
      觸發(fā)器可用于
•         數(shù)據(jù)確認
•         實施復雜的安全性檢查
•         做審計，跟蹤表上所做的數(shù)據(jù)操作等

      查詢觸發(fā)器、過程及函數(shù)
•         Select * from user_triggers;
•         Select * from user_source;

您可能感興趣的文章:

Oracle存儲過程基本語法介紹
Oracle 存儲過程總結(jié)(一、基本應用)
oracle 存儲過程、函數(shù)和觸發(fā)器用法實例詳解
分解oracle存儲過程或函數(shù)調(diào)試過程步驟

標簽：葫蘆島酒泉六安宜春泰安淮南海北孝感

巨人網(wǎng)絡通訊聲明：本文標題《Oracle中關(guān)于數(shù)據(jù)庫存儲過程和存儲函數(shù)的使用》，本文關(guān)鍵詞 Oracle,中,關(guān)于,數(shù)據(jù)庫,存儲,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡，涉及言論、版權(quán)與本站無關(guān)。