Advance PL/SQL programming in Oracle

PROCEDURE:-

Procedure and functions are named PL/SQL programs that are stored in a compiled form in the database. At the time of creation , oracle compiles the procedure or function. If some compilation error if found, then invalid procedure / function is created. The user is reported about this.

Functions take zero or more parameters and return a value. Procedure take zero or more parameter or does not return any value. Both function and procedure can receive or return zero or more values through their parameter.

These procedures are created and then stored as part of a database, just as tables and indexes are stored inside a database. After the stored procedure is executed, the SQL statements run

sequentially on the database server. Some message or data is returned to the user's computer only when the procedure is finished

Syntax:

Create or Replace Procedure <Procedurename>

(<argument> IN <datatype>,…)

(IS/AS)

<variable> declarations;

<constant> declarations;

BEGIN

            <PL/SQL code>;

EXCEPTION

            <Exception PL/SQL block>;

END;

The mode describes whether the parameter can be read form or written  to. Formal parameters can have 3 modes.

IN         - For read only Access

OUT     - For write only Access

IN OUT – For Read-write Access

Example of PROCEDURE:-

set serveroutput on

create or replace procedure findemp

(pempno in emp.empno%type) is

vempno emp.empno%type;

vempname emp.ename%type;

begin

select empno,ename into vempno,vempname from emp where empno=pempno;

dbms_output.put_line(vempname || ' ' || 'exist');

end;

/

For Execute procedure:-

SQL>EXECUTE FINDEMP(7369);

2) create or replace procedure rec (eno emp.empno%type,name out emp.ename%type) IS

BEGIN

   SELECT ename into name

    FROM Emp

   WHERE empno = eno;

END;

/

Call procedure:-

declare

sname emp.ename%type;

begin

rec(7788,sname);

dbms_output.put_line(sname);

end;

/

FUNCTION:-

Unlike procedures, functions can return a value to the caller (procedures cannot return values). This value is returned through the use of the return keyword within the function. One or other way procedure and functions are same except one difference that is mentioned above. Functions can be executed explicitly like procedures.

When a function is created, ORACLE automatically performs the following steps:

1. Compiles the functions

2. Stores the compiled code

3. Stores the function in the database

Oracle performs the following steps to execute a functions :

1. Verify user access

2. Verifies function validity

3. Executes the function

Syntax:-

CREATE OR REPLACE FUNCTION <functionname>

(argument IN datatype,........) RETURN datatype {IS, AS}

variable declaration;

constant declaration;

BEGIN

PL/SQL subprogram body;

EXCEPTION

exception PL/SQL block;

END;

Example:-

For creating a function

clear screen

set serveroutput on

create or replace function tot

(xm1 number,

xm2 number,

xm3 number)

return number as xtot number;

begin

            xtot:=xm1+xm2+xm3;

            return xtot;

end;

/

For calling function:-

clear screen

set serveroutput on

declare

            n number;

            m number;

            mm number;

            tot1 number;

begin

            n:=10;

            m:=10;

            mm:=10;        

            tot1:=tot(n,m,mm);

            dbms_output.put_line(tot1);

end;

/

PACKAGE:-

A package is an object , which holds other objects like procedure, function, cursor, exception etc. within it.

Components of package:-

• Package specification (Package Header)
• Package body

Package Specification:-

• Contains name of the package.
• Contains declaration of procedure, functions, variables, cursors, exceptions etc.
• It does not contain any code for procedure/ function.
• Package elements can appear in any order. Object must be declared in this section before it is referenced.

Syntax:-

CREATE [OR REPLACE] PACKAGE package_name

{AS| IS}

Public_variable_declarations |

Public_exception_declarations |

Public_cursor_declaration |

Function_specifications |

Procedure_specifications

END package name;

Example:-

CREATE OR REPLACE PACKAGE demo

AS

PROCEDURE PRINTF(DATA VARCHAR2);

FUNCTION SCANF RETURN NUMBER;

END demo;

/

Package Body:-

• Package body contains the definition of public objects that are declared in the specification.
• Package body can also have other objects , which are private to the package.
• If package header does not contain any procedure / function then , package body is optional.

Syntax:-

CREATE [OR REPLACE ] PACKAGE BODY package name

{AS | IS}

Public_variable_declarations |

Public_exception_declarations |

Public_cursor_declaration |

Function_specifications |

Procedure_specifications

END [package_name];

Example:-

set serveroutput on

CREATE OR REPLACE PACKAGE  BODY demo

AS

PROCEDURE PRINTF(DATA VARCHAR2)

AS

BEGIN

DBMS_OUTPUT.PUT_LINE(DATA);

END PRINTF;

FUNCTION SCANF RETURN NUMBER

AS

NUM  NUMBER;

BEGIN

      NUM :=&NUM;

      RETURN NUM;

END SCANF;

END demo;                

/

For run the Procedure:-

EXECUTE .<PROCEDURE NAME>(PARAMETER)

Execute demo.printf(‘Dolly’);

• Private objects are not accessible outside the program.
• The variables / constants declared in the package specification can be accessed by any procedure / function within the package.

Triggers

Triggers are program that are executed automatically in response to a change in the database. Oracle allows special type of procedures that are implicitly (automatically) executed when events like INSERT / UPDATE / DELETE occurs. These event procedures are called ‘DATABASE TRIGGERS’.

In short:-

Trigger is used to execute a code on event of database.

Trigger will call automatically when an event fired on database according trigger.

TYPES OF TRIGGER:

ROW TRIGGER:-

This trigger is fired each time a row in the table is affected.

This type of trigger should be used when some action is required when any row of the table is affected. Row trigger can be crated using the ‘FOR EACH ROW’ clause in the ‘CREATE TRIGGER’ command.

STATEMENT TRIGGER:-

This is the default type of triggers. This trigger will be fired once and it is independent of the number of affected rows in table. Even if none of the row is affected , statement trigger will be fired. If WHEN clause is not return then trigger becomes statement trigger.

SYNTAX:-

CREATE [OR REPLACE] TRIGGER <name> [BEFORE / AFTER]

[DELETE / INSERT / UPDATE [OF <col1>,<col2>….]]

ON <table name>

[FOR EACH ROW | WHEN <CONDITION>]

DECLARE

            <Var. / const declaration>

BEGIN

            <PL/SQL statement body>

EXCEPTION

            <exception PL/SQL statement body>

END;

Example:-

1)

clear screen

set serveroutput on

create or replace trigger t1

after update or delete on dolly

for each row

declare

begin

            if updating then

            dbms_output.put_line('update successfully');

            end if;

end;

/

Special IF statement:-

2)

set serveroutput on

create or replace trigger t11

after insert on person

for each row

declare

begin

if inserting then

dbms_output.put_line('record inserted');

elsif updating then

dbms_output.put_line('record updated');

elsif deleting then

dbms_output.put_line('record deleted');

end if;

end;

/

Example of display date of inserting record

3)

CREATE OR REPLACE TRIGGER tr after insert on person

for each row

DECLARE

           

            date1 date;

BEGIN

            date1:= sysdate;

            dbms_output.put_line(' the day and date of updation is ' ||' ' || to_char(date1));

END;