CBSE eBooks

CBSE Guess > eBooks > Class XII > Informatics Practices By Agnelo

Procedures Functions

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:

Can also specify a DEFAULT value for each argument.

Procedures can be called in the usual manner:

• same number of arguments as formal parameters
• arguments given in same order as formal parameters

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

1. PRE DEFINED
2. USER DEFINED EXCEPTION

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

1. Check the validity of the sub-program.
2. Validity of the user.
3. Check for the compiler code and execute it.

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 20

PL/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 /
100

PL/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

1. Triggering event
2. Triggering constraint
3. Action