— scalar Data types:
declare
v_first_name employees.first_name%type;
v_salary employees.salary%type;
begin
select first_name,salary into v_first_name,v_salary
from employees where employee_id=120;
dbms_output.put_line(‘The first name is: ‘||v_first_name);
dbms_output.put_line(‘The salary is: ‘||v_salary);
end;
PL/SQL lets you define two kinds of composite data types: collection and record.
A composite data type stores values that have internal components. You can pass entire composite variables to subprograms as parameters, and you can access internal components of composite variables individually. Internal components can be either scalar or composite. You can use scalar components wherever you can use scalar variables. You can use composite components wherever you can use composite variables of the same type.
In a collection, the internal components always have the same data type, and are called elements. You can access each element of a collection variable by its unique index, with this syntax: variable_name(index). To create a collection variable, you either define a collection type and then create a variable of that type or use %TYPE.
In a record, the internal components can have different data types, and are called fields. You can access each field of a record variable by its name, with this syntax: variable_name.field_name. To create a record variable, you either define a RECORD type and then create a variable of that type or use %ROWTYPE or %TYPE.
Record Type:
============
What is Record Type?
A Record type is a complex data type which allows the programmer to create a new data type with the desired column structure.
It is similar structures in C
It groups one or more column to form a new data type
These columns will have its own name and data type
A Record type can accept the data
Record type simply means a new data type. Once the record type is created, it will be stored as a new data type in the database and the same shall be used to declare a variable in programs.
It will use the keyword ‘TYPE’ to instruct the compiler that it is creating the new data type.
It can be created at “database level” which can be stored as database objects, used all-over the database or it can be created at the “subprogram levels”, which is visible only inside the subprograms.
The database level record type can also be declared for the table columns so that single column can hold the complex data.
The data in these data type can be accessed by referring to their variable_name followed by period operator (.) followed by column_name i.e. ‘<record_type_variable_name>.<column_name>’
You can not create a RECORD type at schema level, To define a RECORD type specify its name and define its field.
Syntax:
=======
CREATE TYPE <type_name_db> IS RECORD
(
<column 1> <datatype>,
);
Syntax for declaration at subprogram level:
==========================================
DECLARE
TYPE <type_name> IS RECORD
(
<columnl> <datatype>,
);
BEGIN
<execution_section>;
END;
%RowType:
========
declare
v_emp_rec employees%rowtype;
begin
select * into v_emp_rec from employees where employee_id=120;
dbms_output.put_line(‘The first name is: ‘||v_emp_rec.first_name);
dbms_output.put_line(‘The phone_numebr is: ‘||v_emp_rec.phone_number);
end;
Record: TYPE
declare
type empl_record_type is record (first_name varchar2(30), salary number(8)); — definition
emp_rec empl_record_type; — declare
begin
emp_rec.first_name:=’John’;
emp_rec.salary:=’20000′;
dbms_output.put_line(emp_rec.first_name ||’ ,’||emp_rec.salary);
end;
********************************************************************************
Record: TYPE with constructor:
declare
type emp_rec_type is record (first_name varchar2(30), salary number); — defintion
emp_rec emp_rec_type := emp_rec_type(‘John’,2000); — declare
begin
dbms_output.put_line(’emp_rec.first_name:=’||emp_rec.first_name);
end;
********************************************************************************
declare
type emp_rec_type is record (first_name varchar2(20),salary number(8));
emp_rec emp_rec_type;
begin
select first_name, salary into emp_rec from employees where employee_id=120;
dbms_output.put_line(’emp_rec.first_name is :=’||emp_rec.first_name);
dbms_output.put_line(’emp_rec.salary is :=’||emp_rec.salary);
end;
declare
type emp_rec_type is record (first_name varchar2(20),salary number(8));
emp_rec emp_rec_type;
begin
select first_name, salary into emp_rec from employees where employee_id=120;
dbms_output.put_line(’emp_rec.first_name is :=’||emp_rec.first_name||’ emp_rec.salary is :=’||emp_rec.salary);
end;
declare
type emp_rec_type is record (first_name varchar2(20),salary number(8));
emp_rec emp_rec_type;
begin
select first_name, salary into emp_rec from employees where employee_id=120;
dbms_output.put_line(’emp_rec.first_name is :=’||emp_rec.first_name||’ emp_rec.salary is :=’||emp_rec.salary);
dbms_output.put_line(’emp_rec.phone_number is :=’||emp_rec.phone_numer);
end;
— Not null constraint in record
set serveroutput on
declare
type emp_rec_type is record (
first_name varchar2(1000) not null:=’John’,
salary number(10));
emp_rec emp_rec_type;
begin
–emp_rec.first_name:=”;
emp_rec.salary:=100;
dbms_output.put_line(emp_rec.first_name ||’ ,’||emp_rec.salary);
end;
/
———————————————————————————
Collections:
============
Collections are used in some of the most important performance optimization features of PL/SQL, such as
BULK COLLECT. SELECT statements that retrieve multiple rows with a single fetch, increasing the speed of data retrieval.
FORALL. Inserts, updates, and deletes that use collections to change multiple rows of data very quickly.
Table functions. PL/SQL functions that return collections and can be called in the FROM clause of a SELECT statement.
A Collection is an ordered group of logically related elements.
Main Purpose of using collection is to improve performance.
Using collection we can load all records once from database into local memory and then we can perform operations on it and save it back to database. It reduces calls to database.
Oracle provided 3 types of collection
======================================
VARRAY – variable array
Nested Table
Associative Array
Collections are used along with
===============================
Bulk collect
Forall
Table functions to improve performance.
You can also use collections to work with lists of data in your program that are not stored in database tables.
Let’s start by defining a common collections vocabulary:
Index value. The location of the data in a collection. Index values are usually integers but one type of collection can also be strings.
Element. The data stored at a specific index value in a collection. Elements in a collection are always of the same type (all of them are strings, dates, or records). PL/SQL collections are homogeneous.
Sparse. A collection is sparse if there is at least one index value between the lowest and highest defined index values that is not defined. For example, a sparse collection has an element assigned to index value 1 and another to index value 10 but nothing in between. The opposite of a sparse collection is a dense one.
Method. A collection method is a procedure or function that either provides information about the collection or changes the contents of the collection. Methods are attached to the collection variable with dot notation (object-oriented syntax), as in my_collection.FIRST.
Three types of collections
==========================
Collections have been enhanced in several ways through the years and across Oracle Database versions. There are now three types of collections to choose from, each with its own set of characteristics and each best suited to a different circumstance:
Associative array. The first type of collection available in PL/SQL, this was originally called a “PL/SQL table” and it can be used only in PL/SQL blocks. Associative arrays can be sparse or dense and can be indexed by integer or string.
Nested table. The nested table can be used in PL/SQL blocks, in SQL statements, and as the data type of columns in tables. Nested tables can be sparse but are almost always dense. They can be indexed only by integer. You can use the MULTISET operator to perform set operations and to perform equality comparisons on nested tables.
Varray. The varray (variable-size array) can be used in PL/SQL blocks, in SQL statements, and as the data type of columns in tables. Varrays are always dense and indexed by integer. When a varray type is defined, you must specify the maximum number of elements allowed in a collection declared with that type.
The associative array is the most commonly used collection type, but nested tables have some powerful, unique features (such as MULTISET operators) that can simplify the code needed to use your collection.
VARRAY – variable array
Nested Table
Associative Array
1. VARRAYS: variable size array
arrays
pre-defined size
index starts with 1
cannot delete elements by index.
2. Nested Tables:
— List
— Variable Size
— Index starts with 1
— can delete the element with in the array
— will not define maximum limit
3. Associative Arrays – Index by tables:
— Indexing can be done with strings
— Map
— Key:value
Steps in Collections:
=====================
1. Define
2. Declare
3. Intialize
4. Assign
5. Access
——————————————————————————–
VARRAY:
——-
declare
type v_array_type is varray(7) of varchar2(30);
address v_array_type:=v_array_type(null,null,null,null,null,null,null);
begin
address(1):=’G2′;
address(2):=’ABC flat’;
address(3):=’kalyan’;
address(4):=’Mumbai new’;
address(5):=’Mumbai’;
address(6):=’India’;
address(7):=’546354′;
dbms_output.put_line(‘The city f customer is: ‘||address(4));
end;
Declare
Type v_array_type is varray(8) of varchar2(40);
v_color v_array_type:=v_array_type(null,null,null,null,null,null,null,null);
begin
v_color(1) := ‘Red’;
v_color(2) := ‘Black’;
v_color(3) := ‘Blue’;
v_color(4) := ‘Green’;
v_color(5) := ‘Yellow’;
v_color(6) := ‘Gray’;
v_color(7) := ‘White’;
v_color(8) := ‘Orange’;
dbms_output.put_line(‘v_color(4) ‘||v_color(4));
end;
Collection Methods:
===================
limit – maximum number of value of varray
count – how many elements stored in the varray(actually initialzed)
first – First index value (immutable) – could not delete any element
last – last index value
trim – last elements will be deleted
delete – delete all the elements in varray, can not delete specific element
extend – extend the number of elements
prior(n) – index of prior element
next(n) – index of next element
——————————————————————————–
Declare
Type v_array_type is varray(8) of varchar2(40);
v_color v_array_type:=v_array_type(null,null,null);
begin
v_color(1) := ‘Red’;
v_color(2) := ‘Black’;
v_color(3) := ‘Blue’;
dbms_output.put_line(‘v_color(1) ‘||v_color(1));
dbms_output.put_line(‘v_color.limit ‘||v_color.limit);
dbms_output.put_line(‘v_color.count ‘||v_color.count);
dbms_output.put_line(‘v_color.first ‘||v_color.first);
dbms_output.put_line(‘v_color.last ‘||v_color.last);
dbms_output.put_line(‘v_color.prior ‘||v_color.prior(3));
dbms_output.put_line(‘v_color.next ‘||v_color.next(3));
v_color.extend();
–v_color.extend(3);
dbms_output.put_line(‘v_color.count ‘||v_color.count);
dbms_output.put_line(‘v_color.next ‘||v_color.next(3));
v_color.trim();
–v_color.trim(2);
dbms_output.put_line(‘v_color.count ‘||v_color.count);
v_color.delete();
dbms_output.put_line(‘v_color.count ‘||v_color.count);
end;
——————————————————————————————-
Nested Tables:
Declare
Type v_nested_table_type is table of varchar2(40);
v_color v_nested_table_type:=v_nested_table_type(null,null,null,null,null,null,null,null);
begin
v_color(1) := ‘Red’;
v_color(2) := ‘Black’;
v_color(3) := ‘Blue’;
v_color(4) := ‘Green’;
v_color(5) := ‘Yellow’;
v_color(6) := ‘Gray’;
v_color(7) := ‘White’;
v_color(8) := ‘Orange’;
dbms_output.put_line(‘v_color(4) ‘||v_color(4));
end;
—————————————————
Nested table will not have limit:
Declare
Type v_nested_table_type is table of varchar2(40);
v_color v_nested_table_type:=v_nested_table_type(null,null,null);
begin
v_color(1) := ‘Red’;
v_color(2) := ‘Black’;
v_color(3) := ‘Blue’;
dbms_output.put_line(‘v_limit ‘||v_color.limit);
dbms_output.put_line(‘v_count ‘||v_color.count);
v_color.extend(4);
v_color(4) := ‘Green’;
dbms_output.put_line(‘v_color(4) ‘||v_color(4));
dbms_output.put_line(‘v_limit ‘||v_color.limit);
dbms_output.put_line(‘v_count ‘||v_color.count);
dbms_output.put_line(‘v_first_index ‘||v_color.first); — index of first element
dbms_output.put_line(‘v_last_index ‘||v_color.last);
end;
**************************************************************
Declare
Type v_nested_table_type is table of varchar2(40);
v_color v_nested_table_type:=v_nested_table_type(null,null,null);
begin
v_color(1) := ‘Red’;
v_color(2) := ‘Black’;
v_color(3) := ‘Blue’;
dbms_output.put_line(‘v_count ‘||v_color.count);
v_color.extend(4);
v_color(4) := ‘Green’;
dbms_output.put_line(‘v_color(4) ‘||v_color(4));
dbms_output.put_line(‘v_limit ‘||v_color.limit); — will give null since it doesn’t have upper limit
dbms_output.put_line(‘v_count ‘||v_color.count);
dbms_output.put_line(‘v_first_index ‘||v_color.first); — index of first element
dbms_output.put_line(‘v_last_index ‘||v_color.last);
dbms_output.put_line(‘v_last_prior ‘||v_color.prior(3));
–v_color.delete(2);
v_color.delete;
dbms_output.put_line(‘v_count ‘||v_color.count);
end;
———————————————————————-
Declare
Type v_nested_table_type is table of varchar2(40);
v_color v_nested_table_type:=v_nested_table_type(null,null,null);
begin
v_color(1) := ‘Red’;
v_color(2) := ‘Black’;
v_color(3) := ‘Blue’;
v_color.extend(4);
v_color(4) := ‘Green’;
v_color.trim(2);
v_color.delete(2);
dbms_output.put_line(‘v_color(4) ‘||v_color(4));
dbms_output.put_line(‘v_limit ‘||v_color.limit);
dbms_output.put_line(‘v_count ‘||v_color.count);
dbms_output.put_line(‘v_first_index ‘||v_color.first); — index of first element
dbms_output.put_line(‘v_last_index ‘||v_color.last);
if v_color.exists(2) then
dbms_output.put_line(‘v_color(2) ‘||v_color(2));
else
dbms_output.put_line(‘Elements is not available’);
end if;
end;
———————————————————————————-
Associative array: Index by table
‘plsql’: ‘Procedural language for SQL’
‘sql’ : ‘Structured Query language’
— In both VARRAY and Nested tables, the index is predefined and maintained by Oracle
— In Associative array the index and the elements will be defined by user
— Associative array is set of key value pair. Each key is unique and it is used to locate the elements
variable_name(index)
Declare
Type v_array_type is table of varchar2(40) index by varchar2(10);
v_color v_array_type;
begin
v_color(‘color1’) := ‘Red’;
v_color(‘color2’) := ‘Black’;
v_color(‘color3’) := ‘Blue’;
v_color(‘color4’) := ‘Green’;
dbms_output.put_line(‘v_color(3) ‘||v_color(‘color3’));
end;
******************************************************************
Declare
Type v_array_type is table of varchar2(40) index by varchar2(10);
v_color v_array_type;
begin
v_color(‘color1’) := ‘Red’;
v_color(‘color2’) := ‘Black’;
v_color(‘color3’) := ‘Blue’;
v_color(‘color4’) := ‘Green’;
dbms_output.put_line(‘v_color(3) ‘||v_color(‘color3’));
dbms_output.put_line(‘v_color(3).first ‘||v_color.first);
dbms_output.put_line(‘v_color(3).first ‘||v_color.last);
dbms_output.put_line(‘v_color(3).count ‘||v_color.count);
–v_color.delete;
–v_color.delete(‘color3’);
dbms_output.put_line(‘v_color(3).count ‘||v_color.count);
end;
Methods will not be available:
Limit
Trim
Extend
——————————————————————————————–
Bulk Collect and Bulk bind:
— If we process or select huge volume of data PLSQL block, performance issue
— Bulk collect — selecting the data table
— Bulk bind — processing the data
=========================================================================================
— select statement in PLSQL block
set serveroutput on;
clear screen;
declare
v_first_name hr.employees.first_name%type;
v_emp_salary hr.employees.salary%type;
begin
select first_name,salary into v_first_name,v_emp_salary from employees where employee_id=120;
dbms_output.put_line(‘The first_name of the employee is ‘||v_first_name);
dbms_output.put_line(‘The salary of the employee is ‘||v_emp_salary);
exception
when no_data_found then
dbms_output.put_line(‘No data found for this employee’);
when too_many_rows then
dbms_output.put_line(‘Many rows are returned from base table’);
end;
–– Using cursor
declare
vemp_name EMPLOYEES.FIRST_NAME%TYPE;
vemp_salary EMPLOYEES.salary%TYPE;
cursor c1 is select first_name,salary from employees;
begin
open c1;
loop
fetch c1 into vemp_name,vemp_salary;
exit when c1%notfound;
dbms_output.put_line(vemp_name||vemp_salary);
end loop;
close c1;
end;
— Bulk collect and Composite variable:
=======================================
set SERVEROUTPUT ON
declare
type nt_salary_type is table of number(10);
nt_salary nt_salary_type := nt_salary_type();
begin
select salary bulk collect into nt_salary from employees;
for i in nt_salary.first..nt_salary.last
loop
dbms_output.put_line(nt_salary(i));
end loop;
end;
