Oracle Database/PL/SQL

Introduction
PL/SQL stands for Procedural Language extension of SQL. It is a combination of SQL along with the procedural features of programming languages and it enhances the capabilities of SQL by injecting the procedural functionality, like conditional or looping statements, into the set-oriented SQL structure.

Advantages of PL/SQL

 * Procedural Language Capability: PL/SQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (FOR loops).
 * Block Structures: PL/SQL consists of blocks of code, which can be nested within each other. Each block forms a unit of a task or a logical module. PL/SQL Blocks can be stored in the database and reused.
 * Better Performance: PL/SQL engine processes multiple SQL statements simultaneously as a single block, thereby reducing network traffic.
 * Exception Handling: PL/SQL handles exceptions (or errors) effectively during the execution of a PL/SQL program. Once an exception is caught, specific actions can be taken depending upon the type of the exception or it can be displayed to the user with a message.

Limitation
PL/SQL can only use SELECT, DML(INSERT, UPDATE, DELETE) and TC(COMMIT, ROLLBACK, SAVEPOINT) statements, DDL (CREATE, ALTER, DROP) and DCL(GRANT, REVOKE) cannot be used directly. Any DDL/DCL however, can be executed from PL/SQL when embedded in an EXECUTE IMMEDIATE statement.

Basic Structure
Each PL/SQL program consists of SQL and PL/SQL statements which form a PL/SQL block. A PL/SQL Block consists of three sections:

Declaration Section: This section is optional and it starts with the reserved keyword DECLARE. This section is used to declare any placeholders like variables, constants, records and cursors, which are used to manipulate data in the execution section.

Execution Section: This section is mandatory and it starts with the reserved keyword BEGIN and ends with END. This section is 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.

Exception Section: The section is optional and it starts with the reserved keyword EXCEPTION. Any exception 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.

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.

This is how PL/SQL looks.

PL/SQL Placeholders
Placeholders are temporary storage area. Placeholders can be any of Variables, Constants and Records. Oracle defines placeholders to store data temporarily, which are used to manipulate data during the execution of a PL SQL block.

Depending on the kind of data you want to store, you can define placeholders with a name and a datatype. Few of the datatypes used to define placeholders are as given below. The placeholders, that store the values, can change through the PL/SQL Block.

PL/SQL Variables
The 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. If NOT NULL is specified, you must provide the initial value.
 * value or DEFAULT value is also an optional specification, where you can initialize a variable.
 * Each variable declaration is a separate statement and must be terminated by a semicolon.

The below example declares two variables, one of which is a not null.

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) Directly assign value to variable.

2) Assign values to variables directly from the database columns.

The example below will get the salary of an employee with id '12345' and display it on the screen. NOTE: The slash '/' indicates to execute the above PL/SQL Block.

PL/SQL Records
Records are composite datatypes, which contains a combination of different scalar datatypes like char, varchar, number etc. Each scalar data types in the record holds a value. A record can store values of a row in a table.

A datatype can be declared in the same way as you create a table, like col_name_1. If a field is based on a column from database table, you can define the datatype as col_name_2. You can also use %type method to declare datatype of variable and constant. Similar to %type, if all the fields of a record are based on the columns of a table, it can be declared by using %rowtype method.

For Example:

Declaring the record as a ROWTYPE Advantages: 1) Do not need to explicitly declare variables for all the columns in a table. 2) If the column specification in the database table is altered, the code does not need to update.

Disadvantage: 1) When a record is created as a ROWTYPE, fields will be created for all the columns in the table and memory will be used to create the datatype for all the fields.

Assign values to record Similar to variable, you can assign value to record either by direct assign or through the SELECT statements If the records is declared as ROWTYPE, SELECT * can be used to assign values.

The column value of the record can be retrieved as below syntax

Scope of Variables and Records
PL/SQL allows the nesting of Blocks within Blocks i.e, the Execution section of an outer block can contain inner blocks. Variables which are accessible to an outer Block are also accessible to all nested inner blocks; however, the variables declared in the inner blocks are not accessible to the 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 outer blocks.
 * Global variables - These are declared in an outer block and can be referenced by its itself and by its inner blocks.

In the below example, two variables are created in the outer block and assigning their product to the third variable created in the inner block. The variables 'var_num1' and 'var_num2' can be accessed anywhere in the block; however, the variable 'var_result' is declared in the inner block, so it cannot be accessed in the outer block.

PL/SQL Constants
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.

For example:

You must assign a value to the constant while declaring it. If you assign a value to the constant later, Oracle will prompt exception.

PL/SQL Conditional Statements
PL/SQL supports programming language features like conditional statements, iterative statements.

The syntax of conditional statements: Note: be aware of the keyword ELSIF, there is no 'E' before 'IF'.

PL/SQL Iterative Statements
An iterative statements are used when you want to repeat the execution of one or more statements for specified number of times. There are three types of loops in PL/SQL:

1. Simple Loop A Simple Loop is used when a set of statements is to be executed at least once before the loop terminates. An EXIT condition must be specified in the loop, otherwise the loop will get into an infinite number of iterations. When the EXIT condition is satisfied the process exits from the loop.

Note: a) Initialize a variable before the loop body. b) Increment the variable in the loop. c) Use a EXIT WHEN statement to exit from the Loop. If you use a EXIT statement without WHEN condition, the statements in the loop is executed only once.

2. While Loop A WHILE LOOP is used when a set of statements has to be executed as long as a condition is true. The condition is evaluated at the beginning of each iteration. The iteration continues until the condition becomes false. Note: a) Initialize a variable before the loop body. b) Increment the variable in the loop. c) EXIT WHEN statement and EXIT statements can be used in while loops it is seldom used.

3. FOR Loop A FOR LOOP is used to execute a set of statements for a pre-determined number of times. Iteration occurs between the start and end integer values given. The counter is always incremented by 1. The loop exits when the counter reaches the value of the end integer. Note: a) The counter variable is implicitly declared in the declaration section, so it's not necessary to declare it explicitly. b) The counter variable is incremented by 1 and does not need to be incremented explicitly. c) EXIT WHEN statement and EXIT statements can be used in FOR loops but it is seldom used.

PL/SQL Cursors
A cursor contains information on a select statement and the rows of data accessed by it. This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.

There are two types of cursors in PL/SQL:

Implicit cursor:
When you execute DML statements like DELETE, INSERT, UPDATE and SELECT..INTO statements, implicit statements are created to process these statements.

Oracle provides few attributes called as implicit cursor attributes to check the status of DML operations. The cursor attributes available are %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN.

For example, When you execute INSERT, UPDATE, or DELETE statements the cursor attributes tell us whether any rows are affected and how many have been affected. When a SELECT... INTO statement is executed in a PL/SQL Block, implicit cursor attributes can be used to find out whether any row has been returned by the SELECT statement. PL/SQL returns an error when no data is selected.

The status of the cursor for each of these attributes are defined in the below table.

uses implicit cursor attributes:

In the above PL/SQL Block, the salaries of all the employees in the 'employee' table are updated. If none of the employee's salary are updated we get a message 'None of the salaries where updated'. Else we get a message like for example, 'Salaries for 100 employees are updated' if there are 100 rows in 'employee' table.

Explicit cursor:
An explicit cursor is defined in the declaration section of the PL/SQL Block. It must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.

There are four steps in using an Explicit Cursor.
 * DECLARE the cursor in the declaration section.
 * OPEN the cursor in the Execution Section.
 * FETCH the data from cursor into PL/SQL variables or records in the Execution Section.
 * CLOSE the cursor in the Execution Section before you end the PL/SQL Block.

Declaration For example:

Using Cursor When a cursor is opened, the first row becomes the current row. When the data is fetched it is copied to the record or variables and the logical pointer moves to the next row and it becomes the current row. On every fetch statement, the pointer moves to the next row. If you want to fetch after the last row, the program will throw an error. When there is more than one row in a cursor we can use loops along with explicit cursor attributes to fetch all the records.

Note:
 * We can fetch the rows in a cursor to a PL/SQL Record or a list of variables created in the PL/SQL Block.
 * If you are fetching a cursor to a PL/SQL Record, the record should have the same structure as the cursor.
 * If you are fetching a cursor to a list of variables, the variables should be listed in the same order in the fetch statement as the columns are present in the cursor.
 * When we try to open a cursor which is not closed in the previous operation, it throws exception.
 * When we try to fetch a cursor after the last operation, it throws exception.

For Example:

Oracle provides some attributes known as Explicit Cursor Attributes to control the data processing while using cursors. We use these attributes to avoid errors while accessing cursors through OPEN, FETCH and CLOSE Statements.

Cursor with a Simple Loop:

The cursor attribute %ISOPEN is used to check if the cursor is open, if the condition is true the program does not open the cursor again. The cursor attribute %NOTFOUND is used to check whether the fetch returned any row. If there is no row found, the program would exit. Typically, when the cursor reach the last row, no more row can be fetched.

Cursor with a While Loop: Using %FOUND to evaluate if the first fetch statement returned a row, if TRUE, the program moves into the while loop. Inside the loop, use fetch statement again to process the next row. If the fetch statement is not executed once before the while loop, the while condition will return false in the first instance and the while loop is skipped.

Cursor with a FOR Loop: When using FOR LOOP, you do not need to declare a record or variables to store the cursor values, do not need to open, fetch and close the cursor. These functions are accomplished by the FOR LOOP automatically. When the FOR loop is processed a record 'rec_emp' of structure 'cur_emp' gets created, the cursor is opened, the rows are fetched to the record 'rec_emp' and the cursor is closed after the last row is processed. By using FOR Loop, you can reduce the number of lines in the program.

PL/SQL Procedures
When calling store procedure from the PL/SQL Block, you simply use the store procedure name to call. If you prefix the 'EXECUTE' keyword in front of the store procedure name, you will receive an error.