Introduction to pl/sql

PL/SQL is a procedural language designed specifically to embrace SQL statements within its syntax. PL/SQL program units are compiled by the Oracle Database server and stored inside the database. And at run-time, both PL/SQL and SQL run within the same server process, bringing optimal efficiency. PL/SQL automatically inherits the robustness, security, and portability of the Oracle Database.

PL/SQL is a powerful, yet straightforward database programming language. It is easy to both write and read, and comes packed with lots of out-of-the-box optimizations and security features.

advantages of plsql

  • It is a standard database language and PL/SQL is strongly integrated with SQL. PL/SQL supports both static and also dynamic SQL. Static SQL is said to support DML operations and also the transaction control from PL/SQL block. In dynamic SQL, SQL allows embedding the DDL statements in the PL/SQL blocks.
  • Also, It then allows sending an entire block of statements to the database at one time. It reduces network traffic and also provides high performance for the applications.
  • It gives high productivity to programmers as it can query, transform and also update the data in the database.
  • This is said to save time on the design and also the debugging by strong features, like the exception handling, encapsulation, data hiding and also object-oriented data types.
  • Applications that are written in PL/SQL languages are portable.
  • This provides high security level.
  • It also provides access to the predefined SQL packages.
  • It also supports for Object-oriented programming.
  • It provides support for developing web applications and server pages.

BLOCK STRUCTURE

PL/SQL is a block-structured language whose code is organized into blocks. A PL/SQL block consists of three sections: declaration, executable, and exception-handling sections. In a block, the executable section is mandatory while the declaration and exception-handling sections are optional.

A PL/SQL block has a name. Functions or Procedures is an example of a named block. A named block is stored into the Oracle Database server and can be reused later.

A block without a name is an anonymous block. An anonymous block is not saved in the Oracle Database server, so it is just for one-time use. However, PL/SQL anonymous blocks can be useful for testing purposes.

Syntax

declaration section
BEGIN
exception execution
EXCEPTION
exception section
END;

Example

DECLARE
message VARCHAR(255) : = 'HELLO DEVELOPER';
BEGIN
DBMS_OUTPUT.PUT_LINE(message);
END;

Output

HELLO DEVELOPER



Data types

Each value in PL/SQL such as a constant, variable and parameter has a data type that determines the storage format, valid values, and allowed operations.
PL/SQL has two kinds of data types: scalar and composite. The scalar types are types that store single values such as number, Boolean, character, and datetime whereas the composite types are types that store multiple values, for example, record and collection.

This tutorial explains the scalar data types that store values with no internal components.

Data type Synonyms
NUMBER DEC, DECIMAL, DOUBLE PRECISION, FLOAT, INTEGER, INT, NUMERIC, REAL, SMALLINT
CHAR CHARACTER, STRING
VARCHAR2 VARCHAR

declaring variables

In PL/SQL, a variable is named storage location that stores a value of a particular data type. The value of the variable changes through the program. Before using a variable, you must declare it in the declaration section of a block.

Syntax

variable_name dataType [NOT NULL] [:= initial_value];

Default Values

PL/SQL allows you to set a default value for a variable at the declaration time. To assign a default value to a variable, you use the assignment operator (:=) or the DEFAULT keyword.

Example

DECLARE
l_product_name VARCHAR2( 100 ) DEFAULT 'Laptop';
BEGIN
NULL;
END;

NOT NULL CONSTRAINT

If you impose the NOT NULL constraint on a value, then the variable cannot accept a NULL value. Besides, a variable declared with the NOT NULL must be initialized with a non-null value. Note that PL/SQL treats a zero-length string as a NULL value.

The following example first declares a variable named l_shipping_status with the NOT NULL constraint. Then, it assigns the variable a zero-length string.

Example

DECLARE

l_shipping_status VARCHAR2( 25 ) NOT NULL := 'Shipped';
BEGIN

l_shipping_status := '';
END;

VARIABLE ASSIGNMENT

To assign a value to a variable, you use the assignment operator (:=)

Example

DECLARE

l_customer_group VARCHAR2(100) := 'Silver';
BEGIN
l_customer_group := 'Gold';
DBMS_OUTPUT.PUT_LINE(l_customer_group);

END;

Anchored declarations

Typically, you declare a variable and select a value from a table column to this variable. If the data type of the table column changes, you must adjust the program to make it work with the new type.

PL/SQL allows you to declare a variable whose data type anchor to a table column or another variable. Consider the following example:

DECLARE

emp_name employee.name%TYPE;
emp_salary employee.salary%TYPE;

BEGIN
SELECT
name, salary
INTO
emp_name, emp_salary
FROM
employee
WHERE
id = 3;
DBMS_OUTPUT.PUT_LINE(emp_name || ':' || emp_salary );

END;

  • First, declare two variables emp_name and emp_salary whose data type anchors to the name and salary columns respectively, in the declaration section of the block.
  • Second, query employee name and salary of the employee id 3 and assign these column values to the emp_name and salary variables in the execution block.

OUTPUT

Kumar:9000

constant

Unlike a variable, a constant holds a value that does not change throughout the execution of the program.
Constants make your code more readable.

Syntax

constant_name constant datatype [NOT NULL] := expression;

Example

DECLARE

co_pi CONSTANT REAL := 3.14159;
co_radius CONSTANT REAL := 10;
co_area CONSTANT REAL := (co_pi * co_radius**2);
BEGIN
DBMS_OUTPUT.PUT_LINE(co_area);

END;

Output

314.159

Operator Precedence

This term determines the grouping of the terms in an expression. This shows how an expression is evaluated. Certain operators will have higher precedence than others, like Multiplication operator has higher precedence than the addition operator.

Example

DECLARE

Y number:= 5 + 2 * 3;
BEGIN
DBMS_OUTPUT.PUT_LINE(Y);

END;

Output

11

In the above equation Y is assigned 11, not 21. Since, * has higher precedence than +. So, it first gets multiplied with 2*3 and then adds into 5.

However, operators with the highest precedence appear at the top of the table, those with the lowest precedence appear at the bottom. And then within the expression, higher precedence operators will be evaluated first.

The precedence of the operators goes like as follow: =, <,>, <=,>=, <>, !=, ~=, ^=, IS NULL, LIKE, BETWEEN, IN

Operator Operation
** Exponentiation
+,- Identity, negation
+,-,|| Addition, subtraction, concatenation
NOT Logical negation
AND conjunction
OR inclusion

Conditional and Iterative Control

PL/SQL Iterative control statements allow a programmer to execute a set of statements multiple times. All iterative statements must have a conditional statement to check whether to continue to execute the block of statement another time.

  • Simple loop
  • While loop
  • For loop

Additional Statements used with PL/SQL Iterative Control Statements

  • exit
  • exit when
  • continue

Simple loop

The simple loop is infinite. The loop keeps on executing the enclosed statements infinitely. Simple loop needs an exit condition and exit statement to terminate.

Syntax

LOOP
Loop statements
Conditional exit using EXIT|EXIT WHEN
END LOOP;

Example

DECLARE

num NUMBER:=1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('Number = '||num);
num:=num+1;
IF num>3 THEN
EXIT;
END IF;
END LOOP;

END;

Output

Number = 1
Number = 2
Number = 3

WHILE loop

WHILE loop is not infinite. The condition of termination is defined after WHILE and before LOOP keywords. Between WHILE LOOP and END LOOP the set of statements can be given. These statements are executed as long as the condition in WHILE is true.

After every iteration or execution of block of statements control returns to WHILE. If the condition is true, next iteration is initiated. If this condition is false, control is passed to statement after END LOOP;

Syntax

WHILE condition expression LOOP
Loop statements
[CONTINUE| EXIT|EXIT WHEN with condition]
END LOOP;

Example

DECLARE
num NUMBER:=1;
BEGIN
WHILE num<=3 LOOP
DBMS_OUTPUT.PUT_LINE('Number = '||num);
num:=num+1;
END LOOP;
END;

Output

Number = 1
Number = 2
Number = 3

FOR loop

FOR Loop is not infinite. In the FOR loop header a counter variable is used which is compared between Lower Bound and Upper Bound values. The variable is initialized to the Lower bound value and is incremented with iterations. The statements block is executed as long as variable’s value remains less than or equal to upper bound value.

Syntax

FOR counter-variable IN Lower Bound.. Upper Bound LOOP
Loop statements
[CONTINUE| EXIT|EXIT WHEN with condition]
END LOOP;

Example

DECLARE
num NUMBER :=1;
BEGIN

FOR i IN num .. 3 LOOP
DBMS_OUTPUT.PUT_LINE ('Number = '||i);
END LOOP;

END;

Output

Number = 1
Number = 2
Number = 3

CURSOR

A cursor is a POINTER to this context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set.

You can name a cursor so that it could be referred to in a program to fetch and process the rows returned by the SQL statement, one at a time. There are two types of cursors :

  • Implicit cursor
  • Explicit cursor

Implicit cursor

Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it.

Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.

Attribute Description
%FOUND Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.
%NOTFOUND The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.
%ISOPEN Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement.
%ROWCOUNT Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.

Employee table

id name salary
1 Rohit 5000
2 Gill 4000
3 Kumar 9000

Example

DECLARE
total_rows number(2);
BEGIN
UPDATE employee
SET salary = salary + 1000;
IF sql%notfound THEN
dbms_output.put_line('employee not found');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' updated ');
END IF;
END;

When the above code is executed at the SQL prompt, it produces the following result −

3 employee selected
PL/SQL procedure successfully completed.


If you check the records in employee table, you will find that the rows have been updated

Output

id name salary
1 Rohit 6000
2 Gill 5000
3 Kumar 10000

Explicit cursor

Explicit cursors are programmer-defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.

Syntax for creating an explicit cursor :

CURSOR cursor_name IS select_statement;

Working with an explicit cursor includes the following steps :

  • Declaring the cursor for initializing the memory
  • Opening the cursor for allocating the memory
  • Fetching the cursor for retrieving the data
  • Closing the cursor to release the allocated memory

Declaring the Cursor

Declaring the cursor defines the cursor with a name and the associated SELECT statement.

Example

CURSOR emp_employee IS
SELECT id, name, salary FROM employee;

Opening the Cursor

Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it.

Example

CURSOR emp_employee IS
SELECT id, name, salary FROM employee;

Fetching the Cursor

Fetching the cursor involves accessing one row at a time.

Example

FETCH emp_employee INTO emp_id, emp_name, emp_salary;

Closing the Cursor

Closing the cursor means releasing the allocated memory.

Example

CLOSE emp_employee;

Complete example of Explicit Cursor

DECLARE
emp_id employee.id%type;
emp_name employee.name%type;
emp_salary employee.salary%type;
CURSOR emp_employee is
SELECT id, name, salary FROM employee;

BEGIN
OPEN emp_employee;
LOOP
FETCH emp_employee into emp_id, emp_name, emp_salary;
EXIT WHEN emp_employee%notfound;
dbms_output.put_line(emp_id || ' ' || emp_name || ' ' || emp_salary);
END LOOP;
CLOSE emp_employee;

END;

Output

1 Rohit 5000
2 Gill 4000
3 Kumar 9000


Exception

Exceptions in PL/SQL. An exception is an error condition during a program execution. PL/SQL supports programmers to catch such conditions using EXCEPTION block in the program and an appropriate action is taken against the error condition.

There are two types of exceptions :

  • system-defined exceptions
  • user-defined exceptions

Syntax

DECLARE
declarations section
BEGIN
executable command(s)

EXCEPTION
exception handling goes here
WHEN exception1 THEN
exception1-handling-statements
WHEN exception2 THEN
exception2-handling-statements
........
WHEN others THEN
exception3-handling-statements

END;

Example

DECLARE
emp_id employee.id%type;
emp_name employee.name%type :='Sammy';
emp_salary employee.salary%type;

BEGIN
SELECT name, salary INTO emp_name, emp_salary
FROM employee
WHERE id = emp_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| emp_name);
DBMS_OUTPUT.PUT_LINE ('salary: ' || emp_salary);

EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('employee not found');
WHEN others THEN
dbms_output.put_line('Error!');

END;

Output

employee not found
PL/SQL procedure successfully completed.

User-defined Exceptions

PL/SQL allows you to define your own exceptions according to the need of your program. A user-defined exception must be declared and then raised explicitly, using either a RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.

Syntax

DECLARE
exception_name EXCEPTION;

Example

DECLARE emp_id customers.id%type := &cemp_id;
emp_name customerS.Name%type;
emp_salary customers.salary%type;
-- user defined exception
ex_invalid_id EXCEPTION;

BEGIN
IF emp_id <= 0 THEN
RAISE ex_invalid_id;
ELSE
SELECT name, salary INTO emp_name, emp_salary
FROM customers
WHERE id = emp_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| emp_name);
DBMS_OUTPUT.PUT_LINE ('salary: ' || emp_salary);
END IF;

EXCEPTION
WHEN ex_invalid_id THEN
dbms_output.put_line('id must be greater than zero!');
WHEN no_data_found THEN
dbms_output.put_line('employee not found');
WHEN others THEN
dbms_output.put_line('Error!');

END;

Output

Enter value for cemp_id: -6 (let's enter a value -6)
old 2: emp_id customers.id%type := &cemp_id;
new 2: emp_id customers.id%type := -6;
id must be greater than zero!

PL/SQL procedure successfully completed.

Triggers

Triggers are stored programs, which are automatically executed or fired when some events occur.
Triggers are, in fact, written to be executed in response to any of the following events :

  • database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
  • database definition (DDL) statement (CREATE, ALTER, or DROP).
  • database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

Triggers can be defined on the table, view, schema, or database with which the event is associated.

Benefits of Triggers

  • Generating some derived column values automatically
  • Enforcing referential integrity
  • Event logging and storing information on table access
  • Synchronous replication of tables
  • Imposing security authorizations
  • Preventing invalid transactions

Syntax

CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;

Employee table

id name salary
1 Rohit 5000
2 Gill 4000
3 Kumar 9000

Example of creating triggers

CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON employee
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.salary - :OLD.salary;
dbms_output.put_line('Old salary: ' || :OLD.salary);
dbms_output.put_line('New salary: ' || :NEW.salary);
dbms_output.put_line('Salary difference: ' || sal_diff);
END;

Triggering Triggers

Let us perform some DML operations on the employee table. Here is one update statement, which will update a record in the table


UPDATE employee
SET salary = salary + 1000
WHERE id = 1;

When a record is updated in the employee table, the above create trigger, display_salary_changes will be fired and it will display the following result −

Output

Old salary: 5000
New salary: 6000
Salary difference: 1000