You typically use PL/SQL to add business logic to the database.
PL/SQL programs are divided up into structures known as blocks.
Each block containing PL/SQL and SQL statements.
A typical PL/SQL block has the following structure:
[DECLARE
declaration_statements
]
BEGIN
executable_statements
[EXCEPTION
exception_handling_statements
]
END;
The declaration and exception blocks are optional.
declaration_statements declares the variables subsequently used in the rest of the block.
These variables are local to that block.
Declarations are always placed at the start of the block.
executable_statements are the actual executable statements for the block.
executable_statements may include statements for performing tasks such as loops, conditional logic, and so on.
exception_handling_statements are statements that handle any errors.
Every statement is terminated by a semicolon (;).
A block is terminated using the END keyword.
DECLARE
2 width INTEGER;
3 height INTEGER := 2;
4 area INTEGER;
5 BEGIN
6 area := 6;
7 width := area / height;
8 DBMS_OUTPUT.PUT_LINE('width = ' || width);
9 EXCEPTION
10 WHEN ZERO_DIVIDE THEN
11 DBMS_OUTPUT.PUT_LINE('Division by zero');
12 END;
13 /
width = 3
PL/SQL procedure successfully completed.
SQL>