ORA-06511: PL/SQL 游标已打开
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14697843/
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
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-01 13:28:42 来源:igfitidea点击:
ORA-06511: PL/SQL Cursor already open
提问by RyanPatrick
Anyone have any ideas as to why the system is telling me that PAR_CURis already open? Everything was working fine until I added the outer most cursor (dup_cur) and now I'm getting this error. Thanks for any help you might have. The data is below as well.
任何人都知道为什么系统告诉我PAR_CUR已经打开？一切正常，直到我添加了最外面的光标 ( dup_cur)，现在我收到了这个错误。感谢您提供的任何帮助。数据也在下面。
Error report:
错误报告：
ORA-06511: PL/SQL: cursor already open
ORA-06512: at line 18
ORA-06512: at line 61
06511. 00000 - "PL/SQL: cursor already open"
*Cause: An attempt was made to open a cursor that was already open.
*Action: Close cursor first before reopening.
CODE:
代码：
ACCEPT p_1 prompt 'PLEASE ENTER THE REGION:'
DECLARE
v_child regions.child_name%TYPE := '&p_1';
v_parent regions.parent_name%TYPE;
v_parent2 regions.child_name%TYPE;
v_parent3 regions.child_name%TYPE;
v_count NUMBER := 0;
v_regionnumber NUMBER := 1;
v_parentcount NUMBER := 0;
v_dup regions.child_name%TYPE;
CURSOR reg_cur IS
SELECT Upper(parent_name)
FROM regions
WHERE Upper(child_name) = Upper(v_child)
AND Upper(parent_name) = Upper(v_dup);
CURSOR par_cur IS
SELECT Upper(parent_name)
FROM regions
WHERE Upper(child_name) = v_parent
AND parent_name IS NOT NULL;
CURSOR dup_cur IS
SELECT Upper(parent_name)
FROM regions
WHERE Upper(child_name) = Upper(v_child);
BEGIN OPEN dup_cur;
开始打开 dup_cur;
LOOP
FETCH dup_cur INTO v_dup;
EXIT WHEN dup_cur%NOTFOUND;
SELECT Count(*)
INTO v_count
FROM regions
WHERE Upper(child_name) = Upper(v_child);
SELECT Count(parent_name)
INTO v_parentcount
FROM regions
WHERE Upper(parent_name) = Upper(v_child);
IF v_count > 0
OR v_parentcount > 0 THEN
SELECT Count(parent_name)
INTO v_count
FROM regions
WHERE Upper(child_name) = Upper(v_child);
IF v_count > 0 THEN
OPEN reg_cur;
FETCH reg_cur INTO v_parent;
dbms_output.Put_line('----- Begin Output -----');
LOOP
IF v_regionnumber < 2 THEN
dbms_output.Put_line('Line 1: (Region 1) '
|| Upper(v_child));
dbms_output.Put_line('Line 2: (Region 1) '
|| Upper(v_child)
|| ' --> '
|| '(Region 2) '
|| Upper (v_parent));
END IF;
OPEN par_cur;
v_parent2 := v_parent;
FETCH par_cur INTO v_parent;
EXIT WHEN par_cur%NOTFOUND;
v_regionnumber := v_regionnumber + 1;
IF v_regionnumber = 2 THEN
dbms_output.Put_line('Line 3: '
|| '(Region 1) '
|| Upper(v_child)
|| ' --> '
|| '(Region 2) '
|| Upper(v_parent2)
|| ' --> '
|| '(Region 3) '
|| Upper(v_parent));
ELSE
IF v_regionnumber = 3 THEN
dbms_output.Put_line('Line 4: '
|| '(Region 1) '
|| Upper(v_child)
|| ' --> '
|| '(Region 2) '
|| Upper(v_parent3)
|| ' --> '
|| '(Region 3) '
|| Upper(v_parent2)
|| ' --> '
|| '(Region 4) '
|| Upper(v_parent));
END IF;
END IF;
CLOSE par_cur;
v_parent3 := v_parent2;
END LOOP;
dbms_output.Put_line('----- End_Output -----');
CLOSE reg_cur;
ELSE
dbms_output.Put_line('----- Begin Output -----'
|| Chr(10)
|| 'Line 1: (Region 1) '
|| Upper(v_child)
|| Chr(10)
|| '----- End_Output -----');
END IF;
ELSE
dbms_output.Put_line('----- Begin Output -----'
|| Chr(10)
|| Upper(v_child)
||' is not in the table.'
|| Chr(10)
|| '----- End_Output -----');
END IF;
END LOOP;
CLOSE dup_cur;
END;
CREATE TABLE regions
(
PARENT_NAME VARCHAR2(30),
CHILD_NAME VARCHAR2(30)
);
INSERT INTO regions VALUES('Texas','Rockford');
INSERT INTO regions VALUES('Colorado','Aurora');
INSERT INTO regions VALUES(NULL,'Asia');
INSERT INTO regions VALUES(NULL,'Australia');
INSERT INTO regions VALUES(NULL,'Europe');
INSERT INTO regions VALUES(NULL,'North America');
INSERT INTO regions VALUES('Asia','China');
INSERT INTO regions VALUES('Asia','Japan');
INSERT INTO regions VALUES('Australia','New South Wales');
INSERT INTO regions VALUES('New South Wales','Sydney');
INSERT INTO regions VALUES('Canada','Ontario');
INSERT INTO regions VALUES('China','Beijing');
INSERT INTO regions VALUES('England','London');
INSERT INTO regions VALUES('Europe','United Kingdom');
INSERT INTO regions VALUES('Illinois','Aurora');
INSERT INTO regions VALUES('Illinois','Chicago');
INSERT INTO regions VALUES('Illinois','Rockford');
INSERT INTO regions VALUES('Wisconsin','Madison');
INSERT INTO regions VALUES('Japan','Osaka');
INSERT INTO regions VALUES('Japan','Tokyo');
INSERT INTO regions VALUES('North America','Canada');
INSERT INTO regions VALUES('North America','United States');
INSERT INTO regions VALUES('Ontario','Ottawa');
INSERT INTO regions VALUES('Ontario','Toronto');
INSERT INTO regions VALUES('United States','Colorado');
INSERT INTO regions VALUES('United States','Illinois');
INSERT INTO regions VALUES('United States','Texas');
INSERT INTO regions VALUES('United Kingdom','England');
COMMIT;
回答by DazzaL
you're opening and closing the cursor in the loop. you should open and close it outside of the loop.
你在循环中打开和关闭光标。你应该在循环之外打开和关闭它。
ie.
IE。
FETCH REG_CUR INTO V_PARENT;
DBMS_OUTPUT.PUT_LINE('----- Begin Output -----');
OPEN PAR_CUR; -- ************OPEN HERE
LOOP
IF V_REGIONNUMBER < 2 THEN
DBMS_OUTPUT.PUT_LINE('Line 1: (Region 1) ' || UPPER(V_CHILD));
DBMS_OUTPUT.PUT_LINE('Line 2: (Region 1) ' || UPPER(V_CHILD) || ' --> ' || '(Region 2) ' || UPPER (V_PARENT));
END IF;
V_PARENT2 := V_PARENT;
FETCH PAR_CUR INTO V_PARENT;
EXIT WHEN PAR_CUR%NOTFOUND;
V_REGIONNUMBER := V_REGIONNUMBER + 1;
IF V_REGIONNUMBER =2 THEN
DBMS_OUTPUT.PUT_LINE('Line 3: ' || '(Region 1) '|| UPPER(V_CHILD) || ' --> ' || '(Region 2) ' || UPPER(V_PARENT2) || ' --> ' || '(Region 3) ' || UPPER(V_PARENT));
ELSE
IF V_REGIONNUMBER =3 THEN
DBMS_OUTPUT.PUT_LINE('Line 4: ' || '(Region 1) '|| UPPER(V_CHILD) || ' --> ' || '(Region 2) ' || UPPER(V_PARENT3) || ' --> ' || '(Region 3) ' || UPPER(V_PARENT2)|| ' --> ' || '(Region 4) ' || UPPER(V_PARENT));
END IF;
END IF;
V_PARENT3 := V_PARENT2;
END LOOP;
CLOSE PAR_CUR;-- ************CLOSE HERE
as you were opening in the loop, on the 2nd iteration, the cursor errors as its already open
当您在循环中打开时，在第二次迭代中，游标错误，因为它已经打开
p.s. it looks like you're just trying to figure out the hierarchy and print it. if so you can just do this:
ps 看起来你只是想弄清楚层次结构并打印出来。如果是这样，你可以这样做：
SQL> select ltrim(str, ' --> ') str
2 from (select child_name, level, SYS_CONNECT_BY_PATH('(Region ' || level || ') ' || child_name, ' --> ') str,
3 parent_name
4 from regions
5 start with child_name = 'Rockford'
6 connect by child_name = prior parent_name)
7 where parent_name is null
8 /
STR
--------------------------------------------------------------------------------
(Region 1) Rockford --> (Region 2) Illinois --> (Region 3) United States --> (Re
gion 4) North America
(Region 1) Rockford --> (Region 2) Texas --> (Region 3) United States --> (Regio
n 4) North America
SQL> select ltrim(str, ' --> ') str
2 from (select child_name, level, SYS_CONNECT_BY_PATH('(Region ' || level || ') ' || child_name, ' --> ') str,
3 parent_name
4 from regions
5 start with child_name = 'London'
6 connect by child_name = prior parent_name)
7 where parent_name is null
8 /
STR
--------------------------------------------------------------------------------
(Region 1) London --> (Region 2) England --> (Region 3) United Kingdom --> (Regi
on 4) Europe
SQL>
回答by Mohsen Heydari
its (generally) recommended to use this syntax when opening any cursor
它（通常）建议在打开任何游标时使用此语法
IF reg_cur %ISOPEN THEN
CLOSE reg_cur ;
END IF;
OPEN reg_cur ;
hope this will help ...
希望这会有所帮助...
