oracle 集合合并sql,ORACLE SQL总结二：集合操作符合和DML语句

3、集合操作符

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

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都会被清除，示例如下：

SQL> update employees

2 set salary=7000

3 where last_name='Banda';

1 row updated.

SQL> savepoint banda_sal;

Savepoint created.

SQL> update employees

2 set salary=7000

3 where last_name='Greene';

1 row updated.

SQL> select last_name,salary from employees where last_name='Banda' or last_name

='Greene';

LAST_NAME SALARY

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

Banda 7000

Greene 7000

SQL> savepoint greene_sal;

Savepoint created.

SQL> rollback to savepoint banda_sal;

Rollback complete.

SQL> select last_name,salary from employees where last_name='Banda' or last_name

='Greene';

LAST_NAME SALARY

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

Banda 7000

Greene 9500

SQL> commit;

Commit complete.

SQL> rollback to savepoint greene_sal;

rollback to savepoint greene_sal

*

ERROR at line 1:

ORA-01086: savepoint 'GREENE_SAL' 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' statements that execute after the commit.

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

在你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

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.

SQL> desc dba_2pc_pending;

Name Null? Type

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

LOCAL_TRAN_ID NOT NULL VARCHAR2(22)

GLOBAL_TRAN_ID VARCHAR2(169)

STATE NOT NULL VARCHAR2(16)

MIXED VARCHAR2(3)

ADVICE VARCHAR2(1)

TRAN_COMMENT VARCHAR2(255)

FAIL_TIME NOT NULL DATE

FORCE_TIME DATE

RETRY_TIME NOT NULL DATE

OS_USER VARCHAR2(64)

OS_TERMINAL VARCHAR2(255)

HOST VARCHAR2(128)

DB_USER VARCHAR2(30)

COMMIT# VARCHAR2(16)

SQL> select * from dba_2pc_pending where db_user='OE';

no rows selected