oracle oracle存储过程中如何获取游标数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24697517/
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 fetch the cursor data in oracle stored procedure
提问by user3829086
create or replace
PROCEDURE get_new
AS
CUST_ID varchar2(100);
ROUTERNAME_N VARCHAR2(100);
BEGIN
CURSOR c1 IS
SELECT TRAFFIC_CUST_ID,ROUTERNAME INTO CUST_ID,ROUTERNAME_N
FROM INTERFACE_ATTLAS
WHERE rownum > 3;
my_ename INTERFACE_ATTLAS.TRAFFIC_CUST_ID%TYPE;
my_salary INTERFACE_ATTLAS.ROUTERNAME%TYPE;
LOOP
FETCH c1 INTO my_ename;
FETCH c1 INTO my_salary;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(my_ename);
end loop;
end;
I am new to oracle and stored procedure. I am trying to get the rows using cursor fetch, and getting following error:
我是 oracle 和存储过程的新手。我正在尝试使用游标获取来获取行，并收到以下错误：
PLS-00103: Encountered the symbol "C1" when expecting one of the following:
:= . ( @ % ;
回答by neshkeev
Rewrite it like this:
像这样重写它：
create or replace
PROCEDURE get_new
AS
my_ename INTERFACE_ATTLAS.TRAFFIC_CUST_ID%TYPE;
my_salary INTERFACE_ATTLAS.ROUTERNAME%TYPE;
CURSOR c1 IS
SELECT TRAFFIC_CUST_ID,ROUTERNAME
FROM INTERFACE_ATTLAS
WHERE rownum > 3;
BEGIN
open c1;
LOOP
FETCH c1 INTO my_ename, my_salary;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(my_ename);
end loop;
close c1;
end;
Do not forget to open and close cursors. It always will print nothing because of rownum > 3;You wanted to type: rownum < 3;, didn't you?
不要忘记打开和关闭游标。它总是不会打印任何东西，因为rownum > 3;你想输入: rownum < 3;，不是吗？
回答by Birma Ram
create or replace
PROCEDURE get_new
AS
CUST_ID varchar2(100);
ROUTERNAME_N VARCHAR2(100);
BEGIN
CURSOR c1 IS
SELECT TRAFFIC_CUST_ID,ROUTERNAME INTO CUST_ID,ROUTERNAME_N
FROM INTERFACE_ATTLAS
WHERE rownum > 3;
my_ename INTERFACE_ATTLAS.TRAFFIC_CUST_ID%TYPE;
my_salary INTERFACE_ATTLAS.ROUTERNAME%TYPE;
LOOP
FETCH c1 INTO my_ename;
FETCH c1 INTO my_salary;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(my_ename);
end loop;
end;
Cursor should be declare in the declaration part. Then have to open in begin-end section. In the declaration section you can not assign value to variable. When you are fetching value you can not select randomly value form cursor,
游标应在声明部分声明。然后必须在开始结束部分打开。在声明部分，您不能为变量赋值。取值时不能从游标中随机选择值，
modified code:
修改后的代码：
create or replace
PROCEDURE get_new
AS
no mean --CUST_ID varchar2(100);
no means -- ROUTERNAME_N VARCHAR2(100);
CURSOR c1 IS
SELECT deptno,job
FROM emp;
my_ename emp.deptno%TYPE;
my_salary emp.job%TYPE;
BEGIN
open c1;
LOOP
fetch c1 into my_ename,my_salary;
-- FETCH c1 INTO my_ename;
--FETCH c1 INTO my_salary;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(my_ename);
end loop;
end;
execute get_new.
回答by Emmanuel
You should probably declare your cursor and your variables my_enameand my_salaryin the dedicated section, i.e. before the BEGIN, and then open your cursor:
你或许应该申报光标和你的变量my_ename和my_salary之前的专用部分，即BEGIN，然后打开你的光标：
IS
CUST_ID varchar2(100);
ROUTERNAME_N VARCHAR2(100);
C1 sys_refcursor;
my_ename INTERFACE_ATTLAS.TRAFFIC_CUST_ID%TYPE;
my_salary INTERFACE_ATTLAS.ROUTERNAME%TYPE;
BEGIN
OPEN C1 for
SELECT ...
回答by Thorsten Kettner
You would have to declare the Cursor before BEGIN. You would use no INTO clause in the cursor declaration. Then you would have to OPEN the cursor. Then you would FETCH INTO my_ename, my_salary, not one after the other (you fetch rows, not columns). WHERE rownum > 3returns no rows. As you don't want a first row, you will never get a second, third and fourth either.
您必须在 BEGIN 之前声明 Cursor。您不会在游标声明中使用 INTO 子句。然后你必须打开光标。然后你会FETCH INTO my_ename, my_salary，而不是一个接一个（你获取行，而不是列）。WHERE rownum > 3不返回任何行。由于您不想要第一排，因此您也永远不会获得第二、第三和第四排。
And you can use an implicit cursor which is easier to deal with (no need to open, fetch and close explicitely):
并且您可以使用更容易处理的隐式游标（无需显式打开、获取和关闭）：
BEGIN
FOR rec IN
(
select traffic_cust_id, routername
from interface_attlas
where rownum <= 3
) LOOP
DBMS_OUTPUT.PUT_LINE(rec.traffic_cust_id || ': ' || rec.salary);
END LOOP;
END;
