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 ;
?
??? 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 href='http://www.1diaocha.com/user/Register.aspx?account=soqian'></a>
?
???
?
?
?
? 
??
