In this blog, I will explain about
PLSQL Record Type and Table Type With Example.
Please don't forget to leave a comment/suggestion.
PLSQL
Record Type
A record
is a group of related data items stored in fields, each with its own name
and datatype. You can think of a record as a variable that can hold a table
row or some columns from a table row. The fields correspond to table columns.
Understanding
PL/SQL Records
Records are composed of a group of
fields, similar to the columns in a row. The %ROWTYPE attribute lets
you declare a PL/SQL record that represents a row in a database table, without
listing all the columns. Your code keeps working even after columns are added
to the table. If you want to represent a subset of columns in a table, or
columns from different tables, you can define a view or declare a cursor to
select the right columns and do any necessary joins, and then apply %ROWTYPE to
the view or cursor.
Defining a Record
The record type can be defined as
below :-
TYPE
type_name IS
RECORD
(
field_name1 datatype1 [NOT NULL] [:= DEFAULT EXPRESSION],
field_name2 datatype2 [NOT NULL] [:= DEFAULT
EXPRESSION],
...
field_nameN datatypeN [NOT NULL] [:= DEFAULT
EXPRESSION);
record-name
type_name;
Here "record-name" is
object of "type_name". we can not access record type directly with
type_name.
Example:-
DECLARE
TYPE books IS RECORD
(title varchar(50),
author
varchar(50),
subject varchar(100),
book_id
number);
book1 books;
book2 books;
Assigning Values To PLSQL record
Type:-
To access any field of a record we
use (.) dot operator.
SYNTAX:-
<type_name>.<field_name>
Example:-
DECLARE
type books is record
(title
varchar(50),
author
varchar(50),
subject
varchar(100),
book_id
number);
book1 books;
book2 books;
BEGIN
-- Book 1 specification
book1.title := 'C
Programming';
book1.author := 'Nuha
Ali ';
book1.subject := 'C
Programming Tutorial';
book1.book_id :=
6495407;
-- Book 2 specification
book2.title := 'Telecom
Billing';
book2.author := 'Zara
Ali';
book2.subject :=
'Telecom Billing Tutorial';
book2.book_id :=
6495700;
-- Print book 1 record
dbms_output.put_line('Book 1 title : '|| book1.title);
dbms_output.put_line('Book 1 author : '|| book1.author);
dbms_output.put_line('Book 1 subject : '|| book1.subject);
dbms_output.put_line('Book 1 book_id : ' || book1.book_id);
-- Print book 2 record
dbms_output.put_line('Book 2 title : '|| book2.title);
dbms_output.put_line('Book 2 author : '|| book2.author);
dbms_output.put_line('Book 2 subject : '|| book2.subject);
dbms_output.put_line('Book 2 book_id : '|| book2.book_id);
END;
PLSQL
Table Type
Both PLSQL Table Types i.e. Index-by
tables and Nested tables have same structure and their rows are accessed using
subscript notation.
Index-By
Tables :-
Index-By Table is also called
Associate Arrary. Index-by table is a set of key value pair, where its
subscript is called as key and value assigned to it called as value. Each key
in Index-by table is unique and it can either be Number or String.
Syntax :-
TYPE type_name IS TABLE OF
element_type [NOT NULL] INDEX BY subscript_type;
table_name type_name;
Declaring and Using Index-By Table:-
DECLARE
--
Associative array indexed by string:
TYPE book IS TABLE OF NUMBER -- Associative array type
INDEX BY VARCHAR2(64);
book_price book; -- Associative array
variable
i
VARCHAR2(64);
BEGIN
--
Add new elements to associative array:
book_price('ABC') := 200;
book_price('DEF') := 750;
book_price('GHI') := 1000;
-- Change value associated with key 'ABC':
book_price('Smallville') := 2001;
-- Print associative array:
i
:= book_price.FIRST;
WHILE i IS NOT NULL LOOP
DBMS_Output.PUT_LINE
('Price of ' || i || ' is ' || TO_CHAR(book_price(i)));
i := book_price.NEXT(i);
END LOOP;
END;
Nested Tables :-
A nested table is
one-dimensional array with arbitrary number of elements whose size can increase dynamically.
Nested table are like Index-By Tables, but there is no INDEX BY clause in
Nested Tables.
Syntax:-
TYPE type_name IS TABLE
OF element_type [NOT NULL];
table_name type_name;
A nested table can be stored in a database column and so it could be used for
simplifying SQL operations where you join a single-column table with a larger
table. An associative array(Index-By table) cannot be stored in the database.
Difference between Nested Table and Array:-
Nested tables are like arrays where in both the case data is stored in one
dimension with below differences:-
- An array has a declared number of elements, but a
nested table does not. The size of a nested table can increase
dynamically.
- An array is always dense, i.e., it always has
consecutive subscripts. A nested array is dense initially, but it can
become sparse when elements are deleted from it.
Declaring
and Using Nested table:-
DECLARE
TYPE
books_table IS TABLE OF VARCHAR2(10);
TYPE
price IS TABLE OF INTEGER;
names
books_table;
rate
price;
total
integer;
BEGIN
names :=
books_table('ABC', 'DEF', 'GHI', 'JKL', 'MNO');
marks:=
rate(98, 97, 78, 87, 92);
total :=
names.count;
dbms_output.put_line('Total '|| total || ' books');
FOR i IN
1 .. total LOOP
dbms_output.put_line('Book:'||names(i)||', Price:' || rate(i));
end loop;
END;
Using PLSQL Record Type and Table Type Together
DECLARE
TYPE
lr_order_detail_rec IS RECORD
( l_order_number oe_order_headers_all.order_number%TYPE,
l_line_number VARCHAR2(20),
l_ordered_item oe_order_lines_all.ordered_item%Type,
l_line_status oe_order_lines_all.flow_status_code%TYPE,
l_order_status oe_order_headers_all.flow_status_code%TYPE);
TYPE
lt_order_detail_tab IS TABLE OF lr_order_detail_rec
INDEX BY BINARY_INTEGER;
lt_order_detail_tab_o lt_order_detail_tab;
CURSOR
lc_order_cur
IS
SELECT order_number, oe_flex_util.get_concat_value (oola.line_number,
oola.shipment_number,
oola.option_number,
oola.component_number,
oola.service_number)
line_num,
oola.ordered_item,
oola.flow_status_code,
ooha.flow_status_code
FROM oe_order_headers_all ooha, oe_order_lines_all oola
WHERE ooha.header_id = oola.header_id
AND ooha.order_number = '52424905';
BEGIN
OPEN
lc_order_cur;
LOOP
FETCH lc_order_cur BULK COLLECT INTO
lt_order_detail_tab_o;
EXIT WHEN lc_order_cur%NOTFOUND;
END LOOP;
CLOSE
lc_order_cur;
dbms_output.put_line('lt_order_detail_tab_o.count:- ' ||
lt_order_detail_tab_o.count);
dbms_output.put_line('Order Number || Line Number || ordered Item || Order
Status || Line Status ' );
FOR i IN
lt_order_detail_tab_o.FIRST..lt_order_detail_tab_o.LAST
LOOP
dbms_output.put_line( lt_order_detail_tab_o(i).l_order_number|| '
'||
lt_order_detail_tab_o(i).l_line_number || ' ' ||
lt_order_detail_tab_o(i).l_ordered_item ||' '||
lt_order_detail_tab_o(i).l_order_status ||' ' ||
lt_order_detail_tab_o(i).l_line_status);
END
LOOP;
END;
Kindly leave a comment/suggestion.
