Procedure in PL/SQL

Syntax:

PROCEDURE name [ARGUMENT LIST]  {IS,AS}

variable declarations

BEGIN

program code

END;

Example:

PROCEDURE swap (A IN OUT NUMBER, B IN OUT NUMBER)

IS

temp NUMBER;

BEGIN

temp :=A;

A :=B;

B :=temp;

END;

Trigger in PL/SQL

Syntax for creating a database trigger:

CREATE OR REPLACE TRIGGER trigger-name

{BEFORE |AFTER } verb-list ON table-name

[FOR EACH ROW[WHEN condition]]

DECLARE

declaration

BEGIN

PL/SQL code

END;

Example:

create or replace trigger check_salary

before insert or update of sal, job

on EMP

for each row when(new.job!=director)

Declare

minsal number;

maxsal number;

begin

select min_sal, max_sal into minsal, maxsal

From salary_mast where job=:new.job;

if(:new-sal<minsal or :new-sal>maxsal)

Then

Message (‘salary out of range’);

end If;

End;