简单演示procedure

prompt
prompt Creating procedure P_BSS_QUERYACCOUNT
prompt =====================================
prompt
CREATE OR REPLACE PROCEDURE P_BSS_QUERYACCOUNT
/*
    Description  : 查询用户基本的订购信息
          Author : 刘宏宗 57983
    Date         : 2007-06-14
    Version      : VMS V2.0D500
    Caller       : com.huawei.umcp.bss.dao.AccountInfoDAO.query()
    Callee       :
    Comments     : 相关表: T_BSS_ACCOUNTINFO
    History      :
      1.  Date   : 2007-06-14
          Author : 刘宏宗 57983
    Modification : 创建
      2. ......
  */
(
  i_UserNo         VARCHAR2, -- 用户号码
  i_SubboxNo       VARCHAR2,--用户的子邮箱号码
  i_StartTime      DATE, -- 开始时间
  i_EndTime        DATE, -- 结束时间
  i_AreaCode       VARCHAR2, -- 区号
  i_StartNum       NUMBER, -- 起始记录数
  i_EndNum         NUMBER, -- 结束记录数
  i_QueryType      NUMBER, -- 查询类型 1查总数 2查内容  3按号码查询
  o_AccountInfoRef OUT SYS_REFCURSOR, -- 用户返回游标
  o_Count          OUT NUMBER, -- 总记录数(最大返回10000,10001表示超过10000条数据)
  o_Ret            OUT NUMBER -- 返回值
) AS
  v_StartTime DATE;
  v_EndTime   DATE;
BEGIN
  o_Ret := 1;
  o_Count := 0;

  IF i_StartTime IS NULL THEN
    v_StartTime := TO_DATE('1970', 'YYYY');
  ELSE
    v_StartTime := i_StartTime;
  END IF;

  IF i_EndTime IS NULL THEN
    v_EndTime := TO_DATE('2050', 'YYYY');
  ELSE
    v_EndTime := i_EndTime;
  END IF;

  IF i_QueryType = 1 THEN
    -- 判断用户号码是否为空
    IF i_UserNo IS NULL THEN
      SELECT COUNT(1)
        INTO o_Count
        FROM T_BSS_ACCOUNTINFO t
       WHERE ROWNUM < 10001
         AND OPEN_TIME >= v_StartTime
         AND OPEN_TIME <= v_EndTime;
    ELSE
      IF I_AREACODE IS NULL THEN
        SELECT COUNT(1)
          INTO o_Count
          FROM T_BSS_ACCOUNTINFO t
         WHERE ROWNUM < 10001
           AND OPEN_TIME >= v_StartTime
           AND OPEN_TIME <= v_EndTime
           AND PARTCOL_USERNO = SUBSTR(i_UserNo, -3, 2);
      ELSE
        SELECT COUNT(1)
          INTO o_Count
          FROM T_BSS_ACCOUNTINFO t
         WHERE ROWNUM < 10001
           AND AREA_CODE = NVL(i_AreaCode, AREA_CODE)
           AND OPEN_TIME >= v_StartTime
           AND OPEN_TIME <= v_EndTime
           AND PARTCOL_USERNO = SUBSTR(i_UserNo, -3, 2);
      END IF;
    END IF;
    OPEN o_AccountInfoRef FOR
      SELECT NULL FROM dual WHERE 1 = 0;
    o_Ret := 0;
    RETURN;
  END IF;

  IF i_QueryType = 3 THEN
  OPEN o_AccountInfoRef FOR
        SELECT USER_NO,
               LEVEL_ID,
               STATUS,
               OPEN_TIME,
               AREA_CODE,
               PARTCOL_USERNO,
               ADDITIONAL_INFO_1,
               ADDITIONAL_INFO_2,
               ADDITIONAL_INFO_3,
               ATTRIBUTVE_POINT,
               DATASITE_CODE,
               FILESITE_CODE,
               FEE_TYPE,
               COUNTRY_CODE,
               SUBBOX_NO
          FROM T_BSS_ACCOUNTINFO
         WHERE USER_NO = I_USERNO
           AND ((I_AREACODE IS NULL) or(AREA_CODE = I_AREACODE ))
           AND OPEN_TIME >= v_StartTime
           AND OPEN_TIME <= v_EndTime
           AND PARTCOL_USERNO = substr(i_UserNo, -3, 2);
          
      SELECT COUNT(1)
        INTO o_Count
        FROM T_BSS_ACCOUNTINFO t
       WHERE USER_NO = i_UserNo
         AND ((I_AREACODE IS NULL) or(AREA_CODE = I_AREACODE ))
         AND OPEN_TIME >= v_StartTime
         AND OPEN_TIME <= v_EndTime
         AND PARTCOL_USERNO = substr(i_UserNo, -3, 2);

  o_Ret := 0;
  RETURN;
  END IF;

  -- 判断用户号码是否为空
  /*IF i_UserNo IS NULL THEN
    -- 不附带条件查询所有订购信息
    OPEN o_AccountInfoRef FOR
      SELECT USER_NO,
             LEVEL_ID,
             STATUS,
             OPEN_TIME,
             AREA_CODE,
             PARTCOL_USERNO,
             ADDITIONAL_INFO_1,
             ADDITIONAL_INFO_2,
             ADDITIONAL_INFO_3,
             ATTRIBUTVE_POINT,
             DATASITE_CODE,
             FEE_TYPE
        FROM (SELECT USER_NO,
                     LEVEL_ID,
                     STATUS,
                     OPEN_TIME,
                     AREA_CODE,
                     PARTCOL_USERNO,
                     ADDITIONAL_INFO_1,
                     ADDITIONAL_INFO_2,
                     ADDITIONAL_INFO_3,
                     ATTRIBUTVE_POINT,
                     DATASITE_CODE,
                     FEE_TYPE,
                     ROWNUM ROWNO
                FROM T_BSS_ACCOUNTINFO
               WHERE OPEN_TIME >= v_StartTime
                 AND OPEN_TIME <= v_EndTime) a
       WHERE a.ROWNO >= nvl(i_StartNum, 0)
         AND a.ROWNO <= nvl(i_EndNum, 15);

    -- 查询数据库中符合条件的用户数量
    SELECT COUNT(1)
      INTO o_Count
      FROM T_BSS_ACCOUNTINFO t
     WHERE ROWNUM < 10001
       AND OPEN_TIME >= v_StartTime
       AND OPEN_TIME <= v_EndTime;

  ELSE*/
      -- 附带UserNo条件查询相应订购信息
      OPEN o_AccountInfoRef FOR
        SELECT USER_NO,
               LEVEL_ID,
               STATUS,
               OPEN_TIME,
               AREA_CODE,
               PARTCOL_USERNO,
               ADDITIONAL_INFO_1,
               ADDITIONAL_INFO_2,
               ADDITIONAL_INFO_3,
               ATTRIBUTVE_POINT,
               DATASITE_CODE,
               FILESITE_CODE,
               FEE_TYPE,
               COUNTRY_CODE
          FROM T_BSS_ACCOUNTINFO
         WHERE USER_NO = I_USERNO
           AND ((i_SubboxNo IS NULL AND SUBBOX_NO IS NULL) or (SUBBOX_NO = i_SubboxNo))
           AND ((I_AREACODE IS NULL) or(AREA_CODE = I_AREACODE ))
           AND OPEN_TIME >= v_StartTime
           AND OPEN_TIME <= v_EndTime
           AND PARTCOL_USERNO = substr(i_UserNo, -3, 2);

      SELECT COUNT(1)
        INTO o_Count
        FROM T_BSS_ACCOUNTINFO t
       WHERE USER_NO = i_UserNo
        AND ((i_SubboxNo IS NULL AND SUBBOX_NO IS NULL) or (SUBBOX_NO = i_SubboxNo))
        AND ((I_AREACODE IS NULL) or(AREA_CODE = I_AREACODE ))
         AND ROWNUM < 10001
         AND OPEN_TIME >= v_StartTime
         AND OPEN_TIME <= v_EndTime
         AND PARTCOL_USERNO = substr(i_UserNo, -3, 2);
  /*END IF;*/

  o_Ret := 0;
  RETURN;
EXCEPTION
  WHEN OTHERS THEN
    o_Ret := 1;
    IF o_AccountInfoRef%ISOPEN THEN
      CLOSE o_AccountInfoRef;
    END IF;
    OPEN o_AccountInfoRef FOR
      SELECT NULL FROM dual WHERE 1 = 0;
    P_COMM_ADDEXCEPTION('P_BSS_QUERYACCOUNT', SQLERRM, SQLCODE, 'i_UserNo=' || i_UserNo);
END P_BSS_QUERYACCOUNT;
/