暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

华为GaussDB T 其他函数

墨天轮 2019-10-12
1200

其他函数

ARRAY_LENGTH

语法:

ARRAY_LENGTH(expr)

功能:计算给定数组expr(或能够转换成数组类型的字符串)的长度(不是元素个数)。

示例:

SELECT array_length('{1,2,3}') FROM SYS_DUMMY; ARRAY_LENGTH('{1,2,3}') ------------------------- 3 SELECT array_length('{}') FROM SYS_DUMMY; ARRAY_LENGTH('{}') ------------------ 0
--创建表TEST。 CREATE TABLE test (f1 INTEGER, f2 INTEGER, f3 INTEGER[]);
--插入记录。 INSERT INTO test VALUES (1,1,array[1,2]), (1,2,array[4,5,6]), (1,3,array[1,3]), (2,100,array[2,4]), (2,200,array[6,8]), (2, 300,array[7,9]), (3, 500,array[5,0]), (3,600,array[9,10]);
--查询给定数组长度。 SELECT array_length(array_agg(f1)) FROM test; ARRAY_LENGTH(F1) ---------------- 8 SELECT array_length(f3) FROM test; ARRAY_LENGTH(F3) ---------------- 2 3 2 2 2 2 2 2
--更新。 UPDATE test SET f3[100] = 100 where f1 = 3; SELECT array_length(f3) FROM test; ARRAY_LENGTH(F3) ---------------- 2 3 2 2 2 2 100 100

BIN2HEX

语法:

BIN2HEX(expr)

功能:将如下数据类型转换成十六进制数(带0x)。

数据类型

说明

GS_TYPE_CHAR

char类型

GS_TYPE_VARCHAR

varchar类型

GS_TYPE_STRING

string类型MD

GS_TYPE_VARBINARY

varbinary类型

GS_TYPE_BINARY

binary类型

GS_TYPE_RAW

raw类型

示例:

将二进制字符串'A123'转换为十六进制。

SELECT BIN2HEX('A123') from SYS_DUMMY; BIN2HEX('A123') --------------- 0x41313233 1 rows fetched.

CHAR_LENGTH

语法:

CHAR_LENGTH(str)

功能:返回输入字符串的长度。

  • 字符串str需要以单引号括起来。
  • 如果入参是数字则被隐式转为字符串。数字不能超出40位(超出了数值转换的最大值),超出不保证结果正确性。

示例:

返回字符串'character'的长度。

SELECT CHAR_LENGTH('character') FROM SYS_DUMMY; CHAR_LENGTH('CHARACTER') ------------------------ 9 1 rows fetched.

COALESCE

语法:

COALESCE ( expression, expression [ , ...] )

功能:返回入参中第一个非空表达式。

expression可以是任意表达式。

说明:如果所有参数均为NULL,则COALESCE返回NULL。

示例:

返回第一个非空表达式。

SELECT COALESCE( NULL, 34, 13, 0 ); COALESCE( NULL, 34, 13, 0 ) --------------------------- 34 1 rows fetched.

CONNECTION_ID

语法:

CONNECTION_ID()

功能:返回当前连接对应的会话ID。

说明:

  • 在同一时间点所有并发的连接之间的会话ID都是不同的。
  • 需要注意的是,如果一个连接断开后其对应的会话被后面新建的一个连接所复用,则对应的会话ID也会被复用。

示例:

返回当前连接会话ID。

SELECT CONNECTION_ID() from SYS_DUMMY; CONNECTION_ID() --------------- 48 1 rows fetched.

TYPE_ID2NAME

语法:

TYPE_ID2NAME(data_type_id)

功能:用于返回数据类型ID对应的数据类型名。

说明:

  • 该函数为诊断函数,如果数据类型ID不存在,则返回UNKNOWN_TYPE。
  • data_type_id为数据类型ID ,具体对应关系如下:

    data_type_id

    data_type_name

    20001

    BINARY_INTEGER

    20002

    BINARY_BIGINT

    20003

    BINARY_DOUBLE

    20004

    NUMBER

    20005

    DECIMAL

    20006

    DATE

    20007

    TIMESTAMP

    20008

    CHAR

    20009

    VARCHAR

    20010

    VARCHAR

    20011

    BINARY

    20012

    VARBINARY

    20013

    CLOB

    20014

    BLOB

    20015

    CURSOR

    20016

    COLUMN

    20017

    BOOLEAN

    20018

    TIMESTAMP_TZ

    20019

    TIMESTAMP_LTZ

    20020

    INTERVAL

    20021

    INTERVAL YEAR TO MONTH

    20022

    INTERVAL DAY TO SECOND

    20023

    RAW

    20024

    IMAGE

    20025

    INTEGER UNSIGNED

    20026

    BIGINT UNSIGNED

    20027

    SMALLINT

    20028

    SMALLINT UNSIGNED

    20029

    TINYINT

    20030

    TINYINT UNSIGNED'

示例:

返回数据类型ID对应的数据类型名。

select TYPE_ID2NAME(20029); TYPE_ID2NAME(20029) ---------------------------------------------------------------- TINYINT 1 rows fetched.

DECODE_NAME

语法:

DECODE_NAME(INDEX_NAME)

功能:去除相同索引或约束的oid部分。该功能暂时未启用。

FOUND_ROWS

语法:

FOUND_ROWS()

功能:返回一条带有LIMIT子句的SELECT语句被执行用于筛选一部分结果的同时又想知道完整的结果集(即包含被LIMIT过滤的结果)的行数。

说明:

  • 如果在一条未指定SQL_CALC_FOUND_ROWS的SELECT语句后调用FOUND_ROWS(),则FOUND_ROWS()返回的行数就是SELECT语句自身返回的结果集行数,将不会包含受LIMIT子句影响被过滤的行数 。
  • 对于UNION或UNION ALL或MINUS语句,FOUND_ROWS()只对全局LIMIT子句有影响。对于各个SELECT子集中出现的LIMIT语句则不会产生影响。
  • 如果执行FOUND_ROWS()函数之前该会话尚未执行过SELECT语句,或之前执行的SELECT语句失败了,又或之前执行的是一个更新语句,这类情况下FOUND_ROWS()函数的返回值未定义。通常情况下会返回一个“0”。

示例:

返回staff_id>1的员工个数。
--删除表employee。 DROP TABLE IF EXISTS employee;
--创建表employee。 CREATE TABLE employee(staff_id INT NOT NULL, first_name VARCHAR(64));
--插入数据。 INSERT INTO employee(staff_id,first_name) values ('1', 'Alice'); INSERT INTO employee(staff_id,first_name) values ('2', 'Jack'); INSERT INTO employee(staff_id,first_name) values ('3', 'Brown'); --提交事务。 COMMIT;
--查找staff_id > 1的人。 SELECT * FROM employee where staff_id>1; STAFF_ID FIRST_NAME ------------ ---------------------------------------------------------------- 2 Jack 3 Brown 2 rows fetched. --返回满足条件的行数。 SELECT FOUND_ROWS(); FOUND_ROWS() -------------------- 2 1 rows fetched.

GET_DISTRIBUTE_STR

该函数只在分布式场景使用,单机场景返回为空。

GET_LOCK

语法:

GET_LOCK(name_expr [, timeout_expr])

功能:GET_LOCK() 为一个会话获取一把锁名为name_expr的咨询锁(advisory lock),之后另一个会话若也调用GET_LOCK()尝试获取同名锁时,将会等待timeout_expr所指定的秒数。如果在此期间内第一个会话释放了锁,则第二个会话将能成功获取锁;如果时间超过指定的超时秒数后第一个会话仍未释放锁,则第二个会话获取锁的尝试失败。

GET_LOCK()的返回值如下:

  • 1: 成功获取到锁。
  • 0: 未能获取到锁。

通过GET_LOCK()获取到的锁可通过以下两种方式释放:

  • 显式释放:通过调用RELEASE_LOCK()释放。
  • 隐式释放:会话中断(不论正常或异常)时该会话占有的锁自动释放。

说明:

  • 表示锁名的name_expr的计算结果的字符串长度不能超过64字节。
  • 同一个会话同时最多可以加32把锁。
  • 同一会话可以多次对同一个锁名上锁,这种情况下,需要对该锁名解锁同样的次数。对同一锁名多次加锁时,第二次加锁开始的次数并不计入上述所说的32把锁的个数。
  • timeout_expr未指定或指定为一个负值时,GET_LOCK()获取不到锁时将一直等待。

示例:

对表中某一列进行加锁。

--删除表employee。 DROP TABLE IF EXISTS employee;
--创建表employee。 CREATE TABLE employee(staff_id INT NOT NULL, first_name VARCHAR(64));
--插入数据。 INSERT INTO employee(staff_id,first_name) values ('1', 'Alice'); INSERT INTO employee(staff_id,first_name) values ('2', 'Jack'); INSERT INTO employee(staff_id,first_name) values ('3', 'Brown'); --提交事务。 COMMIT;
--对'staff_id'列加锁。 SELECT GET_LOCK('staff_id',5); GET_LOCK('STAFF_ID',5) -------------------- 1 1 rows fetched.

TRY_GET_LOCK

语法:

TRY_GET_LOCK(name_expr)

功能:TRY_GET_LOCK(name_expr) 为一个会话尝试获取一把锁名为name_expr的排他咨询锁(exclusive advisory lock),若获取成功返回TRUE,之后另一个会话若也调用TRY_GET_LOCK(name_expr)尝试获取同名锁时,将会立刻返回FALSE,获取同名锁失败,直到该锁释放。

TRY_GET_LOCK()的返回值如下:

  • TRUE: 成功获取到锁。
  • FALSE: 未能获取到锁。

通过TRY_GET_LOCK(name_expr)获取到的锁可通过以下两种方式释放:

  • 显式释放:通过调用RELEASE_LOCK()释放。
  • 隐式释放:会话中断(不论正常或异常)时该会话占有的锁自动释放。

说明:

  • 表示锁名的name_expr的计算结果的字符串长度不能超过64字节。
  • 同一个会话同时最多可以加32把锁。
  • 同一会话可以多次对同一个锁名上锁,这种情况下,需要对该锁名解锁同样的次数。对同一锁名多次加锁时,第二次加锁开始的次数并不计入上述所说的32把锁的个数。

示例:

对表中某一列进行加锁。

--删除表employee。 DROP TABLE IF EXISTS employee;
--创建表employee。 CREATE TABLE employee(staff_id INT NOT NULL, first_name VARCHAR(64));
--插入数据。 INSERT INTO employee(staff_id,first_name) values ('1', 'Alice'); INSERT INTO employee(staff_id,first_name) values ('2', 'Jack'); INSERT INTO employee(staff_id,first_name) values ('3', 'Brown'); --提交事务。 COMMIT;
--对'staff_id'列加锁。 SELECT TRY_GET_LOCK('staff_id'); TRY_GET_LOCK('STAFF_ID') -------------------- TRUE 1 rows fetched.

GET_SHARED_LOCK

语法:

GET_SHARED_LOCK(name_expr [, timeout_expr])

功能:GET_SHARED_LOCK()为一个会话获取一把锁名为name_expr的共享咨询锁,之后另一个会话也可调用GET_SHARED_LOCK()获取该同名锁;如果该锁已被其他会话锁为排他咨询锁,则将会等待参数timeout_expr所指定的秒数结束后获取锁失败,在此期间内若该排他锁被释放了,或只有本会话使用该锁,则获取该锁成功。

GET_SHARED_LOCK()的返回值如下:

  • TRUE: 成功获取到锁。
  • FALSE: 未能获取到锁。

通过GET_SHARED_LOCK()获取到的锁可通过以下两种方式释放:

  • 显式释放:通过调用RELEASE_SHARED_LOCK(name_expr)释放。
  • 隐式释放:会话中断(不论正常或异常),时该会话占有的锁自动释放。

说明:

  • 表示锁名的name_expr的计算结果的字符串长度不能超过64字节。
  • 同一个会话同时最多可以加32把锁。
  • 同一会话可以多次对同一个锁名上锁,需要对该锁名解锁同样的次数。对同一锁名多次加锁时,第二次加锁开始的次数并不计入上述所说的32把锁的个数。
  • timeout_expr未指定或指定为一个负值时,GET_SHARED_LOCK()获取不到锁时将一直等待。
  • TRY_GET_SHARED_LOCK(name_expr)尝试获取名为name_expr的会话级共享咨询锁,无论获取成功或失败都立刻返回结果。

示例:

对表中某一列进行加锁。

--删除表employee。 DROP TABLE IF EXISTS employee;
--创建表employee。 CREATE TABLE employee(staff_id INT NOT NULL, first_name VARCHAR(64));
--插入数据。 INSERT INTO employee(staff_id,first_name) values ('1', 'Alice'); INSERT INTO employee(staff_id,first_name) values ('2', 'Jack'); INSERT INTO employee(staff_id,first_name) values ('3', 'Brown'); --提交事务。 COMMIT;
--对'staff_id'列加锁。 SELECT GET_SHARED_LOCK('staff_id',5); GET_SHARED_LOCK('STAFF_ID',5) -------------------- TRUE 1 rows fetched.

GET_XACT_LOCK

语法:

GET_XACT_LOCK(name_expr)

功能:GET_XACT_LOCK(name_expr) 为一个事务获取一把锁名为name_expr的排他咨询锁(exclusive advisory lock),若获取成功返回TRUE,之后另一个事务若也调用TRY_GET_LOCK(name_expr)获取同名锁时,将会一直等待,直到该锁释放。

GET_XACT_LOCK(name_expr)的返回值如下:

  • TRUE: 成功获取到锁。
  • FALSE: 未能获取到锁。

通过GET_XACT_LOCK(name_expr)获取到的锁可通过以下方式释放:

  • 隐式释放: 事务/会话中断(不论正常或异常)时该事务/会话占有的锁自动释放。

说明:

  • 表示锁名的name_expr的计算结果的字符串长度不能超过64字节。
  • 同一个事务同时最多可以加32把锁。
  • 同一事务可以多次对同一个锁名上锁,在事务/会话结束时会主动释放,无需显示释放。对同一锁名多次加锁时,第二次加锁开始的次数并不计入上述所说的32把锁的个数中。

示例:

对表中某一列进行加锁。

--删除表employee。 DROP TABLE IF EXISTS employee;
--创建表employee。 CREATE TABLE employee(staff_id INT NOT NULL, first_name VARCHAR(64));
--插入数据。 INSERT INTO employee(staff_id,first_name) values ('1', 'Alice'); INSERT INTO employee(staff_id,first_name) values ('2', 'Jack'); INSERT INTO employee(staff_id,first_name) values ('3', 'Brown'); --提交事务。 COMMIT;
--对'staff_id'列加锁。 SELECT GET_XACT_LOCK('staff_id'); GET_XACT_LOCK('STAFF_ID') -------------------- TRUE 1 rows fetched.

TRY_GET_XACT_LOCK

语法:

TRY_GET_XACT_LOCK(name_expr)

功能:TRY_GET_XACT_LOCK(name_expr) 为一个事务尝试获取一把锁名为name_expr的排他咨询锁(exclusive advisory lock),若获取成功返回TRUE,之后另一个事务若也调用TRY_GET_LOCK(name_expr)尝试获取同名锁时,将会立刻返回FALSE,获取同名锁失败,直到该锁释放。

TRY_GET_XACT_LOCK()的返回值如下:

  • TRUE: 成功获取到锁。
  • FALSE: 未能获取到锁。

通过TRY_GET_XACT_LOCK(name_expr)获取到的锁可通过以下方式释放:

  • 隐式释放: 事务/会话中断(不论正常或异常)时该事务/会话占有的锁自动释放。

说明:

  • 表示锁名的name_expr的计算结果的字符串长度不能超过64字节。
  • 同一个事务同时最多可以加32把锁。
  • 同一事务可以多次对同一个锁名上锁,在事务/会话结束时会主动释放,无需显示释放。对同一锁名多次尝试加锁时,若第一次加锁成功,则第二次加锁开始的次数并不计入上述所说的32把锁的个数中。

示例:

对表中某一列进行加锁。

--删除表employee。 DROP TABLE IF EXISTS employee;
--创建表employee。 CREATE TABLE employee(staff_id INT NOT NULL, first_name VARCHAR(64));
--插入数据。 INSERT INTO employee(staff_id,first_name) values ('1', 'Alice'); INSERT INTO employee(staff_id,first_name) values ('2', 'Jack'); INSERT INTO employee(staff_id,first_name) values ('3', 'Brown'); --提交事务。 COMMIT;
--对'staff_id'列加锁。 SELECT TRY_GET_XACT_LOCK('staff_id'); TRY_GET_XACT_LOCK('STAFF_ID') -------------------- TRUE 1 rows fetched.

GET_XACT_SHARED_LOCK

语法:

GET_XACT_SHARED_LOCK(name_expr [, timeout_expr])

功能:GET_XACT_SHARED_LOCK(name_expr[, timeout_expr]) 为一个事务获取一把锁名为name_expr的共享咨询锁,若获取成功返回TRUE,之后另一个事务也可调用GET_XACT_SHARED_LOCK(name_expr)获取该同名锁,如果该同名锁被其他事务锁为排他咨询锁时,将会等待timeout_expr秒,在此期间如果该排他锁被释放,则获取成功,否则获取失败。

GET_XACT_SHARED_LOCK(name_expr[, timeout_expr])的返回值如下:

  • TRUE: 成功获取到锁。
  • FALSE: 未能获取到锁。

通过GET_XACT_SHARED_LOCK(name_expr[, timeout_expr])获取到的锁可通过以下方式释放:

  • 隐式释放: 事务/会话中断(不论正常或异常)时该事务/会话占有的锁自动释放。
  • 通过TRY_GET_XACT_SHARED_LOCK(name_expr)尝试获取名为name_expr的共享咨询锁,无论获取成功或者失败都立刻返回。

说明:

  • 表示锁名的name_expr的计算结果的字符串长度不能超过64字节。
  • 同一个事务同时最多可以加32把锁。
  • 同一事务可以多次对同一个锁名上锁,在事务/会话结束时会主动释放,无需显示释放。对同一锁名多次加锁时,第二次加锁开始的次数并不计入上述所说的32把锁的个数中。

示例:

对表中某一列进行加锁。

--删除表employee。 DROP TABLE IF EXISTS employee;
--创建表employee。 CREATE TABLE employee(staff_id INT NOT NULL, first_name VARCHAR(64));
--插入数据。 INSERT INTO employee(staff_id,first_name) values ('1', 'Alice'); INSERT INTO employee(staff_id,first_name) values ('2', 'Jack'); INSERT INTO employee(staff_id,first_name) values ('3', 'Brown'); --提交事务。 COMMIT;
--对'staff_id'列加锁。 SELECT GET_XACT_SHARED_LOCK('staff_id',5); GET_XACT_SHARED_LOCK('STAFF_ID',5) -------------------- TRUE 1 rows fetched.

GREATEST

语法:

GREATEST( expr1 [, expr2, ... expr_n] )

功能:GREATEST函数返回一个或多个表达式中的最大的值。

  • 返回值类型与expr1的数据类型相同。
  • expr1:要评估的第一个表达式是否是最大的。
  • expr2,... expr_n可选的,要评估的其他表达式。

示例1:

SELECT GREATEST(2, 5, 12, 3); GREATEST(2, 5, 12, 3) --------------------- 12 1 rows fetched.

示例2:

SELECT GREATEST('2', '5', '12', '3'); GREATEST('2', '5', '12', '3') ----------------------------- 5 1 rows fetched.

示例3:

SELECT GREATEST('apples', 'oranges', 'bananas'); GREATEST('APPLES', 'ORANGES', 'BANANAS') ---------------------------------------- oranges 1 rows fetched.

示例4:

SELECT GREATEST('apples', 'applis', 'applas'); GREATEST('APPLES', 'APPLIS', 'APPLAS') -------------------------------------- applis 1 rows fetched.

HASH

语法

HASH(arg1[, arg2, ... arg_n])

功能:根据所有入参计算哈希值。

说明

  • 入参个数最大不超过64个。
  • 入参数据类型不支持blob、clob和长度大于8000字节的字符串或二进制字节流。

示例

SELECT HASH('abc'); HASH('ABC') ------------- 2934528074 1 rows fetched.

ISNUMERIC

语法:

ISNUMERIC(str)

功能:判断入参str是否可以被转换成数字。若可以转换则返回1,否则返回0。

说明

  • 入参是数值型或者字符串。
  • 入参不能为$。

示例1:

SELECT ISNUMERIC('1' + 0) from SYS_DUMMY; ISNUMERIC('1' + 0) --------------------- 1 1 rows fetched.

示例2:

SELECT ISNUMERIC('a' || '1') from SYS_DUMMY; ISNUMERIC('A' || '1') --------------------- 0 1 rows fetched.

LAST_INSERT_ID

语法:

LAST_INSERT_ID([expr])

功能:

  • 参数为空时:返回当前回话最后一次INSERT语句AUTO_INCREMENT列自动生成的值。
  • 如果指定expr参数,则该函数返回参数的值,并将其记住为LAST_INSERT_ID() 返回的下一个值。

示例:

返回最后一条INSERT语句AUTO_INCREMENT列自动生成的值。

--删除表employee。 DROP TABLE IF EXISTS employee;
--创建表employee。 CREATE TABLE employee(staff_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,first_name VARCHAR(10));
--插入数据。 INSERT INTO employee VALUES (NULL, 'Bob'); INSERT INTO employee VALUES (NULL, 'BROWN'); INSERT INTO employee VALUES (NULL, 'ALICE');
--返回最后一条INSERT语句AUTO_INCREMENT列自动生成的值。 SELECT LAST_INSERT_ID(); LAST_INSERT_ID() -------------------- 3 1 rows fetched.

LEAST

语法:

LEAST( expr1 [, expr2, ... expr_n] )

功能:LEAST函数返回一个或多个表达式中的最小的值。

  • 返回值类型与最小的入参expr_n的数据类型相同。
  • expr1要评估的第一个表达式是否是最小的。
  • expr2,... expr_n可选的。要评估的其他表达式。

示例1:

SELECT LEAST(2, 5, 12, 3); LEAST(2, 5, 12, 3) ------------------ 2 1 rows fetched.

示例2

SELECT LEAST('apples', 'oranges', 'bananas'); LEAST('APPLES', 'ORANGES', 'BANANAS') ------------------------------------- apples 1 rows fetched.

示例3

SELECT LEAST('apples', 'applis', 'applas'); LEAST('APPLES', 'APPLIS', 'APPLAS') ----------------------------------- applas 1 rows fetched.

MD5

语法:

md5([expr])

功能:MD5函数将输入的参数expr按照md5算法加密,并输出密文。

  • 参数为NULL时,返回NULL。
  • 如果指定expr参数,则返回expr的md5值。

示例:

--将字符串“gauss”按照MD5算法加密。 select md5('gauss') from SYS_DUMMY; MD5('GAUSS') -------------------------------- 710a4950250286365cf841f765a790f1 1 rows fetched.
--创建表t_md5_test。 create table t_md5_test(f1 int,f2 real,f3 blob,f4 numeric(4,1),f5 varchar(10)); Succeed.
--向表t_md5_test插入数据。 insert into t_md5_test values(2147483648-1,2.345,'100100111111',2.345,'aabbcc'); 1 rows affected.
--加密表中数据列f1,f2,f3,f4,f5。 select md5(f1),md5(f2),md5(f3),md5(f4),md5(f5) from t_md5_test; MD5(F1) MD5(F2) MD5(F3) MD5(F4) MD5(F5) -------------------------------- -------------------------------- -------------------------------- -------------------------------- -------------------------------- c588c0a459f4ccc6f3dd26518d24707a 972da5c9c62440f43c8ad9c672e8bf36 3c96ce254c3e76d02e6959f19609c6dc 1a18da63cbbfb49cb9616e6bfd35f662 61a60170273e74a5be90355ffe8e86ad 1 rows fetched.

OBJECT_ID

语法:

OBJECT_ID(expr[, object_type [, object_owner]])

功能:根据用户指定的数据库对象名(第一参数),数据库对象种类以及对象的owner返回视图USER_OBJECTS中满足指定条件的数据库对象的OBJECT_ID,若owner没有指定,该函数会根据当前会话的用户名作为owner进行查找。若根据参数指定的条件没有找到条件符合的数据库对象,则该函数返回NULL。

说明:

当前版本中可以指定筛选的数据库对象包括以下范围:

  • TABLE(默认值)
  • VIEW
  • DYNAMIC VIEW
  • PROCEDURE
  • TRIGGER
  • FUNCTION

另外,由于GaussDB 100中的数据库对象没有完全的全局唯一标识,因此返回的OBJECT_ID并不能保证全局唯一。只能保证在所指定的同一数据库对象类型中唯一。

示例:

创建一个表,查找表的OBJECT_ID。

--删除表employee。 DROP TABLE IF EXISTS employee;
--创建表employee。 CREATE TABLE employee(staff_id INT NOT NULL, first_name VARCHAR(64));
--插入数据。 INSERT INTO employee(staff_id,first_name) values ('1', 'Alice'); INSERT INTO employee(staff_id,first_name) values ('2', 'Jack'); INSERT INTO employee(staff_id,first_name) values ('3', 'Brown'); --提交事务。 COMMIT;
--查找表employee的OBJECT_ID。 SELECT OBJECT_ID('employee','TABLE'); OBJECT_ID('EMPLOYEE','TABLE') ----------------------------- 2070 1 rows fetched.

RELEASE_LOCK

语法:

RELEASE_LOCK(name_expr)

功能:通过锁名释放会话先前使用GET_LOCK()函数上的锁。

RELEASE_LOCK()的返回值如下:

  • 1: 成功获取所指定的锁。
  • NULL: 当前会话并不占有所指定的锁。

示例:

对表中的某一列加锁后解锁。

--删除表employee。 DROP TABLE IF EXISTS employee;
--创建表employee。 CREATE TABLE employee(staff_id INT NOT NULL, first_name VARCHAR(64));
--插入数据。 INSERT INTO employee(staff_id,first_name) values ('1', 'Alice'); INSERT INTO employee(staff_id,first_name) values ('2', 'Jack'); INSERT INTO employee(staff_id,first_name) values ('3', 'Brown'); --提交事务。 COMMIT;
--对'staff_id'列加锁。 SELECT GET_LOCK('staff_id',5); GET_LOCK('STAFF_ID',5) ---------------------- 1 1 rows fetched.
--对''staff_id'列解锁。 SELECT RELEASE_LOCK('staff_id'); RELEASE_LOCK('STAFF_ID') ---------------------- 1 1 rows fetched.

ROW_NUMBER() OVER

语法:

ROW_NUMBER() OVER (partition by expr order by expr)

功能:只能用于column list中,对查询返回的数据先进行分组再进行排序,然后分组打上排序序号。

示例:

根据部门分组,显示每个部门的工资等级。

--删除表employee。 DROP TABLE IF EXISTS employee;
--创建表employee。 CREATE TABLE employee (staff_id INT,section_id INT,max_salary NUMBER(10,2));
--插入数据。 INSERT INTO employee values(1,10,5500.00); INSERT INTO employee values(2,10,4500.00); INSERT INTO employee values(3,20,1900.00); INSERT INTO employee values(4,20,4800.00); INSERT INTO employee values(5,40,6500.00); INSERT INTO employee values(6,40,14500.00); INSERT INTO employee values(7,40,44500.00); --提交事务。 COMMIT;
--按部门查询员工工资,并进行排序。 SELECT *, Row_Number() OVER (partition by section_id ORDER BY max_salary desc) rank FROM employee; STAFF_ID SECTION_ID MAX_SALARY RANK ------------ ------------ ---------------------------------------- ------------ 1 10 5500 1 2 10 4500 2 4 20 4800 1 3 20 1900 2 7 40 44500 1 6 40 14500 2 5 40 6500 3 7 rows fetched.

SCN2DATE

语法:

SCN2DATE(scn)

功能:由scn值转义为时间值。

说明

  • scn值需要合法值,通过查询对象相关的视图可以得到。

示例:

返回scn值对应的时间值。

select scn2date(t.org_scn) from sys.SYS_TABLES t where t.name = 'TEST'; SCN2DATE(T.ORG_SCN) ---------------------- 2019-01-10 20:35:38 1 rows fetched.

SERIAL_LASTVAL

语法:

SERIAL_LASTVAL('OWNER','TABLE_NAME')

功能:返回表的自增列的缓存值。

  • OWNER为表的owner。
  • TABLE_NAME为表名。

说明

  • OWNERTABLE_NAME必须为大写,且需要用引号括起来。
  • 表如果不包含自增列,函数将返回报错:
    GS-00866,the table has no auto increment column。

示例:

返回包含自增列的表employee的AUTO_INCREMENT列的缓存值。

--删除表employee。 DROP TABLE IF EXISTS employee;
--创建表employee。 CREATE TABLE employee (staff_id INT AUTO_INCREMENT primary key ,section_id INT,max_salary NUMBER(10,2)) AUTO_INCREMENT 1000;
--返回表employee的自增列的缓存值。 SELECT SERIAL_LASTVAL('SYS','EMPLOYEE'); SERIAL_LASTVAL('SYS','EMPLOYEE') -------------------- 1000 1 rows fetched.

SHA

不建议在安全场景中使用该函数。

语法:

SHA(str_expr)

功能:为传入的参数按SHA算法生成一个定长的HASH值,并将该HASH值按字符串形式返回(40字节)。函数SHA1的别名,功能与SHA1相同。

说明:

  • str_expr为字符串表达式,长度超过8000会报错。
  • 传入的值为NULL时,该函数也返回NULL。

示例:

返回abc的HASH值。

SELECT SHA('abc'); SHA('ABC') -------------------- A9993E364706816ABA3E25717850C26C9CD0D89D 1 rows fetched.

SHA1

不建议在安全场景中使用该函数。

语法:

SHA1(str_expr)

功能:为传入的参数按SHA1算法生成一个定长的HASH值,并将该HASH值按字符串形式返回(40字节)。

说明:

  • str_expr为字符串表达式,长度超过8000会报错。
  • 传入的值为NULL时,该函数也返回NULL。

示例:

返回abc的HASH值。

SELECT SHA1('abc'); SHA1('ABC') -------------------- A9993E364706816ABA3E25717850C26C9CD0D89D 1 rows fetched.

SOUNDEX

语法:

SOUNDEX(expr)

功能:返回字符串参数的语音表示形式。

示例:

返回姓氏发音与SMYTHE相同的员工姓名。

--删除表employee。 DROP TABLE IF EXISTS employee;
--创建表employee。 CREATE TABLE employee (last_name varchar(20) ,first_name varchar(20));
--插入数据。 INSERT INTO employee(last_name,first_name) values('Smith','Lindsey'); INSERT INTO employee(last_name,first_name) values('Smith','William'); INSERT INTO employee(last_name,first_name) values('Brown','Bill');
--姓氏发音与SMYTHE相同的员工姓名。 SELECT last_name, first_name FROM employee WHERE SOUNDEX(last_name) = SOUNDEX('SMYTHE') ORDER BY last_name, first_name; LAST_NAME FIRST_NAME -------------------- -------------------- Smith Lindsey Smith William 2 rows fetched.

SYS_CONTEXT

语法:

SYS_CONTEXT(namespace_expr, parameter_expr [, length])

功能:返回指定的namespace关联的上下文中的配置参数(parameter)值。

在当前版本中支持的namespace和parameter如下:

namespace名: USERENV,该namespace下支持的参数为:
  • SID:当前连接对应的会话ID。与CONNECTION_ID()类似,如果一个连接断开后其对应的会话被后面新建的一个连接所复用,则对应的会话ID也会被复用。
  • TERMINAL:当前session的客户端机器的HOST名。
  • CURRENT_SCHEMA:当前查询所执行的默认SCHEMA名。
  • CURRENT_SCHEMAID:当前查询所执行的默认SCHEMA ID。
  • DB_NAME:当前数据库的名称。
  • OS_USER:当前连接的客户端所在操作系统用户名。

说明:

  • namespace_exprparameter_expr必须是计算结果可转换为字符串的表达式。
  • 返回值类型为字符串,默认长度为256字节。超长部分被截断。
  • 第三参数length表示的是该函数返回值的字符串最大长度,length必须是一个可以隐式转换为INTEGER类型的表达式。length的值的有效长度为1~4000,若用户指定的length不在此范围内,则仍然取默认值256字节

示例1:

返回当前会话ID。

SELECT SYS_CONTEXT('USERENV', 'SID') FROM SYS_DUMMY; SYS_CONTEXT('USERENV', 'SID') ---------------------------------------------------------------- 49 1 rows fetched.

示例2:

返回当前session的客户端机器的HOST名。

SELECT SYS_CONTEXT('USERENV', 'TERMINAL') FROM SYS_DUMMY; SYS_CONTEXT('USERENV', 'TERMINAL') ---------------------------------------------------------------- 127.0.0.1 1 rows fetched.

示例3:

返回当前查询所执行的默认SCHEMA名。

SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM SYS_DUMMY; SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') ---------------------------------------------------------------- omm 1 rows fetched.

示例4:

返回当前查询所执行的默认SCHEMA ID。

SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMAID') FROM SYS_DUMMY; SYS_CONTEXT('USERENV', 'CURRENT_SCHEMAID') ---------------------------------------------------------------- 2 1 rows fetched.

示例5:

返回当前数据库的名称。

SELECT SYS_CONTEXT('USERENV', 'DB_NAME') FROM SYS_DUMMY; SYS_CONTEXT('USERENV', 'DB_NAME') ---------------------------------------------------------------- GaussDB 1 rows fetched.

示例6:

返回当前连接的客户端所在操作系统用户名。

SELECT SYS_CONTEXT('USERENV', 'OS_USER') FROM SYS_DUMMY; SYS_CONTEXT('USERENV', 'OS_USER') ---------------------------------------------------------------- omm 1 rows fetched.

SYS_GUID

语法:

SYS_GUID()

功能:生成一个16字节的全局唯一标识(global unique identifier),返回值类型是BINARY。

说明:若要用字符串类型字段接收SYS_GUID()的结果,由于二进制字节转换为字符串是按16进制表示,因此需要将字段定义为32字节及以上。

SYS_GUID函数功能和UUID函数相同,差别仅在于:

  • SYS_GUID函数用于GaussDB 100 1.0.0SPC200之前版本。
  • UUID函数用于GaussDB 100 1.0.0SPC200及之后版本。

示例:

创建一个表格,以全局唯一标识作为主键。

--删除表employee。 DROP TABLE IF EXISTS employee;
--创建表employee。 CREATE TABLE employee(staff_id raw(16) default sys_guid() primary key, first_name VARCHAR(32));
--插入数据。 INSERT INTO employee(first_name) values ( 'GREECE '); INSERT INTO employee(first_name) values ( 'ALAN'); INSERT INTO employee(first_name) values ( 'FRANK '); --提交事务。 COMMIT;
--查询表格。 SELECT * FROM employee; STAFF_ID FIRST_NAME ---------------------------------------------------------------- -------------------------------- B5E49C6E665846A9BF7F00794748AA50 GREECE 2698BC648AC247968DC987555DEAB179 ALAN 46540BE9789E4BF49D0EA87705A444CC FRANK 3 rows fetched.

UUID

语法:

UUID()

功能:生成一个16字节的全局唯一标识(global unique identifier),返回值类型是BINARY。

说明:若要用字符串类型字段接收UUID()的结果,由于二进制字节转换为字符串是按16进制表示,因此需要将字段定义为32字节及以上。

UUID函数功能和SYS_GUID函数相同,差别仅在于:

  • SYS_GUID函数用于GaussDB 100 1.0.0SPC200之前版本。
  • UUID函数用于GaussDB 100 1.0.0SPC200及之后版本。

示例:

创建一个表格,以全局唯一标识作为主键。

--删除表employee。 DROP TABLE IF EXISTS employee;
--创建表employee。 CREATE TABLE employee(staff_id raw(16) default uuid() primary key, first_name VARCHAR(32));
--插入数据。 INSERT INTO employee(first_name) values ( 'GREECE '); INSERT INTO employee(first_name) values ( 'ALAN'); INSERT INTO employee(first_name) values ( 'FRANK '); --提交事务。 COMMIT;
--查询表格。 SELECT * FROM employee; STAFF_ID FIRST_NAME ---------------------------------------------------------------- -------------------------------- B5E49C6E665846A9BF7F00794748AA50 GREECE 2698BC648AC247968DC987555DEAB179 ALAN 46540BE9789E4BF49D0EA87705A444CC FRANK 3 rows fetched.

UPDATING

语法:

UPDATING(col_name)

功能:在update的触发器中使用,用于判断该列上是否进行过UPDATE操作。

示例

判断触发器的列是否发生变化更新,如果发生该列发生变化数据更新,则抛出异常。

--删除已存在的同名表training。 DROP TABLE IF EXISTS training;
--创建同名表training。 CREATE TABLE training(staff_id INT NOT NULL,course_name CHAR(50),course_start_date DATETIME, course_end_date DATETIME,exam_date DATETIME,score INT);
--创建触发器trigger_training。 CREATE OR REPLACE TRIGGER trigger_training BEFORE UPDATE ON training FOR EACH ROW AS wrong_error exception; errno NUMBER; errmsg VARCHAR2(30); BEGIN IF UPDATING('course_name') THEN errno := '-20030'; errmsg := 'cannot update this column'; RAISE wrong_error; END IF; EXCEPTION WHEN wrong_error THEN raise_application_error(errno,errmsg); END; /

USERENV

语法:

USERENV(parameter_expr)

功能:是SYS_CONTEXT()的向下兼容版本,返回的是默认命名空间"USERENV"的配置参数的值。

在当前版本中支持的parameter如下:

  • SID:当前连接对应的会话ID。与CONNECTION_ID()类似,如果一个连接断开后其对应的会话被后面新建的一个连接所复用,则对应的会话ID也会被复用。
  • TERMINAL:当前session的客户端机器的HOST名。
  • CURRENT_SCHEMA:当前查询所执行的默认SCHEMA名。
  • CURRENT_SCHEMAID:当前查询所执行的默认SCHEMA ID。
  • DB_NAME:当前数据库的名称。
  • OS_USER:当前连接的客户端所在操作系统用户名。

说明:parameter_expr必须是计算结果可转换为字符串的表达式。返回值类型为字符串,最大长度为256字节,超长部分被截断。

示例1:

返回当前会话ID。

SELECT SYS_CONTEXT('USERENV', 'SID') FROM SYS_DUMMY; SYS_CONTEXT('USERENV', 'SID') ---------------------------------------------------------------- 49 1 rows fetched.

示例2:

返回当前session的客户端机器的HOST名。

SELECT SYS_CONTEXT('USERENV', 'TERMINAL') FROM SYS_DUMMY; SYS_CONTEXT('USERENV', 'TERMINAL') ---------------------------------------------------------------- 127.0.0.1 1 rows fetched.

示例3:

返回当前查询所执行的默认SCHEMA名。

SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM SYS_DUMMY; SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') ---------------------------------------------------------------- omm 1 rows fetched.

示例4:

返回当前查询所执行的默认SCHEMA ID。

SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMAID') FROM SYS_DUMMY; SYS_CONTEXT('USERENV', 'CURRENT_SCHEMAID') ---------------------------------------------------------------- 2 1 rows fetched.

示例5:

返回当前数据库的名称。

SELECT SYS_CONTEXT('USERENV', 'DB_NAME') FROM SYS_DUMMY; SYS_CONTEXT('USERENV', 'DB_NAME') ---------------------------------------------------------------- GaussDB 1 rows fetched.

示例6:

返回当前连接的客户端所在操作系统用户名。

SELECT SYS_CONTEXT('USERENV', 'OS_USER') FROM SYS_DUMMY; SYS_CONTEXT('USERENV', 'OS_USER') ---------------------------------------------------------------- omm 1 rows fetched.

VERSION

语法:

VERSION()

功能:获取当前的版本信息。

示例:

获取当前的版本信息。

SELECT VERSION(); VERSION() -------------------------------------------------- GaussDB-100-1.0.0B300 Release c87fe47 1 rows fetched.

VSIZE

语法:

VSIZE(expr)

功能:返回指定表达式的值在GaussDB 100内部存储时所占的字节数。

说明:

  • 当表达式是NULL时,该函数也返回一个NULL。
  • 该函数不支持传入的参数表达式的值类型是CLOB类型。

示例:

返回字符Alice在GaussDB 100内部存储时所占的字节数。

SELECT VSIZE('Alice') FROM SYS_DUMMY; VSIZE('ALICE') -------------------- 5 1 rows fetched.
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论