oracle PL/SQL 集合中循环索引变量的使用无效
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28235240/
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
Loop index variable use is invalid in PL/SQL Collections
提问by udarH3
After I'm executing this I receive the following:
执行此操作后，我收到以下信息：
PL/SQL: ORA-00942: table or view does not exist
PLS-00364: loop index variable 'EMP_REC' use is invalid
It is said that:
据说：
In a block or subprogram, user-defined records are instantiated when you enter the block or Subprogram. They cease to exist when you exit the block Subprogram."
在块或子程序中，当您输入块或子程序时，将实例化用户定义的记录。当您退出块子程序时，它们将不复存在。”
Now I think I fully understand what it is saying, I think... But along with this said, is it supposed my FOR section to work right?(why it isn't?), because as I can see, the whole things happen in the block not outside. So till the exit of the block I suppose that v_myrec to exist in the cache or buffer of the private memory allocated by the Oracle server by default because after all v_myrec is a "table" so to speak, therefore the DBMS package should be able to print my "emp_rec.v_sal..." and after the execution of my block completes successfully, THEN v_myrec ceases to exist. I'm little confused here, can anybody explain me this?
现在我想我完全理解它在说什么，我想......但是随着这句话，我的 FOR 部分是否应该正常工作？（为什么不是？），因为正如我所看到的，整个事情发生在街区而不是外面。所以直到块退出我假设 v_myrec 存在于默认情况下由 Oracle 服务器分配的私有内存的缓存或缓冲区中，因为毕竟 v_myrec 是一个“表”可以这么说，因此 DBMS 包应该能够打印我的“emp_rec.v_sal...”，在我的块执行成功完成后，那么 v_myrec 不复存在。我在这里有点困惑，有人可以解释一下吗？
If I got it all wrong please correct me. v
如果我都弄错了，请纠正我。v
DECLARE
TYPE t_rec IS RECORD
(v_sal NUMBER(8) NOT NULL := 0,
v_min_sal NUMBER(8) DEFAULT 1000,
v_hire_date employees.hire_date%TYPE,
v_rec1 employees%ROWTYPE);
v_myrec t_rec;
BEGIN
v_myrec.v_sal := v_myrec.v_min_sal + 500;
v_myrec.v_hire_date := SYSDATE;
SELECT *
INTO v_myrec.v_rec1
FROM employees
WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE(v_myrec.v_rec1.last_name||' '||v_myrec.v_sal||
' '||v_myrec.v_rec1.salary);
FOR emp_rec IN (SELECT *
FROM v_myrec)
LOOP
DBMS_OUTPUT.PUT_LINE(emp_rec.v_sal,...,...);
END LOOP;
END;
回答by Alex Poole
They are separate errors; PLS-00364: loop index variable 'EMP_REC' use is invalidis a knock-on error from your cursor being invalid when it's declared, which gets PL/SQL: ORA-00942: table or view does not exist.
它们是单独的错误；PLS-00364: loop index variable 'EMP_REC' use is invalid是您的光标在声明时无效的连锁错误，它会得到PL/SQL: ORA-00942: table or view does not exist.
v_myrecis not a table. If it was a SQL (schema-level) collection type rather than a PL/SQL collection you could do:
v_myrec不是一张桌子。如果它是 SQL（模式级）集合类型而不是 PL/SQL 集合，您可以执行以下操作：
FOR emp_rec IN (SELECT * FROM TABLE(v_myrec))
but it isn't, so you can't. You can't refer to a PL/SQL collection in plain SQL, even inside a PL/SQL block. And this isn't even a collection, it's just a single record type, so looping over it doesn't really make much sense anyway.
但事实并非如此，所以你不能。您不能在普通 SQL 中引用 PL/SQL 集合，即使在 PL/SQL 块中也是如此。这甚至不是一个集合，它只是一个单一的记录类型，所以无论如何循环它并没有多大意义。
