[入门介绍] Gauss 100 数据库函数介绍之其他函数Part1

其他函数

BIN2HEX

语法:

BIN2HEX(expr)

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

数据类型

说明

GS_TYPE_CHAR

char类型

GS_TYPE_VARCHAR

varchar类型

GS_TYPE_STRING

string类型

GS_TYPE_VARBINARY

varbinary类型

GS_TYPE_BINARY

binary类型

GS_TYPE_RAW

raw类型

示例:

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

SELECT BIN2HEX('A123') from DUAL;

BIN2HEX('A123')
---------------
0x41313233

1 rows fetched.

CHAR_LENGTH

语法:

CHAR_LENGTH(str)

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

  • 字符串str需要以单引号括起来。

  • 如果入参是数字则被隐式转为字符串。数字不能超出40位(超出了数值转换的最大值),超出不保证结果正确性。

示例:

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

SELECT CHAR_LENGTH('character') FROM DUAL;

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 DUAL;

CONNECTION_ID()
---------------
48

1 rows fetched.

DBA_IND_POS

语法:

DBA_IND_POS('column_list','column_id')

功能:属于DBA诊断函数,用于返回列ID在列ID集合的位置。

说明:

  • 该函数为诊断函数,非DBA角色的用户不能直接调用该函数。

  • 如果column_list中没有column_id,则返回0。

  • 位置从1开始计数。

  • column_list为字符串类型,是column_id的集合,ID之间以英文逗号分割。单个column_id为数值类型。数值取值范围[-9223372036854775808, 9223372036854775807]

  • column_id为数值类型。数值取值范围[-9223372036854775808, 9223372036854775807]

示例:

返回column_id在column_list的位置。

SELECT DBA_IND_POS('2,3,1,0,4','4') from dual;

DBA_IND_POS('2,3,1,0,4','4')
----------------------------
5

1 rows fetched.

DBA_LISTCOLS

语法:

DBA_LISTCOLS(user_name,table_name|view_name, column_list)

功能:属于DBA诊断函数,将用户名、表名或视图名下的列集合由列ID形式转化为列名集合的形式。

说明:

  • 该函数为诊断函数,非DBA角色的用户不能直接调用该函数。

  • user_name为字符串类型,必须是数据库中已经存在的用户名。

  • table_name|view_name为字符串类型,必须是数据库中在对应用户下已经存在的表名和视图名。

  • column_list为字符串类型,是列ID的集合,注意不要出现表中不存在的列。

示例:

返回列名集合。下面以GAUSSDBA用户为例,GAUSSDBA为数据库管理员用户。

CREATE TABLE GAUSSDBA.TEST(A INT, B INT);
SELECT DBA_LISTCOLS('GAUSSDBA','TEST','0,1');

DBA_LISTCOLS('GAUSSDBA','TEST','0,1')
----------------------------------------------------------------
A, B

1 rows fetched.

DBA_PARTITIONED_INDSIZE

语法:

DBA_PARTITIONED_INDSIZE(size_type,user_name,table_name,index_name)

功能:属于DBA诊断函数,用于返回分区表上索引的大小。

注意:

  • 在数据库参数UPPER_CASE_TABLE_NAMES值为TRUE(缺省值)情况下,数据库中的用户名、表名默认为大写,此函数中的参数是用户和表时,也必须输入大写名称,否则会报对象不存在错误。

  • 若数据库参数UPPER_CASE_TABLE_NAMES值为FALSE,或者用户通过双引号输入大小写敏感的表对象,那么函数中的参数大小写敏感,否则会报对象不存在错误。

说明:

  • 该函数为诊断函数,非DBA角色的用户不能直接调用该函数。

  • size_type:大小类型。

    • 0:字节数。

    • 1:页面数。

    • 2:EXTENTS数。

  • user_name为字符串类型,必须是数据库中已经存在的用户名。

  • table_name为字符串类型,必须是数据库中在对应用户下已经存在的表名,注意该表必须是分区表。

  • index_name为字符串类型,可选,如果不指定将返回分区表上所有索引的累加值。

  • 仅支持统计分区表上本地(local)索引的大小,全局索引大小统计为0。

示例:

返回分区表上索引累加后的总大小。
--预置创建分区表。
create table test_part_t1(f1 int, f2 real, f3 number, f4 char(30), f5 varchar(30), f6 date, f7 timestamp)
PARTITION BY RANGE(f1)
(
 PARTITION p1 values less than(10),
 PARTITION p2 values less than(20),
 PARTITION p3 values less than(30),
 PARTITION p4 values less than(MAXVALUE)
);
--预置创建分区表索引。
create index idx_t1_1 on test_part_t1(f2,f3);
create index idx_t1_2 on test_part_t1(f4,f5) local;
--预置加入分区表数据。
insert into test_part_t1 values(5, 15, 28, 'abcd', 'abcd', to_date('2018/01/24', 'YYYY/MM/DD'), to_timestamp('2018-01-24 16:00:00.00', 'YYYY-MM-DD HH24:MI:SS.FF3'));
insert into test_part_t1 values(6, 16, 29, '16', '29', to_date('2018/01/24', 'YYYY/MM/DD'), to_timestamp('2018-01-24 16:00:00.00', 'YYYY-MM-DD HH24:MI:SS.FF3'));
--查询分区表上索引累加后的总大小。
SELECT DBA_PARTITIONED_INDSIZE(0,'GAUSSDBA','TEST_PART_T1');

DBA_PARTITIONED_INDSIZE(0,'GAUSSDBA','TEST_PART_T1')
-----------------------------------------------
65536

1 rows fetched.

DBA_PARTITIONED_LOBSIZE

语法:

DBA_PARTITIONED_LOBSIZE(size_type,user_name,table_name,column_id)

功能:属于DBA诊断函数,用于返回分区表上LOB列的大小。

注意:

  • 在数据库参数UPPER_CASE_TABLE_NAMES值为TRUE(缺省值)情况下,数据库中的用户名、表名默认为大写,此函数中的参数是用户和表时,也必须输入大写名称,否则会报对象不存在错误。

  • 若数据库参数UPPER_CASE_TABLE_NAMES值为FALSE,或者用户通过双引号输入大小写敏感的表对象,那么函数中的参数大小写敏感,否则会报对象不存在错误。

说明:

  • 该函数为诊断函数,非DBA角色的用户不能直接调用该函数。

  • size_type:大小类型。

    • 0:字节数。

    • 1:页面数。

    • 2:EXTENTS数。

  • user_name为字符串类型,必须是数据库中已经存在的用户名。

  • table_name为字符串类型,必须是数据库中在对应用户下已经存在的表名,该表必须是分区表。

  • column_id为整型,列的ID值,可选,如果不指定将返回分区表上所有LOB列的累加值;指定情况下必须为LOB列所在列ID。

  • 默认lob数据如果消耗的存储空间不超过4000字节(含12-14字节的额外开销),则lob采用inline模式,将数据存储在heap segment中,不存储在lob segment中,因此本函数统计的结果,不包含inline模式存储的lob数据。

示例:

返回分区表上LOB列累加后的总大小。

--预置创建分区表。
create table test_part_t1(f1 int, f2 real, f3 number, f4 char(30), f5 varchar(30), f6 date, f7 timestamp)
PARTITION BY RANGE(f1)
(
 PARTITION p1 values less than(10),
 PARTITION p2 values less than(20),
 PARTITION p3 values less than(30),
 PARTITION p4 values less than(MAXVALUE)
);
--预置创建分区表索引。
create index idx_t1_1 on test_part_t1(f2,f3);
create index idx_t1_2 on test_part_t1(f4,f5) local;
--预置加入分区表数据。
insert into test_part_t1 values(5, 15, 28, 'abcd', 'abcd', to_date('2018/01/24', 'YYYY/MM/DD'), to_timestamp('2018-01-24 16:00:00.00', 'YYYY-MM-DD HH24:MI:SS.FF3'));
insert into test_part_t1 values(6, 16, 29, '16', '29', to_date('2018/01/24', 'YYYY/MM/DD'), to_timestamp('2018-01-24 16:00:00.00', 'YYYY-MM-DD HH24:MI:SS.FF3'));
--查询分区表上LOB列累加后的总大小。
SELECT DBA_PARTITIONED_LOBSIZE(0,'GAUSSDBA','TEST_PART_T1');

DBA_PARTITIONED_LOBSIZE(0,'GAUSSDBA','TEST_PART_T1')
-----------------------------------------------
0

1 rows fetched.

DBA_PARTITIONED_TABSIZE

语法:

DBA_PARTITIONED_TABSIZE(size_type,user_name,table_name)

功能:属于DBA诊断函数,用于返回分区表上的大小。

注意:

  • 在数据库参数UPPER_CASE_TABLE_NAMES值为TRUE(缺省值)情况下,数据库中的用户名、表名默认为大写,此函数中的参数是用户和表时,也必须输入大写名称,否则会报对象不存在错误。

  • 若数据库参数UPPER_CASE_TABLE_NAMES值为FALSE,或者用户通过双引号输入大小写敏感的表对象,那么函数中的参数大小写敏感,否则会报对象不存在错误。

说明:

  • 该函数为诊断函数,非DBA角色的用户不能直接调用该函数。

  • size_type:大小类型。

    • 0,字节数。

    • 1,页面数。

    • 2,EXTENTS数。

  • user_name为字符串类型,必须是数据库中已经存在的用户名。

  • table_name为字符串类型,必须是数据库中在对应用户下已经存在的表名,该表必须是分区表。

  • 在数据库参数UPPER_CASE_TABLE_NAMES缺省值为TRUE情况下,数据库中的用户名、表名默认为大写,此函数中的参数是用户和表时,也必须输入大写名称,否则会报对象不存在错误。若数据库参数UPPER_CASE_TABLE_NAMES为FALSE,或者用户通过双引号输入大小写敏感的表对象,那么函数中的参数大小写敏感,否则会报对象不存在错误。

示例:

返回分区表的大小。

--预置创建分区表。
create table test_part_t1(f1 int, f2 real, f3 number, f4 char(30), f5 varchar(30), f6 date, f7 timestamp)
PARTITION BY RANGE(f1)
(
 PARTITION p1 values less than(10),
 PARTITION p2 values less than(20),
 PARTITION p3 values less than(30),
 PARTITION p4 values less than(MAXVALUE)
);
--预置创建分区表索引。
create index idx_t1_1 on test_part_t1(f2,f3);
create index idx_t1_2 on test_part_t1(f4,f5) local;
--预置加入分区表数据。
insert into test_part_t1 values(5, 15, 28, 'abcd', 'abcd', to_date('2018/01/24', 'YYYY/MM/DD'), to_timestamp('2018-01-24 16:00:00.00', 'YYYY-MM-DD HH24:MI:SS.FF3'));
insert into test_part_t1 values(6, 16, 29, '16', '29', to_date('2018/01/24', 'YYYY/MM/DD'), to_timestamp('2018-01-24 16:00:00.00', 'YYYY-MM-DD HH24:MI:SS.FF3'));
--返回分区表的大小。
select DBA_PARTITIONED_TABSIZE(0,'GAUSSDBA','TEST_PART_T1');

DBA_PARTITIONED_TABSIZE(0,'GAUSSDBA','TEST_PART_T1')
-----------------------------------------------
65536

1 rows fetched.

DBA_TYPNAME

语法:

DBA_TYPNAME(data_type_id)

功能:属于DBA诊断函数,用于返回数据类型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 dba_typname(20029);

DBA_TYPNAME(20029)
----------------------------------------------------------------
TINYINT

1 rows fetched.