The stored procedure need to dynamic condition
PROCEDURE GET_RPT_LIST
(
I_BRANCH_CD VARCHAR2 IN
, 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;/* project into force (construct) */
- WK_PPS_PARAM T_PPS_PARM; Purpose/* don't period (construct) */
- WK_STATUS NUMBER;/* プ ロ シ ー ジ ャ 戻 り numerical */

The BEGIN
The OPEN O_RPT_LIST_CUR FOR
The SELECT B.R EPORT_NM AS O_REPORT_NM,
A.R EPORT_CD AS O_REPORT_CD,
A. RANCH_CD AS O_RANCH_CD,
A.C LIENT_CD AS O_LIENT_CD,
Arthur c. LNT_NM AS O_CLNT_NM,
A.R EPORT_CREATE_DT AS O_REPORT_CREATE_DT,
Dr. ALID_FLG AS O_VALID_FLG,
A.U PDATE_DT AS O_UPDATE_DT,
A.C hange_Reason AS O_CHANGE_REASON
A, the FROM RPT_LIST_MAIN RPT_INFO_MST B, BV_USER_PROFILE C
WHERE A.R EPORT_CD=B.R EPORT_CD
AND A.C LIENT_CD=Arthur c. LIENT_CD
AND (A.R EPORT_CD=I_REPORT_NM OR
(I_REPORT_NM IS NULL AND
A.R EPORT_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. RANCH_CD=I_BRANCH_CD)
AND (I_CLIENT_CD IS NOT NULL AND A.C LIENT_CD=I_CLIENT_CD)
AND A.R EPORT_DT BETWEEN I_FROM_DT AND I_TO_DT
AND Dr. ALID_FLG=I_VALID_FLG

The ORDER BY a. RANCH_CD, A.C LIENT_CD, A.R EPORT_CD, A.R EPORT_CREATE_DT;
O_STATUS:=G_PROC_SUCCESS;
The RETURN;
The EXCEPTION
The WHEN OTHERS THEN
O_STATUS:=G_PROC_ERROR;
The RETURN;
END GET_RPT_LIST;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

The following two conditions requires dynamic splicing
AND (I_BRANCH_CD IS NOT NULL AND a. RANCH_CD=I_BRANCH_CD)
AND (I_CLIENT_CD IS NOT NULL AND A.C LIENT_CD=I_CLIENT_CD)

CodePudding user response:

Create procedure sp_xx
As

SQL:='select * from x';
SQL: SQL +='where col=1000;

The open cur for SQL;

CodePudding user response:

Dynamic SQL,,,,