SQL SELECT Statement in PL/SQL – Syntax & Example

The SQL SELECT Statement in PL/SQL is the most commonly used command for retrieving data from Oracle databases. Whether you want to query a single column or an entire row, the SELECT statement provides flexible and powerful ways to extract the required data.

Basic Syntax

SELECT select_list
[INTO variable_list]
FROM table_list
[WHERE condition]
[ORDER BY column_list];

SELECT: Specifies the columns or expressions to retrieve. You can also use * to fetch all columns.
INTO: Used only in PL/SQL. It stores the result of the query into one or more variables.
FROM: Indicates the table(s) or views to retrieve data from.
WHERE: Applies filtering conditions to limit the result set.
ORDER BY: Sorts the result set by one or more columns.

Understanding Components of SELECT Statement

`select_list`

Can include column names, literals, functions, or *.
Allows arithmetic operations like salary * 1.1.
You can use aliases (AS) for clarity.

`INTO variable_list`

Required in PL/SQL blocks.
The number and data types of variables must match the select list.
You can use %TYPE to anchor variables to table columns.

v_title BOOKS.TITLE%TYPE;

`FROM table_list`

You can query one or more tables, views, or subqueries (in-line views).
Joins and aliases are also valid here.

`WHERE condition`

Filters records based on conditions using operators like =, >, LIKE, BETWEEN.

Example: SELECT INTO in PL/SQL Block

SET SERVEROUTPUT ON
DECLARE
  v_title BOOKS.TITLE%TYPE;
BEGIN
  SELECT title
  INTO v_title
  FROM books
  WHERE isbn = '72230665';

  DBMS_OUTPUT.PUT_LINE(v_title);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

Output:

Oracle Database 10g PL/SQL Programming

This PL/SQL block demonstrates how to:

Retrieve a specific column (title) using a SELECT statement
Store it in a variable
Display it using DBMS_OUTPUT.PUT_LINE

Handling SELECT Errors in PL/SQL

If no row is returned:

ORA-01403: no data found

If more than one row is returned:

ORA-01422: exact fetch returns more than requested number of rows

Use predefined exceptions to handle these scenarios gracefully:

EXCEPTION
  WHEN NO_DATA_FOUND THEN ...
  WHEN TOO_MANY_ROWS THEN ...

Best Practices for SELECT in PL/SQL

Always use INTO when using SELECT inside PL/SQL.
Ensure the SELECT returns exactly one row when using SELECT INTO.
Anchor variable data types using %TYPE for better compatibility.
Use exception handling to manage unexpected results.

➡️ Learn more about Oracle SELECT Statement – SQL Reference

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.