Oracle Database/PL/SQL

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.

/* multi-lines comments */
-- single line comments
DECLARE 
    Variable declaration
BEGIN 
    Program Execution 
EXCEPTION 
    Exception handling
END;

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.

Number(n,m), Char(n), Varchar2(n), Date, Long, Long Raw, Raw, Blob, Clob, Nclob, Bfile

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 datatype [NOT NULL := value ];

• 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.

DECLARE 
  emp_id varchar2(10);
  salary number(9,2) NOT NULL := 1000.00;

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.

variable_name:=  value;

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

SELECT column_name
INTO variable_name 
FROM table_name
[WHERE condition];

The example below will get the salary of an employee with id '12345' and display it on the screen.

DECLARE
  var_emp_id varchar2(10) = 'A12345'; 
  var_salary number(9,2);  
BEGIN 
  SELECT salary
  INTO var_salary
  FROM employee
  WHERE emp_id = var_emp_id;
  dbms_output.put_line(var_salary);
  dbms_output.put_line('Employee ' || var_emp_id || ' earns salary ' || var_salary); 
END; 
/

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.

TYPE record_name IS RECORD 
  (col_name_1 datatype, 
   col_name_2 table_name.column_name%type);

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.

record_name table_name%ROWTYPE;

For Example:

DECLARE 
  TYPE rec_employee IS RECORD 
   (emp_id          varchar2(10), 
    emp_last_name   employees.last_name%type, 
    emp_dept        employees.dept%type,
    salary          number(9,2)
   );

DECLARE 
  rec_employee employees%ROWTYPE;

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

record_name.col_name := value;

SELECT col_1, col_2 
INTO record_name.col_name_1, record_name.col_name_2 
FROM table_name 
[WHERE condition];

If the records is declared as ROWTYPE, SELECT * can be used to assign values.

SELECT * INTO record_name
FROM table_name 
[WHERE condition];

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

var_name := record_name.col_name;

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.

DECLARE
  var_num1 number;
  var_num2 number;
BEGIN 
  var_num1 := 100; 
  var_num2 := 200; 
  DECLARE 
    var_result number;
  BEGIN
    var_result := var_num1 * var_num2; 
  END;
  /* var_result is not accessible to here */ 
END; 
/

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.

constant_name CONSTANT datatype := VALUE;

For example:

DECLARE 
  comm_pct CONSTANT number(3) := 10;

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:

IF condition_1 THEN 
 statement_1; 
 statement_2; 
[ELSIF condition_2 THEN 
 statement_3;] 
[ELSE 
 statement_4;] 
END IF;

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.

LOOP 
  statements; 
  EXIT; 
  {or EXIT WHEN condition;}
END 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.

WHILE <condition> 
 LOOP statements; 
END LOOP;

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.

FOR counter IN start_val..end_val 
  LOOP statements; 
END LOOP;

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.

Attribute	Return Value
SQL%FOUND	The return value is TRUE, if the DML statements like INSERT, DELETE and UPDATE affect at least one row and if SELECT ….INTO statement return at least one row. The return value is FALSE, if DML statements like INSERT, DELETE and UPDATE do not affect row and if SELECT….INTO statement do not return a row.
SQL%NOTFOUND	The return value is FALSE, if DML statements like INSERT, DELETE and UPDATE at least one row and if SELECT ….INTO statement return at least one row. The return value is TRUE, if a DML statement like INSERT, DELETE and UPDATE do not affect even one row and if SELECT ….INTO statement does not return a row.
SQL%ROWCOUNT	Return the number of rows affected by the DML operations INSERT, DELETE, UPDATE, SELECT
SQL%ISOPEN	Always return FALSE

uses implicit cursor attributes:

DECLARE  var_rows number(5);
BEGIN
  UPDATE employee 
  SET salary = salary + 2000;
  IF SQL%NOTFOUND THEN
    dbms_output.put_line('None of the salaries where updated');
  ELSIF SQL%FOUND THEN
    var_rows := SQL%ROWCOUNT;
    dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');
  END IF; 
END;

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

CURSOR cursor_name IS select_statement;

For example:

DECLARE
   CURSOR cur_emp IS 
   SELECT * 
   FROM employees
   WHERE salary > 10000;

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.

OPEN cursor_name;
FETCH cursor_name INTO record_name|variable_list;
CLOSE cursor_name;

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:

DECLARE 
  rec_emp employees%rowtype;
  CURSOR cur_emp IS 
  SELECT *
  FROM employees
  WHERE salary > 10000; 
BEGIN 
  OPEN cur_emp; 
  FETCH cur_emp INTO rec_emp; 
    dbms_output.put_line (rec_emp.first_name || '  ' 
                       || rec_emp.last_name); 
  CLOSE emp_cur; 
END;

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.

Attributes	Return Values
Cursor_name%FOUND	TRUE, if fetch statement returns at least one row. FALSE, if fetch statement doesn’t return a row.
Cursor_name%NOTFOUND	TRUE, , if fetch statement doesn’t return a row. FALSE, if fetch statement returns at least one row.
Cursor_name%ROWCOUNT	The number of rows fetched by the fetch statement. If no row is returned, the PL/SQL statement returns an error.
Cursor_name%ISOPEN	TRUE, if the cursor is already open in the program. FALSE, if the cursor is not opened in the program.

Cursor with a Simple Loop:

DECLARE 
  CURSOR cur_emp IS 
  SELECT first_name, last_name, salary FROM employees; 
  rec_emp cur_emp%rowtype; 
BEGIN 
  IF NOT cur_emp%ISOPEN THEN 
    OPEN cur_emp; 
  END IF; 
  LOOP 
    FETCH cur_emp INTO rec_emp; 
    EXIT WHEN cur_emp%NOTFOUND; 
    dbms_output.put_line(cur_emp.first_name || ' ' ||cur_emp.last_name 
      || ' ' ||cur_emp.salary); 
  END LOOP; 
END; 
/

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:

DECLARE 
  CURSOR cur_emp IS 
  SELECT first_name, last_name, salary FROM employees; 
  rec_emp cur_emp%rowtype; 
BEGIN 
  IF NOT cur_emp%ISOPEN THEN 
    OPEN cur_emp; 
  END IF; 
  FETCH cur_emp INTO sales_rec;  
 WHILE cur_emp%FOUND THEN  
 LOOP 
   dbms_output.put_line(cur_emp.first_name || ' ' ||cur_emp.last_name 
   || ' ' ||cur_emp.salary); 
   FETCH cur_emp INTO sales_rec; 
 END LOOP; 
END; 
/

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.

DECLARE 
 CURSOR cur_emp IS 
   SELECT first_name, last_name, salary FROM employees; 
 rec_emp cur_emp%rowtype; 
BEGIN 
  FOR rec_emp in cur_emp 
  LOOP  
    dbms_output.put_line(cur_emp.first_name || ' ' ||cur_emp.last_name 
    || ' ' ||cur_emp.salary);  
  END LOOP; 
END;
/

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.

my_sproc;

EXECUTE my_sproc;

PLS-00103: Encountered the symbol "my_sproc" when expecting one of the following:
:= . ( @ % ; immediate
The symbol ":=" was substituted for "my_sproc" to continue.

EXECUTE IMMEDIATE my_sproc;

PLS-00222: no function with name exists in this scope