Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources] نسخه متنی

Lab 4.1 Making Use of DML in PL/SQL

Variables Initialization with SELECT INTO

In PL/SQL, there are two main methods of giving value to variables in a PL/SQL block. The first one, which you learned in Chapter 2, "PL/SQL Concepts," is initialization with the ":=" syntax. In this lab we will learn how to initialize a variable with a select statement by making use of SELECT INTO syntax.

A variable that has been declared in the declaration section of the PL/SQL block can later be given a value with a SELECT statement. The correct syntax is as follows:

SELECT item_name
INTO variable_name
FROM table_name;

It is important to note that any single row function can be performed on the item to give the variable a calculated value.

FOR EXAMPLE

-- ch04_1a.sql
SET SERVEROUTPUT ON
DECLARE
v_average_cost VARCHAR2(10);
BEGIN
SELECT TO_CHAR(AVG(cost), '$9,999.99')
INTO v_average_cost
FROM course;
DBMS_OUTPUT.PUT_LINE('The average cost of a '||
'course in the CTA program is '||
v_average_cost);
END;

In this example, a variable is given the value of the average cost of a course in the course table. First, the variable must be declared in the declaration section of the PL/SQL block. In this example, the variable is given the datatype of VARCHAR2(10) because of the functions used on the data. The same select statement that would produce this outcome in SQL*Plus would be

SELECT TO_CHAR(AVG(cost), '$9,999.99')
FROM course;

The TO_CHAR function is used to format the cost; in doing this, the number datatype is converted to a character datatype. Once the variable has a value, it can be displayed to the screen in SQL*Plus using the PUT_LINE procedure of the DBMS_OUTPUT package.