Oracle pipeline table function 2

BecausePreviousThe pipeline table function writing is a bit rough. add an article for your convenience. The two functions initialize the function data of the pipeline table. Therefore, the function is designed to call another function to obtain the data-included part.
 
The complete code is as follows:
 

 
1: Create row Type
 
 
 
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: Create a table
 

 
Create or replace type subwhiteblack_table_type as table of subwhiteblack_row_type
 
 
 
3: Create a function to obtain row data
 

 
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
-- Custom variable Initialization
WHITELISTTOTALCOUNT: = 0;
WHITEGROUPCOUNT: = 0;
WHITEADDEDCOUNT: = 0;
WHITENOADDEDCOUNT: = 0;
WHITEDEMANDCOUNT: = 0;
BLACKCOUNT: = 0;
RESULTCOUNT: = 0;
ISTABLEEXIST: = 0;

-- SQL statement used to query the total number of enterprise whitelist groups
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;
-- Obtain the number of enterprise whitelist groups. If the number is greater than 0, group query is performed.
Execute immediate strsql into resultcount;
If resultcount> 0 THEN
STRSQL: = STRSQL | 'group by whitelist. SP_ID ';
--- Obtain the total number of whitelist groups for the Enterprise
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 ';
--- Number of white lists filled in by the Enterprise
STRSQL: = BASESTRSQL | 'where U. status in (1, 2 )';
Execute immediate strsql into whiteaddedcount;
--- Number of on-demand enterprise white lists
STRSQL: = BASESTRSQL | 'where U. STATUS = 3 ';
Execute immediate strsql into whitedemandcount;
--- Number of white lists not added to an enterprise
STRSQL: = BASESTRSQL | 'where U. STATUS = 4 ';
Execute immediate strsql into whitenoaddedcount;
--- Total number of enterprise white lists
WHITELISTTOTALCOUNT: = WHITEADDEDCOUNT + whitedemancount + WHITENOADDEDCOUNT;
End if;
--- Obtain the total number of enterprise blacklists SQL
STRSQL: = 'select COUNT (BLACK. ID) FROM NM_BLACK_AND_OBJECT black where black. SP_ID = '| SPID;
-- If the value is greater than 0, the group query is executed.
Execute immediate strsql into resultcount;
If resultcount> 0 THEN
STRSQL: = STRSQL | 'group by black. SP_ID ';
--- Obtain the total number of blacklists of the Enterprise
Execute immediate strsql into blackcount;
End if;
V_SUBWHITEBLACK_ROW: = SUBWHITEBLACK_ROW_TYPE (SPID, SPNAME, WHITEGROUPCOUNT, WHITELISTTOTALCOUNT, WHITEADDEDCOUNT, WHITENOADDEDCOUNT, whitedemancount, BLACKCOUNT );

RETURN V_SUBWHITEBLACK_ROW;
END;
 
 
 
4: Create a function to obtain table data
 
 
 
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
 
--- Initialize the primary account name
CONSTANTSTR: = 'Primary account ';
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 there is no subaccount information, exit; otherwise, continue to execute
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
 
-- Call the function for row data to assign a scalar value.
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. Call methods in java
 
//? It is a parameter dashboard, which is no big difference from a common select statement.
 
Select * from table (getSubWhiteBlackList (?,?)) ;
From java Xiaosheng