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
Below is the basic structure of the PL/SQL program:
Set serveroutput on
Var1 := ‘Hello World’;
When others then
Dbms_output.put_line(‘It is an exception’);
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:
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.
%ROWTYPE is used to declare a record (variable that represents the entire row of a table).
Another example of declaring variable:
Select ename into name from emp where empno = 20;
Any DML statements should be after Begin statement;
Delete from emp where empno = 29;