Oracle 9i Transaction Processing

Transactions are the basic unit of database operations in Oracle 9i, and 3 transaction control commands can be used in pl/sql programs.

Commit command

A commit is a transaction commit command. In Oracle 9i databases, in order to ensure data consistency, the client area is set up for each client in memory, and the transactions that clients operate on the database are completed within the workspace, and changes in the workspace are written to the database, called Physical writes, after the commit command is entered. This ensures that the data in the back-end database read by the other clients is complete and consistent, as shown in Figure 9.26, before any client has a physical commit change.

In sqlplus worksheet, you can perform the following pl/sql programs to turn on the autocommit feature. This will automatically commit the transaction each time the Pl/sql program is executed. The results of the execution are shown in Figure 9.27.
―――――――――――――――――――――――――――――――――――――
Set auto on;
―――――――――――――――――――――――――――――――――――――
"Matching program location": the 9th Chapter \setautocommiton.sql.

Accordingly, the PL/SQL procedure for canceling the AUTOCOMMIT function is as follows.
―――――――――――――――――――――――――――――――――――――
Set Auto off;
―――――――――――――――――――――――――――――――――――――
"Matching program location": the 9th Chapter \setautocommitoff.sql.

Rollback command

Ollback is a transaction rollback command, and you can use the rollback command to roll back to the state of the last commit before committing the commit, if you find that operations such as delete, insert, and update need to be restored.
The following example shows how to roll back with the delete command.
(1) Turn off the automatic submission function first.
(2) Execute the following SQL program in "sqlplus Worksheet" To view all the data in the Scott.emp datasheet and perform the result as shown in Figure 9.28.
―――――――――――――――――――――――――――――――――――――
SELECT * from Scott.emp;
―――――――――――――――――――――――――――――――――――――
"Matching program location": the 9th Chapter \selectemp.sql.

(3) Execute the following SQL program in "sqlplus Worksheet" to delete all data in the Scott.emp datasheet. The results of the execution are shown in Figure 9.29.
―――――――――――――――――――――――――――――――――――――
Delete from Scott.emp;
―――――――――――――――――――――――――――――――――――――
"Matching program location": the 9th Chapter \deleteemp.sql.

(4) Execute the following SQL program in "sqlplus Worksheet" to query the result of deleting the datasheet scott.emp. The results of the execution are shown in Figure 9.30.
―――――――――――――――――――――――――――――――――――――
SELECT * from Scott.emp;
―――――――――――――――――――――――――――――――――――――
"Matching program location": the 9th Chapter \selectemp.sql.

(5) Perform the following SQL program in "sqlplus Worksheet" to complete the rollback of the transaction. The results of the execution are shown in Figure 9.31.
―――――――――――――――――――――――――――――――――――――
Rollback
―――――――――――――――――――――――――――――――――――――
"Matching program location": the 9th Chapter \rollbackemp.sql.

(5) Execute the selectemp.sql file in "Sqlplus Worksheet", and the execution result, as shown in Figure 9.28, indicates that the transaction was successfully rolled back.

SavePoint command

SavePoint is the save Point command. A transaction is usually made up of several commands that can be saved in several sections, so that each savepoint can be rolled back without having to roll back the entire transaction. The syntax format is as follows.
To create a save point: savepoint save a roll call;
Rollback save point: Rollback to save roll name;
The following describes how to use a save point.
(1) Execute the following SQL program in "Sqlplus Worksheet", and the program completes inserting a record into the Scott.emp datasheet. The results of the execution are shown in Figure 9.32.
―――――――――――――――――――――――――――――――――――――
Insert into Scott.emp (empno,ename,sal) VALUES (9000, ' Wang ', 2500);
―――――――――――――――――――――――――――――――――――――
"Matching program location": the 9th Chapter \insertemp.sql.

(2) Execute the following SQL code in "sqlplus Worksheet" to complete the creation of the save point. The results of the execution are shown in Figure 9.33.
―――――――――――――――――――――――――――――――――――――
SavePoint Insertpoint;
―――――――――――――――――――――――――――――――――――――
"Matching program Location": 9th Chapter \ Createsavepoint.sql.

(3) Other SQL statements can be executed in "Sqlplus worksheet".
(4) Execute the following SQL code in "Sqlplus Worksheet", and the program completes rolling back to the specified savepoint "Insertpoint". The results of the execution are shown in Figure 9.34.
―――――――――――――――――――――――――――――――――――――
Rollback to Insertpoint;
―――――――――――――――――――――――――――――――――――――
"Matching program Location": 9th Chapter \ Rollbacksavepoint.sql.

(5) Can execute Selectemp.sql file, query the data before and after rollback.