#
第十二章 使用嵌入式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代码行。