Oview of PL/SQL

Main Features of PL/SQL

PL/SQL combines the data-manipulating power of SQL with the processing power of procedural languages.

 PL/SQL lets you declare constants and variables, control program flow, define subprograms, and trap runtime errors.

1.Error Handling

PL/SQL makes it easy to detect and handle errors. When an error occurs, PL/SQL raises an exception. Normal execution stops and control transfers to the exception-handling part of the PL/SQL block.

2.Blocks

The basic unit of a PL/SQL source program is the block, which groups related declarations and statements.

A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. Only the executable part is required. A block can have a label.

Example 1-1 PL/SQL Block Structure

<< label >> (optional)
DECLARE    -- Declarative part (optional)
  -- Declarations of local types, variables, & subprograms

BEGIN      -- Executable part (required)
  -- Statements (which can use items declared in declarative part)

[EXCEPTION -- Exception-handling part (optional)
  -- Exception handlers for exceptions (errors) raised in executable part]
END;

Blocks can be nested: Because a block is an executable statement, it can appear in another block wherever an executable statement is allowed.

You can submit a block to an interactive tool (such as SQL*Plus or Enterprise Manager) or embed it in an Oracle Precompiler or OCI program. The interactive tool or program runs the block one time. The block is not stored in the database, and for that reason, it is called an anonymous block (even if it has a label).



 

An anonymous block is compiled each time it is loaded into memory, and its compilation has three stages:

1. Syntax checking: PL/SQL syntax is checked, and a parse tree is generated.

2. Semantic checking: Type checking and further processing on the parse tree.

3. Code generation

3. Variables and Constants

L/SQL lets you declare variables and constants, and then use them wherever you can use an expression. As the program runs, the values of variables can change, but the values of constants cannot.

4. Subprograms

A PL/SQL subprogram is a named PL/SQL block that can be invoked repeatedly. If the subprogram has parameters, their values can differ for each invocation. PL/SQL has two types of subprograms, procedures and functions. A function returns a result. 

5.Packages

A package is a schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions. A package is compiled and stored in the database, where many applications can share its contents. You can think of a package as an application.

6.Triggers

A trigger is a named PL/SQL unit that is stored in the database and run in response to an event that occurs in the database. You can specify the event, whether the trigger fires before or after the event, and whether the trigger runs for each event or for each row affected by the event. 

7.Input and Output

Most PL/SQL input and output (I/O) is done with SQL statements that store data in database tables or query those tables.

8.Data Abstraction

• Cursors

A cursor is a pointer to a private SQL area that stores information about processing a specific SQL statement or PL/SQL SELECT INTO statement.
You can use the cursor to retrieve the rows of the result set one at a time.
You can use cursor attributes to get information about the state of the cursor
          —for example, how many rows the statement has affected so far.

• Composite Variables

A composite variable has internal components, which you can access individually. You can pass entire composite variables to subprograms as parameters. PL/SQL has two kinds of composite variables, collections and records.

In a collection, the internal components are always of the same data type, and are called elements. You access each element by its unique index. Lists and arrays are classic examples of collections.

In a record, the internal components can be of different data types, and are called fields. You access each field by its name. A record variable can hold a table row, or some columns from a table row.

• %ROWTYPE Attribute

The %ROWTYPE attribute lets you declare a record that represents either a full or partial row of a database table or view. For every column of the full or partial row, the record has a field with the same name and data type. If the structure of the row changes, then the structure of the record changes accordingly. 

• %TYPE Attribute

The %TYPE attribute lets you declare a data item of the same data type as a previously declared variable or column (without knowing what that type is). If the declaration of the referenced item changes, then the declaration of the referencing item changes accordingly. The %TYPE attribute is particularly useful when declaring variables to hold database values. 

• Abstract Data Types

An Abstract Data Type (ADT) consists of a data structure and subprograms that manipulate the data. The variables that form the data structure are called attributes. The subprograms that manipulate the attributes are called methods.

 

ADTs are stored in the database. Instances of ADTs can be stored in tables and used as PL/SQL variables.

ADTs let you reduce complexity by separating a large system into logical components, which you can reuse.

In the static data dictionary view *_OBJECTS, the OBJECT_TYPE of an ADT is TYPE. In the static data dictionary view *_TYPES, the TYPECODE of an ADT is OBJECT.

 9.Control Statements

Control statements are the most important PL/SQL extension to SQL.
 

PL/SQL has three categories of control statements:

• Conditional selection statements, which let you run different statements for different data values.
• Loop statements, which let you repeat the same statements with a series of different data values.
• Sequential control statements, which allow you to go to a specified, labeled statement, or to do nothing.

10.Conditional Compilation

Conditional compilation lets you customize the functionality in a PL/SQL application without removing source text. 

• Use new features with the latest database release, and disable them when running the application in an older database release.

• Activate debugging or tracing statements in the development environment, and hide them when running the application at a production site.

11.Processing a Query Result Set One Row at a Time

PL/SQL lets you issue a SQL query and process the rows of the result set one at a time.
you can control the process precisely by using individual statements to run the query, retrieve the results, and finish processing.

Example 1-2 Processing Query Result Rows One at a Time

BEGIN
  FOR someone IN (
    SELECT * FROM employees
    WHERE employee_id < 120
    ORDER BY employee_id
  )
  LOOP
    DBMS_OUTPUT.PUT_LINE('First name = ' || someone.first_name ||
                         ', Last name = ' || someone.last_name);
  END LOOP;
END;
/

 Result:

Architecture of PL/SQL 

PL/SQL Engine

The PL/SQL compilation and runtime system is an engine that compiles and runs PL/SQL units. The engine can be installed in the database or in an application development tool,

 

 When an application development tool processes PL/SQL units, it passes them to its local PL/SQL engine. If a PL/SQL unit contains no SQL statements, the local engine processes the entire PL/SQL unit. This is useful if the application development tool can benefit from conditional and iterative control.

PL/SQL Units and Compilation Parameters

A PL/SQL unit is one of these:

• PL/SQL anonymous block

• FUNCTION

• LIBRARY

• PACKAGE

• PACKAGE BODY

• PROCEDURE

• TRIGGER

• TYPE

• TYPE BODY

PL/SQL units are affected by PL/SQL compilation parameters (a category of database initialization parameters). Different PL/SQL units—for example, a package specification and its body—can have different compilation parameter settings.