2674995 Oracle Plsql And Sql

PL/SQL and SQL

-1-

Working with data that is accessed buy SQL can include the use of • • • •

Cursors (Explicit, Implicit and Reference) Associative arrays Nested tables Variable arrays (Varrays)

Explicit Cursors A cursor acts logically as a pointer to a result set, you can move the pointer through the result set processing each row until the end. There are methods for the cursor cursor <cursor_name> is <sql statement>; Creating Opening Fetching data Closing

cursor test_cursor is select name from employees open cursor [argument,argument]; fetch cursor into variable1, variable2......; close cursor

Cursors have attributes that can be used and are obtained by using cursor.attribute_name %ISOPEN

contains true if the cursor is currently open false if not. can contain serveral values

%FOUND

NULL - before a fetch on the cursor TRUE - if a row was fetched successfully FALSE - if a row was fetched unsuccessfully oppostite to %FOUND contains the number of rows fetched so far.

%NOTFOUND %ROWCOUNT Now for an example Cursor example

create or replace procedure test_cursor_proc_1 IS first_name varchar2(30); last_name varchar2(30); cursor test_cursor IS select fname, lname from employees; BEGIN open test_cursor; loop fetch test_cursor into first_name, last_name; exit when test_cursor%NOTFOUND;

PL/SQL and SQL

-2-

dbms_output.put_line( 'First Name: ' || first_name || 'Surname: ' || last_name); end loop; close test_cursor; END; Special Data Types In the last example we had to specify the data types, Oracle can automatically match the data types for us and there are two data types that allow us to do this %type and %rowtype. is used to declare a field with the same type as that of a specified table column

%TYPE

%ROWTYPE

DECLARE empname emp.ename%TYPE BEGIN select ename into empname from emp where rownum = 1; dbms_ouput.put_line( 'Name: ' || empname); END; is used to declare a record with the same types as found in the specified table, view or cursor DECLARE emp emp%ROWTYPE BEGIN emp.empno := 10; emp.ename := 'vallep'; END;

For Cursor You can reduce the amount of code by using the for cursor construct, you had to go through a number of steps to get the data define variables, define the cursor, open the cursor, fetch the data in a loop, exit the loop then close the cursor, with the for construct all you have to do is define the cursor.

example

Implicit Cursor

create or replace procedure test_cursor_proc_2 IS cursor test_cursor IS select fname, lname from employees; BEGIN FOR name_rec IN test_cursor loop dbms_output.put_line( 'First Name: ' || name_rec.first_name || 'Surname: ' || name_rec.last_name); end loop; END;

PL/SQL and SQL

-3-

You can use the PL/SQL statement instead of a cursor, Oracle will create a implicit cursor that operates behind the scenes like a explicit cursor.

example

create or replace procedure test_cursor_proc2_3 IS BEGIN FOR name_rec IN select fname, lname from employees; loop dbms_output.put_line( 'First Name: ' || name_rec.first_name || 'Surname: ' || name_rec.last_name); end loop; END; Note: the select statement will use a implicit cursor, the %ISOPEN will return false since there is no explicit cursor open.

REF cursors A ref cursor allows you to pass a cursor reference from one PL/SQL program unit to another, you can create a variable that will recieve a cursor and enable access to it. create or replace procedure test_cursor_proc_3(p_cursor OUT sys_refcursor) IS BEGIN FOR p_cursor IN select fname, lname from employees; END; Example

create or replace procedure test_cursor_proc_3(p_cursor OUT sys_refcursor) IS type p_cursor is ref cursor return employees%ROWTYPE; BEGIN FOR p_cursor IN select fname, lname from employees; END; Note: the top example is a weakly typed ref cursor, the bottom type is a strongly typed ref cursor.

Collections You may want to fetch a entire dataset into an array like structure, PL/SQL has a class of variables that will let yoy accomplish this is called collections. Although a collection is a single dimensional array you can simulate multidimensional arrays by creating a collection with a data type that is another collection. There are three types of collections • •

associative arrays nested tables

PL/SQL and SQL •

-4-

variable arrays

Associate Arrays are collections that are indexed by a value, arrays can be indexed by binary integer, pls_integer, postive, natural or varchar2. You cannot store an associative array directly in the database, so you need logic to store and retrieve the values in this type of collection with the database., it also cannot be use in SQL.

associative array example

DECLARE TYPE a_char is table of varchar2(10) index by binary_integer v_char_data a_char_data; BEGIN v_char_data.extend; v_char_data(1) := 'ABC'; # can have up to 10 chacaters as stated above v_char_data(2) := 'DEF'; for i in v_char_data.first .. v_char_data.last loop dbms_output.put_line(v_char_data(i)); end loop;

Nested Tables use sequential integers as the index to the collection, a nested table can have nonconsecutive numbers as index, creating what is known as a sparse index.

Nested table example

DECLARE TYPE a_char_data IS table of varchar2(10); v_char_data a_char_data := a_char_data(); # calls the nested table constructor BEGIN v_char_data.extend; v_char_data(1) := 'ABC'; # can have up to 10 chacaters as stated above v_char_data.extend; v_char_data(2) := 'DEF'; for i in v_char_data.first .. v_char_data.last loop dbms_output.put_line(v_char_data(i)); end loop; end; Note: extend is a method that adds a new empty element to a nested table, this must be called first.

Variable Array (Varrays) alos use integers to index the collection and can be stored in a database column. They are generally used when you how what maximum size you need. VArray example

DECLARE TYPE a_char_data IS VARRAY(5) of varchar2(10); v_char_data a_char_data := a_char_data(); # calls the nested table constructor

PL/SQL and SQL BEGIN v_char_data.extend; v_char_data(1) := 'ABC'; v_char_data.extend; v_char_data(2) := 'DEF';

-5-

# can have up to 10 chacaters as stated above

for i in v_char_data.first .. v_char_data.last loop dbms_output.put_line(v_char_data(i)); end loop; end; There are a number of operations that you can perform on a collection EXISTS COUNT LIMIT FIRST LAST NEXT PRIOR

check to see if an element in a collection exists returns the number of elements returns the maximum number of entries in a VARRAY returns the first element returns the last element returns the next element in the collection returns previous element in a collection

EXTEND

extends the number of elements in a collection

TRIM

removes elements from the end of a collection

DELETE

removes elements from a collection

collection.exists(index) collection.count collection.limit collection.first collection.last collection.next collection.prior collection.extend collection.extend(n) collection.extend(n, I) collection.trim collection.trim(n) collection.delete collection.delete(n) collection.delete(m,n)

The table below summaries the differences between the above collections

index type fixed size declared spare index allowed store in database access individual elements in stored collection

Associate Array Number or String No Yes No n/a

Nested Table Number No Yes Yes Yes

VARRAY Number Yes No Yes No

Bulk Collect operations Executng SQL statements in programs causes a context switch between the PL/SQL and SQL engines, too many context switches and performance degrades, to reduce context switches we use bulk collect which lets us

PL/SQL and SQL

-6-

tranfers rows between SQL and PL/SQL as collections. Normally developers usually tend to use the cursor for loops method for precessing data, they declare a cursor, open it, fetch from it row by row in a loop and process the row they fetched. DECLARE cursor test_cursor select object_name from t_all_objects; type cl_type is table of test_cursor%rowtype; recl cl_type ; Bulk collect example BEGIN open test_cursor; fetch test_cursor bulk collect into recl; END; DECLARE cursor test_cursor select object_name from t_all_objects; type cl_type is table of test_cursor%rowtype; recl cl_type ; BEGIN open test_cursor; loop Bulk collect example fetch test_cursor bulk collect into recl limit 2000; (limit rows) for i in 1 .. recl.count loop null; end loop; exit when test_cursor%notfound; end loop; END; Forall statement The forall statement basically does the same as the bulk collect statement, the construct packages up multiple write statements and sends them off to the oracle database in a single message thus reducing overall performance of the whole operation. indices of clause allows bulk operation on a sparse collection (where elements have been deleted) by removing the reference to specific elements. values of clause alows values of one collection to be used as index pointers to another collection. forall example forall example (exception capature) forall example (indices of) forall example

forall IN lower_bound .. upper_bound sql_statement; forall IN lower_bound .. upper_bound save exceptions sql_statement; forall INDICES OF lower_bound .. upper_bound sql_statement; forall VALUES OF lower_bound .. upper_bound

PL/SQL and SQL (values of)

sql_statement;

-7-