Monday, 19 May 2014

PL/SQL - Optimization & Tuning --2

--Example 12–26 DELETE WITH RETURN BULK COLLECT INTO IN FORALL STATEMENT
DROP TABLE emp_temp;

CREATE TABLE emp_temp
AS
     SELECT *
       FROM employees
   ORDER BY employee_id, department_id;

DECLARE
   TYPE NumList IS TABLE OF NUMBER;

   depts   NumList := NumList (10, 20, 30);

   TYPE enum_t IS TABLE OF employees.employee_id%TYPE;

   e_ids   enum_t;

   TYPE dept_t IS TABLE OF employees.department_id%TYPE;

   d_ids   dept_t;
BEGIN
   FORALL j IN depts.FIRST .. depts.LAST
      DELETE FROM emp_temp
            WHERE department_id = depts (j)
        RETURNING employee_id, department_id
             BULK COLLECT INTO e_ids, d_ids;

   DBMS_OUTPUT.PUT_LINE ('Deleted ' || SQL%ROWCOUNT || ' rows:');

   FOR i IN e_ids.FIRST .. e_ids.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE (
         'Employee #' || e_ids (i) || ' from dept #' || d_ids (i));
   END LOOP;
END;
/


--Example 12–27 DELETE with RETURN BULK COLLECT INTO in FOR LOOP Statement

DECLARE
   TYPE NumList IS TABLE OF NUMBER;

   depts   NumList := NumList (10, 20, 30);

   TYPE enum_t IS TABLE OF employees.employee_id%TYPE;

   e_ids   enum_t;

   TYPE dept_t IS TABLE OF employees.department_id%TYPE;

   d_ids   dept_t;
BEGIN
   FOR j IN depts.FIRST .. depts.LAST
   LOOP
      DELETE FROM emp_temp
            WHERE department_id = depts (j)
        RETURNING employee_id, department_id
             BULK COLLECT INTO e_ids, d_ids;
   END LOOP;

   DBMS_OUTPUT.PUT_LINE ('Deleted ' || SQL%ROWCOUNT || ' rows:');

   FOR i IN e_ids.FIRST .. e_ids.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE (
         'Employee #' || e_ids (i) || ' from dept #' || d_ids (i));
   END LOOP;
END;
/


--Example 12–28 Anonymous BLOCK BULK-Binds Input HOST ARRAY

BEGIN
   FORALL i IN :LOWER .. :UPPER
DELETE FROM employees
WHERE department_id = :depts( i);
END;
/


--Example 12–29 Creating AND Invoking PIPELINED TABLE FUNCTION

CREATE OR REPLACE PACKAGE pkg1
   AUTHID DEFINER
AS
   TYPE numset_t IS TABLE OF NUMBER;

   FUNCTION f1 (x NUMBER)
      RETURN numset_t
      PIPELINED;
END pkg1;
/

CREATE OR REPLACE PACKAGE BODY pkg1
AS
   -- FUNCTION f1 returns a collection of elements (1,2,3,... x)
   FUNCTION f1 (x NUMBER)
      RETURN numset_t
      PIPELINED
   IS
   BEGIN
      FOR i IN 1 .. x
      LOOP
         PIPE ROW (i);
      END LOOP;

      RETURN;
   END f1;
END pkg1;
/

SELECT * FROM TABLE (pkg1.f1 (5));


--Example 12–30 PIPELINED TABLE FUNCTION TRANSFORMS EACH ROW TO Two ROWS

CREATE OR REPLACE PACKAGE refcur_pkg
   AUTHID DEFINER
IS
   TYPE refcur_t IS REF CURSOR
      RETURN employees%ROWTYPE;

   TYPE outrec_typ IS RECORD
   (
      var_num     NUMBER (6),
      var_char1   VARCHAR2 (30),
      var_char2   VARCHAR2 (30)
   );

   TYPE outrecset IS TABLE OF outrec_typ;

   FUNCTION f_trans (p refcur_t)
      RETURN outrecset
      PIPELINED;
END refcur_pkg;
/

CREATE OR REPLACE PACKAGE BODY refcur_pkg
IS
   FUNCTION f_trans (p refcur_t)
      RETURN outrecset
      PIPELINED
   IS
      out_rec   outrec_typ;
      in_rec    p%ROWTYPE;
   BEGIN
      LOOP
         FETCH p INTO in_rec;                                     -- input row

         EXIT WHEN p%NOTFOUND;
         out_rec.var_num := in_rec.employee_id;
         out_rec.var_char1 := in_rec.first_name;
         out_rec.var_char2 := in_rec.last_name;
         PIPE ROW (out_rec);                   -- first transformed output row
         out_rec.var_char1 := in_rec.email;
         out_rec.var_char2 := in_rec.phone_number;
         PIPE ROW (out_rec);                  -- second transformed output row
      END LOOP;

      CLOSE p;

      RETURN;
   END f_trans;
END refcur_pkg;
/

SELECT *
  FROM TABLE (refcur_pkg.f_trans (CURSOR (SELECT *
                                            FROM employees
                                           WHERE department_id = 60)));


--Example 12–31 Fetching from Results of Pipelined Table Functions

DECLARE
   r    SYS_REFCURSOR;
   ..   .-- First PL/SQL statement (slower):

 BEGIN
OPEN r FOR SELECT * FROM TABLE(f(CURSOR(SELECT * FROM tab)));
SELECT * BULK COLLECT INTO rec_tab FROM TABLE(g(r));
-- NOTE: When g completes, it closes r.
END;
-- Second PL/SQL statement (faster):

SELECT *
  FROM TABLE (
          g (CURSOR (SELECT * FROM TABLE (f (CURSOR (SELECT * FROM tab))))));

/

--Example 12–32 PIPELINED TABLE FUNCTION WITH Two CURSOR VARIABLE PARAMETERS

CREATE OR REPLACE PACKAGE refcur_pkg
   AUTHID DEFINER
IS
   TYPE refcur_t1 IS REF CURSOR
      RETURN employees%ROWTYPE;

   TYPE refcur_t2 IS REF CURSOR
      RETURN departments%ROWTYPE;

   TYPE outrec_typ IS RECORD
   (
      var_num     NUMBER (6),
      var_char1   VARCHAR2 (30),
      var_char2   VARCHAR2 (30)
   );

   TYPE outrecset IS TABLE OF outrec_typ;

   FUNCTION g_trans (p1 refcur_t1, p2 refcur_t2)
      RETURN outrecset
      PIPELINED;
END refcur_pkg;
/

CREATE PACKAGE BODY refcur_pkg
IS
   FUNCTION g_trans (p1 refcur_t1, p2 refcur_t2)
      RETURN outrecset
      PIPELINED
   IS
      out_rec   outrec_typ;
      in_rec1   p1%ROWTYPE;
      in_rec2   p2%ROWTYPE;
   BEGIN
      LOOP
         FETCH p2 INTO in_rec2;

         EXIT WHEN p2%NOTFOUND;
      END LOOP;

      CLOSE p2;

      LOOP
         FETCH p1 INTO in_rec1;

         EXIT WHEN p1%NOTFOUND;
         -- first row
         out_rec.var_num := in_rec1.employee_id;
         out_rec.var_char1 := in_rec1.first_name;
         out_rec.var_char2 := in_rec1.last_name;
         PIPE ROW (out_rec);
         -- second row
         out_rec.var_num := in_rec2.department_id;
         out_rec.var_char1 := in_rec2.department_name;
         out_rec.var_char2 := TO_CHAR (in_rec2.location_id);
         PIPE ROW (out_rec);
      END LOOP;

      CLOSE p1;

      RETURN;
   END g_trans;
END refcur_pkg;
/

SELECT *
  FROM TABLE (refcur_pkg.g_trans (CURSOR (SELECT *
                                            FROM employees
                                           WHERE department_id = 60),
                                  CURSOR (SELECT *
                                            FROM departments
                                           WHERE department_id = 60)));


--Example 12–33 PIPELINED TABLE FUNCTION AS AGGREGATE FUNCTION
DROP TABLE gradereport;

CREATE TABLE gradereport
(
   student   VARCHAR2 (30),
   subject   VARCHAR2 (30),
   weight    NUMBER,
   grade     NUMBER
);

INSERT INTO gradereport (student,
                         subject,
                         weight,
                         grade)
     VALUES ('Mark',
             'Physics',
             4,
             4);

INSERT INTO gradereport (student,
                         subject,
                         weight,
                         grade)
     VALUES ('Mark',
             'Chemistry',
             4,
             3);

INSERT INTO gradereport (student,
                         subject,
                         weight,
                         grade)
     VALUES ('Mark',
             'Maths',
             3,
             3);

INSERT INTO gradereport (student,
                         subject,
                         weight,
                         grade)
     VALUES ('Mark',
             'Economics',
             3,
             4);

CREATE PACKAGE pkg_gpa
   AUTHID DEFINER
IS
   TYPE gpa IS TABLE OF NUMBER;

   FUNCTION weighted_average (input_values SYS_REFCURSOR)
      RETURN gpa
      PIPELINED;
END pkg_gpa;
/

CREATE PACKAGE BODY pkg_gpa
IS
   FUNCTION weighted_average (input_values SYS_REFCURSOR)
      RETURN gpa
      PIPELINED
   IS
      grade          NUMBER;
      total          NUMBER := 0;
      total_weight   NUMBER := 0;
      weight         NUMBER := 0;
   BEGIN
      LOOP
         FETCH input_values
         INTO weight, grade;

         EXIT WHEN input_values%NOTFOUND;
         total_weight := total_weight + weight; -- Accumulate weighted average
         total := total + grade * weight;
      END LOOP;

      PIPE ROW (total / total_weight);
      RETURN;                                         -- returns single result
   END weighted_average;
END pkg_gpa;
/

SELECT w.COLUMN_VALUE "weighted result"
  FROM TABLE (
          pkg_gpa.weighted_average (
             CURSOR (SELECT weight, grade FROM gradereport))) w;


--Example 12–34 PIPELINED TABLE FUNCTION Does NOT Handle NO_DATA_NEEDED

CREATE TYPE t IS TABLE OF NUMBER
/

CREATE OR REPLACE FUNCTION pipe_rows
   RETURN t
   PIPELINED
   AUTHID DEFINER
IS
   n   NUMBER := 0;
BEGIN
   LOOP
      n := n + 1;
      PIPE ROW (n);
   END LOOP;
END pipe_rows;
/

SELECT COLUMN_VALUE
  FROM TABLE (pipe_rows ())
 WHERE ROWNUM < 5
/

--The following exception handler reraises the NO_DATA_NEEDED exception, instead of treating it as a irrecoverable error:
EXCEPTION
WHEN NO_DATA_NEEDED THEN
RAISE;
WHEN OTHERS THEN
-- (Put error-logging code here)
RAISE_APPLICATION_ERROR(-20000, 'Fatal error.');
END;


--Example 12–35 Pipelined Table Function Handles NO_DATA_NEEDED

CREATE OR REPLACE FUNCTION get_external_source_data
   RETURN t
   PIPELINED
   AUTHID DEFINER
IS
BEGIN
   External_Source.Init ();                                     -- Initialize.

  <<b>>
   BEGIN
      LOOP                                  -- Pipe rows from external source.
         PIPE ROW (External_Source.Next_Row ());
      END LOOP;
   EXCEPTION
      WHEN External_Source.Done
      THEN                                 -- When no more rows are available,
         External_Source.Clean_Up ();                             -- clean up.
      WHEN NO_DATA_NEEDED
      THEN                                    -- When no more rows are needed,
         External_Source.Clean_Up ();                             -- clean up.
         RAISE NO_DATA_NEEDED;              -- Optional, equivalent to RETURN.
   END b;
END get_external_source_data;

/
Posted by at
Labels: , ,