PL/SQL 游标如果没有数据和输出
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13555523/
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 Cursor If No Data & Output
提问by user1777711
CREATE OR REPLACE procedure verify_data
IS
cursor c1 is
select
e.id
from
table1 e
where
id IN (select id from table1)
and id in (select id from table2);
BEGIN
FOR ed in c1
LOOP
DBMS_OUTPUT.PUT_LINE(ed.id||ed.name);
END LOOP;
END;
/
When i execute this PL/SQL , it output as
当我执行这个 PL/SQL 时，它输出为
14Andrew R. Smith
14安德鲁·R·史密斯
I want to ask is how do i make it output as a normal result like this with the formating etc.. and also
我想问的是我如何通过格式化等将其输出为正常结果
Below is what happen if i run the select statement directly with at terminal
下面是如果我在终端直接运行 select 语句会发生什么
ID NAME
---------- --------------------------------------------------
14 Andrew R. Smith
First Question:How to make my DBMS_OUTPUT.PUT_LINE same as what appear on terminal. like the ID then below is the ID data , and NAME etc.
第一个问题：如何使我的 DBMS_OUTPUT.PUT_LINE 与终端上显示的相同。像 ID 那么下面是 ID 数据和 NAME 等。
Next Question:
下一个问题：
I want to ask is
我想问的是
how do i do a output that if cursor is empty or no result, i DBMS_OUTPUT.PUT_LINE("NO RECORDS");
我如何做一个输出，如果游标为空或没有结果，我 DBMS_OUTPUT.PUT_LINE("NO RECORDS");
Thanks for all help!!
谢谢大家的帮助！！
Update:
更新：
I did this
我做了这个
CREATE OR REPLACE procedure verify_data
IS
cursor c1 is
select
e.id
from
table1 e
where
id IN (select id from table1)
and id in (select id from table2);
BEGIN
FOR ed in c1
LOOP
DBMS_OUTPUT.PUT_LINE(ed.id||ed.name);
if c1%notfound then
DBMS_OUTPUT.PUT_LINE('Okay');
end if;
END LOOP;
END;
But it does not print Okaywhen no record is fetch from the PL/SQL. I added the c1%notfound to after END LOOP
但是当没有从 PL/SQL 获取记录时，它不会打印 Okay。我在 END LOOP 之后添加了 c1%notfound
采纳答案by Robert
If you want to see columns name you have to add before your loop one more line:
如果要查看列名称，则必须在循环之前再添加一行：
DBMS_OUTPUT.PUT_LINE('col1 col2 ...');
I think this is only one way to do that.
我认为这只是一种方法。
Below code shows how to check cursor:
下面的代码显示了如何检查光标：
if c1%notfound then
DBMS_OUTPUT.PUT_LINE('NO RECORDS');
end if;
Hereyou can find more information about cursor attributes.
在这里您可以找到有关游标属性的更多信息。
CREATE OR REPLACE procedure verify_data
IS
id number;
name varchar;
cursor c1 is
select
e.id,e.name
from
table1 e
where
id IN (select id from table1)
and id in (select id from table2);
BEGIN
open c1;
fetch c1 into id,name;
if c1%notfound then
DBMS_OUTPUT.PUT_LINE('OK')
EXIT;
else
DBMS_OUTPUT.PUT_LINE(id||name)
end if;
close c1;
END;
回答by nightfox79
Since you are using dbms_output you won't get any headings like in a normal sql select statement.
由于您使用的是 dbms_output，因此您不会像普通的 sql select 语句那样获得任何标题。
So the only way I know is printing your own headings. And if you initialize a counter before the loop and increase it inside the loop, you can check after the loop if there was a result. Then you can print a message like "NO RECORDS" if you want.
所以我知道的唯一方法是打印你自己的标题。如果你在循环之前初始化一个计数器并在循环内增加它，你可以在循环之后检查是否有结果。然后，您可以根据需要打印“无记录”之类的消息。
