华为GaussDB T 其他函数

墨天轮 2019-10-12

1298

其他函数

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为表名。
说明：
OWNER和TABLE_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_expr和parameter_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.

数据类型	说明
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类型

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'

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

 领墨值

 
  有奖问卷
  意见反馈 
 客服小墨