這個存盤程序需要變為動態條件的
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)
uj5u.com熱心網友回復：
create procedure sp_xxas
sql := 'select * from x ' ;
sql : = sql + ' where col = 1000';
open cur for sql ;
uj5u.com熱心網友回復：
動態SQL。。。。轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/115038.html
標籤：開發