SQL server 與Oracle開發比較

轉載老兵處

本文件主要從oracle與sql server語法上進行差異性比較，諸如兩者在管理，效能，優化等方面的差異不作比較。

●概念上區別

1.Oracle是一種物件關係資料庫管理系統（ORDBMS),而Sql server只是關係型資料庫管

理系統（RDBMS）.

2.Oracle使用Internet檔案系統，該系統基於Java的應用程式，可以使資料庫基於成為

Internet的開發平臺；Sql server是基於windows

3.Orace主要的三類檔案是：資料檔案，控制檔案，恢復日誌檔案

Sql server: 主要資料檔案（必須有且只能有一個），次要資料檔案以及日誌檔案

4.兩者支援的命令類別差不多，資料定義語言，資料操縱語言，事務處理控制語言，

資料控制語言.在Oracle中，在事務控制語言中除了commit,rollback等還多了一個

Savepoint，設定儲存點。

5.oracle sql的擴充套件叫PL/SQL,主要的結構化查詢工具有sql*plus，isql*plus, pl/sql等

Ms sql的擴充套件叫Transact-SQL，主要的結構化查詢工具就是自帶的查詢分析器

6.主要資料型別

Orace:主要支援char ,varchar2,long,number,datetime,raw,long raw,clob,blob,bfie

前面三個是Character資料型別，varchar2支援可變長度的字串，long支援可

變長度的字元資料，raw,long raw用於儲存二進位制資料，long raw可變長度

最後三個是大物件（lob)資料型別,儲存非結構化的資訊，例如聲音剪輯，視訊檔案

CLOG表示Character Lob,可以儲存大量的字元資料，它對於儲存非結構化的XML

文件非常有用。BLOG表示Binary LOG,此資料型別可以儲存大型二進位制物件，如

圖形、視訊剪輯，聲音檔案等

支不支援，money,貨幣

Sql server主要支援的文字型別char ,varchar,nchar,nvarchar,text,ntext,image，貨幣型別

Money,二進位制binary,varbinary等等

●開發語法

A.SQL語法

兩者的SQL語法基本上是相同的，只是一些細節方面的問題

一：表的管理：修改表的結構，如增加，刪除列,建立表

修改表

1.修改表的列的資料型別，大小的定義不同，如下面修改資料型別。

Ms sql是ALTER TABLE table_name ALTER COLUMN col DECIMAL (5, 2)

Oracle是 Alter TABLE table_name modify(col decimal(5,2))

2.增加和刪除表的列，兩者是相同的

Alter tabe table_name add(col definition)

Alter tabe table_name drop column col_name

3 .Oracle如果不要用某列，然後又不想刪除，就可以將某個列標記為未用

Alter tabe table_name set unused(tel_no)

.建立臨時表

Oracle的語法是：

Create global temporary table aa(col number) on commit preserve rows;

臨時表只在使用者會話時存在的表，或其資料在使用者事務處理期間可

持續存在的表,建立臨時表時可以指定該表是否應當在使用者會話

期間持續存在（使用on commit preserve rows）

On commit delete rows表示事務處理完成後刪除它的行

Ms sql的語法是:

Select * into #temptable from existedtable

二查詢方面：

1.查表的前N行記錄

oracle是用rownum如select * from table_name where rownum

Ms sql是用top如select top n * from table_name

2.查詢表的結構

Orace可以通過desc來檢視錶的結構語法是：desc table_name

或者使用資料字典表user_tab_coumns也可以檢視到

Select column_name,data_type from user_tab_coumns

Where table_name=''

Ms sql可以通過一些系統的儲存過程來看錶的結構

語法是：exec sp_help table_name

3.將一個表的資料新增到另外一個表中

a.新表存在前提下：兩者語法是一樣的，如

insert into newtable（col1）select col1 from old_table

b.新表不存在前提下，可以用

oracle可以用Create tabe new _table_name as select * from old_table

ms sql可以用Select * into new_table from old_table

三操作符

1．連線操作符

Oracle是‘||’; Ms sql是‘+’

2. Oracle的比較操作符中不等於除了'<>'之外，還有一個'!='

3.算術操作符，都是+-*/；邏輯操作符都是and,not,or(相同點)

4. oracle集合操作符除了union,union all之外還加入intersect,minus

Intersect是僅返回兩個查詢都有行，minus返回第一個查詢有第

二個查詢沒有的行

四函式

1.轉換函式

Oracle中有to_char(),to_date(),to_number()

Ms sql中有cast,convert

2.系統日期：

Oracle: sysdate

Ms sql: getdate()

如

Select sysdate from dual一定要from子句

Select getdate() 不一定要from子句

3.Decode函式相當if else，或者ms中的case語句

語法是decode(value,if1,then1,if2,then2....)

如decode(col1,'1','true','0','fase')

4.常規函式

Nvl 語法是NVL(EXP1,EXP2)表示如果ex1為空則返回ex2

Nvl2語法是nvl(exp1,exp2,exp3)表示如果ex1為空，則返回ex3,否則返回ex2

Nullif語法是nullif(ex1,ex2)如果這兩個表示式相等則返回空

coalesce語法是coalesce（ex1,ex2,....exn）返回第一個非空表示式

5.分組函式

Oracle中的分組函式Rollup,cube

Rollup返回的結果集包含分組行和小計行，cube產生交叉報表

如:

Select a,b,sum(c) from tabname group by rollup(a,b)

Select a,b,sum(c) from tabname group by cube(a,b)

Ms sql中的分組函式compute和compute by

Compute子句為行聚集函式生成彙總值，該彙總值做為一個附加的行顯示

在結果集中。沒有GROUP BY子句的情況下，也可以使用關鍵字COMPUTE.

此關鍵字使用MAX,MIN,SUM,COUNT,AVG等函式生成彙總值，而compute by

則在控制中斷時給出該彙總值,compute by必須包括在order by子句中。

還有很多函式，諸如日期函式，字串函式等等，就不一一作比較了。

Oracle中還有很多比較好的分析函式，也不列舉了。

五．Oracle中的資料庫物件

同義詞

作用：簡化SQL語句；隱藏物件的名稱和所有者，提供物件的公共訪問等。

語法：CREATE PUBLIC SYNONYM seqname FOR OBJECT

序列

用來生成唯一、邊續的整數，它通常用來自動生成主鍵或唯一值的鍵。

建立序列：create sequence seqname [increment by 1] start with 1 maxvalue 10

minvalue 1 cycle cache

訪問序列：

Select seqname.nextval from dual 將返回序列的初始值

Select seqname.currval from dual 返回序列的當前值

簇（cluster）：

簇是共享相同資料塊的一組表，因為這些表具有相同的列，並且經常一起使用。

當兩個或多個表的儲存在物理上十分接近時，可以通過簇來提高使用這些表的

SQL聯接語句的效能。簇是儲存表的方法

應該先建立簇，然後再建立簇中的表，

六．Oracle中的物件型別

1.抽象資料型別

此型別是包含一個或多個子型別的資料型別，並且這些資料型別並不侷限

於標準的oracle資料型別

如：create or replace type t_mm as object(col number(3),

col2 varchar2(20))/

此時可以在建立表時使用該型別，如下

Create table test (aa varchar2(5),bb t_mm, cc number(10))

插入記錄：

Insert into test values('cccc', t_mm(1,'col'))

型別宣告：

使用者定義的資料型別也可以宣告為final,not final,instantiable,

not instantiabe

Not final表示允許型別派生子型別。預設是final

create or replace type t_mm as object(col number(3),

col2 varchar2(20)) Not final

not instantiabe表示型別沒有建構函式。

2.可變陣列

可變陣列有助於在單個行中儲存和重複記錄的屬性。

資料與表中的其它資料是儲存在一起的，有限數目的行，不能被索引

建立可變陣列的語法是：

create type array1 as varray(5) of varchar2(5)

向可變陣列中插入記錄

Insert into test1 values ('2ee', array1('1','2','3','4','5') )

Select * from test1結果集如下

2ee

'1','2','3','4','5'

Select col2 from test1 結果集如下

'1','2','3','4','5'

Select * from table(select t.col2 from test1 where col1='2ee'); 結果集如下

1

2

3

4

5

3.巢狀表

它是包含在表中的表，對每行資料項數目沒有限制，一個表在

另一表中是作為一列，主表中的每一行的巢狀表可以包含若干行。

建立巢狀表

先建立一個型別

Create type ord_ty as object(itemcode varchar2(5),qty _ord number(5),

Qty_held number(5));

建立另一個抽象資料型別，即巢狀表資料型別

Create type ord_nt as table of ord_ty

建立巢狀表

Create table order_mas (orderno varchar2(5),odate date,

vencode varchar2(5), dets ord_nt) nested table dets store as ord_nt_tab;

向巢狀表中插入資料：

Insert into order_mas values（‘001’，to_date('18-07-08','DD-MM-YY'),

'V009',ord_nt( ord_ty('i001',10,5),ord_ty('i002',34,2));

更新巢狀表的值：

Update table(select e.dets from order_mas e where e.orderno='001') p

Set value（p) =ord_ty('i090',8,9) where p.itemcode='i001';

刪除巢狀表的值：

Delete from table(select e.dets from order_mas e where e.orderno='001') p

where p.itemcode='i001';

4.物件表

在物件表中每一行都是一個行物件，物件表與關係表不同：

物件表中的每一行都有一個OID值，即物件識別符號值。該值是在建立行時

分配的。可以使用create table命令來建立物件表。

在建立物件型別時，Oracle中是不允許為屬性定義約束條件，但是

可以在建立物件表時為物件型別的屬性指定約束條件。

Create table vend_master of vend_ty(vencode constraint vc_pk primary key);

建立物件表與關係表語法不同

表的使用方法不同插入資料可以使用抽象資料型別的建構函式，如果物件

表所基於抽象資料型別又基於另一抽象資料型別，則必須多個建構函式的嵌

套呼叫。

Insert into vend_master values (vend_ty());

5.物件檢視

藉助物件檢視可以將物件導向的結構（如抽象資料型別）應用於現有已經

投入使用的表，而不需重建整個應用程式

B.PL/SQL與T_SQL語法

一批處理

SQL的能力畢竟有限，諸如事務處理方面，批處理於是oracle與ms都把它進行了擴充套件，oracle的擴充套件叫PL/SQL由宣告部分，可執行部分，異常處理部分組成順序如下：

Declare declarations

Begin

Executable statements

Exception

Handles

End

Ms的擴充套件叫Transact_SQL,簡稱T_SQL.

批處理：就是一次執行處理一組命令的過程。GO關鍵字樗著批處理的結束。

如use master

go

複雜一點也是由三部分組成：宣告部分，可執行部分，異常處理部分。

不同的是，異常處理部分一般用跳轉語句來實現。

例如：

Declare declarations

Begin

Excutable statements

IF @ERROR <>0 GOTO ERROR

End

ERROR:

BEGIN

RAISERROR(20058,16,-1)

return(1)

END

二邏輯控制語句

1.控制結構：

Oracle

If condition then ........

End if

Case selector

when exp1 then statements

Else statements

End case

SQL SERVER

IF condition

Sql statements

ELSE

Sql statements

Case selector

When ex1 then statements

Else statements

End

2.迭代結構

oracle

Loop statements end loop;

While condition

Loop statement end loop;

Sql server

While condition

Begin

Sql statement

End

面象物件程式設計涉及的概念有物件，類，屬性和方法，物件導向的三大特性是：

封裝，繼承和多型。

將資料和函式包裝到一個單元中的過程稱為封裝。不能從外部訪問資料，只能包裝在

類中的那些函式才能訪問資料

繼承可以是SQL型別的繼承和方法的繼承。

多型是一個物件可以呈現多種形式的能力，這使得不同的物件可以具有相同的名稱

的方法，這些方法實現的任務相似，但實現方式卻不同。

三.變數與常量

Pl/sql變數與常量可以具有屬性，支援的屬性型別有

%type,%rowtype

宣告引用資料庫列或變數的資料型別的變數時，可以使用%type屬性。如：

Declare

Variable_name table_name.col_name %type

使用這個優點是，不需要知道列vencode的準確資料型別

%rowtype屬性提供表示表中行的記錄型別。

四.過程與函式(Procedure & Function)

a.建立儲存過程的語法不同：

Ms sql的語法是：

Create procedure procedure_name

(

@Id int =null,

@name varchar(10) out[put]

)

as

[變數定義區]

begin sql_statement end

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

CREATE FUNCTION function_name (@DATE datetime)

RETURNS int

AS begin sql statement end

物件導向的特性之一是封裝，程式包就是對相關PL/SQL型別，子程式，遊標，異常，變數，和常量的封裝，它包含兩部分程式包規格說明和程式包主體

在包規格說明書中，可以宣告型別，變數，常量，異常，遊標，子程式

程式包主體實現在程式包規格中定義的遊標、子程式

包頭語法部分：

Create or replace package package_name is|as

Public type and item declarations

Subprogram specifications

End package_name

例項如下：

Create or replace package pack_me is

Procedure order_proc(orno varchar2);

Function order_fun(ornos varchar2) return varchar2;

End pack_me

包體語法部分：

Create or replace package body package_name is|as

Private type and item decarations

Subprogram bodies

End package_name;

包體具體例項就不寫了，包頭隻是起一個宣告作用，具體實現部分都在包體裡面。

下面是建立儲存過程的語法：

Create [or replace] procedure procedure_name[arg1 in|out|in out]type {is|as}

[變數定義區]

Begin

Execute Sql statement

Exception handlers

End

注意：引數列表那裡,oracle是先定義引數是輸入還是輸出引數，然後再定義

引數型別;而sql server正好相反

建立函式的語法：

Create function function_name argument

Return datatype is|as

Local decaration

Begin

Excutable statement

Exception

Handles

End;

b.變數賦值

1. Oracle裡的用法：儲存過程中邊查詢邊給變數賦值。

select某一列名into變數名from table where ..;

相當於sql server中的select變數名=列. From table where ….

注意：select * /某一列名into表名

2. Oracle直接賦值的符號是：' := '

五觸發器(Trigger)

Oracle

Create or replace trigger trigger_name [before/after]

[insert/update/delete] on table_name

變數宣告

begin

Sql statement

end

Ms sql

Create trigger trigger_name on table

{for |after|instead of} [insert|update|delete] as sql_statements

六遊標(Curcor)

Oracle中提供兩種遊標型別，它們是靜態遊標和ref遊標

靜態遊標又分為隱式遊標與顯式遊標

Ref遊標，遊標變數是一種引用型別

隱式遊標屬性包括%notfound,%found,%rowcount,%isopen

如：

Begin

Delete from ta where rd='ddd'

If sql%notfound then

Dbms_output.put_line(''未找到值)

Else

Dbms_output.put_line（找到並刪除之）

End if

End

顯示遊標：可以用下面語句控制遊標

Open cursorname

Fetch cursor_name into var_name

Close cursor_name

建立遊標語法：

Oracle:

declare variable

Cursor test is select * from order

Sql server

Declare test cursor for select * from order

七.錯誤處理(Exception & test & debug)

有兩種型別的異常：一種預定義，另外一種是使用者自定義

預定義的型別有很多，如No_data_found,Cursor_already_open

對於Oracle的除錯，可以藉助第三方工具，如toad,Pl.sql Developer,

對於sql server採用變數或者print形式進行除錯

Oracle異常定義部分的示例：

Exception

When then statements

When others then

Statements

End;

其中是系統預定義的名字。

Raiser_application_error用於建立使用者定義的錯誤資訊的過程，使用者定義的錯誤訊息

可以指定的異常描述的更詳細

Sql server在錯誤處理上多采用自定義。或者是用goto跳轉的方式

如：

Begin

Sql statements

if @error<>0 goto error

end

Error:

Return;

或是直接用raiserror

if @error<>0

begin

raiserror('發生錯誤.',-1,-1)

return (1)

end

好了，兩者的主要的差異就寫到這，但願我表述清楚了，由於東西比較多，所以還有很多具體東西沒有寫

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

SQL server 與Oracle開發比較

相關文章