Return column type : Function Return « Function Procedure Packages « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Function Procedure Packages
3. Function Return

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>
SQL> create or replace PACKAGE employee_typeS as
  2      FUNCTION get_id(aiv_code in employee_type.code%TYPE )
  3      return employee_type.id%TYPE;
  4  end employee_typeS;
  5  /

Package created.

SQL>
SQL>
SQL>
SQL> create or replace PACKAGE BODY employee_typeS as
  2      FUNCTION get_id(aiv_code in employee_type.code%TYPE )
  3      return employee_type.id%TYPE is
  4      n_id employee_type.id%TYPE;
  5      begin
  6        select id into n_id from employee_type where code = aiv_code;
  7        return n_id;
  8      end get_id;
  9  end employee_typeS;
 10  /

Package body created.

SQL>
SQL> drop   table employee_type;

Table dropped.

SQL>
SQL>