Oracle PL/SQL - PL SQL Statement SELECT INTO

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Statement
  4. SELECT INTO

Introduction

To get a single row of data, use a SELECT INTO command.

The following code shows how to get the count of all emp in an organization:

Demo

SQL>
SQL> drop table emp;

Table dropped.--  w  w w.ja v  a 2s  .c o  m

SQL> create table emp(
  2    empno    number(4,0),
  3    ename    varchar2(10),
  4    job      varchar2(9),
  5    mgr      number(4,0),
  6    hiredate date,
  7    sal      number(7,2),
  8    comm     number(7,2),
  9    deptno   number(2,0)
 10  );

Table created.
SQL>
SQL> insert into emp values(7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10);
SQL> insert into emp values(7698, 'BLAKE', 'MANAGER', 7839,to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30);
SQL> insert into emp values(7782, 'CLARK', 'MANAGER', 7839,to_date('9-6-1981','dd-mm-yyyy'), 2450, null, 10);
SQL> insert into emp values(7566, 'JONES', 'MANAGER', 7839,to_date('2-4-1981','dd-mm-yyyy'), 2975, null, 20);
SQL> insert into emp values(7788, 'SCOTT', 'ANALYST', 7566,to_date('13-JUL-87','dd-mm-rr') - 85, 3000, null, 20);
SQL>
SQL> declare
  2        v_out_nr NUMBER;
  3  begin
  4        select count(*) into v_out_nr
  5        from emp;
  6        DBMS_OUTPUT.put_line ('the number of emps is:'||v_out_nr);
  7  end;
  8  /
the number of emps is:5

PL/SQL procedure successfully completed.

To use a SELECT INTO command, the query must return exactly one row.

If the SELECT statement returns no rows, the code will throw a NO_DATA_FOUND exception.

If it returns more than one row, the code will throw the TOO_MANY_ROWS exception.

Related Topics