一、简介
在使用Oracle数据过程中，函数是非常好用的，我们经常定义一个函数用来处理相同的相似的问题的结果。
通常我们使用函数返回的都是单独的值，可能是NUMBER，也可能是VARCHAR类型，其实使用函数也可以返回类似于表结构数据的形式的数据集。
最常用的是游标的方式，其次是Table的形式，最后又产生了管道的方式。管道的方式与前两者不同的地方有它可以不用返回值，即RETURN后不用接内容，它是一行一行的返回数据。Table和管道的方式在调用时都是通过'TABLE()'关键字将函数的返回内容仿真成一个数据集。
二、举例三种返回结果集的方法
1、以游标形式返回结果集
(1)创建函数
1
2
3
4
5
6
7
8
9 |
CREATE OR REPLACE FUNCTION FN_R_REFCUR(P_VALUE
IN VARCHAR2)
RETURN SYS_REFCURSOR
IS
C_EMPNO SYS_REFCURSOR;
BEGIN
OPEN C_EMPNO
FOR
SELECT NAME
,ADDR
FROM EMP
WHERE ADDR=P_VALUE;
RETURN
(C_EMPNO);
END
;
/
|
(2)调用函数
1 |
SELECT FN_R_REFCUR(
'Raphael'
)
FROM DUAL;
|
2、以Table形式返回结果集
(1)创建函数
--定义一个行类型
1 |
CREATE OR REPLACE TYPE TYPE_TTEMP_ROW
AS OBJECT(
NAME VARCHAR2(10), ADDR VARCHAR2(20));
|
--定义一个表类型
1 |
CREATE OR REPLACE TYPE TYPE_TEMP_TABLE
AS TABLE OF TYPE_TTEMP_ROW;
|
--创建函数
1
2
3
4
5
6
7
8
9
10
11
12
13
14 |
CREATE OR REPLACE FUNCTION FN_R_TAB (P_VALUE
IN VARCHAR2)
RETURN TYPE_TEMP_TABLE
IS
TEMP_ROW TYPE_TTEMP_ROW;
-- 定义单行
TEMP_TABLE TYPE_TEMP_TABLE := TYPE_TEMP_TABLE();
-- 定义返回结果,并初始化
BEGIN
FOR CURROW
IN (
SELECT NAME
, ADDR
FROM EMP
WHERE NAME
=P_VALUE)
-- 查询名字是参数的值的结果
LOOP
TEMP_ROW := TYPE_TTEMP_ROW(CURROW.
NAME
, CURROW.ADDR);
-- 获得一行
TEMP_TABLE.EXTEND;
-- 表类型增加一行(EXTEND就是扩展的意思,相当于增加一行数据空间)
TEMP_TABLE(EMP_TABLE.
COUNT
) := TEMP_ROW;
-- 一行放进去
END LOOP;
RETURN
(TEMP_TABLE);
END
;
/
|
(2)调用函数
1 |
SELECT *
FROM TABLE
(FN_R_TAB(
'Raphael'
));
|
3、以管道形式返回结果集
(1)创建函数
--定义一个行类型
1 |
CREATE OR REPLACE TYPE TYPE_TTEMP_ROW
AS OBJECT(
NAME VARCHAR2(10), ADDR VARCHAR2(20));
|
--定义一个表类型
1 |
CREATE OR REPLACE TYPE TYPE_TEMP_TABLE
AS TABLE OF TYPE_TTEMP_ROW;
|
--创建函数
1
2
3
4
5
6
7
8
9
10
11
12 |
CREATE OR REPLACE FUNCTION FN_R_PIP(P_VALUE
IN VARCHAR2)
RETURN TYPE_TEMP_TABLE PIPELINEDIS
TEMP_ROW TYPE_TTEMP_ROW;
--定义一个行对象类型变量
BEGIN
FOR CURROW
IN (
SELECT NAME
, ADDR
FROM EMP
WHERE NAME
=P_VALUE)
-- 查询名字是参数值的结果
LOOP
TEMP_ROW := TYPE_TTEMP_ROW(CURROW.
NAME
, CURROW.ADDR);
-- 获得一行
PIPE ROW (TEMP_ROW);
-- 返回一行
END LOOP;
RETURN
;
-- 这里返回没有值
END
;
/
|
(2)调用函数
1 |
SELECT *
FROM TABLE
(FN_R_PIP(
'Raphael'
));
|
官网地址:Oracle | Cloud Applications and Cloud Platform
