?????

?

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

????? 一下是完整代码：

?????

???? 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 ;

?

??? 4：创建获取table数据function

?

????? ?????CREATE OR REPLACE FUNCTION GETSUBWHITEBLACKLIST
?????(
????? MAINACCOUNTID IN NUMBER,
????? SUBACCOUNTNAME IN VARCHAR
?????)
?????RETURN SUBWHITEBLAC