- Lab 1.1: PL/SQL Architecture
- Lab 1.2: PL/SQL Development Environment
- Lab 1.3: PL/SQL: The Basics
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
Lab 1.3: PL/SQL: The Basics
We noted earlier that PL/SQL is not a stand-alone programming language; rather, it exists only as a tool within the Oracle environment. As a result, it does not really have any capabilities to accept input from a user. The lack of this ability is compensated with the special feature of the SQL Developer and SQL*Plus tools called a substitution variable.
Similarly, it is often helpful to provide the user with some pertinent information after the execution of a PL/SQL block, and this is accomplished with the help of the DBMS_OUTPUT.PUT_LINE statement. Note that unlike the substitution variable, this statement is part of the PL/SQL language.
DBMS_OUTPUT.PUT_LINE Statement
In the previous section of this chapter, you saw how the DBMS_OUTPUT.PUT_LINE statement may be used in a script to display information on the screen. The DBMS_OUTPUT.PUT_LINE is a call to the procedure PUT_LINE. This procedure is a part of the DBMS_OUTPUT package that is owned by the Oracle user SYS.
The DBMS_OUTPUT.PUT_LINE statement writes information to the buffer for storage. After a program has completed, the information from the buffer is displayed on the screen. The size of the buffer can be set between 2000 and 1 million bytes.
To see the results of the DBMS_OUTPUT.PUT_LINE statement on the screen, you need to enable it. In SQL Developer, you do so by selecting the View menu option and then choosing the Dbms Output option, as shown in Figure 1.12.
)
Figure 1.12 Enabling DBMS_OUTPUT in SQL Developer: Step 1
After the Dbms Output window appears in SQL Developer, you must click the plus button, as shown in Figure 1.13.
)
Figure 1.13 Enabling DBMS_OUTPUT in SQL Developer: Step 2
After you click the plus button, you will be prompted with the name of the connection for which you want to enable the statement. You need to select ORCLPDB_STUDENT and click OK. The result of this operation is shown in Figure 1.14.
)
Figure 1.14 Enabling DBMS_OUTPUT in SQL Developer: Step 3
To enable the DBMS_OUTPUT statement in SQL*Plus, you enter one of the following statements before the PL/SQL block:
SET SERVEROUTPUT ON;
or
SET SERVEROUTPUT ON SIZE 5000;
The first SET statement enables the DBMS_OUTPUT.PUT_LINE statement, with the default value for the buffer size being used. The second SET statement not only enables the DBMS_OUTPUT.PUT_LINE statement but also changes the buffer size from its default value to 5000 bytes.
Similarly, if you do not want information to be displayed on the screen by the DBMS_OUTPUT.PUT_LINE statement, you can issue the following SET command prior to the PL/SQL block:
SET SERVEROUTPUT OFF;
Substitution Variable Feature
Substitution variables are a special type of variable that enables PL/SQL to accept input from a user at a runtime. These variables cannot be used to output values, however, because no memory is allocated for them. Substitution variables are replaced with the values provided by the user before the PL/SQL block is sent to the database. The variable names are usually prefixed by the ampersand (&) or double ampersand (&&) character.
Consider the following example.
For Example ch01_1b.sql
DECLARE
v_student_id NUMBER := &sv_student_id;
v_first_name VARCHAR2(35);
v_last_name VARCHAR2(35);
BEGIN
SELECT first_name, last_name
INTO v_first_name, v_last_name
FROM student
WHERE student_id = v_student_id;
DBMS_OUTPUT.PUT_LINE ('Student name: '||v_first_name||' '||
v_last_name);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE ('There is no such student');
END;
When this example is executed, the user is asked to provide a value for the student ID. The student’s name is then retrieved from the STUDENT table if there is a record with the given student ID. If there is no record with the given student ID, the message from the exception-handling section is displayed on the screen.
In SQL Developer, the substitution variable feature operates as shown in Figure 1.15.
)
Figure 1.15 Using a Substitution Variable in SQL Developer
After the value for the substitution variable is provided, the results of the execution are displayed in the Script Output window, as shown in Figure 1.16.
)
Figure 1.16 Using a Substitution Variable in SQL Developer: Script Output Window
In Figure 1.16, the substitution of the variable is shown in the Script Output window, and the result of the execution is shown in the Dbms Output window.
In SQL*Plus, the substitution variable feature operates as shown in Figure 1.17. Note that SQL*Plus does not list the complete PL/SQL block in its results, but rather displays the substitution operation only.
)
Figure 1.17 Using a Substitution Variable in SQL*Plus
The preceding example uses a single ampersand for the substitution variable. When a single ampersand is used throughout the PL/SQL block, the user is asked to provide a value for each occurrence of the substitution variable.
For Example ch01_2a.sql
BEGIN
DBMS_OUTPUT.PUT_LINE ('Today is '||'&sv_day');
DBMS_OUTPUT.PUT_LINE ('Tomorrow will be '||'&sv_day');
END;
When executing this example in either SQL Developer or SQL*Plus, you are prompted twice to provide the value for the substitution variable. This example produces the following output:
Today is Monday Tomorrow will be Tuesday
As demonstrated earlier, when the same substitution variable is used with a single ampersand, the user is prompted to provide a value for each occurrence of this variable in the script. To avoid this task, you can prefix the first occurrence of the substitution variable by the double ampersand (&&) character, as highlighted in bold in the following example.
For Example ch01_2b.sql
BEGIN
DBMS_OUTPUT.PUT_LINE ('Today is '||'&&sv_day');
DBMS_OUTPUT.PUT_LINE ('Tomorrow will be '||'&sv_day');
END;
In this example, the substitution variable sv_day is prefixed by a double ampersand in the first DBMS_OUTPUT.PUT_LINE statement. As a result, this version of the example produces different output:
Today is Monday Tomorrow will be Monday
From the output shown, it is clear that the user is asked only once to provide the value for the substitution variable sv_day. In turn, both DBMS_OUTPUT.PUT_LINE statements use the value of Monday entered by the user.
When a substitution variable is assigned to the string (text) data type, it is a good practice to enclose it with single quotes. You cannot always guarantee that a user will provide text information in single quotes. This practice, which will make your program less error prone, is illustrated in the following code fragment.
For Example
DECLARE v_course_no VARCHAR2(5) := '&sv_course_no';
As mentioned earlier, substitution variables are usually prefixed by the ampersand (&) or double ampersand (&&) characters; these are the default characters that denote substitution variables. A special SET command option available in SQL Developer and SQL*Plus also allows you to change the default character to any other character or disable the substitution variable feature. This SET command has the following syntax:
SET DEFINE character
or
SET DEFINE ON
or
SET DEFINE OFF
The first SET command option changes the prefix of the substitution variable from an ampersand to another character. Note, however, that this character cannot be alphanumeric or whitespace. The second (ON option) and third (OFF option) control whether SQL*Plus will look for substitution variables. In addition, the ON option changes the value of the character back to the ampersand.