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);
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( , )) ;
摘自 java小生