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;