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.
-- ch04_1a.sql SET SERVEROUTPUT ON DECLARE v_average_cost VARCHAR2(10); BEGIN SELECT TO_CHAR(AVG(cost), ',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), ',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.