ORACLESQL总结二：集合操作符合和DML语句

ORACLE SQL总结二：集合操作符合和DML语句

 

3、集合操作符 

3.1 集合操作符只能出现在order by从句之前。

  www.2cto.com  

4、DML语句 

4.1 SAVEPOINT 

摘自ORACLE sql references 

Savepoint names must be distinct within a given transaction. If you create a second savepoint with the same identifier as an earlier savepoint, then the earlier savepoint is erased. After a savepoint has been created, you can either continue processing, commit your work, roll back the entire transaction, or roll back to the savepoint. 

翻译如下： 

在一个给定的事务中Savepoint的命名必须是唯一的，如果你产生了一个和较早之前名字相同的savepoint,那么较早之前的那个将被清除。在savepoint被产生之后，你可以继续操作，或者提交(commmit)你的工作或者回滚整个事务，或者回滚到某个savepoint。

 

注意COMMIT后所有savepoint都会被清除，示例如下： 

01

SQL> update employees

02

  2  set salary=7000

03

  3  where last_name=&＃39;Banda&＃39;;

04  www.2cto.com  

 

05

1 row updated.

06

 

07

SQL> savepoint banda_sal;

08

 

09

Savepoint created.

10

 

11

SQL> update employees

12

  2  set salary=7000

13

  3  where last_name=&＃39;Greene&＃39;;

14

 

15

1 row updated.

16

 

17

SQL> select last_name,salary from employees where last_name=&＃39;Banda&＃39; or last_name

18

=&＃39;Greene&＃39;;

19

 

20

LAST_NAME                                              SALARY

21

-------------------------------------------------- ----------

22

Banda                                                    7000

23

Greene                                                   7000

24

 

25

SQL> savepoint greene_sal;

26

 

27

Savepoint created.

28

 

29

SQL> rollback to savepoint banda_sal;

30

   www.2cto.com  

31

Rollback complete.

32

 

33

SQL> select last_name,salary from employees where last_name=&＃39;Banda&＃39; or last_name

34

=&＃39;Greene&＃39;;

35

 

36

LAST_NAME                                              SALARY

37

-------------------------------------------------- ----------

38

Banda                                                    7000

39

Greene                                                   9500

40

 

41

SQL> commit;

42

 

43

Commit complete.

44

 

45

SQL> rollback to savepoint greene_sal;

46

rollback to savepoint greene_sal

47

*

48

ERROR at line 1:

49

ORA-01086: savepoint &＃39;GREENE_SAL&＃39; never established

 

关于这个问题，oracle sql references有比较详细的解释，摘录如下： 

Use the COMMIT statement to end your current transaction and make permanent all changes performed in the transaction. A transaction is a sequence of SQL statements that Oracle Database treats as a single unit. This statement also erases all savepoints in the transaction and releases transaction locks.

使用COMMIT可以终止你当前的事务，并永久性地改变数据库中的数据(写到日志文件)。一个事务是一组SQL语句有序组合，ORACLE数据库将它(事务)作为单个单元处理。COMMIT(提交后)也清除了所有的SAVEPOINT，并释放了事务锁。

Until you commit a transaction:

 

.You can see any changes you have made during the transaction by querying the modified tables, but other users cannot see the changes. After you commit the transaction, the changes are visible to other users&＃39; statements that execute after the commit.

 

.You can roll back (undo) any changes made during the transaction with the ROLLBACK statement .  www.2cto.com  

在你COMMIT一个事务之前： 

在当前事务中，你可以看到任何你改变的数据，但其他用户看不到这些改变。直到你COMMIT了事务，他们才可以看见。 

在当前事务中，你可以ROLLBACK任何改变。

 

Oracle Database issues an implicit COMMIT under the following circumstances:

-Before any syntactically valid data definition language (DDL) statement, even if the statement results in an error

-After any data definition language (DDL) statement that completes without an error

 

ORACLE数据库隐式地执行COMMIT在如下情形下： 

-在任何DDL语句语法验证之前，甚至这条语句执行结果是错误的。 

-在任何DDL语句没有错误地执行完成之后。

 

4.2 ROLLBACK的疑问，DBA_2PC_PENDING为什么是空的？ 

ROLLBACK中的FORCE从句 

FORCE Clause

  www.2cto.com  

Specify FORCE to manually roll back an in-doubt distributed transaction. The transaction is identified by the string containing its local or global transaction ID. To find the IDs of such transactions, query the data dictionary view DBA_2PC_PENDING.

 

A ROLLBACK statement with a FORCE clause rolls back only the specified transaction. Such a statement does not affect your current transaction.

01

SQL> desc dba_2pc_pending;

02

 Name                    Null?    Type

03

 ----------------------- -------- ----------------

04

 LOCAL_TRAN_ID           NOT NULL VARCHAR2(22)

05

 GLOBAL_TRAN_ID                   VARCHAR2(169)

06

 STATE                   NOT NULL VARCHAR2(16)

07

 MIXED                            VARCHAR2(3)

08

 ADVICE                           VARCHAR2(1)

09

 TRAN_COMMENT                     VARCHAR2(255)

10

 FAIL_TIME               NOT NULL DATE

11

 FORCE_TIME                       DATE

12

 RETRY_TIME              NOT NULL DATE

13  www.2cto.com  

 OS_USER                          VARCHAR2(64)

14

 OS_TERMINAL                      VARCHAR2(255)

15

 HOST                             VARCHAR2(128)

16

 DB_USER                          VARCHAR2(30)

17

 COMMIT#                          VARCHAR2(16)

18

 

19

SQL> select * from dba_2pc_pending where db_user=&＃39;OE&＃39;;

20

 

21

no rows selected

 

 

作者 yeyelei