Oracle PL/SQL - PL SQL Function Procedure Package Create package

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Function Procedure Package
  4. Packages

Introduction

Within packages, you can declare variables or any objects.

You can then control the accessibility: visible only within the package or can be referenced from outside the package.

The following code shows a package to handle login functions.

Some functions are accessible only within the package; others can be seen outside of the package.

Demo

SQL>
SQL> create or replace package pkg_emp is
  2  --The variable is declared in the package specification.
  3  --It is visible both inside and outside the package.
  4-- from   w w w  . j av a2  s . c o  m
  5         gv_current_empNo NUMBER;
  6
  7         procedure p_setCurrentEmpNo (i_empNo NUMBER);
  8         function  f_getCurrentEmpNo return NUMBER;
  9
 10         procedure p_giveRaise (i_pcnt NUMBER);
 11  end;
 12  /

Package created.
SQL> create or replace package body pkg_emp is
  5     gv_LOGUSER_tx VARCHAR2(256);
  9     procedure p_validateUser is
 10     begin
 11          if gv_LOGUSER_tx is null then
 12               raise_application_error(-20999,'no valid user!');
 13          else
 14               if gv_LOGUSER_tx not like 'SCOTT%' then
 15                    raise_application_error(-20999,'not enough privileges!');
 16               end if;
 17          end if;
 18     end;
 19
 20     procedure p_setCurrentEmpNo (i_empno number)is
 21     begin
 22          gv_LOGUSER_tx:=user||'|'||sys_context('userenv','ip_address');
 23          gv_current_empno:=i_empNo;
 24     end;
 25
 26     function f_getCurrentEmpno return NUMBER is
 27     begin
 28          return gv_current_empNo;
 29     end;
 30
 31     procedure p_giveRaise (i_pcnt NUMBER) is
 32     begin
 33          p_validateUser;
 34
 35          update emp
 36           set sal=sal*(i_pcnt/100)+sal
 37          where empno = f_getCurrentEmpno;
 38     end;
 39  end;
 40  /

Package body created.

SQL>

Related Topic