PL/SQL Introduction

PL/SQL stands for Procedural Language/SQL. PL/SQL is Oracle's Procedural Language extension to SQL. PL/SQL expands SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. Basically it runs on the database server, but a few Oracle products such as Developer/2000 also contain a PL/SQL engine that resides on the client. Thus, you can run your PL/SQL code on either the client or the server depending on which is more suitable for the task at hand.

Unlike SQL, PL/SQL is procedural, not declarative.

A declarative (non-procedural) programming language is a language that allows the programmer to state the task to be accomplished without specifying the procedures needed to carry it out.

A Procedural programming language is a language in which programs largely consist of a series of commands to assign values to objects.

The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks, which can be nested within each other. Typically, each block performs a logical action in the program. PL/SQL is Block Structured.

The Syntax of a PL/SQL Block

DECLARE
Variable_declarations
BEGIN
Program_code
EXCEPTION
Exception_handlers
END;

Below is the basic structure of the PL/SQL program:

Set serveroutput on
Var1 varchar2(20);
Begin
Var1 := ‘Hello World’;
Dbms_output.put_line(var1);
Exception
When others then
Dbms_output.put_line(‘It is an exception’);
End;
/

After going through the above code following points are worth remembering:

1. In the declaration section all the variables and constants are defined.
2. In PL/SQL all the errors are handled in the Exception block.
3. Begin and End are mandatory statements indicating begin and end of the PL/SQL Block.
4. Variables and Constants must be declared first before they can be used.
5. The declaration of variables and constants are alike, but constant definitions must contain the keyword CONSTANT and must be assigned a value as part of the definition. Later on any attempts to assign a value to a constant will result in an error message.
6. Values can be assigned to variables directly using the “:=” assignment operator, by way of a SELECT ... INTO statement or When used as OUT or IN OUT parameter from a procedure.

Declaring PL/SQL variables and constants.

Example of declaring Variables:

Var1 varchar2(100);
Hire_date Date;
Var2 number default 5;
Var3 number not null := 2;

Not Null means a value may change but it can never be assigned Null.

Var4 varchar2(20) := Null;
Var5 varchar2(20) default Null;

Example of declaring Constants:

Var_constant constant number := 100;

Constants cannot be changed.

You must initialize constants at the time of declaration.


%TYPE and %ROWTYPE

%TYPE is used to declare a variable that is of the same type as a specified table’s column.

Emp_number emp.empno%type;

%ROWTYPE is used to declare a record (variable that represents the entire row of a table).

Emp_record emp%rowtype;

Another example of declaring variable:

Declare
name varchar2(30);
Select ename into name from emp where empno = 20;
Begin
Null;
End;

Any DML statements should be after Begin statement;

Begin
Delete from emp where empno = 29;
Commit;
End;