ORACLE PROJECTS: PL/SQL - Optimization & Tuning --1

#=======================================================================

# Writer : Lokanadham Thandlam

#=======================================================================

--Example 12–1 Specifying that Subprogram Is To Be Inlined

PROCEDURE p1 (x PLS_INTEGER) IS ...

...

PRAGMA INLINE (p1, 'YES');

x:= p1(1) + p1(2) + 17; -- These 2 invocations to p1 are inlined

...

x:= p1(3) + p1(4) + 17; -- These 2 invocations to p1 are not inlined

...

--Example 12–2 Specifying that Overloaded Subprogram Is To Be Inlined

FUNCTION p2 (p boolean) return PLS_INTEGER IS ...

FUNCTION p2 (x PLS_INTEGER) return PLS_INTEGER IS ...

...

PRAGMA INLINE(p2, 'YES');

x := p2(true) + p2(3);

...

--Example 12–3 Specifying that Subprogram Is Not To Be Inlined

PROCEDURE p1 (x PLS_INTEGER) IS ...

...

PRAGMA INLINE (p1, 'NO');

x:= p1(1) + p1(2) + 17; -- These 2 invocations to p1 are not inlined

...

x:= p1(3) + p1(4) + 17; -- These 2 invocations to p1 might be inlined

...

--Example 12–4 PRAGMA INLINE ... 'NO' Overrides PRAGMA INLINE ... 'YES'

PROCEDURE p1 (x PLS_INTEGER) IS ...

...

PRAGMA INLINE (p1, 'YES');

PRAGMA INLINE (p1, 'NO');

PRAGMA INLINE (p1, 'YES');

x:= p1(1) + p1(2) + 17; -- These 2 invocations to p1 are not inlined

...

--Example 12–5 Nested Query Improves Performance

DECLARE

starting_time TIMESTAMP WITH TIME ZONE;

ending_time TIMESTAMP WITH TIME ZONE;

BEGIN

-- Invokes SQRT for every row of employees table:

SELECT SYSTIMESTAMP INTO starting_time FROM DUAL;

FOR item IN (

SELECT DISTINCT(SQRT(department_id)) col_alias

FROM employees

ORDER BY col_alias

)

LOOP

DBMS_OUTPUT.PUT_LINE('Square root of dept. ID = ' || item.col_alias);

END LOOP;

SELECT SYSTIMESTAMP INTO ending_time FROM DUAL;

DBMS_OUTPUT.PUT_LINE('Time = ' || TO_CHAR(ending_time - starting_time));

-- Invokes SQRT for every distinct department_id of employees table:

SELECT SYSTIMESTAMP INTO starting_time FROM DUAL;

FOR item IN (

SELECT SQRT(department_id) col_alias

FROM (SELECT DISTINCT department_id FROM employees)

ORDER BY col_alias

)

LOOP

IF item.col_alias IS NOT NULL THEN

DBMS_OUTPUT.PUT_LINE('Square root of dept. ID = ' || item.col_alias);

END IF;

END LOOP;

SELECT SYSTIMESTAMP INTO ending_time FROM DUAL;

DBMS_OUTPUT.PUT_LINE('Time = ' || TO_CHAR(ending_time - starting_time));

END;

--Example 12–6 NOCOPY Subprogram PARAMETERS

DECLARE

TYPE EmpTabTyp IS TABLE OF employees%ROWTYPE;

emp_tab EmpTabTyp := EmpTabTyp (NULL); -- initialize

t1 NUMBER;

t2 NUMBER;

t3 NUMBER;

PROCEDURE get_time (t OUT NUMBER)

BEGIN

t := DBMS_UTILITY.get_time;

END;

PROCEDURE do_nothing1 (tab IN OUT EmpTabTyp)

BEGIN

NULL;

END;

PROCEDURE do_nothing2 (tab IN OUT NOCOPY EmpTabTyp)

BEGIN

NULL;

END;

BEGIN

SELECT *

INTO emp_tab (1)

FROM employees

WHERE employee_id = 100;

emp_tab.EXTEND (49999, 1); -- Copy element 1 into 2..50000

get_time (t1);

do_nothing1 (emp_tab); -- Pass IN OUT parameter

get_time (t2);

do_nothing2 (emp_tab); -- Pass IN OUT NOCOPY parameter

get_time (t3);

DBMS_OUTPUT.PUT_LINE ('Call Duration (secs)');

DBMS_OUTPUT.PUT_LINE ('--------------------');

DBMS_OUTPUT.PUT_LINE ('Just IN OUT: ' || TO_CHAR ( (t2 - t1) / 100.0));

DBMS_OUTPUT.PUT_LINE ('With NOCOPY: ' || TO_CHAR ( (t3 - t2)) / 100.0);

END;

--consider the FOR LOOP statement in Example 12–7. It sends these DML statements from PL/SQL to SQL one at a time:

DELETE FROM employees_temp

WHERE department_id = depts (10);

DELETE FROM employees_temp

WHERE department_id = depts (30);

DELETE FROM employees_temp

WHERE department_id = depts (70);

--Example 12–7 DELETE Statement in FOR LOOP Statement

DROP TABLE employees_temp;

CREATE TABLE employees_temp

SELECT * FROM employees;

DECLARE

TYPE NumList IS VARRAY (20) OF NUMBER;

depts NumList := NumList (10, 30, 70); -- department numbers

BEGIN

FOR i IN depts.FIRST .. depts.LAST

LOOP

DELETE FROM employees_temp

WHERE department_id = depts (i);

END LOOP;

END;

--Now consider the FORALL statement in Example 12–8. It sends the same three DML statements from PL/SQL to SQL as a batch.

--Example 12–8 DELETE Statement in FORALL Statement

DROP TABLE employees_temp;

CREATE TABLE employees_temp

SELECT * FROM employees;

DECLARE

TYPE NumList IS VARRAY (20) OF NUMBER;

depts NumList := NumList (10, 30, 70); -- department numbers

BEGIN

FORALL i IN depts.FIRST .. depts.LAST

DELETE FROM employees_temp

WHERE department_id = depts (i);

END;

--Example 12–9 Time Difference for INSERT Statement in FOR LOOP and FORALL Statements

DROP TABLE parts1;

CREATE TABLE parts1

(

pnum INTEGER,

pname VARCHAR2 (15)

);

DROP TABLE parts2;

CREATE TABLE parts2

(

pnum INTEGER,

pname VARCHAR2 (15)

);

DECLARE

TYPE NumTab IS TABLE OF parts1.pnum%TYPE

INDEX BY PLS_INTEGER;

TYPE NameTab IS TABLE OF parts1.pname%TYPE

INDEX BY PLS_INTEGER;

pnums NumTab;

pnames NameTab;

iterations CONSTANT PLS_INTEGER := 50000;

t1 INTEGER;

t2 INTEGER;

t3 INTEGER;

BEGIN

FOR j IN 1 .. iterations

LOOP -- populate collections

pnums (j) := j;

pnames (j) := 'Part No. ' || TO_CHAR (j);

END LOOP;

t1 := DBMS_UTILITY.get_time;

FOR i IN 1 .. iterations

LOOP

INSERT INTO parts1 (pnum, pname)

VALUES (pnums (i), pnames (i));

END LOOP;

t2 := DBMS_UTILITY.get_time;

FORALL i IN 1 .. iterations

INSERT INTO parts2 (pnum, pname)

VALUES (pnums (i), pnames (i));

t3 := DBMS_UTILITY.get_time;

DBMS_OUTPUT.PUT_LINE ('Execution Time (secs)');

DBMS_OUTPUT.PUT_LINE ('---------------------');

DBMS_OUTPUT.PUT_LINE ('FOR LOOP: ' || TO_CHAR ( (t2 - t1) / 100));

DBMS_OUTPUT.PUT_LINE ('FORALL: ' || TO_CHAR ( (t3 - t2) / 100));

COMMIT;

END;

--Example 12–10 FORALL Statement for Subset of Collection

DROP TABLE employees_temp;

CREATE TABLE employees_temp

SELECT * FROM employees;

DECLARE

TYPE NumList IS VARRAY (10) OF NUMBER;

depts NumList

:= NumList (5,

10,

20,

30,

50,

55,

57,

60,

70,

75);

BEGIN

FORALL j IN 4 .. 7

DELETE FROM employees_temp

WHERE department_id = depts (j);

END;

--Example 12–11 FORALL Statements for Sparse Collection and Its Subsets

DROP TABLE valid_orders;

CREATE TABLE valid_orders

(

cust_name VARCHAR2 (32),

amount NUMBER (10, 2)

);

DROP TABLE big_orders;

CREATE TABLE big_orders

SELECT *

FROM valid_orders

WHERE 1 = 0;

DROP TABLE rejected_orders;

CREATE TABLE rejected_orders

SELECT *

FROM valid_orders

WHERE 1 = 0;

DECLARE

SUBTYPE cust_name IS valid_orders.cust_name%TYPE;

TYPE cust_typ IS TABLE OF cust_name;

cust_tab cust_typ; -- Collection of customer names

SUBTYPE order_amount IS valid_orders.amount%TYPE;

TYPE amount_typ IS TABLE OF NUMBER;

amount_tab amount_typ; -- Collection of order amounts

TYPE index_pointer_t IS TABLE OF PLS_INTEGER;

/* Collections for pointers to elements of cust_tab collection

(to represent two subsets of cust_tab): */

big_order_tab index_pointer_t := index_pointer_t ();

rejected_order_tab index_pointer_t := index_pointer_t ();

PROCEDURE populate_data_collections

BEGIN

cust_tab :=

cust_typ ('Company1',

'Company2',

'Company3',

'Company4',

'Company5');

amount_tab :=

amount_typ (5000.01,

150.25,

4000.00,

NULL);

END;

BEGIN

populate_data_collections;

DBMS_OUTPUT.PUT_LINE ('--- Original order data ---');

FOR i IN 1 .. cust_tab.LAST

LOOP

DBMS_OUTPUT.PUT_LINE (

'Customer #' || i || ', ' || cust_tab (i) || ': $' || amount_tab (i));

END LOOP;

-- Delete invalid orders:

FOR i IN 1 .. cust_tab.LAST

LOOP

IF amount_tab (i) IS NULL OR amount_tab (i) = 0

THEN

cust_tab.delete (i);

amount_tab.delete (i);

END IF;

END LOOP;

-- cust_tab is now a sparse collection.

DBMS_OUTPUT.PUT_LINE ('--- Order data with invalid orders deleted ---');

FOR i IN 1 .. cust_tab.LAST

LOOP

IF cust_tab.EXISTS (i)

THEN

DBMS_OUTPUT.PUT_LINE (

'Customer #'

|| i

|| ', '

|| cust_tab (i)

|| ': $'

|| amount_tab (i));

END IF;

END LOOP;

-- Using sparse collection, populate valid_orders table:

FORALL i IN INDICES OF cust_tab

INSERT INTO valid_orders (cust_name, amount)

VALUES (cust_tab (i), amount_tab (i));

populate_data_collections; -- Restore original order data

-- cust_tab is a dense collection again.

/* Populate collections of pointers to elements of cust_tab collection

(which represent two subsets of cust_tab): */

FOR i IN cust_tab.FIRST .. cust_tab.LAST

LOOP

IF amount_tab (i) IS NULL OR amount_tab (i) = 0

THEN

rejected_order_tab.EXTEND;

rejected_order_tab (rejected_order_tab.LAST) := i;

END IF;

IF amount_tab (i) > 2000

THEN

big_order_tab.EXTEND;

big_order_tab (big_order_tab.LAST) := i;

END IF;

END LOOP;

/* Using each subset in a different FORALL statement,

populate rejected_orders and big_orders tables: */

FORALL i IN VALUES OF rejected_order_tab

INSERT INTO rejected_orders (cust_name, amount)

VALUES (cust_tab (i), amount_tab (i));

FORALL i IN VALUES OF big_order_tab

INSERT INTO big_orders (cust_name, amount)

VALUES (cust_tab (i), amount_tab (i));

END;

--Verify that correct order details were stored:

SELECT cust_name "Customer", amount "Valid order amount"

FROM valid_orders

ORDER BY cust_name;

SELECT cust_name "Customer", amount "Big order amount"

FROM big_orders

ORDER BY cust_name;

SELECT cust_name "Customer", amount "Rejected order amount"

FROM rejected_orders

ORDER BY cust_name;

--Example 12–12 Handling FORALL EXCEPTIONS Immediately

DROP TABLE emp_temp;

CREATE TABLE emp_temp

(

deptno NUMBER (2),

job VARCHAR2 (18)

);

CREATE OR REPLACE PROCEDURE p

AUTHID DEFINER

TYPE NumList IS TABLE OF NUMBER;

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

error_message VARCHAR2 (100);

BEGIN

-- Populate table:

INSERT INTO emp_temp (deptno, job)

VALUES (10, 'Clerk');

INSERT INTO emp_temp (deptno, job)

VALUES (20, 'Bookkeeper');

INSERT INTO emp_temp (deptno, job)

VALUES (30, 'Analyst');

COMMIT;

-- Append 9-character string to each job:

FORALL j IN depts.FIRST .. depts.LAST

UPDATE emp_temp

SET job = job || ' (Senior)'

WHERE deptno = depts (j);

EXCEPTION

WHEN OTHERS

THEN

error_message := SQLERRM;

DBMS_OUTPUT.PUT_LINE (error_message);

COMMIT; -- Commit results of successful updates

RAISE;

END;

--Invoke procedure:

BEGIN

END;

--Example 12–13 Handling FORALL Exceptions After FORALL Statement Completes

CREATE OR REPLACE PROCEDURE p

AUTHID DEFINER

TYPE NumList IS TABLE OF NUMBER;

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

error_message VARCHAR2 (100);

bad_stmt_no PLS_INTEGER;

bad_deptno emp_temp.deptno%TYPE;

bad_job emp_temp.job%TYPE;

dml_errors EXCEPTION;

PRAGMA EXCEPTION_INIT (dml_errors, -24381);

BEGIN

-- Populate table:

INSERT INTO emp_temp (deptno, job)

VALUES (10, 'Clerk');

INSERT INTO emp_temp (deptno, job)

VALUES (20, 'Bookkeeper');

INSERT INTO emp_temp (deptno, job)

VALUES (30, 'Analyst');

COMMIT;

-- Append 9-character string to each job:

FORALL j IN depts.FIRST .. depts.LAST SAVE EXCEPTIONS

UPDATE emp_temp

SET job = job || ' (Senior)'

WHERE deptno = depts (j);

EXCEPTION

WHEN dml_errors

THEN

FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT

LOOP

error_message := SQLERRM (- (SQL%BULK_EXCEPTIONS (i).ERROR_CODE));

DBMS_OUTPUT.PUT_LINE (error_message);

bad_stmt_no := SQL%BULK_EXCEPTIONS (i).ERROR_INDEX;

DBMS_OUTPUT.PUT_LINE ('Bad statement #: ' || bad_stmt_no);

bad_deptno := depts (bad_stmt_no);

DBMS_OUTPUT.PUT_LINE ('Bad department #: ' || bad_deptno);

SELECT job

INTO bad_job

FROM emp_temp

WHERE deptno = bad_deptno;

DBMS_OUTPUT.PUT_LINE ('Bad job: ' || bad_job);

END LOOP;

COMMIT; -- Commit results of successful updates

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE ('Unrecognized error.');

RAISE;

END;

--Invoke procedure:

BEGIN

END;

SELECT * FROM emp_temp;

--Example 12–14 Showing Number of Rows Affected by Each DELETE in FORALL

DROP TABLE emp_temp;

CREATE TABLE emp_temp

SELECT * FROM employees;

DECLARE

TYPE NumList IS TABLE OF NUMBER;

depts NumList := NumList (30, 50, 60);

BEGIN

FORALL j IN depts.FIRST .. depts.LAST

DELETE FROM emp_temp

WHERE department_id = depts (j);

FOR i IN depts.FIRST .. depts.LAST

LOOP

DBMS_OUTPUT.PUT_LINE (

'Statement #'

|| i

|| ' deleted '

|| SQL%BULK_ROWCOUNT (i)

|| ' rows.');

END LOOP;

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

END;

--Example 12–15 Showing Number of Rows Affected by Each INSERT SELECT in FORALL

DROP TABLE emp_by_dept;

CREATE TABLE emp_by_dept

SELECT employee_id, department_id

FROM employees

WHERE 1 = 0;

DECLARE

TYPE dept_tab IS TABLE OF departments.department_id%TYPE;

deptnums dept_tab;

BEGIN

SELECT department_id

BULK COLLECT INTO deptnums

FROM departments;

FORALL i IN 1 .. deptnums.COUNT

INSERT INTO emp_by_dept (employee_id, department_id)

SELECT employee_id, department_id

FROM employees

WHERE department_id = deptnums (i)

ORDER BY department_id, employee_id;

FOR i IN 1 .. deptnums.COUNT

LOOP

-- Count how many rows were inserted for each department; that is,

-- how many employees are in each department.

DBMS_OUTPUT.PUT_LINE (

'Dept '

|| deptnums (i)

|| ': inserted '

|| SQL%BULK_ROWCOUNT (i)

|| ' records');

END LOOP;

DBMS_OUTPUT.PUT_LINE ('Total records inserted: ' || SQL%ROWCOUNT);

END;

--Example 12–16 BULK-Selecting Two DATABASE COLUMNS INTO Two NESTED TABLES

DECLARE

TYPE NumTab IS TABLE OF employees.employee_id%TYPE;

TYPE NameTab IS TABLE OF employees.last_name%TYPE;

enums NumTab;

names NameTab;

PROCEDURE print_first_n (n POSITIVE)

BEGIN

IF enums.COUNT = 0

THEN

DBMS_OUTPUT.PUT_LINE ('Collections are empty.');

ELSE

DBMS_OUTPUT.PUT_LINE ('First ' || n || ' employees:');

FOR i IN 1 .. n

LOOP

DBMS_OUTPUT.PUT_LINE (

' Employee #' || enums (i) || ': ' || names (i));

END LOOP;

END IF;

END;

BEGIN

SELECT employee_id, last_name

BULK COLLECT INTO enums, names

FROM employees

ORDER BY employee_id;

print_first_n (3);

print_first_n (6);

END;

--Example 12–17 BULK-Selecting INTO NESTED TABLE OF RECORDS

DECLARE

CURSOR c1

SELECT first_name, last_name, hire_date FROM employees;

TYPE NameSet IS TABLE OF c1%ROWTYPE;

stock_managers NameSet; -- nested table of records

BEGIN

-- Assign values to nested table of records:

SELECT first_name, last_name, hire_date

BULK COLLECT INTO stock_managers

FROM employees

WHERE job_id = 'ST_MAN'

ORDER BY hire_date;

-- Print nested table of records:

FOR i IN stock_managers.FIRST .. stock_managers.LAST

LOOP

DBMS_OUTPUT.PUT_LINE (

stock_managers (i).hire_date

|| ' '

|| stock_managers (i).last_name

|| ', '

|| stock_managers (i).first_name);

END LOOP;

END;

--Example 12–18 SELECT BULK COLLECT INTO Statement with Unexpected Results

CREATE OR REPLACE TYPE numbers_type AUTHID DEFINER IS TABLE OF INTEGER

CREATE OR REPLACE PROCEDURE p (i IN INTEGER)

AUTHID DEFINER

numbers1 numbers_type

:= numbers_type (1,

5);

BEGIN

DBMS_OUTPUT.PUT_LINE ('Before SELECT statement');

DBMS_OUTPUT.PUT_LINE ('numbers1.COUNT() = ' || numbers1.COUNT ());

FOR j IN 1 .. numbers1.COUNT ()

LOOP

DBMS_OUTPUT.PUT_LINE ('numbers1(' || j || ') = ' || numbers1 (j));

END LOOP;

--Self-selecting BULK COLLECT INTO clause:

SELECT a.COLUMN_VALUE

BULK COLLECT INTO numbers1

FROM TABLE (numbers1) a

WHERE a.COLUMN_VALUE > p.i

ORDER BY a.COLUMN_VALUE;

DBMS_OUTPUT.PUT_LINE ('After SELECT statement');

DBMS_OUTPUT.PUT_LINE ('numbers1.COUNT() = ' || numbers1.COUNT ());

END p;

--Invoke p:

BEGIN

p (2);

END;

--Invoke p:

BEGIN

p (10);

END;

--Example 12–19 CURSOR Workaround FOR Example 12–18

CREATE OR REPLACE TYPE numbers_type IS TABLE OF INTEGER

CREATE OR REPLACE PROCEDURE p (i IN INTEGER)

AUTHID DEFINER

numbers1 numbers_type

:= numbers_type (1,

5);

CURSOR c

SELECT a.COLUMN_VALUE

FROM TABLE (numbers1) a

WHERE a.COLUMN_VALUE > p.i

ORDER BY a.COLUMN_VALUE;

BEGIN

DBMS_OUTPUT.PUT_LINE ('Before FETCH statement');

DBMS_OUTPUT.PUT_LINE ('numbers1.COUNT() = ' || numbers1.COUNT ());

FOR j IN 1 .. numbers1.COUNT ()

LOOP

DBMS_OUTPUT.PUT_LINE ('numbers1(' || j || ') = ' || numbers1 (j));

END LOOP;

OPEN c;

FETCH c

BULK COLLECT INTO numbers1;

CLOSE c;

DBMS_OUTPUT.PUT_LINE ('After FETCH statement');

DBMS_OUTPUT.PUT_LINE ('numbers1.COUNT() = ' || numbers1.COUNT ());

IF numbers1.COUNT () > 0

THEN

FOR j IN 1 .. numbers1.COUNT ()

LOOP

DBMS_OUTPUT.PUT_LINE ('numbers1(' || j || ') = ' || numbers1 (j));

END LOOP;

END IF;

END p;

--Invoke p:

BEGIN

p (2);

END;

BEGIN

p (10);

END;

--Example 12–20 SECOND Collection Workaround FOR Example 12–18

CREATE OR REPLACE TYPE numbers_type IS TABLE OF INTEGER

CREATE OR REPLACE PROCEDURE p (i IN INTEGER)

AUTHID DEFINER

numbers1 numbers_type

:= numbers_type (1,

5);

numbers2 numbers_type

:= numbers_type (0,

0);

BEGIN

DBMS_OUTPUT.PUT_LINE ('Before SELECT statement');

DBMS_OUTPUT.PUT_LINE ('numbers1.COUNT() = ' || numbers1.COUNT ());

FOR j IN 1 .. numbers1.COUNT ()

LOOP

DBMS_OUTPUT.PUT_LINE ('numbers1(' || j || ') = ' || numbers1 (j));

END LOOP;

DBMS_OUTPUT.PUT_LINE ('numbers2.COUNT() = ' || numbers2.COUNT ());

FOR j IN 1 .. numbers2.COUNT ()

LOOP

DBMS_OUTPUT.PUT_LINE ('numbers2(' || j || ') = ' || numbers2 (j));

END LOOP;

SELECT a.COLUMN_VALUE

BULK COLLECT INTO numbers2 -- numbers2 appears here

FROM TABLE (numbers1) a -- numbers1 appears here

WHERE a.COLUMN_VALUE > p.i

ORDER BY a.COLUMN_VALUE;

DBMS_OUTPUT.PUT_LINE ('After SELECT statement');

DBMS_OUTPUT.PUT_LINE ('numbers1.COUNT() = ' || numbers1.COUNT ());

IF numbers1.COUNT () > 0

THEN

FOR j IN 1 .. numbers1.COUNT ()

LOOP

DBMS_OUTPUT.PUT_LINE ('numbers1(' || j || ') = ' || numbers1 (j));

END LOOP;

END IF;

DBMS_OUTPUT.PUT_LINE ('numbers2.COUNT() = ' || numbers2.COUNT ());

IF numbers2.COUNT () > 0

THEN

FOR j IN 1 .. numbers2.COUNT ()

LOOP

DBMS_OUTPUT.PUT_LINE ('numbers2(' || j || ') = ' || numbers2 (j));

END LOOP;

END IF;

END p;

--Invoke p:

BEGIN

p (2);

END;

--Invoke P:

BEGIN

p (10);

END;

--Example 12–21 Limiting Bulk Selection with ROWNUM, SAMPLE, and FETCH FIRST

DECLARE

TYPE SalList IS TABLE OF employees.salary%TYPE;

sals SalList;

BEGIN

SELECT salary

BULK COLLECT INTO sals

FROM employees

WHERE ROWNUM <= 50;

SELECT salary

BULK COLLECT INTO sals

FROM employees SAMPLE (10);

SELECT salary

BULK COLLECT INTO sals

FROM employees FETCH FIRST 50 ROWS ONLY;

END;

--Example 12–22 BULK-Fetching INTO Two NESTED TABLES

DECLARE

TYPE NameList IS TABLE OF employees.last_name%TYPE;

TYPE SalList IS TABLE OF employees.salary%TYPE;

CURSOR c1

SELECT last_name, salary

FROM employees

WHERE salary > 10000

ORDER BY last_name;

names NameList;

sals SalList;

TYPE RecList IS TABLE OF c1%ROWTYPE;

recs RecList;

v_limit PLS_INTEGER := 10;

PROCEDURE print_results

BEGIN

-- Check if collections are empty:

IF names IS NULL OR names.COUNT = 0

THEN

DBMS_OUTPUT.PUT_LINE ('No results!');

ELSE

DBMS_OUTPUT.PUT_LINE ('Result: ');

FOR i IN names.FIRST .. names.LAST

LOOP

DBMS_OUTPUT.PUT_LINE (

' Employee ' || names (i) || ': $' || sals (i));

END LOOP;

END IF;

END;

BEGIN

DBMS_OUTPUT.PUT_LINE ('--- Processing all results simultaneously ---');

OPEN c1;

FETCH c1

BULK COLLECT INTO names, sals;

CLOSE c1;

print_results ();

DBMS_OUTPUT.PUT_LINE (

'--- Processing ' || v_limit || ' rows at a time ---');

OPEN c1;

LOOP

FETCH c1

BULK COLLECT INTO names, sals

LIMIT v_limit;

EXIT WHEN names.COUNT = 0;

print_results ();

END LOOP;

CLOSE c1;

DBMS_OUTPUT.PUT_LINE ('--- Fetching records rather than columns ---');

OPEN c1;

FETCH c1

BULK COLLECT INTO recs;

FOR i IN recs.FIRST .. recs.LAST

LOOP

-- Now all columns from result set come from one record

DBMS_OUTPUT.PUT_LINE (

' Employee ' || recs (i).last_name || ': $' || recs (i).salary);

END LOOP;

END;

--Example 12–23 uses a FETCH BULK COLLECT statement to fetch a result set into a collection (nested table) of records.

--Example 12–23 Bulk-Fetching into Nested Table of Records

DECLARE

CURSOR c1

SELECT first_name, last_name, hire_date FROM employees;

TYPE NameSet IS TABLE OF c1%ROWTYPE;

stock_managers NameSet; -- nested table of records

TYPE cursor_var_type IS REF CURSOR;

CV cursor_var_type;

BEGIN

-- Assign values to nested table of records:

OPEN CV FOR

SELECT first_name, last_name, hire_date

FROM employees

WHERE job_id = 'ST_MAN'

ORDER BY hire_date;

FETCH CV

BULK COLLECT INTO stock_managers;

CLOSE CV;

-- Print nested table of records:

FOR i IN stock_managers.FIRST .. stock_managers.LAST

LOOP

DBMS_OUTPUT.PUT_LINE (

stock_managers (i).hire_date

|| ' '

|| stock_managers (i).last_name

|| ', '

|| stock_managers (i).first_name);

END LOOP;

END;

--Example 12–24 Limiting Bulk FETCH with LIMIT

DECLARE

TYPE numtab IS TABLE OF NUMBER

INDEX BY PLS_INTEGER;

CURSOR c1

SELECT employee_id

FROM employees

WHERE department_id = 80

ORDER BY employee_id;

empids numtab;

BEGIN

OPEN c1;

LOOP -- Fetch 10 rows or fewer in each iteration

FETCH c1

BULK COLLECT INTO empids

LIMIT 10;

DBMS_OUTPUT.PUT_LINE ('------- Results from One Bulk Fetch --------');

FOR i IN 1 .. empids.COUNT

LOOP

DBMS_OUTPUT.PUT_LINE ('Employee Id: ' || empids (i));

END LOOP;

EXIT WHEN c1%NOTFOUND;

END LOOP;

CLOSE c1;

END;

--Example 12–25 Returning Deleted Rows in Two Nested Tables

DROP TABLE emp_temp;

CREATE TABLE emp_temp

SELECT *

FROM employees

ORDER BY employee_id;

DECLARE

TYPE NumList IS TABLE OF employees.employee_id%TYPE;

enums NumList;

TYPE NameList IS TABLE OF employees.last_name%TYPE;

names NameList;

BEGIN

DELETE FROM emp_temp

WHERE department_id = 30

RETURNING employee_id, last_name

BULK COLLECT INTO enums, names;

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

FOR i IN enums.FIRST .. enums.LAST

LOOP

DBMS_OUTPUT.PUT_LINE ('Employee #' || enums (i) || ': ' || names (i));

END LOOP;

END;

ORACLE PROJECTS

MENUS

Tuesday, 14 January 2014

PL/SQL - Optimization & Tuning --1