Method overload : Package Body « Function Procedure Packages « Oracle PL/SQL Tutorial





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>
SQL> create or replace PACKAGE genderS as
  2      PROCEDURE get_code_descr(
  3      ain_id                         in  gender.id%TYPE,
  4      aov_code                       out gender.code%TYPE,
  5      aov_description                out gender.description%TYPE);
  6
  7      PROCEDURE get_code_id_descr(
  8      aiov_code                      in out gender.code%TYPE,
  9      aon_id                            out gender.id%TYPE,
 10      aov_description                   out gender.description%TYPE,
 11      aid_on                         in     gender.active_date%TYPE);
 12
 13      PROCEDURE get_code_id_descr(
 14      aiov_code                      in out gender.code%TYPE,
 15      aon_id                            out gender.id%TYPE,
 16      aov_description                   out gender.description%TYPE);
 17
 18      FUNCTION get_id
 19      return                                gender.id%TYPE;
 20
 21      FUNCTION get_id(
 22      aiv_code                       in     gender.code%TYPE)
 23      return                                gender.id%TYPE;
 24
 25  end genderS;
 26  /

Package created.

SQL>
SQL>
SQL> create or replace PACKAGE BODY genderS as
  2  FUNCTION get_id
  3  return                                gender.id%TYPE is
  4  n_id                                  gender.id%TYPE;
  5  begin
  6    select 1 into n_id from SYS.DUAL;
  7    return n_id;
  8  end get_id;
  9
 10
 11  FUNCTION get_id(aiv_code in gender.code%TYPE )
 12  return gender.id%TYPE is
 13
 14  n_id gender.id%TYPE;
 15
 16  begin
 17    select id into n_id from gender where code = aiv_code;
 18
 19    return n_id;
 20  end get_id;
 21
 22  PROCEDURE get_code_descr(
 23  ain_id                         in     gender.id%TYPE,
 24  aov_code                          out gender.code%TYPE,
 25  aov_description                   out gender.description%TYPE ) is
 26
 27  begin
 28    select code,description into aov_code,aov_description
 29    from   gender
 30    where  id = ain_id;
 31  end get_code_descr;
 32
 33
 34  PROCEDURE get_code_id_descr(
 35  aiov_code                      in out gender.code%TYPE,
 36  aon_id                            out gender.id%TYPE,
 37  aov_description                   out gender.description%TYPE,
 38  aid_on                         in     gender.active_date%TYPE ) is
 39
 40  v_code                                gender.code%TYPE;
 41
 42  begin
 43    select id,description into aon_id,aov_description
 44    from   gender
 45    where  code = aiov_code
 46    and    aid_on between active_date and nvl(inactive_date, DATES.d_MAX);
 47  exception
 48    when NO_DATA_FOUND then
 49      select id, code,description
 50      into   aon_id,v_code,aov_description
 51      from   gender
 52      where  code like aiov_code||'%'
 53      and    aid_on between active_date and nvl(inactive_date, DATES.d_MAX);
 54
 55      aiov_code := v_code;
 56  end get_code_id_descr;
 57
 58
 59  PROCEDURE get_code_id_descr(
 60  aiov_code                      in out gender.code%TYPE,
 61  aon_id                            out gender.id%TYPE,
 62  aov_description                   out gender.description%TYPE ) is
 63
 64  begin
 65   get_code_id_descr(
 66    aiov_code,
 67    aon_id,
 68    aov_description,
 69    SYSDATE );
 70  end get_code_id_descr;
 71  end genderS;
 72  /

Package body created.

SQL>
SQL> show errors
No errors.
SQL>
SQL>
SQL> drop table gender;

Table dropped.

SQL>










27.11.Package Body
27.11.1.Package with only one function
27.11.2.Package with two procedures
27.11.3.Package declaration and body
27.11.4.Use of 'get' and 'set' prefixes
27.11.5.Use package method in a procedure
27.11.6.Call function in a Package
27.11.7.Method overload
27.11.8.Package method overloading
27.11.9.Reference method from another package
27.11.10.Package Function with 'PRAGMA AUTONOMOUS_TRANSACTION'
27.11.11.Create a package containing stored procedure DELETE_ORDERS and stored function GET_employee_NAME.
27.11.12.Package initialization.
27.11.13.Use package member variable to pass value