可以按照最后的写。
工作中常需要经过一段复杂逻辑处理后,得出的一个结果集。并能够将这个结果集作为一个表看待,去进行关联查询
我一般采用建立函数function的方式来处理。
--创建包,声明function和type CREATE OR REPLACE PACKAGE PAK_TEM AS TYPE DATE_RECORD IS RECORD --自定义类型-行(含字段及类型) ( NAME VARCHAR2 (20), VALUE VARCHAR2 (20) ); TYPE DATE_TABLE IS TABLE OF DATE_RECORD; --自定义table类 FUNCTION GET_TERM_YEARS RETURN DATE_TABLE --返回table类型 PIPELINED; --流水式 END PAK_TEM; / --实现包体中的function CREATE OR REPLACE PACKAGE BODY PAK_TEM AS FUNCTION GET_TERM_YEARS RETURN DATE_TABLE PIPELINED IS L_RESULT DATE_RECORD; BEGIN FOR REC IN (SELECT DISTINCT TO_CHAR (STATUS_TIME, 'YYYY') FROM TEM_TB WHERE TO_CHAR (STATUS_TIME, 'YYYY') != '0001') LOOP L_RESULT.NAME := REC.YEAR || '年'; L_RESULT.VALUE := REC.YEAR; PIPE ROW (L_RESULT); --依次返回行 END LOOP; END; END PAK_TEM; /
像查询一个表一样来操作function,使用 TABLE(自定义函数)
SELECT * FROM TABLE(PAK_TEM.get_term_years());
PS:以下是定义oracle的table类型示例:
--oracle内置类型 TYPE STRING_TABLE IS TABLE OF VARCHAR(2000); --自定义类型(DATE_RECORD) TYPE DATE_RECORD IS RECORD --自定义类型-行(含字段及类型) ( NAME VARCHAR2 (20), VALUE VARCHAR2 (20) ); TYPE DATE_TABLE IS TABLE OF DATE_RECORD; --自定义table类(DATE_TABLE)
自己写的例子:
按照以上例子写的,package:
create or replace package datasTest
as
TYPE DATE_RECORD IS RECORD
(
EMPNO number,
ENAME VARCHAR2 (64)
);
type DATA_TABLE is table of DATE_RECORD;
-- Author : CCDSEW
-- Created : 2017/8/15 9:56:28
-- Purpose :
-- Public function and procedure declarations
function getDates(P_empno NUMBER) return DATA_TABLE PIPELINED;
end datasTest;
package body:
create or replace package body datasTest
as
/* TYPE DATE_RECORD IS RECORD
(
EMPNO number,
ENAME VARCHAR2 (64)
);*/
/* type DATA_TABLE is table of DATE_RECORD;*/
-- Function and procedure implementations
function getDates(P_EMPNO NUMBER) return DATA_TABLE PIPELINED is
L_RESULT DATE_RECORD;
begin
FOR DATA IN (SELECT E.EMPNO,E.ENAME FROM EMP E where E.EMPNO = P_EMPNO) LOOP
L_RESULT.EMPNO := DATA.EMPNO;
L_RESULT.ENAME := DATA.ENAME;
PIPE ROW(L_RESULT);
END LOOP;
end getDates;
end datasTest;
测试:
select datasTest.getDates(7369) from dual;
也可以这样写(比较适合oracle):
package:
create or replace package DATATEST001 is
-- Author : CCDSEW
-- Created : 2017/8/15 13:26:38
-- Purpose : test
-- Public type declarations
TYPE DATE_RECORD IS RECORD
(
EMPNO number,
ENAME VARCHAR2 (64)
);
type DATA_TABLE is table of DATE_RECORD;
-- Public function and procedure declarations
function getDates(P_empno NUMBER) return DATA_TABLE PIPELINED;
end DATATEST001;
package body:
create or replace package body DATATEST001 is
function getDates(P_EMPNO NUMBER) return DATA_TABLE PIPELINED is
L_RESULT DATE_RECORD;
begin
FOR DATA IN (SELECT E.EMPNO,E.ENAME FROM EMP E where E.EMPNO = P_EMPNO) LOOP
L_RESULT.EMPNO := DATA.EMPNO;
L_RESULT.ENAME := DATA.ENAME;
PIPE ROW(L_RESULT);
END LOOP;
end getDates;
end DATATEST001;
测试和之前一样。