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:
- In the declaration section all the variables and constants are defined.
- In PL/SQL all the errors are handled in the Exception block.
- Begin and End are mandatory statements indicating begin and end of the PL/SQL Block.
- Variables and Constants must be declared first before they can be used.
- 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.
- 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.
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;