#
第十二章 使用嵌入式SQL(一)
可以将SQL语句嵌入InterSystemsIRIS®数据平台使用的ObjectScript代码中。这些嵌入式SQL语句在运行时转换为优化的可执行代码。
嵌入式SQL有两种:
- 一个简单的嵌入式SQL查询只能返回单行中的值。简单嵌入式SQL还可以用于单行插入,更新和删除以及其他SQL操作。
- 基于游标的嵌入式SQL查询可以遍历查询结果集,并从多行中返回值。基于游标的嵌入式SQL也可以用于多行更新和删除SQL操作。
**注意:嵌入式SQL不能输入到`Terminal`命令行,也不能在`XECUTE`语句中指定。要从命令行执行SQL,请使用`$SYSTEM.SQL.Execute()`方法或SQL Shell接口。**
# 编译嵌入式SQL
当包含嵌入式SQL的例程被编译时,嵌入式SQL不会被编译。
相反,嵌入式SQL的编译发生在SQL代码的第一次执行(运行时)。
第一次执行定义了一个可执行的缓存查询。
这与动态SQL的编译类似,在动态SQL中,直到执行SQL Prepare操作才编译SQL代码。
直到第一次执行例程,嵌入式SQL代码才会根据SQL表和其他实体进行验证。
因此,可以编译包含嵌入式SQL的持久化类的例程或方法,这些SQL引用在例程编译时不存在的表或其他SQL实体。
由于这个原因,大多数SQL错误是在运行时执行时返回的,而不是编译时返回的。
**在例程编译时,对嵌入式SQL执行SQL语法检查。
ObjectScript编译器失败,并为嵌入式SQL中的无效SQL语法生成编译错误。**
可以使用Management Portal SQL接口测试嵌入式SQL中指定的SQL实体是否存在,而不需要执行SQL代码。
这在验证嵌入式SQL代码中进行了描述,该代码既验证SQL语法,又检查是否存在SQL实体。
可以选择在运行时执行之前验证嵌入式SQL代码,方法是使用`/compileembedded=1`限定符编译包含嵌入式SQL代码的例程,如验证嵌入式SQL代码中所述。
**成功执行的嵌入式SQL语句将生成一个缓存的查询。该嵌入式SQL的后续执行将使用缓存的查询,而不是重新编译嵌入式SQL源。这提供了对嵌入式SQL的缓存查询的性能优势。**
当首次使用`OPEN`命令打开游标时,会执行基于游标的Embedded SQL语句的运行时执行。在执行的这一点上,将生成优化的缓存查询计划,如管理门户中的“ SQL语句”列表中所示。列出的“ SQL语句”位置是包含嵌入式SQL代码的例程的名称。请注意,执行嵌入式SQL不会在“缓存的查询”列表中生成一个条目。这些清单(带有类名称,例如`%sqlcq.USER.cls1`)是由Dynamic SQL查询创建的。
**注意:较早版本的IRIS中使用的`#SQLCompile Mode`预处理程序语句已被弃用。它已被解析,但不再对大多数嵌入式SQL命令执行任何操作。无论`#SQLCompile Mode`设置如何,大多数嵌入式SQL命令都会在运行时进行编译。但是,设置`#SQLCompile Mode = deferred`对于少量的嵌入式SQL命令仍然有意义,因为它会强制在运行时编译所有类型的嵌入式SQL命令。**
## 嵌入式SQL和宏预处理器
可以在方法内和触发器内(前提是它们已定义为使用ObjectScript)或在ObjectScript MAC例程内使用嵌入式SQL。 MAC例程由InterSystems IRIS宏预处理器处理,并转换为INT(中间)代码,随后将其编译为可执行的OBJ代码。这些操作是在包含嵌入式SQL的例程的编译时执行的,而不是在嵌入式SQL代码本身上执行的,嵌入式SQL代码本身直到运行时才进行编译。
**如果嵌入式SQL语句本身包含InterSystems IRIS宏预处理器语句(#命令,`##函`数或`$$macro`引用),则在编译例程时将编译这些语句,并在运行时将其提供给SQL代码。**这可能会影响包含ObjectScript代码主体的`CREATE PROCEDURE`,`CREATE FUNCTION`,`CREATE METHOD`,`CREATE QUERY`或`CREATE TRIGGER`语句。
### 在嵌入式SQL中包含文件
嵌入式SQL语句要求它们引用的任何宏包含文件都必须在运行时加载到系统上。
因为嵌入式SQL的编译将推迟到首次引用之前进行,所以嵌入式SQL类的编译上下文将是运行时环境,而不是包含类或例程的编译时环境。如果运行时当前名称空间与包含例程的编译时名称空间不同,则编译时名称空间中的包含文件可能在运行时名称空间中不可见。在这种情况下,将发生以下情况:
1. 如果在运行时名称空间中看不到包含文件,则嵌入式SQL编译将删除所有包含文件。由于SQL编译很少需要包含文件,因此如果没有这些文件,运行时嵌入式SQL编译通常会成功。
2. 如果删除包含文件后编译失败,则InterSystems IRIS错误将报告例程编译时名称空间,嵌入式SQL运行时名称空间以及从运行时名称空间看不到的包含文件列表。
### #SQLCompile宏指令
宏预处理器提供了三个与嵌入式SQL一起使用的预处理器指令:
- `#SQLCompile Select`指定从`Select`语句返回时数据显示的格式,或者指定插入或更新语句时数据输入所需的格式,或者指定Select输入主机变量。
它支持以下6个选项:`Logical(默认值)`、`Display`、`ODBC`、`Runtime`、`Text(与Display相同)`和`FDBMS`(见下文)。
如果`#SQLCompile Select=Runtime`,可以使用`$SYSTEM.SQL.Util.SetOption("SelectMode",n)`方法来更改数据的显示方式。
`n`取值为`0=Logical`、`1=ODBC`、`2=Display`。
无论指定了`#SQLCompile Select`选项,`INSERT`或`UPDATE`都会自动将指定的数据值转换为相应的逻辑格式进行存储。
不管指定了`#SQLCompile Select`选项,`Select`都会自动将输入的主机变量值转换为谓词匹配的相应逻辑格式。
使用`#SQLCompile Select`进行查询显示如下示例所示。
这些示例显示`DOB`(出生日期)值,然后将`SelectMode`更改为`ODBC`格式,然后再次显示`DOB`。
在第一个例子中,改变`SelectMode`对显示没有影响;
在第二个示例中,因为`#SQLCompile Select=Runtime`,更改`SelectMode`将更改显示:
```java
/// d ##class(PHA.TEST.SQL).EmbedSQL()
ClassMethod EmbedSQL()
{
#SQLCompile Select=Display
&sql(SELECT DOB INTO :a FROM Sample.Person)
IF SQLCODE<0 {
WRITE "SQLCODE error ",SQLCODE," ",%msg
QUIT
} ELSEIF SQLCODE=100 {
WRITE "Query returns no results"
QUIT
}
WRITE "1st date of birth is ",a,!
DO $SYSTEM.SQL.Util.SetOption("SelectMode",1)
WRITE "changed select mode to: ",$SYSTEM.SQL.Util.GetOption("SelectMode"),!
&sql(SELECT DOB INTO :b FROM Sample.Person)
WRITE "2nd date of birth is ",b
}
```
```java
DHC-APP>d ##class(PHA.TEST.SQL).EmbedSQL()
1st date of birth is 04/25/1990
2nd date of birth is 04/25/1990
```
```java
/// d ##class(PHA.TEST.SQL).EmbedSQL1()
ClassMethod EmbedSQL1()
{
#SQLCompile Select=Runtime
&sql(SELECT DOB INTO :a FROM Sample.Person)
IF SQLCODE<0 {WRITE "SQLCODE error ",SQLCODE," ",%msg QUIT}
ELSEIF SQLCODE=100 {WRITE "Query returns no results" QUIT}
WRITE "1st date of birth is ",a,!
//DO $SYSTEM.SQL.Util.SetOption("SelectMode",1)
//WRITE "changed select mode to: ",$SYSTEM.SQL.Util.GetOption("SelectMode"),!
&sql(SELECT DOB INTO :b FROM Sample.Person)
WRITE "2nd date of birth is ",b
}
```
```java
DHC-APP>d ##class(PHA.TEST.SQL).EmbedSQL1()
1st date of birth is 1990-04-25
2nd date of birth is 1990-04-25
```
- 提供`#SQLCompile Select=FDBMS`是为了使嵌入式SQL能够以与`FDBMS`相同的方式格式化数据。
如果一个查询在`WHERE`子句中有一个常量值,`FDBMS`模式假定它是一个显示值,并使用`DisplayToLogical`转换对它进行转换。
如果一个查询在`WHERE`子句中有一个变量,`FDBMS`模式使用`FDBMSToLogical conversion`对它进行转换。
应该设计`FDBMS`转换方法来处理三种`FDBMS`变量格式:`Internal`、`Internal_$c(1)_External`和`$c(1)_External`。
如果查询选择一个变量,它将调用`LogicalToFDBMS`转换方法。
这个方法返回`Internal_$c(1)_External`。
- `#SQLCompile Path(或#Import)`指定模式搜索路径,用于解析`SELECT`、`CALL`、`INSERT`、`UPDATE`、`DELETE`和`TRUNCATE`表等数据管理命令中未限定的表、视图和存储过程名称。
如果没有指定模式搜索路径,或者在指定的模式中找不到表,InterSystems IRIS将使用默认模式。
数据定义语句如`ALTER TABLE`、`DROP VIEW`、`CREATE INDEX`或`CREATE TRIGGER`会忽略`#SQLCompile Path`和`#Import`。
数据定义语句使用默认模式来解析非限定名称。
- `#SQLCompile Audit`计是一个布尔开关,指定嵌入式SQL语句的执行是否应该记录在系统事件审计日志中。
# 嵌入式SQL语法
## `&sql`指令
嵌入式SQL语句由`&sql()`指令与其余代码分开,如以下示例所示:
```java
/// d ##class(PHA.TEST.SQL).EmbedSQL2()
ClassMethod EmbedSQL2()
{
NEW SQLCODE,a
WRITE "调用嵌入式SQL",!
&sql(SELECT Name INTO :a FROM Sample.Person)
IF SQLCODE<0 {
WRITE "SQLCODE错误 ",SQLCODE," ",%msg QUIT
} ELSEIF SQLCODE=100 {
WRITE "查询没有结果" QUIT
}
WRITE "名字是 ",a
}
```
```java
DHC-APP>d ##class(PHA.TEST.SQL).EmbedSQL2()
调用嵌入式SQL
名字是 Adams,Diane F.
```
使用指定一个或多个主机变量的`INTO`子句返回结果。在这种情况下,主机变量名为:`a`。
**`&sql`指令不区分大小写;可以使用`&sql`,`&SQL`,`&Sql`等。 `&sql`指令必须后跟一个开放的括号,并且中间没有空格,换行符或注释。** `&sql`指令可以与标签在同一行上使用,如以下示例所示:
```java
/// d ##class(PHA.TEST.SQL).EmbedSQL3()
ClassMethod EmbedSQL3()
{
Mylabel &sql(
SELECT Name INTO :a
FROM Sample.Person
)
}
```
**`&sql`指令的主体应包含一个有效的Embedded SQL语句,并用括号括起来。可以按照自己喜欢的任何方式设置SQL语句的格式:SQL会忽略空格和换行符。 Studio可以识别`&sql`指令,并使用可识别SQL的着色器对SQL代码语句进行语法着色。**
**当宏预处理器遇到`&sql`指令时,它将随附的SQL语句交给SQL查询处理器。查询处理器返回执行查询所需的代码(ObjectScript INT格式)。然后,宏预处理器用此代码(或对包含该代码的标签的调用)替换`&sql`指令。在Studio中,可以根据需要查看生成的代码,方法是查看为类或例程生成的INT代码(使用“查看”菜单中的“查看其他代码”选项)。**
如果`&sql`指令包含无效的Embedded SQL语句,则宏预处理器会生成编译错误。无效的SQL语句可能具有语法错误,或者引用了在编译时不存在的表或列。
`&sql`指令可以在括号内的任何位置包含SQL样式的注释,可以不包含SQL代码,或仅包含注释文本。如果`&sql`指令不包含SQL代码或仅包含注释文本,则将该指令解析为无操作,并且未定义`SQLCODE变量`。
```java
NEW SQLCODE
WRITE !,"Entering Embedded SQL"
&sql()
WRITE !,"Leaving Embedded SQL"
```
```java
NEW SQLCODE
WRITE !,"Entering Embedded SQL"
&sql(/* SELECT Name INTO :a FROM Sample.Person */)
WRITE !,"Leaving Embedded SQL"
```
## &sql替代语法
由于复杂的嵌入式SQL程序可能包含多个`&sql`指令(包括嵌套的`&sql`指令),因此提供了以下替代语法格式:
- `## sql(...)`:此指令在功能上等同于`&sql`。它提供了另一种语法来使代码清晰。但是,它不能包含标记语法。
- `&sql (...)`:此伪指令允许指定多个`&sql`伪指令,并使用用户选择的标记字符或字符串标识每个伪伪指令。下一节将介绍此标记语法。
## &sql标记语法
可以使用用户定义的标记语法来标识特定的`&sql`指令。该语法由在`“&sql”`和右括号之间指定的字符或字符串组成。在嵌入式SQL的结尾处,在右括号后必须立即显示此标记的相反内容。语法如下:
```java
&sql( SQL statement )
```
请注意,在`&sql`,标记和右括号之间不允许有空格(空格,制表符或行返回),并且在右括号和反向标记之间不允许有空格。
**标记可以是单个字符或一系列字符。标记不能包含以下标点符号:**
```java
( + - / \ | * )
```
标记不能包含空格字符(空格,制表符或换行符)。它可能包含所有其他可打印字符和字符组合,包括Unicode字符。标记和反向标记区分大小写。
**相应的反向标记必须包含与反向标记相同的字符。例如:`&sqlABC(...)CBA`。** 如果标记包含[或{字符,则反向标记必须包含相应的]或}字符。以下是有效的`&sql`标记和反向标记对的示例:
```java
&sql@@( ... )@@
&sql[( ... )]
&sqltest( ... )tset
&sql[Aa{( ... )}aA]
```
选择标记字符或字符串时,请注意以下重要的SQL限制:SQL代码不能在代码中的任何位置(包括文字字符串和注释)包含字符序列`“)”`。例如,如果标记“ABC,则字符串`“)CBA”`不能出现在嵌入式SQL代码中的任何位置。如果发生这种情况,有效标记和有效SQL代码的组合将使编译失败。因此,在选择标记字符或字符串时要格外小心,以防止发生这种冲突,这一点很重要。
## 嵌入式SQL和行偏移量
嵌入式SQL的存在会影响ObjectScript行偏移量,如下所示:
- 嵌入式SQL在例程中的该点处将INT代码行的总数加(至少)2。因此,嵌入式SQL的单行计为3行,嵌入式SQL的两行计为4行,依此类推。调用其他代码的嵌入式SQL可以向INT代码添加更多行。
一个虚拟的嵌入式SQL语句,仅包含一个注释,算作2条INT代码行,如以下示例所示:`&sql(/ *供将来使用* /)`。
- 嵌入式SQL中的所有行都计为行偏移,包括注释和空白行。
可以使用`^ROUTINE`全局显示INT代码行。