Oracle PL/SQL Tutorial/Transaction/Transaction Level

Материал из SQL эксперт
Перейти к: навигация, поиск

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>