Inner function : Code Block « PL SQL Programming « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Programming
3. Code Block

SQL>
SQL>
SQL>
SQL> create table employee (
  2  id                             number,
  3  employee_type_id                 number,
  4  external_id                    varchar2(30),
  5  first_name                     varchar2(30),
  6  middle_name                    varchar2(30),
  7  last_name                      varchar2(30),
  8  name                           varchar2(100),
  9  birth_date                     date  ,
 10  gender_id                      number );

Table created.

SQL>
SQL>
SQL>
SQL> create table gender (
  2  id                             number,
  3  code                           varchar2(30),
  4  description                    varchar2(80),
  5  active_date                    date          default SYSDATE  not null,
  6  inactive_date                  date );

Table created.

SQL>
SQL>
SQL>
SQL> insert into gender ( id, code, description ) values ( 1, 'F', 'Female' );

1 row created.

SQL> insert into gender ( id, code, description ) values ( 2, 'M', 'Male' );

1 row created.

SQL> insert into gender ( id, code, description ) values ( 3, 'U', 'Unknown' );

1 row created.

SQL>
SQL> create table employee_type (
  2  id                             number,
  3  code                           varchar2(30),
  4  description                    varchar2(80),
  5  active_date                    date          default SYSDATE  not null,
  6  inactive_date                  date );

Table created.

SQL>
SQL> insert into employee_type(id,code,description)values(1,'C','Contractor' );

1 row created.

SQL> insert into employee_type(id,code,description)values(2,'E','Employee' );

1 row created.

SQL> insert into employee_type(id,code,description)values(3,'U','Unknown' );

1 row created.

SQL>
SQL>
SQL>
SQL> set serveroutput on size 1000000;
SQL>
SQL> declare
  2  n_count                               number := 0;
  3
  4  FUNCTION add_worker(
  5  aiv_first_name                        employee.first_name%TYPE,
  6  aiv_middle_name                       employee.middle_name%TYPE,
  7  aiv_last_name                         employee.last_name%TYPE,
  8  aid_birth_date                        employee.birth_date%TYPE,
  9  aiv_gender_code                       gender.code%TYPE,
 10  aiv_employee_type_code                employee_type.code%TYPE)
 11  return                                number is
 12
 13  v_name                                employee.name%TYPE;
 14
 15  begin
 16    v_name        := rtrim(aiv_last_name||', '||aiv_first_name||' '||aiv_middle_name);
 17    begin
 18      insert into employee (
 19             id,
 20             employee_type_id,
 21             external_id,
 22             first_name,
 23             middle_name,
 24             last_name,
 25             name,
 26             birth_date,
 27             gender_id )
 28      select 1,
 29             myCursor.id,
 30             lpad(to_char(1), 9, '0'),
 31             aiv_first_name,
 32             aiv_middle_name,
 33             aiv_last_name,
 34             v_name,
 35             aid_birth_date,
 36             c2.id
 37      from   employee_type myCursor,
 38             gender c2
 39      where  myCursor.code = aiv_employee_type_code
 40      and    c2.code = aiv_gender_code
 41      and not exists (
 42        select 1
 43        from   employee x
 44        where  x.name       = v_name
 45        and    x.birth_date = aid_birth_date
 46        and    x.gender_id  = c2.id );
 47
 48      return sql%rowcount;
 49    exception
 50      when OTHERS then
 51        raise_application_error(-20001, SQLERRM||' on insert employee'||' in add_worker');
 52    end;
 53  end add_worker;
 54
 55  begin
 56    n_count := n_count + add_worker('JOHN',   'J.', 'DOE', to_date('19800101', 'YYYYMMDD'), 'M', 'C');
 57    n_count := n_count + add_worker('JANE',   'J.', 'DOE', to_date('19800101', 'YYYYMMDD'), 'F', 'E');
 58    n_count := n_count + add_worker('JOHNNY', 'E.', 'DOE', to_date('19980101', 'YYYYMMDD'), 'M', 'E');
 59    n_count := n_count + add_worker('JANIE',  'E.', 'DOE', to_date('19980101', 'YYYYMMDD'), 'F', 'E');
 60    DBMS_OUTPUT.PUT_LINE(to_char(n_count)||' row(s) inserted.');
 61  end;
 62  /
4 row(s) inserted.

PL/SQL procedure successfully completed.

SQL>
SQL> drop table gender;

Table dropped.

SQL>
SQL> drop table employee;

Table dropped.

SQL>
SQL> drop table employee_type;

Table dropped.

SQL>