Introduction to Pl/Sql

Chapter 11

CHAPTER 11

------

INTRODUCTION TO PL/SQL

In addition to the non-procedural Structured Query Language, Oracle provides the Procedural Language-Structured Query Language (PL/SQL), an Oracle extension to the standard SQL. PL/SQL combines procedural and object-oriented programming by incorporating all of the standard procedural language capabilities such as decision and iterative structures along with object-oriented features. The PL/SQL programming approach is very structured and tightly integrated with Oracle database software. For example, PL/SQL supports all SQL datatypes such as NUMBER and VARCHAR2.

PL/SQL enhances SQL. PL/SQL enables you to embed SQL's DML commands for selecting, inserting, updating, and deleting data rows within a PL/SQL program. It also enables the use of SQL's cursor and transaction control commands, functions, operators, and pseudo columns/pseudo tables. PL/SQL provides additional capabilities that SQL lacks:

• Secure code through encryption and by storing code on a server instead of a client computer.
• Handle exceptions that arise due to data entry errors or programming errors.
• Process record with iterative loop code that manipulates records one at a time.
• Work with variables, records, arrays, objects, and other common programming language constructs.

Objectives

This chapter provides a basic understanding of the basics of the PL/SQL language. Your learning objectives for this chapter are:

• Learn about the advantages of using PL/SQL.
• Learn basic PL/SQL terminology including the lexical units.
• Learn the common data types used in writing PL/SQL programs.
• Learn to write a PL/SQL program including the use of comments, declaration of variables, and how to write assignment statements.
• Use the SELECT INTO statement.
• Use substitution variables.
• Execute a PL/SQL program that generates output.

PL/SQL Advantages and Capabilities

Application Performance

One of the most important aspects of any programming application or set of applications that interface with databases is performance of the system. Performance can be measured using a number of factors, but the factor most often emphasized is system response time.

PL/SQL improves system response time by decreasing network traffic significantly. IN turn, application programs process data more efficiently. Computer programs designed around SQL require a call to the database each time an SQL statement executes. When a SQL statement is issued on a client computer, the request is made to the database on a server, and a result table is sent back to the client. As a result, a single SQL statement causes two trips on the network. If multiple SELECT statements are issued, the network traffic can markedly increase. For example, four SELECT statements cause eight network trips.

PL/SQL provides the capability to define a "block" of programming statements, and can transmit this block to an Oracle database as a unit of work. Whenmultiple SELECT statements are part of a PL/SQL block, there are still only two network trips. This improves system response time by decreasing the amount of network and performance overhead that is incurred with an approach where each SELECT statement processes individually. This reduces network traffic as illustrated in Figure 11.1.

Figure 11.1

Productivity, Portability, and Security

PL/SQL offers a full range of design and debugging features including exception handling, encapsulation of objects, and user definition of object-oriented data types. PL/SQL coding is the same for all Oracle tools. This makes it easy to transfer your skill set when working with various Oracle applications such as Oracle Forms.

PL/SQL applications will execute on any operating system that supports the use of the Oracle RDBMS. This portability makes it possible for the development of program libraries that can store reusable code blocks, sub procedures, functions, and packages.

PL/SQL stored procedures run on a server instead of a client computer. This means that the procedures can be secured from tampering by unscrupulous hackers by restricting access through the use of standard Oracle database security. Consider a typical business requirement to update a customer order. Instead of granting access to the customer order table, you can grant access to a procedure that has been coded to update the table.

Pre-Defined Packages

Oracle provides several predefined packages that define application programming interfaces (API) that can be called from a PL/SQL program. This listing of packages provides you with an idea of the type of external packages that can be called from a PL/SQL program.

• DBMS_ALERT – used for processing database triggers. Triggers, as you will learn later, are coding procedures that execute based on events within the database such as the update of a row in a table.
• DBMS_FILE – used to read and write text files.
• DBMS_HTTP – used for hypertext transfer protocol (HTTP) callouts. A server-side callout is a shell script or compiled executable located on an Oracle database that can be automatically executed based on an event that is registered with Oracle Notification Services.
• DBMS_OUTPUT – used to display output produced by PL/SQL programs.
• DBMS_PIPE – used for communication with Oracle Net software.

Object-Oriented Programming and Web Applications

PL/SQL supports the definition of object types that can implement an object-oriented data model. Defining your own object types can reduce development time for complex applications through the development of associated object "methods" that enable moving SQL code out of scripts and into methods.

PL/SQL also enables the development of Web applications and pages. You can learn to program applications to generate Web pages directly from an Oracle database. In this fashion, data from an Oracle database can be available with Web technology for both the Internet and for business intranets. PL/SQL programming techniques can be used with Oracle's PL/SQL Gateway and the PL/SQL Web Toolkit. The PL/SQL gateway software allows a Web browser to execute a PL/SQL procedure through an HTTP listener. The PL/SQL Web Toolkit is a set of pre-defined PL/SQL packages with a generic programming interface for using stored procedures.

PL/SQL BLOCK STRUCTURE

PL/SQL is a block-structured language. That is, the basic units (procedures, functions, and anonymous blocks) that make up a PL/SQL program are logical blocks, which can contain any number of nested sub-blocks. Typically, each logical block corresponds to a problem or sub problem to be solved.

A block is the most basic unit in PL/SQL. All PL/SQL programs are combined into blocks. These blocks can also be nested within the other. Usually, PL/SQL blocks combine statements that represent a single logical task. The block can contain a combination of PL/SQL statements and SQL statements as illustrated in Figure 11.2. It is very easy to move PL/SQL modules between server-side and client-side applications. When the PL/SQL engine is located on the server, the entire PL/SQL block is passed to the PL/SQL engine on the Oracle server. The block is then processed as illustrated in the figure.

Figure 11.2

PL/SQL blocks can be divided into two groups: named and anonymous. Named blocks are used when creating subroutines. Subroutines include procedures and functions. Anonymous PL/SQL blocks, as you have probably guessed, do not have names. As a result, they cannot be stored in the database and referenced later.

How a PL/SQL Program Executes

An anonymous block executes by first sending the block to the PL/SQL engine on the server where it is compiled. In contrast, a named PL/SQL block is compiled only at the time of its creation, or if it has been modified. The compilation process includes syntax checking, binding, and p-code generation. Syntax checking involves checking PL/SQL code for syntax or compilation errors. If errors exist, the programmer corrects them and when a clean compile is achieved, the compiler assigns a storage address to program variables that are used to hold data for Oracle. This process is called binding.

After binding, p-code is generated for the PL/SQL block. P-code is a list of instructions to the PL/SQL engine. For named PL/SQL blocks, p-code is stored in the database, and it is used the next time the program is executed. After the process of compilation completes, the status for a named PL/SQL block is set to VALID, and the status is also stored in the database. If the compilation process is not successful, the status for a named PL/SQL block is set to INVALID.

PL/SQL Block Sections

PL/SQL blocks contain three sections. These are the declaration, executable, and exception-handling sections. The executable section is the only mandatory section of a block. Both the declaration and exception handling sections are optional. A PL/SQL block has the following structure:

DECLARE

Declaration statements

BEGIN

Executable statements

EXCEPTION

Exception-handling statements

END;

Declaration Section

The declaration section is the first section of the PL/SQL block. It is used to define PL/SQL identifiers such as variables, constants, and cursors. You will learn about the various PL/SQL identifiers as you complete your study of this chapter.

ExecutableSection

The executable section is the next section of the PL/SQL block. This section is used to store the executable statements that allow you to manipulate the identifiers declared in the declaration section. This is where the bulk of the programming code will be stored.

Exception-Handling Section

The exception-handling section is the last section of the PL/SQL block. This section contains statements that are executed whenever a runtime error occurs within the block. Runtime errors occur while a program is running and cannot be detected by the PL/SQL compiler. When a runtime error occurs, program control is passed to the exception-handling section of the block. The runtime error is then evaluated, and a specific exception is raised or executed. PL/SQL Example 11.1 is a very simple PL/SQL program that illustrates the declaration and executable sections.

/* PL SQL Example 11.1

Program: ch11-1.sql */

DECLARE

num_age NUMBER(3) := 20; -- assign value to variable

BEGIN

num_age := 23;

DBMS_OUTPUT.PUT_LINE('My age is: ' || TO_CHAR(num_age));

END;

/

The declaration section begins with the keyword DECLARE. PL/SQL Example 11.1 has a declaration section that declares a single variable named num_age. This variable is declared to be of data type NUMBER, and stories defined to store up to a 3-digit numeric value with a default value of 20.

The executable section begins with the keyword BEGIN and the first assignment statement assigns a new value of 23 to the variable namednum_age. This is following by an output statement that displays output on the computer monitor. The program ends with a “/” symbol. Issuing a "slash" command causes execution of the program.

Creating and Executing a PL/SQL Program

There are a number of ways to create and execute a PL/SQL program. We will introduce you to three different options and you can use the one you like best. The three options are to use Oracle SQL*Plus software, use a third-party telnet program such as PuTTY, or to use the Oracle SQL Developer software.

Displaying Output

Regardless of the approach that you take to create and execute a PL/SQL program, many of the programs that you code will need to generate output to display on the computer monitor screen. Use the SET SERVEROUTPUT ON statement to cause the display of output to a computer monitor screen when you use SQL*Plus or PuTTY. Normally, you will execute the statement at the SQL> prompt prior to executing a PL/SQL program as shown in this example. Also, you only need to issue this command once for every SQL*PLUS session. Figure 11.3 illustrates issuing the statement within SQL*Plus.

SQL> SET SERVEROUTPUT ON;

Using Oracle SQL*Plus to Create/Execute PL/SQL Programs

The first option for creating and executing a PL/SQL program is to write the programming code by using a plain text editor such as Windows Notepad. You can then execute the program by logging on to and using the Oracle SQL*Plussoftware provided by Oracle for a Microsoft Windows client environment. In fact, the default text editor for SQL*Plus is Windows Notepad. You simply type the program to be executed using Notepad and save the program to a file. Normally a filename extension of .sql is used when naming a PL/SQL program as you did earlier when you learned to create SQL command files.

To execute the program, open an Oracle SQL*Plus command window as shown in Figure 11.3. You launch Oracle SQL*Plus by clicking the Windows Start button, Programs menu option, and then navigating through the operating system menu system to access the Oracle home directory folder and the Application Development subfolder. On launch, you must enter the information required to connect to your Oracle database. Your instructor will provide the database connection information to you.

Figure 11.3

Let us assume that the ch11-1.sql program is stored on your client computer and you plan to use SQL*Plus wish to execute the program. You have written the program by using Notepad. Simply copy/paste the lines of code into the SQL*Plus programming window for testing and execution by using the Edit-Paste menu option. The program is executed by issuing the "/" command.

You can also load a program file into SQL*Plus by using the File-Open menu option. Assume the file is stored on drive C: in a folder named Temp. Just click on the File menu and select the Open option. Use the Open File dialog window to browse to the program file, select it, and click the Open button.

Sometimes the File-Open menu option will generate an SP2-0423: Illegal GET command error message as shown here.

SQL>

SP2-0423: Illegal GET command

What actually happens behind the scenes when you use File-Open is SQL*Plus generates a GET command to retrieve the file's contents into SQL*Plus's internal command buffer. The GET command cannot process a retrieval of a file if there are any embedded blank spaces in either the file name or the directory path (folder names) where the file is located. Thus, if you store your PL/SQL files in your Windows My Documents folder or if you name the files with embedded blank spaces, File-Open will fail. An alternative is to issue a GET command at the SQL> prompt similar to the one shown here. Specify the entire directory path and file name inside double-quote marks.

SQL> GET "C:\Documents and Settings\bockd.AC\My Documents\CMIS563\Scripts\ch11-1.sql"

Using PuTTY to Create/Execute PL/SQL Programs

Another approach you can use to execute PL/SQL programs is to use a freeware package such as PuTTY. PuTTY includes a secure telnet type of packageto enable secure connections to an Oracle database across the Internet. It is a popular alternative for Oracle databases that are running on a LINUX operating system server. PuTTY is available for download from the Web for free.PuTTY creates a Telnet-like command window within which you can connect to an Oracle server. After establishing a database connection, you next issue the sqlplus command at the operating system prompt and you will enter the SQL*Plus environment. The SQL> prompt illustrated in Figure 11.4 tells you that Oracle is ready to process your PL/SQL code.

One of your first tasks is to execute a SET SERVEROUTPUT ON statement to enable the display of output in the PuTTY window as shown in Figure 11.4. Assuming the program is stored to a file named ch11-1.sql, you can execute the program by issuing either the @ch11-1.sql or START ch11-1.sql command at the SQL> promptas shown in Figure 11.4. Note that the "@" symbol is shorthand for the START command. Also note that if the filename extension is .sql, you do not need to specify the filename extension.

Figure 11.4

The file that stores the program must be on the current operating system directory folder or else you must specify the entire path to the file as part of the command to execute the program. Alternatively, you may copy/paste the PL/SQL program into PuTTY at the SQL> prompt. Issuing the "/" command at the end of the program as you did in PL/SQL Example 11.1 will cause the program to execute.

Using SQL Developer to Create/Execute PL/SQL Programs

A third approach to creating, executing, and testing PL/SQL programs requires you to download Oracle's SQL Developer software. This software is a free download from the Oracle web site. To download, you must create an Oracle account on the web site, but the accounts are also free.

Figure 11.5 illustrates Oracle SQL Developer software with a number of database connections available. In this instance, the programmer is using the connection named dbock@ORACLE2. In this connection, dbock is the user account and ORACLE2 is the name of the database service. If you previously created a PL/SQL program and stored it to a file, you can use the File-Open menu option to open the file and load the program into a SQL Worksheet window that is a tab labeled ch11-1.sql in Figure 11.5.

Do you remember the SET SERVEROUTPUT ON statement that you used with SQL*PLUS? It does not execute as a SQL statement in SQL Developer – the command is not recognized so SQL Developer will ignore it. You can display output to the Results window pane, but only if you select the DBMS Output tab identified in Figure 11.5 and click the toggle server button to cause the set serveroutput onstatement to execute.

Figure 11.5