Oracle PL/SQL - Declaring Autonomous Function in Package

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL SQL Statement
  4. Declaring Autonomous Function in Package

Introduction

The following code marks a package function as autonomous.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from   w  w  w  .  ja v a 2s .c o m

SQL> CREATE TABLE emp(
  2  empid NUMBER(6),
  3  first_name VARCHAR2(20),
  4  last_name VARCHAR2(25),
  5  email VARCHAR2(25),
  6  phone_number VARCHAR2(20),
  7  hire_date DATE,
  8  job_id VARCHAR2(10),
  9  salary NUMBER(8,2),
 10  commission_pct NUMBER(2,2),
 11  manager_id NUMBER(6),
 12  department_id NUMBER(4)) ;
SQL>
SQL> INSERT INTO emp VALUES( 100, 'Steven', 'King', 'SKING', '123.123.4567', TO_DATE('17-JUN-1987', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 10);
SQL> INSERT INTO emp VALUES( 200, 'Joe', 'Lee', 'abc', '123.123.9999', TO_DATE('17-JUN-1980', 'dd-MON-yyyy'), 'TESTER', 25000, NULL, NULL, 20);
SQL>
SQL> CREATE OR REPLACE PACKAGE emp_actions AS  -- package specification
  2    FUNCTION raise_salary (emp_id NUMBER, sal_raise NUMBER)
  3    RETURN NUMBER;
  4  END emp_actions;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY emp_actions AS  -- package body
  2     -- code for function raise_salary
  3     FUNCTION raise_salary (emp_id NUMBER, sal_raise NUMBER)
  4     RETURN NUMBER IS
  5       PRAGMA AUTONOMOUS_TRANSACTION;
  6       new_sal NUMBER(8,2);
  7     BEGIN
  8       UPDATE emp SET salary =
  9         salary + sal_raise WHERE empid = emp_id;
 10       COMMIT;
 11       SELECT salary INTO new_sal FROM emp
 12         WHERE empid = emp_id;
 13      RETURN new_sal;
 14    END raise_salary;
 15  END emp_actions;
 16  /

Package body created.

SQL>

Related Topics