Categories:

System functions (System Information)

SYSTEM$STREAM_GET_TABLE_TIMESTAMP

返回位于指定流当前偏移处或之前的最新表版本的时间戳（以纳秒为单位）。查询（或使用）流时，返回的记录包括在此表版本之后和当前时间之前提交的所有事务。

Note

This function was created primarily as a means to “bootstrap” a stream (i.e. return the set of records inserted between the period when the table was created (at table version t0) and the specified stream was created). Since this function was introduced, CREATE STREAM and SELECT statements that include the CHANGES clause now support Time Travel using the AT | BEFORE clause. These options provide greater flexibility for querying historical table records.

语法

SYSTEM$STREAM_GET_TABLE_TIMESTAMP('<stream_name>')

实参

stream_name

要查询的流的名称。

  • Note that the entire name must be enclosed in single quotes, including the database and schema (if the name is fully-qualified), i.e. '<db>.<schema>.<stream_name>'.
  • If the stream name is case-sensitive or includes any special characters or spaces, double quotes are required to process the case/characters. The double quotes must be enclosed within the single quotes, i.e. '"<stream_name>"'.

使用说明

  • 当输入是视图上的流时，此函数将返回错误。

要在现有流的当前偏移处或之前创建流，我们建议提供现有流名称作为 AT | BEFORE 子句的输入，以简化操作并最大限度地兼容现有流：

CREATE STREAM ... AT ( STREAM => '<stream-name>' )

示例

查询流的当前偏移的时间戳：

create table MYTABLE1 (id int);

create table MYTABLE2(id int);

create or replace stream MYSTREAM on table MYTABLE1;

insert into MYTABLE1 values (1);

-- consume the stream
begin;
insert into MYTABLE2 select id from MYSTREAM;
commit;

-- return the current offset for the stream
select system$stream_get_table_timestamp('MYSTREAM');