What's up internet?
I am Manish from RebellionRider.com.
Welcome to the second tutorial in the FORALL series and the sixth one in bulk data processing
series.
Today we will learn how to use FORALL statement with the first bound clause which is Lower
and Upper bound.
Before starting let's understand one thing; FORALL statement does the same work as the
bulk collect clause but in the inverse manner.
For example, with bulk collect we were fetching the data from the tables and storing it into
the collection, but now with FORALL statement we will fetch the data from the collection
and store it into the table.
That being said let's come back to our first bound clause of FORALL statement which is
Lower and upper bound.
In lower and upper bound we have to specify the valid range of consecutive
Taking both the points which we discussed so far let's do an example and try to understand
FORALL statement with Lower and Upper bound clause more clearly.
So with FORALL statement we fetch the data from the Collection and store it into the
table which means we need a collection with some data and a table into which we can dump
that data.
Let's first set the server output on and then start with creating a new table for holding
the data.
SET SERVEROUTPUT ON; CREATE TABLE tut_77 (
Mul_tab NUMBER(5) );
Here is the table.
Just to keep the demonstration simple, we have a table with no constraints on it and
consists only of column "mul_tab" for holding the multiplication table.
No fancy stuff, I think it is simple enough to easily understand the concept.
Let's move ahead.
Next we will write a PL/SQL block into which we will be creating a collection and then
populating it with some data.
Then using FORALL statement we will dump that data into our table tut_77.
So let's do it.
DECLARE TYPE My_Array IS TABLE OF NUMBER INDEX BY
PLS_INTEGER; col_var my_Array;
tot_rec NUMBER; Before writing the execution section, let's
quickly take a look at what we have done in the declaration section.
Here in this section we have declared an Associative array with the name "my_Array" along with
its collection variable.
For more details on Associative arrays you can refer to my PL/SQL Tutorial number 57,
I will leave the link in the description below.
Next to the associative array we have declared another variable with the name "tot_rec"
of NUMBER data type.
We will use this variable to check how many records are fetched from the collection and
stored into the table.
Now let's move ahead.
BEGIN --populate the collection
FOR i IN 1..10 LOOP col_var(i) := 9*i;
END LOOP; We have started our execution section with
a FOR Loop.
Using this Numeric FOR Loop we are populating our collection "My_Array" with the multiplication
table of 9.
Task number one: Creating a table, is done Task Number two: Declaring and populating
the collection is also done.
These two tasks set the stage for the demonstration of the FORALL statement.
Now we are left with the last task.
Here we will take the data from the collection 'my_array' and store it into the table
tut_77 using FORALL statement.
So let's write the FORALL statement.
FORALL idx IN 1..10 INSERT INTO tut_77 (mul_tab)
VALUES(col_var(idx)); Here is our FORALL statement.
In this statement we have used 1 as our lower bound and 10 as our upper bound.
The DML operation which we are performing using our FORALL statement is INSERT.
Using this INSERT DML we are inserting values into our table tut_77.
So what are these values?
These are the values which we inserted into the collection My_Array.
As you know that, there are two mandatory rules that must be followed while writing
the FORALL statement.
These rules are: 1.
Firstly a FORALL statement can have only one DML statement at a time.
2.
Secondly that DML statement in its either VALUES or WHERE clause must reference the
collection whose indexes we used in our bound clause.
Let's analyze our FORALL statement.
Our FORALL statement has only one DML which is INSERT, and this INSERT dml statement is
referencing the collection My_Array through the collection variable col_var in its VALUES
clause.
Which means both the rules are being fulfilled and we are good to go.
Let's do one more thing before execution.
SELECT count (*) INTO tot_rec FROM tut_77; DBMS_OUTPUT.PUT_LINE ('Total records inserted
are '||tot_rec); Just to ensure that our program has executed
as we expected we have these two statements here.
Using the first statement we are storing the value which is the total number of records
of the table tut_77 into the variable tot_rec and using the second statement which is an
output statement we are displaying back that value from the variable tot_rec to the user.
If the output of this DBMS OUTPUT statement is 10 then we can say that our program has
executed successfully.
This is because we have programmed our FORALL statement to INSERT only 10 records into our
table.
I am expecting there is no data initially stored into this table as we have just created
it.
So let's execute this program and raise the curtains.
Here is our result and fortunately it is 10.
This means the program worked as per our expectations.
"Every execution of this program will append 10 records to the previously stored data in
the table and the result of this output statement will change accordingly."
Hope this demonstration helped you in understanding the concepts of FORALL with the Lower and
Upper bound clause.
If so then do make sure to hit the thumbs up button and like the video.
Also share it with your friends on your social media and subscribe to the channel if you
haven't already.
This is the tutorial on bulk data processing with PL/SQL FORALL statement with the first
bound clause i.e. lower and upper bound in Oracle Database.
Thanks for watching.
This is Manish from RebellionRider.com
For more infomation >> The Kira Justice - "ESCOLHAS" (Música feita em LIVE) │ TV size - Duration: 1:52. 
For more infomation >> 2017 economy - Duration: 2:11. 
For more infomation >> How Resistance Bombs "FALL" in Space (Star Wars: The Last Jedi) - Duration: 3:33. 
For more infomation >> Helene Fischer: Wunderbare Neuigkeiten! Ein Freund hat es jetzt verraten - Duration: 6:57. 
For more infomation >> Louisville Boat, RV and Sport Show starts Jan. 24 - Duration: 2:46. 
Không có nhận xét nào:
Đăng nhận xét