Whats is PL/SQL

About PL/SQL

PL/SQL:

• Stands for “Procedural Language extension to SQL”
• Is Oracle Corporation’s standard data access language for relational databases
• Seamlessly integrates procedural constructs with SQL

Structured Query Language (SQL) is the primary language used to access and modify data in relational databases. There are only a few SQL commands, so you can easily learn and use them.

Consider an example:

SQL> SELECT first_name, department_id, salary FROM employees;

The preceding SQL statement is simple and straightforward. However, if you want to alter any data that is retrieved in a conditional manner, you soon encounter the limitations of SQL. Consider a slightly modified problem statement: For every employee retrieved, check the department ID and salary. Depending on the department’s performance and also the employee’s salary, you may want to provide varying bonuses to the employees.

Looking at the problem, you know that you have to execute the preceding SQL statement, collect the data, and apply logic to the data.

• One solution is to write a SQL statement for each department to give bonuses to the employees in that department. Remember that you also have to check the salary component before deciding the bonus amount. This makes it a little complicated.
• A more effective solution might include conditional statements. PL/SQL is designed to meet such requirements. It provides a programming extension to the already-existing SQL.

About PL/SQL

PL/SQL Provides a block structure for executable units of code. Maintenance of code is made easier with such a well-defined structure. It also provides procedural constructs such as Variables, constants, and data types, Control structures such as conditional statements and loops, Reusable program units that are written once and executed many times.

PL/SQL defines a block structure for writing code. Maintaining and debugging code is made easier with such a structure because you can easily understand the flow and execution of the program unit.

PL/SQL offers modern software engineering features such as data encapsulation, exception handling, information hiding, and object orientation. It brings state-of-the-art programming to the Oracle Server and toolset. PL/SQL provides all the procedural constructs that are available in any third-generation language (3GL).

PL/SQL Run-Time Architecture

The PL/SQL engine resides in:

• The Oracle database for executing stored subprograms
• The Oracle Forms client when you run client/server applications, or in the Oracle Application Server when you use Oracle Forms Services to run Forms on the web

Irrespective of the PL/SQL run-time environment, the basic architecture remains the same.Therefore, all PL/SQL statements are processed in the Procedural Statement Executor, and all SQL statements must be sent to the SQL Statement Executor for processing by the Oracle Server processes. The SQL environment may also invoke the PL/SQL environment. For example, the PL/SQL environment is invoked when a PL/SQL function is used in a SELECT statement.

The PL/SQL engine is a virtual machine that resides in memory and processes the PL/SQL m-code instructions. When the PL/SQL engine encounters a SQL statement, a context switch is made to pass the SQL statement to the Oracle Server processes. The PL/SQL engine waits for the SQL statement to complete and for the results to be returned before it continues to process subsequent statements in the PL/SQL block. The Oracle Forms PL/SQL engine runs in the client for the client/server implementation, and in the application server for the Forms Services implementation. In either case, SQL statements are typically sent over a network to an Oracle Server for processing.

Benefits of PL/SQL

Integration of procedural constructs with SQL

The most important advantage of PL/SQL is the integration of procedural constructs with SQL. SQL is a nonprocedural language. When you issue a SQL command, your command tells the database server what to do. However, you cannot specify how to do it. PL/SQL integrates control statements and conditional statements with SQL, giving you better control of your SQL statements and their execution. Earlier in this lesson, you saw an example of the need for such integration.

Improved performance

Without PL/SQL, you would not be able to logically combine SQL statements as one unit. If you have designed an application that contains forms, you may have many different forms with fields in each form. When a form submits data, you may have to execute a number of SQL statements. SQL statements are sent to the database one at a time. This results in many network trips and one call to the database for each SQL statement, thereby increasing network traffic and reducing the performance(especially in a client/server model). With PL/SQL, you can combine all these SQL statements into a single program unit. The application can send the entire block to the database instead of sending the SQL statements one at a time. This significantly reduces the number of database calls. As the slide illustrates, if the application is SQL intensive, you can use PL/SQL blocks to group SQL statements before sending them to the Oracle database server for execution.

Modularized program development:

The basic unit in all PL/SQL programs is the block. Blocks can be in a sequence or they can be nested in other blocks. Modularized program development has the following advantages:

• You can group logically related statements within blocks.
• You can nest blocks inside larger blocks to build powerful programs.
• You can break your application into smaller modules. If you are designing a complex application, PL/SQL allows you to break down the application into smaller, manageable, and logically related modules.
• You can easily maintain and debug code.

In PL/SQL, modularization is implemented using procedures, functions, and packages, which are discussed in the lesson titled “Introducing Stored Procedures and Functions.”

Integration with tools

The PL/SQL engine is integrated into Oracle tools such as Oracle Forms and Oracle Reports. When you use these tools, the locally available PL/SQL engine processes the procedural statements; only the SQL statements are passed to the database.

Portability

PL/SQL programs can run anywhere an Oracle Server runs, irrespective of the operating system and platform. You do not need to customize them to each new environment. You can write portable program packages and create libraries that can be reused in different environments.

Exception handling

PL/SQL enables you to handle exceptions efficiently. You can define separate blocks for dealing with exceptions. You learn more about exception handling in the lesson titled “Handling Exceptions.”PL/SQL shares the same data type system as SQL (with some extensions) and uses the same expression syntax.

PL/SQL Block Structure

A PL/SQL block consists of four sections:

• Declarative (optional): The declarative section begins with the keyword DECLARE and ends when the executable section starts.
• Begin (required): The executable section begins with the keyword BEGIN. This section needs to have at least one statement. However, the executable section of a PL/SQL block can include any number of PL/SQL blocks.
• Exception handling (optional): The exception section is nested within the executable section. This section begins with the keyword EXCEPTION.
• End (required): All PL/SQL blocks must conclude with an END statement. Observe that END is terminated with a semicolon.

In a PL/SQL block, the keywords DECLARE, BEGIN, and EXCEPTION are not terminated by a semicolon. However, the keyword END, all SQL statements, and PL/SQL statements must be terminated with a semicolon.