ORACLE函數詳解【weber出品】

標籤：style   blog   color   os   io   使用   ar   for   資料   

一、什麼是函數

一個函數:

1. 是命名的PL/SQL塊，必須返回一個值

2. 可以儲存到資料庫中重複執行

3. 可以作為運算式的一部分或者提供一個參數值

二、建立函數的文法

必須至少有一個傳回值，建立模板：

CREATE [OR REPLACE] FUNCTION function_name [(parameter1 [mode1] datatype1, ...)]RETURN datatype IS|AS [local_variable_declarations; …]BEGIN  -- actions;  RETURN expression;END [function_name];

建立函數樣本：

create or replace function get_sal(v_employee_id employees.employee_id%type)  return number is  v_salary employees.salary%type;begin  select salary    into v_salary    from employees   where employee_id = v_employee_id;  return v_salary;end;

三、執行函數的方式：

1. 作為運算式的一部分調用

1.1 使用主機變數擷取結果

SQL> conn hr/hrSQL> variable sal number(6);SQL> execute :sal:=get_sal(100);PL/SQL 過程已成功完成。SQL> col sal for 999999SQL> print sal;    SAL-------  24000

1.2 使用本地變數擷取結果

declare  sal number(6);begin  sal := get_sal(100);  dbms_output.put_line(‘員工的工資：‘ || sal);end;

2. 作為參數傳遞給另一個子程式

SQL> set serveroutput onSQL> execute dbms_output.put_line(get_sal(100));24000PL/SQL procedure successfully completedSQL> select get_sal(100) from dual;GET_SAL(100)------------       24000

3. 在SQL語句中使用

SQL> select get_sal(100) from dual;GET_SAL(100)------------       24000

 

四、使用者定義的函數在SQL語句中的優勢

函數：

1. oracle自訂的函數

2. 使用者自訂的函數

優勢：

1. 在SQL無法使用的時候，可以擴充SQL語句

2. 在WHERE子句中用來過濾資料時，可以提高過濾資料的效率

3. 可以操作資料

在SQL運算式中使用函數: 樣本

create or replace function tax(value employees.salary%type) return number isbegin  return(value * 0.08);end;

SQL> select first_name,tax(salary) from employees;FIRST_NAME           TAX(SALARY)-------------------- -----------Donald                       208Douglas                      208Jennifer                     352             .......Kevin                        240107 rows selected

五、使用者定義函數的調用位置

使用者定義的函數與內建的單行函數一樣，可以用於:

1. SELECT列表

2. WHERE和HAVING子句的條件運算式

3. 查詢語句的CONNECT BY, START WITH, ORDER BY, GROUP BY ，INSERT語句的VALUE子句、UPDATE語句的SET子句

 

六、在SQL運算式中調用函數的限制

在SQL運算式中調用函數的限制:

1. 在SQL語句中只能調用儲存函數（伺服器端），而不能調用用戶端的函數

2. 在SQL語句中調用的函數只能帶有輸入參數（IN），而不能帶有輸出參數（OUT）和輸入輸出參數（INOUT）

3. 在SQL語句中調用的函數只能使用SQL所支援的標準資料類型，而不能使用PL/SQL的特有資料類型（例如BOOLEAN,TABLE和RECORD等）

4. 在SQL語句中調用的函數不能包含INSERT,UPDATE,和DELETE語句

我們來驗證第四條：

create or replace function dml_call_sal(sal number) return number is begin  insert into employees(employee_id, last_name, email, hire_date, job_id, salary)  VALUES    (1, ‘Frost‘, ‘[email protected]‘, SYSDATE, ‘SA_MAN‘, sal);return (sal+100);  end;

在sql中調用這個函數:
SQL> update employees set salary=dml_call_sal(2000) where employee_id=107;update employees set salary=dml_call_sal(2000) where employee_id=107ORA-04091: 表 HR.EMPLOYEES 發生了變化, 觸發器/函數不能讀它ORA-06512: 在 "HR.DML_CALL_SAL", line 3

 

七、查看函數的資料字典

以下視圖可以查看函數的資訊:

USER_SOURCE ALL_SOURCEDBA_SOURCESELECT textFROM   user_sourceWHERE  type = ‘FUNCTION‘and    name =‘DML_CALL_SAL‘ORDER BY line; 

使用USER_OBJECTS查看類型為FUNCTION的對象名

SELECT object_nameFROM   user_objectsWHERE  object_type = ‘FUNCTION‘;

SQL> select object_name from user_objects where object_type=‘FUNCTION‘;
OBJECT_NAME
--------------------------------------------------------------------------------
GET_SAL
TAX
DML_CALL_SAL

使用USER_SOURCE查看函數的內容

SQL> select text,line,name ,type from user_source where type=‘FUNCTION‘ and name=‘DML_CALL_SAL‘;TEXT                                                                                   LINE NAME                           TYPE-------------------------------------------------------------------------------- ---------- ------------------------------ ------------function dml_call_sal(sal number) return number is                                        1 DML_CALL_SAL                   FUNCTIONbegin                                                                                     2 DML_CALL_SAL                   FUNCTION  insert into employees(employee_id, last_name, email, hire_date, job_id, salary          3 DML_CALL_SAL                   FUNCTION  VALUES                                                                                  4 DML_CALL_SAL                   FUNCTION    (1, ‘Frost‘, ‘[email protected]‘, SYSDATE, ‘SA_MAN‘, sal);                           5 DML_CALL_SAL                   FUNCTIONreturn (sal+100);                                                                         6 DML_CALL_SAL                   FUNCTION end;                                                                                     7 DML_CALL_SAL                   FUNCTION7 rows selected

八、過程與函數對比

 

九、刪除函數

刪除函數:

文法: DROP FUNCTION function_name樣本:DROP FUNCTION get_sal;

當函數被刪除以後，在函數的所有許可權都被刪除

The CREATE OR REPLACE 文法等於先刪除函數，燃燒再建立，許可權仍然被保留 

 

十、許可權

1. 定義者許可權（default）

SQL> conn hr/hrConnected.SQL> create table e as select * from employees;Table created.SQL> select count(*) from e;  COUNT(*)----------       107SQL> conn scott/tiger;Connected.

SQL> create table e as select * from emp;

Table created.

SQL> select count(*) from e;

COUNT(*)
----------
14

SQL> conn hr/hr;
Connected.
SQL> create or replace procedure insert_e is
2 begin
3 insert into e select * from e;
4 commit;
5 end;
6 /

Procedure created.

SQL> show user
USER is "HR"

SQL> grant execute on insert_e to scott;

Grant succeeded.

SQL> conn scott/tiger
Connected.
SQL> select count(*) from e;

COUNT(*)
----------
14

SQL> conn hr/hr;
Connected.
SQL> select count(*) from e;

COUNT(*)
----------
107

SQL> conn scott/tiger;
Connected.

SQL> execute hr.insert_e;

PL/SQL procedure successfully completed.

SQL> conn hr/hr
Connected.
SQL> select count(*) from e;

COUNT(*)
----------
214

2. 調用者許可權

預設情況，程式以其擁有者身份（定義者）執行。定義者許可權的程式與其所在模式綁定，調用對象不需要加上模式完整名稱。例如，假如模式HR和OE都有deparments表，HR擁有的程式可直接調用departments而不用HR.departments.而如果OE調用HR的程式，程式仍然調用的是HR的departments.

如果希望不同模式(schema)調用相同的程式卻可以操作各自擁有的對象，就可以在定義程式的時候加上AUTHID CURRENT_USER。

conn hr/hrcreate or replace procedure insert_e authid current_user isbegin  insert into e select * from e;  commit;end;grant execute on insert_e to scott;conn scott/tigerexecute insert_e;select count(*) from e;  COUNT(*)----------        28conn hr/hrselect count(*) from e;  COUNT(*)----------       214

ORACLE函數詳解【weber出品】