Collections:
Manage Transaction Isolation Levels in Oracle
How To Manage Transaction Isolation Level in Oracle?
✍: FYIcenter.com
Transaction isolation level can be managed in a procedure by using the SET TRANSACTION and COMMIT statements. Here is a sample script on how to manage transaction isolation level:
SQL> CREATE OR REPLACE PROCEDURE HR.TOTAL_SALARY AS 2 total_salary NUMBER(12,2); 3 BEGIN 4 SET TRANSACTION READ ONLY; 5 SELECT SUM (salary) INTO total_salary FROM employees; 6 DBMS_OUTPUT.PUT_LINE('Total salary 1: ' 7 || total_salary); 8 -- Other sessions may change salaries of some records 9 SELECT SUM (salary) INTO total_salary FROM employees; 10 DBMS_OUTPUT.PUT_LINE('Total salary 2: ' 11 || total_salary); 12 COMMIT; 13 END; 14 / SQL> EXECUTE TOTAL_SALARY; Total salary 1: 691400 Total salary 2: 691400
"READ ONLY" transaction level takes a read only snapshot of the database. This allows other sessions to update the database without any locks. All queries in the session will produces identical results. So both SELECT statements in this script will return the same value guaranteed.
⇒ Example of Passing Parameters to Procedures in Oracle
⇐ Create an Array in PL/SQL in Oracle
2019-03-08, 1676🔥, 0💬
Popular Posts:
How To Replace Given Values with NULL using NULLIF() in SQL Server Transact-SQL? Sometime you want t...
How Fixed Length Strings Are Truncated and Padded in SQL Server Transact-SQL? When the length of the...
How To Change the Name of a Database User in SQL Server? If you want to change the name of an existi...
How To Replace Given Values with NULL using NULLIF() in SQL Server Transact-SQL? Sometime you want t...
What is sqlservr.exe - Process - SQL Server (SQLEX?PRESS) in SQL Server? Process sqlservr.exe is the...