oracle資料庫之儲存函數和過程

標籤：end   dba   src   部門   ret   授權   esc   sqlplus   處理常式   

一、引言

    ORACLE 提供可以把 PL/SQL 程式儲存在資料庫中，並可以在任何地方來運行它。這樣就叫預存程序或函數。過程和函數統稱為 PL/SQL 子程式，他們是被命名的 PL/SQL 塊，均儲存在資料庫中，並通過輸入、輸出參數或輸入/輸出參數與其調用者交換資訊。過程和函數的唯一區別是函數總向調用者返回資料，而過程則不返回資料。

二、 建立函數

 （一）建立內嵌函數

文法如下：

CREATE [OR REPLACE] FUNCTION function_name

[ (argment [ { IN | IN OUT }] Type,

argment [ { IN | OUT | IN OUT } ] Type ]

[ AUTHID DEFINER | CURRENT_USER ]

RETURN return_type

{ IS | AS }

<類型.變數的說明>

BEGIN

FUNCTION_body

EXCEPTION

其它語句

END;

說明:

1) OR REPLACE 為可選. 有了它, 可以或者建立一個新函數或者替換相同名字的函數, 而不會出現衝突

2) 函數名後面是一個可選的參數列表, 其中包含 IN, OUT 或 IN OUT 標記. 參數之間用逗號隔開. IN 參數標記表示傳遞給函數的值在該函數執行中不改變; OUT 標記表示一個值在函數中進行計算並通過該參數傳遞給調用語句; IN OUT 標記表示傳遞給函數的值可以變化並傳遞給調用語句. 若省略標記, 則參數隱含為 IN。

3) 因為函數需要返回一個值, 所以 RETURN 包含返回結果的資料類型.

例1. 不帶參數的函數

 

例2.擷取某部門的工資總和：

 

（二）內嵌函數的調用

        函式宣告時所定義的參數稱為形式參數，應用程式調用時為函數傳遞的參數稱為實際參數。應用程式在調用函數時，可以使用以下三種方法向函數傳遞參數：

第一種參數傳遞格式稱為位置標記法，格式為：

 argument_value1[,argument_value2 …]

例 3：計算某部門的工資總和：

 

第二種參數傳遞格式稱為名稱標記法，格式為：

argument => parameter [,…]

其中：argument 為形式參數，它必須與函數定義時所聲明的形式參數名稱相同。Parameter 為實際參數。

在這種格式中，形勢參數與實際參數成對出現，相互間關係唯一確定，所以參數的順序可以任意排列。

例 4：計算某部門的工資總和：

 

第三種參數傳遞格式稱為混合標記法：

即在調用一個函數時，同時使用位置標記法和名稱標記法為函數傳遞參數。採用這種參數傳遞方法時，使用位置標記法所傳遞的參數必須放在名稱標記法所傳遞的參數前面。也就是說，無論函數具有多少個參數，只要其中有一個參數使用名稱標記法，其後所有的參數都必須使用名稱標記法。

例 5：

 

     無論採用哪一種參數傳遞方法，實際參數和形式參數之間的資料傳遞只有兩種方法：傳址法和傳值法。所謂傳址法是指在調用函數時，將實際參數的地址指標傳遞給形式參數，使形式參數和實際參數指向記憶體中的同一地區，從而實現參數資料的傳遞。這種方法又稱作參照法，即形式參數參照實際參數資料。輸入參數均採用傳址法傳遞資料。

     傳值法是指將實際參數的資料拷貝到形式參數，而不是傳遞實際參數的地址。預設時，輸出參數和輸入/輸出參數均採用傳值法。在函數調用時，ORACLE 將實際參數資料拷貝到輸入/輸出參數，而當函數正常運行退出時，又將輸出形式參數和輸入/輸出形式參數資料拷貝到實際參數變數中。

（三）參數預設值

在 CREATE OR REPLACE FUNCTION 語句中聲明函數參數時可以使用 DEFAULT 關鍵字為輸入參數指定預設值。

例 6：

 

      具有預設值的函數建立後，在函數調用時，如果沒有為具有預設值的參數提供實際參數值，函數將使用該參數的預設值。但當調用者為預設參數提供實際參數時，函數將使用實際參數值。在建立函數時，只能為輸入參數設定預設值，而不能為輸入/輸出參數設定預設值。

三、預存程序

（一） 建立預存程序：

在 ORACLE SERVER 上建立預存程序,可以被多個應用程式調用,可以向預存程序傳遞參數,也可以向預存程序傳回參數.

（二）建立過程文法:

CREATE [OR REPLACE] PROCEDURE Procedure_name

[ (argment [ { IN | IN OUT }] Type,

argment [ { IN | OUT | IN OUT } ] Type ]

[ AUTHID DEFINER | CURRENT_USER ]

{ IS | AS }

<類型.變數的說明>

BEGIN

<執行部分>

EXCEPTION

<可選的異常錯誤處理程式>

END;

例 7．刪除指定員工記錄；

 

例 8．插入員工記錄；

 

（三） 調用預存程序

ORACLE 使用 EXECUTE 語句來實現對預存程序的調用：

EXEC[UTE]Procedure_name( parameter1, parameter2…);

例 9：查詢指定員工記錄；

 

調用方法:

 

例 10．計算指定部門的工資總和，並統計其中的職工數量。

 

調用方法:

 

 

（四）AUTHID

在建立預存程序時, 可使用 AUTHID CURRENT_USER 或 AUTHID DEFINER 選項,以表明在執行該過程時 Oracle 使用的許可權.

1) 如果使用 AUTHID CURRENT_USER 選項建立一個過程, 則 Oracle 用調用該過程的使用者權限執行該過程. 為了成功執行該過程, 調用者必須具有訪問該預存程序體中引用的所有資料庫物件所必須的許可權

2) 如果用預設的 AUTHID DEFINER 選項建立過程, 則 Oracle 使用流程所有人的特權執行該過程.為了成功執行該過程, 過程的所有者必須具有訪問該預存程序體中引用的所有資料庫物件所必須的許可權. 想要簡化應用程式使用者的特權管理, 在建立預存程序時, 一般選擇 AUTHID DEFINER 選項 –-- 這樣就不必授權給需要調用的此過程的所有使用者了.

（五） 開發儲存體過程步驟

開發儲存體過程、函數、包及觸發器的步驟如下：

1、 使用文字編輯處理軟體編輯預存程序源碼

使用文字編輯處理軟體編輯預存程序源碼，需將源碼存為文字格式設定。

2、在 SQLPLUS 或用調試工具將預存程序程式進行解釋

在 SQLPLUS 或用調試工具將預存程序程式進行解釋；

在 SQL>下調試，可用 START 或 GET 等 ORACLE 命令來啟動解釋。如：

SQL>START c:\stat1.sql

3、 調試源碼直到正確

我們不能保證所寫的預存程序達到一次就正確。所以這裡的調式是每個程式員必須進行的工作之一。

在 SQLPLUS 下來調式主要用的方法是：

（1）、使用 SHOW ERROR 命令來提示源碼的錯誤位置；

（2）、使用 user_errors 資料字典來查看各預存程序的錯誤位置。

4、 授權執行權給相關的使用者或角色

如果調式正確的預存程序沒有進行授權，那就只有建立者本人才可以運行。所以作為應用系統的一部分的預存程序也必須進行授權才能達到要求。在 SQL*PLUS 下可以用 GRANT 命令來進行預存程序的運行授權。

GRANT EXECUTE ON dbms_job TO PUBLIC WITH GRANT OPTION

5、 與過程相關資料字典

USER_SOURCE, ALL_SOURCE, DBA_SOURCE, USER_ERRORS

相關的許可權:

CREATE ANY PROCEDURE

DROP ANY PROCEDURE

在 SQL*PLUS 中，可以用 DESCRIBE 命令查看過程的名字及其參數表。

DESCRIBE Procedure_name;

（六） 刪除過程和函數

1．刪除過程

可以使用 DROP PROCEDURE 命令對不需要的過程進行刪除，文法如下：

DROP PROCEDURE [user.]Procudure_name;

2．刪除函數

可以使用 DROP FUNCTION 命令對不需要的函數進行刪除，文法如下：

DROP FUNCTION [user.]Function_name;

oracle資料庫之儲存函數和過程