Oracle 存储过程案例02

坚行天下

已于 2024-03-22 09:11:40 修改

阅读量454

点赞数 11

文章标签： oracle 数据库

于 2024-03-22 09:11:29 首次发布

本文链接：https://blog.csdn.net/m0_38061183/article/details/136928547

版权

文章详细描述了一个OraclePL/SQL包PKG_MACS01150R，包含包体中的多个过程和函数，如F_PARM_DISPLAY用于参数显示，P_GET_DEPT_DESC获取部门描述，以及P_GET_ACCOUNT_REPORT用于生成用户账户报告。包中涉及的数据处理涉及SQL查询和错误处理机制。

摘要由CSDN通过智能技术生成

Header案例：

create or replace package PKG_MACS01150R is
PKG_NAME varchar(30) := 'PKG_MACS01150R';

v_user_code1 varchar2(32767);
v_user_code2 varchar2(32767);
v_user_code3 varchar2(32767);
v_user_code4 varchar2(32767);
v_user_code5 varchar2(32767);
v_user_code6 varchar2(32767);
v_user_code7 varchar2(32767);
v_user_code8 varchar2(32767);
v_user_code9 varchar2(32767);
v_user_code10 varchar2(32767);

PROCEDURE p_unpack_code(piv_usercode_list IN VARCHAR2);

FUNCTION F_PARM_DISPLAY(PIV_SEGMENT IN VARCHAR2, PIV_CODE IN VARCHAR2)
return varchar2;

PROCEDURE P_GET_DEPT_DESC(PIV_CAMPUS IN VARCHAR2,
PIV_DEPT IN VARCHAR2,
PON_ERR_TYPE OUT NUMBER,
POV_ERR_TEXT OUT VARCHAR2,
POV_RETURNSTR OUT VARCHAR2,
PO_CUR OUT PKG_SYSTEM_UTILS.SYS_REFCURSOR);

PROCEDURE P_GET_ACCOUNT_REPORT(PIV_APP_CODE IN VARCHAR2,
PIV_USER_CODE IN VARCHAR2,
PIV_USER_NAME IN VARCHAR2,
PIV_ROLE_CODE IN VARCHAR2,
PIV_DESCIPLINE IN VARCHAR2,
PIV_PROGRAME_BORDE IN VARCHAR2,
PIV_CAMPUS IN VARCHAR2,
PIV_DEPT IN VARCHAR2,
PIV_ACTIVE IN VARCHAR2,
-- PIV_LASTUP_FROM_DATE IN DATE,
-- PIV_LASTUP_TO_DATE IN DATE,
PON_ERR_TYPE OUT NUMBER,
POV_ERR_TEXT OUT VARCHAR2,
POV_RETURNSTR OUT VARCHAR2,
PO_CUR OUT PKG_SYSTEM_UTILS.SYS_REFCURSOR);

end PKG_MACS01150R;

Body案例：

create or replace package body PKG_MACS01150R is

FUNCTION F_PARM_DISPLAY(PIV_SEGMENT IN VARCHAR2, PIV_CODE IN VARCHAR2)
RETURN VARCHAR2 IS

V_PARAM_DISPLAY VARCHAR2(100);
v_max_year NUMBER := 0;
BEGIN

SELECT ATTRIB01
INTO V_PARAM_DISPLAY
FROM MSYS_PARM
WHERE SEGMENT = PIV_SEGMENT
AND ACAD_YEAR = 0
AND SEMESTER = 0
AND CODE = PIV_CODE;
RETURN V_PARAM_DISPLAY;
END F_PARM_DISPLAY;

PROCEDURE P_GET_DEPT_DESC(PIV_CAMPUS IN VARCHAR2,
PIV_DEPT 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;

PROCEDURE P_CLOSE_CUR IS
BEGIN
IF RESULT_CUR%ISOPEN THEN
CLOSE RESULT_CUR;
END IF;
END;
BEGIN
OPEN RESULT_CUR FOR

union

select distinct short_desc
from (SELECT msd.campus,
msd.dept dept_code,
msd.acad_year,
msd.semester,
msd.short_desc short_desc,
RANK() OVER(PARTITION by msd.campus, msd.dept order by msd.acad_year desc, msd.semester desc) dis_rank
FROM msys_campus_dept mcdp,
msys_dept msd,
(select t.campus
from msys_campus t
where PIV_CAMPUS like
'%' || '@'|| nvl(t.campus || '@', ' ') || '%') mcp
WHERE mcdp.dept = msd.dept
and mcdp.campus = msd.campus
AND mcdp.CAMPUS = mcp.CAMPUS(+)
and PIV_DEPT like '%' ||'@'|| nvl(msd.dept || '@', ' ') || '%') t
where dis_rank = 1
and dept_code not in
(select distinct dept_code
from (SELECT msd.campus,
msd.dept dept_code,
msd.acad_year,
msd.semester,
msd.short_desc short_desc,
RANK() OVER(PARTITION by msd.campus, msd.dept order by msd.acad_year desc, msd.semester desc) dis_rank
FROM msys_campus_dept mcdp,
msys_dept msd,
(select t.campus
from msys_campus t
where PIV_CAMPUS like
'%' ||'@'|| nvl(t.campus || '@', ' ') || '%') mcp
WHERE mcdp.dept = msd.dept
and mcdp.campus = msd.campus
AND mcdp.CAMPUS = mcp.CAMPUS
and PIV_DEPT like
'%' || '@'||nvl(msd.dept || '@', ' ') || '%') t
where dis_rank = 1);

IF RESULT_CUR %NOTFOUND THEN
N_ERR_TYPE := 1;
V_ERR_TEXT := '0002|[PKG_LOV.P_GET_MSYS_LOV]';
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 := '[PKG_LOV.P_GET_MSYS_LOV]' || SQLERRM;
POV_RETURNSTR := V_RETURNSTR;
PO_CUR := RESULT_CUR;

END P_GET_DEPT_DESC;


PROCEDURE p_unpack_code(piv_usercode_list IN VARCHAR2) is

v_course_array pkg_system_utils.unpacklst_type;
v_course_array_index NUMBER;
n_err_type NUMBER := 0;
v_err_text VARCHAR2(32767) := NULL;
v_returnStr VARCHAR2(32767) := NULL;
BEGIN
v_user_code1 := null;
v_user_code2 := null;
v_user_code3 := null;
v_user_code4 := null;
v_user_code5 := null;
v_user_code6 := null;
v_user_code7 := null;
v_user_code8 := null;
v_user_code9 := null;
v_user_code10 := null;

MSRS_UTIL.p_unpacklist(piv_inputstr => REPLACE(piv_userCode_list,
' ',
''),
piv_delimiter => ',',
pov_unpacklist => v_course_array,
pon_index => v_course_array_index,
pon_err_type => n_err_type,
pov_err_text => v_err_text,
pov_returnstr => v_returnstr);
IF v_course_array_index >= 0 THEN
v_user_code1 := v_course_array(0);
END IF;
IF v_course_array_index >= 1 THEN
v_user_code2 := v_course_array(1);
END IF;
IF v_course_array_index >= 2 THEN
v_user_code3 := v_course_array(2);
END IF;
IF v_course_array_index >= 3 THEN
v_user_code4 := v_course_array(3);
END IF;
IF v_course_array_index >= 4 THEN
v_user_code5 := v_course_array(4);
END IF;
IF v_course_array_index >= 5 THEN
v_user_code6 := v_course_array(5);
END IF;
IF v_course_array_index >= 6 THEN
v_user_code7 := v_course_array(6);
END IF;
IF v_course_array_index >= 7 THEN
v_user_code8 := v_course_array(7);
END IF;
IF v_course_array_index >= 8 THEN
v_user_code9 := v_course_array(8);
END IF;
IF v_course_array_index >= 9 THEN
v_user_code10 := v_course_array(9);
END IF;

END p_unpack_code;


PROCEDURE P_GET_ACCOUNT_REPORT(
                               PIV_APP_CODE IN VARCHAR2,
PIV_USER_CODE IN VARCHAR2,
PIV_USER_NAME IN VARCHAR2,
PIV_ROLE_CODE IN VARCHAR2,
PIV_DESCIPLINE IN VARCHAR2,
PIV_PROGRAME_BORDE IN VARCHAR2,
PIV_CAMPUS IN VARCHAR2,
PIV_DEPT IN VARCHAR2,
                               PIV_ACTIVE     IN VARCHAR2,
-- PIV_LASTUP_FROM_DATE IN DATE,
-- PIV_LASTUP_TO_DATE IN DATE,
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;

PROCEDURE P_CLOSE_CUR IS
BEGIN
IF RESULT_CUR%ISOPEN THEN
CLOSE RESULT_CUR;
END IF;
END;
BEGIN

if (PIV_USER_CODE IS not null) then
p_unpack_code(PIV_USER_CODE);
end if;

   OPEN RESULT_CUR FOR
   SELECT MAUP.USER_CODE,
           MAUP.USER_NAME,
           MAUP.C_NAME,
           MAUR.APP_CODE,
           MAUR.ADMIN_SYSTEM_CODE,
           MAUR.STATUS,
           TO_CHAR(MAUR.VALIDITY_FROM, PKG_SYSTEM_UTILS.DATE_TIME_FORMAT) VALIDITY_FROM,
           TO_CHAR(MAUR.VALIDITY_TO, PKG_SYSTEM_UTILS.DATE_TIME_FORMAT) VALIDITY_TO,
           MAUR.ROLE_CODE,
           MAUR.USER_TYPE,
           PKG_MACS01140M_SUMMARY.F_GET_DISCIPLINE_DESC(MADA.DISCIPLINE) DISCIPLINE,
           PKG_MACS01140M_SUMMARY.F_GET_PROGRAMMEBOARD_DESC(MADA.PROG_BOARD) PROG_BOARD,
           PKG_MACS01140M_SUMMARY.F_GET_CAMPUS_DESC(MADA.CAMPUS) CAMPUS,
           PKG_MACS01140M_SUMMARY.F_GET_DEPARTMENT_DESC(MADA.DEPT,MADA.CAMPUS) DEPT,
           MAUF.FUNC_CODE,
           MAGR.GRANTABLE_ROLE_CODE,
           MAAP.APP_NAME,
           MAUS.SYS_CODE APPLICANT_GROUP,
           F_PARM_DISPLAY('AS_SYS_CODE', MAUS.SYS_CODE) APPLICANT_GROUP_DESC,
           MAUP.CREATED_BY,
           TO_CHAR(MAUP.LAST_UPDATED_DATE,
                   PKG_SYSTEM_UTILS.DATE_TIME_FORMAT) LASTUPDATEDATE,
           MADA.CREATED_BY DR_CREATED_BY,
           TO_CHAR(MADA.LAST_UPDATED_DATE,
                   PKG_SYSTEM_UTILS.DATE_TIME_FORMAT) DRLASTUPDATEDATE

       FROM MACS_USER_PROFILE MAUP,
           MACS_USER_ROLE MAUR,
           MACS_USER_ROLE_DATA_ACCESS MADA,
           MACS_USER_GRANTABLE_ROLE MAGR,
           MACS_USER_SYS MAUS,
           MACS_USER_FUNC MAUF,
           MACS_APPLICATION MAAP
   WHERE MAUP.USER_CODE = MAUR.USER_CODE(+)
           --
       AND MAUR.APP_CODE = MADA.APP_CODE(+)
       AND MAUR.ADMIN_SYSTEM_CODE = MADA.ADMIN_SYSTEM_CODE(+)
       AND MAUR.USER_CODE = MADA.USER_CODE(+)
       AND MAUR.ROLE_CODE = MADA.ROLE_CODE(+)
           --
       AND MAUR.APP_CODE = MAGR.APP_CODE(+)
       AND MAUR.ADMIN_SYSTEM_CODE = MAGR.ADMIN_SYSTEM_CODE(+)
       AND MAUR.USER_CODE = MAGR.USER_CODE(+)
       AND MAUR.ROLE_CODE = MAGR.ROLE_CODE(+)
           --
       AND MAUR.APP_CODE = MAUS.APP_CODE(+)
       AND MAUR.USER_CODE = MAUS.USER_CODE(+)
           --
       AND MAUR.APP_CODE = MAUF.APP_CODE(+)
       AND MAUR.ADMIN_SYSTEM_CODE = MAUF.ADMIN_SYSTEM_CODE(+)
       AND MAUR.USER_CODE = MAUF.USER_CODE(+)
       AND MAUR.ROLE_CODE = MAUF.ROLE_CODE(+)
           --
       AND MAUR.APP_CODE = MAAP.APP_CODE(+)

       AND MAUR.APP_CODE = PIV_APP_CODE
       AND MAUR.STATUS = PIV_ACTIVE
       AND UPPER(MAUP.USER_NAME) like '%'||UPPER(NVL(PIV_USER_NAME , MAUP.USER_NAME))||'%'
       AND MAUR.ROLE_CODE = NVL(PIV_ROLE_CODE , MAUR.ROLE_CODE)

       AND ((v_user_code1 is not null and
           MAUP.USER_CODE LIKE '%' || v_user_code1 || '%') OR
           (v_user_code2 is not null and
           MAUP.USER_CODE LIKE '%' || v_user_code2 || '%') OR
           (v_user_code3 is not null and
           MAUP.USER_CODE LIKE '%' || v_user_code3 || '%') OR
           (v_user_code4 is not null and
           MAUP.USER_CODE LIKE '%' || v_user_code4 || '%') OR
           (v_user_code5 is not null and
           MAUP.USER_CODE LIKE '%' || v_user_code5 || '%') OR
           (v_user_code6 is not null and
           MAUP.USER_CODE LIKE '%' || v_user_code6 || '%') OR
           (v_user_code7 is not null and
           MAUP.USER_CODE LIKE '%' || v_user_code7 || '%') OR
           (v_user_code8 is not null and
           MAUP.USER_CODE LIKE '%' || v_user_code8 || '%') OR
           (v_user_code9 is not null and
           MAUP.USER_CODE LIKE '%' || v_user_code9 || '%') OR
           (v_user_code10 is not null and
           MAUP.USER_CODE LIKE '%' || v_user_code10 || '%') OR PIV_USER_CODE IS NULL)


       AND ((MADA.DISCIPLINE is not null
AND DECODE (PIV_DESCIPLINE, '', MADA.DISCIPLINE, PIV_DESCIPLINE) LIKE '%'||MADA.DISCIPLINE||'%' ) or
(MADA.DISCIPLINE = '*'))

       AND ((MADA.PROG_BOARD is not null
AND DECODE (PIV_PROGRAME_BORDE, '', MADA.PROG_BOARD, PIV_PROGRAME_BORDE) LIKE '%'||MADA.PROG_BOARD||'%' ) or
(MADA.PROG_BOARD = '*'))

       AND ((MADA.CAMPUS is not null
AND DECODE (PIV_CAMPUS, '', MADA.CAMPUS, PIV_CAMPUS) LIKE '%'||MADA.CAMPUS||'%' ) or
(MADA.CAMPUS = '*'))

       AND ((MADA.DEPT is not null
AND DECODE (PIV_DEPT, '', MADA.DEPT, PIV_DEPT) LIKE '%'||MADA.DEPT||'%' ) or
(MADA.DEPT = '*'))
       ;

WHEN OTHERS THEN
P_CLOSE_CUR;
PON_ERR_TYPE := 9;
POV_ERR_TEXT := '[PKG_LOV.P_GET_MSYS_LOV]' || SQLERRM;
POV_RETURNSTR := V_RETURNSTR;
PO_CUR := RESULT_CUR;
END;

end PKG_MACS01150R;