这个存储过程需要变为动态条件的
PROCEDURE GET_RPT_LIST
(
I_BRANCH_CD IN VARCHAR2
, I_FROM_DT IN VARCHAR2
, I_TO_DT IN VARCHAR2
, I_REPORT_NM IN VARCHAR2
, I_CLIENT_CD IN VARCHAR2
, I_VALID_FLG IN NUMBER
, O_RPT_LIST_CUR OUT REF_RPT_LIST
, O_STATUS OUT NUMBER
) IS --WK_RPT_INPUT_PARM T_RPT_INPUT_PARM; /* 入力項目(構造体) */
-- WK_PPS_PARAM T_PPS_PARM; /* 目的別期間(構造体) */
-- WK_STATUS NUMBER; /* プロシージャ戻り値 */BEGIN
OPEN O_RPT_LIST_CUR FOR
SELECT B.REPORT_NM AS O_REPORT_NM,
A.REPORT_CD AS O_REPORT_CD,
A.BRANCH_CD AS O_RANCH_CD,
A.CLIENT_CD AS O_LIENT_CD,
C.CLNT_NM AS O_CLNT_NM,
A.REPORT_CREATE_DT AS O_REPORT_CREATE_DT,
A.VALID_FLG AS O_VALID_FLG,
A.UPDATE_DT AS O_UPDATE_DT,
A.Change_Reason AS O_CHANGE_REASON
FROM RPT_LIST_MAIN A, RPT_INFO_MST B, BV_USER_PROFILE C
WHERE A.REPORT_CD = B.REPORT_CD
AND A.CLIENT_CD = C.CLIENT_CD
AND (A.REPORT_CD = I_REPORT_NM OR
(I_REPORT_NM IS NULL AND
A.REPORT_CD IN (G_REPORT_CD_001,
G_REPORT_CD_002,
G_REPORT_CD_003,
G_REPORT_CD_004,
G_REPORT_CD_005,
G_REPORT_CD_006,
G_REPORT_OTHER)))
AND (I_BRANCH_CD IS NOT NULL AND A.BRANCH_CD = I_BRANCH_CD)
AND (I_CLIENT_CD IS NOT NULL AND A.CLIENT_CD = I_CLIENT_CD)
AND A.REPORT_DT BETWEEN I_FROM_DT AND I_TO_DT
AND A.VALID_FLG = I_VALID_FLG ORDER BY A.BRANCH_CD, A.CLIENT_CD, A.REPORT_CD, A.REPORT_CREATE_DT;
O_STATUS := G_PROC_SUCCESS ;
RETURN;
EXCEPTION
WHEN OTHERS THEN
O_STATUS := G_PROC_ERROR ;
RETURN ;
END GET_RPT_LIST;
------------------------------------------------------------------------------------------------------------------以下两个条件需要动态拼接
AND (I_BRANCH_CD IS NOT NULL AND A.BRANCH_CD = I_BRANCH_CD)
AND (I_CLIENT_CD IS NOT NULL AND A.CLIENT_CD = I_CLIENT_CD)
PROCEDURE GET_RPT_LIST
(
I_BRANCH_CD IN VARCHAR2
, I_FROM_DT IN VARCHAR2
, I_TO_DT IN VARCHAR2
, I_REPORT_NM IN VARCHAR2
, I_CLIENT_CD IN VARCHAR2
, I_VALID_FLG IN NUMBER
, O_RPT_LIST_CUR OUT REF_RPT_LIST
, O_STATUS OUT NUMBER
) IS --WK_RPT_INPUT_PARM T_RPT_INPUT_PARM; /* 入力項目(構造体) */
-- WK_PPS_PARAM T_PPS_PARM; /* 目的別期間(構造体) */
-- WK_STATUS NUMBER; /* プロシージャ戻り値 */BEGIN
OPEN O_RPT_LIST_CUR FOR
SELECT B.REPORT_NM AS O_REPORT_NM,
A.REPORT_CD AS O_REPORT_CD,
A.BRANCH_CD AS O_RANCH_CD,
A.CLIENT_CD AS O_LIENT_CD,
C.CLNT_NM AS O_CLNT_NM,
A.REPORT_CREATE_DT AS O_REPORT_CREATE_DT,
A.VALID_FLG AS O_VALID_FLG,
A.UPDATE_DT AS O_UPDATE_DT,
A.Change_Reason AS O_CHANGE_REASON
FROM RPT_LIST_MAIN A, RPT_INFO_MST B, BV_USER_PROFILE C
WHERE A.REPORT_CD = B.REPORT_CD
AND A.CLIENT_CD = C.CLIENT_CD
AND (A.REPORT_CD = I_REPORT_NM OR
(I_REPORT_NM IS NULL AND
A.REPORT_CD IN (G_REPORT_CD_001,
G_REPORT_CD_002,
G_REPORT_CD_003,
G_REPORT_CD_004,
G_REPORT_CD_005,
G_REPORT_CD_006,
G_REPORT_OTHER)))
AND (I_BRANCH_CD IS NOT NULL AND A.BRANCH_CD = I_BRANCH_CD)
AND (I_CLIENT_CD IS NOT NULL AND A.CLIENT_CD = I_CLIENT_CD)
AND A.REPORT_DT BETWEEN I_FROM_DT AND I_TO_DT
AND A.VALID_FLG = I_VALID_FLG ORDER BY A.BRANCH_CD, A.CLIENT_CD, A.REPORT_CD, A.REPORT_CREATE_DT;
O_STATUS := G_PROC_SUCCESS ;
RETURN;
EXCEPTION
WHEN OTHERS THEN
O_STATUS := G_PROC_ERROR ;
RETURN ;
END GET_RPT_LIST;
------------------------------------------------------------------------------------------------------------------以下两个条件需要动态拼接
AND (I_BRANCH_CD IS NOT NULL AND A.BRANCH_CD = I_BRANCH_CD)
AND (I_CLIENT_CD IS NOT NULL AND A.CLIENT_CD = I_CLIENT_CD)
解决方案 »
- 系统警告说REDO LOG Writer经常时间长 经常200多ms
- 为什么加上“statisticst=none”就可以解决问题?
- Oracle存储过程,在一个for循环里面,要catch到异常,然后continue或者跳出循环怎么做
- 2008年最新的中国行政区划代码?
- 关于几个sql优化的问题
- 各位大虾,请问怎么给ORACLE传递数组呢???
- =====Oracle序列问题。=====
- 能否同时安装oracle7.3 和oracle9客户端,急!!!!!
- 如何配置才能让oracle支持中文按拼音排序?(8.1.7)
- PL/SQL的开发优势在什么地方?
- plsql中,关于两个时间字段相减的写法
- Oracle 同步数据判断语句问题
assql := 'select * from x ' ;
sql : = sql + ' where col = 1000';open cur for sql ;