oracle 如何在开始后声明游标？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33387022/
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
How to declare a cursor after BEGIN?
提问by olivia
I want to know if a cursor can be declared after BEGIN.
我想知道是否可以在BEGIN.
And how can I export the result of the plsql to an Excel sheet, because I have to run this procedure as a job.
以及如何将 plsql 的结果导出到 Excel 工作表，因为我必须将此过程作为作业运行。
CREATE OR REPLACE PROCEDURE masc(v_amsprogramid VARCHAR2) AS
v_mid VARCHAR2(50);
v_sid VARCHAR2(50);
CURSOR c1 IS
SELECT DISTINCT mid
FROM table_a WHERE aid = v_aid
ORDER BY mid;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_mid;
EXIT WHEN c1%NOTFOUND;
DECLARE
CURSOR c2 IS
SELECT DISTINCT sid INTO v_sid
FROM table_b WHERE mid = v_mid;
BEGIN
OPEN c2;
LOOP
FETCH c1 INTO v_mid;
EXIT WHEN c1%NOTFOUND;
dbms_output.PUT_LINE('MID : ' || v_mid);
dbms_output.PUT_LINE('Sid : ' || v_sid);
END LOOP;
CLOSE c2;
END LOOP;
CLOSE c1;
END masc;
回答by Wernfried Domscheit
Maybe you are looking for this:
也许你正在寻找这个：
create or replace PROCEDURE MASC (V_AMSPROGRAMID VARCHAR2) AS
V_MID VARCHAR2(50);
V_SID VARCHAR2(50);
CURSOR C1 IS
SELECT distinct MID from table_a WHERE AID = V_AID
ORDER BY MID;
CURSOR C2 IS
SELECT DISTINCT SID INTO V_SID FROM table_b WHERE MID = V_MID
ORDER BY MID;
BEGIN
...
or
或者
create or replace PROCEDURE MASC (V_AMSPROGRAMID VARCHAR2) AS
V_MID VARCHAR2(50);
V_SID VARCHAR2(50);
CURSOR C1 IS
SELECT distinct MID from table_a WHERE AID = V_AID
ORDER BY MID;
CURSOR C2(v in NUMBER) IS
SELECT DISTINCT SID INTO V_SID FROM table_b WHERE MID = v
ORDER BY MID;
BEGIN
OPEN C1;
...
OPEN C2(V_MID);
...
回答by Lalit Kumar B
I want to know if i can declare a cursor after begin
我想知道是否可以在开始后声明游标
Not exactly. But you could use a cursor for loopinstead of declaring an explicit cursor.
不完全是。但是您可以使用游标 for 循环而不是声明显式游标。
For example,
例如，
FOR i IN (SELECT distinct MID from table_a WHERE AID = V_AID ORDER BY MID)
LOOP
<do something>
END LOOP;
But anyway, this would be slower as row-by-row is slow-by-slow. I don't see a need of procedure at all. If you really need to do it in PL/SQLthen consider BULK COLLECT.
但无论如何，这会更慢，因为逐行是缓慢的。我认为根本不需要程序。如果您确实需要在PL/SQL 中执行此操作，请考虑BULK COLLECT。
And how can i export the result of the plsql to an excel sheet because i ahev to run this procedure as a job.
以及如何将 plsql 的结果导出到 Excel 表，因为我希望将此过程作为作业运行。
I don't see a need of PL/SQLin that case. You could simply use SPOOLin SQL*Plus.
在这种情况下，我认为不需要PL/SQL。您可以简单地在SQL*Plus 中使用SPOOL。
For example,
例如，
sqlplus user/pass@service_name
<required formatting options>
SPOOL /location/myfile.csv
SELECT distinct MID from table_a WHERE AID = V_AID ORDER BY MID;
SPOOL OFF
回答by Prabhat Sharma
U can use reference cursor for this purpose
你可以为此使用参考光标
create or replace PROCEDURE MASC (V_AMSPROGRAMID VARCHAR2) AS
V_MID VARCHAR2(50);
V_SID VARCHAR2(50);
C1 sys_refcursor ;
c2 sys_refcursor ;
BEGIN
OPEN C1 for SELECT distinct MID from table_a WHERE AID = V_AID
ORDER BY MID;
LOOP
FETCH C1 INTO V_MID;
EXIT WHEN C1%NOTFOUND;
open C2 for SELECT DISTINCT SID INTO V_SID FROM table_b WHERE MID = V_MID;
LOOP
FETCH C1 INTO V_MID;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('MID : ' || V_MID);
DBMS_OUTPUT.PUT_LINE('Sid : ' || V_SID);
END LOOP;
CLOSE C2;
CLOSE C1;
END LOOP;
回答by Boneist
You can declare multiple cursors in the same pl/sql block. There is no need to declare the second cursor after you've opened the first cursor!
您可以在同一个 pl/sql 块中声明多个游标。打开第一个游标后，无需声明第二个游标！
You would write something like:
你会写这样的东西：
create or replace procedure masc (p_amsprogramid varchar2)
as
v_mid varchar2(50);
v_sid varchar2(50);
cursor c1
is
select distinct mid
from table_a
where aid = p_amsprogramid
order by mid;
cursor c2
is
select distinct sid
from table_b
where mid = v_mid;
begin
open c1;
loop
fetch c1 into v_mid;
exit when c1%notfound;
open c2;
loop
fetch c1 into v_mid;
exit when c1%notfound;
dbms_output.put_line('mid : ' || v_mid);
dbms_output.put_line('sid : ' || v_sid);
end loop;
close c2;
end loop;
close c1;
end masc;
/
However, if you were to replace your open-cursor-loop-fetches as a cursor-for-loop, you could simplify things a bit:
但是，如果您要将 open-cursor-loop-fetches 替换为 cursor-for-loop，则可以稍微简化一下：
create or replace procedure masc (p_amsprogramid varchar2)
as
cursor c1
is
select distinct mid
from table_a
where aid = p_amsprogramid
order by mid;
cursor c2
is
select distinct sid
from table_b
where mid = v_mid;
begin
for rec1 in c1
loop
for rec2 in c2
loop
dbms_output.put_line('mid : ' || rec1.mid);
dbms_output.put_line('sid : ' || rec2.sid);
end loop;
end loop;
end masc;
/
Looking at that, you've got a nested cursor loop. This screams procedural thinking, rather than set-based thinking, which is pretty much a big no-no when you're working with datasets in the database (ie. it's slow. You're having to constantly switch between the SQL and PL/SQL engines, instead of simply asking the SQL engine to calculate everything before delivering it to the PL/SQL engine).
看看那个，你有一个嵌套的游标循环。这激起了程序思维，而不是基于集合的思维，当您在数据库中处理数据集时，这几乎是一个很大的禁忌（即它很慢。您必须不断地在 SQL 和 PL/ SQL 引擎，而不是简单地要求 SQL 引擎在将其交付给 PL/SQL 引擎之前计算所有内容）。
By doing the nested cursor loop, you're basically reinventing NESTED LOOP joins - something the SQL engine can do far better than you can (not to mention it might not be the most efficient join, and the SQL engine could choose a better way of doing the join!). Any time you see a nested cursor loop, you should IMMEDIATELYstop and look to see if you can combine the queries into a single select statement. (Actually, any time you see a loop you should pause and consider whether you really do need it; sometimes it's necessary, but if you're doing something like selecting a set of results and then going through each row and then doing an update, consider merging the select into the update so that you have a statement that updates all the rows at once. It'll be much faster!)
通过执行嵌套游标循环，您基本上是在重新发明 NESTED LOOP 连接 - SQL 引擎可以做得比您做得更好（更不用说它可能不是最有效的连接，并且 SQL 引擎可以选择更好的方式加入！）。任何时候看到嵌套的游标循环时，都应该立即停止并查看是否可以将查询合并为一个选择语句。（实际上，任何时候你看到一个循环你都应该暂停并考虑你是否真的需要它；有时这是必要的，但如果你正在做一些事情，比如选择一组结果，然后遍历每一行，然后进行更新，考虑将选择合并到更新中，以便您有一个语句可以一次更新所有行。它会快得多！）
For example, your original procedure could be rewritten as:
例如，您的原始程序可以重写为：
create or replace procedure masc (p_amsprogramid varchar2)
as
cursor c1
is
select distinct a.mid,
b.sid
from table_a a
inner join table_b b on (a.mid = b.mid)
where a.aid = p_amsprogramid
order by mid;
begin
for rec1 in c1
loop
dbms_output.put_line('mid : ' || rec1.mid);
dbms_output.put_line('sid : ' || rec1.sid);
end loop;
end masc;
/
Much simpler to read, understand and maintain, I think you'll agree!
阅读、理解和维护要简单得多，我想你会同意的！
If you're wanting to write the results of the sql query out as a file, you'll need to use UTL_FILE, instead of DBMS_OUTPUT. Bear in mind that the directory the file is written to needs to be something that is mounted/mapped to the server the database sits on. If you write the results as character-delimited, you can then easily import that file into Excel.
如果您想将 sql 查询的结果作为文件写出，则需要使用UTL_FILE, 而不是DBMS_OUTPUT. 请记住，写入文件的目录需要安装/映射到数据库所在的服务器。如果将结果写为字符分隔，则可以轻松地将该文件导入 Excel。
You might find this to be of use.
您可能会发现这很有用。
