Oracle PL/SQL Tutorial/Transaction/Transaction Level
Содержание
Set the transaction isolation level using the SET TRANSACTION statement.
<source lang="sql">
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;</source>
set transaction isolation level read committed;
<source lang="sql">
begin
set transaction isolation level read committed;
end; / SQL> SQL></source>
set transaction isolation level to serializable in PL/SQL
<source lang="sql">
Declare
v_nr NUMBER;
Begin
set transaction isolation level serializable;
End; /</source>
SET TRANSACTION READ WRITE
<source lang="sql">
SQL> CREATE TABLE project (
2 pro_id NUMBER(4), 3 pro_name VARCHAR2(40), 4 budget NUMBER(9,2), 5 CONSTRAINT project_pk PRIMARY KEY (pro_id) 6 );
Table created. SQL> SQL> SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1001, "A",12345); 1 row created. SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1002, "ERP",23456); 1 row created. SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1003, "SQL",34567); 1 row created. SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1004, "CRM",45678); 1 row created. SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1005, "VPN",56789); 1 row created. SQL> SQL> SQL> CREATE TABLE server_usage (
2 pro_id NUMBER(4), 3 emp_id NUMBER, 4 time_log_date DATE, 5 hours_logged NUMBER(8,2), 6 dollars_charged NUMBER(8,2), 7 CONSTRAINT server_usage_pk PRIMARY KEY (pro_id, emp_id, time_log_date) 8 );
Table created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,101,to_date("4-Apr-2004","dd-mon-yyyy"),1123,222);
1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,102,to_date("4-Apr-2005","dd-mon-yyyy"),1124,223);
1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1003,103,to_date("4-Apr-2006","dd-mon-yyyy"),1125,224);
1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1004,104,to_date("4-Apr-2007","dd-mon-yyyy"),1126,225);
1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1005,105,to_date("4-Apr-2008","dd-mon-yyyy"),1127,226);
1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,106,to_date("4-Apr-2009","dd-mon-yyyy"),1128,227);
1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,107,to_date("4-Apr-2010","dd-mon-yyyy"),1129,228);
1 row created. SQL> SET ECHO ON SQL> SQL> SET TRANSACTION READ WRITE; SQL> SQL> SQL> INSERT INTO project
2 SELECT 2008, pro_name, budget FROM project 3 WHERE pro_id = 1002;
1 row created. SQL> SQL> SQL> UPDATE server_usage
2 SET pro_id = 2008 3 WHERE pro_id = 1002;
2 rows updated. SQL> SQL> SQL> ROLLBACK; Rollback complete. SQL> SQL> SQL> drop table project; Table dropped. SQL> drop table server_usage; Table dropped.</source>