下面步骤展示了如何使用显式游标循环获取记录集的每行记录。

1. 首先在声明部分。

a. 声明一个游标。

CURSOR cursor_name IS query;

或者

CURSOR cursor_name(para_namedata_type) IS query;

b. 声明一个记录保存游标返回的行记录。

record_name cursor_name%ROWTYPE;

1. 然后在执行部分。

a. 打开游标

OPEN cursor_name;

或者

OPEN cursor_name(para_name);

b. 从游标记录集中获取行数据。每次一行，结合下面格式的 LOOP 语句一起使用可以获取多行数据。

LOOP
FETCHcursor_name INTO record_name;
EXIT WHENcursor_name%NOTFOUND;
statement;
[ statement; ]...
END LOOP;

c. 关闭游标

CLOSE cursor_name;

示例：使用显式游标循环获取记录集的每行记录

delimiter /
CREATE OR REPLACE PACKAGE pkg_ware_mgmt as
    PROCEDURE sp_record_print(p_w_id IN ware.w_id%type);
END;
/
delimiter ;
delimiter /
CREATE OR REPLACE PACKAGE BODY pkg_ware_mgmt 
AS  
     PROCEDURE sp_record_print_by_record(p_record IN ware%ROWTYPE)
      AS
      BEGIN
            dbms_output.put_line('Print a record :');
            dbms_output.put_line('W_ID : ' || p_record.w_id 
                || ', W_YTD : ' || p_record.w_ytd 
                || ', W_TAX : ' || p_record.w_tax 
                || ', W_NAME : ' || p_record.w_name
                || ', W_STREET_1 : ' || p_record.w_street_1
                || ', W_STREET_2 : ' || p_record.w_street_2
                || ', W_CITY : ' || p_record.w_city
                || ', W_STATE : ' || p_record.w_state
                || ', W_ZIP : ' || p_record.w_zip )
            ;
            dbms_output.put_line('');
    END;
    PROCEDURE sp_record_print(p_w_id IN ware.w_id%TYPE )
    IS
        CURSOR c1(cp_w_id ware.w_id%TYPE ) IS SELECT * FROM ware WHERE w_id = cp_w_id ;
        r_ware  ware%ROWTYPE; 
    BEGIN
        OPEN c1(p_w_id);
        dbms_output.put_line('Open a cursor with a parameter [ ' || p_w_id || ' ].');
        LOOP 
            FETCH c1 INTO r_ware ;
            dbms_output.put_line('Fetch the cursor one time.');
            EXIT WHEN c1%NOTFOUND ;
            sp_record_print_by_record(r_ware);
        END LOOP;
    EXCEPTION 
        WHEN OTHERS THEN
            dbms_output.put_line('Raise an unkown exception !');          
  END ;
END; 
/
delimiter ;
obclient> set serveroutput on;
Query OK, 0 rows affected (0.00 sec)
obclient> call pkg_ware_mgmt.sp_record_print(1);
Query OK, 0 rows affected (0.12 sec)
Open a cursor with a parameter [ 1 ].
Fetch the cursor one time.
Print a record :
W_ID : 1, W_YTD : 1200, W_TAX : .1868, W_NAME : W_NAME_1, W_STREET_1 : jTNkXKWXOdh, W_STREET_2 : lf9QXTXXGoF04IZBkCP7, W_CITY : srRq15uvxe5, W_STATE : GQ, W_ZIP : 506811111
Fetch the cursor one time.
obclient>