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>