Practice -3 (continued)

Simple Example Program on PL/SQL

5) Using Loop

  set serveroutput on
  DECLARE
  v_count number(2);
  v_sum number(2) :=0;
  v_avg number(3,1);
  BEGIN
  v_count := 1;
  loop
  v_sum :=v_sum+v_count;
  v_count :=v_count+1;
  dbms_output.put_line('v_count is'||v_count);
  exit when v_count>10;
  end loop;
  v_avg :=v_sum/10;
  dbms_output.put_line('average is'||v_avg);
  end;

  Output:

   v_count is2
   v_count is3
   v_count is4
   v_count is5
   v_count is6
   v_count is7
   v_count is8
   v_count is9
   v_count is10
   v_count is11
   average is5.5
   PL/SQL procedure successfully completed


6) Cursor

  set serveroutput on
  DECLARE
  v_first employee.fname%type;
  v_lname employee.lname%type;
  v_role employee.role%type;
  cursor employee_cur is
  select fname,lname,role from employee where employee_id='ADM001';
  BEGIN
  if not employee_cur%isopen then
  open employee_cur;
  end if;
  loop
  fetch employee_cur into v_first,v_lname,v_role;
  exit when not employee_cur%found;
  dbms_output.put_line('firstname' || v_first || 'lastname' ||v_lname || 'role' || v_role);
  end loop;
  end;

  Output:

Here we use the same table we have created earlier (employee24)

   fnameAdam lnameEve roleClient
   PL/SQL procedure successfully completed
 
7) Creating a procedure

  create procedure sw_emp9(emp_id in employee.employee_id%type,o_first out employee.fname %type,o_last out employee.lname%type)
  is
  begin
  select fname,lname into o_first,o_last from employee where employee_id=emp_id;
  end;

  Output:

8) Calling a procedure

  declare
  v_fname employee.fname%type;
  v_lname employee.lname%type;
  v_id employee.employee_id%type := '&emp_id';
  begin
  search_emp(v_id,v_fname,v_lname);
  dbms_output.put_line('firstname' || v_fname || 'lastname' ||v_lname);
  end;

  Output:

  old 4: v_id employee24.emp_id%type := '&employee_id';
  new 4: v_id employee24.emp_id%type := 'ADM001';

 

9) Creating a trigger

  create trigger employee_insert_before
  before insert on employee
  for each row
  begin
  dbms_output.put_line('before insert of ');
  end;

  Output:  Trigger created