Live ORACLE

CREATE OR REPLACE PACKAGE emp_actions

AS -- package specification



  FUNCTION raise_salary(emp_id  NUMBER,sal_raise  NUMBER) RETURN NUMBER;

END emp_actions;

/


CREATE OR REPLACE PACKAGE BODY emp_actions

AS -- package body



  -- code for function raise_salary

  FUNCTION Raise_salary

       (emp_id     NUMBER,

        sal_raise  NUMBER)

  RETURN NUMBER

  IS

    PRAGMA AUTONOMOUS_TRANSACTION;

    new_sal  NUMBER(8,2);

  BEGIN

    UPDATE employees

    SET    salary = salary

                      + sal_raise

    WHERE  employee_id = emp_id;

    COMMIT;

    SELECT salary

    INTO   new_sal

    FROM   employees

    WHERE  employee_id = emp_id;

    RETURN new_sal;

  END raise_salary;

END emp_actions;

/

Declaring an Autonomous Function in a Package

DECLARE

  CURSOR c1 IS

    SELECT employee_id,

           last_name,

           salary,

           rowid

    FROM   employees;

   emp_rec  c1%ROWTYPE;

BEGIN

  OPEN c1;

  

  LOOP

    FETCH c1 INTO emp_rec;

    

    EXIT WHEN c1%NOTFOUND;

    

    IF emp_rec.salary = 0 THEN

      DELETE FROM employees

      WHERE       rowid = emp_rec.rowid;

    END IF;

  END LOOP;

  

  CLOSE c1;

END;

/

Using %ROWTYPE with Cursors reference ROWID

DECLARE

  CURSOR c1 IS

    SELECT last_name,

           job_id,

           rowid

    FROM   employees;

   my_lastname  employees.last_name%TYPE;

   my_jobid     employees.job_id%TYPE;

   my_rowid     UROWID;

BEGIN

  OPEN c1;

  

  LOOP

    FETCH c1 INTO my_lastname,my_jobid,my_rowid;

    

    EXIT WHEN c1%NOTFOUND;

    

    UPDATE employees

    SET    salary = salary * 1.02

    WHERE  rowid = my_rowid;

    

    -- this mimics WHERE CURRENT OF c1

    COMMIT;

  END LOOP;

  

  CLOSE c1;

END;

/

Fetching Across COMMITs Using ROWID

DECLARE

  -- if "FOR UPDATE OF salary" is included on following line, an error is raised

  CURSOR c1 IS

    SELECT *

    FROM   employees;

   emp_rec  employees%ROWTYPE;

BEGIN

  OPEN c1;

 

  LOOP

    FETCH c1 INTO emp_rec; -- FETCH fails on the second iteration with FOR UPDATE

   

    EXIT WHEN c1%NOTFOUND;

   

    IF emp_rec.employee_id = 105 THEN

      UPDATE employees

      SET    salary = salary * 1.05

      WHERE  employee_id = 105;

    END IF;

   

    COMMIT; -- releases locks

 

  END LOOP;

END;

/

Fetching Across Commits

DECLARE

 my_emp_id  NUMBER(6);

  my_job_id  VARCHAR2(10);

  my_sal     NUMBER(8,2);

  CURSOR c1 IS

    SELECT employee_id,

           job_id,

           salary

    FROM   employees

    FOR UPDATE;

BEGIN

 OPEN c1;



 LOOP

   FETCH c1 INTO my_emp_id,my_job_id,my_sal;

 

   IF my_job_id = 'SA_REP' THEN

     UPDATE employees

     SET    salary = salary * 1.02

     WHERE CURRENT OF c1;

   END IF;

 

   EXIT WHEN c1%NOTFOUND;

 END LOOP;

END;

/

Using CURRENT OF to Update the Latest Row Fetched From a Cursor

DECLARE

  daily_order_total    NUMBER(12,2);

   weekly_order_total   NUMBER(12,2);

   monthly_order_total  NUMBER(12,2);

BEGIN

  COMMIT; -- ends previous transaction

 

  SET TRANSACTION READ ONLY NAME 'Calculate Order Totals';

 

  SELECT Sum(order_total)

  INTO   daily_order_total

  FROM   orders

  WHERE  order_date = SYSDATE;

 

  SELECT Sum(order_total)

  INTO   weekly_order_total

  FROM   orders

  WHERE  order_date = SYSDATE - 7;

 

  SELECT Sum(order_total)

  INTO   monthly_order_total

  FROM   orders

  WHERE  order_date = SYSDATE - 30;

 

  COMMIT; -- ends read-only transaction



END;

/

Using SET TRANSACTION to Begin a Read-only Transaction

DROP TABLE emp_name CASCADE CONSTRAINTS PURGE;
DROP INDEX empname_ix;

CREATE TABLE emp_name
AS
SELECT employee_id,
     last_name,
     salary
FROM   employees;

CREATE UNIQUE INDEX empname_ix ON emp_name (
  employee_id);

DECLARE
emp_id        employees.employee_id%TYPE;
emp_lastname  employees.last_name%TYPE;
emp_salary    employees.salary%TYPE;
BEGIN
SELECT employee_id,
     last_name,
     salary
INTO   emp_id,emp_lastname,emp_salary
FROM   employees
WHERE  employee_id = 120;

SAVEPOINT my_savepoint;

UPDATE emp_name
SET    salary = salary * 1.1
WHERE  employee_id = emp_id;

DELETE FROM emp_name
WHERE       employee_id = 130;

SAVEPOINT my_savepoint; -- move my_savepoint to current poin

INSERT INTO emp_name
VALUES     (emp_id,
          emp_lastname,
          emp_salary);
EXCEPTION
WHEN dup_val_on_index THEN
ROLLBACK TO my_savepoint;

dbms_output.Put_line('Transaction rolled back.');
END;
/ 

Transaction rolled back.

PL/SQL procedure successfully completed.

Reusing a SAVEPOINT With ROLLBACK

DROP TABLE emp_name CASCADE CONSTRAINTS PURGE; 
DROP INDEX empname_ix; 

CREATE TABLE emp_name 
 AS 
SELECT employee_id, 
     last_name, 
     salary 
FROM   employees; 

CREATE UNIQUE INDEX empname_ix ON emp_name ( 
  employee_id); 

DECLARE 
emp_id        employees.employee_id%TYPE; 
emp_lastname  employees.last_name%TYPE; 
emp_salary    employees.salary%TYPE; 
BEGIN 
SELECT employee_id, 
     last_name, 
     salary 
INTO   emp_id,emp_lastname,emp_salary 
FROM   employees 
WHERE  employee_id = 120; 
 
UPDATE emp_name 
SET    salary = salary * 1.1 
WHERE  employee_id = emp_id; 
 
DELETE FROM emp_name 
WHERE       employee_id = 130; 
 
SAVEPOINT do_insert; 
 
INSERT INTO emp_name 
VALUES     (emp_id, 
          emp_lastname, 
          emp_salary); 
EXCEPTION 
WHEN dup_val_on_index THEN 
ROLLBACK TO do_insert; 
 
dbms_output.Put_line('Insert has been rolled back'); 
END; 
/

Insert has been rolled back

Using SAVEPOINT With ROLLBACK