I've been trying to query employees using the cursor with the parameter however I want to retrieve all the data without passing a value into the parameter, any ideas?

    DECLARE

   cursor emp_cursor(v_dept_id number) IS
    SELECT * FROM employees
    WHERE department_id = v_dept_id;
    
    
BEGIN 
    FOR emp_record IN emp_cursor(60) LOOP
        dbms_output.put_line(' id = ' || emp_record.employee_id);
    END LOOP;
END;

CodePudding user response：

If you use -1 to represent wanting all values then you can use:

DECLARE
  cursor emp_cursor(v_dept_id number) IS
    SELECT * FROM employees
    WHERE department_id = v_dept_id
    OR    v_dept_id = -1;
BEGIN 
  FOR emp_record IN emp_cursor(-1) LOOP
    dbms_output.put_line(' id = ' || emp_record.employee_id);
  END LOOP;
END;
/

Then, for the sample data:

CREATE TABLE employees (employee_id, department_id) AS
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 2, 1 FROM DUAL UNION ALL
SELECT 3, 1 FROM DUAL UNION ALL
SELECT 4, 2 FROM DUAL UNION ALL
SELECT 5, 2 FROM DUAL UNION ALL
SELECT 6, 2 FROM DUAL;

Outputs:

 id = 1
 id = 2
 id = 3
 id = 4
 id = 5
 id = 6

fiddle

CodePudding user response：

Your code is ok - just change the where clause like below. It covers all options of passing v_dept_id - if it is Null or -1 you'll get all the records - otherways just for department passed to the cursor:

SET SERVEROUTPUT ON
DECLARE
    CURSOR emp_cursor(v_dept_id NUMBER) IS
    SELECT * FROM emp
--  below is the where clause handling all of the options
    WHERE DEPTNO = CASE Nvl(v_dept_id, -1) WHEN -1 THEN DEPTNO ELSE v_dept_id END;
BEGIN 
    FOR emp_record IN emp_cursor(10) LOOP
        dbms_output.put_line(' id = ' || emp_record.empno);
    END LOOP;
END;

Results for passed values:

Note: Old emp table here - adjust it to yours