Oracle PL/SQL - Handling exceptions in implicit cursors

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Cursor
  4. Implicit Cursors

Introduction

You can use an implicit cursor with the SELECT INTO command even if there is a possibility of returning no rows or more than one row from the query.

Demo

SQL>
SQL> drop table dept;

Table dropped.-- from   www . ja va  2 s.  c om

SQL> create table dept(
  2    deptno number(2,0),
  3    dname  varchar2(14),
  4    loc    varchar2(13),
  5    constraint pk_dept primary key (deptno)
  6  );

Table created.

SQL>
SQL> insert into dept values(10, 'ACCOUNTING', 'NEW YORK');
SQL> insert into dept values(20, 'RESEARCH', 'DALLAS');
SQL> insert into dept values(30, 'SALES', 'CHICAGO');
SQL> insert into dept values(40, 'OPERATIONS', 'BOSTON');
SQL>
SQL> create or replace function f_getdName_tx (in_deptNo NUMBER)
  2    return VARCHAR2 is v_out_tx dept.dName%TYPE;
  3  begin
  4         select dName into v_out_tx
  5         from dept
  6         where deptNo =  in_deptNo;
  7         return v_out_tx;
  8  exception
  9         when no_data_found then
 10              return 'NO SUCH DEPARTMENT';
 11  end f_getdName_tx;
 12  /

Function created.

Related Topic