(Subject Notes) Informatics Practices

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:

• 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:

• 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

Courtesy : Cbseguess.com

Go To Page : 1, 2, 3, 4 , 5 , 6 >>