Lab 2.2 PLSQL in SqlPlus | Oracle PL[s]SQL by Example

Lab 2.2 PL/SQL in Sql*Plus

Lab Objectives

After this Lab, you will be able to:

Use Substitution Variables

Use the DBMS_OUTPUT.PUT_LINE statement

SQL*Plus is an interactive tool that allows you to type SQL or PL/SQL statements at the command prompt. These statements are then sent to the database. Once they are processed , the results are sent back from the database and displayed on the screen. However, there are some differences between entering SQL and PL/SQL statements.

Consider the following example of a SQL statement.

FOR EXAMPLE

 SELECT first_name, last_name   FROM student;

The semicolon terminates this SELECT statement. Therefore, as soon as you type the semicolon and hit the ENTER key, the result set is displayed to you.

Now, consider the example of the PL/SQL block used in the previous Lab.

FOR EXAMPLE

 DECLARE    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 = 123;    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 student with '          'student id 123'); END;  .   /

There are two additional lines at the end of the block containing "." and "/". The "." marks the end of the PL/SQL block and is optional. The "/" executes the PL/SQL block and is required.

When SQL*Plus reads a SQL statement, it knows that the semicolon marks the end of the statement. Therefore, the statement is complete and can be sent to the database. When SQL*Plus reads a PL/SQL block, a semicolon marks the end of the individual statement within the block. In other words, it is not a block terminator. Therefore, SQL*Plus needs to know when the block has ended. As you have seen in the preceding example, it can be done with a period and a forward slash.

Substitution Variables

We noted earlier that PL/SQL is not a stand-alone programming language. It only exists as a tool within the Oracle programming environment. As a result, it does not really have capabilities to accept input from a user . However, SQL*Plus allows a PL/SQL block to receive input information with the help of substitution variables. Substitution variables cannot be used to output values, because no memory is allocated for them. SQL*Plus will substitute a variable before the PL/SQL block is sent to the database. Substitution variables are usually prefixed by the ampersand (&) character or double ampersand (&&) character. Consider the following example.

FOR EXAMPLE

 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.

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. Consider the following example.

FOR EXAMPLE

 BEGIN    DBMS_OUTPUT.PUT_LINE ('Today is ''&sv_day');    DBMS_OUTPUT.PUT_LINE ('Tomorrow will be ''&sv_day'); END;

This example produces the following output:

  Enter value for sv_day: Monday   old   2:    DBMS_OUTPUT.PUT_LINE ('Today is ''&sv_day');   new   2:    DBMS_OUTPUT.PUT_LINE ('Today is ''Monday');   Enter value for sv_day: Tuesday   old   3:    DBMS_OUTPUT.PUT_LINE ('Tomorrow will be ''&sv_day');   new   3:    DBMS_OUTPUT.PUT_LINE ('Tomorrow will be ''Tuesday');   Today is Monday   Tomorrow will be Tuesday   PL/SQL procedure successfully completed.

When a substitution variable is used in the script, the output produced by the program contains the statements that show how the substitution was done. For example, consider the following lines of the output produced by the preceding example:

  old   2:    DBMS_OUTPUT.PUT_LINE ('Today is ''&sv_day');   new   2:    DBMS_OUTPUT.PUT_LINE ('Today is ''Monday');

If you do not want to see these lines displayed in the output produced by the script, use the SET command option before you run the script, as shown:

  SET VERIFY OFF;

Then the output appears as follows :

  Enter value for sv_day: Monday   Enter value for sv_day: Tuesday   Today is Monday   Tomorrow will be Tuesday   PL/SQL procedure successfully completed.

You have probably noticed that the substitution variable sv_day appears twice in this PL/SQL block. As a result, when this example is run, the user is asked twice to provide the value for the same variable. Now, consider an altered version of the example as follows (changes are shown in bold).

FOR EXAMPLE

 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 double ampersand in the first DBMS_OUTPUT.PUT_LINE statement. As a result, this version of the example produces different output.

  Enter value for sv_day: Monday   old   2:    DBMS_OUTPUT.PUT_LINE ('Today is ''&&sv_day');   new   2:    DBMS_OUTPUT.PUT_LINE ('Today is ''Monday');   old   3:    DBMS_OUTPUT.PUT_LINE ('Tomorrow will be ''&sv_day');   new   3:    DBMS_OUTPUT.PUT_LINE ('Tomorrow will be ''Monday');   Today is Monday   Tomorrow will be Monday   PL/SQL procedure successfully completed.

From the output shown, it is clear that the user is asked only once to provide the value for the substitution variable sv_day . As a result, 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) datatype, 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 will make your program less error prone. This is illustrated in the following code fragment.

FOR EXAMPLE

 v_course_no VARCHAR2(5) := '&sv_course_no';

As mentioned earlier, substitution variables are usually prefixed by the ampersand (&) character or double ampersand (&&) characters. These are default characters that denote substitution variables. There is a special SET command option available in SQL*Plus that 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

  SET DEFINE ON

  SET DEFINE OFF

The first set command option changes the prefix of the substitution variable from an ampersand to another character. However, it is important for you to note that this character cannot be alphanumeric or white space. The second (ON option) and third (OFF option) control whether SQL*Plus will look for substitution variables or not. In addition, the ON option changes the value of the character back to the ampersand.

DBMS_OUTPUT.PUT_LINE

You already have seen some examples of how the DBMS_OUTPUT.PUT_LINE statement can be used. This statement is used to display information on the screen. It is very helpful when you want to see how your PL/SQL block is executed. For example, you might want to see how variables change their values throughout the program, in order to debug it.

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.

DBMS_OUTPUT.PUT_LINE writes information to the buffer for storage. Once a program has been completed, the information from the buffer is displayed on the screen. The size of the buffer can be set between 2,000 and 1,000,000 bytes. Before you can see the output printed on the screen, one of the following statements must be entered before the PL/SQL block.

  SET SERVEROUTPUT ON;

  SET SERVEROUTPUT ON SIZE 5000;

The first SET statement enables the DBMS_OUTPUT.PUT_LINE statement, and the default value for the buffer size is 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 5,000 bytes.

Similarly, if you do not want information to be displayed on the screen by the DBMS_OUTPUT.PUT_LINE statement, the following SET command can be issued prior to the PL/SQL block.

  SET SERVEROUTPUT OFF;