CREATE FUNCTION¶
说明¶
创建一个函数。
参数¶
OR REPLACE
OR REPLACE FUNCTION将要么创建一个新函数，要么替换现有的定义
[ schema. ] funcname
新函数的名字，可以指定模式名，并且必须符合标识符规则。 更多相关信息，请参见 关键字和标识符 。
argname
参数的名字，必须符合标识符规则。
在同一个函数中，各参数名不能重复。参数名中不能包含$符号。
在调用函数的时候，可以使用参数名来对参数赋值，不过如果使用参数名来对参数赋值必须在按序赋值的参数之后。
IN | OUT | INOUT | IN OUT
参数输入/输出类型
- IN 表示该参数是输入参数，在函数内不可改变传入的参数值
- OUT 表示该参数是输出参数，在函数内初始为空
- INOUT | IN OUT 表示该参数是输入输出参数
缺省则为 IN
NULL | NOT NULL
参数是否可以为空，缺省则为 NULL
- NULL 表示该参数可以为空
- NOT NULL 表示该参数不能为空
datatype
参数类型，输入类型可以是基本类型，也可以是复合类型，域类型，或者和一个现有字段相同的类型，一个字段的类型是用tablename.columnname%TYPE表示的；使用这个东西可以帮助函数独立于表定义的修改。
n
一个函数的参数的个数，现在最多为1024。
retype
返回的类型。
返回类型可以声明为一个基本类型，复合类型，域类型，或者和一个现有字段相同的类型，一个字段的类型是用tablename.columnname%TYPE表示的；使用这个东西可以帮助函数独立于表定义的修改。
func_body
函数体，有关说明请参见 PL过程语言开发手册 。
lanname
函数语言名，现有的函数语言为plOSCAR,SQL
PIPELINED
函数是否为管道函数
default_value
为该参数声明一个默认值
deterministic_clause
标识函数结果是否只是根据输入来决定，同时只能使用一个以下值：
- DETERMINISTIC：如果你的函数当输入一样时，会返回同样的结果，等同于IMMUTABLE。
- IMMUTABLE：如果你的函数当输入一样时，会返回同样的结果，等同于DETERMINISTIC。
- STABLE：如果你的函数当输入一样时，可能会返回不一样的结果，等同于VOLATILE。
- VOLATILE：如果你的函数当输入一样时，可能会返回不一样的结果，等同于STABLE。详情参见 sys_proc（函数/过程） 的字段 PROVOLATILE。
默认情况下是VOLATILE。如果要创建函数索引，则该函数必须是DETERMINISTIC修饰的。
例如，示例1。
invoker_rights_clause
标识函数执行时的权限。分为以下两种，同时只存在一种：
- AUTHID DEFINER：定义者权限，执行时函数的权限是定义者的权限。
- AUTHID CURRENT_USER：当前用户权限，执行函数时的权限是当前用户，即调用者。
默认情况下是AUTHID DEFINER。
例如，示例2。
注解
LANGUAGE SQL：对于语言为SQL的函数，函数体由SQL语句组成，绝大部分SQL语句都可以出现在函数里面，但是事务处理语句不能。
函数重载：神通数据库允许函数重载；也就是只要输入参数不同，几个不同的函数可以同名。参数不同包括参数类型不同和参数个数不同。
对Oracle的兼容：神通数据库在函数的创建语法上对Oracle进行兼容。
示例¶
示例1：有DETERMINISTIC标识的函数与没有DETERMINISTIC标识的函数的区别：
--不带有DETERMINISTIC标识的函数，用于f2函数调用
create or replace function f1(a int) return number is
r number;
begin
r := a * ( mod(RANDOM(), 10));
return r;
end;
/
--当两次随机数的结果不同时返回0，如果都相同就返回1
create or replace function f2() return int is
r int;
i int = 0;
begin
for x in (select f1(1) as f from dual CONNECT BY LEVEL<=5) loop
if i = 0 then--第一次执行需要初始化
r = x.f;
i = i+1;
else
if x.f <> r then--求得任何一个不连续相同的值就返回结果
return 0;
end if;
end if;
end loop;
return 1;--全部数据都相同的情况返回1
end;
/
select f2();--调用的f1函数没有DETERMINISTIC标识，结果返回0，意思是不全部相同
F2(int) |
-------------
0 |
总数目：1
--带有DETERMINISTIC标识的函数，用于f2函数调用
create or replace function f1(a int) return number DETERMINISTIC is
r number;
begin
r := a * ( mod(RANDOM(), 10));
return r;
end;
/
--当两次随机数的结果不同时返回0，如果都相同就返回1
create or replace function f2() return int is
r int;
i int = 0;
begin
for x in (select f1(1) as f from dual CONNECT BY LEVEL<=5) loop
if i = 0 then--第一次执行需要初始化
r = x.f;
i = i+1;
else
if x.f <> r then--求得任何一个不连续相同的值就返回结果
return 0;
end if;
end if;
end loop;
return 1;--全部数据都相同的情况返回1
end;
/
select f2();--调用的f1函数有DETERMINISTIC标识，结果返回1，意思是全部相同
F2(int) |
-------------
1 |
总数目：1
--创建函数索引
--带有DETERMINISTIC标识的函数，用于f2函数调用
create or replace function f1(a int) return number DETERMINISTIC is
r number;
begin
r := a * ( mod(RANDOM(), 10));
return r;
end;
/
create table t1(a int,b int);--测试表
create index index_t1 on t1(f1(b));--只有当函数有DETERMINISTIC标识才可以创建
explain select * from t1 where f1(b) > 9;--查看执行计划
QUERY PLAN(text)
----------------------
Index Scan using INDEX
_T1(Normal Index Scan
) on T1 (cost=0.00..
36.32 rows=2731 width
=8)
Index Key: ("F1(B)"
> 9)
总数目：2
示例2: 在sysdba模式下创建表，如果是CURRENT_USER时，使用其他用户则无法访问:
--初始化环境
connect sysdba/szoscar55;
drop table test cascade;
create table test(a int);
insert into test values (1);
insert into test values (2);
insert into test values (3);
drop user u1 cascade;
connect syssecure/szoscar55;
set min_password_len =0;
connect sysdba/szoscar55;
create user u1 with password 'sz_OSCAR55';
--创建错误的存储过程
create or replace function f1(a int) return int AUTHID current_user
as
declare
r int;
begin
select count(*) into r from test;
return r + a;
end;
/
grant EXECUTE on function f1(int) to u1;
CONNECT u1/sz_OSCAR55;
select sysdba.f1(1) from dual;
ERROR, 表或视图 "TEST" 不存在或无权访问
--正确的存储过程
CONNECT sysdba/szoscar55;
create or replace function f1(a int) return int AUTHID DEFINER
as
declare
r int;
begin
select count(*) into r from test;
return r + a;
end;
/
CONNECT u1/sz_OSCAR55;
select sysdba.f1(1) from dual;
F1(int) |
-------------
4 |
总数目：1
示例3: 创建函数、重载函数
-- 清理环境
DROP FUNCTION func1(INT, INT) CASCADE;
DROP FUNCTION func1(INT, INT, INT) CASCADE;
-- 创建函数
CREATE FUNCTION func1(pa INT, pb INT) RETURN INT AS
BEGIN
RETURN pa + pb;
END;
/
-- 重载函数
CREATE FUNCTION func1(pa INT, pb INT, pc INT) RETURN INT AS
BEGIN
RETURN pa + pb + pc;
END;
/
-- 调用两个参数的函数 func1
SELECT func1(1, 3);
FUNC1(int) |
----------------
4 |
总数目：1
-- 调用三个参数的函数 func1
SELECT func1(1, 3, 5);
FUNC1(int) |
----------------
9 |
总数目：1
-- 删除函数
DROP FUNCTION func1(INT, INT);
DROP FUNCTION func1(INT, INT, INT);
示例4: 创建管道函数
-- 创建返回值类型
create or replace type rec is object(a int, b varchar2(10));
/
create or replace type nt is table of rec;
/
--创建管道函数
create or replace function pipe_func return nt pipelined is
a rec;
begin
for i in 1..5 loop
a := rec(i, 'i:='||i);
pipe row(a);
end loop;
end;
/
select * from table(pipe_func);
A(int) |B(varchar) |
----------------------------
1 |i:=1 |
----------------------------
2 |i:=2 |
----------------------------
3 |i:=3 |
----------------------------
4 |i:=4 |
----------------------------
5 |i:=5 |
总数目：5
--删除函数
drop function pipe_func();
drop type nt;
drop type rec;