??? ??

?

?由于上一篇關于管道表函數寫的有些粗糙，追加一篇，方便大家理解。兩個函數完成管道表函數數據的初始化，所以設計到一個函數中調用另一個函數獲取含數據部分。

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

?????

???? 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 SUBWHITEBLACK_TABLE_TYPE PIPELINED
?????AS
?????V_ROW_TYPE SUBWHITEBLACK_ROW_TYPE;
????? SPID NUMBER;
????? SPNAME VARCHAR(200);
????? WHITEGROUPCOUNT NUMBER;
????? WHITELISTCOUNT NUMBER;
????? WHITEADDEDCOUNT NUMBER;
????? WHITENOADDEDCOUNT NUMBER;
????? WHITEDEMANDCOUNT NUMBER;
????? BLACKCOUMT NUMBER;
????? RESULTCOUNT NUMBER;
????? ISTABLEEXIST NUMBER ;
????? STRSQL VARCHAR(2000);
????? CONSTANTSTR VARCHAR(20) ;
????? TYPE T_CUR IS REF CURSOR;
????? V_PCUR T_CUR;
????? TYPE NM_SP_INFO_AAT IS TABLE OF NM_SP_INFO.ID%TYPE
????? INDEX BY PLS_INTEGER;
????? NM_SP_INFO_IDS NM_SP_INFO_AAT ;
????? TYPE NM_SP_INFO_BBT IS TABLE OF NM_SP_INFO.SP_NAME%TYPE
????? INDEX BY PLS_INTEGER;
????? NM_SP_INFO_NAMES NM_SP_INFO_BBT ;
?????BEGIN

?????? ---初始化主賬戶名稱
?????? CONSTANTSTR := '主賬戶' ;
?????? IF? MAINACCOUNTID > 0 THEN
????????? IF SUBACCOUNTNAME IS NOT NULL AND INSTR(CONSTANTSTR,SUBACCOUNTNAME,-1,1) > 0 THEN
???????????? V_ROW_TYPE := GETSUBWHITEBLACKBYID(MAINACCOUNTID,CONSTANTSTR);??????? PIPE ROW (V_ROW_TYPE);
????????? END IF ;
????????? IF SUBACCOUNTNAME IS? NULL THEN
???????????? V_ROW_TYPE := GETSUBWHITEBLACKBYID(MAINACCOUNTID,CONSTANTSTR);
???????????? PIPE ROW (V_ROW_TYPE);
????????? END IF ;
?????? END IF ;

?????? STRSQL := 'SELECT SP.ID SPID ,SP.SP_NAME NAME FROM NM_SP_INFO SP WHERE SP.ID <> 0 AND SP.OPEN_SUBACCOUNT = 1' ;
?????? STRSQL := STRSQL || ' AND SP.ID <>'||MAINACCOUNTID||' AND SP.SP_GRPID ='||MAINACCOUNTID ;
?????? IF? SUBACCOUNTNAME IS NOT NULL THEN
?????????? STRSQL := STRSQL || ' AND SP.SP_NAME LIKE (''%'||SUBACCOUNTNAME||'%'')' ;
?????? END IF ;
?????? EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ('||STRSQL||')' INTO RESULTCOUNT ;
?????????? --如果沒有子賬戶信息，則退出;否則繼續執行
?????? IF RESULTCOUNT = 0 THEN
????????? RETURN ;
?????? END IF ;
?????? IF RESULTCOUNT > 0 THEN
????????? OPEN V_PCUR FOR STRSQL;
????????? FETCH V_PCUR BULK COLLECT INTO NM_SP_INFO_IDS,NM_SP_INFO_NAMES;
????????? FOR I IN NM_SP_INFO_IDS.FIRST .. NM_SP_INFO_IDS.LAST
????????? LOOP

???????????? --此處調用獲取行數據的function 進行標量賦值
????????????? V_ROW_TYPE := GETSUBWHITEBLACKBYID(NM_SP_INFO_IDS(I),NM_SP_INFO_NAMES(I));
????????????? PIPE ROW (V_ROW_TYPE);
????????? END LOOP ;
????????? CLOSE V_PCUR ;
?????? END IF ;

?????END ;

?

?

? 5:java中調用方法

???? //? 為參數站位符 , 和普通select語句沒什么太大區別

???? select * from table(getSubWhiteBlackList(?,?)) ;

?

?

? <a ></a>

?

???

?

?

?

?

??

oracle 管道表函數2