Package for output employee table and log message : Package « PL SQL « Oracle PL / SQL






  1. Oracle PL / SQL
  2. PL SQL
  3. Package

Package for output employee table and log message

    
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    MGR NUMBER(4),
  5                    HIREDATE DATE,
  6                    SAL NUMBER(7, 2),
  7                    COMM NUMBER(7, 2),
  8                    DEPTNO NUMBER(2));

Table created.

SQL>
SQL> INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK',    7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7521, 'WARD',  'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER',  7839, TO_DATE('2-APR-1981',  'DD-MON-YYYY'), 2975, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

1 row created.

SQL>
SQL> create table myLogTable(
  2    username varchar2(30),
  3    date_time timestamp,
  4    message varchar2(4000) );

Table created.

SQL>
SQL>
SQL>
SQL> create or replace
  2  package employee_pkg as
  3    procedure print_ename( p_empno number );
  4    procedure print_sal( p_empno number );
  5  end employee_pkg;
  6  /

Package created.

SQL>
SQL>  create or replace
  2    package body employee_pkg as
  3
  4      procedure log_message( p_message varchar2 ) is
  5      pragma autonomous_transaction;
  6      begin
  7        insert into myLogTable( username, date_time, message )
  8        values ( user, current_date, p_message );
  9        commit;
 10      end log_message;
 11
 12      function get_emp_record( p_empno number ) return emp%rowtype is
 13        l_emp_record emp%rowtype;
 14      begin
 15        log_message( 'Looking for record where EMPNO = ' || p_empno );
 16        select *
 17          into l_emp_record
 18          from emp
 19         where empno = p_empno;
 20        return l_emp_record;
 21      exception
 22        when NO_DATA_FOUND then
 23          return null;
 24      end get_emp_record;
 25
 26      procedure print_data( p_emp_record emp%rowtype,
 27                            p_column varchar2 ) is
 28        l_value varchar2(4000);
 29      begin
 30        if p_emp_record.empno is null then
 31          log_message( 'No Data Found.' );
 32          dbms_output.put_line( 'No Data Found.' );
 33        else
 34          case p_column
 35            when 'ENAME' then
 36              l_value := p_emp_record.ename;
 37            when 'SAL' then
 38              l_value := nvl(p_emp_record.sal,0);
 39            else
 40              l_value := 'Invalid Column';
 41          end case;
 42          log_message( 'About to print ' || p_column || ' = ' || l_value );
 43          dbms_output.put_line( p_column || ' = ' || l_value );
 44        end if;
 45      end print_data;
 46
 47      procedure print_ename( p_empno number ) is
 48      begin
 49        print_data( get_emp_record( p_empno ), 'ENAME' );
 50      end print_ename;
 51
 52      procedure print_sal( p_empno number ) is
 53      begin
 54        print_data( get_emp_record( p_empno ), 'SAL' );
 55      end print_sal;
 56
 57    end employee_pkg;
 58    /

Package body created.

SQL>
SQL>  exec employee_pkg.print_ename( 7781 );
No Data Found.

PL/SQL procedure successfully completed.

SQL>
SQL>  exec employee_pkg.print_ename( 7782 );
ENAME = CLARK

PL/SQL procedure successfully completed.

SQL>
SQL>  select * from myLogTable;

USERNAME                       DATE_TIME
------------------------------ ---------------------------------------------------------------------------
MESSAGE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYS                            11-JUN-08 08.44.45.000000 PM
Looking for record where EMPNO = 7781

SYS                            11-JUN-08 08.44.45.000000 PM
No Data Found.

SYS                            11-JUN-08 08.44.45.000000 PM
Looking for record where EMPNO = 7782


USERNAME                       DATE_TIME
------------------------------ ---------------------------------------------------------------------------
MESSAGE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYS                            11-JUN-08 08.44.45.000000 PM
About to print ENAME = CLARK


SQL>
SQL>  execute employee_pkg.print_ename( 1234 );
No Data Found.

PL/SQL procedure successfully completed.

SQL>
SQL>  execute employee_pkg.print_ename( 7782 );
ENAME = CLARK

PL/SQL procedure successfully completed.

SQL>
SQL>  execute employee_pkg.print_sal( 7782 );
SAL = 2450

PL/SQL procedure successfully completed.

SQL>
SQL> drop table myLogTable;

Table dropped.

SQL> drop table emp;

Table dropped.








Related examples in the same category

1.Create a package declaration and create its body after
2.Create a package level variable
3.call utitilities package
4.Package with package level cursor variable
5.Overloading Packaged Subprograms
6.A PL/SQL package with two methods
7.Package level Exception
8.This package will not compile because the body does not match the specification.
9.dependencies between an anonymous calling block and package runtime state.
10.RESTRICT_REFERENCES pragma.
11.Without RESTRICT_REFERENCES pragma.
12.Persistance of packaged variables.
13.A package with one method
14.Package level variable as global level variable
15.Global definition
16.Student fetch package
17.Cursor operation between package functions
18.The pragma is not valid at the package level.
19.Create package and member cursor
20.Nested package reference
21.Overloaded packages.
22.Overloading based on user defined object types.
23.package RECURSION
24.Crosss reference between two packages
25.Counter package
26.Package initialization.
27.Packages allows forward references, thus opening the possibilities for recursion.
28.Use package level type as global variables
29.Use package member variable to pass value