PL/SQL Based Forms Blocks

Create or replace package employee_pkg is


  type emp_rec is record
    (emp_id             employee.emp_id%type,
     first_name         employee.first_name%type,
     last_name          employee.last_name%type,
     ssn                employee_confidential.ssn%type,
     salary             employee_confidential.salary%type);


  type emp_tbl is table of emp_rec index by binary_integer;


  -- cursor variable to be used in do_query_cur procedure
  type emp_ref is ref cursor return emp_rec;


  -----------------------------
  procedure do_query(p_emp_table     IN OUT emp_tbl,
                     p_emp_id        IN     employee.emp_id%type);


  -----------------------------
  -- second version of query procedure, this time use cursor variable
  --   instead of PL/SQL table
  procedure do_query_cur(p_emp_set   IN OUT emp_ref,
                         p_emp_id    IN     employee.emp_id%type);


  -----------------------------
  procedure do_insert(p_emp_table    IN OUT emp_tbl);


  -----------------------------
  procedure do_update(p_emp_table    IN OUT emp_tbl);


  -----------------------------
  procedure do_delete(p_emp_table    IN OUT emp_tbl);


  -----------------------------
  -- note that do_lock is needed if the form will need to 
  --   perform update or delete 
  procedure do_lock(p_emp_table      IN OUT emp_tbl);


end employee_pkg;




Create or replace package body employee_pkg is


  procedure do_query(p_emp_table     IN OUT emp_tbl,
                     p_emp_id        IN     employee.emp_id%type) is
    cursor emp_cur (p_id   employee.emp_id%type) is
       select e.emp_id, 
              e.first_name,
              e.last_name,
              c.ssn,
              c.salary
         from employee e,
              employee_confidential c
        where e.emp_id = c.emp_id and
              e.emp_id = p_id;
    idx   number := 1;
  begin
    for rec in emp_cur(p_emp_id) loop

  -- populate a PL/SQL table with a record
      p_emp_table(idx) := rec;      
  -- increment index
      idx              := idx + 1;  
    end loop; 
  end; 


  -----------------------------
  -- second version of query procedure, this time use cursor variable
  --   instead of PL/SQL table
  procedure do_query_cur(p_emp_set   IN OUT emp_ref,
                         p_emp_id    IN     employee.emp_id%type) is
  begin
    open p_emp_set for
       select e.emp_id, 
              e.first_name,
              e.last_name,
              c.ssn,
              c.salary
         from employee e,
              employee_confidential c
        where e.emp_id = c.emp_id and
              e.emp_id = p_emp_id;
  end;

  -----------------------------
  procedure do_insert(p_emp_table    IN OUT emp_tbl) is
    numOfRows   number;
  begin
    numOfRows  := p_emp_table.count;
    for idx in 1 .. numOfRows loop
      insert into employee
        (emp_id,
         first_name,
         last_name) values
        (p_emp_table(idx).emp_id,
         p_emp_table(idx).first_name,
         p_emp_table(idx).last_name);

      insert into employee_confidential
        (emp_id,
         ssn,
         salary) values
        (p_emp_table(idx).emp_id,
         p_emp_table(idx).ssn,
         p_emp_table(idx).salary); 
    end loop;
  end ;

  -----------------------------
  procedure do_update(p_emp_table    IN OUT emp_tbl) is
    numOfRows   number;
  begin
    numOfRows  := p_emp_table.count;
    for idx in 1 .. numOfRows loop
      update employee set
          first_name  = p_emp_table(idx).first_name,
          last_name   = p_emp_table(idx).last_name
        where
          emp_id = p_emp_table(idx).emp_id ;

      update employee_confidential set
          ssn     = p_emp_table(idx).ssn,
          salary  = p_emp_table(idx).salary
        where 
          emp_id = p_emp_table(idx).emp_id; 
    end loop;
  end ;

  -----------------------------
  procedure do_delete(p_emp_table    IN OUT emp_tbl) is
    numOfRows   number;
  begin
    numOfRows  := p_emp_table.count;
    for idx in 1 .. numOfRows loop
      delete employee
        where emp_id = p_emp_table(idx).emp_id ;

      delete employee_confidential
        where emp_id = p_emp_table(idx).emp_id; 
    end loop;
  end ;

  -----------------------------
  -- note that do_lock is needed if the form will need to 
  --   perform update or delete
  procedure do_lock(p_emp_table      IN OUT emp_tbl) is
    numOfRows   number;
    id          employee.emp_id%type;
  begin
    numOfRows  := p_emp_table.count;
    for idx in 1 .. numOfRows loop
      select emp_id
        into id
        from employee
       where emp_id = p_emp_table(idx).emp_id
       for update nowait;

      select emp_id
        into id
        from employee_confidential
       where emp_id = p_emp_table(idx).emp_id
       for update nowait;
    end loop;
  end ;

end employee_pkg;