发布新帖
文章
· 三月 27, 2021 阅读大约需 11 分钟

第十三章 使用动态SQL（五）

#SQL #Caché #InterSystems IRIS #InterSystems IRIS for Health

第十三章 使用动态SQL（五）

从结果集中返回特定的值

要从查询结果集中返回特定的值，必须一次一行遍历结果集。
要遍历结果集，请使用%Next()实例方法。
(对于单一值，结果对象中没有行，因此%Next()返回0，而不是错误。)
然后，可以使用%Print()方法显示整个当前行的结果，或者检索当前行的指定列的值。

%Next()方法获取查询结果中下一行的数据，并将该数据放入结果集对象的data属性中。
%Next()返回1，表示它位于查询结果中的某一行上。
%Next()返回0，表示它位于最后一行(结果集的末尾)之后。
每次调用%Next()返回1个增量%ROWCOUNT;
如果游标定位在最后一行之后(%Next()返回0)，%ROWCOUNT表示结果集中的行数。

如果SELECT查询只返回聚合函数，每个%Next()设置%ROWCOUNT=1。
第一个%Next()返回1并设置%SQLCODE=0和%ROWCOUNT=1，即使表中没有数据;
任何随后的%Next()返回0，并设置%SQLCODE=100和%ROWCOUNT=1。

从结果集中获取一行后，可以使用以下任何一种方式显示该行的数据:
- rset.%Print()返回查询结果集中当前行的所有数据值。
- rset.%GetRow()和rset.getrows()以编码列表结构的元素形式从查询结果集中返回一行的数据值。
- rset.name按查询结果集中的属性名称、字段名称、别名属性名称或别名字段名称返回数据值。
- rset.%Get("fieldname")通过字段名或别名从查询结果集中或存储的查询返回一个数据值。
- rset.%GetData(n)按列号从查询结果集中或存储的查询中返回一个数据值。

%Print()方法

%Print()实例方法从结果集中检索当前记录。默认情况下，%Print()在数据字段值之间插入空白空格分隔符。
%Print()不会在记录的第一个字段值之前或最后一个字段值之后插入空白;
它在记录的末尾发出一个行返回。
如果数据字段值已经包含空格，则将该字段值括在引号中，以将其与分隔符区分开来。
例如，如果%Print()返回城市名称，它将按如下方式返回它们: "New York" Boston Atlanta "Los Angeles" "Salt Lake City" Washington.
引用包含分隔符作为数据值一部分的字段值，即使从未使用过%Print()分隔符;
例如，如果结果集中只有一个字段。

可以选择指定％Print（）参数，该参数提供在字段值之间放置的另一个定界符。指定其他定界符将覆盖包含空格的数据字符串的引用。此％Print（）分隔符可以是一个或多个字符。它指定为带引号的字符串。通常，％Print（）分隔符最好是在结果集数据中找不到的字符或字符串。但是，如果结果集中的字段值包含％Print（）分隔符（或字符串），则该字段值将用引号引起来，以将其与分隔符区分开。

如果结果集中的字段值包含换行符，则该字段值将以引号引起来。

以下ObjectScript示例使用％Print（）遍历查询结果集以显示每个结果集记录，并使用 "^|^" 定界符分隔值。请注意％Print（）如何显示FavoriteColors字段中的数据，该字段是元素的编码列表：

/// d ##class(PHA.TEST.SQL).ROWCOUNTPrint()
ClassMethod ROWCOUNTPrint()
{
    SET q1="SELECT TOP 5 Name,DOB,Home_State,FavoriteColors "
    SET q2="FROM Sample.Person WHERE FavoriteColors IS NOT NULL"
    SET myquery = q1_q2
    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()
    WHILE rset.%Next() {
        WRITE "Row count ",rset.%ROWCOUNT,!
        DO rset.%Print("^|^")
    }
    WRITE !,"End of data"
    WRITE !,"Total row count=",rset.%ROWCOUNT
}


DHC-APP> d ##class(PHA.TEST.SQL).ROWCOUNTPrint()
Row count 1
yaoxin^|^54536^|^WI^|^$lb("Red","Orange","Yellow")
Row count 2
姚鑫^|^^|^^|^$lb("Red","Orange","Yellow","Green")
Row count 3
姚鑫^|^^|^^|^$lb("Red","Orange","Yellow","Green","Green")
Row count 4
Isaacs,Roberta Z.^|^^|^^|^$lb("Red","Orange","Yellow","Green","Yellow")
Row count 5
Chadwick,Zelda S.^|^50066^|^WI^|^$lb("White")

End of data
Total row count=5

下面的示例显示如何将包含定界符的字段值括在引号中。在此示例中，大写字母A用作字段定界符；因此，任何包含大写字母A的字段值（名称，街道地址或州缩写）都将以引号引起来。

/// d ##class(PHA.TEST.SQL).ROWCOUNTPrint2()
ClassMethod ROWCOUNTPrint2()
{
    SET myquery = "SELECT TOP 25 Name,Home_Street,Home_State,Age 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
    }
    SET rset = tStatement.%Execute()
    WHILE rset.%Next() {
        DO rset.%Print("A")
    }
    WRITE !,"End of data"
    WRITE !,"Total row count=",rset.%ROWCOUNT
}

DHC-APP>d ##class(PHA.TEST.SQL).ROWCOUNTPrint2()
yaoxinA889 Clinton DriveAWIA30
xiaoliAAA
姚鑫AAA7
姚鑫AAA7
姚鑫AAA43
姚鑫AAA
姚鑫AAA
Isaacs,Roberta Z.AAA
Chadwick,Zelda S.A9889 Clinton DriveAWIA43
Fives,James D.A2091 Washington BlvdANDA88
Vonnegut,Jose P.A3660 Main PlaceAWIA47
Chadbourne,Barb B.A1174 Second StreetA"VA"A93
"Quigley,Barb A."A"6501 Ash Avenue"AKYA73

％GetRow（）和％GetRows（）方法

％GetRow（）实例方法从结果集中检索当前行（记录），作为字段值元素的编码列表：

/// d ##class(PHA.TEST.SQL).ROWCOUNTPrint3()
ClassMethod ROWCOUNTPrint3()
{
    SET myquery = "SELECT TOP 17 %ID,Name,Age 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
    }
    SET rset = tStatement.%Execute()
    FOR  { 
        SET x=rset.%GetRow(.row,.status)
        IF x=1 {
            WRITE $LISTTOSTRING(row," | "),! 
        } ELSE {
            WRITE !,"End of data"
            WRITE !,"Total row count=",rset.%ROWCOUNT
            RETURN 
        }
    }
}

％GetRows（）实例方法从结果集中检索指定大小的一组行（记录）。每行作为字段值元素的编码列表返回。

下面的示例返回结果集中的第1、6和11行。在此示例中，％GetRows（）第一个参数（5）指定％GetRows（）应该检索五行的连续组。如果成功检索到一组五行，％GetRows（）将返回1。 .rows参数通过引用传递这五行的下标数组，因此，rows（1）返回每五组中的第一行：第1、6和11行。指定rows（2）将返回第2、7行和12。

/// d ##class(PHA.TEST.SQL).ROWCOUNTPrint4()
ClassMethod ROWCOUNTPrint4()
{
    SET myquery = "SELECT TOP 17 %ID,Name,Age 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
    }
    SET rset = tStatement.%Execute()
    FOR  { 
        SET x=rset.%GetRows(5,.rows,.status)
        IF x=1 {
            WRITE $LISTTOSTRING(rows(1)," | "),! 
        } ELSE {
            WRITE !,"End of data"
            WRITE !,"Total row count=",rset.%ROWCOUNT
            RETURN 
        }
    }
}

可以使用ZWRITE rows命令返回检索到的数组中的所有下标，而不是按下标检索单个行。请注意，上面的示例ZWRITE行不会返回结果集中的第16行和第17行，因为在检索到最后一组五行之后，这些行是余数。

rset.name属性

当InterSystems IRIS生成结果集时，它将创建一个结果集类，其中包含一个与该结果集中的每个字段名称和字段名称别名相对应的唯一属性。

可以使用rset.name属性按属性名称，字段名称，属性名称别名或字段名称别名返回数据值。

  • 属性名称：如果未定义字段别名，则将字段属性名称指定为rset.PropName。结果集字段属性名称取自表定义类中的相应属性名称。
  • 字段名称：如果没有定义字段别名，请将字段名称（或属性名称）指定为rset。“fieldname”。这是表定义中指定的SQLFIELDNAME。 Intersystems Iris使用此字段名称来查找相应的属性名称。在许多情况下，属性名称和字段名称（SQLFieldName）是相同的。
  • 别名属性名称：如果定义了字段别名，则将别名属性名称指定为rset.AliasProp。别名属性名称是根据SELECT语句中的列名称别名生成的。不能为具有已定义别名的字段指定字段属性名称。
  • 别名：如果定义了字段别名，则将此别名（或别名属性名称）指定为rset。“ alias”。这是SELECT语句中的列名别名。您不能为具有已定义别名的字段指定字段名称。
  • 集合，表达式或子查询：InterSystems IRIS为这些选择项分配一个字段名称Aggregate_n，Expression_n或Subquery_n（其中整数n对应于查询中指定的选择项列表的顺序）。可以使用字段名称（rset。“ SubQuery_7”不区分大小写），相应的属性名称（rset.Subquery7区分大小写）或用户定义的字段名称别名来检索这些select-item值。也可以只使用rset。％GetData（n）指定选择项的序列号。

指定属性名称时，必须使用正确的字母大小写；指定字段名称时，不需要正确的字母大小写。

使用属性名称对rset.name的调用具有以下后果：

  • 字母大小写：属性名称区分大小写。字段名称不区分大小写。 Dynamic SQL可以自动解决指定字段或别名与相应属性名称之间的字母大小写差异。但是，解决字母大小写需要时间。为了最大限度地提高性能，应该指定属性名称或别名的确切字母大小写。
  • 非字母数字字符：属性名称只能包含字母数字字符（起始的％字符除外）。如果相应的SQL字段名称或字段名称别名包含非字母数字字符（例如Last_Name），则可以执行以下任一操作：
    • 指定用引号分隔的字段名称。例如，rset。“ Last_Name”）。分隔符的这种使用不需要启用分隔符。执行大写字母解析。
    • 指定相应的属性名称，以消除非字母数字字符。例如，rset.LastName（或rset。“ LastName”）。必须为属性名称指定正确的字母大小写。
    • ％属性名称：通常，以％字符开头的属性名称保留供系统使用。如果字段属性名称或别名以％字符开头，并且该名称与系统定义的属性冲突，则返回系统定义的属性。例如，对于SELECT Notes AS％Message，调用rset。％Message将不返回Notes字段值。它返回为语句结果类定义的％Message属性。可以使用rset。％Get（“％Message”）返回字段值。
    • 列别名：如果指定了别名，则Dynamic SQL始终匹配该别名，而不匹配字段名称或字段属性名称。例如，对于SELECT Name AS Last_Name，只能使用rset.LastName或rset。“ Last_Name”来检索数据，而不能使用rset.Name。
    • 重复名称：如果名称解析为相同的属性名称，则它们是重复的。重复名称可以是对表中同一字段的多个引用，对表中不同字段的别名引用或对不同表中字段的引用。例如，SELECT p.DOB，e.DOB指定两个重复的名称，即使这些名称引用了不同表中的字段。

如果SELECT语句包含相同字段名称或字段名称别名的多个实例，则rset.propname或rset。“fieldname”始终返回SELECT语句中指定的第一个。例如，对于SELECT C.NAME，P.NAME来自Sample.person as p，sample.company使用rset.name检索公司名称字段数据;选择C.Name，P.Name作为来自Sample.person的名称，As P，Sample.com本文使用RSET。“name”还检索公司名称字段数据。如果查询中存在重复的名称字段，则字段名称（名称）的最后一个字符由字符（或字符）替换为创建唯一属性名称。因此，查询中的重复名称字段名称具有相应的唯一属性名称，以NAM0（第一个重复）通过NAM9开始，并通过NAMZ继续大写字母NAMA。

对于使用％Prepare（）准备的用户指定的查询，可以单独使用属性名称。对于使用％PrepareClassQuery（）准备的存储查询，必须使用％Get（“ fieldname”）方法。

下面的示例返回由属性名称指定的三个字段的值：两个属性值分别由属性名称和第三个属性值由别名属性名称。在这些情况下，指定的属性名称与字段名称或字段别名相同：

/// d ##class(PHA.TEST.SQL).PropSQL()
ClassMethod PropSQL()
{
    SET myquery = "SELECT TOP 5 Name,DOB AS bdate,FavoriteColors FROM Sample.Person"
    SET tStatement = ##class(%SQL.Statement).%New(1)
    SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {
        WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT
    }
    SET rset = tStatement.%Execute()
    WHILE rset.%Next() {
        WRITE "Row count ",rset.%ROWCOUNT,!
        WRITE rset.Name
        WRITE " prefers ",rset.FavoriteColors
        WRITE " birth date ",rset.bdate,!!
    }
    WRITE !,"End of data"
    WRITE !,"Total row count=",rset.%ROWCOUNT
}

DHC-APP>d ##class(PHA.TEST.SQL).PropSQL()
Row count 1
yaoxin prefers Red,Orange,Yellow birth date 1990-04-25

Row count 2
xiaoli prefers  birth date

Row count 3
姚鑫 prefers  birth date 2014-01-02

Row count 4
姚鑫 prefers  birth date 2014-01-02

Row count 5
姚鑫 prefers  birth date 1978-01-28


End of data
Total row count=5

在上面的示例中，返回的字段之一是FavoriteColors字段，其中包含％List数据。若要显示此数据，％New（1）类方法将％SelectMode属性参数设置为1（ODBC），从而导致该程序将％List数据显示为逗号分隔的字符串，并以ODBC格式显示出生日期：

下面的示例返回Home_State字段。因为属性名称不能包含下划线字符，所以本示例指定用引号（“ Home_State”）分隔的字段名称（SqlFieldName）。还可以指定不带引号的相应生成的属性名称（HomeState）。请注意，定界字段名称（“ Home_State”）不区分大小写，但是生成的属性名称（HomeState）是区分大小写的：

/// d ##class(PHA.TEST.SQL).PropSQL1()
ClassMethod PropSQL1()
{
    SET myquery = "SELECT TOP 5 Name,Home_State FROM Sample.Person"
    SET tStatement = ##class(%SQL.Statement).%New(2)
    SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
    SET rset = tStatement.%Execute()
    WHILE rset.%Next() {
        WRITE "Row count ",rset.%ROWCOUNT,!
        WRITE rset.Name
        WRITE " lives in ",rset."Home_State",!
    }
    WRITE !,"End of data"
    WRITE !,"Total row count=",rset.%ROWCOUNT
}

DHC-APP>d ##class(PHA.TEST.SQL).PropSQL1()
Row count 1
yaoxin lives in WI
Row count 2
xiaoli lives in
Row count 3
姚鑫 lives in
Row count 4
姚鑫 lives in
Row count 5
姚鑫 lives in

End of data
Total row count=5
讨论 (1)1
登录或注册以继续