Scribd
Upload
35 views

PL SQL7

The PL/SQL SELECT INTO statement allows fetching a single row from a table into variables. It requires the number of columns selected to match the number of variables, and their data types to be compatible. It will raise exceptions if more than one row is returned or if no rows are returned. Examples demonstrate selecting into a single variable, a record, and multiple variables from one or more tables. Errors may occur if the number of columns and variables don't match or if data types are incompatible.

Uploaded by

Faiyaz Raza
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
35 views

PL SQL7

The PL/SQL SELECT INTO statement allows fetching a single row from a table into variables. It requires the number of columns selected to match the number of variables, and their data types to be compatible. It will raise exceptions if more than one row is returned or if no rows are returned. Examples demonstrate selecting into a single variable, a record, and multiple variables from one or more tables. Errors may occur if the number of columns and variables don't match or if data types are incompatible.

Uploaded by

Faiyaz Raza
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 3

PL/SQL SELECT INTO statement is the simplest and fastest way to fetch a single row

from a table into


variables. The following illustrates the syntax of the PL/SQL SELECT INTO
statement:

In this syntax, the number of columns in the variable_list must be the same as the
number of
variables (or the number of components of a record) in the select_list . In
addition, their
corresponding data type must be compatible.

Besides the WHERE clause, you can use other clauses in the SELECT statement such as
INNER JOIN ,
GROUP BY , HAVING , and UNION .

If the SELECT statement returns more than one row, Oracle will raise the
TOO_MANY_ROWS exception. If
the SELECT statement does not return any row, Oracle will raise the NO_DATA_FOUND
exception.

PL/SQL SELECT INTO examples

Let’s use the customers and contacts tables in the sample database for
demonstration.
SELECT
select_list
INTO
variable_list
FROM
table_name
WHERE
condition;

A) PL/SQL SELECT INTO – selecting one column example


The following example uses a SELECT INTO statement to get the name of a customer
based on the
customer id, which is the primary key of the customers table.

In this example:

First, declare a variable l_customer_name whose data type anchors to the name
columns of the
customers table. This variable will hold the customer name.
Second, use the SELECT INTO statement to select value from the name column and
assign it to
the l_customer_name variable.
Third, show the customer name using the dbms_output.put_line procedure.

Because the customers table has only one row with customer ID 100, the code block
displayed the
customer name.
DECLARE
l_customer_name customers.name%TYPE;
BEGIN
-- get name of the customer 100 and assign it to l_customer_name
SELECT name INTO l_customer_name
FROM customers
WHERE customer_id = 100;
-- show the customer name
dbms_output.put_line( v_customer_name );
END;

Verizon

If there were no such row, the code block would fail with an unhandled
NO_DATA_FOUND exception.

B) PL/SQL SELECT INTO – selecting a complete row example


The following example fetches the entire row from the customers table for a
specific customer ID:

Here is the output:

In this example:

First, declare a record based on the row of the customers table. This record will
hold the entire
row of the customers table.
Second, select the customer whose id is 100 into the r_customer record.
Third, show the customer’s name and website.

C) PL/SQL SELECT INTO – selecting data into multiple variables example


The following example fetches the names of customer and contact from the customers
and contacts
tables for a specific customer id.
DECLARE
r_customer customers%ROWTYPE;
BEGIN
-- get the information of the customer 100
SELECT * INTO r_customer
FROM customers
WHERE customer_id = 100;
-- show the customer info
dbms_output.put_line( r_customer.name || ', website: ' || r_customer.website );
END;

Verizon, website: http://www.verizon.com

DECLARE
l_customer_name customers.name%TYPE;

Oracle issued the following output:

In this example:

First, declare three variables l_customer_name , l_contact_first_name ,


l_contact_last_name to
hold the customer and contact’s name.
Second, use the SELECT INTO statement to fetch customer and contact names of the
customer id
100 from the customers and contacts tables into the corresponding variables
l_customer_name , l_contact_first_name , l_contact_last_name .
Third, display the customer and contact names.
l_contact_first_name contacts.first_name%TYPE;
l_contact_last_name contacts.last_name%TYPE;
BEGIN
-- get customer and contact names
SELECT
name,
first_name,
last_name
INTO
l_customer_name,
l_contact_first_name,
l_contact_last_name
FROM
customers
INNER JOIN contacts USING( customer_id )
WHERE
customer_id = 100;
-- show the information
dbms_output.put_line(
l_customer_name || ', Contact Person: ' ||
l_contact_first_name || ' ' || l_contact_last_name );
END;

Verizon, Contact Person: Elisha Lloyd 

PL/SQL SELECT INTO common errors

If the number of columns and expression in the SELECT clause is greater than the
number of variables
in the INTO clause, Oracle issues this error:

Oracle issues the following error if the number of columns and expression in the
SELECT clause is less
than the number of variables in the INTO clause:

If the number of variables and element in the select list are the same, but their
corresponding
datatypes are not compatible so that Oracle cannot implicitly convert from one type
to the other. It will
issue the following error:

Now, you should know how to use the PL/SQL SELECT INTO statement to fetch a single
row from a
table into variables.

You might also like