Oracle PL/SQL 游标更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22441670/
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
Oracle PL/SQL cursor update
提问by user3357925
I'm using oracle. My SQL skills are very bad, I want to update information from a query that I have obtained through the use of a cursor, I've read about using the WHERE CURRENT OFstatement, but I don't see how that can fit into my current code. Does anyone mind lending a helping hand? I want to allow a calling program to update a row in the cursor (I want to update the race location) returned by the query in my current code. Here's my code so far:
我正在使用甲骨文。我的 SQL 技能非常糟糕，我想更新我通过使用游标获得的查询中的信息，我已经阅读了有关使用该WHERE CURRENT OF语句的信息，但我不知道它如何适合我当前的代码。有人介意伸出援助之手吗？我想允许调用程序更新当前代码中查询返回的游标中的一行（我想更新比赛位置）。到目前为止，这是我的代码：
DECLARE
l_race_rec race%rowtype;
CURSOR Query1
IS
SELECT *
FROM RACE
WHERE Race_Time='22-SEP-14 12.00.00.000000000';
BEGIN
OPEN Query1;
LOOP
FETCH query1 INTO l_race_rec;
EXIT WHEN query1%notfound;
dbms_output.put_line( l_race_rec.raceid || ', ' || l_race_rec.race_location || ', ' ||
l_race_rec.race_type || ', ' || l_race_rec.race_time || ', ' || l_race_rec.sex || ', ' ||
l_race_rec.minage || ', ' || l_race_rec.maxage );
END LOOP;
CLOSE Query1;
END;
回答by Bob Jarvis - Reinstate Monica
Here's an example to get you going:
这是一个让你开始的例子：
DECLARE
l_race_rec race%rowtype;
CURSOR Query1 IS
SELECT *
FROM RACE
WHERE Race_Time = '22-SEP-14 12.00.00.000000000';
nSome_value NUMBER := 42;
BEGIN
OPEN Query1;
LOOP
FETCH query1 INTO l_race_rec;
EXIT WHEN query1%notfound;
dbms_output.put_line(l_race_rec.raceid || ', ' ||
l_race_rec.race_location || ', ' ||
l_race_rec.race_type || ', ' ||
l_race_rec.race_time || ', ' ||
l_race_rec.sex || ', ' ||
l_race_rec.minage || ', ' ||
l_race_rec.maxage );
UPDATE RACE
SET SOME_FIELD = nSome_value
WHERE CURRENT OF QUERY1;
END LOOP;
CLOSE Query1;
END;
Share and enjoy.
分享和享受。
回答by wieseman
Why don't you use a cursor for loop.
为什么不使用游标循环。
...
for row in query1
loop
dbms_output.put_line(row.raceid || ', ' ||
row.race_location || ', ' ||
row.race_type || ', ' ||
row.race_time || ', ' ||
row.sex || ', ' ||
row.minage || ', ' ||
row.maxage );
UPDATE RACE
SET SOME_FIELD = nSome_value
WHERE CURRENT OF QUERY1;
end loop;
...
In this way there no need to open and to close a cursor.
这样就不需要打开和关闭游标。
Keep in mind that a cursor for loop works better for a cursor with more than 1 row as result.
请记住，循环游标对于结果超过 1 行的游标效果更好。
Good luck.
祝你好运。
