创建存储函数
语法格式
sql
CreProcedureStmt::=
{CREATE | CREATE OR REPLACE | CREATE OR REPLACE FORCE} ProcDef
ProcDecl::=
FUNCTION [IF NOT EXISTS] name_space func_args RETURN TypeName [PIPELINED] [AUTHID DEFAULT | AUTHID ColId | AUTHID USER]
| FUNCTION [IF NOT EXISTS] name_space func_args RETURN SELF AS RESULT [AUTHID DEFAULT | AUTHID ColId | AUTHID USER]参数说明
IF NOT EXISTS：创建存储函数时存在同名存储函数则忽略此错误，该关键字无法判断已有同名存储函数与当前创建存储函数属性是否一致。name_space：自定义函数名。func_args：形式参数名。TypeName：参数的类型名或返回值类型。
说明：
使用PIPELINED函数以外的存储函数时需给出返回值。
使用PIPELINED关键字指定的函数即成为PIPELINED函数，此类函数遵循以下规则：
- 返回结果必须为自定义
TABLE与VARRAY类型。 - 返回结果支持嵌套
RECORD类型，但不支持嵌套包内自定义类型。 - 函数可以不包含
RETURN子句，若包含RETURN子句，RETURN子句不能返回任何表达式。 - 函数只支持函数表的用法，不支持出现语句的其他位置。
- 使用PIPELINED函数作为函数表的查询语句不支持字段筛选、不支持字段使用其他函数或其他表达式、不支持条件过滤等。
- PIPELINED函数目前只支持配合
PIPE ROW子句提供实时数据返回功能。 - 使用
PIPE ROW语句用于快速实时返回结果，并且PIPE ROW语句只能出现在PIPELINED函数中。PIPE ROW返回结果必须为函数返回值中的元素类型。
普通函数示例
示例1
创建存储函数获取表test_proc_tab中ID字段的最大值，并返回该值；执行存储函数与执行存储过程不同点在于：执行存储过程使用关键字EXEC，执行存储函数使用SELECT。sqlSQL> CREATE OR REPLACE FUNCTION func_test() RETURN INT AS ret INT; BEGIN SELECT MAX(id) INTO ret FROM test_proc_tab; RETURN ret; END; / SQL> SELECT func_test() FROM dual; EXPR1 | ------------------------------------------------------------------------------ 10 |示例2
创建一个表值函数，返回类型为集合类型，与记录类型相似可以返回多行记录。sqlSQL> CREATE OR REPLACE FUNCTION func_tab(num INT) RETURN TABLE OF RECORD(id INT, name VARCHAR(10)) IS DECLARE subtype rec IS RECORD(id INT, name CHAR(10)); tab TABLE OF rec; BEGIN FOR i IN 1 .. num LOOP tab.EXTEND; tab(tab.last) := rec(i, 'name' || i); END FOR; RETURN tab; END; / SQL> SELECT * FROM TABLE(func_tab(3)); ID | NAME | ------------------------------------------------------------------------------ 1 | name1| 2 | name2| 3 | name3|
PIPELINED函数示例
自定义函数中使用PIPELINED函数
使用PIPELINED函数。
sql
-- 创建table
SQL> CREATE OR REPLACE TYPE tab AS TABLE OF VARCHAR(100);
/
-- 创建函数，pipe row内返回的varchar为tab集合的元素类型
SQL> CREATE OR REPLACE FUNCTION fun(a INT)
RETURN tab PIPELINED
IS
j VARCHAR:='test';
BEGIN
FOR i IN 1..10 LOOP
PIPE ROW('abc'||j);
j:= j||(a+i);
END LOOP;
PIPE ROW('pipe row end');
RETURN;
END;
/
-- 使用函数
SQL> SELECT * FROM TABLE(fun(1));
COLUMN_VALUE |
------------------------------------------------------------------------------
abctest|
abctest2|
abctest23|
abctest234|
abctest2345|
abctest23456|
abctest234567|
abctest2345678|
abctest23456789|
abctest2345678910|
pipe row end|错误使用示例：
查询使用PIPELINED函数当作目标字段，返回错误
E19067。sqlSQL> SELECT fun(1); Error: [E19067] 系统不支持的操作RETURN语句包含表达式，返回错误
E19212，E10133 L6 C5。sqlSQL> CREATE OR REPLACE FUNCTION fun() RETURN tab PIPELINED IS BEGIN PIPE ROW('PIPE ROW END'); RETURN 123; END; / Error: [E19212] 代码编译错误 [E10133 L6 C5] 管道函数中的 RETURN 语句不能包含表达式PIPE ROW子句在非PIPELINED函数中使用，返回错误
E19212，E10134 L5 C5。sqlSQL> CREATE OR REPLACE FUNCTION fun() RETURN tab IS BEGIN PIPE ROW('PIPE ROW END'); END; / Error: [E19212] 代码编译错误 [E10134 L5 C5] PIPE 语句只能在管道函数中使用返回类型不为有效的集合类型，返回错误
E10135。sqlSQL> CREATE OR REPLACE FUNCTION fun() RETURN VARCHAR PIPELINED IS BEGIN PIPE ROW('PIPE ROW END'); END; / Error: [E10135] 管道函数必须具有支持的集合返回类型
包中使用PIPELINED函数
包定义PIPELINED函数。
sql-- 创建包头 SQL> CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER AS TYPE num_tab IS TABLE OF NUMBER; FUNCTION pkg_fun(x NUMBER) RETURN num_tab PIPELINED; END; / -- 创建包体 SQL> CREATE OR REPLACE PACKAGE BODY pkg AS FUNCTION pkg_fun(x NUMBER) RETURN num_tab PIPELINED IS BEGIN PIPE ROW(x); PIPE ROW(x + 100); RETURN; END; END; / -- 使用包函数 SQL> SELECT * FROM TABLE(pkg.pkg_fun(103)); COLUMN_VALUE | ------------------------------------------------------------------------------ 103| 203|返回游标结果RECORD类型。
sql-- 创建表 SQL> CREATE TABLE t_tab(id int, c1 VARCHAR,c2 VARCHAR,c3 VARCHAR,c4 VARCHAR); -- 插入数据 SQL> INSERT INTO t_tab VALUES(1,'1','a','26','z')(2,'2','b','25','y')(3,'3','c','24','x')(4,'4','d','23','w'); -- 创建包头 SQL> CREATE OR REPLACE PACKAGE pkg IS TYPE var1 IS t_tab%ROWTYPE; TYPE var2 IS TABLE OF var1; FUNCTION pkg_func() RETURN var2 PIPELINED; END; / -- 创建包体 SQL> CREATE OR REPLACE PACKAGE BODY pkg IS FUNCTION pkg_func() RETURN var2 PIPELINED IS TYPE curtype IS REF CURSOR; c2 curtype; out_rec t_tab%ROWTYPE; BEGIN OPEN c2 FOR SELECT * FROM t_tab; LOOP FETCH c2 INTO out_rec; EXIT WHEN c2%NOTFOUND; PIPE ROW(out_rec); END LOOP; CLOSE c2; RETURN; END; END; / -- 使用包函数 SQL> SELECT * FROM TABLE(pkg.pkg_func()); ID | C1 | C2 | C3 | C4 | ------------------------------------------------------------------------------ 1 | 1| a| 26| z| 2 | 2| b| 25| y| 3 | 3| c| 24| x| 4 | 4| d| 23| w|
错误使用示例：
返回集合类型嵌套了包内定义类型（RECORD除外），返回错误
E10135。sqlSQL> CREATE OR REPLACE PACKAGE pkg IS TYPE var1 IS VARRAY(10) OF VARCHAR; TYPE var2 IS VARRAY(10) OF var1; FUNCTION pkg_func() RETURN var2 PIPELINED; END; / Error: [E10135] 管道函数必须具有支持的集合返回类型声明为PIPELINED函数、定义没有指定PIPELINED关键字，返回错误
E8012。sql-- 创建包头 SQL> CREATE OR REPLACE PACKAGE pkg IS TYPE var1 IS VARRAY(10) OF VARCHAR; FUNCTION pkg_func() RETURN var1 PIPELINED; END; / -- 创建包体 SQL> CREATE OR REPLACE PACKAGE BODY pkg is FUNCTION pkg_func() RETURN var1 IS BEGIN RETURN var1(1); END; END; / -- 使用函数 SQL> SELECT * FROM TABLE(pkg.pkg_func()); Error: [E8012] 包PKG成员函数PKG_FUNC不可用
UDT中使用PIPELINED函数
UDT定义PIPELINED函数。
sql
-- 创建类型var
CREATE OR REPLACE TYPE var AS VARRAY(10) OF VARCHAR(10);
-- 创建类型va2
CREATE OR REPLACE TYPE var2 AS VARRAY(10) OF var;
-- 创建类型obj
CREATE OR REPLACE TYPE obj AS OBJECT(
a INT,
MEMBER FUNCTION obj_func() RETURN var2 PIPELINED
);
-- 创建类型obj body
CREATE OR REPLACE TYPE BODY obj AS
MEMBER FUNCTION obj_func() RETURN var2 PIPELINED
IS
x var2 := var2();
BEGIN
x.extend(8);
x(1):=var(1);
x(5):=var(5);
FOR i IN x.FIRST..x.LAST LOOP
PIPE ROW(x(i));
END LOOP;
RETURN;
END;
END;
-- 使用函数
SELECT * FROM TABLE(obj(1).obj_func());
COLUMN_VALUE|
------------+
[1] |
<NULL> |
<NULL> |
<NULL> |
[5] |
<NULL> |
<NULL> |
<NULL> |错误使用示例：
声明为PIPELINED函数、定义没有指定PIPELINED关键字，返回错误E9015 L2 C5。
sql
-- 创建类型obj函数声明使用pipelined关键字
CREATE OR REPLACE TYPE obj AS OBJECT(
a INT,
MEMBER FUNCTION obj_func() RETURN var2 PIPELINED
);
-- 创建类型obj body 函数声明不使用使用pipelined关键字
CREATE OR REPLACE TYPE BODY obj AS
MEMBER FUNCTION obj_func() RETURN var2
IS
x var2 := var2();
BEGIN
x.EXTEND(8);
x(1):=var(1);
x(5):=var(5);
FOR i IN x.FIRST..x.LAST LOOP
PIPE ROW(x(i));
END LOOP;
RETURN;
END;
END;
Error: [E9015 L2 C5] 成员函数OBJ_FUNC未声明