Oracle stored procedure cursor usage analysis

Time:2020-10-27

This paper describes the Oracle stored procedure cursor usage. For your reference, the details are as follows:

Five steps to use a cursor

1. Declare variables to hold the index returned by the select statement
2. Declare the cursor and specify the select statement
3. Open cursor
4. Get record from cursor
5. Close cursor

Get each record from the cursor using the fetch statement. The fetch statement reads the index of the column into the specified variable;

Grammar:


fetch cursor_name
into variable[, variable ...];

example:


create or replace procedure sel_person
is
v_id person.id%type;
v_name person.pname%type;
v_birthday person.birthday%type;
cursor temp_cursor is select * from person;
begin
  open temp_cursor;
  loop
  fetch temp_cursor into v_id,v_name,v_birthday;
  exit when temp_cursor%notfound;
  dbms_output.put_line(v_id||'----'||v_name||'----'||v_birthday);
  end loop;
  close temp_cursor;
end sel_person;

Note: to determine whether the loop ends, you can use the boolean variable temp_ cursor%notfound。 This variable is true when the fetch reaches the last record in the cursor and no more records can be read.

I hope this paper will be helpful to Oracle program design.