SQL系统信息功能

系统信息功能

提取会话和系统信息的几个功能可用。

功能 描述

current_schema()

返回将用于任何表或在不指定目标架构的情况下创建的任何表或其他命名对象的架构的名称。

选择 CURRENT_SCHEMA FROM DUAL;

当前用户()

返回适用于权限检查的用户标识符的名称。

选择 CURRENT_USER FROM DUAL;

数据库()

返回数据库的名称。

选择 数据库() FROM DUAL;

getcommitprotocol()

返回事务使用的提交协议。

选择 getcommitprotocol() FROM DUAL;
/* safe */

getConnectionID()

Returns the ID of the current database connection. Details about database connections are stored in the SYSTEM.CONNECTIONS table (see 连接系统表说明)。

选择 getConnectionID() FROM DUAL;

geteffectiveplatformversion()

返回数据库的有效版本。这与Nuodb产品释放版本不同。但是,这两个版本密切相关。

当新功能添加到数据库中时,在新产品发布版本中,更新了数据库的有效版本。所有节点必须在同一有效版本下运行。在滚动升级中,当数据库中的所有节点都能够支持该更新版本时,自动更新有效版本。

一旦更新了数据库的有效版本,所有节点都将开始在此较新版本中运行。如果节点不能支持数据库的当前有效版本(即不能支持数据库的所有功能),则不会允许节点加入域。

选择 geteffectiveplatformversion() FROM DUAL;

getNodeID()

Returns the unique ID of the transaction engine of the local connection. Details of system nodes are stored in the SYSTEM.NODES table (see 节点系统表描述)。

选择 getNodeID() FROM DUAL;

getTransactionId()

返回当前事务的唯一标识符。

选择 getTransactionId() FROM DUAL;

getupdatecount()

Returns the number of rows that were affected by the last , 更新, 删除 or 代替 statement. This function returns the same value that is returned by the JDBC Statement#getUpdateCount() function. Also, this is the value in the 更新COUNT column of the SYSTEM.LASTSTATEMENT table (See raketstatement system表说明)。

A second select of this function, immediately following the first select, will return zero. When you select from the function that 选择 statement becomes the last statement executed. The 更新COUNT column in the SYSTEM.LASTSTATEMENT table equals zero for a 选择 statement.

选择 getupdatecount() FROM DUAL;

last_insert_id()

Returns the last automatically generated ID for a column declared with GENERATED ALWAYS AS IDENTITY or GENERATED BY DEFAULT AS IDENTITY. This will return 0 for a new connection. If multiple rows are inserted with one statement, the ID of the first row is returned. The value returned is on a per-connection basis; it is the last ID generated for the client, and not necessarily the last ID inserted into the table.

选择 last_insert_id() FROM DUAL;

OPTIONAL_FIELD(column_name, default_value)

Returns the value defined by the column_name. If the column does not exist in the table being selected from then this will return the value defined by the default_value. This can be useful when doing upgrades to schema. Before new columns are added to a table the application code that references these new columns can be deployed without generating errors.

选择 OPTIONAL_FIELD(table_columnname,'NEW') FROM tablename;

scope_identity()

Returns the last identity values that are generated in any table in the current session, restricted to values within the current scope. The current scope is a stored procedure, trigger, function, or batch. Two statements are in the same scope if they are in the same stored procedure, function, or batch. Whereas last_insert_id() will return the last insert identity relative to the table, scope_identity() will return the last identity relative to the scope. If multiple rows are inserted with one statement (batch), the ID of the last row is returned by scope_identity().

选择 scope_identity() FROM DUAL;

用户()

返回适用于权限检查的用户标识符的名称。 用R is valid in any expression.

选择 用R() FROM DUAL;

例子

示例显示current_user与user()

There is a difference between the 用户() function and the pseudo-column CURRENT_USER. CURRENT_USER only has a predefined meaning when it’s selected from DUAL, but 用户() is valid in any expression:

# 用R() versus CURRENT_USER()
选择 current_user FROM dual;
 [CURRENT_USER]
 ---------------
       DBA

选择 user() FROM dual;
 [用R]
 -------
   DBA

# Work log example
CREATE TABLE work_log (tablename STRING, columnname STRING, change_desc STRING);
 INTO work_log VALUES ('HOCKEY','NUMBER','Changed from integer to string');

选择 user() AS changed_by, tablename, columnname, change_desc FROM work_log;
 CHANGED_BY  TABLENAME  COLUMNNAME           CHANGE_DESC
 ----------- ---------- ----------- ------------------------------
     DBA       HOCKEY     NUMBER    Changed from integer to string

选择 current_user AS changed_by,tablename,columnname,change_desc FROM work_log;
 CHANGED_BY  TABLENAME  COLUMNNAME           CHANGE_DESC
 ----------- ---------- ----------- ------------------------------
     DBA       HOCKEY     NUMBER    Changed from integer to string

示例 LAST_INSERT_ID

# LAST_INSERT_ID Examples
CREATE TABLE t (id INT GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY, name VARCHAR(10));
 INTO t (name) VALUES ('User1');

选择 * FROM t;

 ID  NAME
--- -----
 1  User1

选择 last_insert_id() FROM dual;

 [LAST_INSERT_ID]
-----------------
        1

# If multiple rows are inserted, returns the id of the first one.
 INTO t (name) VALUES ('User2'), ('User3');

选择 * FROM t;

 ID  NAME
--- -----
 1  User1
 2  User2
 3  User3

选择 last_insert_id() FROM dual;

 [LAST_INSERT_ID]
 -----------------
        2

Example showing LAST_INSERT_ID versus SCOPE_IDENTITY

CREATE TABLE t1 (id INT GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY, name VARCHAR(10));
CREATE TABLE t2 (id INT GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY, name VARCHAR(10));
 INTO t1 (name) VALUES ('greg');
 INTO t2 (name) VALUES ('marcia'), ('peter'), ('jan'), ('bobby'), ('cindy');
选择 last_insert_id() FROM dual;

 [LAST_INSERT_ID]
 -----------------
         1

选择 scope_identity() FROM dual;

 [SCOPE_IDENTITY]
 -----------------
         5

示例 OPTIONAL_FIELD

CREATE TABLE my_table
(id INTEGER PRIMARY KEY
,description STRING);
 INTO my_table VALUES
    (1,'row-1')
   ,(2,'row-2');
选择 id,description FROM my_table;
 ID  DESCRIPTION
 --- ------------
  1     row-1
  2     row-2

/* Before schema Upgrade. Column SOURCE does not exist */
/*   For now, we can execute code and provide a default value */
选择 id,description,OPTIONAL_FIELD(source,'Batch') FROM my_table;
 ID  DESCRIPTION  ['Batch']
 --- ------------ ----------
  1     row-1       Batch
  2     row-2       Batch

/* Schema upgrade */
ALTER TABLE my_table ADD source STRING;

/* Column SOURCE now exists, but has NULL value */
/*    The actual column value is returned */
选择 id,description,OPTIONAL_FIELD(source,'batch') FROM my_table;
 ID  DESCRIPTION  MY_TABLE.SOURCE
 --- ------------ ----------------
  1     row-1          <null>
  2     row-2          <null>


UPDATE my_table  source = CASE ID WHEN 1 then 'Batch' ELSE 'Online' END;

选择 id,description,OPTIONAL_FIELD(source,'batch') FROM my_table;
 ID  DESCRIPTION  MY_TABLE.SOURCE
 --- ------------ ----------------
  1     row-1          Batch
  2     row-2          Online