【開發篇sql】基礎概述（三） DDL和DML

3，DDL和DML

Oracle的DDL主要有create，alter，drop，truncate等，DML主要有insert，update，select，delete，merge等。Oracle的DDL一個特點是自動提交，這與其他的一些資料庫DDL不同。

對於oracle，ddl執行過程可以用一段偽碼示意如下：

begin

COMMIT;

do the ddl;

COMMIT;

exception

when others then

ROLLBACK;

RAISE;

end;

也即在執行ddl之前有一個提交動作，執行完成之後也有一個提交動作，而執行失敗時，第一個提交動作已經完成，所以會提交當前事務。用一個例子來看：

SQL> create table t_test as select * from dual;

Table created

SQL> update t_test a set a.dummy = 'z';

1 row updated

SQL> create table t_test as select * from dual;

create table t_test as select * from dual

ORA-00955: 名稱已由現有物件使用

SQL> rollback;

Rollback complete

SQL> select * from t_test;

DUMMY

-----

z

對於dml來說也可以設定為AUTOCOMMIT，比如sqlplus中的set autocommit：

SQL> set autocommit on;

SQL> update scott.t_test set dummy = 'v';

1 row updated

Commit complete

或者java的jdbc連線中的SetAutoCommit(boolean isCommit)：

使用java.sql.Connection或者oracle.jdbc.OracleConnection建立的一個新的連線預設為auto-commit模式，程式碼如下：

// Connect to the database

// You can put a database hostname after the @ sign in the connection URL.

OracleDataSource ds = new OracleDataSource();

ods.setURL("jdbc:oracle:oci:@");//或者也可以使用瘦客戶端連線(thin)

ods.setUser("scott");

ods.setPassword("tiger");

Connection conn = ods.getConnection();

// It's faster when auto commit is off

conn.setAutoCommit (false); //一般來講這裡關閉自動提交併不是為了更快，而是為了更好的控制提交的時機。

// Create a Statement

Statement stmt = conn.createStatement ();

...

如果對DDL進行trace，會發現ddl其實就是一系列的dml，這些dml操作了資料字典表。例子如下：

SQL> alter session set events '10046 trace name context forever , level 12';

Session altered.

SQL> create table xxxx (a number);

Table created.

使用tkprof格式化trace檔案，可以看到：

Trace file: test1_ora_7449.trc

Sort options: default

********************************************************************************

count = number of times OCI procedure was executed

cpu = cpu time in seconds executing

elapsed = elapsed time in seconds executing

disk = number of physical reads of buffers from disk

query = number of buffers gotten for consistent read

current = number of buffers gotten in current mode (usually for update)

rows = number of rows processed by the fetch or execute call

********************************************************************************

create table xxxx (a number)

call count cpu elapsed disk query current rows

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

Parse 1 0.01 0.02 0 0 0 0

Execute 1 0.03 0.03 0 1 8 0

Fetch 0 0.00 0.00 0 0 0 0

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

total 2 0.04 0.06 0 1 8 0

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

---------------------------------------- Waited ---------- ------------

SQL*Net message to client 2 0.00 0.00

SQL*Net message from client 1 33.73 33.73

********************************************************************************

select dummy

from

dual where ora_dict_obj_type = 'SYNONYM' AND ora_dict_obj_owner = 'PUBLIC'

.....(以下省略)

看一個具體的DDL create table的完整語句：

SQL> select dbms_metadata.get_ddl('TABLE','T_TEST_UNDO','SYS') from dual;

CREATE TABLE "SYS"."T_TEST_UNDO"

( "A" NUMBER,

"B" VARCHAR2(20)

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE "SYSTEM"

注意到以上程式碼中的一些引數，這些被稱作common sql ddl clauses,分別包括了：

Allocate extent clause

用來分配物件的extent，可以指定size子句，指定資料檔案（datafile），指定確切的instance

Constraint

用來定義完整性約束，比如not null ,unique,primary key,foreign key,check等，通常可以通過inline或者out-of-line 兩種方式來定義約束（not null除外，只能使用inline），比如：

create table t_test_id(

id number unique,--inline的unique約束

name varchar2(20) not null

);

Create table t_test_undo_1(

Id number not null,--not null 只能inline

S_id number ,

Name varchar2(20),

Primary key (s_id),--

constraint fk__t_test_ud1 foreign key (Id)

references t_test_id (ID)

);

或者：

Create table t_test_undo_1(

Id number not null,--

S_id number ,

Name varchar2(20)

);

alter table t_test_undo_1 add constraint pk_sid Primary key (s_id);

alter table t_test_undo_1 add constraint fk__t_test_ud1 foreign key (Id)

references t_test_id (ID);

Deallocate unused clause

可以顯示的釋放在一個資料庫段物件結束時未使用的空間，使得空間可以被其他段物件使用。要注意的是不能同時在一個語句中設定deallocate_unused_clause和allocate_extent_clause。

Logging clause

在建立資料庫物件時，這個引數設定為logging或者nologging，用來控制是否記錄redo log，要注意的是並不能完全nologging，並且在不同的資料庫模式下，影響也不同（archived和unarchived）。DDL中允許nologging模式的語句主要有下列：

Create table ... as select

alter table ... move

alter table ... xxx partition

Create index

Alter index ... xxx

Physical attributes clause

可以用來設定table,cluster,index或者materialized的pctfree,pctused,和initans引數。這些引數的原理和作用詳見oracle管理基礎一章。

Storage clause

Oracle應該如何儲存資料庫物件的設定。主要的引數如下：

INITIAL 物件的第一個extent的大小

NEXT 下一擴充套件的extent大小

MINEXTENTS 最小的extents數量

MAXEXTENTS 最大的extents數量

PCTINCREASE 指定extent較前一個的增長的百分比。

需要注意的是，pctincreate值大於0會造成extent不一致，如果next較多的話，可能會造成空間利用率低下，雖然smon會自動合併表空間中的碎片，但是這些空間也不能很好的利用。（oracle建議設定為0，可以減少碎片和避免產生非常大的臨時段，而回滾段不能設定pctincrease，預設是0）

FREELISTS 一個空閒連結串列組內的空閒連結串列數

FREELIST GROUPS 空閒連結串列組的數量

以上兩個引數只能在create table,cluster,index時使用

BUFFER_POOL 可以用來為物件設定一個預設的緩衝池，同樣的，回滾段不能設定。

有關儲存引數的詳述見oracle管理基礎一章。

再看看DML insert的大致語法：

Insert {direct/parallel/append}

{all/first} into

{schema}[table/view/materialized view]{@dblink}

{partition/subpartition}

{when ..then}

[values/select]

{returning into ..}

{log errors into }

{reject limit {integer/unlmited}}

涉及到的clause有插入路徑，方式，schema，是否使用dblink，指定分割槽或者子分割槽，插入條件，返回值，錯誤日誌記錄，拒絕的限制等等，具體可以參見oracle官方文件《sql reference》。在oracle資料庫特性一章中單獨對分割槽，直接路徑插入，物化檢視等進行了詳述。

由此也可以看到oracle的dml擴充套件相當強，可以滿足很多資料操縱的需求，關注這些oracle提供的特性會使得問題的解決有更多的選擇。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-662795/，如需轉載，請註明出處，否則將追究法律責任。

【開發篇sql】 基礎概述（三） DDL和DML

相關文章

【開發篇sql】基礎概述（三） DDL和DML