01 Introduction to PL/SQL

About to PL/SQL

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

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

Improved Performance

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.

Exception Handling

PL/SQL enables you to handle exceptions efficiently.

PL/SQL Blocks

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:

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:

  • variables,

  • constants,

  • records and

  • cursors,

which are used to manipulate data in the execution section.

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.

Exception 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 :

DECLARE

Variable declaration

BEGIN

Program Execution

EXCEPTION

Exception handling

END;

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:

  • Procedures

  • Functions

  • Anonymous code blocks

Procedures

Procedures are named objects that contain SQL and PL/SQL statements. Procedures are stored in the database.

Functions

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

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

DECLARE

v_fname VARCHAR2(20);

BEGIN

SELECT employee_id INTO v_fname

FROM employees WHERE employee_id=100;

END

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:

SET SERVEROUTPUT ON;

Use a predefined Oracle Package and its procedure in the anonymous block:

DBMS_OUTPUT.PUT_LINE

dbms_output.put_line('hello');

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

PL/SQL variables are placeholders that store the values that can change through the PL/SQL Block.

Variable names

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

Identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr];

where:

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

DECLARE

salary number (6);

* “salary” is a variable of datatype number and of length 6.

NOT NULL

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.

DECLARE

salary number(4);

dept varchar2(10) NOT NULL := “HR Dept”;

Below is a list of examples of variable declaration and initialization:

DECLARE

v_hiredate DATE;

v_deptno NUMBER(2) NOT NULL := 10;

v_location VARCHAR2(13) DEFAULT ‘Atlanta’;

c_comm CONSTANT NUMBER :=1400;

Exercise

Write and execute the code blocks below and explain why the out differs:

Block 1

Block 2

SET SERVEROUTPUT ON;

DECLARE

v_Myname VARCHAR2(20);

BEGIN

dbms_output.put_line('My name is: '||v_myname);

v_myname:='John';

dbms_output.put_line('My name is: '||v_myname);

END;

SET SERVEROUTPUT ON;

DECLARE

v_Myname VARCHAR2(20):='John';

BEGIN

dbms_output.put_line('My name is: '||v_myname);

v_myname:='Steven';

dbms_output.put_line('My name is: '||v_myname);

END;

Assignment Operator

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:

variable_name:= value;

2) We can assign values to variables directly from the database columns by using a SELECT.. INTO statement.

The General Syntax is:

SELECT column_name

INTO variable_name

FROM table_name

[WHERE condition];

Example:

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

SET SERVEROUTPUT ON

DECLARE

var_salary number(6);

var_emp_id number(6) := 100;

BEGIN

SELECT salary

INTO var_salary

FROM employees

WHERE employee_id = var_emp_id;

dbms_output.put_line(var_salary);

dbms_output.put_line('The employee '

|| var_emp_id || ' has salary ' || var_salary);

END;

/

NOTE

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.

For Example:

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.

1> DECLARE

2> var_num1 number;

3> var_num2 number;

4> BEGIN

5> var_num1 := 100;

6> var_num2 := 200;

7> DECLARE

8> var_mult number;

9> BEGIN

10> var_mult := var_num1 * var_num2;

11> END;

12> END;

13> /

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:

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 CONSTANT datatype := VALUE;

where:

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

DECLARE

salary_increase CONSTANT number (3) := 10;

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.

DECLARE

salary_increase CONSTANT number(3);

BEGIN

salary_increase := 100;

dbms_output.put_line (salary_increase);

END;

Exercice

What displays the code block below:

SET SERVEROUTPUT ON;

DECLARE

v_Myname VARCHAR2(20):='John';

BEGIN

DECLARE

v_Myname VARCHAR2(20):='Nested John';

BEGIN

dbms_output.put_line('My name is: '||v_myname);

END;

dbms_output.put_line('My name is: '||v_myname);

v_myname:='Steven';

dbms_output.put_line('My name is: '||v_myname);

END;