Oracle read-only transaction Test Study Notes

In oracle, only select statements in read-only transactions cannot have DML statements. All queries can only view the data before the transaction starts. Next I will introduce you to the Oracle read-only transaction test instance.

We know that directly executing the select statement won't start the transaction, so how can we start the read-only transaction.

 

The Code is as follows:

Copy code

-- Demonstrate read-only transactions -- Create a table Create table t_read_only (id number primary key) -- Insert a data entry Insert into t_read_only (id) values (1 ); -- Enable Read-only transactions (only queries in read-only transactions do not support DML statements) Set transaction read only; Declare Type to_table_type is table of number index by binary_integer; V_numbers to_table_type; Begin Select id bulk collect into v_numbers from t_read_only; -- load data to an array in batches -- Print the query results For I in 1 .. v_numbers.count loop Dbms_output.put_line (v_numbers (I )); End loop; -- Do not submit the transaction to execute the following select statement, and then re-open the SQL window to execute the select statement again. End; -- Insert data and execute Query Select * from t_read_only; -- Re-open an SQL window and execute insert data Begin Insert into t_read_only (id) values (2 ); Commit; -- submit a transaction End;

We can find that the database committed by transaction 2 is not queried in transaction 1, so the read-only transaction can only see the data when the transaction is started, and the operations on other transactions are invisible.