ORACLE函数详解【weber出品】

时间：2023-01-07 08:45:45

一、什么是函数

一个函数:

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

SQL> variable sal number();

SQL> execute :sal:=get_sal();

PL/SQL 过程已成功完成。

SQL> col sal for 

SQL> print sal;

    SAL

-------

1.2 使用本地变量获取结果

declare

  sal number();

begin

  sal := get_sal();

  dbms_output.put_line('员工的工资：' || sal);

end;

2. 作为参数传递给另一个子程序

SQL> set serveroutput on

SQL> execute dbms_output.put_line(get_sal());

PL/SQL procedure successfully completed

SQL> select get_sal() from dual;

GET_SAL()

------------

3. 在SQL语句中使用

SQL> select get_sal() from dual;

GET_SAL()

------------

四、用户定义的函数在SQL语句中的优势

函数：

1. oracle自定义的函数

2. 用户自定义的函数

优势：

1. 在SQL无法使用的时候，可以扩展SQL语句

2. 在WHERE子句中用来过滤数据时，可以提高过滤数据的效率

3. 可以操作数据

在SQL表达式中使用函数: 示例

create or replace function tax(value employees.salary%type) return number is

begin

  return(value * 0.08);

end;
SQL> select first_name,tax(salary) from employees;

FIRST_NAME           TAX(SALARY)

-------------------- -----------

Donald

Douglas

Jennifer

             .......

Kevin

 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

    (, 'Frost', 'jfrost@company.com', SYSDATE, 'SA_MAN', sal);

return (sal+);

 end;
在sql中调用这个函数:
SQL> update employees set salary=dml_call_sal() where employee_id=;

update employees set salary=dml_call_sal() where employee_id=

ORA-: 表 HR.EMPLOYEES 发生了变化, 触发器/函数不能读它

ORA-: 在 "HR.DML_CALL_SAL", line

七、查看函数的数据字典

以下视图可以查看函数的信息:

USER_SOURCE 

ALL_SOURCE

DBA_SOURCE

SELECT text

FROM   user_source

WHERE  type = 'FUNCTION'

and    name ='DML_CALL_SAL'

ORDER BY line;

使用USER_OBJECTS查看类型为FUNCTION的对象名

SELECT object_name

FROM   user_objects

WHERE  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                                         DML_CALL_SAL                   FUNCTION

begin                                                                                      DML_CALL_SAL                   FUNCTION

  insert into employees(employee_id, last_name, email, hire_date, job_id, salary           DML_CALL_SAL                   FUNCTION

  VALUES                                                                                   DML_CALL_SAL                   FUNCTION

    (, 'Frost', 'jfrost@company.com', SYSDATE, 'SA_MAN', sal);                            DML_CALL_SAL                   FUNCTION

return (sal+);                                                                          DML_CALL_SAL                   FUNCTION

 end;                                                                                      DML_CALL_SAL                   FUNCTION

 rows selected

八、过程与函数对比

九、删除函数

删除函数:

语法: DROP FUNCTION function_name

示例:DROP FUNCTION get_sal;

当函数被删除以后，在函数的所有权限都被删除

The CREATE OR REPLACE 语法等于先删除函数，燃烧再创建，权限仍然被保留

十、权限

1. 定义者权限（default）

SQL> conn hr/hr

Connected.

SQL> create table e as select * from employees;

Table created.

SQL> select count(*) from e;

  COUNT(*)

----------

SQL> 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/hr

create or replace procedure insert_e authid current_user is

begin

  insert into e select * from e;

  commit;

end;

grant execute on insert_e to scott;

conn scott/tiger

execute insert_e;

select count(*) from e;

  COUNT(*)

----------

conn hr/hr

select count(*) from e;

  COUNT(*)

----------
       


相关文章