Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 38 ยท PL/SQL Basics

SELECT INTO, %TYPE and Records

Read table data into PL/SQL variables with SELECT INTO, anchor types with %TYPE and %ROWTYPE, use records, run DML inside blocks and check SQL%ROWCOUNT.

PL/SQL exists to combine program logic with SQL. Inside a block you can write INSERT, UPDATE, DELETE and MERGE exactly as in SQL, and you can pull query results into variables with SELECT ... INTO. This lesson covers the glue: how a value gets from a table into a variable, how to declare variables whose types follow the table (%TYPE and %ROWTYPE), and how to find out what your DML statement did.

All code here is PL/SQL and read-only. Stand-ins use SQL to show the same data operations in the editor. You should already know blocks and variables and control flow.

SELECT INTO

SELECT columns INTO variables FROM ... stores the result in PL/SQL variables, one variable per column and in the same order:

PLSQL
DECLARE
  v_last_name  VARCHAR2(40);
  v_salary     NUMBER(9,2);
BEGIN
  SELECT last_name, salary
  INTO   v_last_name, v_salary
  FROM   employees
  WHERE  employee_id = 107;

  DBMS_OUTPUT.PUT_LINE(v_last_name || ' earns ' || v_salary);
END;
/

The query must return exactly one row. Otherwise Oracle raises one of two predefined exceptions:

Rows returned Exception Error
0 NO_DATA_FOUND ORA-01403
2 or more TOO_MANY_ROWS ORA-01422

An aggregate query always returns one row, even over an empty table: SELECT COUNT(*) INTO v_n FROM employees WHERE ... is safe, but SELECT MAX(salary) INTO ... yields NULL when nothing matches instead of NO_DATA_FOUND. Catch the exceptions when the lookup might fail:

PLSQL
DECLARE
  v_name employees.last_name%TYPE;
BEGIN
  SELECT last_name INTO v_name FROM employees WHERE employee_id = 99999;
  DBMS_OUTPUT.PUT_LINE(v_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No such employee');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('More than one match');
END;
/

This prints No such employee. To fetch many rows, use a cursor loop (chapter 6) or BULK COLLECT (next lesson), not SELECT INTO.

Stand-in: a scalar subquery gives one value in SQL, and returns NULL instead of raising an exception when there is no row:

SQL Stand-in for SELECT INTO ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

%TYPE: anchor a variable to a column

Hard-coding VARCHAR2(40) for a name invites bugs: when a DBA widens the column to 80, your variable still holds 40 characters and a VALUE_ERROR appears in production. %TYPE copies the data type of a column (or another variable) at compile time:

PLSQL
DECLARE
  v_last_name employees.last_name%TYPE;
  v_salary    employees.salary%TYPE;
  v_bonus     v_salary%TYPE := 0;       -- same type as another variable

If the column changes, the block is recompiled automatically on next use. %TYPE does not copy a NOT NULL constraint or default value.

%ROWTYPE and records

table%ROWTYPE declares a record with one field per column of the table. A record is a group of related variables that you pass and fill together:

PLSQL
DECLARE
  r_emp employees%ROWTYPE;
BEGIN
  SELECT * INTO r_emp FROM employees WHERE employee_id = 107;
  DBMS_OUTPUT.PUT_LINE(r_emp.last_name || ' / ' || r_emp.job_id || ' / ' || r_emp.salary);
  r_emp.salary := r_emp.salary * 1.1;
END;
/

Fields are read and written with dot notation. SELECT * into a %ROWTYPE record is one of the few places where * is good style: the record follows the table. You can also define your own record type for just the fields you need:

PLSQL
DECLARE
  TYPE t_person IS RECORD (
    name   VARCHAR2(80),
    salary NUMBER(9,2) := 0
  );
  r_person t_person;
BEGIN
  r_person.name   := 'Ada Lovelace';
  r_person.salary := 5200;
  DBMS_OUTPUT.PUT_LINE(r_person.name || ' ' || r_person.salary);
END;
/

This prints Ada Lovelace 5200. A cursor or query can be given a record too: cursor_name%ROWTYPE.

You can insert or update from a whole record, which saves listing every column:

PLSQL
INSERT INTO employees_archive VALUES r_emp;
UPDATE employees SET ROW = r_emp WHERE employee_id = r_emp.employee_id;

Two limits: VALUES r_emp requires the record to match the table's column order, and SET ROW = updates every column including the key, so use it only when you really mean that.

DML inside PL/SQL

INSERT, UPDATE, DELETE and MERGE work as in SQL, and they can use variables:

PLSQL
DECLARE
  v_dept   employees.department_id%TYPE := 10;
  v_raise  NUMBER := 0.05;
BEGIN
  UPDATE employees
  SET    salary = salary * (1 + v_raise)
  WHERE  department_id = v_dept;

  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' employees updated');
  COMMIT;
END;
/

The implicit cursor SQL holds information about the most recent DML statement or single-row SELECT INTO:

Attribute Meaning
SQL%ROWCOUNT Number of rows affected
SQL%FOUND TRUE if at least one row was affected
SQL%NOTFOUND TRUE if no row was affected

Read SQL%ROWCOUNT right after the statement, because the next SQL statement resets it. An UPDATE that matches no rows does not raise an exception; check SQL%NOTFOUND if that is a problem.

Remember that DML in a block is not saved until COMMIT. Wherever a block can fail halfway, decide whether you commit at the end, or leave transaction control to the caller; chapter 8 explains the trade-offs. Avoid putting COMMIT inside a procedure that other code calls inside its own transaction.

RETURNING ... INTO puts changed values into variables:

PLSQL
UPDATE employees SET salary = salary + 500
WHERE employee_id = 107
RETURNING salary INTO v_new_salary;

Stand-ins you can run

The row count has a SQL counterpart. Run the update, then count what changed:

SQL Stand-in for UPDATE and SQL%ROWCOUNT

Press Run (or Ctrl + Enter) to see the result here.

A record is a row. SELECT * into a %ROWTYPE record is a single-row query, and field access is a column reference:

SQL Stand-in for a %ROWTYPE record ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What happens when SELECT ... INTO finds no rows?

    SELECT INTO must return exactly one row. Zero rows raises NO_DATA_FOUND (ORA-01403), two or more raise TOO_MANY_ROWS.
  2. 2. Why use employees.salary%TYPE instead of NUMBER(9,2)?

    %TYPE anchors the declaration to the column, so a changed column definition does not leave a mismatched variable.
  3. 3. What does r employees%ROWTYPE declare?

    A %ROWTYPE variable is a record whose fields mirror the table's columns, accessed as r.column_name.
  4. 4. An UPDATE in a block matches zero rows. What happens?

    DML that affects no rows is not an error. Check SQL%ROWCOUNT or SQL%NOTFOUND.

Summary

  • SELECT ... INTO fills variables from exactly one row; zero rows raise NO_DATA_FOUND, several raise TOO_MANY_ROWS.
  • %TYPE and %ROWTYPE anchor variables to column and row types, so code survives schema changes.
  • Records group fields; whole records can be used in INSERT ... VALUES and UPDATE ... SET ROW.
  • DML works inside blocks; SQL%ROWCOUNT, SQL%FOUND and SQL%NOTFOUND describe the last statement.
  • RETURNING INTO captures changed values; COMMIT is not automatic.

Next: PL/SQL Collections and Bulk Processing