Difference between for and forall in oracle sql plsql interview question and answer.

-

 for vs forall oracle






For

Forall

It is an actual loop which will go to the records or rows one by one and do processing.

It is not an actual loop which will not go to the records or rows one by one and do processing.

Row processing can be done.

Row processing cannot be done.

Bulk DML operation is not performed. It is suitable for individual operation.

Bulk DML operation is performed.

It is slower than forall.

It is faster than for loop when it issues the series of static or dynamic statements.

It is not having performance like Forall.

It is having better performance than FOR, when it is working with collection.

It is the loop.

It is not the loop because it is the statement.

It is an iterative statement that executes for each value specified within the range.

It is not an iterative statement, instead it is declarative statement to execute all the DML statements once with all the given values in the collection.

Number of statements are not fixed between LOOP and END LOOP.

Only one DML statement is allowed after Forall keyword.

Iteration is done for each value present in the collection range. Context switching occurs between SQL & PLSQL engine, if any sql statements are used within the loop.

Iteration is not done. It executes the DML statement which is used for the entire collection as input. No content switching occurs.

Invidual records of the collection can be accessed and used within FOR Loop.

Individual records of the collection cannot be accessed and used. Instead, collection as the whole will be used by DML statement.

With the help of clause,FORALL is used with the nested tables where some elements have been deleted and associative array that have the number subscripts.Here,clause refers to the value of the index variable along with the element subscript of the specified collection. Insert, update and delete operations are using the collection to do changes for the multiple rows of the data very quickly.




QUERIES FOR PRACTICE:

create table name_list_emp(ename varchar2(20));

DECLARE
TYPE emp_name_list_type IS TABLE OF VARCHAR(20);
lv_emp_name_list emp_name_list_type:=emp_name_list_type();
BEGIN
lv_emp_name_list.extend(6);
lv_emp_name_list(1):='RAHUL';
lv_emp_name_list(2):='RAKESH';
lv_emp_name_list(3):='RAHIM';
lv_emp_name_list(4):='MOHAN';
lv_emp_name_list(5):='NAFIS';
lv_emp_name_list(6):='SHYAM';
FOR I in lv_emp_name_list.first..lv_emp_name_list.last
LOOP
insert into name_list_emp values(lv_emp_name_list(i));
END LOOP;
END;
/

select * from name_list_emp;

truncate table name_list_emp;

DECLARE
TYPE emp_name_list_type IS TABLE OF VARCHAR(20);
lv_emp_name_list emp_name_list_type:=emp_name_list_type();
BEGIN
lv_emp_name_list.extend(6);
lv_emp_name_list(1):='RAHUL';
lv_emp_name_list(2):='RAKESH';
lv_emp_name_list(3):='RAHIM';
lv_emp_name_list(4):='MOHAN';
lv_emp_name_list(5):='NAFIS';
lv_emp_name_list(6):='SHYAM';
FORALL I in lv_emp_name_list.first..lv_emp_name_list.last
insert into name_list_emp values(lv_emp_name_list(i));
END;
/

select * from name_list_emp;

DECLARE
TYPE emp_name_list_type IS TABLE OF VARCHAR(20);
lv_emp_name_list emp_name_list_type:=emp_name_list_type('RAVI','ANURAG','CHANDAN','HIMADRI','MAHESH','SANTOSH');
BEGIN
FOR I in lv_emp_name_list.first..lv_emp_name_list.last
LOOP
insert into name_list_emp values(lv_emp_name_list(i));
END LOOP;
END;
/

select * from name_list_emp;

DECLARE
TYPE emp_name_list_type IS TABLE OF VARCHAR(20);
lv_emp_name_list emp_name_list_type:=emp_name_list_type('SUSHIL','SHUBAM','SABIR','RUPESH','ANKUR','SUNIL');
BEGIN
FORALL I in lv_emp_name_list.first..lv_emp_name_list.last
insert into name_list_emp values(lv_emp_name_list(i));
END;
/

select * from name_list_emp;




 






Advantages of Forall:

It transfers the data effectively from the collection into the table.
It reduces the context switching between SQL and plsql engine and help to improve the performance of DML statement.
Time taken to perform operation of DML statement on the table is lesser.

Disadvantages of Forall:

It must be followed by the single DML statement.
It is used in server-side programming, and it is not used in the client-side programming.
Collection subscript must be the index variable and expression in subscript is not allowed.










Comments

Post a Comment

Popular posts from this blog

Difference between union and union all in oracle sql plsql

-
Image
"This is one of the most important questions that interviewer might be asking in an interview.Infyosis,Linked group,Kyndryl,Vodafone Idea, IBM and Mphasis etc." INTRODUCTION: It is said to be set operators which is used to combine, add or concatenate two or more result sets. It is used in the multiple select statements where to combine to produce the final desired results. In other words, multiple select statements of similar column name in same order and having same data type to produce the unified result. It merges the content of the two structurally compatible organized tables into single table.  In MIS datalake,when you are adding the modules of GST reconciliation which is to be used in micros in Excel or VB script, union and union all set operator is preferred for eliminating duplicate records or keeping duplicate records as per business requirement. There are several alternatives like bulk collect,join,set operators like intersect & minus for combining the data from...
Read more

Explain oracle pl sql datatypes interview question and answer for fresher and experienced.

-
Image
 Oracle PLSQL Data Types : PLSQL constant,value,variable,parameter,function which return the value are having the data type. It will determine the storage space information occupied and occurred in the memory or storage format and range constraints for valid values and operations as per specified size limit. It will determine how the data will be going to stored, handled and treated by the oracle engine during the period of processing and storage. It is used in the PLSQL blocks. It allows you to define your sub-types. PLSQL codes are embedded into the java program. Subtypes are used to make the data types compatible with data types used in the PLSQL program while embedding the PLSQL code into the java program. Subtype will be compatible with ANSI/ISO & IBM. It provides the pre-defined data types. Pre-defined data types are categorized into the four types: Composite LOB Reference Scalar Scalar Datatypes: It stores the values without the internal components. It holds the single ...
Read more

Difference between full join and cross join-oracle sql plsql

-
Image
 Difference between full join and cross join in sql:- They behave differently to combine the rows from the two tables. FULL OUTER JOIN: It is also called as the full join .It will return all the rows when there is either match in the left table or right table. If there is no match, it will return NULL for the rows of the columns which is lacking the matching rows. Syntax: select c.*,d.*  from tableC c FULL OUTER JOIN tableD d on c.id=d.id; select e.ename,d.dname from emp e FULL OUTER JOIN dept d ON e.deptno=d.deptno; When you want to retain the unmatched rows i.e operations which is not present in the emp table and having deptno as 40.So,ename is appeared as NULL. CROSS JOIN: It returns the cartesian product of both the tables i.e. Every row of the table A is combined with every row of the table B.ON condition is not used due to not match rows. Syntax: select c.*.d.* from tableC c CROSS JOIN tableD d; select e.ename,d.dname from emp e CROSS JOIN dept d; ENAME ...
Read more