oracle PL/SQL FOR 循环隐式游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15854749/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PL/SQL FOR LOOP IMPLICIT CURSOR
提问by qasim.hasnain
There are 2 tables EMPLOYEESand DEPARTMENTSwith department_idas primary key for DEPARTMENTSand foreign key on EMPLOYEES.
有2个表EMPLOYEES，并DEPARTMENTS与department_id作为主键DEPARTMENTS和外键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 EXPLICITcursors.
I thought why not try with FOR loop and a IMPLICITcursors.
我想打印属于特定部门的所有员工姓名。我知道它可以通过 JOINS 或EXPLICIT游标轻松实现。我想为什么不尝试使用 FOR 循环和IMPLICIT游标。
My question is if it is syntactically correct to write INTOlike this. If so why is not assigning any values?
我的问题是这样写在语法上是否正确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 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;
回答by David Aldridge
You don't use INTO with an implicit cursor:
您不要将 INTO 与隐式游标一起使用：
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;
/
回答by Robert Harvey
Check this page: http://www.techonthenet.com/oracle/loops/cursor_for.php.
检查此页面：http: //www.techonthenet.com/oracle/loops/cursor_for.php。
I don't think what you're trying to do is valid. Break this up into two steps: the FOR loop and the INTO. You can still have a SELECT in the FOR IN, it just can't be an INTO.
我不认为你试图做的事情是有效的。将其分为两步：FOR 循环和 INTO。你仍然可以在 FOR IN 中有一个 SELECT，它不能是一个 INTO。
