DDL/DML/DCL/TCL

DDL
Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

• CREATE - to create objects in the database
• ALTER - alters the structure of the database
• DROP - delete objects from the database
• TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
• COMMENT - add comments to the data dictionary
• RENAME - rename an object

DML
Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

• SELECT - retrieve data from the a database
• INSERT - insert data into a table
• UPDATE - updates existing data within a table
• DELETE - deletes all records from a table, the space for the records remain
• MERGE - UPSERT operation (insert or update)
• CALL - call a PL/SQL or Java subprogram
• EXPLAIN PLAN - explain access path to data
• LOCK TABLE - control concurrency

DCL
Data Control Language (DCL) statements. Some examples:

• GRANT - gives user's access privileges to database
• REVOKE - withdraw access privileges given with the GRANT command

TCL
Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

• COMMIT - save work done
• SAVEPOINT - identify a point in a transaction to which you can later roll back
• ROLLBACK - restore database to original since the last COMMIT
• SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

关于DML，DDL，DCL的补充

(2009-11-04 16:14:23)

TE>转载TE>

标签： 回滚 dml rollback commit ddl 杂谈

分类：Oracle

取自网络各位，留作记录。

 

DML：insert \ update \ delete \ merge (Oracle 独有，用于数据库同步)
DDL：create \ drop \ alter \ truncate \ rename \ comment
DCL：grant \ revoke
Transaction ：commit \ rollback \ savepoint

 

  用SQL命令间接完成的提交为隐式提交。这些命令是：
ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，
EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

 

 

 对于DML语句，oracle不会自动提交事务，直到有一条commit或者rollback命令来处理时才会将改动反应到数据库里。而对于DDL和DCL,oracle会马上提交，也就是说一执行完这两类语句，就会反应到数据库中，还有一种情况是，前面执行的DML没有被手动提交，执行完DDL或者DCL后，oracle也会将DML对与数据的改动提交到数据库中去。

ddl是不要事务的，也就是说，操作后就不能回滚的，事务主要是针对dml语句来说的。dml语句对数据的修改需要commit才能生效，如果rollback，将回滚你的修改。如果既没有commit又没有rollback的话，这个dml处于中间状态，涉及到的lock不会释放。对于sqlplus，默认情况下，没有显示的commit，dml是不会提交的。

不过这里需要注意，如果执行了ddl语句，是会提交这个ddl语句前所有的dml语句的，这是个隐性提交。