CODEWITHSUNDEEP
oracle-databaseplsql
xpetta
xpetta

Reputation: 718

Adding conditionals to PL/SQL cursors

In the below PL/SQL program I have 2 cursors that are almost similar in functionality. I'm not sure, how I can retain the same functionality just by having one cursor and get rid of the get_all_employes cursor which doesn't have the where clause.

Thanks in advance for your time and help!

CREATE OR REPLACE PROCEDURE EMP_DETAILS(dept_id VARCHAR2) IS

  CURSOR get_all_employes IS
    SELECT EMP_ID, EMP_NAME, EMP_SAL, EMP_DEPT FROM EMP;

  CURSOR get_employee_info IS
    SELECT EMP_ID, EMP_NAME, EMP_SAL, EMP_DEPT FROM EMP WHERE EMP_DEPT = dept_id;

BEGIN
  IF dept_id IS NULL THEN
     FOR X IN get_all_employes;
       DBMS_OUTPUT.PUT_LINE(X.EMP_ID || ' ' || X.EMP_NAME || ' ' || X.EMP_SAL || X.EMP_DEPT);
     END LOOP;
  ELSE
     FOR Y IN get_all_employes;
       DBMS_OUTPUT.PUT_LINE(Y.EMP_ID || ' ' || Y.EMP_NAME || ' ' || Y.EMP_SAL || Y.EMP_DEPT);
     END LOOP;
  END IF;
END EMP_DETAILS;
/

Upvotes: 1

Views: 99

Answers (2)

MT0
MT0

Reputation: 167972

Use one cursor and include WHERE EMP_DEPT = dept_id OR dept_id IS NULL:

CREATE PROCEDURE EMP_DETAILS(
  dept_id IN EMP.EMP_DEPT%TYPE
)
IS
  CURSOR get_employee_info IS
    SELECT EMP_ID,
           EMP_NAME,
           EMP_SAL,
           EMP_DEPT
    FROM   EMP
    WHERE  EMP_DEPT = dept_id
    OR     dept_id IS NULL;
BEGIN
  FOR Y IN get_employee_info LOOP
    DBMS_OUTPUT.PUT_LINE(Y.EMP_ID || ' ' || Y.EMP_NAME || ' ' || Y.EMP_SAL || Y.EMP_DEPT);
  END LOOP;
END EMP_DETAILS;
/

So, for the test data:

CREATE TABLE emp ( EMP_ID, EMP_NAME, EMP_SAL, EMP_DEPT ) AS
SELECT 1, 'a', 100, 1 FROM DUAL UNION ALL
SELECT 2, 'b', 200, 1 FROM DUAL UNION ALL
SELECT 3, 'c', 300, 2 FROM DUAL;

Then:

BEGIN
  emp_details( NULL );
END;
/

Would output all the rows:

1 a 1001
2 b 2001
3 c 3002

and:

BEGIN
  emp_details( 1 );
END;
/

would output only the rows for department 1:

1 a 1001
2 b 2001

db<>fiddle here

Upvotes: 2

OldProgrammer
OldProgrammer

Reputation: 12169

Try something like this (fyi your example code does not compile)

create or replace PROCEDURE EMP_DETAILS(dept_id VARCHAR2) IS
TYPE EmpCurTyp IS REF CURSOR; 
emp_cv EmpCurTyp;
empRec EMP%ROWTYPE;
BEGIN
  IF dept_id IS NULL THEN
    open emp_cv for SELECT EMP_ID, EMP_NAME, EMP_SAL, EMP_DEPT FROM EMP;
  ELSE
   open emp_cv for SELECT EMP_ID, EMP_NAME, EMP_SAL, EMP_DEPT FROM EMP WHERE EMP_DEPT = dept_id;
  END IF;

 LOOP
    FETCH emp_cv INTO empRec;
    IF emp_cv%NOTFOUND
    THEN
      EXIT;
    END IF;
     DBMS_OUTPUT.PUT_LINE(empREc.EMP_ID || ' ' || empREc.EMP_NAME || ' ' || empREc.EMP_SAL || empREc.EMP_DEPT);
  END LOOP;

  CLOSE emp_cv;
END EMP_DETAILS;

Upvotes: 2

Related Questions