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

https://sites.google.com/a/ictedu.info/web/e-learning/oracle-pl-sql-programming/leksion-01/about%20plsql.png?attredirects=0


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:


https://sites.google.com/a/ictedu.info/web/e-learning/oracle-pl-sql-programming/leksion-01/plsql%20engine.gif

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 

Benefits of PL/SQL




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


https://sites.google.com/a/ictedu.info/web/e-learning/oracle-pl-sql-programming/leksion-01/block%20structure.png

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
https://sites.google.com/a/ictedu.info/web/e-learning/oracle-pl-sql-programming/leksion-01/block%20types.png

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

https://sites.google.com/a/ictedu.info/web/e-learning/oracle-pl-sql-programming/leksion-01/execute%20a%20block.png





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;