Lab 4.1 Making Use of DML in PLSQL

Lab 4.1 Making Use of DML in PL/SQL

Lab Objectives

After this Lab, you will be able to:

Use the SELECT INTO Syntax for Variable Initialization

Use DML in PL/SQL Block

Make Use of a Sequence in a PL/SQL Block

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), ',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.