第十二章 使用嵌入式SQL（二）

嵌入式SQL代碼

簡單的SQL語句

可以將簡單的SQL語句（單個嵌入式SQL語句）用于各種操作，包括：

• INSERT，UPDATE，INSERT OR UPDATE和DELETE語句。
• `DDL語句。
• GRANT和REVOKE語句。
• 只返回一行的SELECT語句(或者如果只對返回的第一行感興趣)。

簡單的SQL語句也被稱為非基于游標的SQL語句。本章稍后將介紹基于游標的嵌入式SQL。

例如，以下語句查找ID為43的(唯一的)患者的姓名:

 &sql(SELECT Name INTO :name
    FROM Patient
    WHERE %ID = 43)

如果對可以返回多行的查詢使用簡單語句，則只返回第一行:

 &sql(SELECT Name INTO :name
    FROM Patient
    WHERE Age = 43)

根據(jù)查詢的不同，不能保證哪一行將首先被返回。此外，如果一個查詢包含一個INTO語句，并且該表不包含任何數(shù)據(jù)(SQLCODE=100)，執(zhí)行該查詢將導致未定義(空)的輸出主機變量。因此，在訪問輸出主機變量之前，所有簡單嵌入式SQL語句都應(yīng)該測試SQLCODE=0。

架構(gòu)名稱解析

表名，視圖名或存儲過程名是合格的（指定架構(gòu)名稱）或不合格的（不指定架構(gòu)名稱）。如果名稱未指定架構(gòu)名稱，則InterSystems IRIS會按以下方式解析架構(gòu)名稱：

• 數(shù)據(jù)定義：InterSystems IRIS使用系統(tǒng)范圍內(nèi)的默認架構(gòu)來解析不合格的名稱。如果默認模式不存在，則InterSystems IRIS將創(chuàng)建模式和相應(yīng)的類包。所有數(shù)據(jù)定義語句都使用系統(tǒng)范圍內(nèi)的默認架構(gòu)。數(shù)據(jù)定義語句忽略#Import和#SQLCompile Path宏預(yù)處理程序指令。
• 數(shù)據(jù)管理：InterSystems IRIS對包含嵌入式SQL語句的類或例程使用#SQLCompile路徑和/或#Import宏預(yù)處理程序指令指定的架構(gòu)搜索路徑。 #Import和#SQLCompile Path指令是具有不同功能的可能模式名稱的相互獨立列表。二者之一或兩者均可用于為不合格的表，視圖或存儲過程名稱提供架構(gòu)名稱。如果未指定架構(gòu)搜索路徑，則InterSystems IRIS將使用系統(tǒng)范圍的默認架構(gòu)名稱。

文字值

嵌入式SQL查詢可能包含文字值（字符串，數(shù)字或日期）。字符串應(yīng)用單引號（'）引起來。 （在InterSystems SQL中，雙引號指定分隔的標識符）：

/// d ##class(PHA.TEST.SQL).EmbedSQL4()
ClassMethod EmbedSQL4()
{
    &sql(SELECT 'Employee (' || Name || ')' INTO :name 
        FROM Sample.Employee)
    IF SQLCODE<0 {
        WRITE "SQLCODE錯誤 ",SQLCODE," ",%msg  QUIT
    } ELSEIF SQLCODE=100 {
        WRITE "查詢沒有結(jié)果"  QUIT
    }
    WRITE name
}

DHC-APP>d ##class(PHA.TEST.SQL).EmbedSQL4()
Employee (Adams,Susan E.)

數(shù)值可以直接使用。在InterSystems IRIS將這些文字值與字段值進行比較之前，先對文字數(shù)字和時間戳值進行“l(fā)ightly normalized”，如以下示例所示，其中+0050.000被格式化為50：

/// d ##class(PHA.TEST.SQL).EmbedSQL5()
ClassMethod EmbedSQL5()
{
    &sql(SELECT Name,Age INTO :name,:age 
       FROM Sample.Person
       WHERE Age = +0050.000)
    IF SQLCODE<0 {
        WRITE "SQLCODE錯誤 ",SQLCODE," ",%msg  QUIT
    } ELSEIF SQLCODE=100 {
        WRITE "查詢沒有結(jié)果"  QUIT
    }
    WRITE name," age=",age
}

DHC-APP>d ##class(PHA.TEST.SQL).EmbedSQL5()
Russell,Quentin V. age=50

可以指定算術(shù)、函數(shù)和特殊變量表達式:

/// d ##class(PHA.TEST.SQL).EmbedSQL6()
ClassMethod EmbedSQL6()
{
    &sql(DECLARE C3 CURSOR FOR 
            SELECT Name, Age - 65, $HOROLOG INTO :name,:retire,:today 
            FROM Sample.Person
            WHERE Age > 60
            ORDER BY Age, Name)
    &sql(OPEN C3)
    QUIT:(SQLCODE'=0)
    &sql(FETCH C3)
    WHILE (SQLCODE = 0) {
        WRITE $ZDATE(today)," ",name," has ",retire," eligibility years",!
        &sql(FETCH C3) 
    }
    &sql(CLOSE C3)
}

DHC-APP>d ##class(PHA.TEST.SQL).EmbedSQL6()
03/13/2021 Moon,Rhonda T. has -4 eligibility years
03/13/2021 Olsen,Ashley G. has -4 eligibility years
03/13/2021 Quixote,Terry J. has -4 eligibility years
03/13/2021 Yoders,Liza U. has -4 eligibility years
03/13/2021 Gore,Alfred M. has -3 eligibility years
03/13/2021 Houseman,Alice R. has -2 eligibility years
03/13/2021 Nichols,Heloisa M. has -2 eligibility years
03/13/2021 Houseman,Martin D. has 0 eligibility years
03/13/2021 LaRocca,David X. has 0 eligibility years
03/13/2021 Ng,Liza Z. has 0 eligibility years
03/13/2021 Smith,Elvis Y. has 0 eligibility years

在嵌入式SQL中，字符串文字中不允許使用以##開頭的一些字符序列，而必須使##lit指定。這些字符序列是##;, ##beginlit, ##expression(, ##function(, ##quote(, ##stripq(, and ##unique(。例如，以下示例失敗：

ClassMethod EmbedSQL7()
{
  WRITE "Embedded SQL test",!
  &sql(SELECT 'the sequence ##unique( is restricted' INTO :x)
  WRITE x
}

以下解決方法成功：

/// d ##class(PHA.TEST.SQL).EmbedSQL7()
ClassMethod EmbedSQL7()
{
    WRITE "Embedded SQL test",!
    &sql(SELECT 'the sequence ##lit(##unique() is restricted' INTO :x)
    WRITE x
}

DHC-APP>d ##class(PHA.TEST.SQL).EmbedSQL7()
Embedded SQL test
the sequence ##unique( is restricted

數(shù)據(jù)格式

在嵌入式SQL中，數(shù)據(jù)值處于“邏輯模式”。也就是說，值采用SQL查詢處理器使用的本機格式。對于未定義LogicalToODBC或LogicalToDisplay轉(zhuǎn)換的字符串，整數(shù)和其他數(shù)據(jù)類型，這無效。數(shù)據(jù)格式會影響％List數(shù)據(jù)以及％Date和％Time數(shù)據(jù)類型。

％List數(shù)據(jù)類型在邏輯模式下顯示為以非打印列表編碼字符開頭的元素值。 WRITE命令將這些值顯示為連接的元素。例如，Sample.Person的FavoriteColors字段以％List數(shù)據(jù)類型存儲數(shù)據(jù)，例如：$LISTBUILD（'Red'，'Black'）。在嵌入式SQL中，這在邏輯模式下顯示為RedBlack，長度為12個字符。在“顯示”模式下，它顯示為“Red,Black”；在ODBC模式下，它顯示為Red,Black。在下面的示例中顯示：

/// d ##class(PHA.TEST.SQL).EmbedSQL8()
ClassMethod EmbedSQL8()
{
    &sql(DECLARE C4 CURSOR FOR
            SELECT TOP 10 FavoriteColors INTO :colors
            FROM Sample.Person WHERE FavoriteColors IS NOT NULL)
    &sql(OPEN C4)
    QUIT:(SQLCODE'=0)
    &sql(FETCH C4)
    WHILE (SQLCODE = 0) {
        WRITE $LENGTH(colors),": ",colors,!
        &sql(FETCH C4) 
    }
    &sql(CLOSE C4)
}

DHC-APP>d ##class(PHA.TEST.SQL).EmbedSQL8()
21: ?ReOrangYellow
28: ?ReOrangYellowGreen
35: ?ReOrangYellowGreenGreen
36: ?ReOrangYellowGreeYellow
7: White
7: Black
14: GreenWhite
8:Purple
8:Yellow
10: ?Red?Red
 

InterSystems IRIS提供的％Date和％Time數(shù)據(jù)類型使用InterSystems IRIS內(nèi)部日期表示形式（$HOROLOG格式）作為其邏輯格式。 ％Date數(shù)據(jù)類型在邏輯模式下返回INTEGER數(shù)據(jù)類型值；在“顯示”模式下為VARCHAR數(shù)據(jù)類型值，在“ODBC”模式下為DATE數(shù)據(jù)類型值。 ％TimeStamp數(shù)據(jù)類型的邏輯，顯示和ODBC格式使用ODBC日期-時間格式（YYYY-MM-DD HH：MM：SS）。

例如，考慮以下類定義：

Class MyApp.Patient Extends %Persistent
{
/// Patient name
Property Name As %String(MAXLEN = 50);

/// Date of birth
Property DOB As %Date;

/// Date and time of last visit
Property LastVisit As %TimeStamp;
}

針對該表的簡單嵌入式SQL查詢將以邏輯模式返回值。例如，考慮以下查詢：

 &sql(SELECT Name, DOB, LastVisit
        INTO :name, :dob, :visit
         FROM Patient
        WHERE %ID = :id)

該查詢將三個屬性的邏輯值返回到主機變量名稱，dob和visit中：

請注意，dob是$HOROLOG格式?？梢允褂?code>$ZDATETIME函數(shù)將其轉(zhuǎn)換為顯示格式：

 SET dob = 44051
 WRITE $ZDT(dob,3),!

與WHERE子句中的true相同的考慮因素。例如，要查找具有給定生日的患者，必須在WHERE子句中使用邏輯值：

 &sql(SELECT Name INTO :name
        FROM Patient
        WHERE DOB = 43023)

或者，使用主機變量：

 SET dob = $ZDH("01/02/1999",1)

 &sql(SELECT Name INTO :name
        FROM Patient
        WHERE DOB = :dob)

在這種情況下，我們使用$ZDATEH函數(shù)將顯示格式日期轉(zhuǎn)換為其等效的$HOROLOG邏輯值。

權(quán)限檢查

嵌入式SQL不執(zhí)行SQL特權(quán)檢查?？梢栽L問所有表，視圖和列，并執(zhí)行任何操作，而不管特權(quán)分配如何。假定使用嵌入式SQL的應(yīng)用程序?qū)⒃谑褂们度胧絊QL語句之前檢查特權(quán)。

可以在嵌入式SQL中使用InterSystems SQL ％CHECKPRIV語句來確定當前權(quán)限。