Informatics Practices by Agnelo
PL/SQL provides packaging mechanism for small blocks of procedural code:
PROCEDURE ProcName(ArgList) IS
declarations;
BEGIN
statements;
EXCEPTION handlers;
END ProcName;
FUNCTION FuncName(ArgList) RETURN Type IS
declarations;
BEGIN
statements; -- including RETURN Expr;
EXCEPTION handlers;
END FuncName;
Each argument has a mode:
| IN | parameter is used for input only (default) |
| OUT | paramter is used to return a result |
| IN OUT | returns result, but initial value is used |
Can also specify a DEFAULT value for each argument.
Procedures can be called in the usual manner:
Or can be called via named parameters e.g.
PROCEDURE p(a1 IN NUMBER DEFAULT 13,
a2 OUT CHAR, a3 IN OUT INT)
...
p(a2 => ch, a3 => my_int);
Procedure Example
A procedure to raise the salary of an employee:
PROCEDURE raise(emp# INTEGER, increase REAL) IS
current_salary REAL;
salary_missing EXCEPTION;
BEGIN
SELECT salary INTO current_salary
FROM Employees WHERE id# = emp#;
IF current_salary IS NULL THEN
RAISE salary_missing;
ELSE
UPDATE Employees
SET salary = salary + increase
WHERE id# = emp#;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN INSERT INTO Audit
VALUES (emp#, "Unknown employee");
WHEN salary_missing THEN INSERT INTO Audit
VALUES (emp#, "Null salary");
END;
EXCECPTION HANDLING (HANDLING ERRORS) : it’s a PL/SQL procedure.
General Syntax
DECLARE
BEGIN
PL / SQL
EXCEPTION
END;
It is an exception condition where the oracle engine FAILS S to understand what is the next course of action, this condition is called exception and we need to handle it
They are 2 types
PRE DEFINED:
TWO_MANY ROWS
NO_DATA_FOUND
LOGON_DENIED
CURSOR_NOT_DPEN
ZERO_DIVIDE
These are automatically defined
They do not need to be declared in the DECALRE in the PL/SQL BLOCK, that can be directly handled by the ORACLE database engine.
USER DEFINED EXCEPTION
They need to be declared in the DECALRE in the PL/SQL BLOCK.
SYNTAX:
DECALRE
Less_sal Exception;
BEGIN
PL / SQL Block;
EXCEPTION
Define the Exception;
END;
FUNCTIONS AND PROCEDURES
Functions and Procedures can be created and replaced
Named PL/SQL block programs which are created, compiled and saved in the database. Procedures and functions are general purpose programs which can be called in the PL/SQL block program.
3 steps
When a procedure and functions are created they are compiled and stored in small p_code
Create or replace procedure P1 ( a IN NUMBER, b IN NUMBER, c OUT NUMBER)
IS / AS
Begin
c = a + b;
End;
EXAMPLE:
SQL> create or replace procedure p
1(a in number, b in
2 is
3 begin
4 c:=a+b;
5 end;
6 /Procedure created.
SQL> declare
2 a number;
3 b number;
4 c number;
5 begin
6 p1(10,10,c);
7 dbms_output.put_line('The Result is' || c);
8 end;
9 /PL/SQL procedure successfully completed.
SQL> set serveroutput on;
SQL> /
The Result is 20PL/SQL procedure successfully completed.
SQL> create or replace procedure p2(a in number, c out number)
2 is
3 begin
4 c:=a*2;
5 end;
6 /Procedure created.
SQL> declare
2 a number;
3 c number;
4 begin
5 p2(50,c);
6 dbms_output.put_line(c);
7 end;
8 /
100PL/SQL procedure successfully completed.
Create or replace function total (unitprice NUMBER, qty NUMBER)
Return number;
IS
Total number;
Begin
Total: = unitprice*qty;
Return(total);
End;
We can INSERT, UPDATE , DELETE.
THEY are created and stored n the database when a DML statement is issued against the table ORACLE engine searches for associated Trigger, and If the trigger is associated the DATABASE TRIGGER will execute.
They consist of 3 parts