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