Oracle数据操作和控制语言详解.DOC

1、Oracle 数据操作和控制语言详解日期：2005-11-01来源：天极网 作者：佚名字体：大 中 小 SQL 语言共分为四大类：数据查询语言 DQL，数据操纵语言 DML， 数据定义语言 DDL，数据控制语言 DCL。其中用于定义数据的结构，比如 创建、修改或者删除数据库；DCL 用于定义数据库用户的权限；在这篇文章中我将详细讲述这两种语言在 Oracle 中的使用方法。 DML 语言DML 是 SQL 的一个子集，主要用于修改数据，下表列出了 ORACLE 支持的 DML 语句。语句 用途INSERT 向表中添加行UPDATE 更新存储在表中的数据DELETE 删除行SELECT FOR

2、UPDATE 禁止其他用户访问 DML 语句正在处理的行。LOCK TABLE 禁止其他用户在表中使用 DML 语句插入数据INSERT 语句常常用于向表中插入行，行中可以有特殊数据字段，或者可以用子查询从已存在的数据中建立新行。列目录是可选的，缺省的列的目录是所有的列名，包括 comlumn_id,comlumn_id 可以在数据字典视图 ALL_TAB_COLUMNS,USER_TAB_COLUMNS,或者 DBA_TAB_COLUMNS 中找到。插入行的数据的数量和数据类型必须和列的数量和数据类型相匹配。不符合列定义的数据类型将对插入值实行隐式数据转换。NULL 字符串将一个 NULL

3、值插入适当的列中。关键字NULL 常常用于表示将某列定义为 NULL 值。下面的两个例子是等价的。INSERT INTO customers(cust_id,state,post_code)VALUE(Ariel,NULL,94501); 或INSERT INTO customers(cust_id,state,post_code)VALUE(Ariel,94501);更新数据UPDATE 命令用于修改表中的数据。UPDATE order_rollupSET(qty,price)=(SELECT SUM(qty),SUM(price) FROM order_lines WHERE custom

4、er_id=KOHLWHERE cust_id=KOHLAND order_period=TO_DATE(01-Oct-2000)删除数据DELETE 语句用来从表中删除一行或多行数据，该命令包含两个语句：1、关键字 DELETE FROM 后跟准备从中删除数据的表名。2、 WHERE 后跟删除条件DELETE FROM po_linesWHERE ship_to_state IN (TX,NY,IL)AND order_date清空表如果你想删除表中所有数据，清空表，可以考虑使用 DDL语言的 TRUNCATE 语句。TRUNCATE 就像没有 WHERE 子句的 DELETE 命令一样。

5、TRUNCATE 将删除表中所有行。TRUNCATE 不是 DML 语句是 DDL 语句，他和 DELETE 右不同的特点。TRUNCATE TABLE (schema)table DROP(REUSE) STORAGE STORAGE 子串是可选的，缺省是 DROP STORAGE。当使用 DROP STORAGE 时将缩短表和表索引，将表收缩到最小范围，并重新设置 NEXT 参数。REUSE STORAGE 不会缩短表或者调整 NEXT 参数。TRUNCATE 和 DELETE 有以下几点区别1、TRUNCATE 在各种表上无论是大的还是小的都非常快。如果有 ROLLBACK 命令 DEL

6、ETE 将被撤销，而 TRUNCATE 则不会被撤销。2、TRUNCATE 是一个 DDL 语言，向其他所有的 DDL 语言一样，他将被隐式提交，不能对 TRUNCATE 使用 ROLLBACK 命令。3、TRUNCATE 将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时，经过 TRUNCATE 操作后的表比 DELETE 操作后的表要快得多。4、TRUNCATE 不能触发任何 DELETE 触发器。5、不能授予任何人清空他人的表的权限。6、当表被清空后表和表的索引讲重新设置成初始大小，而delete 则不能。7、不能清空父表。SELECT FOR UPDATEselect fo

7、r update 语句用于锁定行，阻止其他用户在该行上修改数据。当该行被锁定后其他用户可以用 SELECT 语句查询该行的数据，但不能修改或锁定该行。锁定表LOCK 语句常常用于锁定整个表。当表被锁定后，大多数DML 语言不能在该表上使用。LOCK 语法如下：LOCK schema table IN lock_mode其中 lock_mode 有两个选项：share 共享方式exclusive 唯一方式例：LOCK TABLE intentory IN EXCLUSIVE MODE死锁当两个事务都被锁定，并且互相都在等待另一个被解锁，这种情况称为死锁。当出现死锁时，ORACLE 将检测死锁条件

8、，并返回一个异常。1 2 3 4 下一页 事务控制事务控制包括协调对相同数据的多个同步的访问。当一个用户改变了另一个用户正在使用的数据时，oracle 使用事务控制谁可以操作数据。事务事务表示工作的一个基本单元，是一系列作为一个单元被成功或不成功操作的 SQL 语句。在 SQL 和 PL/SQL 中有很多语句让程序员控制事务。程序员可以：1、显式开始一个事物，选择语句级一致性或事务级一致性2、设置撤销回滚点，并回滚到回滚点3、完成事务永远改变数据或者放弃修改。事务控制语句语句 用途Commit 完成事务，数据修改成功并对其他用户开放Rollback 撤销事务，撤销所有操作rollback to

9、 撤销在设置的回滚点以后的操作savepoint set transaction响应事务或语句的一致性；特别对于事务使用回滚段例：BEGINUPDATE checkingSET balance=balance-5000WHERE account=Kieesha;INSERT INTO checking_log(action_date,action,amount)VALUES (SYSDATE,Transfer to brokerage,-5000);UPDATE brokerageSET cash_balance=cash_balance+5000WHERE account=Kiesha;IN

10、SERT INTO brokerage_log(action_date,action,amount)VALUES (SYSDATE,Tracfer from checking,5000)COMMITEXCEPTIONWHEN OTHERSROLLBACKENDSavepoint 和 部分回滚(Partial Rollback)在 SQL 和 PL/SQL 中 Savepoint 是在一事务范围内的中间标志。经常用于将一个长的事务划分为小的部分。保留点 Savepoint 可标志长事务中的任何点，允许可回滚该点之后的操作。在应用程序中经常使用 Savepoint；例如一过程包含几个函数，在每个函

11、数前可建立一个保留点，如果函数失败，很容易返回到每一个函数开始的情况。在回滚到一个 Savepoint 之后，该 Savepoint 之后所获得的数据封锁被释放。为了实现部分回滚可以用带 TO Savepoint 子句的 ROLLBACK 语句将事务回滚到指定的位置。例BEGIN INSERT INTO ATM_LOG(who,when,what,where)VALUES (Kiesha,SYSDATE,Withdrawal of $100,ATM54)SAVEPOINT ATM_LOGGED;UPDATE checkingSET balance=balance-100RETURN balan

12、ce INTO new_balance;IF new_balance0THENROLLBACK TO ATM_LOGGED;COMMITRAISE insufficient_funda;END IFEND 关键字 SAVEPOINT 是可选的，所以下面两个语句是等价的：ROLLBACK TO ATM_LOGGED;ROLLBACK TO SAVEPOINT ATM_LOGGED;一致性和事务一致性是事物控制的关键慨念。掌握了 oracle 的一致性模型，能使您更好的，更恰当的使用事务控制。oracle 通过一致性保证数据只有在事务全部完成后才能被用户看见和使用。这项技术对多用户数据库有巨大的作用。oracle 常常使用语句级（state-level)一致性，保证数据在语句的生命期之间是可见的但不能被改变。事务由多个语句组成，当使用事务时，事物级（transaction-level)一致性在整个事务生命期中保证数据对所有语句都是可见的。