Practice - 3

Simple Example Program on PL/SQL

1) Simple program

   set serveroutput on
   DECLARE
   num1 number;
   num2 number;
   BEGIN
   num1 := 5;
   num2 :=num1*2;
   dbms_output.put_line('double of num1 is'||num2);
   end;

  Output:  double of num1 is10

2) Calculating the area

   set serveroutput on
  DECLARE
  area number;
  radius number;
  pi constant number := 3.14;
  BEGIN
  radius := 5;
  area := pi*radius*radius;
  dbms_output.put_line('area is'||area);
  end;

  Output:  area is  78.5
 

3) Using IF

  set serveroutput on
  DECLARE
  v_day varchar2(10) :='&day';
  BEGIN
  if(v_day='sunday') then
  dbms_output.put_line('sunday is a holiday');
  else
  dbms_output.put_line('another day');
  end if;
  end;

  Output:
  

old 2: v_day varchar2(10) :='&day'; 

  new 2: v_day varchar2(10) :='mon'; 

  another day 

  PL/SQL procedure successfully completed.

4) Using SELECT

  set serveroutput on
  DECLARE
  v_first employee.fname%type;
  v_lname employee.lname%type;
  v_role employee.role%type;
  BEGIN
  select fname,lname,role into v_first,v_lname,v_role from employee where
  employee_id='ADM001';
 dbms_output.put_line('firstname'||v_first||'lastname'||v_lname||'role'||v_role);
  end;

  Output:
 
To get the output we now first create and insert data into table.
Here we are creating employee24

create table employee24

(

emp_id varchar(20),

fname char(20),

lname char(20),

role char(20)

);

insert into employee24

values

(

'&emp_id',

'&fname',

'&lname',

'&role'

);

select * from employee24

Final Output:

firstnameAdam lastnameEve roleClient
PL/SQL procedure successfully completed.