SQL in ORACLE and SQL Server

dbigbear發表於2007-10-19
SQLOracleServer

其它行級別的函式

下面是Oracle支援的其它行級別的函式以及它們的Microsoft SQL Server等價函式。

函式

Oracle

Microsoft SQServer

返回第一個非空表示式

DECODE

COALESCE

當前序列值

CURRVAL

N/A

下一個序列值

NEXTVAL

N/A

如果exp1 = exp2, 返回null

DECODE

NULLIF

使用者登入賬號ID數字

UID

SUSER_ID

使用者登入名

USER

SUSER_NAME

使用者資料庫ID數字

UID

USER_ID

使用者資料庫名

USER

USER_NAME

當前使用者

CURRENT_USER

CURRENT_USER

使用者環境(audit trail)

USERENV

N/A

在CONNECT BY子句中的級別

LEVEL

N/A

聚合函式

下面是Oracle支援的合計函式和它們的Microsoft SQL Server等價函式。

函式

Oracle

Microsoft SQServer

Average

AVG

AVG

Count

COUNT

COUNT

Maximum

MAX

MAX

Minimum

MIN

MIN

Standard deviation

STDDEV

STDEV or STDEVP

Summation

SUM

SUM

Variance

VARIANCE

VAR or VARP

條件測試

Oracle 的DECODE語句和Microsoft SQL Server的CASE表示式都執行條件測試。當test_value中的值和後面的任何表示式匹配的時候，相關的值就返回。如果沒有找到任何匹配的值， 就返回default_value。如果沒有指定default_value，在沒有匹配的時候，DECODE和CASE都返回一個NULL。下表顯示了 該語句的語法，同時給出了轉換DECODE命令的示例。

Oracle

Microsoft SQ

DECODE (test_value,
expression1, value1
[[,expression2, value2] […]]
[,default_value]
)

CREATE VIEW STUDENT_ADMIN.STUDENT_GPA
(SSN, GPA)
AS SELECT SSN, ROUND(AVG(DECODE(grade
,'A', 4
,'A+', 4.3
,'A-', 3.7
,'B', 3
,'B+', 3.3
,'B-', 2.7
,'C', 2
,'C+', 2.3
,'C-', 1.7
,'D', 1
,'D+', 1.3
,'D-', 0.7
,0)),2)
FROM STUDENT_ADMIN.GRADE
GROUP BY SSN

CASE input_expression
WHEN when_expression THEN    result_expression
[[WHENwhen_expressionTHEN    result_expression] [...]]
[ELSEelse_result_expression]
END

CREATE VIEW STUDENT_ADMIN.STUDENT_GPA
(SSN, GPA)
AS SELECT SSN, ROUND(AVG(CASE grade
WHEN 'A' THEN 4
WHEN 'A+' THEN 4.3
WHEN 'A-' THEN 3.7
WHEN 'B' THEN 3
WHEN 'B+' THEN 3.3
WHEN 'B-' THEN 2.7
WHEN 'C' THEN 2
WHEN 'C+' THEN 2.3
WHEN 'C-' THEN 1.7
WHEN 'D' THEN 1
WHEN 'D+' THEN 1.3
WHEN 'D-' THEN 0.7
ELSE 0
END),2)
FROM STUDENT_ADMIN.GRADE
GROUP BY SSN

CASE表示式可以支援用SELECT語句執行布林測試，這是DECODE命令所不允許的。欲瞭解關於CASE表示式的詳細資訊，請參閱SQL Server聯機手冊。

把值轉換為不同的資料型別

Microsoft SQL Server的CONVERT和CAST函式都是多目標轉換函式。它們提供了相似的功能，把一種資料型別的表示式轉換為另一種資料型別的表示式，並且支援多種專門資料的格式。

  • CAST(expression AS data_type)
  • CONVERT (data type[(length)], expression [, style])

CAST是一個SQL-92標準的函式。這些函式執行同Oracle的TO_CHAR、TO_NUMBER、TO_DATE、HEXTORAW以及RAWTOTEXT函式相同的功能。

這裡所指的資料型別是任何表示式將被轉換成為的系統資料型別。不能使用使用者定義的資料型別。長度引數是可選的，該引數用於char、varchar、binary以及varbinary資料型別。允許的最大長度是8000。

轉換

Oracle

Microsoft SQServer

字元到數字

TO_NUMBER('10')

CONVERT(numeric, '10')

數字到字元

TO_CHAR(10)

CONVERT(char, 10)

字元到日期

TO_DATE('04-JUL-97')
TO_DATE('04-JUL-1997',
'dd-mon-yyyy')
TO_DATE('July 4, 1997',
'Month dd, yyyy')

CONVERT(datetime, '04-JUL-97')
CONVERT(datetime, '04-JUL-1997')
CONVERT(datetime, 'July 4, 1997')

日期到字元

TO_CHAR(sysdate)
TO_CHAR(sysdate, 'dd mon yyyy')
TO_CHAR(sysdate, 'mm/dd/yyyy')

CONVERT(char, GETDATE())
CONVERT(char, GETDATE(), 106)
CONVERT(char, GETDATE(), 101)

16進位制到2進位制

HEXTORAW('1F')

CONVERT(binary, '1F')

2進位制到16進位制

RAWTOHEX
(binary_column)

CONVERT(char, binary_column)

請注意字串是怎樣轉換為日期的。在Oracle中，預設的日期格式模型是“DD-MON-YY”如果你使用任何其它格式，你必須提供一個合適的日期格式模型。CONVERT函式自動轉換標準日期格式，不需要任何格式模型。

從日期轉換到字串時，CONVERT函式的預設輸出是“dd mon yyyy hh:mm:ss:mmm(24h)”。用一個數字風格程式碼來格式化輸出，使它能輸出為其它型別的日期格式模型。欲瞭解CONVERT函式的詳細資訊，請參閱SQL Server聯機手冊。

下表顯示了Microsoft SQL Server日期的預設輸出。

Without Century

With Century

Standard

Output

-

0 or 100 (*)

Default

mon dd yyyy hh:miAM (or PM)

1

101

USA

mm/dd/yy

2

102

ANSI

yy.mm.dd

3

103

British/French

dd/mm/yy

4

104

German

dd.mm.yy

5

105

Italian

dd-mm-yy

6

106

-

dd mon yy

7

107

-

mon dd, yy

8

108

-

hh:mm:ss

-

9 or 109 (*)

Default milliseconds

mon dd yyyy hh:mi:ss:mmm (AM or PM)

10

110

USA

mm-dd-yy

11

111

Japan

yy/mm/dd

12

112

ISO

yymmdd

-

13 or 113 (*)

Europe default

dd mon yyyy hh:mm:ss:mmm(24h)

14

114

-

hh:mi:ss:mmm(24h)

使用者定義函式

Oracle PL/SQL函式可以在Oracle SQL語句中使用。在Microsoft SQL Server中一般可以通過其它方式來實現同樣的功能。

在SQL Server中可以用表中給出的查詢來代替。

Oracle

Microsoft SQServer

SELECT SSN, FNAME, LNAME, )    TUITION_PAID,
   TUITION_PAID/GET_SUM_
   MAJOR(MAJOR)
   AS PERCENT_MAJOR
FROM STUDENT_ADMIN.STUDENT

SELECT SSN, FNAME, LNAME, TUITION_PAID, TUITION_PAID/SUM_MAJOR AS PERCENT_MAJOR
FROM STUDENT_ADMIN.STUDENT,
  (SELECT MAJOR,      SUM(TUITION_PAID) SUM_MAJOR
  FROM STUDENT_ADMIN.STUDENT
  GROUP BY MAJOR) SUM_STUDENT
WHERE STUDENT.MAJOR =      SUM_STUDENT.MAJOR

CREATE OR REPLACE FUNCTION GET_SUM_MAJOR
(INMAJOR VARCHAR2) RETURN NUMBER
AS SUM_PAID NUMBER;
BEGIN
SELECT SUM(TUITION_PAID) INTO    SUM_PAID
FROM STUDENT_ADMIN.STUDENT
WHERE MAJOR = INMAJOR;
RETURN(SUM_PAID);
END GET_SUM_MAJOR;

No CREATE FUNCTION syntax is required; use CREATE PROCEDURE syntax.

比較操作符

Oracle和Microsoft SQL Server的比較操作符幾乎是一樣的。

算符

Oracle

Microsoft SQServer

等於

(=)

(=)

大於

(>)

(>)

小於

(<)

(<)

大於或等於

(>=)

(>=)

小於或等於

(<=)

(<=)

不等於

(!=, <>, ^=)

(!=, <>, ^=)

不大於，不小於

N/A

!> , !<

在集合中任意成員中

IN

IN

不在集合中的任何成員中

NOT IN

NOT IN

集合中的任意值

ANY, SOME

ANY, SOME

提交集合中的所有值

!= ALL, <> ALL, < ALL,
> ALL, <= ALL, >= ALL, != SOME, <> SOME,
< SOME, > SOME,
<= SOME, >= SOME

!= ALL, <> ALL, < ALL,
> ALL, <= ALL, >= ALL, != SOME, <> SOME,
< SOME, > SOME,
<= SOME, >= SOME

像模式（Like pattern）

LIKE

LIKE

不像模式（Not like pattern）

NOT LIKE

NOT LIKE

X和y之間的值

BETWEEN x AND y

BETWEEN x AND y

不在x和y之間的值

NOT BETWEEN

NOT BETWEEN

值存在

EXISTS

EXISTS

值不存在

NOT EXISTS

NOT EXISTS

值{為|不為}空

IS NULL, IS NOT NULL

Same. Also = NULL,
!= NULL for backward compatibility (not recommended).

模式匹配

SQL Server的LIKE關鍵字提供了有用的萬用字元搜尋功能，這個功能在Oracle中不支援。除了所有的RDBMS都支援的（%）和（_）萬用字元以外，SQL Server還支援（[ ]）和（[^]）萬用字元。

（[ ]）字元用來查詢在一個範圍內的所有單個字元。例如，如果你需要查詢包含一個從a到f的字元的資料，你可以這樣寫：“LIKE '[a-f]'”或者“LIKE '[abcdef]'”。這些附加的萬用字元的有效性在下表中給出。

Oracle

Microsoft SQ

SELECT * FROM STUDENT_ADMIN.STUDENT
WHERE LNAME LIKE 'A%'
OR LNAME LIKE 'B%'
OR LNAME LIKE 'C%'

SELECT * FROM STUDENT_ADMIN.STUDENT
WHERE LNAME LIKE '[ABC]%'

[^]萬用字元用來標記那些不在特定範圍內的字元。例如，如果除了a到f以外的所有字元都是可以接受的，你可以這樣書寫：LIKE '[^a - f]'或者LIKE '[^abcdef]'。

欲瞭解關於LIKE關鍵字的詳細資訊，請參閱SQL Server聯機手冊。

在比較中使用 NULL

儘管Microsoft SQL Server傳統上支援SQL-92標準的和一些非標準的NULL行為，但是它還是支援Oracle中的NULL的用法。

為了支援分散式查詢，SET ANSI_NULLS必須設定為ON。

在 進行連線的時候，SQL Server的SQL Server ODBC驅動程式和OLE DB提供者自動把SET ANSI_NULLS設定為ON。這個設定可以在ODBC資料來源、ODBC連線屬性、或者是在連線到SQL Server之前在應用程式中設定的OLE DB連線屬性中進行配置。在從DB-Library應用程式中連線時，SET ANSI_NULLS預設為OFF。

當SET ANSI_DEFAULTS為ON時，SET ANSI_NULLS被允許。

欲瞭解關於NULL用法的詳細資訊，請參閱SQL Server聯機手冊。

字串連線

Oracle使用兩個管道符號（||）來作為字串連線操作符，SQL Server則使用加號（+）。這個差別要求你在應用程式中做小小的修改。

Oracle

Microsoft SQ

SELECT FNAME||' '||LNAME AS NAME
FROM STUDENT_ADMIN.STUDENT

SELECT FNAME +' '+ LNAME AS    NAME
FROM STUDENT_ADMIN.STUDENT

流控制（ Control-of-Flow ）語言

流控制語言控制SQL語句執行流，語句塊以及儲存過程。PL/SQL和Transact-SQL提供了多數相同的結構，但是還是有一些語法差別。

關鍵字

這是兩個RDBMS支援的關鍵字。

語句

Oracle PL/SQL

Microsoft SQServer
Transact-SQL

宣告變數

DECLARE

DECLARE

語句塊

BEGIN...END;

BEGIN...END

條件處理

IF…THEN,
ELSIF…THEN,
ELSE
ENDIF;

IF…[BEGIN…END]
ELSE <condition>
[BEGIN…END]
ELSE IF <condition>
CASE expression

無條件結束

RETURN

RETURN

無條件結束當前程式塊後面的語句

EXIT

BREAK

重新開始一個WHILE迴圈

N/A

CONTINUE

等待指定間隔

N/A (dbms_lock.sleep)

WAITFOR

迴圈控制

WHILE LOOP…END LOOP;


LABEL…GOTO LABEL;
FOR…END LOOP;
LOOP…END LOOP;

WHILE <condition>
BEGIN… END

LABEL…GOTO LABEL

程式註釋

/* … */, --

/* … */, --

列印輸出

RDBMS_OUTPUT.PUT_
LINE

PRINT

引發程式錯誤（Raise program error）

RAISE_APPLICATION_
ERROR

RAISERROR

執行程式

EXECUTE

EXECUTE

語句終止符

Semicolon (;)

N/A

宣告變數

Transact-SQL和PL/SQL的變數是用DECLARE關鍵字建立的。Transact-SQL變數用@標記，並且就像PL/SQL一樣，在第一次建立時，用空值初始化。

Oracle

Microsoft SQ

DECLARE
VSSN CHAR(9);
VFNAME VARCHAR2(12);
VLNAME VARCHAR2(20);
VBIRTH_DATE DATE;
VLOAN_AMOUNT NUMBER(12,2);

DECLARE
@VSSN CHAR(9),
@VFNAME VARCHAR2(12),
@VLNAME VARCHAR2(20),
@VBIRTH_DATE DATETIME,
@VLOAN_AMOUNT NUMERIC(12,2)

Transact -SQL不支援%TYPE和%ROWTYPE變數資料型別定義。一個Transact-SQL變數不能在DECLARE命令中初始化。在 Microsoft SQL Server資料型別定義中也不能使用Oracle的NOT NULL和CONSTANT關鍵字。

像Oracle的LONG和LONG RAW資料型別一樣。文字和圖形資料型別不能被用做變數定義。此外，Transact-SQL不支援PL/SQL風格的記錄和表的定義。

給變數賦值

Oracle和Microsoft SQL Server提供了下列方法來為本地變數賦值。

Oracle

Microsoft SQ

Assignment operator (:=)

SET @local_variable = value

SELECT...INTO syntax for selecting column values from a single row

SELECT @local_variable = expression [FROM…] for assigning a literal value, an expression involving other local variables, or a column value from a single row

FETCH…INTO syntax

FETCH…INTO syntax

這裡有一些語法示例

Oracle

Microsoft SQ

DECLARE VSSN CHAR(9);
VFNAME VARCHAR2(12);
VLNAME VARCHAR2(20);
BEGIN
VSSN := '123448887';
SELECT FNAME, LNAME INTO VFNAME, VLNAME FROM STUDENTS WHERE SSN=VSSN;
END;

DECLARE @VSSN CHAR(9),
@VFNAME VARCHAR(12),
@VLNAME VARCHAR(20)
SET @VSSN = '12355887'
SELECT @VFNAME=FNAME, @VLNAME=LNAME FROM STUDENTS WHERE SSN = @VSSN

語句塊

Oracle PL/SQL和Microsoft SQL Server Transact-SQL都支援用BEGIN…END術語來標記語句塊。Transact-SQL不需要在DECLARE語句後使用一個語句塊。如果在 Microsoft SQL Server 中的IF語句和WHILE迴圈中有多於一個語句被執行，則需要使用BEGIN…END語句塊。

Oracle

Microsoft SQ

DECLARE
DECLARE VARIABLES ...
BEGIN -- THIS IS REQUIRED SYNTAX
PROGRAM_STATEMENTS ...
IF ...THEN
STATEMENT1;
STATEMENT2;
STATEMENTN;
END IF;
WHILE ... LOOP
STATEMENT1;
STATEMENT2;
STATEMENTN;
END LOOP;
END; -- THIS IS REQUIRED SYNTAX

DECLARE
DECLARE VARIABLES ...
BEGIN -- THIS IS OPTIONAL SYNTAX
PROGRAM_STATEMENTS ...
IF ...
BEGIN
STATEMENT1
STATEMENT2
STATEMENTN
END
WHILE ...
BEGIN
STATEMENT1
STATEMENT2
STATEMENTN
 END
END -- THIS IS REQUIRED SYNTAX

條件處理

Microsoft SQL Server Transact-SQL的條件語句包括IF和ELSE，但不包括Oracle PL/SQL中的ELSEIF語句。可以用巢狀多重IF語句來到達同樣的效果。對於廣泛的條件測試，用CASE表示式也許更容易和可讀一些。

Oracle

Microsoft SQ

DECLARE
VDEGREE_PROGRAM CHAR(1);
VDEGREE_PROGRAM_NAME VARCHAR2(20);
BEGIN
VDEGREE_PROGRAM := 'U';
IF VDEGREE_PROGRAM = 'U' THEN
VDEGREE_PROGRAM_NAME :=
'Undergraduate';
ELSIF VDEGREE_PROGRAM = 'M'  THEN VDEGREE_PROGRAM_
    NAME := 'Masters';
ELSIF VDEGREE_PROGRAM = 'P'     THEN VDEGREE_PROGRAM_
NAME := 'PhD';
ELSE VDEGREE_PROGRAM_
NAME := 'Unknown';
END IF;
END;

DECLARE
@VDEGREE_PROGRAM CHAR(1),
@VDEGREE_PROGRAM_NAME VARCHAR(20)
SELECT @VDEGREE_PROGRAM = 'U'
SELECT @VDEGREE_PROGRAM_
   NAME = CASE @VDEGREE_PROGRAM
   WHEN 'U' THEN 'Undergraduate'
   WHEN 'M' THEN 'Masters'
   WHEN 'P' THEN 'PhD'.
   ELSE 'Unknown'
END

重複執行語句（迴圈）

Oracle PL/SQL提供了無條件的LOOP和FOR LOOP。Transact-SQL則提供了WHILE迴圈和GOTO語句。

WHILE Boolean_expression

    {sql_statement | statement_block}

    [BREAK] [CONTINUE]

WHILE迴圈需要測試一個布林表示式來決定一個或者多個語句的重複執行。只要給定的表示式結果為真，這個（些）語句就一直重複執行下去。如果有多個語句需要執行，則這些語句必須放在一個BEGIN…END塊中。

Oracle

Microsoft SQ

DECLARE
COUNTER NUMBER;
BEGIN
COUNTER := 0
WHILE (COUNTER <5) LOOP
COUNTER := COUNTER + 1;
END LOOP;
END;

DECLARE
@COUNTER NUMERIC
SELECT@COUNTER = 1
WHILE (@COUNTER <5)
BEGIN
   SELECT @COUNTER =
   @COUNTER +1
END

語 句的執行可以在迴圈的內部用BREAK和CONTINUE關鍵字控制。BREAK關鍵字使WHILE迴圈無條件的結束，而CONTINUE關鍵字使 WHILE迴圈跳過後面的語句重新開始。BREAK關鍵字同Oracle PL/SQL中的EXIT關鍵字是等價的。而在Oracle中沒有和CONTINUE等價的關鍵字

GOTO 語句

Oracle和Microsoft SQL Server都有GOTO語句，但是語法不同。GOTO語句使Transact-SQL跳到指定的標號處執行，在GOTO語句後指定標號之間的任何語句都不會被執行。

Oracle

Microsoft SQ

GOTO label;
<<label name here>>

GOTO label

PRINT 語句

Transact-SQL的PRINT語句執行同PL/SQL的RDBMS_OUTPUT.put_line過程同樣的操作。該語句用來列印使用者給定的訊息。

用PRINT語句列印的訊息上限是8,000個字元。定義為char或者varchar資料型別的變數可以嵌入列印語句。如果使用其它資料型別的變數，則必須使用CONVERT或者CAST函式。本地變數、全域性變數可以被列印。可以用單引號或者雙引號來封閉文字。

從儲存過程返回

Microsoft SQL Server和Oracle都有RETURN語句。RETURN使你的程式從查詢或者過程中無條件的跳出。RETURN是立即的、完全的、並且可以用於從過程、批處理或者語句塊的任意部分跳出。在REUTRN後面的語句將不會被執行。

Oracle

Microsoft SQ

RETURN expression:

RETURN [integer_expression]

引發程式錯誤（ Raising program errors ）

Transact-SQL的RAISERROR返回一個使用者定義的錯誤訊息，並且設定一個系統標誌來記錄發生了一個錯誤。這個功能同PL/SQL的raise_application_error異常處理器的功能是相似的。

RAISERROR語句允許客戶重新取得sysmessages表的一個入口，或者用使用者指定的嚴重性和狀態資訊動態的建立一條訊息。在被定義後，訊息被送回客戶端作為系統錯誤訊息。

RAISERROR ({msg_id | msg_str}, severity, state

    [, argument1 [, argument2]])

    [WITH options]

在 轉換你的PL/SQL程式時，也許用不著使用RAISERROR語句。在下面的示例程式碼中。PL/SQL程式使用 raise_application_error異常處理器，但是Transact-SQL程式則什麼也沒用。包括 raise_application_error異常處理器是為了防止PL/SQL返回不明確的未經處理的異常錯誤訊息。作為代替，當一個不可預見的問題 發生的時候，異常處理器總是返回Oracle錯誤訊息。

當一個Transact-SQL失敗時，它總是返回一個詳細的錯誤訊息給客戶程式。因此，除非需要某些特定的錯誤處理，一般是不需要RAISERROR語句的。

Oracle

Microsoft SQ

CREATE OR REPLACE FUNCTION
DEPT_ADMIN.DELETE_DEPT
(VDEPT IN VARCHAR2) RETURN NUMBER AS
BEGIN
DELETE FROM DEPT_ADMIN.DEPT
WHERE DEPT = VDEPT;
RETURN(SQL%ROWCOUNT);
EXCEPTION
WHEN OTHER THEN
RAISE_APPLICATION_ERROR
(-20001,SQLERRM);
END DELETE_DEPT;
/

CREATE PROCEDURE
DEPT_ADMIN.DELETE_DEPT
@VDEPT VARCHAR(4) AS
DELETE FROM DEPT_DB.DBO.DEPT
WHERE DEPT = @VDEPT
RETURN @@ROWCOUNT
GO

 

相關文章