ANBOB™

经常见到select * from table(function(args))的查询方法,对pl/sql 强大功能赞不绝口

看例子

只介绍基本的表定义
icme_org
(
org_id number pk number,
parent_org_id fk number,
org_code varchar2, — like 002 002001 002002
org_name varchar2)

icme_student
(ic_code varchar2(9),
name varchar2,
org_id number fk, — only in leaf org_id
..)

需求：根据org机构表的机构id返回它下面的所有人卡号、姓名
两种方法
1，
create or replace type stu_rows as object(
sno varchar2(10),
sname varchar2(40));

create or replace type stu_tab as table of stu_rows;

CREATE OR REPLACE FUNCTION getstus (f_obc NUMBER)
RETURN stu_tab
IS
l_stu_tab   stu_tab := stu_tab ();
BEGIN
SELECT stu_rows (ic_code, name)
BULK COLLECT INTO l_stu_tab
FROM icme_student
WHERE org_id IN (    SELECT org_id
FROM icme_org
WHERE CONNECT_BY_ISLEAF = 1
START WITH org_id = f_obc
CONNECT BY PRIOR org_id = parent_org_id);
return l_stu_tab;
end;
/

select * from table(getstus(37000211));

2,
create or replace type stu_rows as object(
sno varchar2(10),
sname varchar2(40));

create or replace type stu_tab as table of stu_rows;
CREATE OR REPLACE FUNCTION getstus2 (f_obc NUMBER)
RETURN stu_tab
PIPELINED
IS
BEGIN
FOR cur IN (SELECT ic_code, name
FROM icme_student
WHERE org_id IN (    SELECT org_id
FROM icme_org
WHERE CONNECT_BY_ISLEAF = 1
START WITH org_id = f_obc
CONNECT BY PRIOR org_id = parent_org_id))
LOOP
PIPE ROW (stu_rows (cur.ic_code, cur.name));
END LOOP;

RETURN;
END;

执行计划
———————————————————-
Plan hash value: 1438452351

———————————————————————————————
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————————
|   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    24   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| GETSTUS |       |       |            |          |
———————————————————————————————

在返回128行小数据量时cr gets 第一种略小于第二种，且都比直接查询还要小几个读

–仅参考。

extend read:

sqlserver 返回结果集方法

CREATE FUNCTION DBO.FN_GET_ROWS()
RETURNS @TABLE TABLE
(
TEST_ID INT,
TEST_DESC VARCHAR(30)
)
AS
BEGIN
INSERT @TABLE
SELECT TEST_ID, TEST_DESC
FROM DBO.TEST
RETURN
END
GO

DB2返回结果集方法
CREATE FUNCTION FN_GET_ROWS()
RETURNS TABLE
(
TEST_ID INT,
TEST_DATE TIMESTAMP
)
LANGUAGE SQL
READS SQL DATA
CALLED ON NULL INPUT
NO EXTERNAL ACTION
BEGIN ATOMIC
RETURN
SELECT TEST_ID, TEST_DATE
FROM TEST;
END@

打赏

微信扫一扫，打赏作者吧～