第三章 SQL語言元素（一）

命令和關鍵字

InterSystems SQL命令（也稱為SQL語句）以關鍵字開頭，后跟一個或多個參數。其中一些參數可能是子句或函數陕赃，由它們自己的關鍵字標識省艳。

• InterSystems SQL命令沒有命令終止符，除非在特殊情況下（例如SQL過程代碼或觸發(fā)代碼）回铛，在這種情況下狗准，SQL命令以單個分號（;）終止。否則茵肃，InterSystems SQL命令不需要或接受分號命令終止符腔长。在InterSystems SQL中指定分號命令終止符會導致SQLCODE -25錯誤。 TSQL的InterSystemsIRIS?數據平臺實現（Transact-SQL）接受但不需要分號命令終止符验残。在將SQL代碼導入Inter Systems SQL時捞附，會去除分號命令終止符。
• InterSystems SQL命令沒有空格限制您没。如果命令項之間用空格隔開鸟召，則至少需要一個空格。 如果命令項之間用逗號分隔氨鹏，則不需要空格药版。算術運算符之前或之后不需要空格∮骼纾可以在以空格分隔的項目之間槽片，以逗號分隔的參數列表中的項目之間或在算術運算符之前或之后插入換行符或多個空格。

InterSystems SQL關鍵字包括命令名稱肢础，函數名稱还栓，謂詞條件名稱，數據類型名稱传轰，字段約束剩盒，優(yōu)化選項和特殊變量。它們還包括AND慨蛙，OR和NOT邏輯運算符辽聊，NULL列值指示符以及ODBC函數構造纪挎，例如{d dateval}和{fn CONCAT（str1，str2）}跟匆。

• 關鍵字不區(qū)分大小寫异袄。按照慣例，在本文檔中玛臂，關鍵字用大寫字母表示烤蜕，但是InterSystems SQL沒有大小寫限制。
• 有許多關鍵字是SQL保留字迹冤。 InterSystems SQL僅保留那些不能明確解析的關鍵字讽营。 SQL保留字可用作分隔符。

函數:內在的和外在的

• 內在的:InterSystems SQL支持大量內在的(系統(tǒng)提供的)函數泡徙。
  這些函數包括數字函數橱鹏、字符串函數以及日期和時間函數。

聚合函數是SQL固有函數堪藐，它計算列的所有值并返回單個聚合值莉兰。

• InterSystems SQL也可以支持用戶提供的ObjectScript函數調用(外部函數)，如下所示:

這種寫法只能在mac routine里庶橱，類文件里編譯報錯贮勃。

MySQL
     &sql(SELECT Name,$$MyFunc() INTO :n,:f FROM Sample.Person)
     WRITE "name is: ",n,!
     WRITE "function value is: ",f,!
     QUIT
MyFunc()
    SET x="my text"
    QUIT x

如果將用戶提供的（外部）函數的使用配置為系統(tǒng)范圍的選項贪惹，則該SQL語句只能調用用戶提供的（外部）函數苏章。默認為“否”。默認情況下奏瞬，嘗試調用用戶提供的函數會發(fā)出SQLCODE -372錯誤枫绅。可以使用％SYSTEM.SQL類的SetAllowExtrinsicFunctions（）方法在系統(tǒng)范圍內配置SQL對外部函數的使用硼端。若要確定當前設置并淋，請調用$SYSTEM.SQL.CurrentSettings（），該顯示顯示“允許在SQL語句中使用外部函數”選項珍昨。

不能使用用戶提供的函數來調用%routine(名稱以%字符開頭的例程)县耽。
嘗試這樣做會發(fā)出SQLCODE -373錯誤。

文字

InterSystems SQL文字具有以下語法：

literal ::= 
number | string-literal
number ::= 
 {digit}[.]digit{digit}[E[+|-]digit{digit}] 
digit ::=
 0..9
string-literal ::= 
std-string-literal | ObjectScript-empty-string
std-string-literal ::= 
 ' {std-character-representation} '
std-character-representation ::=
nonquote-character | quote-symbol
quote-symbol ::= 
 ''
ObjectScript-empty-string ::= 
 ""

文字是一系列代表實際（文字）值的字符镣典。它可以是數字或字符串兔毙。

• 數字不需要任何分隔符。它可以由數字0到9兄春，小數點字符澎剥，指數符號以及加號和減號組成。數字中只能使用一個小數點字符赶舆。該小數點只能用于數字的基數部分哑姚，不能用于指數部分祭饭。小數點后不需要數字。允許前導零和尾隨零叙量。指數（科學符號）符號為字母E倡蝙；大寫字母E和小寫字母E都可以接受，但是大寫字母E是首選用法宛乃。加號或減號可以加一個底數或一個指數悠咱。多個加號和減號可以加上x個基數； SQL將這些符號視為運算符征炼。 x只能有一個正負號析既。 SQL將此符號視為文字的一部分。請勿在數字中使用逗號或空格谆奥。
• 字符串文字包含一對分隔符眼坏，其中包含任何類型的字符串。首選的定界符是單引號字符酸些。要將分隔符指定為字符串中的文字宰译，請將該字符加倍；例如： 'Mary's office'.

空字符串是文字字符串魄懂；它由兩個單引號字符（''）表示沿侈。 NULL不是文字值；它表示沒有任何值市栗。

注意：在嵌入式SQL中缀拭，不允許在字符串文字中使用以##開頭的一些字符序列，如“使用嵌入式SQL”一章的“文字值”中所述填帽。此限制不適用于其他SQL調用蛛淋，例如動態(tài)SQL。

字符串分割符

使用單引號（'）字符作為字符串定界符篡腌。 SQL兼容性支持雙引號字符（“）的使用凳兵，但由于與定界標識符標準沖突搞动，因此強烈建議不要使用。將一對雙引號字符""解析為無效的定界標識符。并生成SQLCODE -1錯誤斟珊。

要在字符串中指定單引號字符作為字面字符糖耸，請指定一對這樣的字符作為字面轉義序列淘衙。
例如毁欣，'a 'normal' string'。

串聯

雙豎條(||)是首選的SQL連接操作符缀台。
它可以用于連接兩個數字棠赛、兩個字符串或一個數字和一個字符串。

下劃線(_)作為SQL連接操作符提供，以保證ObjectScript的兼容性睛约。
此連接操作符只能用于連接兩個字符串鼎俘。

如果兩個操作數都是字符串，并且兩個字符串都具有相同的排序規(guī)則類型辩涝，則所得的級聯字符串具有該排序規(guī)則類型贸伐。在所有其他情況下，連接的結果是排序類型EXACT怔揩。

NULL和空字符串

使用NULL關鍵字表示沒有指定值捉邢。
在SQL中，NULL始終是表示數據值因任何原因未指定或不存在的首選方式商膊。

SQL零長度字符串(空字符串)由兩個單引號字符指定伏伐。
空字符串(")與空字符串是不同的。
空字符串是一個已定義的值晕拆，一個不包含字符的字符串藐翎，一個長度為0的字符串。
一個零長度的字符串在內部由非顯示字符$CHAR(0)表示实幕。

注意:不建議使用SQL零長度字符串作為字段輸入值或字段默認值吝镣。
使用NULL表示數據值的缺失。

在SQL編碼中應避免使用SQL零長度字符串昆庇。
但是末贾，由于許多SQL操作都會刪除末尾的空格，所以只包含空格字符(空格和制表符)的數據值可能會導致SQL的零長度字符串整吆。

注意拱撵，不同的SQL length函數返回不同的值:length、CHAR_LENGTH和DATALENGTH返回SQL長度掂为。
$LENGTH返回ObjectScript表示長度裕膀。
長度不計算尾隨空格;
所有其他長度函數都計算末尾的空格员串。

null 處理

NOT NULL數據約束要求字段必須接收一個數據值;
不允許指定NULL而不是值勇哗。
這個約束不阻止使用空字符串值。

SELECT語句的WHERE或HAVING子句中的IS NULL謂詞選擇空值;
它不選擇空字符串值寸齐。

IFNULL函數計算一個字段值欲诺，如果字段值為NULL，則返回第二個參數中指定的值渺鹦。
它不會將空字符串值視為非空值扰法。

COALESCE函數從提供的數據中選擇第一個非空值。
它將空字符串值視為非空值毅厚。

當CONCAT函數或concenate操作符(||)連接一個字符串和一個NULL時塞颁，結果是NULL。
如下面的例子所示:

SELECT {fn CONCAT('fred',NULL)} AS FuncCat,   -- returns <null>
       'fred'||NULL AS OpCat                  -- returns <null>

AVG、COUNT祠锣、MAX酷窥、MIN和SUM聚合函數在執(zhí)行操作時忽略NULL值。
(COUNT *統(tǒng)計所有行伴网，因為不可能有一個所有字段都為空值的記錄蓬推。)
SELECT語句的DISTINCT關鍵字在其操作中包含NULL;
如果指定的字段有空值，DISTINCT返回一個空行.

AVG澡腾、COUNT和MIN沸伏、聚合函數受空字符串值的影響。
MIN函數將空字符串視為最小值动分，即使存在值為0的行毅糟。
MAX和SUM聚合函數不受空字符串值的影響。

null 表達式

對大多數SQL函數提供NULL作為操作數將返回NULL澜公。

任何以NULL作為操作數的SQL算術操作都返回NULL值留特。
因此,7 +零=零。
這包括二元運算加法(+)玛瘸、減法(-)蜕青、乘法(*)、除法(/)糊渊、整數除法(\)和取模(#)右核，以及一元符號運算符加號(+)和減號(-)。

算術操作中指定的空字符串將被視為0(零)值渺绒。
除法(/)贺喝，整數除法(\)，或對空字符串(6/ ")取模(#)會導致<DIVIDE>錯誤宗兼。

NULL的長度

在SQL中躏鱼，NULL的長度是沒有定義的(它返回< NULL >)。
然而殷绍，空字符串的長度被定義為長度為0染苛。
如下面的例子所示:

SELECT LENGTH(NULL) AS NullLen,   -- returns <null>
       LENGTH('') AS EmpStrLen    -- returns 0

如本例所示，SQL LENGTH函數返回SQL長度主到。

可以使用ASCII函數將SQL的零長度字符串轉換為NULL茶行，示例如下:

SELECT LENGTH(NULL) AS NullLen,                  -- returns <null> 
       LENGTH({fn ASCII('')}) AS AsciiEmpStrLen, -- returns <null>
       LENGTH('') AS EmpStrLen                   -- returns 0

但是，對標準SQL的某些系統(tǒng)間IRIS擴展對NULL和空字符串的長度的處理是不同的登钥。
$LENGTH函數返回這些值的InterSystems IRIS內部表示:NULL表示為長度為0的已定義值畔师，SQL空字符串表示為長度為0的字符串。
該功能與ObjectScript兼容牧牢。

SELECT $LENGTH(NULL) AS NullLen,    -- returns 0
$LENGTH('') AS EmpStrLen,           -- returns 0
$LENGTH('a') AS OneCharStrLen,      -- returns 1
$LENGTH(CHAR(0)) AS CharZero        -- returns 0 

這些值的內部表示方式的另一個重要位置是%STRING看锉、%SQLSTRING和%SQLUPPER函數姿锭，它們將空格附加到值中。
因為NULL實際上沒有值伯铣，所以在它后面添加一個空格會創(chuàng)建一個長度為1的字符串艾凯。
但是一個空字符串確實有一個字符值，所以在它后面加上一個空格會創(chuàng)建一個長度為2的字符串懂傀。
如下面的例子所示:

SELECT CHAR_LENGTH(%STRING(NULL)) AS NullLen,  -- returns 1
CHAR_LENGTH(%STRING('')) AS EmpStrLen          -- returns 2

注意趾诗，這個例子使用的是CHAR_LENGTH，而不是LENGTH蹬蚁。
因為LENGTH函數刪除了末尾的空格恃泪，所以LENGTH(%STRING(NULL))返回長度為0的字符串;
LENGTH(%STRING("))返回長度為2的字符串，因為%STRING追加的是前導空格犀斋，而不是尾隨空格贝乎。

ObjectScript和SQL

當SQL NULL輸出到ObjectScript時，它由ObjectScript空字符串("")表示叽粹，長度為0的字符串览效。

當SQL零長度字符串數據輸出到ObjectScript時，它由包含$CHAR(0)的字符串表示虫几，該字符串長度為1锤灿。

/// d ##class(PHA.TEST.SQL).Null()
ClassMethod Null()
{
    &sql(SELECT NULL,'' INTO :a,:b)
    WRITE !,"NULL length: ",$LENGTH(a)         // returns 0
    WRITE !,"empty string length: ",$LENGTH(b) // returns 1
}

DHC-APP>d ##class(PHA.TEST.SQL).Null()
 
NULL length: 0
empty string length: 1

在ObjectScript中，沒有值通常用空字符串("")表示辆脸。
當這個值被傳遞到嵌入式SQL中時但校，它會被視為空值，如下面的例子所示:

/// d ##class(PHA.TEST.SQL).Null1()
ClassMethod Null1()
{
    SET x=""
    SET myquery="SELECT NULL As NoVal,:x As EmpStr"
    SET tStatement=##class(%SQL.Statement).%New()
    SET qStatus=tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
    SET rset=tStatement.%Execute()
    WHILE rset.%Next() {
        WRITE "NoVal:",rset.%Get("NoVal")," length ",$LENGTH(rset.%Get("NoVal")),!      // length 0
        WRITE "EmpStr:",rset.%Get("EmpStr")," length ",$LENGTH(rset.%Get("EmpStr")),!   // length 0
    }
    WRITE "End of data"
}

DHC-APP>d ##class(PHA.TEST.SQL).Null1()
NoVal: length 0
EmpStr: length 0
End of data

如果指定了一個未定義的輸入主機變量啡氢，嵌入式SQL將其值視為NULL状囱。

當將NULL或空字符串值從嵌入式SQL傳遞到ObjectScript時，NULL被轉換為長度為0的字符串倘是，空字符串被轉換為長度為1的字符串亭枷。
如下面的例子所示:

/// d ##class(PHA.TEST.SQL).Null2()
ClassMethod Null2()
{
    &sql(SELECT 
        NULL,
        ''
        INTO :a,:b)
    WRITE !,"The length of NULL is: ",$LENGTH(a)         // length 0
    WRITE !,"The length of empty string is: ",$LENGTH(b) // length 1
}

DHC-APP>d ##class(PHA.TEST.SQL).Null2()
 
The length of NULL is: 0
The length of empty string is: 1

在下面的例子中，SQL的空字符串加上一個空格被傳遞為長度為2的字符串:

/// d ##class(PHA.TEST.SQL).Null3()
ClassMethod Null3()
{
    &sql(SELECT %SQLUPPER('')
        INTO :y )
    WRITE !,"SQL empty string length: ",$LENGTH(y)
}

DHC-APP> d ##class(PHA.TEST.SQL).Null3()
 
SQL empty string length: 2