PLSQL User Defined Types Record and Table Please

PL/SQL User Defined Types Record and Table Please use speaker notes for additional information!

Records A record is defined as a composite data structure because it is composed of multiple elements (components). The record does not have its own value because it is viewed as a structure to store and access the individual components each of which has its own value. In Oracle there are three types of RECORDS: table-based, cursor-based and programmer-defined. While their uses vary, they have the same internal structure. Every record is composed of one or more elements/fields.

Implicit cursors are used by PL/SQL blocks whenever an SQL statement is executed if there is not an existing explicit cursor associated with the statement. The implicit cursor is automatically setup by Oracle and is not under the control of the programmer/developer. Oracle sets up what is know as the context area which contains the information Oracle needs to process a statement. The cursor serves as a pointer or handle to the context area. In PL/SQL the implicit cursor is used to process the DML statements such as INSERT, DELETE, UPDATE as well as the SELECT INTO statement.

TYPE - RECORD SQL> edit atsd 1 SQL> SELECT * FROM donor 2 WHERE idno = 23456; IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT --------------- -- ---------23456 Susan Ash 21 Main St Fall River MA 02720 04 -MAR-92 100 Amy Costa SET VERIFY OFF ACCEPT in_idno PROMPT 'Please enter the donor idno: ' DECLARE TYPE rec_donor IS RECORD (rec_id VARCHAR 2(5), rec_name VARCHAR 2(15), rec_yrgoal NUMBER(7, 2)); a_rec_donor; -- Note: this declares a variable of the defined type BEGIN SELECT idno, name, yrgoal INTO a_rec_donor. rec_id, a_rec_donor. rec_name, a_rec_donor. rec_yrgoal FROM new_donor WHERE idno = &in_idno; INSERT INTO some_donor VALUES(a_rec_donor. rec_id, a_rec_donor. rec_name, a_rec_donor. rec_yrgoal); END; / SET VERIFY ON SQL> @ atsd 1 Please enter the donor idno: 23456 PL/SQL procedure successfully completed. SQL> SELECT * FROM some_donor; IDNO NAME YRGOAL ----------23456 Susan Ash 100

TYPE - RECORD SQL> SELECT * FROM donor WHERE idno = 11111; IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT --------------- -- ---------11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03 -JUL-98 500 John Smith SQL> edit atsd 2 SET VERIFY OFF ACCEPT in_idno PROMPT 'Please enter the donor idno: ' DECLARE TYPE rec_donor IS RECORD (rec_id VARCHAR 2(5), rec_name VARCHAR 2(15), rec_yrgoal NUMBER(7, 2)); a_rec_donor; -- Note: this declares a variable of the defined type BEGIN SELECT idno, name, yrgoal INTO a_rec_donor FROM new_donor WHERE idno = &in_idno; INSERT INTO some_donor VALUES(a_rec_donor. rec_id, a_rec_donor. rec_name, a_rec_donor. rec_yrgoal); END; / SET VERIFY ON SQL> @atsd 2 Please enter the donor idno: 11111 PL/SQL procedure successfully completed. SQL> SELECT * FROM some_donor; IDNO ----23456 11111 NAME YRGOAL --------Susan Ash 100 Stephen Daniels 500

TYPE - RECORD SQL> edit atsd 2 SET VERIFY OFF ACCEPT in_idno PROMPT 'Please enter the donor idno: ' DECLARE TYPE rec_donor IS RECORD (rec_id VARCHAR 2(5), rec_name VARCHAR 2(15), rec_yrgoal NUMBER(7, 2)); a_rec_donor; -- Note: this declares a variable of the defined type BEGIN /*This version does not work because of a_rec_donor in the VALUES clause */ SELECT idno, name, yrgoal INTO a_rec_donor FROM new_donor WHERE idno = &in_idno; INSERT INTO some_donor VALUES(a_rec_donor); END; / SET VERIFY ON SQL> @ atsd 2 a Please enter the donor idno: 33333 VALUES(a_rec_donor); * ERROR at line 10: ORA-06550: line 10, column 11: PLS-00382: expression is of wrong type ORA-06550: line 9, column 4: PL/SQL: SQL Statement ignored

SQL> DESC some_donor Name Null? ----------------IDNO NAME YRGOAL ROWTYPE Type ---VARCHAR 2(5) VARCHAR 2(15) NUMBER(7, 2) SQL> edit atsd 4 SET VERIFY OFF ACCEPT in_idno PROMPT 'Please enter the donor idno: ' DECLARE v_donor some_donor%ROWTYPE; BEGIN SELECT idno, name, yrgoal INTO v_donor FROM new_donor WHERE idno = &in_idno; INSERT INTO some_donor VALUES(v_donor. idno, v_donor. name, v_donor. yrgoal); END; / SET VERIFY ON

ROWTYPE SQL> SELECT * FROM some_donor; IDNO ----23456 11111 NAME YRGOAL --------Susan Ash 100 Stephen Daniels 500 SQL> @ atsd 4 Please enter the donor idno: 22222 PL/SQL procedure successfully completed. SQL> SELECT * FROM some_donor; IDNO ----23456 11111 22222 NAME YRGOAL --------Susan Ash 100 Stephen Daniels 500 Carl Hersey

TABLES PL/SQL tables are the answer to the need for an array structure. An array is basically a temporary table in memory available during the session. They are not database tables! The PL/SQL table is indexed by a binary integer. To declare a PL/SQL table: first define the table structure using TYPE … IS TABLE and then once the type has been created, you can declare the actual table.

TYPE. . TABLE SQL> edit table 1 SET SERVEROUTPUT ON DECLARE TYPE t_table 1 IS TABLE OF VARCHAR 2(12) INDEX BY BINARY_INTEGER; v_table_var t_table 1; v_count_result NUMBER; BEGIN v_table_var(5) : = 'Fifth week'; v_table_var(2) : = 'Second week'; v_table_var(12) : = 'Twelfth week'; v_table_var(6) : = 'Sixth week'; v_count_result: = v_table_var. COUNT; dbms_output. put_line('The count is: '||v_count_result); END; / SET SERVEROUTPUT OFF SQL> @ table 1 The count is: 4 PL/SQL procedure successfully completed.

TYPE. . TABLE SQL> edit table 1 a SET SERVEROUTPUT ON DECLARE Output: TYPE t_table 1 IS TABLE OF VARCHAR 2(12) INDEX BY BINARY_INTEGER; SQL> @ table 1 a v_table_var t_table 1; The current place is: 2 v_count_result NUMBER; The current place is: 5 v_place NUMBER; The current place is: 6 BEGIN The current place is: 12 v_table_var(5) : = 'Fifth week'; The count is: 4 v_table_var(2) : = 'Second week'; v_table_var(12) : = 'Twelfth week'; v_table_var(6) : = 'Sixth week'; v_place : = v_table_var. FIRST; FOR x IN 1. . 4 LOOP dbms_output. put_line('The current place is: ' || v_place); v_place : = v_table_var. NEXT(v_place); END LOOP; v_count_result: = v_table_var. COUNT; dbms_output. put_line('The count is: '||v_count_result); END; / SET SERVEROUTPUT OFF

TYPE. . TABLE SQL> edit table 2 SQL> @table 2 Enter value for in_num: 5 old 6: v_num NUMBER : =&in_num; new 6: v_num NUMBER : =5; The count is: 4 The record exists SET SERVEROUTPUT ON DECLARE PL/SQL procedure successfully completed. TYPE t_table 1 IS TABLE OF VARCHAR 2(12) INDEX BY BINARY_INTEGER; v_table_var t_table 1; v_count_result NUMBER; v_num NUMBER : =&in_num; BEGIN v_table_var(5) : = 'Fifth week'; v_table_var(2) : = 'Second week'; v_table_var(12) : = 'Twelfth week'; v_table_var(6) : = 'Sixth week'; v_count_result: = v_table_var. COUNT; dbms_output. put_line('The count is: '||v_count_result); IF v_table_var. EXISTS(v_num) THEN dbms_output. put_line('The record exists'); ELSE dbms_output. put_line('The record does not exist'); END IF; SQL> @ table 2 END; Enter value for in_num: 8 / old 6: v_num NUMBER : =&in_num; SET SERVEROUTPUT OFF new 6: v_num NUMBER : =8; The count is: 4 The record does not exist PL/SQL procedure successfully completed.

TABLES I am starting out with an input area which I assign the value of South Shore MA. My goal is to search the table until I find a match. To do this, I first set up the table and then I assigned values to the table. Finally I looped through the table displaying my progress until a match was found. When a match is found I exit the loop and display the match. SQL> edit areacodett SET SERVEROUTPUT ON DECLARE input_area VARCHAR 2(20) : ='South Shore MA'; TYPE t_areacode_type IS TABLE OF VARCHAR 2(20) Output: INDEX BY BINARY_INTEGER; v_area t_areacode_type; SQL> @ areacodett v_index NUMBER; Rhode Island 401 BEGIN Southeastern MA 508 v_area(508) : = 'Southeastern MA'; Boston MA 617 v_area(617) : = 'Boston MA'; South Shore MA 781 v_area(781) : = 'South Shore MA'; Match: South Shore MA 781 v_area(401) : = 'Rhode Island'; v_area(999) : = 'region not found'; v_index : = v_area. FIRST; LOOP DBMS_OUTPUT. PUT_LINE(v_area(v_index) ||' ' || v_index); EXIT WHEN v_index = v_area. LAST OR v_area(v_index) = input_area; v_index : = v_area. NEXT(v_index); END LOOP; DBMS_OUTPUT. PUT_LINE('Match: ' || v_area(v_index) ||' ' || v_index); END; / SET SERVEROUTPUT OFF

TYPE. . TABLE In this example, processing stops when Boston is encountered. This causes the EXIT because v_area(v_index) = input_area. SQL> edit areacodett SET SERVEROUTPUT ON DECLARE input_area VARCHAR 2(20) : =‘Boston MA'; TYPE t_areacode_type IS TABLE OF VARCHAR 2(20) INDEX BY BINARY_INTEGER; Output: v_area t_areacode_type; SQL> @ areacodett v_index BINARY_INTEGER; Rhode Island 401 BEGIN Southeastern MA 508 v_area(508) : = 'Southeastern MA'; Boston MA 617 v_area(617) : = 'Boston MA'; v_area(781) : = 'South Shore MA'; Match: Boston MA 617 v_area(401) : = 'Rhode Island'; v_area(999) : = 'region not found'; v_index : = v_area. FIRST; LOOP DBMS_OUTPUT. PUT_LINE(v_area(v_index) ||' ' || v_index); EXIT WHEN v_index = v_area. LAST OR v_area(v_index) = input_area; v_index : = v_area. NEXT(v_index); END LOOP; DBMS_OUTPUT. PUT_LINE('Match: ' || v_area(v_index) ||' ' || v_index); END; / SET SERVEROUTPUT OFF

TYPE…TABLE SQL> edit areacodett In this example, there is no match for XXX MA, so when all elements of the array have been completed, processing ends. The LAST attribute is what makes this work. When v_index which keeps getting incremented to the NEXT number finall equals the last number in the table, processing ends. SET SERVEROUTPUT ON DECLARE input_area VARCHAR 2(20) : =‘XXX MA'; TYPE t_areacode_type IS TABLE OF VARCHAR 2(20) Output: INDEX BY BINARY_INTEGER; SQL> @ areacodett v_area t_areacode_type; Rhode Island 401 v_index NUMBER; Southeastern MA 508 BEGIN Boston MA 617 v_area(508) : = 'Southeastern MA'; South Shore MA 781 v_area(617) : = 'Boston MA'; region not found 999 v_area(781) : = 'South Shore MA'; Match: region not found 999 v_area(401) : = 'Rhode Island'; v_area(999) : = 'region not found'; v_index : = v_area. FIRST; LOOP DBMS_OUTPUT. PUT_LINE(v_area(v_index) ||' ' || v_index); EXIT WHEN v_index = v_area. LAST OR v_area(v_index) = input_area; v_index : = v_area. NEXT(v_index); END LOOP; DBMS_OUTPUT. PUT_LINE('Match: ' || v_area(v_index) ||' ' || v_index); END; / SET SERVEROUTPUT OFF