文章
姚 鑫 · 十月 20, 2021
# 第五十一章 SQL命令 HAVING（二）
## In和%INLIST谓词
`IN`谓词用于将值与一系列非结构化的项进行匹配。
`%INLIST`谓词是 IRIS扩展，用于将值与列表结构的元素进行匹配。
使用任一谓词，都可以执行相等比较和子查询比较。
在中有两种格式。第一个用作使用与`OR运`算符链接在一起的多个相等比较的速记。例如：
```sql
SELECT Name, Home_State FROM Sample.Person
GROUP BY Home_State
HAVING Home_State IN ('ME','NH','VT','MA','RI','CT')
```
如果`Home_State`等于括号列表中的任意值，则计算为`TRUE`。列表元素可以是常量或表达式。排序规则适用于IN比较，因为它适用于相等性测试。默认情况下，`IN`比较使用字段定义的排序规则类型；默认情况下，字符串字段定义为`SQLUPPER`，不区分大小写。
当日期或时间用于IN谓词相等比较时，会自动执行适当的数据类型转换。如果`HAVING`子句字段是`TIMESTAMP`类型，则`DATE`或`TIME`类型的值将转换为`TIMESTAMP`。如果`HAVING`子句字段为`DATE`类型，则`TIMESTAMP`或`STRING`类型的值将转换为`DATE`。如果`HAVING`子句字段为TIME类型，则`TIMESTAMP`或`STRING`类型的值将转换为`TIME`。
下面的示例都执行相同的相等比较并返回相同的数据。
`groupby`字段指定对于每个成功的相等比较只返回一条记录。
`DOB`字段的数据类型为`Date`:
```sql
SELECT Name,DOB FROM Sample.Person
GROUP BY DOB
HAVING DOB IN ({d '2014-01-02'},{d '1990-04-25'})
```
```sql
SELECT Name,DOB FROM Sample.Person
GROUP BY DOB
HAVING DOB IN ({ts '2014-01-02 00:00:00'},{ts '1990-04-25 00:00:00'})
```
`%INLIST`谓词可用于对列表结构的元素执行相等比较。
`%INLIST`使用`EXACT`排序。
因此，默认情况下，`%INLIST`字符串比较是区分大小写的。
下面的例子使用`%INLIST`来匹配一个字符串值到`FavoriteColors`列表字段的元素:
```sql
SELECT Name,FavoriteColors FROM Sample.Person
HAVING 'Red' %INLIST FavoriteColors
```
它返回`FavoriteColors`中包含元素`“Red”`的所有记录。
下面的嵌入式SQL示例将`Home_State`列值与`northne`(新英格兰北部各州)列表中的元素匹配:
```java
ClassMethod Having()
{
s northne = $lb("VT","NH","ME")
&sql(
DECLARE StateCursor CURSOR FOR
SELECT Name,Home_State
INTO :name,:state FROM Sample.Person
HAVING Home_State %INLIST :northne
)
&sql(OPEN StateCursor)
q:(SQLCODE'=0)
n %ROWCOUNT,%ROWID
for {
&sql(FETCH StateCursor)
q:SQLCODE
w !,"#",%ROWCOUNT," Name=",name," State=",state,!
}
w !,"Final Fetch SQLCODE: ",SQLCODE
&sql(CLOSE StateCursor)
}
```
```java
DHC-APP>d ##class(PHA.TEST.SQLCommand).Having()
#1 Name=Lepon,Jeff Z. State=NH
#2 Name=Ingleman,Terry A. State=NH
#3 Name=Jung,Keith W. State=NH
#4 Name=Xiang,Kirsten U. State=ME
#5 Name=Jackson,Ralph V. State=VT
#6 Name=Tesla,Geoffrey O. State=NH
```
还可以在子查询中使用`IN`或`%INLIST`来测试列值(或任何其他表达式)是否等于任何子查询行值。
例如:
```sql
SELECT Name,Home_State FROM Sample.Person
HAVING Name IN
(SELECT Name FROM Sample.Employee
HAVING Salary < 50000)
```
注意，子查询在`SELECT`列表中必须只有一个项。
## %STARTSWITH谓词
IRIS `%STARTSWITH`比较操作符允许对字符串或数字的初始字符执行部分匹配。
下面的示例使用`%STARTSWITH`。
它根据年龄进行选择，然后为每个以“S”开头的Name返回一条记录:
```sql
SELECT Name,Age FROM Sample.Person
WHERE Age > 30
HAVING Name %STARTSWITH 'S'
ORDER BY Name
```
与其他字符串字段比较一样，`%STARTSWITH`比较不区分大小写。
## Contains Operator ([)
`Contains`操作符是左括号符号:`[`。
它允许将子字符串(字符串或数字)匹配到字段值的任何部分。
比较总是区分大小写的。
下面的例子在`HAVING`子句中使用`Contains`操作符选择那些`Home_State`值包含`“K”`的记录，然后对这些状态执行`%AFTERHAVING`计数:
```sql
SELECT Home_State,COUNT(Home_State) AS States,
COUNT(Home_State %AFTERHAVING) AS KStates
FROM Sample.Person
HAVING Home_State [ 'K'
```
## FOR SOME谓词
`HAVING`子句的`FOR SOME`谓词决定是否根据一个或多个字段值的条件测试返回结果集。
该谓词的语法如下:
```
FOR SOME (table[AS t-alias]) (fieldcondition)
```
`FOR SOME`指定字段`condition`的值必须为`true`;
至少有一个字段值必须匹配指定的条件。
`Table`可以是单个表，也可以是逗号分隔的表列表，也可以是表别名。
`Fieldcondition`为指定表中的一个或多个字段指定一个或多个条件。
`table`参数和字段`condition`参数都必须用括号分隔。
下面的例子展示了`FOR SOME`谓词的用法:
```sql
SELECT Name,Age
FROM Sample.Person
HAVING FOR SOME (Sample.Person)(Age>20)
ORDER BY Age
```
在上面的示例中，如果至少有一个字段包含大于`20`的`Age`值，则返回所有记录。
否则，不返回任何记录。
## NULL 谓词
这将检测未定义的值。
你可以检测所有空值，或所有非空值:
```sql
SELECT Name, FavoriteColors FROM Sample.Person
HAVING FavoriteColors IS NULL
```
```sql
SELECT Name, FavoriteColors FROM Sample.Person
HAVING FavoriteColors IS NOT NULL
ORDER BY FavoriteColors
```
使用`GROUP BY`子句，可以为指定字段的每个非空值返回一条记录:
```sql
SELECT Name, FavoriteColors FROM Sample.Person
GROUP BY FavoriteColors
HAVING FavoriteColors IS NOT NULL
ORDER BY FavoriteColors
```
## EXISTS 谓词
它使用子查询来测试子查询是否计算为空集。
```sql
SELECT t1.disease FROM illness_tab t1 WHERE EXISTS
(SELECT t2.disease FROM disease_registry t2
WHERE t1.disease = t2.disease
HAVING COUNT(t2.disease) > 100)
```
## LIKE、%MATCHES和%PATTERN谓词
这三个谓词允许执行模式匹配。
- `LIKE`允许使用文字和通配符进行模式匹配。
当希望返回包含已知字面值子字符串的数据值，或在已知序列中包含多个已知子字符串时，请使用`LIKE`。
`LIKE`使用目标的排序规则进行字母大小写比较。
- `%MATCHES`允许使用文字、通配符、列表和范围进行模式匹配。
当希望返回包含已知字面值子字符串的数据值，或包含一个或多个位于可能字符列表或范围内的字面值字符，或在已知序列中包含多个这样的子字符串时，请使用`%MATCHES`。
`%MATCHES`使用`EXACT`排序法进行字母大小写比较。
- 允许指定字符类型的模式。
例如,`'1U4L1",".A'`(`1`个大写字母，`4`个小写字母，一个逗号，后面跟着任意数量的字母字符)。
如果希望返回包含已知字符类型序列的数据值，请使用`%PATTERN`。
当数据值不重要，但这些值的字符类型格式很重要时，`%PATTERN`特别有用。
`PATTERN`还可以指定已知的文字字符。
它使用`EXACT`排序法进行文字比较，这总是区分大小写的。
要与字符串的第一个字符进行比较，请使用`%STARTSWITH`谓词。
# 示例
下面的示例为每个至少有一个`21`岁以下的人的州返回一行。
对于每一行，它返回该州所有人的平均、最小和最大年龄。
```sql
SELECT Home_State, MIN(Age) AS Youngest,
AVG(Age) AS AvgAge, MAX(Age) AS Oldest
FROM Sample.Person
GROUP BY Home_State
HAVING Age < 21
ORDER BY Youngest
```
下面的示例为每个至少有一个`21`岁以下的人的州返回一行。
对于每一行，它返回该州所有人的平均、最小和最大年龄。
使用`%AFTERHAVING`关键字，它还返回该州`21`岁以下的人的平均年龄(`AvgYouth`)，以及该州`21`岁以下最年长的人的年龄(`OldestYouth`)。
```sql
SELECT Home_State,AVG(Age) AS AvgAge,
AVG(Age %AFTERHAVING) AS AvgYouth,
MIN(Age) AS Youngest, MAX(Age) AS Oldest,
MAX(Age %AFTERHAVING) AS OldestYouth
FROM Sample.Person
GROUP BY Home_State
HAVING Age < 21
ORDER BY AvgAge
```
文章
姚 鑫 · 三月 29, 2021
# 第十三章 使用动态SQL（七）
# SQL元数据
动态SQL提供以下类型的元数据：
- 在“准备”之后，描述查询类型的元数据。
- 在“准备”之后，描述查询中选择项的元数据（“列”和“扩展列信息”）。
- 在准备之后，描述查询参数的元数据：参数，`：var`参数和常量。 （语句参数，形式参数和对象）
- 执行之后，描述查询结果集的元数据。在执行Prepare操作（`％Prepare（）`，`％PrepareClassQuery（）`或`％ExecDirect（）`）之后，可以使用`％SQL.StatementMetadata`属性值。
- 可以直接为最新的`％Prepare（）`返回`％SQL.Statement`元数据属性。
- 可以返回包含`％SQL.StatementMetadata`属性的oref的`％SQL.Statement％Metadata`属性。这使可以返回多个准备操作的元数据。
`SELECT`或`CALL`语句返回所有这些元数据。 `INSERT`，`UPDATE`或`DELETE`返回语句类型元数据和形式参数。
## 语句类型元数据
使用`％SQL.Statement`类进行`Prepare`之后，可以使用`％SQL.StatementMetadata statementType`属性来确定准备哪种类型的SQL语句，如以下示例所示。本示例使用`％SQL.Statement％Metadata`属性来保存和比较两`个Prepare`操作的元数据：
```java
/// d ##class(PHA.TEST.SQL).MetaData()
ClassMethod MetaData()
{
SET tStatement = ##class(%SQL.Statement).%New()
SET myquery1 = "SELECT TOP ? Name,Age,AVG(Age),CURRENT_DATE FROM Sample.Person"
SET myquery2 = "CALL Sample.SP_Sample_By_Name(?)"
SET qStatus = tStatement.%Prepare(myquery1)
IF qStatus'=1 {
WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT
}
SET meta1 = tStatement.%Metadata
SET qStatus = tStatement.%Prepare(myquery2)
IF qStatus'=1 {
WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT
}
SET meta2 = tStatement.%Metadata
WRITE "语句类型query 1: ",meta1.statementType,!
WRITE "语句类型query 2: ",meta2.statementType,!
WRITE "End of metadata"
}
```
```java
DHC-APP>d ##class(PHA.TEST.SQL).MetaData()
语句类型query 1: 1
语句类型query 2: 45
End of metadata
```
`statementType`属性的“类引用”条目列出了语句类型整数代码。最常见的代码是1（`SELECT`查询）和45（`CALL`到存储的查询）。
可以使用`％GetImplementationDetails（）`实例方法返回相同的信息，如成功准备的结果中所述。
执行查询后，可以从结果集中返回语句类型名称（例如`SELECT`）。
## 选择项目Select-item元数据
使用`％SQL.Statement`类准备`SELECT`或`CALL`语句之后，可以通过显示所有元数据或指定各个元数据项来返回有关查询中指定的每个选择项列的元数据。此列元数据包括ODBC数据类型信息，以及客户端类型和InterSystems Objects属性的起源以及类类型信息。
以下示例返回最近准备的查询中指定的列数：
```java
/// d ##class(PHA.TEST.SQL).MetaData1()
ClassMethod MetaData1()
{
SET myquery = "SELECT %ID AS id,Name,DOB,Age,AVG(Age),CURRENT_DATE,Home_State FROM Sample.Person"
SET tStatement = ##class(%SQL.Statement).%New()
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
WRITE "Number of columns=",tStatement.%Metadata.columnCount,!
WRITE "End of metadata"
}
```
```java
DHC-APP>d ##class(PHA.TEST.SQL).MetaData1()
Number of columns=7
End of metadata
```
以下示例返回列名称（或列别名），ODBC数据类型，最大数据长度（精度），以及每个`SELECT`项目字段的比例：
```java
/// d ##class(PHA.TEST.SQL).MetaData2()
ClassMethod MetaData2()
{
SET $NAMESPACE="SAMPLES"
SET myquery=2
SET myquery(1)="SELECT Name AS VendorName,LastPayDate,MinPayment,NetDays,"
SET myquery(2)="AVG(MinPayment),$HOROLOG,%TABLENAME FROM Sample.Vendor"
SET rset = ##class(%SQL.Statement).%New()
SET qStatus = rset.%Prepare(.myquery)
IF qStatus'=1 {
WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT
}
SET x=rset.%Metadata.columns.Count()
SET x=1
WHILE rset.%Metadata.columns.GetAt(x) {
SET column=rset.%Metadata.columns.GetAt(x)
WRITE !,x," ",column.colName," 是数据类型 ",column.ODBCType
WRITE " 大小为 ",column.precision," 规模 = ",column.scale
SET x=x+1
}
WRITE !,"End of metadata"
}
```
```java
DHC-APP>d ##class(PHA.TEST.SQL).MetaData2()
1 VendorName 是数据类型 12 大小为 50 规模 = 0
2 LastPayDate 是数据类型 9 大小为 10 规模 = 0
3 MinPayment 是数据类型 8 大小为 6 规模 = 0
4 NetDays 是数据类型 4 大小为 3 规模 = 0
5 Aggregate_5 是数据类型 8 大小为 20 规模 = 0
6 Expression_6 是数据类型 12 大小为 255 规模 = 0
7 Literal_7 是数据类型 12 大小为 13 规模 = 0
End of metadata
```
下面的示例使用`％SQL.StatementMetadata％Display（）`实例方法显示所有列元数据：
```java
/// d ##class(PHA.TEST.SQL).MetaData3()
ClassMethod MetaData3()
{
SET tStatement = ##class(%SQL.Statement).%New()
SET qStatus = tStatement.%Prepare("SELECT %ID AS id,Name,DOB,Age,AVG(Age),CURRENT_DATE,Home_State FROM Sample.Person")
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
DO tStatement.%Metadata.%Display()
WRITE !,"End of metadata"
}
```
```java
DHC-APP>d ##class(PHA.TEST.SQL).MetaData3()
Columns (SQLRESULTCOL, property 'columns'):
Column Name Type Prec Scale Null Label Table Schema CType
----------- ---- ---- ----- ---- ------------ ------------ ------------ -----
id 4 10 0 0 id Person Sample 5
Name 12 50 0 0 Name Person Sample 10
DOB 9 10 0 1 DOB Person Sample 2
Age 4 10 0 1 Age Person Sample 5
Aggregate_5 2 20 8 1 Aggregate_5 14
Expression_6 9 11 0 2 Expression_6 2
Home_State 12 2 0 1 Home_State Person Sample 10
Extended Column Info (SQLRESULTCOL)
Flags: 1:AutoIncrement,2:CaseSensitive,3:Currency,4:ReadOnly,5:RowVersion,
6:Unique,7:Aliased,8:Expression,9:Hidden,10:Identity,11:KeyColumn,
12:RowId
Column Name Linked Prop Type Class Flags
------------ --------------------- --------------------- -----------------------
id Sample.Person Y,N,N,Y,N,Y,Y,N,N,Y,Y,Y
Name Sample.Person.Name %Library.String N,N,N,N,N,N,N,N,N,N,N,N
DOB Sample.Person.DOB %Library.Date N,N,N,N,N,N,N,N,N,N,N,N
Age Sample.Person.Age %Library.Integer N,N,N,N,N,N,N,N,N,N,N,N
Aggregate_5 %Library.Numeric N,N,N,Y,N,N,Y,N,N,N,N,N
Expression_6 %Library.Date N,N,N,Y,N,N,Y,Y,N,N,N,N
Home_State Sample.Address.State
%Library.String N,N,N,N,N,N,N,N,N,N,N,N
Statement Parameters (property 'parameters'):
Nbr. Type precision scale nullable colName columntype
---- ---- --------- ----- -------- ------------ ----------
Formal Parameters (property 'formalParameters'):
Nbr. Type precision scale nullable colName columntype
---- ---- --------- ----- -------- ------------ ----------
Objects:
Col Column Name Extent ExportCall
--- ----------- ----------------- -----------------------------
1 id Sample.Person ##class(Sample.Person).%SQLQuickLoad
```
这将返回所选字段的两个表列表。第一列元数据表列出了列定义信息：
显示标题 | `%SQL.StatementColumn`属性 | 描述
---|---|---
Column Name | colName |列的SQL名称。如果为该列提供了别名，则会在此处列出该列的别名，而不是字段名称。名称和别名将被截断为12个字符。对于表达式，聚合，文字，主机变量或子查询，列出了分配的`“ Expression_n”`，`“ Aggregate_n”`，`“ Literal_n”`，`“ HostVar_n”`或`“ Subquery_n”`标签（`n`为`SELECT`项序列号）。如果为表达式，聚合，文字，主机变量或子查询分配了别名，则在此处列出该别名。
Type| ODBCType |ODBC数据类型的整数代码。请注意，这些ODBC数据类型代码与CType数据类型代码不同。
Prec| precision|精度或最大长度（以字符为单位）。日期，时间，PosixTime和TimeStamp数据类型中描述了TIME数据类型的精度和小数位元数据。
Scale| scale| 小数位数的最大数目。对于整数或非数值返回0。日期，时间，PosixTime和TimeStamp数据类型中描述了`TIME`数据类型的精度和小数位元数据。
Null| isNullable| 一个整数值，指示是否将列定义为`Non-NULL（0）`，或者是否允许`NULL（1）`。 RowID返回0。如果`SELECT`项是可能导致`NULL`的聚合或子查询，或者如果它指定`NULL`文字，则该项设置为1。如果`SELECT`项是表达式或主机变量，则设置此项到2（无法确定）。
Label| label| 列名或列别名（与列名相同）。
Table| tableName| SQL表名称。即使为表指定了别名，也始终在此处列出实际的表名。如果`SELECT`项是表达式或聚合，则不会列出任何表名。如果`SELECT`项是子查询，则列出子查询表名称。
Schema| schemaName|表的架构名称。如果未指定架构名称，则返回系统范围的默认架构。如果`SELECT`项是表达式或聚合，则不会列出任何模式名称。如果SELECT项是子查询，则不会列出任何架构名称。
CType| clientType| 客户端数据类型的整数代码。
第二列元数据表列出了扩展列信息。扩展列信息表列出了具有十二个布尔标志（SQLRESULTCOL）的每一列，这些标志被指定为Y（是）或N（否）：
显示标题 | `%SQL.StatementColumn`属性 | 描述
---|---|---
1: AutoIncrement| isAutoIncrement| TRowID和IDENTITY字段返回Y。
2: CaseSensitive| isCaseSensitive |具有`％EXACT`归类的字符串数据类型字段返回Y。引用`％SerialObject`嵌入式对象的属性返回Y。
3: Currency| isCurrency| 使用％Library.Currency数据类型定义的字段，例如`MONEY`数据类型。
4: ReadOnly |isReadOnly|表达式，聚合，文字，`HostVar`或子查询返回Y。RowID，IDENTITY和RowVersion字段返回Y。
5: RowVersion| isRowVersion|RowVersion字段返回Y。
6: Unique| isUnique| 定义为具有唯一值约束的字段。 RowID和IDENTITY字段返回Y。
7: Aliased| isAliased| 系统为非字段选择项提供别名。因此，无论用户是否通过指定列别名替换了系统别名，表达式，聚合，文字，HostVar或子查询都将返回Y。此标志不受用户指定的列别名的影响。
8: Expression| isExpression| 表达式返回Y。
9: Hidden| isHidden| 如果使用`％PUBLICROWID`或`SqlRowIdPrivate = 0`（默认值）定义表，则RowID字段返回N。否则，RowID字段返回Y。引用`％SerialObject`嵌入式对象的属性返回Y。
10: Identity| isIdentity| 定义为IDENTITY字段的字段返回Y。如果未隐藏RowID，则RowID字段返回Y。
11: KeyColumn| isKeyColumn| 定义为主键字段或外键约束目标的字段。 RowID字段返回Y。
12: RowID| isRowId |ROWID和Identity字段返回Y.
扩展列信息元数据表列出了每个选定字段的列名称（SQL名称或列别名），链接属性（链接的持久性类属性）和类型类（数据类型类）。请注意，链接属性列出了持久性类名（不是SQL表名）和属性名（不是列别名）。
- 对于普通表字段（`SELECT Name FROM Sample.Person`）： `Linked Prop=Sample.Person.Name, Type Class=%Library.String`.
- 对于表格的RowID (`SELECT %ID FROM Sample.Person`): `Linked Prop= [none], Type Class=Sample.Person`.
- 对于表达式，聚合，文字，`HostVar`或子查询 (`SELECT COUNT(Name) FROM Sample.Person`): `Linked Prop= [none], Type Class=%Library.BigInt`.
- 供参考`％Serial Object`嵌入式对象属性 (`SELECT Home_State FROM Sample.Person`). `Linked Prop=Sample.Address.State, Type Class=%Library.String.`
- 对于引用`％SerialObject`嵌入式对象的字段(`SELECT Home FROM Sample.Person`). `Linked Prop=Sample.Person.Home, Type Class=Sample.Address`.
在此示例中，`Sample.Person`中的`Home_State`字段引用`％SerialObject`类`Sample.Address`的`State`属性。
下面的示例返回带有一个形式参数（也就是语句参数）的被调用存储过程的元数据：
```java
/// d ##class(PHA.TEST.SQL).MetaData4()
ClassMethod MetaData4()
{
SET $NAMESPACE="SAMPLES"
SET mysql = "CALL Sample.SP_Sample_By_Name(?)"
SET tStatement = ##class(%SQL.Statement).%New()
SET qStatus = tStatement.%Prepare(.mysql)
IF qStatus'=1 {
WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT
}
DO tStatement.%Metadata.%Display()
WRITE !,"End of metadata"
}
```
它不仅返回列（字段）信息，还返回语句参数，形式参数和对象的值。
以下示例返回具有三个形式参数的的元数据。这三个参数之一用问号（`？`）指定，使其成为语句参数：
```java
/// d ##class(PHA.TEST.SQL).MetaData5()
ClassMethod MetaData5()
{
SET $NAMESPACE="SAMPLES"
SET mycall = "CALL personsets(?,'MA')"
SET tStatement = ##class(%SQL.Statement).%New(0,"sample")
SET qStatus = tStatement.%Prepare(mycall)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
DO tStatement.%Metadata.%Display()
WRITE !,"End of metadata"
}
```
```java
DHC-APP>d ##class(PHA.TEST.SQL).MetaData5()
Columns (SQLRESULTCOL, property 'columns'):
Column Name Type Prec Scale Null Label Table Schema CType
----------- ---- ---- ----- ---- ------------ ------------ ------------ -----
Extended Column Info (SQLRESULTCOL)
Flags: 1:AutoIncrement,2:CaseSensitive,3:Currency,4:ReadOnly,5:RowVersion,
6:Unique,7:Aliased,8:Expression,9:Hidden,10:Identity,11:KeyColumn,
12:RowId
Column Name Linked Prop Type Class Flags
------------ --------------------- --------------------- -----------------------
Statement Parameters (property 'parameters'):
Nbr. Type precision scale nullable colName columntype
---- ---- --------- ----- -------- ------------ ----------
1 12 50 0 2 name 1
Formal Parameters (property 'formalParameters'):
Nbr. Type precision scale nullable colName columntype
---- ---- --------- ----- -------- ------------ ----------
1 4 4 0 2 _isc_sp_ret_val 5
2 12 50 0 2 name 1
3 12 50 0 2 state 1
Objects:
Col Column Name Extent ExportCall
--- ----------- ----------------- -----------------------------
End of metadata
```
请注意，此元数据不返回任何列信息，但是“语句参数”，“形式参数”列表包含列名称和数据类型。
## Query参数元数据
使用`％SQL.Statement`类进行`Prepare`之后，您可以返回有关查询参数的元数据：输入参数（指定为问号（`？`）），输入主机变量（指定为`：varname`）和常量（文字值）。可以返回以下元数据：
- `？`参数`：parameterCount`属性
- ODBC数据类型为`？`参数`：％SQL.StatementMetadata％Display（）`实例方法“语句参数”列表。
- ？，v（：var）和c（常量）参数的列表：`％GetImplementationDetails（）`实例方法，如成功准备的结果中所述。
- ？，v（：var）和c（常量）参数的ODBC数据类型：`formalParameters`属性。
`％SQL.StatementMetadata％Display（）`实例方法“形式参数”列表。
- 查询文本，其中显示以下参数：`％GetImplementationDetails（）`实例方法，如成功准备结果中所述。
语句元数据`％Display（）`方法列出了“语句参数”和“形式参数”。对于每个参数，它列出了顺序参数号，ODBC数据类型，精度，小数位数，该参数是否可为空（2表示始终提供一个值）及其对应的属性名称（colName）和列类型。
请注意，某些ODBC数据类型以负整数形式返回。
下面的示例按顺序返回每个查询参数（`？`，`：var`和常量）的ODBC数据类型。请注意，`TOP`参数以数据类型12（`VARCHAR`）而不是数据类型4（`INTEGER`）返回，因为可以指定`TOP ALL`：
```java
/// d ##class(PHA.TEST.SQL).MetaData6()
ClassMethod MetaData6()
{
SET myquery = 4
SET myquery(1) = "SELECT TOP ? Name,DOB,Age+10 "
SET myquery(2) = "FROM Sample.Person"
SET myquery(3) = "WHERE %ID BETWEEN :startid :endid AND DOB=?"
SET myquery(4) = "ORDER BY $PIECE(Name,',',?)"
SET tStatement = ##class(%SQL.Statement).%New()
SET qStatus = tStatement.%Prepare(.myquery)
IF qStatus'=1 {
WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT
}
SET prepmeta = tStatement.%Metadata
WRITE "Number of ? parameters=",prepmeta.parameterCount,!
SET formalobj = prepmeta.formalParameters
SET i=1
WHILE formalobj.GetAt(i) {
SET prop=formalobj.GetAt(i)
WRITE prop.colName," type= ",prop.ODBCType,!
SET i=i+1
}
WRITE "End of metadata"
}
```
执行`Execute`之后，无法从查询结果集元数据中获取参数元数据。在结果集中，所有参数均已解析。因此`parameterCount = 0`，`formalParameters`不包含任何数据。
## Query结果集元数据
使用`％SQL.Statemen`t类执行`Execute`之后，可以通过调用返回结果集元数据：
- `％SQL.StatementResult`类的属性。
- `％SQL.StatementResult％GetMetadata（）`方法，访问`％SQL.StatementMetadata`类属性。
### ％SQL.StatementResult属性
执行查询操作后，`％SQL.StatementResult`返回：
- `％StatementType`属性返回与最近执行的SQL语句相对应的整数代码。以下是这些整数代码的部分列表：`1 = SELECT; 2 = INSERT; 3 = UPDATE; 4 = DELETE or TRUNCATE TABLE; 9 = CREATE TABLE; 15 = CREATE INDEX; 45 = CALL`.
- `％StatementTypeName`计算的属性基于`％StatementType`返回最近执行的SQL语句的命令名称。此名称以大写字母返回。请注意，`TRUNCATE TABLE`操作将作为`DELETE`返回。即使执行了更新操作，`INSERT OR UPDATE`也将作为`INSERT`返回。
- `％ResultColumnCount`属性返回结果集行中的列数。
下面的示例显示这些属性：
```java
/// d ##class(PHA.TEST.SQL).MetaData7()
ClassMethod MetaData7()
{
SET myquery = "SELECT TOP ? Name,DOB,Age FROM Sample.Person WHERE Age > ?"
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(10,55)
IF rset.%SQLCODE=0 {
WRITE "Statement type=",rset.%StatementType,!
WRITE "Statement name=",rset.%StatementTypeName,!
WRITE "Column count=",rset.%ResultColumnCount,!
WRITE "End of metadata"
} ELSE {
WRITE !,"SQLCODE=",rset.%SQLCODE," ",rset.%Message
}
}
```
### ％SQL.StatementResult ％GetMetadata（）
执行之后，可以使用`％SQL.StatementResult ％GetMetadata（）`方法访问`％SQL.StatementMetadata`类属性。这些是在Prepare之后由`％SQL.Statement％Metadata`属性访问的相同属性。
以下示例显示了属性：
```java
/// d ##class(PHA.TEST.SQL).MetaData8()
ClassMethod MetaData8()
{
SET myquery=2
SET myquery(1)="SELECT Name AS VendorName,LastPayDate,MinPayment,NetDays,"
SET myquery(2)="AVG(MinPayment),$HOROLOG,%TABLENAME FROM Sample.Vendor"
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()
IF rset.%SQLCODE=0 {
SET rsmeta=rset.%GetMetadata()
SET x=rsmeta.columns.Count()
SET x=1
WHILE rsmeta.columns.GetAt(x) {
SET column=rsmeta.columns.GetAt(x)
WRITE !,x," ",column.colName," is data type ",column.ODBCType
WRITE " with a size of ",column.precision," and scale = ",column.scale
SET x=x+1 }
} ELSE {
WRITE !,"SQLCODE=",rset.%SQLCODE," ",rset.%Message
}
WRITE !,"End of metadata"
}
```
请注意，结果集元数据不提供参数元数据。这是因为`Execute`操作会解析所有参数。因此，在结果集中，`parameterCount = 0`，而`formalParameters`不包含任何数据。
# 审核动态SQL
InterSystems IRIS支持动态SQL语句的可选审核。启用％System /％SQL / DynamicStatement系统审核事件时，将执行动态SQL审核。默认情况下，未启用此系统审核事件。
如果启用％System /％SQL / DynamicStatement，则系统将自动审核在系统范围内执行的每个`％SQL.Statement`动态语句。审核将信息记录在审核数据库中。
要查看审核数据库，请依次转到管理门户，系统管理，选择安全性，审核，然后查看审核数据库。可以将“事件名称”过滤器设置为DynamicStatement，以将View Audit Database限制为Dynamic SQL语句。审核数据库列出了时间（本地时间戳），用户，PID（进程ID）和事件的描述。说明指定动态SQL语句的类型。例如，SQL SELECT语句（`％SQL.Statement`）或SQL CREATE VIEW语句（`％SQL.Statement`）。
通过选择事件的详细信息链接，可以列出其他信息，包括事件数据。事件数据包括执行的SQL语句和该语句的任何参数的值。例如：
```java
SELECT TOP ? Name , Age FROM Sample . MyTest WHERE Name %STARTSWITH ?
/*#OPTIONS {"DynamicSQLTypeList":",1"} */
Parameter values:
%CallArgs(1)=5
%CallArgs(2)="Fred"
```
事件数据的总长度（包括语句和参数）为3,632,952个字符。如果该语句和参数长于3632952，则事件数据将被截断。
InterSystems IRIS还支持ODBC和JDBC语句的审核（事件名称= XDBCStatement），以及嵌入式SQL语句的审核（事件名称= EmbeddedStatement）。
