Lecture Seventeen

PL/SQL

Learning Objectives

· To illustrate the features and benefits of PL/SQL.

· To introduce the PL/SQL program constructs.

Introduction

• PL/SQL is a procedural language extension to SQL, which is the standard data access language for relational databases.
• PL/SQL is a powerful transaction processing language. It allows the data manipulation and query statements of SQL to be included in block-structured and procedural units of code.
• It combines SQL with the design capabilities of procedural programming languages such as C and FORTRAN.
• PL/SQL offers modern software engineering features such as data encapsulation and exception handling.
• In object-oriented programming, encapsulation is the inclusion in a program object of all the resources needed for the object to function.
• PL/SQL offers features that enable information hiding and object orientation.
• PL/SQL uses SQL statements to manipulate Oracle data and flow-of-control statements to process the data.
• PL/SQL fully supports SQL datatypes. This reduces the need to convert data passed between your applications and the database.

Benefits of PL/SQL

• PL/SQL provides several advantages that are available in the procedural programming languages.

• PL/SQL is a block-structured language. This means that the basic units that make up a PL/SQL program, such as procedures, functions, and anonymous blocks, are logical blocks, which can contain any number of nested subblocks.
• One of the benefits of using PL/SQL is that you can modularize program development.
• This means that you can group logically related statements within blocks.
• Modularity lets you break an application down into manageable, well-defined logic modules. PL/SQL meets this need with program units.
• The modular structure of PL/SQL programs enables you to break down a complex problem into a set of manageable, well-defined, logical modules.
• Besides blocks and subprograms, PL/SQL provides packages, which allow you to group related program items into larger units.
• Modularized program development enables you to place the reusable PL/SQL code in libraries.
• PL/SQL code in libraries can be shared among Oracle Developer applications.
• You can also store this code in an Oracle server to make it accessible to any application that can interact with an Oracle database.
• Typically, each logical block corresponds to a problem or subproblem to be solved. PL/SQL supports the divide-and-conquer approach to problem solving called stepwise refinement.
• Applications written in PL/SQL are portable to any operating system and platform on which Oracle runs.
• Therefore PL/SQL programs can run anywhere that Oracle can run. You can also write portable program libraries, which can be reused in different environments.
• Another benefit of PL/SQL is that you can declare identifiers such as variables and constants. You can use these identifiers in SQL and procedural statements.
• An additional benefit of PL/SQL is that it enables you to declare variables of various datatypes.
• PL/SQL enables you to declare variables dynamically, based on the data structure of tables and columns in the database.
• Control structures are an important PL/SQL extension to SQL. Not only does PL/SQL let you manipulate Oracle data, it also lets you process the data using conditional, iterative, and sequential flow-of-control statements. Collectively, these statements can handle any situation.
• The procedural language control provided by PL/SQL enables you to process the rows returned by a multiple-row query individually with an explicit cursor.
• PL/SQL adds functionality to nonprocedural tools such as Oracle Forms and Oracle Reports.
• With PL/SQL in these tools, you can use familiar procedural constructs to build applications. Moreover, PL/SQL is the same in all environments.
• For example, scripts written with one tool can be used by other tools.
• A good programming language provides capabilities for handling errors and recovering from them if possible.
• PL/SQL implements error handling via exceptions and exception handlers. In PL/SQL, a warning or error condition is called an exception.
• You can declare user-defined error conditions and process them with an exception handling routine.
• PL/SQL plays a central role to both the Oracle server and Oracle development tools. This is done through stored procedures, stored functions, database triggers, and Oracle Developer component triggers.
• PL/SQL also improves performance by adding procedural processing power to Oracle tools, such as Oracle Forms and Reports.
• Using PL/SQL, a tool can do any computation quickly and efficiently without calling on the Oracle server. This saves time and reduces network traffic.
• The PL/SQL and SQL languages are tightly integrated. PL/SQL supports all of the SQL datatypes and the non value NULL. This allows you to manipulate Oracle data easily and efficiently. It also helps you to write high-performance code.
• Without PL/SQL, Oracle processes SQL statements one at a time. Each SQL statement results in another call to Oracle and higher performance overhead.
• With PL/SQL, an entire block of statements can be sent to Oracle at one time. This can drastically reduce the network and performance overhead.

PL/SQL Engine

• PL/SQL is a technology that is used by the Oracle server and by certain Oracle tools. Blocks of PL/SQL are passed to and processed by a PL/SQL engine, which may reside in the tool or in the Oracle server.
• Many Oracle tools, including Oracle Developer, have their own PL/SQL engine. This PL/SQL engine is independent of the engine present in the Oracle server.
• The engine that is used depends on the source from where the PL/SQL block of code is invoked.
• When you submit PL/SQL blocks from a Pro* program, user-exit, SQL*PLUS, or Server Manager, the PL/SQL engine in the Oracle server processes them.
• It separates out the SQL statements and sends them individually to the SQL statement executor.
• The PL/SQL engine executes procedural statements but sends SQL statements to the SQL Statement Executor in the Oracle server.
• By filtering out the SQL statements, only a single transfer is required from the application to the Oracle Server. This reduces network traffic and greatly enhances performance, especially in a client/server network.
• The PL/SQL engine in Oracle tools executes only the procedural statements in the PL/SQL block at the application site. It uses the Procedural Statement Executor in the local PL/SQL engine for this purpose.

PL/SQL block

• PL/SQL is a block-structured language. A set of logically related statements forms a block.
• A basic block of PL/SQL code can contain three sections: Declarative, Executable, and Exception Handling.
• These sections are used according to the requirements of an application being developed.
• The Declarative section is the first part of a PL/SQL block. It is used for declaring items, such as variables, constants, cursors, and definitions of error situations called exceptions. Declaring an item allocates storage space for the item.
• You use the DECLARE keyword to signal the start of the Declarative section in a PL/SQL block. The Declarative section is optional.
• The second section of a PL/SQL block is the Executable section. It contains SQL and PL/SQL statements for querying and for manipulating data.
• You use the BEGIN keyword to signal the start of the Executable section in a PL/SQL block.
• The END keyword signifies the end of the Executable section.
• There needs to be at least one executable statement within a set of BEGIN and END statements. As part of the syntax, a semicolon is placed after the END keyword.
• The final section in the PL/SQL block is the Exception Handling section. It specifies the actions to be performed when errors and abnormal conditions arise in the Executable section.
• In a PL/SQL block, conditions that lead to errors are known as exceptions. You can take care of exceptions by defining the handlers for them.
• Exceptions are placed in a separate section of a PL/SQL block called the Exception Handling section. The Exception Handling section is optional and is embedded in the Executable section of a block and is placed at the end of the Executable section.

PL/SQL Program Constructs

• You can build an application to contain various program constructs. Program constructs are PL/SQL blocks or modules that execute different functions, depending on the execution environment.
• A program construct can be a single PL/SQL block, a nested block, or a group of blocks.
• These blocks can be either entirely separate or nested one within another. By using the basic PL/SQL block, you can create a variety of program constructs.

Anonymous Block

• An anonymous block is an unnamed PL/SQL block embedded in an application or issued interactively.

• An anonymous block is declared at the point in an application where it is to be executed. It is then passed to the PL/SQL engine for execution at run time.
• You can embed an anonymous block in a precompiler program and in SQL*Plus or Server Manager.

Stored Procedure Or Function

• Another program construct is the stored procedure or function. A stored procedure or function is a PL/SQL program unit that has a name and can take parameters.
• A stored procedure or function is stored in the data dictionary and can be called by many users.
• The term stored procedure is sometimes used generically to cover both stored procedures and stored functions.
• The only difference between procedures and functions is that functions always return a single value to the caller, while procedures do not return a value to the caller.

Application Procedure Or Function

• The application procedure or function is a named PL/SQL block stored in an Oracle Developer application or shared library.
• This construct can use input and output parameters, and it can be invoked repeatedly.
• Procedures and functions declared as part of a Developer application are distinct from those stored in the database, although their general structure is the same.
• Stored subprograms are database objects and are stored in the data dictionary. They can be accessed by any number of applications, including Developer applications.
• Some of the application procedures or functions are available in the components of Oracle Developer, such as Forms.

Package

• A is an encapsulated collection of related program objects, such as procedures, functions, variables, constants, cursors, and exceptions, stored together in the database or in Oracle Tools like Oracle Developer components.

Triggers

• Triggers are procedures that are stored in the database and are implicitly run, or fired, when a DML event occurs in the database.
• You can define triggers to fire before or after an INSERT, UPDATE, or DELETE occurs on a table or view.

Application Trigger

• The application trigger is associated with an application event and is executed automatically when a specified event occurs.

Syntax Rules

• Before you use PL/SQL blocks to create an application, it is essential to understand the general rules of syntax that apply to them. This knowledge enables you to write error-free code and to simplify the compilation and debugging of code.
• Because PL/SQL is an extension of SQL, the general syntax rules that are applicable to SQL are also applicable to the PL/SQL language.
• Identifiers are used to name PL/SQL program items and units, which include constants, variables, exceptions, cursors, cursor variables, subprograms, and packages.
• An identifier name can have a maximum of 30 characters. An identifier name begins with an alphabetical character.
• Reserved words cannot be used as identifier names unless they are enclosed in double quotation marks.
• Reserved words are written in uppercase letters to promote readability.
• The identifier name needs to be different from the column names in a table used in the block.
• If an identifier name in a SQL statement is the same as a column name, then the engine interprets it as a reference to the column and not the identifier.
• You enclose the characters and date literals in a PL/SQL statement in single quotation marks. Character literals include all the printable characters in the PL/SQL character set: letters, numerals, spaces, and special symbols.
• Numeric literals in a PL/SQL block are represented by either a simple value such as -32.5 or a scientific notation such as 2E5. The 2E5 means 2*(10 to the power of 5) = 200000.
• You use two hyphens (--) to indicate a single line comment. Comments are strictly informational and do not enforce any condition or behavioral logic, or data.
• Multiple line comments, if incorporated, are enclosed within the symbols /* and */. You comment the code to document each phase and to assist with debugging.
• The lexical units, such as identifiers and literals, can be separated by one or more spaces. You cannot embed spaces in lexical units except for string literals and comments.
• Statements can be split across lines, but keywords cannot be split.
• A PL/SQL block is terminated by a slash on a line by itself.

Summary

• PL/SQL is a programming environment that is native to the Oracle database.
• It features seamless integration with other database objects in Oracle and with SQL.
• There are three parts to a PL/SQL program: the declaration area, the execution area, and the exception handler.
• There are two categories of PL/SQL blocks: named and anonymous blocks. Named blocks include procedures, functions, packages, and triggers.

Review Questions

1. What are the benefits of accessing the database with PL/SQL?

2. What are the advantages of using pl/sql to access the database over using other programming languages for database access?

3. Identify the three parts of a PL/SQL block: declaration, execution, and exception handler.

4. What are the syntactic and semantic differences between named and anonymous blocks?

Answer:

4. The most important difference to remember is that named blocks are stored in Oracle as compiled code and can be referenced with a procedure or function call either from other pl/sql blocks or from the SQL*Plus command line. Anonymous blocks must be parsed and executed every time they are run, and they are usually stored in flat files called from SQL*Plus.

Practice Test

1. You develop a PL/SQL named program and compile it. Your PL/SQL program is now stored in which of the following areas?

A. In an executable program B. In a flat file C. In the Oracle database D. In SQL*Plus

2. You are using PL/SQL to support code centralization in your organization. Which of the following choices does not identify a feature offered by PL/SQL's centralization?

A. More manageable upgrade process

B. Program that takes advantage of client PC power

C. Single copy of program available for all users

D. Native communication with the database

3. You want to use the feature in PL/SQL that allows different versions of the same procedure to accept information of different datatypes. Which of the following terms best describes this feature?

A. Encapsulation B. Packages C. Modularization D. Overloading

4. Use the following code block to answer this question:

SQL> create or replace package my_pkg is

2 /*********/

3 function f_no_op

4 ( myvar in number)

5 return number;

6 /********/

7 procedure no_op

8 ( myvar in number) ;

9 end;

10 /

Package created.

Which of the following choices identifies the type of code block shown here?

A. Package specification

B. Package body

C. Procedure specification

D. Procedure body

5. You are creating a function in PL/SQL. Which two of the following components must appear in every function in PL/SQL (without them, Oracle returns an error)?

A. Declared variables

B. A return statement

C. A begin-end section

D. An exception section

6. You are developing PL/SQL programs. Which of the following choices best describes a possible definition for a trigger?

A. A code block containing procedure and function specifications that has an associated body containing the actual code

B. A code block containing procedure and function bodies that has an associated specification containing the procedure and function definitions

C. A code block associated with a table that Oracle executes whenever a specified event occurs

D. An unnamed code block parsed and executed at the same time

Answer

1.C. 2. B. 3-D. 4. A. 5. B and C. 6. C.

Assignment

Required Readings

Textbook:   

                  Chapter 7 “PL/SQL Basics"  

Chip Dawes and Biju Thomas 
Oracle DBA and PL/SQL Study Guide 
Sybex. 
ISBN 0-7821-2682-0.