1 / 15

PL/SQL User Defined Types Record and Table

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).

noreen
Download Presentation

PL/SQL User Defined Types Record and Table

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  2. 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.

  3. Implicit cursor 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.

  4. 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 TYPE - RECORD SQL> edit atsd1 SET VERIFY OFF ACCEPT in_idno PROMPT 'Please enter the donor idno: ' DECLARE TYPE rec_donor IS RECORD (rec_idVARCHAR2(5),rec_nameVARCHAR2(15), rec_yrgoal NUMBER(7,2)); a_rec_donor 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> @ atsd1 Please enter the donor idno: 23456 PL/SQL procedure successfully completed. SQL> SELECT * FROM some_donor; IDNO NAME YRGOAL ----- --------------- --------- 23456 Susan Ash 100

  5. 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 TYPE - RECORD SQL> edit atsd2 SET VERIFY OFF ACCEPT in_idno PROMPT 'Please enter the donor idno: ' DECLARE TYPE rec_donor IS RECORD (rec_id VARCHAR2(5), rec_name VARCHAR2(15), rec_yrgoal NUMBER(7,2)); a_rec_donorrec_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> @atsd2 Please enter the donor idno: 11111 PL/SQL procedure successfully completed. SQL> SELECT * FROM some_donor; IDNO NAME YRGOAL ----- --------------- --------- 23456 Susan Ash 100 11111 Stephen Daniels 500

  6. TYPE - RECORD SQL> edit atsd2 SET VERIFY OFF ACCEPT in_idno PROMPT 'Please enter the donor idno: ' DECLARE TYPE rec_donor IS RECORD (rec_id VARCHAR2(5), rec_name VARCHAR2(15), rec_yrgoal NUMBER(7,2)); a_rec_donor 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> @ atsd2a 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

  7. SQL> DESC some_donor Name Null? Type ------------------------------- -------- ---- IDNO VARCHAR2(5) NAME VARCHAR2(15) YRGOAL NUMBER(7,2) ROWTYPE SQL> edit atsd4 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

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

  9. 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.

  10. TYPE..TABLE SQL> edit table1 SET SERVEROUTPUT ON DECLARE TYPE t_table1 IS TABLE OF VARCHAR2(12) INDEX BY BINARY_INTEGER; v_table_var t_table1; 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> @ table1 The count is: 4 PL/SQL procedure successfully completed.

  11. TYPE..TABLE SQL> edit table1a SET SERVEROUTPUT ON DECLARE TYPE t_table1 IS TABLE OF VARCHAR2(12) INDEX BY BINARY_INTEGER; v_table_var t_table1; v_count_result NUMBER; v_place 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_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 Output: SQL> @ table1a The current place is: 2 The current place is: 5 The current place is: 6 The current place is: 12 The count is: 4

  12. SQL> @table2 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 PL/SQL procedure successfully completed. TYPE..TABLE SQL> edit table2 SET SERVEROUTPUT ON DECLARE TYPE t_table1 IS TABLE OF VARCHAR2(12) INDEX BY BINARY_INTEGER; v_table_vart_table1; 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; END; / SET SERVEROUTPUT OFF SQL> @ table2 Enter value for in_num: 8 old 6: v_num NUMBER :=&in_num; new 6: v_num NUMBER :=8; The count is: 4 The record does not exist PL/SQL procedure successfully completed.

  13. 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 VARCHAR2(20) :='South Shore MA'; TYPE t_areacode_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER; v_areat_areacode_type; v_index NUMBER; BEGIN v_area(508) := 'Southeastern MA'; v_area(617) := 'Boston MA'; v_area(781) := 'South Shore MA'; 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 Output: SQL> @ areacodett Rhode Island 401 Southeastern MA 508 Boston MA 617 South Shore MA 781 Match: South Shore MA 781

  14. 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 VARCHAR2(20) :=‘Boston MA'; TYPE t_areacode_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER; v_areat_areacode_type; v_index BINARY_INTEGER; BEGIN v_area(508) := 'Southeastern MA'; v_area(617) := 'Boston MA'; v_area(781) := 'South Shore MA'; 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 Output: SQL> @ areacodett Rhode Island 401 Southeastern MA 508 Boston MA 617 Match: Boston MA 617

  15. TYPE…TABLE 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. SQL> edit areacodett SET SERVEROUTPUT ON DECLARE input_area VARCHAR2(20) :=‘XXX MA'; TYPE t_areacode_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER; v_areat_areacode_type; v_index NUMBER; BEGIN v_area(508) := 'Southeastern MA'; v_area(617) := 'Boston MA'; v_area(781) := 'South Shore MA'; 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 WHENv_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 Output: SQL> @ areacodett Rhode Island 401 Southeastern MA 508 Boston MA 617 South Shore MA 781 region not found 999 Match: region not found 999

More Related