using meta-SQL 使用元SQL 六

%Table

Syntax

%Table(recname)

Description

Use the %Table construct to return the SQL table name for the record specified with recname.

使用%Table构造返回使用recname指定的记录的SQL表名。

This construct can be used to specify temporary tables for running parallel Application Engine processes across different subsets of data.

此构造可用于指定临时表，以便跨不同数据子集运行并行Application Engine进程。

Example

For example, the following statement returns the record PS_ABSENCE_HIST:

例如，以下语句返回记录PS_ACCESS_HIST:

%Table(ABSENCE_HIST)

If the record is a temporary table and the current process has a temporary table instance number specified, then %Table resolves to that instance of the temporary table PS_ABSENCE_HISTnn, where nn is the instance number.

如果记录是一个临时表，并且当前进程指定了一个临时表实例号，则%Table解析为临时表PS_LAUSE_HISTnn的该实例，其中nn是临时表实例号。

Related Links

“%Table” (PeopleCode Language Reference)

%Test

Syntax

%Test(Prefix, Test, Suffix)

Description

The %Test construct can be used with records that have no key values.

Test构造可用于没有键值的记录。

Parameters

Parameter	Description
Prefix	Specify a string that is conditionally added before the expansion of the test string. You cannot use meta-SQL in this parameter.
Test	Specify a meta-SQL string to be expanded.
Suffix	Specify a string that is conditionally added at the end of the test string. You can use meta-SQL in this parameter.

Returns

If the expansion of Test produces only a blank (or empty) string, the entire %Test meta-SQL construct is replaced with an empty string. Otherwise, the %Test meta-SQL construct is replaced by the prefix, then the expansion of Test, and then the suffix.

如果Test的展开只产生一个空白（或空）字符串，那么整个%Test meta-SQL构造将被替换为一个空字符串。否则，%Test meta-SQL构造将被前缀、Test的扩展和后缀所取代。

Example

The following meta-SQL generates valid SQL even when the given record has no keys:

下面的元SQL生成有效的SQL，即使给定的记录没有键:

%SelectAll(:1) %Test(WHERE ,%KeyEqual(:1));

%TextIn

Syntax

%TextIn(BindVariable)

Description

%TextIn construct, when used with a bind variable, allows the insertion and updating of a text string into a LongChar field (column).

%当TextIn构造与bind变量一起使用时，允许将文本字符串插入和更新到LongChar字段（列）中。

This construct is mandatory for any LongChar field insertion or update to be compatible on all database platforms on which it is supported.

对于任何LongChar字段的插入或更新来说，这个结构都是强制性的，以便在支持它的所有数据库平台上兼容。

Parameters

Parameter	Description
BindVariable	Specify a bind variable.

Example

In the following example, :1 is a bind variable in PeopleCode:

在下面的示例中，:1是PeopleCode中的绑定变量:

&String1 = "This is a test."

SqlExec("INSERT INTO PS_TABLE1 (STMTID, SQLSTMT) VALUES (1, %TextIn(:1))", &String1⇒

)

%TimeAdd

Syntax

%TimeAdd(datetime, add-minutes)

Description

This construct generates the SQL that adds add-minutes (a positive or negative integer literal or expression, provided that the expression resolves to a data type that can be used in datetime arithmetic for the given RDBMS) to the provided datetime (which can be a datetime literal or expression).

此构造生成的SQL将add-minutes（一个正整数或负整数文字或-表达式，前提是表达式解析为一个可用于给定RDBMS的datetime算法的数据类型）添加到所提供的datetime（它可以是一个datetime文字或表达式）。

Note: On some platforms, you can use time-value in place of datetime. However, this can give a SQL error on other platforms. This meta-SQL construct is not implemented for COBOL.

附注：在某些平台上，可以使用时间值代替日期时间。但是，这可能会在其他平台上产生SQL错误。这个元SQL构造不是为COBOL实现的。

Parameters

Parameter	Description
time	Specify a Time or DateTime value to add more time to.
add-minutes	Specify the number of minutes to add to time. This must be a numeric value or an expression that resolves to a numeric value.

Example

SELECT %TimeAdd(%CurrentTimeIn, 60) FROM %SelectNoTable

%TimeNull

Syntax

%TimeNull

Description

Use this meta-SQL to specify a null value for a time field. Only use this meta-SQL in Insert or Update statements. Do not use this meta-SQL in a Where clause.

使用这个元SQL为时间字段指定空值。仅在Insert或Update语句中使用此元SQL。不要在Where子句中使用这个元SQL。

Note: This meta-SQL is not implemented for COBOL.

注意：这个元SQL不是为COBOL实现的。

This meta-SQL resolves into a database-specific SQL substring, as shown in the following table:

此meta-SQL解析为特定于数据库的SQL子字符串，如下表所示：

Database	Resolved Substring
DB2	NULLIF(CURRENT TIME, CURRENT TIME)
All others	NULL

Parameters None.

%TimeOut

Syntax

%TimeOut(time_col)

Description

The %TimeOut meta-SQL variable expands to either a platform-specific SQL substring or datetime value, depending on the database platform, representing the time_col column in the Select clause of a SQL query.

Parameters

Parameter	Description
time_col	Specify a time column.

%TimePart

Syntax

%TimePart(DTTM_Column)

Description

%TimePart returns the time portion of the specified datetime column.

%TimePart返回指定日期时间列的时间部分。

Note: This meta-SQL is not implemented for COBOL.

注意：这个元SQL不是为COBOL实现的。

Considerations Using %TimePart

使用%TimePart的注意事项

Use %TimeOut meta-SQL when fetching from the database:

从数据库中提取时使用%TimeOut元SQL:

%TimeOut(%TimePart(DTTM_COLUMN)) from some_table

If a literal is used as the parameter to %TimePart, it must be wrapped in %DateTimeIn as shown in the following:

如果文本用作%TimePart的参数，则必须将其包装在%DateTimeIn中，如下所示：

insert into some_table values(%TimePart(%DateTimeIn('2001-01-01-12.34.56.789012')))

Parameters

Parameter	Description
DTTM_Column	Specify the datetime column to return the time for. 指定要返回时间的datetime列。

%TrimSubstr

Syntax

%TrimSubstr(source_str, start, length)

Description

%TrimSubstr, like %Substring, expands to a substring of source_str, except that trailing blanks are removed from the substring.

%TrimSubstr和%Substring一样，扩展为source_str的子字符串，只是子字符串中的尾随空格被移除。

Note: If you trim a string of blanks, an empty string is returned on all database platforms except Oracle, when a Null is returned. If a Null result is not acceptable, such as when using the result as a value to insert into a non-nullable column, you can turn the Null into a single blank using the %COALESCE meta-SQL with %TrimSubstr, for example: %COALESCE( %TrimSubstr( <expression>), ' ')

注意：如果你修剪了一个空白字符串，在除甲骨文以外的所有数据库平台上都会返回一个空字符串，当返回Null时。如果Null结果不可接受，例如当将结果用作值插入不可为空的列时，可以使用%COALESCE meta-SQL和%TrimSubstr将Null转换为单个空白，例如:% COALESCE （% TrimSubstr （《表达式》），''）

Related Links

%Substring

%Truncate

Syntax

%Truncate(expression, factor)

Description

%Truncate truncates an expression to a specified scale before or after the decimal point.

%Truncate将表达式截断为小数点之前或之后的指定比例。

Considerations Using %Truncate

使用%截断的注意事项

You may get incorrect results or runtime SQL errors if you try to use an expression for factor. The following code example produces incorrect results:

如果尝试使用factor表达式，可能会得到不正确的结果或运行时SQL错误。下面的代码示例生成不正确的结果：

%Truncate(10.337, 2 + 1)

Parameters

Parameter	Description
Expression	Specify an expression involving numeric constants and database columns.
Factor	Specify an integer or bind variable in SQLExec PeopleCode.  The range of a factor is -30 to +31. A negative number truncates to left of the decimal point.

Example

例子

Here is an example:

下面是一个例子

%Truncate(10.337, 2) = 10.33

%Truncate(13.37, 0) = 13

%Truncate(19.337, -1) = 10 SQLExec("SELECT %Truncate(field_c1, :1) from RECORD_T", field_c2, &Result);

In the example, field_c1 and field_c2 are two fields in the record.

在本例中，field_c1和field_c2是记录中的两个字段。

%TruncateTable

Syntax

%TruncateTable(table name)

Description

Use the %TruncateTable construct to invoke a bulk delete command on a table. This construct is functionally identical to a Delete SQL statement with no Where clause, but it is faster on databases that support bulk deletes. If you are familiar with COBOL, this construct is an enhanced version of the COBOL meta-SQL construct with the same name.

使用%TruncateTable构造对表调用批量删除命令。此构造在功能上与不带Where子句的Delete SQL语句相同，但在支持批量删除的数据库上速度更快。如果您熟悉COBOL，那么这个结构是同名COBOL meta-SQL结构的增强版本。

Some database vendors have implemented bulk delete commands that decrease the time required to delete all the rows in a table by not logging rollback data in the transaction log. For the databases that support these commands, Application Engine replaces %TruncateTable with Truncate Table SQL. For the other database types, %TruncateTable is replaced with Delete From SQL.

某些数据库供应商实现了批量删除命令，这些命令通过不将回滚数据记录在事务日志中来减少删除表中所有行所需的时间。对于支持这些命令的数据库，应用程序引擎将%TruncateTable替换为Truncate表SQL。对于其他数据库类型，%TruncateTable将替换为从SQL删除。

You should commit after the step that immediately precedes the step containing the %TruncateTable statement. In general, you should use this construct early in your Application Engine program as an initialization task. In addition, avoid using this meta-SQL when your Application Engine program is started from the PeopleCode CallAppEngine function.

您应该在包含%TruncateTable语句的步骤之前的步骤之后提交。通常，您应该在应用程序引擎程序的早期使用此构造作为初始化任务。此外，当应用程序引擎程序从PeopleCode CallAppEngine函数启动时，应避免使用此元SQL。

Unlike the COBOL version, Application Engine determines if a commit is possible before making the substitution. If a commit is possible, Application Engine makes the substitution and then forces a checkpoint and commit after the delete runs successfully.

与COBOL版本不同，Application Engine在进行替换之前确定提交是否可能。如果可以提交，则应用程序引擎进行替换，然后在成功运行删除后强制执行检查点并提交。

If a commit is not possible, Application Engine replaces the meta-SQL with a Delete From string. This string ensures restart integrity when your program runs against a database for which an implicit commit is associated with Truncate Table or for which rollback data is not logged.

For databases that either run an implicit commit for %TruncateTable or require a commit before or after this meta-SQL, replace %TruncateTable with an unconditional delete in the following circumstances:

• A commit is not allowed, as in an Application Engine program called from PeopleCode.
• The program issues a non-select SQL statement since the last commit occurred. In such a situation, data is likely to have changed.
• You are deferring commits in a Select/Fetch loop within a restartable program.

Note: To use a record name as the argument for %TruncateTable (instead of an explicit table name), you must include a %Table meta-SQL function to resolve the unspecified table name.

For example, to specify the record PO_WEEK as the argument, use the following statement:

%TruncateTable(%Table(PO_WEEK)).

Related Links

%Table

“%TruncateTable” (PeopleCode Language Reference)

相关链接%表"%TruncateTable"（人代码语言参考）

%Update

Syntax

%Update(:num [ , :num2 ])

Description

This is a shorthand for:

这是一个简写：

Update %Table(:num) set %UpdatePairs(:num) where %KeyEqual(:num2)

If num2 is omitted, the value defaults to num.

如果省略num2，则默认值为num。

%UpdatePairs

Syntax

%UpdatePairs(recname [correlation_id])

Description

The %UpdatePairs construct produces a comma-separated list of fieldname = 'fieldvalue' phrases for each changed field of the given record. Input processing is applied to the values in the following ways:

UpdatePairs 构造为给定记录的每个更改字段生成一个逗号分隔的 fieldname ='fieldvalue '短语列表。输入处理以下列方式应用于值：

• If the field is a Date, a Time, or a DateTime value, its value is automatically wrapped in %Datein, %TimeIn, or %DateTimeIn, respectively.
• 如果字段是“日期”、“时间”或“日期时间”值，则其值将分别自动包装在%Datein、%TimeIn或%Date TimeIn中。
• If the field is a string, its value is automatically wrapped in quotes.
• 如果字段是字符串，它的值会自动用引号括起来。
• If the field has a null value, NULL is the given value.
• 如果字段有空值，则NULL为给定值。

Note: This meta-SQL construct can only be used in PeopleCode programs, not in Application Engine SQL actions. Also, this meta-SQL construct is not implemented for COBOL.

附注：这个元SQL构造只能在PeopleCode程序中使用，不能在Application Engine SQL操作中使用。而且，这个元SQL构造也不是为COBOL实现的。

Parameters

Parameter	Description
recname	Specify the name of the record to use for updating. This can be a bind variable, a record object, or a record name in the form recname. You can't specify RECORD. recname, a record name in quotation marks, or a table name.
correlation_id	Identify the single-letter correlation ID to relate the record specified by recname and its fields.

Example

Suppose that the record &REC has one key: FNUM, and the FCHAR field has changed. Here is an example:

假设记录&REC只有一个键：FNUM，并且FC AR字段已经更改。下面是一个例子：

Local record &REC;

&REC = CreateRecord(RECORD.MYRECORD);

&REC.FNUM.Value = 27;

&REC.FCHAR.Value = 'Y';

SQLExec("Update TABLE set %UpdatePairs(:1) where %KeyEqual(:1)", &REC)

The example expands to:

该示例扩展为：

"Update TABLE set FCHAR = 'Y' where FNUM = 27"

The following example updates all the fields on a base record (&REC) that are not also fields on the related language record (&REC_RELATED_LANG). It creates a holding record (&REC_TEMP), copies the fields to update from the base record to the holding record, and then uses the holding record for the update.

下面的示例更新基记录（&REC）上的所有字段，这些字段不也是相关语言记录（&REC_RELATED_LANG）上的字段。它创建一个保持记录（&REC_TEMP），将要更新的字段从基本记录复制到保持记录，然后使用保持记录进行更新。

&UPDATE = CreateSQL("Update %Table(:1) set %UpdatePairs(:1) Where %KeyEqual(:2)");

&REC_TEMP = CreateRecord(@("RECORD." | &REC.Name));

&FIELD_LIST_ARRAY = CreateArray();

For &I = 1 to &REC_RELATED_LANG.FieldCount

   &FIELD_LIST_ARRAY.Push(&REC_RELATED_LANG.GetField(&I).Name);

End-For;

For &I = 1 to &REC.FieldCount

   If &FIELD_LIST_ARRAY.Find(&REC.GetField(&I).Name) = 0 then

      &REC_TEMP.GetField(&I).Value = &REC.GetField(&I).Value;

   End-If;

End-For;

&UPDATE.Execute(&REC_TEMP, &REC);

%UpdateStats

Syntax

%UpdateStats(record name ,[HIGH/LOW])

For example,

举个例子，

%UpdateStats(PO_WRK1) The default is LOW.

默认值为低。

Description

Use the %UpdateStats construct to generate a platform-dependent SQL statement that updates the system catalog tables used by the database optimizer in choosing optimal query plans. Use this construct after your program has inserted large amounts of data into a temporary table that will be deleted before the end of the program run. This construct saves you from having to use dummy seed data for the temporary table and having to update statistics manually.

使用%UpdateStats构造生成平台相关的SQL语句，该语句更新数据库优化器在选择最佳查询计划时使用的系统目录表。在程序将大量数据插入一个临时表后使用此构造，该临时表将在程序运行结束前删除。这种构造使您不必为临时表使用虚拟种子数据，也不必手动更新统计信息。

Notes About %UpdateStats

关于%UpdateStats

For databases that either run an implicit commit for %UpdateStats or require a commit before or after this meta-SQL, Application Engine skips %UpdateStats in the following circumstances:

对于运行%UpdateStats隐式提交或在此meta-SQL之前或之后要求提交的数据库，在下列情况下，应用程序引擎会略过%UpdateStats：

• A commit is not allowed, as in an Application Engine program called from PeopleCode.
• 不允许提交，例如在从PeopleCode调用的应用程序引擎程序中。
• The program issues a non-select SQL statement since the last commit occurred.
• 自上次提交以来，程序发出一条非select SQL语句。

In such a situation, data is likely to have changed.

在这样的情况下，数据很可能已经发生了变化。

• You are deferring commits in a Select/Fetch loop in a restartable program.
• 您正在可重新启动程序中的Select/Fetch循环中延迟提交。

Application Engine skips %UpdateStats even if the previous condition is false.

应用程序引擎跳过%即使前一个条件为false也要更新stats。

The following table shows how the %UpdateStats construct is resolved by the supported database systems:

下表显示了受支持的数据库系统如何解析%UpdateStats构造：

Database Function	Behavior
MSS %UpdateStats	Specifying LOW produces the statement UPDATE STATISTICS tablename Specifying HIGH produces the statement UPDATE STATISTICS tablename WITH FULLSCA⇒ N

Oracle %UpdateStats	Oracle uses DDL templates (in PSDDLMODEL) to determine SQL statements for %UpdateStats. Use DDLORA.DMS to change. Specifying LOW produces the statement execute DBMS_STATS.GATHER_TABLE_STATS (ownname=>⇒'PT8468908', tabname=>'PSSTATU⇒ S', estimate_percent=⇒ >20, method_opt=> 'FOR ALL INDEXED COLUMNS SIZE⇒1', cascade=>TRUE) Specifying HIGH produces the statement execute DBMS_STATS.GATHER_TABLE_STATS (ownname=> 'PT848908', tabname=>'PSSTATU⇒ S', estimate_percent=>⇒ dbms_stats.auto_sample_size, method_opt=⇒ > 'FOR ALL INDEXED COLUMNS SIZE 1', cascade=>TRUE)
DB2 UNIX %UpdateStats	In DB2 UNIX, %UpdateStats is performed by issuing sqlustat () calls that are equivalent to SQL statements. The sqlustat () is an internal DB2 API call function rather than an SQL command. Specifying LOW is equivalent to issuing the statement RUNSTATS ON TABLE tablename AND INDEXES ⇒ ALL Specifying HIGH is equivalent to issuing the statement RUNSTATS ON TABLE tablename  WITH DISTRIBUTION AND DETAILED INDEXES ⇒ ALL Note: You cannot view the sqlustat() calls nor the RUNSTATS statement in the SQL trace.
Db2 for z/OS %UpdateStats	Uses a DDL model template (in PSDDLMODEL) to format a control statement for the Db2 for z/OS Runstats utility. See the product documentation for PeopleSoft 9.2 Application Installation for DB2 for z/OS for more details on using %UpdateStats. Specifying LOW produces the statement RUNSTATS TABLESPACE [DBNAME].[TBSPCNAME]⇒  TABLE([DBNAME].[TABLE]) SAMPLE 25 [INDE⇒ XLIST] REPORT NO SHRLEVEL CHANGE UPDATE ACCESSPATH Specifying HIGH produces the statement RUNSTATS TABLESPACE [DBNAME].[TBSPCNAME]⇒  TABLE([DBNAME].[TABLE]) [INDEXLIST] REPORT NO SHRLEVEL CHANGE UPDATE ACCESSPATH

%UpdateStats Database Considerations

%UpdateStats数据库注意事项

The following table lists potential issues that you might encounter when using %UpdateStats:

Database	Consideration
Oracle	Oracle has an implicit commit after the %UpdateStats statement executes. Same behavior as previous consideration.
Database	Consideration
Db2 for z/OS	For Db2 for z/OS, %UpdateStats requires IBM stored procedure DSNUTILS running in an authorized Work Load Manager Application Environment. It is also highly recommended that individual tables intended to be a target of the %UpdateStats function are segregated to their own tablespaces. Refer to the following documents for more details on using %UpdateStats: PeopleSoft 9.2 Application Installation for DB2 for z/OS “Understanding Administration Tools” (Getting Started with PeopleTools) Note: You can trace information messages from the Runstats command on DB2 for z/OS executed as a result of issuing %UpdateStats. To enable this trace, select the SQL Informational Trace check box on the Configuration Manager – Trace page.
All	%UpdateStats consumes a large amount of time and database resources if run against very large tables. Therefore, analyze permanent data tables outside of application programs. Also, if temporary tables are likely to grow very large during a batch run, run the batch program only with %UpdateStats enabled to seed the statistics data or when the data composition changes dramatically.

Disabling %UpdateStats

禁用%UpdateStats

You can disable %UpdateStats in the following ways:

您可以通过以下方式禁用%UpdateStats:

• Include the following parameter on the command line when running an Application Engine program:
• 在运行应用程序引擎程序时，在命令行中包含以下参数：

-DBFLAGS  1

• Change the Dbflags=0 parameter in the PeopleSoft Process Scheduler configuration file (or PSADMIN) to Dbflags=1.
• 将 PeopleSoft 进程调度程序配置文件（或 PSADMIN ）中的 Dbflags = 0 参数更改为 Db flag = 1

Using %UpdateStats With COBOL

使用COBOL更新%

You can use the %UpdateStats construct from SQL embedded in COBOL programs. Use this syntax:

您可以使用嵌入在COBOL程序中的SQL中的%UpdateStats构造。使用以下语法:

%UpdateStats(tablename)

When you issue this construct from PeopleTools, the parameter is record name.

当您从PeopleTools发出这个构造时，参数是record name。

%Upper

Syntax

%Upper(charstring)

Description

The %Upper construct converts the string charstring to uppercase. You can use wildcards with charstring, such as the percent sign (%).

Upper构造将字符串转换为大写。可以将通配符与字符串一起使用，例如百分号（%）。

Note: This meta-SQL construct is not implemented for COBOL.

注意：这个元SQL构造不是为COBOL实现的。

Considerations with COBOL and Unicode

使用COBOL和Unicode的注意事项

COBOL's uppercase function is not Unicode-aware, and corrupts Unicode data. To use an uppercase function with COBOL, use the function supplied with PeopleTools called PTPUPPER.

Cobol的大写函数不能识别Unicode，而且会损坏Unicode数据。要在COBOL中使用大写函数，请使用PeopleTools提供的名为PTPUPPER的函数。

The syntax to call PTPUPPER is:

调用PTPUPPER的语法是:

CALL 'PTPUPPER' USING SQLRT

   <any PIC S9(4) COMP field that contains the fields defined length (non-unicode)>    <the String field - max PIC X(8192).>

The following is an example from Unicode-expanded source code:

以下是Unicode扩展源代码的示例:

1. W-WORK.
2. W-DESCR      PIC X(90)   VALUE SPACES.

    02   W-SIZE       PIC S9(4)   COMP VALUE +30.

      CALL 'PTPUPPER' USING SQLRT

         W-SIZE OF W-WORK

         W-DESCR OF W-WORK

Parameters

Parameter	Description
charstring	Specify the string to convert to uppercase.

Example

SELECT EMPLID, NAME FROM PS_EMPLOYEES WHERE %UPPER(NAME) LIKE %UPPER(sch%)