How to iterate through each row of a table in Oracle?

2 years ago
Olivia Parker
1 minute

In Oracle, Cursors can be used to iterate through each record in a table. Below is an example code that loops through each record in a table.

DECLARE
  -- 声明游标变量
  CURSOR cur_data IS
    SELECT * FROM your_table; -- 将 your_table 替换为你要操作的表名
  
  -- 声明变量来存储取出的数据
  -- 根据表的结构定义对应的变量类型和名称
  -- 例如，如果表中有两个列，分别是 id（整数类型）和 name（字符串类型），则定义两个变量
  v_id your_table.id%TYPE;
  v_name your_table.name%TYPE;
BEGIN
  -- 打开游标
  OPEN cur_data;
  
  -- 循环取出数据
  LOOP
    -- 使用 FETCH 语句从游标中取出一行数据，并将数据存储到声明的变量中
    FETCH cur_data INTO v_id, v_name;
    
    -- 判断是否取出了数据
    EXIT WHEN cur_data%NOTFOUND;
    
    -- 在这里可以对取出的数据进行操作
    -- 例如，输出数据
    DBMS_OUTPUT.PUT_LINE('ID: ' || v_id || ', Name: ' || v_name);
  END LOOP;

  -- 关闭游标
  CLOSE cur_data;
END;

The code above uses a cursor to fetch a row of data from the table and store it in the respective variables. The fetched data can then be manipulated, for example, printed to the console. The loop will continue until there is no more data to fetch, then it will exit the loop.

bannerAds