Lab 2.2 PLSQL in SQLPlus

Team-Fly    

Oracle® PL/SQL® Interactive Workbook, Second Edition
By Benjamin Rosenzweig, Elena Silvestrova
Table of Contents
Chapter 2.  PL/SQL Concepts


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.

graphics/intfig03.gif 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.

graphics/intfig03.gif 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.

graphics/intfig03.gif 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.

graphics/intfig03.gif 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. 

graphics/intfig07.gif

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

graphics/intfig03.gif 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.

graphics/intfig03.gif 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 

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. 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; 

or

 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; 

Lab 2.2 Exercises

2.2.1 Use Substitution Variables

In this exercise, you will calculate the square of a number. The value of the number will be provided with the help of a substitution variable. Then the result will be displayed on the screen.

Create the following PL/SQL script:

 -- ch02_1a.sql, version 1.0  SET SERVEROUTPUT ON  DECLARE     v_num NUMBER := &sv_num;     v_result NUMBER;  BEGIN     v_result := POWER(v_num, 2);     DBMS_OUTPUT.PUT_LINE ('The value of v_result is: '||        v_result);  END; 

Execute the script, and then answer the following questions:

a)

If the value of v_num is equal to 10, what output is printed on the screen?

b)

What is the purpose of using a substitution variable?

c)

Why is it considered a good practice to enclose substitution variables with single quotes for string datatypes?

2.2.2 Use the DBMS_OUTPUT.PUT_LINE Statement

In this exercise, you will determine the day of the week based on today's date. You will then display the results on the screen.

Create the following PL/SQL script:

 -- ch02_2a.sql, version 1.0  SET SERVEROUTPUT ON  DECLARE     v_day VARCHAR2(20);  BEGIN     v_day := TO_CHAR(SYSDATE, 'Day');     DBMS_OUTPUT.PUT_LINE ('Today is '||v_day);  END; 

Execute the script, and then answer the following questions:

a)

What was printed on the screen?

b)

What will be printed on the screen if the statement SET SERVEROUTPUT OFF is issued? Why?

c)

How would you change the script to display the time of the day as well?

Lab 2.2 Exercise Answers

This section gives you some suggested answers to the questions in Lab 2.2, with discussion related to how those answers resulted. The most important thing to realize is whether your answer works. You should figure out the implications of the answers here and what the effects are from any different answers you may come up with.

2.2.1 Answers

a)

If the value of v_num is equal to 10, what output is printed on the screen?

A1:

Answer: Your output should look like the following:

 Enter value for v_num: 10  old   2:    v_num    NUMBER := &sv_num;  new   2:    v_num    NUMBER := 10;  The value of v_result is: 100  PL/SQL procedure successfully completed. 

The first line of the output asks you to provide a value for the substitution variable sv_num. Then the actual substitution is shown to you in lines 2 and 3. In the second line, you can see the original statement from the PL/SQL block. In the third line, you can see the same statement with the substitution value. The next line shows the output produced by the DBMS_OUTPUT.PUT_LINE statement. Finally, the last line informs you that your PL/SQL block was executed successfully.

b)

What is the purpose of using a substitution variable?

A2:

Answer: A substitution variable allows the PL/SQL block to accept information provided by the user at the time of execution. Substitution variables are used for input purposes only. They cannot be used to output values for a user.

c)

Why is it considered a good practice to enclose substitution variables with single quotes for string datatypes?

A3:

Answer: A program cannot depend wholly on a user to provide text information in single quotes. Enclosing a substitution variable with single quotes allows a program to be less error-prone.

2.2.2 Answers

a)

What was printed on the screen?

A1:

Answer: Your output should look like the following:

 Today is Friday  PL/SQL procedure successfully completed. 

In this example, SQL*Plus does not ask you to enter the value of the v_day variable because no substitution variable is used. The value of v_day is computed with the help of TO_CHAR and SYSDATE functions. Then it is displayed on the screen with the help of the DBMS_OUTPUT.PUT_LINE statement.

b)

What will be printed on the screen if the statement SET SERVEROUTPUT OFF is issued? Why?

A2:

Answer: If the statement SET SERVEROUTPUT OFF is issued prior to the execution of the PL/SQL block, no output will be printed on the screen. The output will look like following:

 PL/SQL procedure successfully completed. 

It is important to note that when substitution variables are used, the user is prompted to enter the value for the variable regardless of the SERVEROUTPUT setting. The prompt for the user is provided by SQL*Plus and does not depend on the option chosen for the SERVEROUTPUT.

c)

How would you change the script to display the time of the day as well?

A3:

Answer: Your script should look similar to this script. Changes are shown in bold letters.

 -- ch02_2b.sql, version 2.0  SET SERVEROUTPUT ON  DECLARE     v_day VARCHAR2(20);  BEGIN     v_day := TO_CHAR(SYSDATE, 'Day, HH24:MI');     DBMS_OUTPUT.PUT_LINE ('Today is '|| v_day);  END; 

The statement shown in bold has been changed in order to display time of the day as well. The output produced by this PL/SQL block is as follows:

 Today is Friday   , 23:09  PL/SQL procedure successfully completed. 

Lab 2.2 Self-Review Questions

In order to test your progress, you should be able to answer the following questions.

Answers appear in Appendix A, Section 2.2.

1)

SQL*Plus understands a semicolon as a terminating symbol of a PL/SQL block.

  1. _____ True

  2. _____ False

2)

Substitution variables are used to

  1. _____ read input information provided by a user.

  2. _____ provide a user with output information.

  3. _____ both a and b.

3)

PUT_LINE is one of the procedures from the DBMS_OUTPUT package.

  1. _____ True

  2. _____ False

4)

DBMS_OUTPUT.PUT_LINE writes information to the buffer for storage before it is displayed on the screen.

  1. _____ True

  2. _____ False

5)

The SET command SET SERVEROUTPUT ON SIZE 8000 is used to

  1. _____ enable the DBMS_OUTPUT.PUT_LINE statement only.

  2. _____ change the buffer size only.

  3. _____ enable the DBMS_OUTPUT.PUT_LINE statement and change the buffer size.


    Team-Fly    
    Top
     



    Oracle PL. SQL Interactive Workbook
    Oracle PL/SQL Interactive Workbook (2nd Edition)
    ISBN: 0130473200
    EAN: 2147483647
    Year: 2002
    Pages: 146

    Similar book on Amazon

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net