oracle 管道表函数2 - 数据库编程

由于上一篇关于管道表函数写的有些粗糙，追加一篇，方便大家理解。两个函数完成管道表函数数据的初始化，所以设计到一个函数中调用另一个函数获取含数据部分。

一下是完整代码：

1：创建row类型

create or replace type subwhiteblack_row_type as object
(
spid number,
spname varchar(200),
whitegroupcount number,
whitelistcount number,
whiteaddedcount number,
whitenoaddedcount number,
whitedemandcount number,
blackcoumt number
)

2：创建table类型

create or replace type subwhiteblack_table_type as table of subwhiteblack_row_type

3：创建获取row数据的function

CREATE OR REPLACE FUNCTION GETSUBWHITEBLACKBYID
(
SPID IN NUMBER ,
SPNAME IN VARCHAR
)
RETURN SUBWHITEBLACK_ROW_TYPE
AS
WHITELISTTOTALCOUNT NUMBER;
WHITEGROUPCOUNT NUMBER;
WHITEADDEDCOUNT NUMBER;
WHITENOADDEDCOUNT NUMBER;
WHITEDEMANDCOUNT NUMBER;
BLACKCOUNT NUMBER;
RESULTCOUNT NUMBER;
ISTABLEEXIST NUMBER ;
BASESTRSQL VARCHAR(2000);
STRSQL VARCHAR(2000);
CONSTANTSTR VARCHAR(20) ;
V_SUBWHITEBLACK_ROW SUBWHITEBLACK_ROW_TYPE ;
BEGIN

--自定义变量初始化
WHITELISTTOTALCOUNT := 0;
WHITEGROUPCOUNT := 0;
WHITEADDEDCOUNT := 0;
WHITENOADDEDCOUNT := 0;
WHITEDEMANDCOUNT := 0;
BLACKCOUNT := 0;
RESULTCOUNT := 0;
ISTABLEEXIST := 0;

--查询企业白名单组总数的SQL
STRSQL := 'SELECT COUNT(WHITELIST.ID) FROM NM_WHITE_LIST WHITELIST WHERE 1=1 ';
IF SPID > 0 THEN
STRSQL := STRSQL || 'AND WHITELIST.SP_ID ='||SPID;
END IF ;
IF SPID <= 0 THEN
RETURN V_SUBWHITEBLACK_ROW ;
END IF ;
--获得企业白名单组数，如果大于0 执行分组查询
EXECUTE IMMEDIATE STRSQL INTO RESULTCOUNT ;
IF RESULTCOUNT > 0 THEN
STRSQL := STRSQL || ' GROUP BY WHITELIST.SP_ID ' ;
---获取该企业的白名单组总数
EXECUTE IMMEDIATE STRSQL INTO WHITEGROUPCOUNT ;
END IF ;
STRSQL := 'SELECT COUNT(*) FROM ALL_TABLES TALBES WHERE TALBES.TABLE_NAME = ''NM_NET_USER_'||SPID||'''' ;
EXECUTE IMMEDIATE STRSQL INTO ISTABLEEXIST ;
IF ISTABLEEXIST > 0 THEN
BASESTRSQL := 'SELECT COUNT(U.MDN) FROM NM_NET_USER_'||SPID||' U ' ;
---企业已填加白名单数
STRSQL := BASESTRSQL || ' WHERE U.STATUS IN (1,2)' ;
EXECUTE IMMEDIATE STRSQL INTO WHITEADDEDCOUNT ;
---企业点播白名单数
STRSQL := BASESTRSQL || ' WHERE U.STATUS = 3' ;
EXECUTE IMMEDIATE STRSQL INTO WHITEDEMANDCOUNT ;
---企业未添加白名单数
STRSQL := BASESTRSQL || ' WHERE U.STATUS = 4' ;
EXECUTE IMMEDIATE STRSQL INTO WHITENOADDEDCOUNT ;
---企业白名单总数
WHITELISTTOTALCOUNT := WHITEADDEDCOUNT + WHITEDEMANDCOUNT + WHITENOADDEDCOUNT ;
END IF ;
---获取企业黑名单总数SQL
STRSQL := 'SELECT COUNT(BLACK.ID) FROM NM_BLACK_AND_OBJECT BLACK WHERE BLACK.SP_ID ='||SPID ;
--如果大于0 执行分组查询
EXECUTE IMMEDIATE STRSQL INTO RESULTCOUNT ;
IF RESULTCOUNT > 0 THEN
STRSQL := STRSQL ||' GROUP BY BLACK.SP_ID' ;
---获取该企业的黑名单总数
EXECUTE IMMEDIATE STRSQL INTO BLACKCOUNT ;
END IF ;
V_SUBWHITEBLACK_ROW :=SUBWHITEBLACK_ROW_TYPE(SPID ,SPNAME,WHITEGROUPCOUNT,WHITELISTTOTALCOUNT,WHITEADDEDCOUNT,WHITENOADDEDCOUNT,WHITEDEMANDCOUNT,BLACKCOUNT);

RETURN V_SUBWHITEBLACK_ROW ;
END ;

oracle 管道表函数2 (一)