I have written PLSQL scripts several times and this time, I thought to blog it.
In this article, you will see how to write an simple PLSQL script to update records in DB on demand.
1) Write the Anonymous PLSQL block skeleton in a file saved as .SQL extension. My requirement is to schedule this PLSQL script via cron job scheduler and execute it via shell script. Thus, I have not opted for writing a procedure since it needs package which should be compiled and available in all the DBs. This script is an ad hoc on demand basis to be run and so, I do not want it to be a package.
2) Declare & define all the variables that you need and initialize them as needed.
Then, declare the cursor with select SQL which will be the input for the updating the records in DB.
Example syntax:
The above example is when you don't have to pass any input to the cursor. If you want to pass any input argument to the cursor to be used in the select query, then it will be.
You can also use the variable declared in the declare section in the select query.
3) Write your business execution logic inside the BEGIN section.
In this example, open the cursor, loop through the result of the select query cursor and do the update the query and then end the loop.
Example:
4) In the EXCEPTION section, handle the exceptions appropriately. I have handled it as:
5) Finally the entire block is ended with END section. For every BEGIN, END should be there.
You can use BEGIN.....END blocks inside the execution section any number of times, if you want to create a separate unit of execution to handle it and its exceptions separately from the whole unit.
We have completed the anonymous PLSQL script for updating records in DB in a much efficient way. Hopefully this article helped you with your assignment. Leave your opinion in comment section.
All the best!!
--------------------------------------------------------------------------------------
Appendix
2.a) SKIP LOCKED
If you had noticed, I have used "for update skip locked" in the select query.
Since I will be running this PLSQL via scheduler in the background, there are chances of application operating on the rows that this script may try to update, hence without skip locked, update query will have to wait till it gets lock of all the rows to update.
To avoid this, I have used skip locked, so that select query will fetch only those rows which are available for update and consecutive update query in the execution block can do update successfully without worrying.
3.a) CURSOR
Cursor is used to host the select query and then to fetch the result set records of select query to apply the business logic needed.
Syntax is:
3.b) BULK COLLECT
Without bulk collect, the default behavior is to retrieve result set record of select query of cursor one by one. To speed up the things and to reduce the update queries to be executed, bulk collect helps us.
Bulk collect with limit clause with notify cursor to fetch X number of result set records in one time instead of one by one.
Example:
This above statement implies that fetch upto 100 records from the select query by the cursor. So, at a time, 100 records can be processed. Thus, improving the speed and also, reduces no. of queries being executed.
3.c) FORALL
If we write the update query inside the fetch bulk collect clause, update query will be executed 100 times as we are fetching 100 records. Though we have increased the speed by fetching more records in single shot, but no. of update queries are more.
So, the context switch between PLSQL Runtime Engine and SQL Engine is more.
FORALL comes to the rescue. If we write the update query within the FORALL clause, then PLSQL Engine will record all the update queries and then handsover to SQL Engine in single shot, thus only 1 context switch and improving performance again.
Example:
Remember FORALL is not a loop and thus, no loop and end loop keywords are used.
References:
In this article, you will see how to write an simple PLSQL script to update records in DB on demand.
1) Write the Anonymous PLSQL block skeleton in a file saved as .SQL extension. My requirement is to schedule this PLSQL script via cron job scheduler and execute it via shell script. Thus, I have not opted for writing a procedure since it needs package which should be compiled and available in all the DBs. This script is an ad hoc on demand basis to be run and so, I do not want it to be a package.
/*
Documentation about purpose of this anonymous PLSQL
*/
DELCARE
/* Declare & define variables with or without initialization
*/
BEGIN
/*
Execution block
*/
EXCEPTION
/*
Catch exceptions and handle them as per your need
*/
WHEN OTHERS THEN
RAISE;
END;
2) Declare & define all the variables that you need and initialize them as needed.
Then, declare the cursor with select SQL which will be the input for the updating the records in DB.
Example syntax:
CURSOR EXAMPLE_CURSOR IS
select table2.xyz
from table1, table2
where
table1.a = 123
and table1.b = 234
and table1.c = table2.c
and table2.d = 345
for update skip locked;
The above example is when you don't have to pass any input to the cursor. If you want to pass any input argument to the cursor to be used in the select query, then it will be.
CURSOR EXAMPLE_CURSOR(p_arg_1 IN NUMBER, p_arg_2 IN NUMBER) IS
select table2.xyz
from table1, table2
where
table1.a = 123
and table1.b = p_arg_1
and table1.c = table2.c
and table2.d = p_arg_2
for update skip locked;
You can also use the variable declared in the declare section in the select query.
3) Write your business execution logic inside the BEGIN section.
In this example, open the cursor, loop through the result of the select query cursor and do the update the query and then end the loop.
Example:
OPEN EXAMPLE_CURSOR;
LOOP
FETCH EXAMPLE_CURSOR BULK COLLECT INTO v_var_1 LIMIT 100;
IF v_var_1.COUNT>0 THEN
FORALL indx IN 1 .. v_var_1.COUNT
update table3
set col1 = 123, col2 = 234
where col3 = v_var_1 (indx);
DBMS_OUTPUT.PUT_LINE('No. of table3 rows updated = ' || SQL%ROWCOUNT);
FORALL j IN 1 .. v_var_1.COUNT
update table4
set col1 = 234
where col2 = v_var_1 (j);
DBMS_OUTPUT.PUT_LINE('No. of table4 rows updated = ' || SQL%ROWCOUNT);
COMMIT;
ELSE -- if there is no records then exit
EXIT;
END IF;
END LOOP;
CLOSE EXAMPLE_CURSOR;
4) In the EXCEPTION section, handle the exceptions appropriately. I have handled it as:
EXCEPTION
WHEN OTHERS THEN
vmessage := 'Encountered '||SQLERRM||' , while execution';
DBMS_OUTPUT.PUT_LINE('Exception message = ' || vmessage);
ROLLBACK;
raise_application_error(-20200,vmessage);
5) Finally the entire block is ended with END section. For every BEGIN, END should be there.
You can use BEGIN.....END blocks inside the execution section any number of times, if you want to create a separate unit of execution to handle it and its exceptions separately from the whole unit.
We have completed the anonymous PLSQL script for updating records in DB in a much efficient way. Hopefully this article helped you with your assignment. Leave your opinion in comment section.
All the best!!
--------------------------------------------------------------------------------------
Appendix
2.a) SKIP LOCKED
If you had noticed, I have used "for update skip locked" in the select query.
Since I will be running this PLSQL via scheduler in the background, there are chances of application operating on the rows that this script may try to update, hence without skip locked, update query will have to wait till it gets lock of all the rows to update.
To avoid this, I have used skip locked, so that select query will fetch only those rows which are available for update and consecutive update query in the execution block can do update successfully without worrying.
3.a) CURSOR
Cursor is used to host the select query and then to fetch the result set records of select query to apply the business logic needed.
Syntax is:
OPEN EXAMPLE_CURSOR;
LOOP
FETCH EXAMPLE_CURSOR BULK COLLECT INTO v_var_1 LIMIT 100;
IF v_var_1.COUNT>0 THEN
/*
Business logic
*/
ELSE -- if there is no records then exit
EXIT;
END IF;
END LOOP;
CLOSE EXAMPLE_CURSOR;
3.b) BULK COLLECT
Without bulk collect, the default behavior is to retrieve result set record of select query of cursor one by one. To speed up the things and to reduce the update queries to be executed, bulk collect helps us.
Bulk collect with limit clause with notify cursor to fetch X number of result set records in one time instead of one by one.
Example:
FETCH EXAMPLE_CURSOR BULK COLLECT INTO v_var_1 LIMIT 100;
This above statement implies that fetch upto 100 records from the select query by the cursor. So, at a time, 100 records can be processed. Thus, improving the speed and also, reduces no. of queries being executed.
3.c) FORALL
If we write the update query inside the fetch bulk collect clause, update query will be executed 100 times as we are fetching 100 records. Though we have increased the speed by fetching more records in single shot, but no. of update queries are more.
So, the context switch between PLSQL Runtime Engine and SQL Engine is more.
FORALL comes to the rescue. If we write the update query within the FORALL clause, then PLSQL Engine will record all the update queries and then handsover to SQL Engine in single shot, thus only 1 context switch and improving performance again.
Example:
FORALL j IN 1 .. v_var_1.COUNT
Remember FORALL is not a loop and thus, no loop and end loop keywords are used.
References:
- If you want to read more about BULK COLLECT and FORALL, then refer https://blogs.oracle.com/oraclemagazine/bulk-processing-with-bulk-collect-and-forall
