写错了吧 FOR V_Record_Userlist in c_cursor loop在for循环中隐式的打开了游标,不需要open 所以你可以改成while看看
TO wylwyl1130(落雪山林) 有道理. 请问,如果我要在上面的语句中一定要用FOR语句应该如何做呢?
如果 是--wylwyl1130(落雪山林)--说的那样 我又改动了一下,还是有问题。 FUNCTION FUN_CS_GETDICTLIST return c_Type IS c_cursor c_Type; c_cursor2 c_Type;----改动 begin open c_cursor for select * FROM TEST6.TEST_2; c_cursor2:=c_cursor;----改动 close c_cursor2;----改动 FOR V_Record_Userlist in c_cursor2 loop----改动 DBMS_OUTPUT.PUT_LINE(V_Record_Userlist.USERNAME); END LOOP; return c_cursor; END FUN_CS_GETDICTLIST; ----错误 Compilation errors for PACKAGE BODY SYS.P_TESTError: PLS-00221:'C_CURSOR2'不是过程吗,或是未定义。 Line: 70 Text: FOR V_Record_Userlist in c_cursor2 loopError: PL/SQL: Statement ignored Line: 70 Text: FOR V_Record_Userlist in c_cursor2 loop
--一个for游标循环的简单例子: declare pi constant number(9, 7) := 3.1415927; area number(14, 2); cursor rad_cursor is select * from radius_vals; begin for rad_val in rad_cursor loop area := pi * power(rad_val.radius, 2); insert into AREAS values (rad_val.radius, area); end loop; end;-- for循环不需要open和close的,会隐式打开与关闭
请问,如果我要在上面的语句中一定要用FOR语句应该如何做呢?
我刚刚学习,很菜,不知道c_Type 是什么类型?
to JeromeLiu(烛光) 我自己定义的: type c_Type is ref cursor ;
游标变量不能在包(头)中声明,即使声明了也只能open for ,无法进行fetch等操作不过好像能编译成功,执行的时候就会出现问题
Currently, cursor variables are subject to the following restrictions:You cannot declare cursor variables in a package. For example, the following declaration is not allowed: CREATE PACKAGE emp_stuff AS TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; emp_cv EmpCurTyp; -- not allowed END emp_stuff; Remote subprograms on another server cannot accept the values of cursor variables. Therefore, you cannot use RPCs to pass cursor variables from one server to another. If you pass a host cursor variable to PL/SQL, you cannot fetch from it on the server side unless you also open it there on the same server call. You cannot use comparison operators to test cursor variables for equality, inequality, or nullity. You cannot assign nulls to a cursor variable. You cannot use REF CURSOR types to specify column types in a CREATE TABLE or CREATE VIEW statement. So, database columns cannot store the values of cursor variables. You cannot use a REF CURSOR type to specify the element type of a collection, which means that elements in a index-by table, nested table, or varray cannot store the values of cursor variables. Cursors and cursor variables are not interoperable; that is, you cannot use one where the other is expected. For example, the following cursor FOR loop is not allowed because it attempts to fetch from a cursor variable: DECLARE TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; emp_cv EmpCurTyp; ... BEGIN ... FOR emp_rec IN emp_cv LOOP ... -- not allowed END;
FOR V_Record_Userlist in c_cursor loop在for循环中隐式的打开了游标,不需要open
所以你可以改成while看看
请问,如果我要在上面的语句中一定要用FOR语句应该如何做呢?
我又改动了一下,还是有问题。
FUNCTION FUN_CS_GETDICTLIST return c_Type IS
c_cursor c_Type;
c_cursor2 c_Type;----改动
begin
open c_cursor for select * FROM TEST6.TEST_2;
c_cursor2:=c_cursor;----改动
close c_cursor2;----改动
FOR V_Record_Userlist in c_cursor2 loop----改动
DBMS_OUTPUT.PUT_LINE(V_Record_Userlist.USERNAME);
END LOOP;
return c_cursor;
END FUN_CS_GETDICTLIST;
----错误
Compilation errors for PACKAGE BODY SYS.P_TESTError: PLS-00221:'C_CURSOR2'不是过程吗,或是未定义。
Line: 70
Text: FOR V_Record_Userlist in c_cursor2 loopError: PL/SQL: Statement ignored
Line: 70
Text: FOR V_Record_Userlist in c_cursor2 loop
declare
pi constant number(9, 7) := 3.1415927;
area number(14, 2);
cursor rad_cursor is
select * from radius_vals;
begin
for rad_val in rad_cursor loop
area := pi * power(rad_val.radius, 2);
insert into AREAS values (rad_val.radius, area);
end loop;
end;-- for循环不需要open和close的,会隐式打开与关闭
type c_Type is ref cursor ;
CREATE PACKAGE emp_stuff AS
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
emp_cv EmpCurTyp; -- not allowed
END emp_stuff;
Remote subprograms on another server cannot accept the values of cursor variables. Therefore, you cannot use RPCs to pass cursor variables from one server to another.
If you pass a host cursor variable to PL/SQL, you cannot fetch from it on the server side unless you also open it there on the same server call.
You cannot use comparison operators to test cursor variables for equality, inequality, or nullity.
You cannot assign nulls to a cursor variable.
You cannot use REF CURSOR types to specify column types in a CREATE TABLE or CREATE VIEW statement. So, database columns cannot store the values of cursor variables.
You cannot use a REF CURSOR type to specify the element type of a collection, which means that elements in a index-by table, nested table, or varray cannot store the values of cursor variables.
Cursors and cursor variables are not interoperable; that is, you cannot use one where the other is expected. For example, the following cursor FOR loop is not allowed because it attempts to fetch from a cursor variable:
DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
emp_cv EmpCurTyp;
...
BEGIN
...
FOR emp_rec IN emp_cv LOOP ... -- not allowed
END;