What is the purpose of a SELECT statement in the PL/SQL block? Explain it with an example.

Question

What will be an ideal response?What will be an ideal response?

Answer 1

The SELECT statement retrieves data from Oracle tables. The syntax of SELECT is
different in PL/SQL because it is used to retrieve values from a row into a list of
variables or into a PL/SQL record. The general syntax is

SELECT ColumnNames
INTO VariableNames / RecordName
FROM TableName
WHERE condition;

Where ColumnNames must contain at least one column and may include arithmetic or
string expressions, single-row functions, and group functions. VariableNames contains a
list of local or host variables to hold values retrieved by the SELECT clause.  The
variables are either declared at SQL*Plus prompt, or declared locally under the
DECLARE section. All the features of SELECT in SQL are available with an added
mandatory INTO clause.
The INTO clause must contain one variable for each value retrieved from the table. The
order and data type of columns and variables must correspond. The SELECT…INTO
statement must return one and only one row. It is your responsibility to code a statement
that returns one row of data. If no rows are returned, the standard exception (error
condition) NO_DATA_FOUND occurs. If more than one row is retrieved, the
TOO_MANY_ROWS exception occurs. You will learn more about exceptions and
exception handling in the next chapter.
For example,
SELECT StudentId, Last, First, MajorId
INTO v_studid, v_last, v_first, v_major
FROM student
WHERE StudentId = ‘00103’;