Implicit Cursors

Contact Us or call 1-877-932-8228
Implicit Cursors

Implicit Cursors

Code Sample:

Use-of-SQL-in-PLSQL/Demos/select_into.sql
declare
  v_first_name varchar2(100);
  v_last_name  varchar2(100);
begin
  select first_name, last_name
  into v_first_name, v_last_name
  from employees
  where employee_id = 100;
  dbms_output.put_line(v_first_name || '  ' || v_last_name);
exception
  when NO_DATA_FOUND then
    dbms_output.put_line('No data found-is the employee id valid?');
  when TOO_MANY_ROWS then
    dbms_output.put_line('Too many records. ' || 
                         'Are you identifying the employee by id?');

end;
/

Code Explanation

If you expect to retrieve exactly one row you can use the SELECT INTO syntax shown here. One common case is when obtaining a count of records that satisfy a condition: exactly one value is guaranteed to be returned. Note that INSERT, UPDATE, DELETE and SELECT INTO all use implicit cursors.

If a SELECT INTO clause retrieves multiple records, the following exception will be raised: ORA-01422: exact fetch returns more than requested number of rows. This can be seen by modifying the demo above and removing the WHERE clause.

If a SELECT INTO clause retrieves no records, the following exception will be raised: ORA-01403: no data found. This can be shown by changing the demo so that the where clause specifies WHERE employee_id=1..

These exceptions can be addressed either through exception handling or by using explicit cursors. An explicit cursor can be used to handle a query that returns zero or more rows. It will be discussed later.

Next