ORACLE官方SQL语言参考之关于用户定义函数的实例讲解

关于用户定义函数

可以在 PL/sql、Java 或 C 中编写用户定义函数, 以提供 sql 或 sql 内置函数中不可用的功能。用户定义的函数可以出现在 SQL 语句中, 而无论表达式的位置如何。

例如, 用户定义函数可用于以下内容:

select 语句的选择列表SELECT

WHERE子句的条件

CONNECTBY,STARTWITH,ORDERBY, 按子句GROUPBY

INSERT语句的VALUES子句

UPDATE语句的SET子句

注意：

Oracle SQL 不支持调用具有布尔参数或返回的函数。因此, 如果您的用户定义函数将从 SQL 语句中调用, 则必须设计它们以返回数字 (0 或 1) 或字符串 ('TRUE' 或 'FALSE').

user_defined_function::=

插图 "user_defined_function" 的说明

可选表达式列表必须与函数、包或运算符的属性相匹配。

对用户定义函数的限制DISTINCT的和ALL的关键字仅对用户定义的聚合函数有效。

另请参见:

创建函数, 以了解有关创建函数的信息, 包括对用户定义函数的限制

Oracle 数据库高级应用程序开发人员指南, 用于全面讨论用户功能的创建和使用

先决条件

用户定义的函数必须创建为顶级函数, 或者使用包规范声明, 然后才能在 SQL 语句中命名。

若要在 SQL 表达式中使用用户函数, 必须拥有或具有对用户函数的EXECUTE权限。若要查询使用用户函数定义的视图, 您必须在视图上具有SELECT权限。从视图中选择不需要单独的EXECUTE权限。

另请参见:

创建有关创建顶级函数的信息并创建包以了解有关指定打包函数的信息的功能

名称优先级

在 SQL 语句中, 数据库列的名称优先于没有参数的函数的名称。例如, 如果人力资源管理器在hr架构中创建以下两个对象:

CREATE TABLE new_emps (new_sal NUMBER, ...);

CREATE FUNCTION new_sal RETURN NUMBER IS BEGIN ... END;

然后在下面两个语句中, 对new_sal的引用指的是列new_emps.new_sal:

SELECT new_sal FROM new_emps;

SELECT new_emps.new_sal FROM new_emps;

要访问函数new_sal, 您可以输入:

SELECT hr.new_sal FROM new_emps;

下面是对 SQL 表达式中允许的用户函数的一些示例调用:

circle_area (radius)

payroll.tax_rate (empno)

hr.employees.tax_rate (dependent, empno)@remote

例子要从架构hr调用tax_rate用户函数, 请对tax_table中的ss_no和sal列执行该功能, 并指定以下内容:

SELECT hr.tax_rate (ss_no, sal)

INTO income_tax

FROM tax_table WHERE ss_no = tax_id;

inINTO子句是 PL/SQL, 允许您将结果放入变量income_tax.

命名约定

如果只给出一个可选架构或包名称, 则第一个标识符可以是架构名称或包名称。例如, 确定在参考PAYROLL单中是否有PAYROLL。TAX_RATE是一个架构或包名称, Oracle 数据库的收益如下:

检查当前架构中的PAYROLL包。

如果找不到PAYROLL包, 则查找包含顶级TAX_RATE函数的架构名称PAYROLL。如果找不到此类函数, 则返回错误。

如果在当前架构中找到了PAYROLL包, 请在PAYROLL包中查找TAX_RATE函数。如果找不到此类函数, 则返回错误。

还可以使用已为其定义的任何同义词引用存储的顶级函数。