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

Home
Oracle PL/SQL Tutorial
1.Introduction
2.Query Select
3.Set
4.Insert Update Delete
5.Sequences
6.Table
7.Table Joins
8.View
9.Index
10.SQL Data Types
11.Character String Functions
12.Aggregate Functions
13.Date Timestamp Functions
14.Numerical Math Functions
15.Conversion Functions
16.Analytical Functions
17.Miscellaneous Functions
18.Regular Expressions Functions
19.Statistical Functions
20.Linear Regression Functions
21.PL SQL Data Types
22.PL SQL Statements
23.PL SQL Operators
24.PL SQL Programming
25.Cursor
26.Collections
27.Function Procedure Packages
28.Trigger
29.SQL PLUS Session Environment
30.System Tables Data Dictionary
31.System Packages
32.Object Oriented
33.XML
34.Large Objects
35.Transaction
36.User Privilege
Oracle PL/SQL Tutorial » Function Procedure Packages » Package Body 




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' );

row created.

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

row created.

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

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 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
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.