oracle 在 sqlplus 命令模式下声明游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8942708/
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
Declare cursor in sqlplus command mode
提问by Mohan P
I have an SQL file which uses declares a cursor and I am running it using @abc, but it did not execute all statements and waiting without returning to command prompt. It did not proceed after declare cursor statement. When I tried to run the declare cursor statement in command mode, the same problem is happening again. I am able to return to SQL priompt only after pressing Ctrl + C. I am very new to SQL world. Though this could be a basic mistake, I am not able to find out the solution in any site. Any help is greatly appreciated.
我有一个使用声明游标的 SQL 文件，我正在使用@abc 运行它，但它没有执行所有语句并等待而不返回命令提示符。在声明游标语句之后它没有继续。当我尝试在命令模式下运行声明游标语句时，同样的问题再次发生。只有在按 Ctrl + C 后，我才能返回 SQL priompt。我对 SQL 世界很陌生。尽管这可能是一个基本错误，但我无法在任何站点中找到解决方案。任何帮助是极大的赞赏。
SQL> DECLARE CURSOR id_cursor IS SELECT id FROM user_names WHERE dept_no = 1002
AND BITAND(flags, 4) = 4 AND time_created BETWEEN 1137974400 AND 1326067199;
2
3
4 ;
5
6
回答by Vincent Malgrat
All DECLAREand BEGINblocks in SQL*Plus need to be ended with a /on a new empty line:
SQL*Plus 中的所有DECLARE和BEGIN块都需要/在一个新的空行上以 a结束：
SQL> DECLARE
2 CURSOR c IS SELECT 1 FROM DUAL;
3 BEGIN
4 NULL;
5 END;
6 /
PL/SQL procedure successfully completed.
Without this /SQL*Plus has no way to know that your statement has ended (so in your example it waits for user input).
没有这个/SQL*Plus 就无法知道你的语句已经结束（所以在你的例子中它等待用户输入）。
回答by John Doyle
After the ;type a /and enter. This will run your PL/SQL statement. But the sample you've given does nothing but declare a cursor. You must then use it like so:
;输入a后/回车。这将运行您的 PL/SQL 语句。但是您提供的示例只是声明了一个游标。然后你必须像这样使用它：
declare
cursor ID_CURSOR is
select ID
from USER_NAMES
where DEPT_NO = 1002
and bitand(FLAGS, 4) = 4
and TIME_CREATED between 1137974400 and 1326067199;
begin
for REC in ID_CURSOL loop
<do something with your data>;
end loop;
end;
/
