Oracle带参数Cursor：无需传参如何查询全部员工数据？

方法1：给参数设置默认值并调整WHERE条件 #

给游标参数设置默认值NULL，同时修改WHERE子句逻辑，当参数为NULL时自动匹配所有记录。这种方式既保留了传入部门ID查询指定部门的功能，也支持不传入参数（或传NULL）查询全部员工。

示例代码：

DECLARE
   -- 为参数设置默认值NULL
   cursor emp_cursor(v_dept_id number DEFAULT NULL) IS
    SELECT * FROM employees
    -- 参数为NULL时条件恒成立，返回全部数据
    WHERE department_id = v_dept_id OR v_dept_id IS NULL;
    
BEGIN 
    -- 调用方式1：传入部门ID，查询指定部门
    FOR emp_record IN emp_cursor(60) LOOP
        dbms_output.put_line('指定部门员工 id = ' || emp_record.employee_id);
    END LOOP;

    -- 调用方式2：不传入参数，查询全部员工
    dbms_output.put_line('------------------------');
    FOR emp_record IN emp_cursor() LOOP
        dbms_output.put_line('全部员工 id = ' || emp_record.employee_id);
    END LOOP;
END;
/

方法2：使用游标重载 #

定义两个同名游标，一个带参数用于查询指定部门，一个不带参数用于查询全部员工。PL/SQL会根据调用时的参数情况自动匹配对应的游标。

示例代码：

DECLARE
   -- 不带参数的游标：查询全部员工
   cursor emp_cursor IS
    SELECT * FROM employees;

   -- 带参数的游标：查询指定部门员工
   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;

    -- 调用不带参数的游标
    dbms_output.put_line('------------------------');
    FOR emp_record IN emp_cursor LOOP
        dbms_output.put_line('全部员工 id = ' || emp_record.employee_id);
    END LOOP;
END;
/

方法3：使用动态SQL游标 #

通过动态拼接SQL语句，根据是否传入参数决定是否添加部门过滤条件，适合更复杂的动态查询场景。

示例代码：

DECLARE
   v_dept_id number; -- 可赋值为具体部门ID，或保持NULL以查询全部
   v_sql varchar2(1000);
   type emp_refcursor is ref cursor;
   emp_cursor emp_refcursor;
   emp_record employees%rowtype;
BEGIN 
    -- 基础SQL语句
    v_sql := 'SELECT * FROM employees';
    -- 若传入部门ID，则拼接过滤条件
    IF v_dept_id IS NOT NULL THEN
        v_sql := v_sql || ' WHERE department_id = :dept_id';
    END IF;

    -- 根据参数情况打开游标
    IF v_dept_id IS NOT NULL THEN
        OPEN emp_cursor FOR v_sql USING v_dept_id;
    ELSE
        OPEN emp_cursor FOR v_sql;
    END IF;

    -- 遍历并输出结果
    LOOP
        FETCH emp_cursor INTO emp_record;
        EXIT WHEN emp_cursor%NOTFOUND;
        dbms_output.put_line('员工 id = ' || emp_record.employee_id);
    END LOOP;
    CLOSE emp_cursor;
END;
/

内容的提问来源于stack exchange，提问作者Moustafa Youssef