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

嵌入式SQL變量

以下局部變量在嵌入式SQL中具有特殊用途。這些局部變量名稱區(qū)分大小寫。在過程啟動時，這些變量是不確定的。它們由嵌入式SQL操作設(shè)置。也可以使用SET命令直接設(shè)置它們，或使用NEW命令將其重置為未定義。像任何局部變量一樣，值將在過程持續(xù)期間或直到設(shè)置為另一個值或使用NEW進(jìn)行定義之前一直存在。例如，某些成功的嵌入式SQL操作未設(shè)置％ROWID。執(zhí)行這些操作后，％ROWID是未定義的或保持設(shè)置為其先前值。

• %msg
• %ROWCOUNT
• %ROWID
• SQLCODE

這些局部變量不是由Dynamic SQL設(shè)置的。 （請注意，SQL Shell和Management Portal SQL接口執(zhí)行Dynamic SQL。）相反，Dynamic SQL設(shè)置相應(yīng)的對象屬性。

在嵌入式SQL中使用以下ObjectScript特殊變量。這些特殊的變量名稱不區(qū)分大小寫。在過程啟動時，這些變量將初始化為一個值。它們由嵌入式SQL操作設(shè)置。不能使用SET或NEW命令直接設(shè)置它們。

• $TLEVEL
• $USERNAME

作為已定義的InterSystems IRIS嵌入式SQL接口的一部分，InterSystems IRIS可以在嵌入式SQL處理期間設(shè)置任何這些變量。

如果嵌入式SQL在類方法中（procedureBlock = ON），則系統(tǒng)會自動將所有這些變量放在PublicList中，并自動將SQLCODE，％ROWID，％ROWCOUNT，％msg以及SQL語句?？梢酝ㄟ^引用方法來傳遞這些變量；通過引用傳遞的變量將不會在類方法過程塊中自動更新。

如果嵌入式SQL在例程中，則程序員有責(zé)任在調(diào)用嵌入式SQL之前新建％msg，％ROWCOUNT，％ROWID和SQLCODE變量。更新這些變量可防止干擾這些變量的先前設(shè)置。為避免<FRAMESTACK>錯誤，不應(yīng)在迭代周期內(nèi)執(zhí)行此NEW操作。

%msg

包含系統(tǒng)提供的錯誤消息字符串的變量。如果InterSystems SQL將SQLCODE設(shè)置為負(fù)整數(shù)（表示錯誤），則僅設(shè)置％msg。如果SQLCODE設(shè)置為0或100，則％msg變量與其先前值保持不變。

此行為不同于相應(yīng)的Dynamic SQL ％Message屬性，當(dāng)沒有當(dāng)前錯誤時，該屬性將設(shè)置為空字符串。

在某些情況下，特定的SQLCODE錯誤代碼可能與一個以上的％msg字符串相關(guān)聯(lián)，描述了生成SQLCODE的不同條件。 ％msg還可以接受用戶定義的消息字符串。當(dāng)觸發(fā)器代碼顯式設(shè)置％ok = 0來中止觸發(fā)器時，這最常用于從觸發(fā)器發(fā)出用戶定義的消息。

當(dāng)執(zhí)行SQL代碼時，將使用有效的NLS語言生成錯誤消息字符串?？梢栽诓煌腘LS語言環(huán)境中編譯SQL代碼。該消息將根據(jù)運行時NLS環(huán)境生成。請參見$ SYS.NLS.Locale.Language。

%ROWCOUNT

一個整數(shù)計數(shù)器，指示受特定語句影響的行數(shù)。

• INSERT，UPDATE，INSERT OR UPDATE和DELETE將％ROWCOUNT設(shè)置為受影響的行數(shù)。帶有顯式值的INSERT命令只能影響一行，因此將％ROWCOUNT設(shè)置為0或1。INSERT查詢結(jié)果，UPDATE或DELETE可以影響多行，因此可以將％ROWCOUNT設(shè)置為0或正數(shù)。整數(shù)。
• 無論刪除多少行還是刪除任何行，TRUNCATE TABLE始終將％ROWCOUNT設(shè)置為–1。因此，要確定實際刪除的行數(shù)，請在TRUNCATE TABLE之前對表執(zhí)行COUNT（*），或者使用DELETE而不是TRUNCATE TABLE刪除表中的所有行。
• 沒有聲明游標(biāo)的SELECT只能作用于一行，因此執(zhí)行簡單的SELECT總是會將％ROWCOUNT設(shè)置為1（與檢索到的選擇標(biāo)準(zhǔn)匹配的單行）或0（沒有與選擇標(biāo)準(zhǔn)匹配的行）。
• DECLARE游標(biāo)名CURSOR FOR SELECT不會初始化％ROWCOUNT； SELECT之后，％ROWCOUNT不變，而OPEN游標(biāo)名之后，％ROWCOUNT不變。第一個成功的FETCH設(shè)置％ROWCOUNT。如果沒有行符合查詢選擇條件，則FETCH設(shè)置％ROWCOUNT = 0；否則，設(shè)置％ROWCOUNT = 0。如果FETCH檢索與查詢選擇條件匹配的行，則它將設(shè)置％ROWCOUNT = 1。隨后的每個獲取行的FETCH都將遞增％ROWCOUNT。 CLOSE時或FETCH發(fā)出SQLCODE 100（無數(shù)據(jù)或無更多數(shù)據(jù)）時，％ROWCOUNT包含已檢索的總行數(shù)。

此SELECT行為與相應(yīng)的Dynamic SQL％ROWCOUNT屬性不同，該屬性在查詢執(zhí)行完成時設(shè)置為0，并且僅在程序迭代查詢返回的結(jié)果集時才遞增。

如果SELECT查詢僅返回聚合函數(shù)，則每個FETCH都將設(shè)置％ROWCOUNT = 1。即使表中沒有數(shù)據(jù)，第一個FETCH始終以SQLCODE = 0來完成；任何后續(xù)的FETCH均以SQLCODE = 100完成，并設(shè)置％ROWCOUNT = 1。

以下嵌入式SQL示例聲明一個游標(biāo)，并使用FETCH來獲取表中的每一行。到達(dá)數(shù)據(jù)結(jié)尾（SQLCODE = 100）時，％ROWCOUNT包含已檢索的行數(shù)：

/// d ##class(PHA.TEST.SQL).ROWCOUNT()
ClassMethod ROWCOUNT()
{
    SET name="LastName,FirstName",state="##"
    &sql(DECLARE EmpCursor CURSOR FOR 
        SELECT Name, Home_State
        INTO :name,:state FROM Sample.Person
        WHERE Home_State %STARTSWITH 'M')
    WRITE !,"BEFORE: Name=",name," State=",state
    &sql(OPEN EmpCursor)
    QUIT:(SQLCODE'=0)
    FOR { 
        &sql(FETCH EmpCursor)
        QUIT:SQLCODE  
        WRITE !,"Row fetch count: ",%ROWCOUNT
        WRITE " Name=",name," State=",state
    }
    WRITE !,"最終提取SQLCODE: ",SQLCODE
    &sql(CLOSE EmpCursor)
    WRITE !,"AFTER: Name=",name," State=",state
    WRITE !,"提取的總行數(shù): ",%ROWCOUNT
}

DHC-APP>d ##class(PHA.TEST.SQL).ROWCOUNT()
 
BEFORE: Name=LastName,FirstName State=##
Row fetch count: 1 Name=O'Rielly,Chris H. State=MS
Row fetch count: 2 Name=Orwell,John V. State=MT
Row fetch count: 3 Name=Zevon,Heloisa O. State=MI
...
Row fetch count: 37 Name=Joyce,Elmo R. State=MO
Row fetch count: 38 Name=Jafari,Christine Z. State=MI
最終提取SQLCODE: 100
AFTER: Name=Jafari,Christine Z. State=OH
提取的總行數(shù): 38

以下嵌入式SQL示例執(zhí)行UPDATE并設(shè)置受更改影響的行數(shù)：

/// d ##class(PHA.TEST.SQL).ROWCOUNT1()
ClassMethod ROWCOUNT1()
{
    &sql(UPDATE Sample.Employee 
        SET Salary = (Salary * 1.1)
        WHERE Salary < 50000)
    IF SQLCODE<0 {
        WRITE "SQLCODE error ",SQLCODE," ",%msg  QUIT
        }
    WRITE "Employees: ", %ROWCOUNT,!
}

DHC-APP>d ##class(PHA.TEST.SQL).ROWCOUNT1()
Employees: 48

請記住，所有嵌入式SQL語句（在給定進(jìn)程內(nèi)）都會修改％ROWCOUNT變量。如需要％ROWCOUNT提供的值，請確保在執(zhí)行其他Embedded SQL語句之前獲取其值。根據(jù)嵌入式SQL的調(diào)用方式，可能必須在輸入嵌入式SQL之前新建％ROWCOUNT變量。

另請注意，顯式回滾事務(wù)不會影響％ROWCOUNT的值。例如，以下內(nèi)容將報告已進(jìn)行了更改，即使它們已經(jīng)滾動了。

/// d ##class(PHA.TEST.SQL).ROWCOUNT2()
ClassMethod ROWCOUNT2()
{
    TSTART // 開始事務(wù)
    NEW SQLCODE,%ROWCOUNT,%ROWID
    &sql(UPDATE Sample.Employee 
        SET Salary = (Salary * 1.1)
        WHERE Salary < 50000)
    IF SQLCODE<0 {
        WRITE "SQLCODE error ",SQLCODE," ",%msg  QUIT
    }
    TROLLBACK // 強制回滾;不會修改％rowcount
    Write "Employees: ", %ROWCOUNT,!
}

DHC-APP>d ##class(PHA.TEST.SQL).ROWCOUNT2()
Employees: 37

隱式事務(wù)（例如，如果UPDATE未通過約束檢查）由％ROWCOUNT反映。

%ROWID

初始化進(jìn)程時，未定義％ROWID。當(dāng)發(fā)出NEW ％ROWID命令時，％ROWID將重置為未定義。 ％ROWID由下面描述的嵌入式SQL操作設(shè)置。如果該操作不成功或成功完成，但未獲取或修改任何行，則％ROWID值與其先前值保持不變：未定義，或由先前的嵌入式SQL操作設(shè)置為某個值。因此，在每個嵌入式SQL操作之前，請務(wù)必新建％ROWID。

％ROWID設(shè)置為受以下操作影響的最后一行的RowID：

• INSERT，UPDATE，INSERT OR UPDATE或DELETE：單行操作后，％ROWID變量包含系統(tǒng)分配的RowID（對象ID）值，該值分配給插入，更新或刪除的記錄。經(jīng)過多行操作之后，％ROWID變量包含系統(tǒng)分配的最后一條插入，更新或刪除的記錄的RowID（對象ID）的值。如果未插入，更新或刪除任何記錄，則％ROWID變量值將保持不變。 TRUNCATE TABLE沒有設(shè)置％ROWID。
• 基于游標(biāo)的SELECT：DECLARE游標(biāo)名稱CURSOR和OPEN游標(biāo)名稱語句未初始化％ROWID； ％ROWID值與其先前值保持不變。第一個成功的FETCH設(shè)置％ROWID。隨后的每個獲取行的FETCH都會將％ROWID重置為當(dāng)前RowID值。如果FETCH檢索一行可更新游標(biāo)，則會設(shè)置％ROWID。可更新游標(biāo)是其中頂部FROM子句僅包含一個元素（單個表名或可更新視圖名）的游標(biāo)。如果游標(biāo)不可更新，則％ROWID保持不變。如果沒有行符合查詢選擇條件，則FETCH不會更改先前的％ROWID值（如果有）。 CLOSE時或FETCH發(fā)出SQLCODE 100（無數(shù)據(jù)或無更多數(shù)據(jù)）時，％ROWID包含檢索到的最后一行的RowID。

具有DISTINCT關(guān)鍵字或GROUP BY子句的基于游標(biāo)的SELECT不會設(shè)置％ROWID。 ％ROWID值與其先前的值（如果有）保持不變。

如果基于游標(biāo)的SELECT僅返回聚合函數(shù)值，則不會設(shè)置％ROWID。如果它同時返回字段值和聚合函數(shù)值，則將每個FETCH的％ROWID值設(shè)置為查詢返回的最后一行的RowID。

• 沒有聲明游標(biāo)的SELECT不會設(shè)置％ROWID。完成簡單的SELECT語句后，％ROWID值將保持不變。

在Dynamic SQL中，相應(yīng)的％ROWID屬性返回插入，更新或刪除的最后一條記錄的RowID值。執(zhí)行SELECT查詢時，Dynamic SQL不會返回％ROWID屬性值。

可以使用以下方法調(diào)用從ObjectScript中檢索當(dāng)前的％ROWID：

DHC-APP>  WRITE $SYSTEM.SQL.GetROWID()
213

在執(zhí)行INSERT，UPDATE，DELETE，TRUNCATE TABLE或基于游標(biāo)的SELECT操作之后，LAST_IDENTITY SQL函數(shù)將為最近修改的記錄返回IDENTITY字段的值。如果表沒有IDENTITY字段，則此函數(shù)返回最近修改記錄的RowID。

SQLCODE

運行嵌入式SQL查詢后，必須在處理輸出主機(jī)變量之前檢查SQLCODE。

如果SQLCODE = 0，則查詢成功完成并返回數(shù)據(jù)。輸出主機(jī)變量包含字段值。
如果SQLCODE = 100，則查詢成功完成，但是輸出主機(jī)變量值可能不同。任何一個：

• 查詢返回一個或多個數(shù)據(jù)行（SQLCODE = 0），然后到達(dá)數(shù)據(jù)的末尾（SQLCODE = 100），在這種情況下，輸出主機(jī)變量設(shè)置為返回的最后一行的字段值。 ％ROWCOUNT> 0。
• 查詢未返回任何數(shù)據(jù)，在這種情況下，輸出主機(jī)變量未定義。 ％ROWCOUNT = 0。

如果查詢僅返回聚合函數(shù)，則即使表中沒有數(shù)據(jù)，第一個FETCH也會始終以SQLCODE = 0和％ROWCOUNT = 1來完成。第二個FETCH以SQLCODE = 100和％ROWCOUNT = 1結(jié)束。如果表中沒有數(shù)據(jù)或沒有數(shù)據(jù)與查詢條件匹配，查詢將根據(jù)需要將輸出主機(jī)變量設(shè)置為0或空字符串。

如果SQLCODE為負(fù)數(shù)，則查詢失敗，并顯示錯誤條件。

根據(jù)嵌入式SQL的調(diào)用方式，可能必須在輸入嵌入式SQL之前新建SQLCODE變量。在觸發(fā)代碼中，將SQLCODE設(shè)置為非零值會自動將％ok = 0設(shè)置為中止并回滾觸發(fā)操作。

在動態(tài)SQL中，相應(yīng)的％SQLCODE屬性返回SQL錯誤代碼值。

$TLEVEL

事務(wù)級計數(shù)器。
InterSystems SQL將$TLEVEL初始化為0。
如果沒有當(dāng)前事務(wù)，$TLEVEL為0。

• 初始START TRANSACTION將$LEVEL設(shè)置為1。其他START TRANSACTION語句對$TLEVEL無效。
• 每個SAVEPOINT語句將$TLEVEL加1。
• ROLLBACK TO SAVEPOINT點名語句減少$TLEVEL。遞減量取決于指定的保存點。
• COMMIT將$LEVEL重置為0。
• ROLLBACK將$LEVEL重置為0。

還可以使用％INTRANSACTION語句來確定事務(wù)是否在進(jìn)行中。

$TLEVEL也由ObjectScript事務(wù)命令設(shè)置。

$USERNAME

SQL用戶名與InterSystems IRIS用戶名相同，存儲在ObjectScript $USERNAME特殊變量中。用戶名可以用作系統(tǒng)范圍的默認(rèn)架構(gòu)，也可以用作架構(gòu)搜索路徑中的元素。