How put a condition in the where clause in the procedure?-CodePudding

I want to put a condition in the where clause according to the null condition of the parameter in the procedure. But the procedure I made has some problem. There's problem is in IF clause.

How I can put a condition in the where clause in the procedure?

CREATE OR REPLACE PROCEDURE SP_PROCEDURE(START_DATE, END_DATE)
    IS
      START_DATE DATE;
      END_DATE DATE;
    BEGIN
      START_DATE := TO_DATE(START_DATE, 'YYYYMMDD');
      END_DATE := TO_DATE(END_DATE, 'YYYYMMDD');
    
      INSERT INTO USER
        (
          USR_KEY,
          USR_NAME
        )
      SELECT
        USR_KEY,
        USR_NAME
      FROM
        USER
      WHERE
        1 = 1
        IF START_DATE THEN --I think there's problem here..
          AND USR_CRT_DATE >= START_DATE
        END IF;
    
    COMMIT;
    
      EXCEPTION
      
        WHEN OTHERS THEN
            ROLLBACK;
    END;

CodePudding user response：

If I'm reading your requirements correctly, this should do the trick

CREATE OR REPLACE PROCEDURE SP_PROCEDURE(START_DATE, END_DATE)
    IS
      START_DATE DATE;
      END_DATE DATE;
    BEGIN
      START_DATE := TO_DATE(START_DATE, 'YYYYMMDD');
      END_DATE := TO_DATE(END_DATE, 'YYYYMMDD');
    
      INSERT INTO USER
        (
          USR_KEY,
          USR_NAME
        )
      SELECT
        USR_KEY,
        USR_NAME
      FROM
        USER
      WHERE (USR_CRT_DATE >= START_DATE
             or START_DATE is null );
    
    COMMIT;
    
    END;

You don't need that exception handler. By default if a PL/SQL unit fails, it will rollback changes to the commencement point of the procedure.