PL / SQL循环隐式游标 [英] PL/SQL FOR LOOP IMPLICIT CURSOR

问题描述

有两个表 EMPLOYEES 和部门与 department_id 作为 DEPARTMENTS 和 EMPLOYEES 中的外键的主键。

我想打印属于特定部门的所有员工姓名。我知道可以通过JOINS或 EXPLICIT 游标轻松实现。
我想为什么不尝试使用FOR循环和一个 IMPLICIT 游标。

我的问题是如果它在语法上是正确的，像这样写 INTO 。如果是这样，为什么不分配任何值？

  DECLARE 
 emp_dept_id employees.department_id％TYPE; 
 emp_emp_id employees.employee_id％TYPE; 
 emp_last_name employees.last_name％TYPE; 
 dept_dept_id departments.department_id％TYPE; 
 dept_dept_name departments.department_name％TYPE; 
 v_count数字DEFAULT 0; 
 BEGIN 
 FOR IN IN（SELECT DISTINCT department_id，department_name 
 INTO dept_dept_id，dept_dept_name $ b $ FROM部门）
 LOOP 
 --v_COUNT：= v_COUNT + 1 ; 
 DBMS_OUTPUT.PUT_LINE（'HELLO'|| dept_dept_id ||''|| dept_dept_name）; 
 FOR j IN（SELECT employee_id，last_name 
 INTO emp_emp_id，emp_last_name 
 FROM employees）
 --WHERE department_id = dept_dept_id）
 LOOP 
 DBMS_OUTPUT.PUT_LINE （emp_emp_id ||''|| emp_last_name）; 
 v_COUNT：= v_COUNT + 1; 
 END LOOP; 
 END LOOP; 
 DBMS_OUTPUT.PUT_LINE（v_COUNT）; 
 END; 
  

解决方案

  DECLARE 
 emp_dept_id employees.department_id％TYPE; 
 emp_emp_id employees.employee_id％TYPE; 
 emp_last_name employees.last_name％TYPE; 
 v_count数字DEFAULT 0; 
 BEGIN 
 FOR IN（选择DISTINCT department_id，department_name 
 FROM departments）
 LOOP 
 --v_COUNT：= v_COUNT + 1; 
 DBMS_OUTPUT.PUT_LINE（'HELLO'|| i.department_id ||''|| i.department_name）; 
 FOR j IN（SELECT employee_id，last_name 
 INTO emp_emp_id，emp_last_name 
 FROM employees）
 --WHERE department_id = i.department_id）
 LOOP 
 DBMS_OUTPUT .PUT_LINE（emp_emp_id ||''|| emp_last_name）; 
 v_COUNT：= v_COUNT + 1; 
 END LOOP; 
 END LOOP; 
 DBMS_OUTPUT.PUT_LINE（v_COUNT）; 
 END; 
 / 
  

There are 2 tables EMPLOYEES and DEPARTMENTS with department_id as primary key for DEPARTMENTS and foreign key on EMPLOYEES.

I want to print all the employee names that belong to a particular department. I know it can be easily achieved by JOINS or EXPLICIT cursors. I thought why not try with FOR loop and a IMPLICIT cursors.

My question is if it is syntactically correct to write INTO like this. If so why is not assigning any values?

DECLARE
    emp_dept_id employees.department_id%TYPE;
    emp_emp_id employees.employee_id%TYPE;
    emp_last_name employees.last_name%TYPE;
    dept_dept_id departments.department_id%TYPE;
    dept_dept_name departments.department_name%TYPE;
    v_count number DEFAULT 0;
BEGIN
    FOR i IN (SELECT DISTINCT department_id, department_name
                INTO dept_dept_id, dept_dept_name
        FROM departments)
    LOOP
        --v_COUNT := v_COUNT + 1;
        DBMS_OUTPUT.PUT_LINE('HELLO'||dept_dept_id||' '||dept_dept_name);
        FOR j IN (SELECT employee_id, last_name
            INTO emp_emp_id, emp_last_name
            FROM employees)
            --WHERE department_id=dept_dept_id)
        LOOP
            DBMS_OUTPUT.PUT_LINE(emp_emp_id||' '||emp_last_name);
            v_COUNT := v_COUNT + 1;
        END LOOP;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(v_COUNT);
END;

解决方案

You don't use INTO with an implicit cursor:

    DECLARE
        emp_dept_id employees.department_id%TYPE;
        emp_emp_id employees.employee_id%TYPE;
        emp_last_name employees.last_name%TYPE;
        v_count number DEFAULT 0;
    BEGIN
        FOR i IN (SELECT DISTINCT department_id, department_name
            FROM departments)
        LOOP
            --v_COUNT := v_COUNT + 1;
            DBMS_OUTPUT.PUT_LINE('HELLO'||i.department_id||' '||i.department_name);
            FOR j IN (SELECT employee_id, last_name
                INTO emp_emp_id, emp_last_name
                FROM employees)
                --WHERE department_id=i.department_id)
            LOOP
                DBMS_OUTPUT.PUT_LINE(emp_emp_id||' '||emp_last_name);
                v_COUNT := v_COUNT + 1;
            END LOOP;
        END LOOP;
        DBMS_OUTPUT.PUT_LINE(v_COUNT);
    END;
    /

这篇关于PL / SQL循环隐式游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！