01 Introduction to PL/SQL
About to PL/SQL
Stands for “Procedural Language Extension to SQL”
Is Oracle’s standard data access language for relational databases
Seamlessly integrated procedural constructs with SQL
Provides a block structure for executable units of code.
Maintenance of code is easier with such a well-defined structure
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
The PL/SQL Engine:
Oracle uses a PL/SQL engine to processes the PL/SQL statements. A PL/SQL code can be stored in the client system (client-side) or in the database (server-side).
The PL/SQL compilation and run-time system is an engine that compiles and executes PL/SQL blocks and subprograms. .The engine can be installed in an Oracle server or in an application development tool such as Oracle Forms.
In either environment, the PL/SQL engine accepts as input any valid PL/SQL block or subprogram.
Figure shows the PL/SQL engine processing an anonymous block.
The PL/SQL engine executes procedural statements but sends SQL statements to the SQL engine in the Oracle database.
Benefits of PLSQL
Integration of procedural constructs with SQL
PL/SQL is tightly integrated with SQL, the most widely used database manipulation language. For example:
PL/SQL lets you use all SQL data manipulation, cursor control, and transaction control statements, and all SQL functions, operators, and pseudocolumns.
PL/SQL fully supports SQL data types, You need not convert between PL/SQL and SQL data types. For example, if your PL/SQL program retrieves a value from a column of the SQL type VARCHAR2, it can store that value in a PL/SQL variable of the type VARCHAR2.
PL/SQL subprograms are stored in executable form, which can be invoked repeatedly.
Because stored subprograms run in the database server, a single invocation over the network can start a large job.
This division of work reduces network traffic and improves response times.
Stored subprograms are cached and shared among users, which lowers memory requirements and invocation overhead.
Modularized program development
The basic unit in all PL/SQL programs is the block. Black 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 programs
You can break your application in smaller modules
In PL/SQL modularization is implemented using procedures, functions and packages.
Integration with tools
PL/SQL engine is integrated with Oracle tools such as Oracle Forms and Oracle Reports.
PL/SQL enables you to handle exceptions efficiently.
Each PL/SQL program consists of SQL and PL/SQL statements which from a PL/SQL block.
A PL/SQL Block consists of three sections:
The Declaration section (optional).
The Execution section (mandatory).
The Exception (or Error) Handling section (optional).Declaration Section:
The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE.
This section is optional and is used to declare any placeholders like:
which are used to manipulate data in the execution section.
The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and ends with END.
This is a mandatory section and is the section where the program logic is written to perform any task.
The programmatic constructs like loops, conditional statement and SQL statements form the part of execution section.
The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION.
This section is optional. Any errors in the program can be handled in this section, so that the PL/SQL Blocks terminates gracefully.
If the PL/SQL Block contains exceptions that cannot be handled, the Block terminates abruptly with errors.
Statement Ending ;
Every statement in the above three sections must end with a semicolon ; .
PL/SQL blocks can be nested within other PL/SQL blocks.
Comments can be used to document code.
General syntax for a PL/SQL block is :
PL/SQL Block Types
A PL/SQL program comprises one or more blocks. These blocks can be entirely separate or nested within another block.
There are three types of blocks that make up a PL/SQL Program:
Anonymous code blocks
Procedures are named objects that contain SQL and PL/SQL statements. Procedures are stored in the database.
Functions are named objects that contain SQL and PL/SQL statements. Unlike a procedure , a function returns a value of a specified data type. Functions are stored in the database.
Anonymous blocks are unnamed blocks. They are declared inline and are not stored in the database.
They are passed to the PL/SQL Engine for execution at run time.
A sample PL/SQL anonymous code block
The example block has the declarative section and executable section.
The anonymous block gets the first_name of the employee whose employee_id is 100 and stores it in a variable called v_fname,
the which is declared upfront in the declaration section
Enabling output of a PL/SQL block
To enable output in SQL Developer, execute the following command before running the PL/SQL block:
Use a predefined Oracle Package and its procedure in the anonymous block:
DBMS_OUTPUT: Displaying Output
DBMS_OUTPUT provides a mechanism for displaying information from your PL/SQL program on your screen (your session's output device, to be more specific). Each user has a DBMS_OUTPUT buffer of up to 1,000,000 bytes in size.
You can write information to this buffer by calling the DBMS_OUTPUT.PUT and DBMS_OUTPUT.PUT_LINE programs.
Execute a PL/SQL Block
PL/SQL variables are placeholders that store the values that can change through the PL/SQL Block.
Variable names must follow the following rules:
Must start with a letter
Can include letters or numbers
Can include special characters such as $ _ and #
Must contain no more than 30 characters
Must not include reserved words
Declaring and Initializing variables
General Syntax to declare a variable is
variable_name is the name of the variable.
datatype is a valid PL/SQL datatype.
NOT NULL is an optional specification on the variable
value or DEFAULT value is an optional specification, where you can initialize a variable
Each variable declaration is a separate statement and must be terminated by a semicolon.
For example, if you want to store the current salary of an employee, you can use a variable.
* “salary” is a variable of datatype number and of length 6.
When a variable is specified as NOT NULL, you must initialize the variable when it is declared.
For example: The below example declares two variables, one of which is a not null.
Below is a list of examples of variable declaration and initialization:
Write and execute the code blocks below and explain why the out differs:
The value of a variable can change in the execution or exception section of the PL/SQL Block.
We can assign values to variables in the two ways given below.
1) We can directly assign values to variables through the assignment operator (:=)
The General Syntax is:
2) We can assign values to variables directly from the database columns by using a SELECT.. INTO statement.
The General Syntax is:
The program below will get the salary of an employee with id '1116' and display it on the screen.
The backward slash '/' in the above program indicates to execute the above PL/SQL Block.
Scope of PS/SQL Variables (nested blocks )
PL/SQL allows the nesting of Blocks within Blocks i.e, the Execution section of an outer block can contain inner blocks.
Therefore, a variable which is accessible to an outer Block is also accessible to all nested inner Blocks.
The variables declared in the inner blocks are not accessible to outer blocks.
Based on their declaration we can classify variables into two types.
Local variables - These are declared in a inner block and cannot be referenced by outside Blocks.
Global variables - These are declared in a outer block and can be referenced by its itself and by its inner blocks.
In the example below we are creating two variables in the outer block and assigning their product to the third variable created in the inner block.
The variable 'var_mult' is declared in the inner block, so cannot be accessed in the outer block i.e. it cannot be accessed after line 11.
The variables 'var_num1' and 'var_num2' can be accessed anywhere in the block.
As the name implies a constant is a value used in a PL/SQL Block that remains unchanged throughout the program.
A constant is a user-defined literal value. You can declare a constant and use it instead of actual value.
If you want to write a program which will increase the salary of the employees by 25%, you can declare a constant and use it throughout the program.
Next time when you want to increase the salary again you can change the value of the constant which will be easier than changing the actual value throughout the program.
General Syntax to declare a constant is:
constant_name is the name of the constant i.e. similar to a variable name.
The word CONSTANT is a reserved word and ensures that the value does not change.
VALUE - It is a value which must be assigned to a constant when it is declared. You cannot assign a value later.
For example, to declare salary_increase, you can write code as follows:
You must assign a value to a constant at the time you declare it.
If you do not assign a value to a constant while declaring it and try to assign a value in the execution section, you will get a error.
If you execute the below Pl/SQL block you will get error.
What displays the code block below: