PL-SQL Syntaxes-Examples_Part2.txt

11) Procedures

  declare
    procedure findarea(myrad number) is
       area number;
  begin
       area:=3.14*myrad*myrad;
       dbms_output.put_line('Area is '||area);
  end;
  begin
     findarea(3);
  end;

12) Functions.

  declare
     function findarea(myrad number) return number is
       area number;
  begin
     area:=3.14*myrad*myrad;
     return(area);
  end;
  begin
     dbms_output.put_line('Area is '||findarea(3));
  end;

13) Stored Procedures

   create or replace procedure myproc(myrad number)
   is
   x number;
   begin
      x:=5;
      x:=myrad*10;
      dbms_output.put_line('The output is '||x);
   end;
 

14) Execute Stored Procedures

 
    exec myproc(10);

15) Stored Procedures without declarations.
                                                                                                   

  create or replace procedure myproc(myrad number) is
  begin
     dbms_output.put_line('The output is '||myrad*10);
  end;

16) Creation of triggers

  create or replace trigger trig1
  after insert
  on tea1
  for each row
  begin
       dbms_output.put_line('U have inserted one record');
  end;

Trigger created.

17) Altering of triggers.

 alter trigger trig1 disable;

 

21) Trigger for day.
    create or replace trigger trig190
    before insert
    on emp
    begin
      if(to_char(sysdate,'DY')='FRI')
      then
        dbms_output.put_line('Sorry , not allowed on Friday');
      end if;
    end;

22) Raise Application Error.

    create or replace trigger trig190
    before insert
    on emp
    begin
      if(to_char(sysdate,'DY')='FRI')
      then
         raise_application_error(-20200,'Not allowed');
      end if;
    end;

Trigger to prevent any person with A or a from getting inserted.

create or replace trigger trig_a
before insert
on emp
for each row
begin
   if(:new.ename like 'A%' or  :new.ename like 'a%')
   then
      raise_application_error(-20678,'U cannot insert with A');
   end if;
end;

 

23) Granting execute permissions to Stored Procedures.

grant all on myproc to trng100;

24) User Records

  declare
    type myrecord is record
    (
       name emp.ename%TYPE,
       salary emp.sal%TYPE
    );
    myr myrecord;
    begin
    select ename,sal into myr from emp where ename='ADAMS';
    dbms_output.put_line(myr.name);
  end;

25) PL/SQL tables

    declare
      type mytable is table of varchar(20)
        index by binary_integer;
      mt mytable;
    begin
      mt(1):='Software';
      mt(2):='Hardware';
      dbms_output.put_line(mt.count());
   end;

26) Packages

   create or replace package circle as
       function area(radius number) return number;
       function perimeter(radius number) return number;
   end;
 

   create or replace package body circle as
       function area(radius number) return number is
       begin
          return(3.14*radius*radius);
       end;
       function perimeter(radius number) return number is
       begin
         return(2*3.14*radius);
       end;
   end;

    begin
       dbms_output.put_line(circle.area(10));
    end;

28) Triggers for Audit Operations  (Reference)

create table  trigger_audit
(
   serial_num  number(10) primary key,
   name varchar(10),
   operation varchar(10),
   trans_date date
);

Trigger for Auditing.

 create or replace trigger trig1_audit
 after insert or delete or update
 on emp
 declare
    username varchar(20);
    trans_time varchar(20);
    status varchar(20);
 begin
    if(inserting)
    then
               status := 'INSERT';
    end if;
    if(updating)
    then
               status := 'UPDATE';
    end if;
    if(deleting)
    then
               status := 'DELETE';
    end if;
    select user  into username from dual;
    select to_char(sysdate,'dd-mon-yyyy') into trans_time from dual;
    insert into trigger_audit
    values(seq1_audit.nextval,username,status,trans_time);
 end;