oracle 结果集建表,Oracle通过调用函数返回一个查询的结果集

一.一般调用

1.创建一个Table

-- Create table

create table MASTER

(

ID   NVARCHAR2(10) not null,

NAME NVARCHAR2(100)

)

tablespace USERS

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

minextents 1

maxextents unlimited

);

-- Create/Recreate primary, unique and foreign key constraints

alter table MASTER

add constraint PK_ID primary key (ID)

using index

tablespace USERS

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 64K

minextents 1

maxextents unlimited

);

2.创建Package以及函数

CREATE OR REPLACE PACKAGE pkg_test

AS

TYPE ReturnDataSet IS REF CURSOR;

FUNCTION get return pkg_test.ReturnDataSet;

END pkg_test;

CREATE OR REPLACE PACKAGE BODY pkg_test

AS

FUNCTION get return pkg_test.ReturnDataSet

IS

ACursor pkg_test.ReturnDataSet;

BEGIN

OPEN ACursor FOR

SELECT ID, NAME

FROM MASTER;

return ACursor;

END get;

END pkg_test;

3.在pl/sql Developer中调用

select pkg_test.get()from dual

二.在此基础上进一步扩展一下，实现交叉表的形式

除了MASTER表之外，再创建一个Business表

MASTER表的ID字段与Business表的MasterID字段相关联

-- Create table

create table BUSINESS

(

ID       NVARCHAR2(10) not null,

NAME     NVARCHAR2(200),

MASTERID NVARCHAR2(10) not null,

COUNT    INTEGER

)

tablespace USERS

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

minextents 1

maxextents unlimited

);

-- Create/Recreate primary, unique and foreign key constraints

alter table BUSINESS

add constraint PK_BUSINESS_ID primary key (ID, MASTERID)

using index

tablespace USERS

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 64K

minextents 1

maxextents unlimited

);

添加数据后

Master表中数据为

ID        Name

001       一番

002       二番

003       三番

Bussiness表中数据为

ID            NAME         MASTERID        COUNT

10000001      佐藤          001              200

10000001      佐藤          002               10

10000001      佐藤          003               15

10000002      村上          001              100

10000002      村上          002               12

10000003      飛鳥桜        001               50

10000003      飛鳥桜        003               66

通过查询数据库，想实现以下效果

ID            NAME       一番     二番      三番

10000001      佐藤        200     10        15

10000002      村上        100     12

10000003      飛鳥桜       50                66

步骤

1：创建package(同上)

CREATE OR REPLACE PACKAGE pkg_test

AS

TYPE ReturnDataSet IS REF CURSOR;

FUNCTION get return pkg_test.ReturnDataSet;

END pkg_test;

2：创建package body

CREATE OR REPLACE PACKAGE BODY pkg_test

AS

FUNCTION get return pkg_test.ReturnDataSet

IS

ACursor pkg_test.ReturnDataSet;

STRSQL VARCHAR2(4000);

v_ID MASTER.ID%TYPE;

v_NAME MASTER.NAME%TYPE;

CURSOR cur is SELECT ID, NAME FROM MASTER;

BEGIN

STRSQL:='SELECT a.ID, a.NAME,';

/*for v in cur loop

STRSQL:=STRSQL || 'SUM(DECODE(MasterID,''' || v.id ||''',Count,''''))  ' || v.name ||',';

end loop;*/

OPEN cur;

LOOP

FETCH cur INTO v_ID,v_NAME;

EXIT WHEN cur%NOTFOUND;

STRSQL:=STRSQL || 'SUM(DECODE(MasterID,''' || v_ID ||''',Count,''''))  ' || v_NAME ||',';

END LOOP;

CLOSE cur;

STRSQL:=substr(STRSQL,0,length(STRSQL)-1);

STRSQL:=STRSQL ||'  from Business a  group by a.ID,a.Name';

DBMS_OUTPUT.put_line(STRSQL);

OPEN ACursor FOR

STRSQL;

return ACursor;

END get;

END pkg_test;

3：进行调试输出，即可得到想要的结果

SELECT PKG_TEST.get() FROM DUAL