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; /
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.