using meta-SQL 使用元SQL 五

%Select

Syntax

%Select(statefield1[,statefield2]...[,statefieldN])

Select field1[,field2]...[,fieldN]

The statefields must be valid fields on the state record (they may be fieldname or recordname.fieldname, as with %Bind), and fields must be either valid fields in the From tables or hard-coded values.

状态字段必须是状态记录上的有效字段(它们可以是字段名或记录名.字段名，如%BIND)，而且字段必须是FROM表中的有效字段或硬编码值。

Description

Use the %Select construct to identify the state record fields to hold the values returned by the corresponding Select statement. The %Select construct is required at the beginning of all Select statements. For example, you need one in the flow control actions and one in the SQL actions that contain a Select statement.

使用%Select构造来标识保存相应Select语句返回值的状态记录字段。所有Select语句的开头都需要%Select构造。例如，在流控制操作中需要一个，在包含Select语句的SQL操作中需要一个。

You use the %Select construct to pass variables to the state record, and you use the %Bind construct to retrieve or reference the variables.

使用%Select构造将变量传递给状态记录，使用%Bind构造检索或引用变量。

Example

Consider the following sample statement:

考虑下面的示例语句：

%SELECT(BUSINESS_UNIT,CUST_ID)

SELECT BUSINESS_UNIT, CUST_ID

FROM PS_CUST_DATA

   WHERE PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE)

The following steps illustrate the execution of the previous statement:

下面的步骤说明了前一条语句的执行：

1. Resolve bind variables.

解析绑定变量。

The string %Bind(PROCESS_INSTANCE) is replaced with the value of the state record field called

PROCESS_INSTANCE.

字符串%Bind（PROCESS_INSTANCE）被称为PROCESS_INSTANCE的状态记录字段的值替换

1. Execute the SQL Select statement.

执行SQL Select语句。

1. Perform a SQL Fetch statement.

执行SQL Fetch语句。

If a row is returned, the state record fields BUSINESS_UNIT and CUST_ID are updated with the results. If the Fetch statement does not return any rows, all fields in the %Select construct retain their previous values.

如果返回一行，则使用结果更新状态记录字段BUSINESS_UNIT和CUST_ID。如果Fetch语句不返回任何行，则%Select构造中的所有字段将保留它们以前的值。

Note: All fields referenced by a %Select construct must be defined in the associated state record. Also, aggregate functions always return a row, so they always cause the state record to be updated. As such, for aggregate functions, no difference exists between using %SelectInit or %Select.

注意：%Select构造引用的所有字段必须在关联的状态记录中定义。此外，聚合函数总是返回一行，因此它们总是导致状态记录被更新。因此，对于聚合函数，使用%SelectInit或%Select没有区别。

%SelectAll

Syntax

%SelectAll(:num [ correlation _id])

Description

%SelectAll is shorthand for selecting all fields in the specified record, wrapping DateTime fields with %DateOut, %TimeOut, and so on.

%SelectAll是选择指定记录中的所有字段的简写，用% TimeOut，%DateOut包装DateTime字段，依此类推。

The pseudocode looks like this:

伪代码是这样的：

Select(AllFields, :num correlation_id) from %Table(:num) prefix

This shortcut is only appropriate if the statement is being used in PeopleCode or Application Engine to read data into memory. Dynamic views should retain the internal database formats for DateTime fields.

此快捷方式仅适用于在PeopleCode或应用程序引擎中使用该语句将数据读入内存的情况。动态视图应该保留DateTime字段的内部数据库格式。

Using %SelectAll with CreateSQL

在CreateSQL中使用%SelectAll

You can use %SelectAll with the CreateSQL function without a record object. It must subsequently be executed with the record object with which you want to do the Select statement. Here is an example:

您可以在没有记录对象的情况下使用CreateSQL函数%SelectAll。它必须随后与要执行Select语句的记录对象一起执行。下面是一个例子：

    &REC_PROJ_FUNDING = CreateRecord(Record.PROJ_FUNDING); /* free standing record

 object */

    /* Create SQL objects */

    &SQL_PROJ_FUNDING_SEL = CreateSQL("%SelectAll(:1)" /* bind this later */);

        /* bind the %SelectAll */

        &SQL_PROJ_FUNDING_SEL.Execute(&REC_PROJ_FUNDING);

    While &SQL_PROJ_FUNDING_SEL.Fetch(&REC_PROJ_FUNDING);

  /* Process row content ... /*

  End-While;

You could also move the CreateRecord SQL statements out of the loop (and then move the close statements out of the loop too).

您还可以将CreateRecord SQL语句移出循环（然后将close语句也移出循环）。

%SelectByKey

Syntax

%SelectByKey(:num [ correlation_id ])

Description

This is a shorthand for:

这是一个简写：

Select %List(Select_List, :num correlation_id) from %Table(:num) correlation_id whe⇒ re %KeyEqual(:num, correlation_id)

%SelectByKeyEffDt

Syntax

%SelectByKeyEffDt(:num1, :num2)

Description

This is a shorthand for:

这是一个简写：

Select %List(Select_List, :num1) from %Table(:num1) A where %KeyEqualNoEffDt(:num1 ⇒ A) and %EffDtCheck(:num1 B, A, :num2)

%SelectDistinct

Syntax

%SelectDistinct(:num [ prefix])

Description

%SelectDistinct is shorthand for selecting all fields in the specified record, wrapping DateTime fields with %DateOut, %TimeOut, and so on.

%SelectDistinct是选择指定记录中的所有字段的简写形式，用%DateOut、%TimeOut等将DateTime字段包装起来。

The pseudocode looks like this:

伪代码是这样的：

Select DISTINCT(AllFields, :num correlation_id) from %Table(:num) prefix

This shortcut is only appropriate if the statement is being used in PeopleCode or Application Engine to read data into memory. Dynamic views should retain the internal database formats for DateTime fields.

此快捷方式仅适用于在PeopleCode或应用程序引擎中使用该语句将数据读入内存的情况。动态视图应该保留DateTime字段的内部数据库格式。

%SelectInit

Syntax

%SelectInit(statefield1[,statefield2]...[,statefieldN])

Select field1[,field2]...[,fieldN]

The statefields must be valid fields on the state record (they may be fieldname or recordname.fieldname, as with %Bind), and fields must be either valid fields in the From tables or hard-coded values.

状态字段必须是状态记录上的有效字段(它们可以是字段名或记录名.字段名，如%BIND)，而且字段必须是FROM表中的有效字段或硬编码值。

Description

Use the %SelectInit construct to identify the state record fields to hold the values returned by the corresponding Select statement.

使用%SelectInit构造来标识保存相应Select语句返回值的状态记录字段。

The %SelectInit construct is identical to the %Select construct with the following exception: if the Select statement returns no rows, then %SelectInit re-initializes the buffers. In the case of a %Select construct where no rows are returned, the state record fields retain their previous values.

SelectInit构造与%Select构造相同，只有以下例外：如果Select语句不返回任何行，则%SelectInit将重新初始化缓冲区。在不返回行的%Select构造的情况下，状态记录字段保留其以前的值。

Note: For aggregate functions, no difference exists between using %SelectInit or %Select.

附注：对于聚合函数，使用%SelectInit或%Select没有区别。

%Space

Description

Use the %Space meta-variable to specify a single space. Usage is similar to %Comma.

使用%Space元变量指定单个空格。用法类似于%逗号。

Related Links

%Comma %SQL

%SelectDummyTable

Description

Use the %SelectDummyTable variable to perform a SELECT without specifying a specific table. The database platform-specific “dummy table” is substituted in the SELECT.

Example

Before: In the following example, the SELECT was performed on the one-row PeopleTools installation table.

使用%SelectDummyTable变量执行SELECT而不指定特定表。数据库平台特定的“虚拟表”在SELECT中被替换。

SELECT 'x'   FROM PS_INSTALLATION WHERE ...

After: Using the %SelectNoTable variable ensures that the SQL will not fail if the table does not exist or if the table contains more than one row.

After：如果表不存在或者表包含多个行，使用%SelectNoTable变量可以确保SQL不会失败。

SELECT 'x'   FROM %SelectDummyTable WHERE ...

Before: In the following example, %SelectInit is used to initialize files in an Application Engine state record.

之前：在以下示例中，%SelectInit用于初始化应用程序引擎状态记录中的文件。

%SelectInit(GL_JP_AET.PROCESS_STATUS, GL_JP_AET.PROCESS_ORIG, GL_LOG_MSG_AET.MESSAG⇒ E_SET_NBR, GL_LOG_MSG_AET.MESSAGE_NBR, GL_LOG_MSG_AET.MESSAGE_PARM1, GL_LOG_MSG_AET⇒

.MESSAGE_PARM2, GL_LOG_MSG_AET.MESSAGE_PARM3)

 SELECT 'P'

 , 'P'

 , 5830

 , 4

 , TO_CHAR(1)

 , 'DVP1'

 , 'EK'

  FROM PS_INSTALLATION

After: Using the %SelectNoTable variable ensures that the SQL will not fail if the table does not exist or if the table contains more than one row.

After：如果表不存在或者表包含多个行，使用%SelectNoTable变量可以确保SQL不会失败。

%SelectInit(GL_JP_AET.PROCESS_STATUS, GL_JP_AET.PROCESS_ORIG, GL_LOG_MSG_AET.MESSAG⇒ E_SET_NBR, GL_LOG_MSG_AET.MESSAGE_NBR, GL_LOG_MSG_AET.MESSAGE_PARM1, GL_LOG_MSG_AET⇒

.MESSAGE_PARM2, GL_LOG_MSG_AET.MESSAGE_PARM3)

 SELECT 'P'

 , 'P'

 , 5830

 , 4

 , TO_CHAR(1)

 , 'DVP1'

 , 'EK'

  FROM %SelectDummyTable

%SQL

Syntax

%SQL(SQL_ID [, paramlist])

where paramlist is a list of arguments that are used for dynamic substitutions at runtime, in the form:

其中paramlist是用于运行时动态替换的参数列表，格式如下:

arg1 [, arg2]. . .

Description

Use the %SQL construct for common SQL fragments that you have already defined and want to reuse, substituting additional values dynamically. SQL_ID is the name of a SQL definition created using either Application Designer or the StoreSQL function.

对于已经定义并希望重用的常见SQL片段，使用%SQL构造，动态替换其他值。SQL_ID是使用应用程序设计器或StoreSQL函数创建的SQL定义的名称。

You can only nest up to 10 %SQL statements at a time.

一次最多只能嵌套10%的SQL语句。

Note: This meta-SQL construct is not implemented for COBOL. A SQL definition is not the same as the

SQL object that is instantiated from the SQL class at runtime. A SQL definition is created either using Application Designer at design time, or using the StoreSQL function. A SQL object is instantiated at runtime from the SQL class, and has methods and properties associated with it like any other object.

注意：这个元SQL构造不是为COBOL实现的。SQL定义与在运行时从SQL类实例化的SQL对象不同。SQL定义是在设计时使用应用程序设计器或使用StoreSQL函数创建的。SQL对象是在运行时从SQL类实例化的，并且像其他任何对象一样具有与之关联的方法和属性。

When a specified SQL definition has more than one version, the database type always takes precedence.

当指定的SQL定义具有多个版本时，数据库类型始终优先。

If one or more versions of a SQL definition are found for the database type of the current database connection, and if any of the versions have an effective date less than or equal to the value returned for %AsOfDate, the most recent version is used.

如果没有找到当前数据库类型的版本，或者如果所有版本的有效日期都大于%AsOfDate返回的值，系统将在数据库类型Generic下查找SQL定义的有效版本。

If no versions are found for the current database type, or if all of the versions have effective dates greater than the value returned for %AsOfDate, the system looks for an effective version of the SQL definition under the database type Generic.

如果没有找到当前数据库类型的版本，或者如果所有版本的有效日期都大于%AsOfDate返回的值，系统将在数据库类型Generic下查找SQL定义的有效版本。

If no version is found, an error occurs.

如果没有找到版本，则会发生错误。

Application Engine Considerations

应用程序引擎注意事项

Application Engine programs use the current date to compare with the effective date, not the date returned by %AsOfDate.

应用程序引擎程序使用当前日期与生效日期进行比较，而不是使用%AsOfDate返回的日期。

Special SQL Characters

特殊SQL字符

The following meta-SQL variables can be used as part of the %SQL construct to represent special characters as SQL parameters.

下面的meta-SQL变量可用作%SQL结构的一部分，以将特殊字符表示为SQL参数。

Meta-SQL Variable	Description
%Comma	Represents a single comma.
%LeftParen	Allows you to pass a left parenthesis character to a %P() variable, without closing the SQL object.
%RightParen	Allows you to pass a right parenthesis character to a %P() variable, without closing the SQL object.
%Space	Represents a space.

Example

例子

In the following example, the SQL definition MY_SQL was created in Application Designer to be the following:

在下面的示例中，在应用程序设计器中创建的SQL定义MY_SQL如下：

%P(1).EFFDT = (SELECT MAX(EFFDT) FROM ...)

In the following example, the %SQL statement is dynamically generated:

在下面的示例中，%SQL语句是动态生成的:

UPDATE PS_TEMP SET ...

WHERE ...

AND %SQL(MY_SQL, PS_TEMP)

The previous example resolves to the following:

上一个示例解析为以下内容：

UPDATE PS_TEMP SET ...

WHERE ...

AND PS_TEMP.EFFDT = (SELECT MAX(EFFDT) FROM ...)

Related Links

“Understanding SQL Class” (PeopleCode API Reference)

相关链接"理解SQL类"（PeopleCode API参考）

%SqlHint

Syntax

%SqlHint(SQL_cmd, index, hint_text, DB_platform [,{ENABLE | DISABLE}])

Description

Use the %SqlHint function to insert a database platform-specific SQL hint into the specified SQL statement. The hint is inserted immediately after the SQL command specified by the SQL_cmd parameter.

使用%SqlHint函数在指定的SQL语句中插入特定于数据库平台的SQL提示。提示会在SQL_cmd参数指定的SQL命令之后立即插入。

This meta-SQL function is ignored in any of the following circumstances:

在下列情况下，将忽略此meta-SQL函数:

• The current database connection does not match the DB_platform parameter.
• 当前数据库连接与DB_platform参数不匹配。
• The DB_platform parameter is not specified as ORACLE. (This is a limitation of the current release.)
• DB_platform参数未指定为ORACLE。（这是当前版本的限制。）
• The nth occurrence of the SQL command specified by SQL_cmd and index does not exist in the current SQL statement.
• 由SQL_cmd和index指定的第n次出现的SQL命令在当前SQL语句中不存在。

Parameters

Parameter	Description
SQL_cmd	Specifies the SQL command that will use the hint as one of the following literal constants: SELECT INSERT UPDATE DELETE MERGE
index	Specifies which occurrence of the SQL command will use the hint as an Integer value from 1 to 99.
hint_text	Specifies the SQL hint as a String value enclosed in single quotes. The hint can include other meta-SQL, such as %Table.
DB_platform	Specifies the database platform for which the hint is valid as one of the following literal constants: ORACLE DB2 DB2UNIX SQLSERVER Note: Currently, ORACLE is the only supported platform.  This meta-SQL function is ignored for all other platforms.
ENABLE | DISABLE	Specifies whether to enable or disable the hint as a literal constant. Note: ENABLE is the default value for this optional parameter.

Example 1

Before: The following example includes an Oracle-specific SQL hint to be inserted after the first SELECT of the SQL statement:

Before:以下示例包含要插入到SQL语句的第一个SELECT之后的Oracle特定SQL提示:

%SqlHint(SELECT, 1, '/*+ FIRST_ROWS(10) */', ORACLE)

 SELECT EMPLID

  FROM PS_JOB

After: For an Oracle connection, this meta-SQL would expand to:

After：对于甲骨文连接，这个元SQL将扩展为：

 SELECT '/*+ FIRST_ROWS(10) */' EMPLID

  FROM PS_JOB

After: On all other connections, this meta-SQL would expand to:

在所有其他连接上，这个meta-SQL将扩展到：

 SELECT EMPLID

  FROM PS_JOB

Example 2

例2

Before: In the following example, %SqlHint functions will be expanded and applied after all other metaSQL expansion has occurred. In this example, the APPEND hint will be applied to the first INSERT found in this SQL statement. The LEADING hint will be applied to the first SELECT found in this SQL statement.

Before:在以下示例中，%SqlHint函数将在所有其他元SQL展开之后展开和应用。在本例中，APPEND提示将应用于此SQL语句中找到的第一个INSERT。LEADING提示将应用于此SQL语句中找到的第一个SELECT。

%SqlHint(INSERT, 1, '/*+ APPEND*/', ORACLE, ENABLE ),%SqlHint(SELECT, 1, '/*+ LEADI⇒

NG(H) INDEX(L, PSFJRNL_LN) */', ORACLE, ENABLE) %InsertSelect(JRNL_LIU_TAO, JRNL_LN⇒

 L, BUSINESS_UNIT_IU=H.BUSINESS_UNIT_IU, LEDGER_GROUP=H.LEDGER_GROUP, IU_SYS_TRAN_C⇒ D=H.IU_SYS_TRAN_CD, IU_TRAN_CD=H.IU_TRAN_CD, PROCESS_INSTANCE=%Bind(PROCESS_INSTANC⇒

E))

  FROM %Table(JRNL_HIU_TAO) H, PS_%Bind(GL_JEDIT_WK_AET.RECNAME_JRNL_LN,NOQUOTES) L⇒

 WHERE H.PROCESS_INSTANCE=%Bind(PROCESS_INSTANCE)

   AND H.BUSINESS_UNIT=L.BUSINESS_UNIT

   AND H.JOURNAL_ID=L.JOURNAL_ID

   AND H.JOURNAL_DATE=L.JOURNAL_DATE

   AND H.UNPOST_SEQ=L.UNPOST_SEQ;

After: The SQL statement after all meta-SQL expansion and hint insertion:

after：在所有元SQL扩展和提示插入后的SQL语句：

 INSERT /*+ APPEND */ INTO PS_JRNL_LIU_TAO5 (BUSINESS_UNIT , JOURNAL_ID , JOURNAL_D⇒

ATE

/* For the purposes of clarity, many columns in this column list have been omitted ⇒ from this example. */

 , DEPTID , SCENARIO , BUSINESS_UNIT_IU)

 SELECT /*+ LEADING(H) INDEX(L, PSFJRNL_LN) */ L.BUSINESS_UNIT

 , L.JOURNAL_ID

 , L.JOURNAL_DATE

/* For the purposes of clarity, many columns in this column list have been omitted ⇒ from this example. */

 ,L.DEPTID

 ,L.SCENARIO

 , H.BUSINESS_UNIT_IU

  FROM PS_JRNL_HIU_TAO5 H

  , PS_%Bind(GL_JEDIT_WK_AET.RECNAME_JRNL_LN,NOQUOTES) L

 WHERE H.PROCESS_INSTANCE=%Bind(PROCESS_INSTANCE)

   AND H.BUSINESS_UNIT=L.BUSINESS_UNIT

   AND H.JOURNAL_ID=L.JOURNAL_ID

   AND H.JOURNAL_DATE=L.JOURNAL_DATE

   AND H.UNPOST_SEQ=L.UNPOST_SEQ ;

%SQLRows

Description

描述

Use the %SQLRows meta-variable to specify whether a SQL action returned any rows.

使用%SQLRows元变量来指定SQL操作是否返回任何行。

You can use %SQLRows in any Application Engine SQL statement, but the underlying value is affected only by SQL actions. It is not affected by Do When, Do Select, Do While, and Do Until actions. For Select statements, the value can only be 0 or 1: row not found or rows found, respectively. It does not reflect the actual number of rows that meet the Where criteria. To find the number of rows that meet the Where criteria, code a Select Count (*) statement.

您可以在任何应用程序引擎SQL语句中使用%SQLRows，但基础值只受SQL操作的影响。它不受执行“何时执行”、“选择执行”、“同时执行”和“直到执行”操作。对于Select语句，值只能为0或1:未找到行或已找到行。它不反映满足Where条件的实际行数。要查找满足Where条件的行数，请对Select Count（*）语句进行编码。

%Substring

Syntax

%Substring(source_str, start, length)

Description

%Substring expands to a substring of source_str.

%substring扩展为source_str的子字符串。

Parameters

Parameter	Description
source_str	Specify the source string.
start	Specify the substring's beginning position. The first character of source_str is position 1.
length	Specify the length of the substring.