Live ORACLE

DROP TABLE emp_name CASCADE CONSTRAINTS PURGE;


DROP TABLE emp_sal CASCADE CONSTRAINTS PURGE;


DROP TABLE emp_job CASCADE CONSTRAINTS PURGE;


DROP INDEX empname_ix;


DROP INDEX empsal_ix;


DROP INDEX empjobid_ix;


CREATE TABLE emp_name
AS
SELECT employee_id,
       last_name
FROM   employees;


CREATE UNIQUE INDEX empname_ix ON emp_name (employee_id);


CREATE TABLE emp_sal
AS
SELECT employee_id,
       salary
FROM   employees;


CREATE UNIQUE INDEX empsal_ix ON emp_sal (employee_id);


CREATE TABLE emp_job
AS
SELECT employee_id,
       job_id
FROM   employees;


CREATE UNIQUE INDEX empjobid_ix ON emp_job (employee_id);


DECLARE

emp_id        NUMBER(6);

 emp_lastname  VARCHAR2(25);

 emp_salary    NUMBER(8,2);

 emp_jobid     VARCHAR2(10);

BEGIN

SELECT employee_id,

       last_name,

       salary,

       job_id

INTO   emp_id,emp_lastname,emp_salary,emp_jobid

FROM   employees

WHERE  employee_id = 120;



INSERT INTO emp_name

VALUES     (emp_id,

            emp_lastname);



INSERT INTO emp_sal

VALUES     (emp_id,

            emp_salary);



INSERT INTO emp_job

VALUES     (emp_id,

            emp_jobid);

EXCEPTION

WHEN dup_val_on_index THEN

  ROLLBACK;



  dbms_output.Put_line('Inserts have been rolled back');

END;

/

Inserts have been rolled back

Using ROLLBACK

DROP TABLE accounts CASCADE CONSTRAINTS PURGE;


CREATE TABLE accounts (

  account_id NUMBER(6),

  balance    NUMBER(10,2));


INSERT INTO accounts

VALUES     (7715,

            6350.00);


INSERT INTO accounts

VALUES     (7720,

            5100.50);


DECLARE

  transfer  NUMBER(8,2) := 250;

BEGIN

  UPDATE accounts

  SET    balance = balance - transfer

  WHERE  account_id = 7715;

 

  UPDATE accounts

  SET    balance = balance + transfer

  WHERE  account_id = 7720;

 

  COMMIT COMMENT 'Transfer From 7715 to 7720' WRITE IMMEDIATE NOWAIT;

END;

/

Using COMMIT With the WRITE Clause

DECLARE

 TYPE emp_cur_typ IS REF CURSOR;

  emp_cur    EMP_CUR_TYP;

  dept_name  departments.department_name%TYPE;

  emp_name   employees.last_name%TYPE;

  CURSOR c1 IS

    SELECT department_name,

           -- second item in the result set is another result set,

           -- which is represented as a ref cursor and labelled "employees".

           CURSOR (SELECT e.last_name

                   FROM   employees e

                   WHERE  e.department_id = d.department_id) employees

    FROM   departments d

    WHERE  department_name LIKE 'A%';

BEGIN

 OPEN c1;



 LOOP

   FETCH c1 INTO dept_name,emp_cur;

 

   EXIT WHEN c1%NOTFOUND;

 

   dbms_output.Put_line('Department: '

                        ||dept_name);

 

   -- for each row in the result set, the result set from a subquery is processed

   -- the set could be passed to a procedure for processing rather than the loop

   LOOP

     FETCH emp_cur INTO emp_name;

   

     EXIT WHEN emp_cur%NOTFOUND;

   

     dbms_output.Put_line('-- Employee: '

                          ||emp_name);

   END LOOP;

 END LOOP;



 CLOSE c1;

END;

/

Department: Administration
-- Employee: Whalen
Department: Accounting
-- Employee: Higgins
-- Employee: Gietz

PL/SQL procedure successfully completed.

Using a Cursor Expression

CREATE OR REPLACE PACKAGE emp_data

AS

  TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;

 

  -- emp_cv EmpCurTyp; -- not allowed

  PROCEDURE open_emp_cv;

END emp_data;

/


CREATE OR REPLACE PACKAGE BODY emp_data

AS

  -- emp_cv EmpCurTyp; -- not allowed

  PROCEDURE Open_emp_cv

  IS

    emp_cv  EMPCURTYP; -- this is legal

 

  BEGIN

    OPEN emp_cv FOR

      SELECT *

      FROM   employees;

  END open_emp_cv;

END emp_data;

/

Declaration of Cursor Variables in a Package

DECLARE

TYPE empcurtyp IS REF CURSOR;

 TYPE namelist IS TABLE OF employees.last_name%TYPE;

 TYPE sallist IS TABLE OF employees.salary%TYPE;

 emp_cv  EMPCURTYP;

 names   NAMELIST;

 sals    SALLIST;

BEGIN

OPEN emp_cv FOR

  SELECT last_name,

         salary

  FROM   employees

  WHERE  job_id = 'SA_REP';



FETCH emp_cv BULK COLLECT INTO names,sals;



CLOSE emp_cv;



-- loop through the names and sals collections

FOR i IN names.FIRST.. names.LAST LOOP

  dbms_output.Put_line('Name = '

                       ||Names(i)

                       ||', salary = '

                       ||Sals(i));

END LOOP;

END;

/

Name = Tucker, salary = 10000
Name = Bernstein, salary = 9500
Name = Hall, salary = 9000
Name = Olsen, salary = 8000
Name = Cambrault, salary = 7500
Name = Tuvault, salary = 7000
Name = King, salary = 10000
Name = Sully, salary = 9500
Name = McEwen, salary = 9000
Name = Smith, salary = 8000
Name = Doran, salary = 7500
Name = Sewall, salary = 7000
Name = Vishney, salary = 10500
Name = Greene, salary = 9500
Name = Marvins, salary = 7200
Name = Lee, salary = 6800
Name = Ande, salary = 6400
Name = Banda, salary = 6200
Name = Ozer, salary = 11500
Name = Bloom, salary = 10000
Name = Fox, salary = 9600
Name = Smith, salary = 7400
Name = Bates, salary = 7300
Name = Kumar, salary = 6100
Name = Abel, salary = 11000
Name = Hutton, salary = 8800
Name = Taylor, salary = 8600
Name = Livingston, salary = 8400
Name = Grant, salary = 7000
Name = Johnson, salary = 6200

PL/SQL procedure successfully completed.

Fetching from a Cursor Variable into Collections

DECLARE

TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;

 emp_cv   EMPCURTYP;

 emp_rec  employees%ROWTYPE;

BEGIN

OPEN emp_cv FOR

  SELECT *

  FROM   employees

  WHERE  employee_id < 120;



LOOP

  FETCH emp_cv INTO emp_rec; -- fetch from cursor variable



  EXIT WHEN emp_cv%NOTFOUND; -- exit when last row is fetched



  -- process data record

  dbms_output.Put_line('Name = '

                       ||emp_rec.first_name

                       ||' '

                       ||emp_rec.last_name);

END LOOP;



CLOSE emp_cv;

END;

/

Name = Steven King
Name = Neena Kochhar
Name = Lex De Haan
Name = Alexander Hunold
Name = Bruce Ernst
Name = David Austin
Name = Valli Pataballa
Name = Diana Lorentz
Name = Nancy Greenberg
Name = Daniel Faviet
Name = John Chen
Name = Ismael Sciarra
Name = Jose Manuel Urman
Name = Luis Popp
Name = Den Raphaely
Name = Alexander Khoo
Name = Shelli Baida
Name = Sigal Tobias
Name = Guy Himuro
Name = Karen Colmenares

PL/SQL procedure successfully completed.

Fetching from a Cursor Variable into a Record

CREATE OR REPLACE PACKAGE admin_data

AS

  TYPE gencurtyp IS REF CURSOR;

 

  PROCEDURE open_cv(generic_cv  IN OUT GENCURTYP,choice  INT);

END admin_data;

/


CREATE OR REPLACE PACKAGE BODY admin_data

AS

  PROCEDURE Open_cv

       (generic_cv  IN OUT GENCURTYP,

        choice      INT)

  IS

  BEGIN

    IF choice = 1 THEN

      OPEN generic_cv FOR

        SELECT *

        FROM   employees;

    ELSIF choice = 2 THEN

      OPEN generic_cv FOR

        SELECT *

        FROM   departments;

    ELSIF choice = 3 THEN

      OPEN generic_cv FOR

        SELECT *

        FROM   jobs;

    END IF;

  END;

END admin_data;

/

Cursor Variable with Different Return Types

CREATE OR REPLACE PACKAGE emp_data

AS

  TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;

 

  PROCEDURE open_emp_cv(emp_cv  IN OUT EMPCURTYP,choice  INT);

END emp_data;

/


CREATE OR REPLACE PACKAGE BODY emp_data

AS

  PROCEDURE Open_emp_cv

       (emp_cv  IN OUT EMPCURTYP,

        choice  INT)

  IS

  BEGIN

    IF choice = 1 THEN

      OPEN emp_cv FOR

        SELECT *

        FROM   employees

        WHERE  commission_pct IS NOT NULL;

    ELSIF choice = 2 THEN

      OPEN emp_cv FOR

        SELECT *

        FROM   employees

        WHERE  salary > 2500;

    ELSIF choice = 3 THEN

      OPEN emp_cv FOR

        SELECT *

        FROM   employees

        WHERE  department_id = 100;

    END IF;

  END;

END emp_data;

/

Stored Procedure to Open Ref Cursors with Different Queries

CREATE OR REPLACE PACKAGE emp_data

AS

  TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;

 

  PROCEDURE open_emp_cv(emp_cv  IN OUT EMPCURTYP);

END emp_data;

/


CREATE OR REPLACE PACKAGE BODY emp_data

AS

  PROCEDURE Open_emp_cv

       (emp_cv  IN OUT EMPCURTYP)

  IS

  BEGIN

    OPEN emp_cv FOR

      SELECT *

      FROM   employees;

  END open_emp_cv;

END emp_data;

/

Stored Procedure to Open a Ref Cursor

DECLARE

  TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;

   emp_cv  EMPCURTYP;

BEGIN

  IF NOT emp_cv%ISOPEN THEN -- open cursor variable

 

    OPEN emp_cv FOR

      SELECT *

      FROM   employees;

  END IF;

 

  CLOSE emp_cv;

END;

/

Checking If a Cursor Variable is Open

DECLARE

 TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;

  emp  EMPCURTYP;

  -- after result set is built, process all the rows inside a single procedure

  -- rather than calling a procedure for each row

  PROCEDURE Process_emp_cv

       (emp_cv  IN EMPCURTYP)

  IS

    person  employees%ROWTYPE;

  BEGIN

    dbms_output.Put_line('-----');

  

    dbms_output.Put_line('Here are the names from the result set:');

  

    LOOP

      FETCH emp_cv INTO person;

    

      EXIT WHEN emp_cv%NOTFOUND;

    

      dbms_output.Put_line('Name = '

                           ||person.first_name

                           ||' '

                           ||person.last_name);

    END LOOP;

  END;

BEGIN

 -- First find 10 arbitrary employees.

 OPEN emp FOR

   SELECT *

   FROM   employees

   WHERE  ROWNUM < 11;



 Process_emp_cv(emp);



 CLOSE emp;



 -- find employees matching a condition.

 OPEN emp FOR

   SELECT *

   FROM   employees

   WHERE  last_name LIKE 'R%';



 Process_emp_cv(emp);



 CLOSE emp;

END;

/

-----
Here are the names from the result set:
Name = Donald OConnell
Name = Douglas Grant
Name = Jennifer Whalen
Name = Michael Hartstein
Name = Pat Fay
Name = Susan Mavris
Name = Hermann Baer
Name = Shelley Higgins
Name = William Gietz
Name = Steven King
-----
Here are the names from the result set:
Name = Trenna Rajs
Name = Den Raphaely
Name = Michael Rogers
Name = John Russell

PL/SQL procedure successfully completed.

Passing a REF CURSOR as a Parameter

DECLARE

 emp_job     employees.job_id%TYPE := 'ST_CLERK';

  emp_salary  employees.salary%TYPE := 3000;

  my_record   employees%ROWTYPE;

  CURSOR c1(job VARCHAR2,

             max_wage NUMBER) IS

    SELECT *

    FROM   employees

    WHERE  job_id = job

           AND salary > max_wage;

BEGIN

 -- Any of the following statements opens the cursor:

 -- OPEN c1('ST_CLERK', 3000); OPEN c1('ST_CLERK', emp_salary);

 -- OPEN c1(emp_job, 3000); OPEN c1(emp_job, emp_salary);

 OPEN c1(emp_job,emp_salary);



 LOOP

   FETCH c1 INTO my_record;

 

   EXIT WHEN c1%NOTFOUND;

 

   -- process data record

   dbms_output.Put_line('Name = '

                        ||my_record.last_name

                        ||', salary = '

                        ||my_record.salary

                        ||', Job Id = '

                        ||my_record.job_id);

 END LOOP;

END;

/

Name = Nayer, salary = 3200, Job Id = ST_CLERK
Name = Bissot, salary = 3300, Job Id = ST_CLERK
Name = Mallin, salary = 3300, Job Id = ST_CLERK
Name = Ladwig, salary = 3600, Job Id = ST_CLERK
Name = Stiles, salary = 3200, Job Id = ST_CLERK
Name = Rajs, salary = 3500, Job Id = ST_CLERK
Name = Davies, salary = 3100, Job Id = ST_CLERK

PL/SQL procedure successfully completed.

Passing Parameters to Explicit Cursors

DECLARE

  CURSOR c1(job VARCHAR2,

             max_wage NUMBER) IS

    SELECT *

    FROM   employees

    WHERE  job_id = job

           AND salary > max_wage;

BEGIN

  FOR person IN c1('CLERK',3000) LOOP

    -- process data record

    dbms_output.Put_line('Name = '

                         ||person.last_name

                         ||', salary = '

                         ||person.salary

                         ||', Job Id = '

                         ||person.job_id);

  END LOOP;

END;

/

Passing Parameters to a Cursor FOR Loop

DECLARE

 -- For each department, find the average salary. Then find all the employees in

 -- that department making more than that average salary.

 CURSOR c1 IS

   SELECT   department_id,

            last_name,

            salary

   FROM     employees t

   WHERE    salary > (SELECT Avg(salary)

                      FROM   employees

                      WHERE  t.department_id = department_id)

   ORDER BY department_id;

BEGIN

 FOR person IN c1 LOOP

   dbms_output.Put_line('Making above-average salary = '

                        ||person.last_name);

 END LOOP;

END;

/

Making above-average salary = Hartstein
Making above-average salary = Raphaely
Making above-average salary = Weiss
Making above-average salary = Fripp
Making above-average salary = Kaufling
Making above-average salary = Vollman
Making above-average salary = Mourgos
Making above-average salary = Ladwig
Making above-average salary = Rajs
Making above-average salary = Sarchand
Making above-average salary = Bull
Making above-average salary = Chung
Making above-average salary = Dilly
Making above-average salary = Bell
Making above-average salary = Everett
Making above-average salary = Hunold
Making above-average salary = Ernst
Making above-average salary = Russell
Making above-average salary = Partners
Making above-average salary = Errazuriz
Making above-average salary = Cambrault
Making above-average salary = Zlotkey
Making above-average salary = Tucker
Making above-average salary = Bernstein
Making above-average salary = Hall
Making above-average salary = King
Making above-average salary = Sully
Making above-average salary = McEwen
Making above-average salary = Vishney
Making above-average salary = Greene
Making above-average salary = Ozer
Making above-average salary = Bloom
Making above-average salary = Fox
Making above-average salary = Abel
Making above-average salary = King
Making above-average salary = Greenberg
Making above-average salary = Faviet
Making above-average salary = Higgins

PL/SQL procedure successfully completed.

Using a Correlated Subquery

DECLARE

CURSOR c1 IS

  SELECT t1.department_id,

         department_name,

         staff

  FROM   departments t1,

         (SELECT   department_id,

                   Count(* ) AS staff

          FROM     employees

          GROUP BY department_id) t2

  WHERE  t1.department_id = t2.department_id

         AND staff >= 5;

BEGIN

FOR dept IN c1 LOOP

  dbms_output.Put_line('Department = '

                       ||dept.department_name

                       ||', staff = '

                       ||dept.staff);

END LOOP;

END;

/

Department = Shipping, staff = 45
Department = Finance, staff = 6
Department = Sales, staff = 34
Department = IT, staff = 5
Department = Purchasing, staff = 6

PL/SQL procedure successfully completed.

Using a Subquery in a FROM Clause

DECLARE

CURSOR c1 IS

  -- main query returns only rows where the salary is greater than the average

  SELECT employee_id,

         last_name

  FROM   employees

  WHERE  salary > (SELECT Avg(salary)

                   FROM   employees);

 CURSOR c2 IS

   -- subquery returns all the rows in descending order of salary

   -- main query returns just the top 10 highest-paid employees

   SELECT *

   FROM   (SELECT   last_name,

                    salary

           FROM     employees

           ORDER BY salary DESC,

                    last_name)

   WHERE  ROWNUM < 11;

BEGIN

FOR person IN c1 LOOP

  dbms_output.Put_line('Above-average salary: '

                       ||person.last_name);

END LOOP;



FOR person IN c2 LOOP

  dbms_output.Put_line('Highest paid: '

                       ||person.last_name

                       ||' $'

                       ||person.salary);

END LOOP;

-- subquery identifies a set of rows to use with CREATE TABLE or INSERT

END;

/

Above-average salary: Hartstein
Above-average salary: Mavris
Above-average salary: Baer
Above-average salary: Higgins
Above-average salary: Gietz
Above-average salary: King
Above-average salary: Kochhar
Above-average salary: De Haan
Above-average salary: Hunold
Above-average salary: Greenberg
Above-average salary: Faviet
Above-average salary: Chen
Above-average salary: Sciarra
Above-average salary: Urman
Above-average salary: Popp
Above-average salary: Raphaely
Above-average salary: Weiss
Above-average salary: Fripp
Above-average salary: Kaufling
Above-average salary: Vollman
Above-average salary: Russell
Above-average salary: Partners
Above-average salary: Errazuriz
Above-average salary: Cambrault
Above-average salary: Zlotkey
Above-average salary: Tucker
Above-average salary: Bernstein
Above-average salary: Hall
Above-average salary: Olsen
Above-average salary: Cambrault
Above-average salary: Tuvault
Above-average salary: King
Above-average salary: Sully
Above-average salary: McEwen
Above-average salary: Smith
Above-average salary: Doran
Above-average salary: Sewall
Above-average salary: Vishney
Above-average salary: Greene
Above-average salary: Marvins
Above-average salary: Lee
Above-average salary: Ozer
Above-average salary: Bloom
Above-average salary: Fox
Above-average salary: Smith
Above-average salary: Bates
Above-average salary: Abel
Above-average salary: Hutton
Above-average salary: Taylor
Above-average salary: Livingston
Above-average salary: Grant
Highest paid: King $24000
Highest paid: De Haan $17000
Highest paid: Kochhar $17000
Highest paid: Russell $14000
Highest paid: Partners $13500
Highest paid: Hartstein $13000
Highest paid: Errazuriz $12000
Highest paid: Greenberg $12000
Highest paid: Higgins $12000
Highest paid: Ozer $11500

PL/SQL procedure successfully completed.

Using a Subquery in a Cursor

BEGIN

FOR item IN (SELECT first_name

                    ||' '

                    ||last_name AS full_name,

                    salary * 10 AS dream_salary

             FROM   employees

             WHERE  ROWNUM <= 5)

LOOP

  dbms_output.Put_line(item.full_name

                       ||' dreams of making '

                       ||item.dream_salary);

END LOOP;

END;

/

Donald OConnell dreams of making 26000
Douglas Grant dreams of making 26000
Jennifer Whalen dreams of making 44000
Michael Hartstein dreams of making 130000
Pat Fay dreams of making 60000

PL/SQL procedure successfully completed.

Using an Alias For Expressions in a Query

DECLARE

 CURSOR c1 IS

   SELECT last_name

   FROM   employees

   WHERE  ROWNUM < 11;

  NAME  employees.last_name%TYPE;

BEGIN

 OPEN c1;



 LOOP

   FETCH c1 INTO NAME;

 

   EXIT WHEN c1%NOTFOUND

              OR c1%NOTFOUND IS NULL;

 

   dbms_output.Put_line(c1%ROWCOUNT

                        ||'. '

                        ||NAME);

 

   IF c1%ROWCOUNT = 5 THEN

     dbms_output.Put_line('--- Fetched 5th record ---');

   END IF;

 END LOOP;



 CLOSE c1;

END;

/

1. Abel
2. Ande
3. Atkinson
4. Austin
5. Baer
--- Fetched 5th record ---
6. Baida
7. Banda
8. Bates
9. Bell
10. Bernstein

PL/SQL procedure successfully completed.

Using %ROWCOUNT

DECLARE

CURSOR c1 IS

  SELECT last_name,

         salary

  FROM   employees

  WHERE  ROWNUM < 11;

 my_ename   employees.last_name%TYPE;

 my_salary  employees.salary%TYPE;

BEGIN

OPEN c1;



LOOP

  FETCH c1 INTO my_ename,my_salary;



  IF c1%NOTFOUND THEN -- fetch failed, so exit loop



    -- Another form of this test is "EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;"

    EXIT;

  ELSE -- fetch succeeded



    dbms_output.Put_line('Name = '

                         ||my_ename

                         ||', salary = '

                         ||my_salary);

  END IF;

END LOOP;

END;

/

Name = OConnell, salary = 2600
Name = Grant, salary = 2600
Name = Whalen, salary = 4400
Name = Hartstein, salary = 13000
Name = Fay, salary = 6000
Name = Mavris, salary = 6500
Name = Baer, salary = 10000
Name = Higgins, salary = 12000
Name = Gietz, salary = 8300
Name = King, salary = 24000

PL/SQL procedure successfully completed.

Using %NOTFOUND

DECLARE

  CURSOR c1 IS

    SELECT last_name,

           salary

    FROM   employees

    WHERE  ROWNUM < 11;

   the_name    employees.last_name%TYPE;

   the_salary  employees.salary%TYPE;

BEGIN

  IF c1%ISOPEN = false THEN -- cursor was not already open

 

    OPEN c1;

  END IF;

 

  FETCH c1 INTO the_name,the_salary;

 

  CLOSE c1;

END;

/

Using %ISOPEN

DECLARE

CURSOR c1 IS

SELECT last_name,

       salary

FROM   employees

WHERE  ROWNUM < 11;

my_ename   employees.last_name%TYPE;

my_salary  employees.salary%TYPE;

BEGIN

OPEN c1;



LOOP

FETCH c1 INTO my_ename,my_salary;



IF c1%FOUND THEN -- fetch succeeded



  dbms_output.Put_line('Name = '

                       ||my_ename

                       ||', salary = '

                       ||my_salary);

ELSE -- fetch failed, so exit loop



  EXIT;

END IF;

END LOOP;

END;

/

Name = OConnell, salary = 2600
Name = Grant, salary = 2600
Name = Whalen, salary = 4400
Name = Hartstein, salary = 13000
Name = Fay, salary = 6000
Name = Mavris, salary = 6500
Name = Baer, salary = 10000
Name = Higgins, salary = 12000
Name = Gietz, salary = 8300
Name = King, salary = 24000

PL/SQL procedure successfully completed.

Using %FOUND