Oracle中的游标for循环 [英] Cursor for loop in Oracle

问题描述

请解释一下如何在oracle中使用cursor for loop。

Please, explain me how to use cursor for loop in oracle.

如果我使用下一个代码，一切正常。

If I use next code, all is fine.

for rec in (select id, name from students) loop
    -- do anything
end loop;

但是如果我为这个sql语句定义变量，它不工作。

But if I define variable for this sql statement, it doesn't work.

v_sql := 'select id, name from students';

for rec in v_sql loop
    -- do anything
end loop;

错误：PLS-00103

Error: PLS-00103

推荐答案

要解决与您的问题中的第二种方法相关的问题，您需要使用

To address issues associated with the second approach in your question you need to use

游标变量和显式方法打开游标和获取数据。不允许在 FOR 循环中使用游标变量

cursor variable and explicit way of opening a cursor and fetching data. It is not

：

declare
  l_sql varchar2(123);        -- variable that contains a query
  l_c   sys_refcursor;        -- cursor variable(weak cursor). 
  l_res your_table%rowtype;   -- variable containing fetching data  
begin
  l_sql := 'select * from your_table';

  -- Open the cursor and fetching data explicitly 
  -- in the LOOP.

  open l_c for l_sql;

  loop
    fetch l_c into l_res;
    exit when l_c%notfound;   -- Exit the loop if there is nothing to fetch.

     -- process fetched data 
  end loop;

  close l_c; -- close the cursor
end;

了解详情

这篇关于Oracle中的游标for循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！