Oracle 存储过程案例01

Header 案例：

create or replace PACKAGE MSRS_UTIL IS
  PKG_NAME VARCHAR2(30) := 'MSRS_UTIL';
  ---------------------------------------------------------------------------------------------
  FUNCTION F_WELCOME_MESSAGE(PIV_APP_CODE IN VARCHAR2) RETURN VARCHAR2;
  
  ---------------------------------------------------------------------------------------------
  
    PROCEDURE P_UNPACKLIST(PIV_INPUTSTR   IN VARCHAR2,
                         PIV_DELIMITER  IN VARCHAR2,
                         POV_UNPACKLIST OUT PKG_SYSTEM_UTILS.UNPACKLST_TYPE,
                         PON_INDEX      OUT NUMBER,
                         PON_ERR_TYPE   OUT NUMBER,
                         POV_ERR_TEXT   OUT VARCHAR2,
                         POV_RETURNSTR  OUT VARCHAR2);
                         
  ---------------------------------------------------------------------------------------------
  
  FUNCTION F_GET_TOKEN(V_LIST  IN VARCHAR2,
                       N_INDEX IN NUMBER,
                       V_DELIM IN VARCHAR2 DEFAULT ',') RETURN VARCHAR2;
                       
  ---------------------------------------------------------------------------------------------
    
  PROCEDURE P_GET_MSYS_PARM(PIV_RMS_WS      IN VARCHAR2,
                                   PON_ERR_TYPE      OUT NUMBER,
                                   POV_ERR_TEXT      OUT VARCHAR2,
                                   POV_RETURNSTR     OUT VARCHAR2,
                                   PO_CUR            OUT PKG_SYSTEM_UTILS.SYS_REFCURSOR);
                                   
  
                       
                                   
END MSRS_UTIL;

body案例

create or replace PACKAGE BODY MSRS_UTIL IS
  --------------------------------------------------------------------------------------------

  FUNCTION F_WELCOME_MESSAGE(PIV_APP_CODE IN VARCHAR2) RETURN VARCHAR2 IS
    v_welcome_msg varchar2(1024);
    n_count       number;
  begin
    select count(*)
      into n_count
      from msys_parm mp
     where mp.segment = 'WELCOME_MSG'
       and mp.acad_year = 0
       and mp.semester = '0'
       and mp.code = PIV_APP_CODE;
    if n_count > 0 then
      select mp.attrib01
        into v_welcome_msg
        from msys_parm mp
       where mp.segment = 'WELCOME_MSG'
         and mp.acad_year = 0
         and mp.semester = '0'
         and mp.code = PIV_APP_CODE;
    else
      v_welcome_msg := null;
    end if;
    return v_welcome_msg;
  end;
  
  
  -------------------------------------------------------------------------------
  
  PROCEDURE P_UNPACKLIST(piv_inputstr   IN VARCHAR2,
                         piv_delimiter  IN VARCHAR2,
                         pov_unpacklist OUT Pkg_System_Utils.unpacklst_type,
                         pon_index      OUT NUMBER,
                         pon_err_type   OUT NUMBER,
                         pov_err_text   OUT VARCHAR2,
                         pov_returnstr  OUT VARCHAR2) IS
    n_err_type   NUMBER := 0;
    v_err_text   VARCHAR2(32767) := NULL;
    v_returnstr  VARCHAR2(32767) := NULL;
    v_unpacklist Pkg_System_Utils.unpacklst_type;
    v_index      NUMBER := 0;
    v_inputstr   VARCHAR2(32767);
    v_pos        NUMBER := -1;
  BEGIN
    v_inputstr := piv_inputstr;
    -- WHILE v_inputstr IS NOT NULL LOOP
    WHILE v_pos <> 0 LOOP
      v_pos := INSTR(v_inputstr, piv_delimiter);
      dbms_output.put_line(v_pos);
      IF v_pos = 0 THEN
        v_unpacklist(v_index) := v_inputstr || '%';
        EXIT;
      ELSE
        v_unpacklist(v_index) := SUBSTR(v_inputstr, 1, v_pos - 1) || '%';
        v_index := v_index + 1;
        v_inputstr := SUBSTR(v_inputstr,
                             v_pos + NVL(LENGTH(piv_delimiter), 0));
      END IF;
    END LOOP;
    -- assign OUT parameters
    pov_unpacklist := v_unpacklist;
    pon_index      := v_index;
    pon_err_type   := n_err_type;
    pov_err_text   := v_err_text;
    pov_returnstr  := v_returnstr;
  EXCEPTION
    WHEN OTHERS THEN
      -- assign OUT parameters
      pov_unpacklist := v_unpacklist;
      pon_err_type   := 9;
      pov_err_text   := '[MSRS_UTIL.p_unpacklist] ' || SQLERRM;
      pov_returnstr  := v_returnstr;
  END p_unpacklist;
  
   
   -------------------------------------------------------------------------------
    
   FUNCTION F_GET_TOKEN(V_LIST  IN VARCHAR2,
                       N_INDEX IN NUMBER,
                       V_DELIM IN VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS
    n_start_pos number;
    n_end_pos   number;
    v_str       varchar2(4000);
  begin
  
    if N_INDEX = 1 then
      n_start_pos := 1;
    
    elsif n_index <= 0 then
      return null;
    
    else
      n_start_pos := instr(V_LIST, V_DELIM, 1, N_INDEX - 1);
      if n_start_pos = 0 then
        return null;
      else
        n_start_pos := n_start_pos + length(V_DELIM);
      end if;
    end if;
  
    n_end_pos := instr(V_LIST, V_DELIM, n_start_pos, 1);
  
    if n_end_pos = 0 then
      v_str := substr(V_LIST, n_start_pos);
      --return trim(replace(substr(V_LIST, n_start_pos), chr(10), ''));
    else
      v_str := substr(V_LIST, n_start_pos, n_end_pos - n_start_pos);
      --return trim(replace(substr(V_LIST, n_start_pos, n_end_pos - n_start_pos), chr(10), ''));
    end if;
  
    return trim(replace(replace(v_str, chr(10), ''), chr(9), ''));
  END F_GET_TOKEN;
  
  -------------------------------------------------------------------------------
   PROCEDURE P_GET_MSYS_PARM(       PIV_RMS_WS      IN VARCHAR2,
                                   PON_ERR_TYPE      OUT NUMBER,
                                   POV_ERR_TEXT      OUT VARCHAR2,
                                   POV_RETURNSTR     OUT VARCHAR2,
                                   PO_CUR            OUT PKG_SYSTEM_UTILS.SYS_REFCURSOR) IS
  
    N_ERR_TYPE   NUMBER := 0;
    V_ERR_TEXT   VARCHAR2(32767) := NULL;
    V_RETURNSTR  VARCHAR2(32767) := NULL;
    RESULT_CUR   PKG_SYSTEM_UTILS.SYS_REFCURSOR;
    V_UNPACKLIST PKG_SYSTEM_UTILS.UNPACKLST_TYPE;
    PROCEDURE P_CLOSE_CUR IS
    BEGIN
      IF RESULT_CUR%ISOPEN THEN
        CLOSE RESULT_CUR;
      END IF;
    END;
  BEGIN
  
    -- get record in P_GET_MSYS_PARM
  OPEN result_cur FOR
       select distinct ATTRIB01,ATTRIB02,ATTRIB04,ATTRIB05 from msys_parm t 
       where t.segment =PIV_RMS_WS
        and t.code ='URL';
  
    IF result_cur%notfound THEN
      n_err_type := 1;
      v_err_text := '0002|[MSRS_UTIL.P_GET_MSYS_PARM]'; --No data found.
      RAISE pkg_system_utils.user_defined_exception;
    END IF;
    PON_ERR_TYPE  := n_err_type;
    POV_ERR_TEXT  := v_err_text;
    POV_RETURNSTR := v_returnstr;
    po_cur        := result_cur;
  EXCEPTION
    WHEN pkg_system_utils.user_defined_exception THEN
      p_close_cur;
      PON_ERR_TYPE  := n_err_type;
      POV_ERR_TEXT  := 'errors.' || PKG_NAME || '.' || v_err_text;
      POV_RETURNSTR := v_returnstr;
    
    WHEN OTHERS THEN
      p_close_cur;
      PON_ERR_TYPE  := 9;
      POV_ERR_TEXT  := '[MSRS_UTIL.P_GET_MSYS_PARM]' || SQLERRM;
      POV_RETURNSTR := v_returnstr;
      po_cur        := result_cur;
    
  END;
                                   
                                   
END MSRS_UTIL;