link管理
链接快照平台
- 输入网页链接，自动生成快照
- 标签化管理网页链接
相关文章推荐
|
乐观的番茄 · mysql写日期循环_mob64ca12d0 ...· 3 天前 · |
|
|
喝醉的卤蛋 · SQL Server ...· 3 天前 · |
|
|
幸福的马铃薯 · mysql将某一个月所有天数构造出来_mys ...· 3 天前 · |
|
|
豪气的地瓜 · 8. 函数 — KingbaseES产品手册· 3 天前 · |
|
|
稳重的警车 · Fiji macro to select ...· 昨天 · |
|
|
坚强的板栗 · 使用wx.request发送HTTP请求 ...· 3 月前 · |
|
|
腼腆的围巾 · [CMake] Suppressing ...· 4 月前 · |
|
|
大力的枕头 · 中山首个“制储加”一体化加氢站试运营，首批本 ...· 6 月前 · |
|
|
悲伤的麻辣香锅 · 使用 Stateflow ...· 6 月前 · |
|
|
微笑的企鹅 · 看到经济内生的力量，就更有可能在未来抓住发展 ...· 10 月前 · |
link管理
›
8. 函数 — KingbaseES产品手册
| table director test select |
| https://help.kingbase.com.cn/v8.6.8.14/development/sql-plsql/sql/Function.html |
|
豪气的地瓜
3 天前 |
大多数标量函数在给定 null 参数时返回 null。您可以使用该NVL函数在发生 null 时返回一个值。例如，如果为空，则表达式NVL(commission_pct,0)返回 0，如果commission_pct不为空，则返回 的值commission_pct。
有关聚集函数如何处理空值的信息，请参阅
8.1.2.
SQL函数的语法
在 SQL 函数的语法图中，参数由它们的数据类型表示。当参数function出现在 SQL 语法中时，将其替换为本节中描述的函数之一。函数按其参数和返回值的数据类型分组。
将 SQL 函数应用于 LOB 列时，KingbaseES 数据库会在 SQL 和 PL/SQL 处理期间创建临时 LOB。您应该确保临时表空间配额足以为您的应用程序存储这些临时 LOB。
SQL 函数可能对排序规则敏感，这意味着它执行的字符值比较或匹配由排序规则控制。函数使用的特定排序规则由函数参数的排序规则确定。
如果 SQL 函数的结果具有字符数据类型，则排序规则派生规则定义排序规则以与结果关联。
以下内容对KingbaseES数据库中的函数按照其用途进行分类，然后对每个函数的用法、功能、使用示例进行说明。
下列表显示KingbaseES了可用的数学函数。这些函数中有许多都有多种不同的形式，区别是参数不同，在这些数字函数的参数中，
double
precision
。除非特别指明，任何特定形式的函数都返回和它的参数相同的数据类型。
double
precision
数据的函数大多数是在宿主系统的 C 库基础上实现的。因此，边界情况下的准确度和行为是根据宿主系统而变化的。
的隐式强制措施，在KingbaseES V8R3 之前，这些函数也可以接受多种非字符串数据类型。这些强制措施在目前版本中已经删除，因为它们常常导致令人惊讶的行为。不过，字符串串接操作符（
）仍然接受非字符串输入，只要至少一个输入是一种字符串类型，如下表所示。对于其他情况，如果你需要复制之前的行为，可以为
插入一个显式强制措施。对于
操作符，支持两个字符之间输入若干空格或者换行的字符，效果和不加空格或换行一致。
输出模板串中，一些特定的模式可以被识别并且被替换成基于给定值的被恰当地格式化的数据。任何不属于模板模式的文本都简单地照字面拷贝。同样，在一个输入
模板串里（对其他函数），模板模式标识由输入数据串提供的值。如果在模板字符串中有不是模板模式的字符，输入数据字符串中的对应字符会被简单地跳过（不管它们是否等于模板字符串字符）。
下表展示了可以用于格式化日期和时间值的模版。
select a, b, c, count(distinct c) over (partition by a) v from tab where a < 3;
a | b | c | v
---+---+---+---
1 | 1 | 1 | 5
1 | 1 | 1 | 5
1 | 1 | 2 | 5
1 | 2 | 2 | 5
1 | 2 | 3 | 5
1 | 2 | 3 | 5
1 | | 4 | 5
1 | | 4 | 5
1 | | 5 | 5
1 | | 5 | 5
2 | 3 | 1 | 5
2 | 3 | 1 | 5
2 | 3 | 2 | 5
2 | 4 | 2 | 5
2 | 4 | 3 | 5
2 | 4 | 3 | 5
2 | | 4 | 5
2 | | 4 | 5
2 | | 5 | 5
2 | | 5 | 5
(20 rows)
adddate(date,float4) returns date
adddate(date,interval) return datetime
adddate(datetime,float4) return datetime
adddate(datetime,interval) return datetime
adddate(timestamp,float4) return timestamp
adddate(timestamp,interval) return timestamp
adddate(time,float4) return datetime
adddate(time,interval) return datetime
test=# select adddate(datetime'2022-10-24 21:30:00', interval '5' hour);
adddate
---------------------
2022-10-25 02:30:00
(1 row)
test=# select adddate(timestamp'2022-10-24 21:30:00', 520);
adddate
---------------------
2024-03-27 21:30:00
(1 row)
test=# select adddate(timestamp'2022-10-24 21:30:00', interval '5' minute);
adddate
---------------------
2022-10-24 21:35:00
(1 row)
test=# select adddate(time'21:30:00', 520);
adddate
---------------------
2024-12-02 21:30:00
(1 row)
test=# select adddate(time'21:30:00', interval '5' second);
adddate
---------------------
2023-07-01 21:30:05
(1 row)
select add_months(date'2012-1-20',-1) AS RESULT;
select add_months(timestamp'2012-1-31 10:00:00',-20) AS RESULT;
select add_months('20120101',12) AS RESULT;
CREATE DIRECTORY dir as '/tmp/bfile';
CREATE DIRECTORY dir1 as '/tmp/bfile';
CREATE TABLE test_bfile(id int, column_bfile bfile);
CREATE TABLE test_bfile1(id int, column_bfile bfile default NULL);
INSERT INTO test_bfile VALUES(1, bfilename('','myfile.sql'));
INSERT INTO test_bfile VALUES(2, bfilename('dir',''));
INSERT INTO test_bfile VALUES(3, bfilename('dir','myfile.sql'));
INSERT INTO test_bfile VALUES(4, bfilename('dir1','myfile1.sql'));
INSERT INTO test_bfile VALUES(5, NULL);
INSERT INTO test_bfile VALUES(6, '');
SELECT * FROM test_bfile;
id|column_bfile |
--+--------------------------------+
1|bfilename(NULL, 'myfile.sql') |
2|bfilename('dir', NULL) |
3|bfilename('dir', 'myfile.sql') |
4|bfilename('dir1', 'myfile1.sql')|
5| |
6| |
CREATE TABLE tinyint_fun(a tinyint);
INSERT INTO tinyint_fun values('55');
INSERT INTO tinyint_fun values('100');
SELECT bit_and(a) from tinyint_fun;
CREATE TABLE tinyint_fun(a tinyint);
INSERT INTO tinyint_fun values('55');
INSERT INTO tinyint_fun values('100');
SELECT bit_or(a) from tinyint_fun;
该BITAND函数将其输入和输出视为位向量；输出是AND输入的按位。expr1和的类型expr2是NUMBER，结果是类型NUMBER。如果任一参数BITAND为NULL，则结果为NULL。参数必须在 -(2 (n-1) ) .. ((2 (n-1) )-1) 范围内。如果参数超出此范围，则结果未定义。
结果分几个步骤计算。首先，每个参数 A 都被替换为 value SIGN(A)*FLOOR(ABS(A))。这种转换具有将每个参数截断为零的效果。接下来，每个参数 A（现在必须是整数值）被转换为 n 位二进制补码二进制整数值。使用按位运算组合这两个位值AND。最后，将得到的 n 位二进制补码值转换回NUMBER。
select bittonumeric('0110'); --6
create table tb(a varbit(10));
insert into tb values('1110110');
select bittonumeric(a) from tb; --118
SELECT box(circle '((0,0),2.0)')；
SELECT box(point '(0,0)')；
SELECT box(point '(0,0)', point '(1,1)')；
SELECT box(polygon '((0,0),(1,1),(2,0))')；
positive_col int CHECK (positive_col > 0),
indexed_col int,
CONSTRAINT comment_test_pk PRIMARY KEY (id));
COMMENT ON COLUMN comment_test.id IS 'Column ''id'' on comment_test';
SELECT col_description('comment_test'::regclass, 1) as comment;
2020-01-01 01:00:00
SELECT CONVERT_TZ(timestamp'2000-01-01 12:00:00','GMT','MET') "CONVERT_TZ";
CONVERT_TZ
--------------------
2000-01-01 13:00:00
SELECT CONVERT_TZ(timestamp'2000-01-01 12:00:00','Africa/Tripoli','Africa/Abidjan') "CONVERT_TZ";
CONVERT_TZ
--------------------
2000-01-01 10:00:00
CREATE TABLE xmltbl (a int, b text);
INSERT INTO xmltbl VALUES (1, 'one'), (2, 'two'), (-1, null);
DECLARE xc CURSOR WITH HOLD FOR SELECT * FROM xmltbl ORDER BY 1, 2;
SELECT cursor_to_xml('xc'::refcursor, 5, false, true, '');
1999-12-31 00:00:00
SELECT DATE_SUB(TIMESTAMP'2000-01-01 12:00:00',INTERVAL '1' HOUR) "DATE_SUB";
DATE_SUB
---------------------
2000-01-01 11:00:00
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');
SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
DECODE逐个将expr与每个搜索值(search)进行比较。如果expr等于搜索值(search)，则KingbaseES数据库返回相应的结果(result)。如果找不到匹配项，则将返回默认值(default)。如果省略默认值，则返回空值(NULL)。
参数可以是任何类型。如果expr和search是字符数据，那么KingbaseES使用非添加的比较语义对它们进行比较。返回的字符串是VARCHAR数据类型，与第一个结果参数在同一个字符集中。
如果第一个搜索结果对是数字，则KingbaseES将比较所有搜索结果表达式和第一个用于确定具有最高数字优先级的参数的表达式，隐式将其余参数转换为该数据类型，并返回该数据类型。search、result和default可以从表达式派生。
KingbaseES数据库使用短路评估。数据库只在将每个search与expr进行比较之前对其求值，而不是在将所有search与expr进行比较之前对其求值。因此，如果前一个search等于expr，KingbaseES不再计算其它search、result。 KingbaseES自动将expr和每个search转换为比较前的第一个search。
KingbaseES自动转换返回值与第一个结果相同的数据类型。如果第一个结果具有数据类型CHAR或者如果第一个结果为空，则KingbaseES将返回值转换为数据类型VARCHAR。
在DECODE函数中，KingbaseES认为两个空值相等。DECODE函数中的最大参数个数(包括expr、search、result和default)为255个。
SELECT existsnode('//town[text() = ''Toronto'']' PASSING BY REF '<towns><town>Bidford-on-Avon</town><town>Cwmbran</town><town>Bristol</town></towns>');
SELECT existsnode('//town[text() = ''Cwmbran'']' PASSING BY REF '<towns><town>Bidford-on-Avon</town><town>Cwmbran</town><town>Bristol</town></towns>');
SELECT existsnode('<a><b>d</b></a>', '/a/b');
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
结果：20
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
结果：21
SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16
20:38:40.12-08');
结果：982384720.12
SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
结果：442800
SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01');
结果：2005
SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
结果：2006
SELECT extract(xml('<行><ID>1</ID></行>'), '/行/ID');
SELECT extract(xml('<行><ID>1</ID></行>'), '/行');
SELECT extract(xml('<行 属性="测试属性"><ID>1</ID></行>'), '/行');
SELECT extract(xml('<行 属性="测试属性"><ID>1</ID></行>'), '/行/@属性');
SELECT extractvalue('<a><b>b</b></a>','/a/b');
SELECT extractvalue('<a xmlns="http://example.com"><b>test</b></a>', '//mydefns:b/text()',ARRAY[ARRAY['mydefns', 'http://example.com']]);
SELECT extractvalue('<a><b>b1</b><b>b2</b></a>','/a/b[2]');
FIND_IN_SET(str,strlist)返回第二个参数中第一个参数的索引（位置）。如果字符串str在由N个子字符串组成的strlist中，则返回1~N范围内的值。strlist是由以’,’分隔的子字符串组成。如果str不在strlist中或strlist是空字符串，则返回0。
FIND_IN_SET(str,oid)返回str在sys_set中对应的位置索引值。如果字符串str在sys_set中，则返回1~64范围内的值（sys_set按照setdisplayorder从小到大排序后的位置索引值）。如果str不在系统表sys_set中，则返回0。oid为settypid，具体见
sys_set
aggregate_function KEEP
( DENSE_RANK FIRST ORDER BY expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] [ , … ] )
[ OVER ( [ query_partition_clause ] ) ]
[ FILTER ( WHERE filter_clause ) ]
SELECT department_id,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) "Best"
FROM employees
GROUP BY department_id
ORDER BY department_id;
SELECT last_name, department_id, salary,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Best"
FROM employees
ORDER BY department_id, salary, last_name;
INSERT INTO datetimes VALUES
(1, '11:00', '11:00 BST', '1 year', '2000-10-19 10:23:54+01', '2000-10-19 10:23:54'),
(2, '12:00', '12:00 BST', '2 years', '2001-10-19 10:23:54+01', '2001-10-19 10:23:54'),
(3, '13:00', '13:00 BST', '3 years', '2001-10-19 10:23:54+01', '2001-10-19 10:23:54'),
(4, '14:00', '14:00 BST', '4 years', '2002-10-19 10:23:54+01', '2002-10-19 10:23:54'),
(5, '15:00', '15:00 BST', '5 years', '2003-10-19 10:23:54+01', '2003-10-19 10:23:54'),
(6, '15:00', '15:00 BST', '5 years', '2004-10-19 10:23:54+01', '2004-10-19 10:23:54'),
(7, '17:00', '17:00 BST', '7 years', '2005-10-19 10:23:54+01', '2005-10-19 10:23:54'),
(8, '18:00', '18:00 BST', '8 years', '2006-10-19 10:23:54+01', '2006-10-19 10:23:54'),
(9, '19:00', '19:00 BST', '9 years', '2007-10-19 10:23:54+01', '2007-10-19 10:23:54'),
(10, '20:00', '20:00 BST', '10 years', '2008-10-19 10:23:54+01', '2008-10-19 10:23:54');
SELECT id, f_time, first_value(id) OVER w
FROM datetimes
WINDOW w AS (ORDER BY f_time RANGE BETWEEN
'70 min'::interval preceding AND '2 hours'::interval following);
SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three');
结果：Testing one, two, three, %
SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly');
结果：INSERT INTO "Foo bar" VALUES('O''Reilly')
SELECT format('INSERT INTO %I VALUES(%L)', 'locations', 'C:\Program Files');
结果：INSERT INTO locations VALUES(E'C:\\Program Files')
SELECT format('Testing %3$s, %2$s, %1$s', 'one', 'two', 'three');
结果：Testing three, two, one
SELECT format('|%*2$s|', 'foo', 10, 'bar');
结果：| bar|
SELECT format('|%1$*2$s|', 'foo', 10, 'bar');
结果：| foo|
SELECT * FROM generate_series(2,4);
SELECT * FROM generate_series(5,1,-2);
SELECT * FROM generate_series(4,3);
SELECT generate_series(1.1, 4, 1.3);
SELECT current_date + s.a AS dates FROM generate_series(0,14,7) AS s(a);
SELECT * FROM generate_series('2008-03-01 00:00'::timestamp, '2008-03-04 12:00', '10 hours');
CREATE OR REPLACE FUNCTION unnest2(anyarray)
RETURNS SETOF anyelement AS $$
select $1[i][j]
from generate_subscripts($1,1) g1(i),
generate_subscripts($1,2) g2(j);
$$ LANGUAGE sql IMMUTABLE;
CREATE FUNCTION
SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]);
CREATE TABLE group_concat_test(a int, b int ,c int PRIMARY KEY);
INSERT INTO group_concat_test VALUES(1,1,3);
INSERT INTO group_concat_test VALUES(2,3,2);
INSERT INTO group_concat_test VALUES(3,2,1);
INSERT INTO group_concat_test VALUES(1,2,0);
INSERT INTO group_concat_test VALUES(NULL,2,6);
CREATE EXTENSION kdb_utils_function;
SELECT group_concat(a) FROM group_concat_test;
SELECT group_concat(distinct a) FROM group_concat_test;
SELECT group_concat(b order by b desc) FROM group_concat_test;
SELECT group_concat(b separator '--') FROM group_concat_test;
SELECT group_concat(a order by b,c) FROM t1;
GROUPING将超聚合行与常规分组行区分开来。GROUP BY扩展，例如ROLLUP和CUBE生成超聚合行，其中所有值的集合由 null 表示。使用该GROUPING函数，您可以将表示超聚合行中所有值的集合的空值与常规行中的空值区分开来。
expr函数中的必须GROUPING匹配GROUP BY子句中的表达式之一。expr如果该行中的值是表示所有值的集合的空值，则该函数返回值 1 。否则，它返回零。函数返回值的数据类型GROUPING为 KingbaseES NUMBER。有关这些术语的讨论，请参阅
SELECT
group_by_clause。
在以下示例中，它使用示例表hr.departments，hr.employees如果GROUPING函数返回 1（表示超聚合行而不是表中的常规行），则字符串“All Jobs”出现在“JOB”列中，而不是 null否则会出现：
SELECT DECODE(GROUPING(department_name), 1, 'ALL DEPARTMENTS', department_name) AS department, DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job, COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal" FROM employees e, departments d WHERE d.department_id = e.department_id GROUP BY ROLLUP (department_name, job_id) ORDER BY department, job;
GROUPING_ID返回对应于与行关联的GROUPING位向量的数字。GROUPING_ID仅适用于包含GROUP BY扩展（例如ROLLUP或CUBE）和GROUPING函数的SELECT语句。GROUPING_ID在功能上等同于获取多个GROUPING函数的结果并将它们连接成一个位向量（一串1和0）。通过使用GROUPING_ID，可以避免使用多个GROUPING函数并使行过滤条件更易于表达。使用GROUPING_ID可以更轻松地进行行过滤，因为可以使用GROUPING_ID = n的单个条件来识别所需的行。
expr函数中的必须GROUPING_ID匹配GROUP BY子句中的表达式之一。最右边参数指派的位是最低有效位，如果对应的表达式被包括在产生结果行的分组集合的分组条件中则每一位是 0，否则是 1。函数返回值的数据类型GROUPING为 KingbaseES NUMBER。有关这些术语的讨论，请参阅:ref:
SELECT
group_by_clause。
在以下示例中，它使用表items_sold，GROUPING_ID函数返回make和model列是否包含在超聚合行中的位的向量。make为高位，model为低位。
SELECT make, model, GROUPING_ID(make,model), sum(sales) FROM items_sold GROUP BY ROLLUP(make,model);
make | model | grouping_id | sum
------+-------+-------------+-----
| | 3 | 55
Foo | Tour | 0 | 20
Foo | GT | 0 | 10
Bar | Sport | 0 | 5
Bar | City | 0 | 15
Bar | | 0 | 5
Foo | | 1 | 30
Bar | | 1 | 25
(8 rows)
SELECT IF(100, 'T', 'F') AS RESULT1,
IF(1 - 1, 'T', 'F') AS RESULT2,
IF('100', 'T', 'F') AS RESULT3,
IF(NULL, 'T', 'F') AS RESULT4;
SELECT insertchildxml('<a>one <b></b> three <b></b></a>', '//b', 'name', '<name>newnode</name>');
SELECT insertchildxml('<a xmlns="http://example.com"><b>test</b></a>', '//mydefns:b/text()', 'name', '<name>newnode</name>', ARRAY[ARRAY['mydefns', 'http://example.com']]);
SELECT insertxmlbefore('<a><b>b</b></a>','/a/b','<c>c</c>');
SELECT insertxmlbefore('<a><b></b></a>','/a/b','<c>c</c>');
SELECT insertxmlbefore('<a><b></b></a>','/a/b','<c></c>');
SELECT insertxmlbefore('<a xmlns="http://example.com"><b>test</b></a>', '//mydefns:b/text()', '<name>newnode</name>', ARRAY[ARRAY['mydefns', 'http://example.com']]);
SELECT ISNULL('2001-01-10', TO_DATE('05 Dec 2000', 'DD Mon YYYY')) AS RESULT1,
ISNULL(NULL, TO_DATE('05 Dec 2000', 'DD Mon YYYY')) AS RESULT2,
ISNULL(NULL, 123) AS RESULT3, ISNULL(789, 123) AS RESULT4,
ISNULL(NULL, '123') AS RESULT5;
expression [ FORMAT JSON [ ENCODING UTF8 ] ]
[ { WITH | WITHOUT } UNIQUE [ KEYS ] ]
[ RETURNING json_data_type ]
FROM ( SELECT $$a$$ || x AS b, y AS c,
ARRAY[ROW(x.*,ARRAY[1,2,3]),
ROW(y.*,ARRAY[4,5,6])] AS z
FROM generate_series(1,2) x,
generate_series(4,5) y) q;
JSON_ARRAY (
[ { value_expression [ FORMAT JSON ] } [, ...] ]
[ { NULL | ABSENT } ON NULL ]
[ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ]
SELECT JSON_ARRAY(f.code,f.title,f.did) AS films FROM films AS f WHERE f.did = 103;
films
----------------------------------------------------
["P_301", "Vertigo", 103]
["P_302", "Becket", 103]
["P_303", "48 Hrs", 103]
(3 rows)
JSON_ARRAY (
[ query_expression ]
[ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ]
SELECT JSON_ARRAY(SELECT f.title FROM films AS f where f.did = 103) AS film_titles;
film_titles
----------------------------------------------------
["Vertigo", "Becket", "48 Hrs"]
(1 row)
[ { value_expression [ FORMAT JSON ] } [, ...] ]
[ { NULL | ABSENT } ON NULL ]
[ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ]
create table t1(id int, name text);
insert into t1 values(1,'aa');
insert into t1 values(2,'bb');
insert into t1 values(3,'cc');
insert into t1 values(4,'dd');
insert into t1 values(3,'cccc');
select json[id,name] from t1;
-------------
[1, "aa"]
[2, "bb"]
[3, "cc"]
[4, "dd"]
[3, "cccc"]
[ ORDER BY sort_expression ]
[ { NULL | ABSENT } ON NULL ]
[ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ]
SELECT JSON_ARRAYAGG( f.title ORDER BY f.title ASC) AS film_titles FROM films AS f;
film_titles
----------------------------------------------------
["48 Hrs", "Absence of Malice", "Bananas", "Becket", "Bed Knobs and
Broomsticks", "Das Boot", "Storia di una donna", "Taxi Driver",
"The African Queen", "The King and I", "There's a Girl in my
Soup", "The Third Man", "Une Femme est une Femme", "Vertigo", "War
and Peace", "West Side Story", "Yojimbo"]
(1 row)
context_item, path_expression
[ PASSING { value AS varname }[, ...]]
[ RETURNING data_type ]
[ { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ]
SELECT JSON_EXISTS(jsonb '{"key1": [1,2,3]}', 'strict $.key1[*] ?(@ > 2)');
**返回值：**
json_exists
-------------
(1 row)
SELECT JSON_EXISTS(jsonb '{"a": [1,2,3]}', 'lax $.a[5]' ERROR ON ERROR);
**返回值：**
json_exists
-------------
(1 row)
SELECT JSON_EXISTS(jsonb '{"a": [1,2,3]}', 'strict $.a[5]');
**返回值：**
json_exists
-------------
(1 row)
SELECT json_object('{a, 1, b, "def", c, 3.5}');
**返回值：**
{"a": "1", "b": "def", "c": "3.5"}
SELECT json_object('{{a,1},{b, "def"},{c, 3.5}}');
**返回值：**
{"a": "1", "b": "def", "c": "3.5"}
json_object (
[ { key_expression { value | ':' } value_expression [ format json [ encoding utf8 ] ] }[, ...] ]
[ { null | absent } on null ]
[ returning data_type [ format json [ encoding utf8 ] ] ]
[ { with | without } unique [ keys ] ]
SELECT JSON_OBJECT('name' : first_name || ' ' || last_name,'email' : email,'phone' : phone_number,'hire_date' : hire_date)
FROM hr.employees WHERE employee_id = 140;
**返回值：**
{"name":"Joshua Patel","email":"JPATEL","phone":"650.121.1834","hire_date":"1998-04-06T00:00:00"}
SELECT JSON_OBJECT('key1' VALUE NULL ABSENT ON NULL) FROM dual;
**返回值：**
SELECT JSON_OBJECT ('name' value '"Foo"' FORMAT JSON ) FROM DUAL;
**返回值：**
{"name":"Foo"}
SELECT json_object('id' value 'aa', 'id' value 'bb' with unique keys) FROM dual;
**返回值：**
ERROR: duplicate JSON key "id"
CREATE TEMP TABLE foo (serial_num int, name text, type text);
INSERT INTO foo VALUES (847001,'t15','GE1043');
INSERT INTO foo VALUES (847002,'t16','GE1043');
INSERT INTO foo VALUES (847003,'sub-alpha','GESS90');
SELECT json_object_agg(name, type) FROM foo;
json_objectagg(
[ { key_expression { VALUE | ':' } value_expression } ]
[ { NULL | ABSENT } ON NULL ]
[ { WITH | WITHOUT } UNIQUE [ KEYS ] ]
[ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ]
create table t1(id int, name text);
insert into t1 values(1,'aa');
insert into t1 values(2,'bb');
insert into t1 values(3,'cc');
insert into t1 values(4,'dd');
insert into t1 values(3,'cccc');
select json_objectagg(id : name) from t1;
{ "1" : "aa", "2" : "bb", "3" : "cc", "4" : "dd", "3" : "cccc" }
select json_objectagg(id value name) from t1;
{ "1" : "aa", "2" : "bb", "3" : "cc", "4" : "dd", "3" : "cccc" }
select json_objectagg(id value name with unique keys returning jsonb) from t1;
ERROR: duplicate JSON object key value
json_objectarray(
[ { key_expression { VALUE | ':' } value_expression } ]
[ { NULL | ABSENT } ON NULL ]
[ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ]
[ { WITH | WITHOUT } UNIQUE [ KEYS ] ]
create table t1(id int, name text);
insert into t1 values(1,'aa');
insert into t1 values(2,'bb');
insert into t1 values(3,'cc');
insert into t1 values(4,'dd');
insert into t1 values(3,'cccc');
select json_objectagg(id : name) from t1;
{ "1" : "aa", "2" : "bb", "3" : "cc", "4" : "dd", "3" : "cccc" }
select json_objectagg(id value name) from t1;
{ "1" : "aa", "2" : "bb", "3" : "cc", "4" : "dd", "3" : "cccc" }
select json_objectagg(id value name returning jsonb with unique keys ) from t1;
ERROR: duplicate JSON object key value
json_query (
expression [FORMAT JSON [ENCODING UTF8]],JSON_basic_path_expression
[ PASSING { value AS varname } [, ...]]
[ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ]
[ { WITHOUT | WITH { CONDITIONAL | [UNCONDITIONAL] } } [ ARRAY ] WRAPPER ]
[ { KEEP | OMIT } QUOTES [ ON SCALAR STRING ] ]
[{ERROR|NULL|EMPTY|EMPTY ARRAY|EMPTY OBJECT|DEFAULT expression } ON EMPTY ]
[{ERROR|NULL|EMPTY|EMPTY ARRAY|EMPTY OBJECT|DEFAULT expression } ON ERROR ]
a.WITHOUT WRAPPER：默认选项，返回值不用‘[]’包围。
b.WITH WRAPPER:返回值用‘[]’包围，等价于WITH ARRAY WRAPPER。
c.WITH UNCONDITIONAL WRAPPER:等价于WITH WRAPPER.
d.WITH CONDITIONAL WRAPPER：
a.NULL ON ERROR - 返回NULL,此为默认选项
b.ERROR ON ERROR - 报错
c.EMPTY ON ERROR - 等价于 EMPTY ARRAY ON ERROR.
d.EMPTY ARRAY ON ERROR - 返回空数组 ([])
e.EMPTY OBJECT ON ERROR - 返回空对象 ({})
a.NULL ON EMPTY - 返回NULL,此为默认选项
b.ERROR ON EMPTY - 报错
c.EMPTY ON EMPTY - 等价于 EMPTY ARRAY ON ERROR.
d.EMPTY ARRAY ON EMPTY - 返回空数组 ([])
e.EMPTY OBJECT ON EMPTY - 返回空对象 ({})
SELECT JSON_query(jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $"xy")' PASSING 1 AS XY);
json_query
------------
(1 row)
SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text KEEP QUOTES);
json_query
------------
"aaa"
(1 row)
SELECT JSON_query(jsonb '{"a":"b","c":"d"}', '$.ab' returning varchar empty on empty);
json_query
------------
(1 row)
select json_query(jsonb '["aaa",123]', '$[*]' returning varchar(2) error on error);
ERROR: JSON path expression in JSON_QUERY should return singleton item without wrapper
json_serialize (
expression [ FORMAT JSON [ ENCODING UTF8 ] ]
[ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ]
{"a":{"a":
select json_serialize('{"a":{"a":"c"}}' returning bytea format json encoding utf8 );
----------------------------------
\x7b2261223a7b2261223a2263227d7d
explain(verbose,costs off) select json_serialize( '1' returning text) as json_type ;
----------------------------------------------------
Result
Output: JSON_SERIALIZE('1'::json RETURNING text)
(2 rows)
json_table (
context_item,
path_expression [ AS json_path_name ] [ PASSING { value AS varname } [, ...] ]
COLUMNS ( json_table_column [, ...] )
PLAN ( json_table_plan ) |
PLAN DEFAULT ( { INNER | OUTER } [ , { CROSS | UNION } ] |
{ CROSS | UNION } [ , { INNER | OUTER } ] )
[{ERROR | EMPTY} ON ERROR]
name type [ PATH json_path_specification ]
[ { WITHOUT | WITH { CONDITIONAL | [UNCONDITIONAL] } } [ ARRAY ] WRAPPER ]
（注：此处实际只能支持WITHOUT [ARRAY] WRAPPER）
[ { KEEP | OMIT } QUOTES [ ON SCALAR STRING ] ]
（注：此处实际不支持此子句）
[ { ERROR | NULL | DEFAULT expression } ON EMPTY ]
[ { ERROR | NULL | DEFAULT expression } ON ERROR ]
name type FORMAT json_representation
[ PATH json_path_specification ]
[ { WITHOUT | WITH { CONDITIONAL | [UNCONDITIONAL] } } [ ARRAY ] WRAPPER ]
[ { KEEP | OMIT } QUOTES [ ON SCALAR STRING ] ]
[ { ERROR | NULL | EMPTY { ARRAY | OBJECT } | DEFAULT expression } ON EMPTY ]
[ { ERROR | NULL | EMPTY { ARRAY | OBJECT } | DEFAULT expression } ON ERROR ]
name type EXISTS [ PATH json_path_specification ]
[ { ERROR | TRUE | FALSE | UNKNOWN } ON ERROR ] |
NESTED PATH json_path_specification [ AS path_name ] COLUMNS ( json_table_column [, ...] )
PLAN( json_path_name [ { OUTER | INNER } json_table_plan_primary ] |
json_table_plan_primary { UNION json_table_plan_primary } [...] |
json_table_plan_primary { CROSS json_table_plan_primary } [...]
SELECT t.* from json_table('{"a":{"b":"c"}}'::jsonb, '$.a' columns(b text format json path '$.b'))t;
-----
(1 row)
SELECT t.* from json_table('{"a":{"b":"c"}}'::jsonb, '$.a' columns(b text exists path '$.b'))t;
------
(1 row)
select t.* from json_table('
{"a": 1, "b": [], "c": []},
{"a": 2, "b": [1, 2, 3], "c": [10, null, 20]}
]'::jsonb ,
'$[*]' columns(
a int path 'lax $.a',
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
a | b | c
---+---+----
1 | |
2 | 1 |
2 | 2 |
2 | 3 |
2 | | 10
2 | |
2 | | 20
(7 rows)
select t.* from json_table('[
{"a": 1, "b": [], "c": []},
{"a": 2, "b": [1, 2, 3], "c": [10, null, 20]}
]'::jsonb , '$[*]' columns(
id for ordinality,
id2 for ordinality,
a int path 'lax $.a',
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
id | id2 | a | b | c
----+-----+---+---+----
1 | 1 | 1 | |
2 | 2 | 2 | 1 |
2 | 2 | 2 | 2 |
2 | 2 | 2 | 3 |
2 | 2 | 2 | | 10
2 | 2 | 2 | |
2 | 2 | 2 | | 20
(7 rows)
SELECT
(VALUES ('1'), ('"err"')) vals(js)
LEFT OUTER JOIN
JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$' NULL ON ERROR) ERROR ON ERROR) jt
ON true;
js | a
-------+---
1 | 1
"err" |
(2 rows)
基础数据1：
CREATE TEMP TABLE jsonb_table_test (js jsonb);
INSERT INTO jsonb_table_test
VALUES (
{"a": 1, "b": [], "c": []},
{"a": 2, "b": [1, 2, 3], "c": [10, null, 20]}
基础数据1-例子1：
select
jsonb_table_test jtt,
json_table (
jtt.js,'strict $[*]' as p
columns (
n for ordinality,
a int path 'lax $.a' default -1 on empty,
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
plan (p outer (pb union pc))
) jt;
n | a | b | c
---+---+---+----
1 | 1 | |
2 | 2 | 1 |
2 | 2 | 2 |
2 | 2 | 3 |
2 | 2 | | 10
2 | 2 | |
2 | 2 | | 20
(7 rows)
基础数据1-例子2：
select
jsonb_table_test jtt,
json_table (
jtt.js,'strict $[*]' as p
columns (
n for ordinality,
a int path 'lax $.a' default -1 on empty,
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
plan (p inner (pb union pc))
) jt;
n | a | b | c
---+---+---+----
2 | 2 | 1 |
2 | 2 | 2 |
2 | 2 | 3 |
2 | 2 | | 10
2 | 2 | |
2 | 2 | | 20
(6 rows)
基础数据1-例子3：
select
jsonb_table_test jtt,
json_table (
jtt.js,'strict $[*]' as p
columns (
n for ordinality,
a int path 'lax $.a' default -1 on empty,
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
plan (p inner (pb cross pc))
) jt;
n | a | b | c
---+---+---+----
2 | 2 | 1 | 10
2 | 2 | 1 |
2 | 2 | 1 | 20
2 | 2 | 2 | 10
2 | 2 | 2 |
2 | 2 | 2 | 20
2 | 2 | 3 | 10
2 | 2 | 3 |
2 | 2 | 3 | 20
(9 rows)
基础数据2：
CREATE TEMP TABLE jsonb_table_test (js jsonb);
INSERT INTO jsonb_table_test
VALUES (
{"a": 1, "b": [], "c": []},
{"a": 2, "b": [1, 2, 3], "c": [10, null, 20]},
{"a": 3, "b": [1, 2], "c": []},
{"x": "4", "b": [1, 2], "c": 123}
基础数据2-例子1:
select
jsonb_table_test jtt,
json_table (
jtt.js,'strict $[*]' as p
columns (
n for ordinality,
a int path 'lax $.a' default -1 on empty,
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
plan default (outer, union)
) jt;
n | a | b | c
---+----+---+----
1 | 1 | |
2 | 2 | 1 |
2 | 2 | 2 |
2 | 2 | 3 |
2 | 2 | | 10
2 | 2 | |
2 | 2 | | 20
3 | 3 | 1 |
3 | 3 | 2 |
4 | -1 | 1 |
4 | -1 | 2 |
(11 rows)
基础数据2-例子2：
select
jsonb_table_test jtt,
json_table (
jtt.js,'strict $[*]' as p
columns (
n for ordinality,
a int path 'lax $.a' default -1 on empty,
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
plan (p outer (pb union pc))
) jt;
n | a | b | c
---+----+---+----
1 | 1 | |
2 | 2 | 1 |
2 | 2 | 2 |
2 | 2 | 3 |
2 | 2 | | 10
2 | 2 | |
2 | 2 | | 20
3 | 3 | 1 |
3 | 3 | 2 |
4 | -1 | 1 |
4 | -1 | 2 |
(11 rows)
基础数据3:
CREATE TABLE my_films ( js jsonb );
INSERT INTO my_films VALUES (
"favorites": [
"kind": "comedy",
"films": [
"title": "Bananas",
"director": "Woody Allen"
"title": "The Dinner Game",
"director": "Francis Veber"
"kind": "horror",
"films": [
"title": "Psycho",
"director": "Alfred Hitchcock"
"kind": "thriller",
"films": [
"title": "Vertigo",
"director": "Alfred Hitchcock"
"kind": "drama",
"films": [
"title": "Yojimbo",
"director": "Akira Kurosawa"
基础数据3-例子1：
SELECT
my_films,
JSON_TABLE ( js, '$.favorites[*]' COLUMNS (
id FOR ORDINALITY,
kind text PATH '$.kind',
NESTED PATH '$.films[*]' COLUMNS (
title text PATH '$.title',
director text PATH '$.director'))) AS jt;
id | kind | title | director
----+----------+------------------+-------------------
1 | comedy | Bananas | Woody Allen
1 | comedy | The Dinner Game | Francis Veber
2 | horror | Psycho | Alfred Hitchcock
3 | thriller | Vertigo | Alfred Hitchcock
4 | drama | Yojimbo | Akira Kurosawa
(5 rows)
基础数据3-例子2:
SELECT
director1 AS director, title1, kind1, title2, kind2
my_films,
JSON_TABLE ( js, '$.favorites' AS favs COLUMNS (
NESTED PATH '$[*]' AS films1 COLUMNS (
kind1 text PATH '$.kind',
NESTED PATH '$.films[*]' AS film1 COLUMNS (
title1 text PATH '$.title',
director1 text PATH '$.director')
NESTED PATH '$[*]' AS films2 COLUMNS (
kind2 text PATH '$.kind',
NESTED PATH '$.films[*]' AS film2 COLUMNS (
title2 text PATH '$.title',
director2 text PATH '$.director'
PLAN (favs OUTER ((films1 INNER film1) CROSS (films2 INNER film2)))
) AS jt
WHERE kind1 > kind2 AND director1 = director2;
director | title1 | kind1 | title2 | kind2
------------------+---------+----------+--------+--------
Alfred Hitchcock | Vertigo | thriller | Psycho | horror
(1 row)
expression [ format json [ encoding utf8 ] ],
json_basic_path_expression
[returning json_value_return_type]
[{ null|error|default literal} on error]
[{ null|error|default literal} on empty]
select json_value(jsonb '{"a": 1, "b": {"c":2,"c":3}}','$.b.d' default 'error' on empty);
error
FROM ( SELECT $$a$$ || x AS b, y AS c,
ARRAY[ROW(x.*,ARRAY[1,2,3]),
ROW(y.*,ARRAY[4,5,6])] AS z
FROM generate_series(1,2) x,
generate_series(4,5) y) q;
SELECT jsonb_object('{a, 1, b, "def", c, 3.5}');
**返回值：**
{"a": "1", "b": "def", "c": "3.5"}
SELECT jsonb_object('{{a,1},{b, "def"},{c, 3.5}}');
**返回值：**
{"a": "1", "b": "def", "c": "3.5"}
CREATE TEMP TABLE foo (serial_num int, name text, type text);
INSERT INTO foo VALUES (847001,'t15','GE1043');
INSERT INTO foo VALUES (847002,'t16','GE1043');
INSERT INTO foo VALUES (847003,'sub-alpha','GESS90');
SELECT jsonb_object_agg(name, type) FROM foo;
aggregate_function KEEP
( DENSE_RANK LAST ORDER BY expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] [ , … ] )
[ OVER ( [ query_partition_clause ] ) ]
[ FILTER ( WHERE filter_clause ) ]
SELECT lead(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
lead | ten | four
------+-----+------
0 | 0 | 0
4 | 0 | 0
| 4 | 0
1 | 1 | 1
7 | 1 | 1
9 | 7 | 1
| 9 | 1
| 0 | 2
3 | 1 | 3
| 3 | 3
(10 rows)
函数返回的LENGTH长度char。LENGTH使用输入字符集定义的字符计算长度。LENGTHB使用字节而不是字符。LENGTHC使用 Unicode 完整字符。LENGTH2使用 UCS2 代码点。LENGTH4使用 UCS4 代码点。
char可以是任何数据类型CHAR, VARCHAR, NCHAR, NVARCHAR, CLOB, 或NCLOB. 例外是LENGTHC,LENGTH2和LENGTH4, 不允许char是CLOBor NCLOB。返回值是数据类型NUMBER。如果char具有 data type CHAR，则长度包括所有尾随空格。如果char为 null，则此函数返回 null。
对 LENGTHB 的限制：该LENGTHB函数仅支持单字节 LOB。它不能与CLOB多NCLOB字节字符集中的数据一起使用。
以下示例使用LENGTH使用单字节数据库字符集的函数：
SELECT LENGTH('CANDIDE') "Length in characters" FROM DUAL;
+----------------------+
| Length in characters |
+======================+
| 7 |
+----------------------+
SELECT LENGTHB('1001'::bit) bit_lengthb, lengthb('100111111110000000000000000000000000001111'::bytea) bytea_lengthb; FROM DUAL;
+--------------------+---------------------+
| bit_lengthb | bytea_lengthb |
+====================+=====================+
| 1 | 42 |
+--------------------|---------------------|
select *, listagg(user_name,',') within group (order by user_name) over (partition by phone_num) from account;
user_name | phone_num | amount | listagg
-----------+-------------+--------+------------------
Lily | 13642160022 | 5000 | Lily,Lucy
Lucy | 13642160022 | 3000 | Lily,Lucy
| 13642160022 | 5050 | Lily,Lucy
Addy | 15950558535 | 6800 | Addy,Alice,Aline
Alice | 15950558535 | 12000 | Addy,Alice,Aline
Aline | 15950558535 | 8500 | Addy,Alice,Aline
Tom | 18622685434 | 10000 | Tom,Toney
Toney | 18622685434 | 9000 | Tom,Toney
(8 rows)
两者expr1和expr2都可以是数据类型CHAR、VARCHAR、NCHAR、NVARCHAR、CLOB或NCLOB中任何一种。如果expr1是字符数据类型，则返回字符串是VARCHAR类型。如果expr1是国家字符数据类型，则返回的字符串是数据类型NVARCHAR，如果是expr1是数据类型LOB，则返回 LOB。返回的字符串与expr1的字符集相同。参数n必须是NUMBER整数或可以隐式转换为NUMBER整数的值。
如果不指定expr2，则默认为一个空白。如果expr1长于n，则此函数返回expr1适合的部分n。
参数n是显示在终端屏幕上的返回值的总长度。在大多数字符集中，这也是返回值中的字符数。但是，在某些多字节字符集中，字符串的显示长度可能与字符串中的字符数不同。
下面的示例用星号 (*) 和句点 (.) 字符向左填充字符串：
SELECT LPAD('Page 1',15,'*.') "LPAD example" FROM DUAL;
+-----------------+
| LPAD example |
+=================+
| *.*.*.*.*Page 1 |
+-----------------+
LTRIM从字符串包含的所有字符的左端删除set。如果不指定set，则默认为一个空白。KingbaseES 数据库char从其第一个字符开始扫描并删除所有出现的字符，set直到到达一个不在的字符set，然后返回结果。
两者char和set都可以是任何数据类型CHAR、VARCHAR、NCHAR、NVARCHAR、CLOB或NCLOB。如果char是字符数据类型，则返回VARCHAR，N如果char是国家字符数据类型，则返回的字符串数据类型VARCHAR，如果是char 数据类型是LOB，则返回 LOB。
以下示例从字符串 中删除所有最左侧出现的小于号 ( <)、大于号 ( >) 和等号 ( )：=
SELECT LTRIM('<=====>BROWNING<=====>', '<>=') "LTRIM Example" FROM DUAL;
+-----------------+
| LTRIM Example |
+=================+
| BROWNING<=====> |
+-----------------+
minute：分钟值，可以是integer、numeric等数值类型，或者real、double precision等浮点数类型。若参数存在小数时，将其四舍五入成整数后再进行计算。
second：秒值，或者real、double precision等浮点数类型。若参数超出6位的小数部分时，将其四舍五入为6位小数后再进行计算。
time类型时间值。
make_interval(years int default 0, months int default 0, weeks int default 0,
days int default 0, hours int default 0, mins int default 0,
secs double default 0.0)
select make_timestamptz(1973, 07, 15, 08, 15, 55.33, '+8');
make_timestamptz
-------------------------------
1973-07-15 08:15:55.330000+08
(1 row)
SELECT manager_id, last_name, salary, MAX(salary) OVER (PARTITION BY manager_id) AS mgr_max FROM employees ORDER BY manager_id, last_name, salary;
+------------+-----------+--------+---------+
| MANAGER_ID | LAST_NAME | SALARY | MGR_MAX |
+============+===========+========+=========+
| 100 | Cambrault | 11000 | 17000 |
+------------+-----------+--------+---------+
| 100 | De Haan | 17000 | 17000 |
+------------+-----------+--------+---------+
| 100 | Errazuriz | 12000 | 17000 |
+------------+-----------+--------+---------+
| 100 | Fripp | 8200 | 17000 |
+------------+-----------+--------+---------+
| 100 | Hartstein | 13000 | 17000 |
+------------+-----------+--------+---------+
| 100 | Kaufling | 7900 | 17000 |
+------------+-----------+--------+---------+
| 100 | Kochhar | 17000 | 17000 |
+------------+-----------+--------+---------+
| . . . | | | |
+------------+-----------+--------+---------+
SELECT manager_id, last_name, salary FROM (SELECT manager_id, last_name, salary, MAX(salary) OVER (PARTITION BY manager_id) AS rmax_sal FROM employees) WHERE salary = rmax_sal ORDER BY manager_id, last_name, salary;
+------------+-----------+--------+
| MANAGER_ID | LAST_NAME | SALARY |
+============+===========+========+
| 100 | De Haan | 17000 |
+------------+-----------+--------+
| 100 | Kochhar | 17000 |
+------------+-----------+--------+
| 101 | Greenberg | 12008 |
+------------+-----------+--------+
| 101 | Higgins | 12008 |
+------------+-----------+--------+
| 102 | Hunold | 9000 |
+------------+-----------+--------+
| 103 | Ernst | 6000 |
+------------+-----------+--------+
| 108 | Faviet | 9000 |
+------------+-----------+--------+
| 114 | Khoo | 3100 |
+------------+-----------+--------+
| 120 | Nayer | 3200 |
+------------+-----------+--------+
| 120 | Taylor | 3200 |
+------------+-----------+--------+
| 121 | Sarchand | 4200 |
+------------+-----------+--------+
| 122 | Chung | 3800 |
+------------+-----------+--------+
| 123 | Bell | 4000 |
+------------+-----------+--------+
| 124 | Rajs | 3500 |
+------------+-----------+--------+
| 145 | Tucker | 10000 |
+------------+-----------+--------+
| 146 | King | 10000 |
+------------+-----------+--------+
| 147 | Vishney | 10500 |
+------------+-----------+--------+
| 148 | Ozer | 11500 |
+------------+-----------+--------+
| 149 | Abel | 11000 |
+------------+-----------+--------+
| 201 | Fay | 6000 |
+------------+-----------+--------+
| 205 | Gietz | 8300 |
+------------+-----------+--------+
| | King | 24000 |
+------------+-----------+--------+
22 rows selected.
SELECT manager_id, last_name, hire_date, salary,MIN(salary) OVER(PARTITION BY manager_id ORDER BY hire_date RANGE UNBOUNDED PRECEDING) AS p_cmin FROM employees ORDER BY manager_id, last_name, hire_date, salary;
+------------+-----------+-----------+--------+--------+
| MANAGER_ID | LAST_NAME | HIRE_DATE | SALARY | P_CMIN |
+============+===========+===========+========+========+
| 100 | Cambrault | 15-OCT-07 | 11000 | 6500 |
+------------+-----------+-----------+--------+--------+
| 100 | De Haan | 13-JAN-01 | 17000 | 17000 |
+------------+-----------+-----------+--------+--------+
| 100 | Errazuriz | 10-MAR-05 | 12000 | 7900 |
+------------+-----------+-----------+--------+--------+
| 100 | Fripp | 10-APR-05 | 8200 | 7900 |
+------------+-----------+-----------+--------+--------+
| 100 | Hartstein | 17-FEB-04 | 13000 | 7900 |
+------------+-----------+-----------+--------+--------+
| 100 | Kaufling | 01-MAY-03 | 7900 | 7900 |
+------------+-----------+-----------+--------+--------+
| 100 | Kochhar | 21-SEP-05 | 17000 | 7900 |
+------------+-----------+-----------+--------+--------+
| 100 | Mourgos | 16-NOV-07 | 5800 | 5800 |
+------------+-----------+-----------+--------+--------+
| 100 | Partners | 05-JAN-05 | 13500 | 7900 |
+------------+-----------+-----------+--------+--------+
| 100 | Raphaely | 07-DEC-02 | 11000 | 11000 |
+------------+-----------+-----------+--------+--------+
| 100 | Russell | 01-OCT-04 | 14000 | 7900 |
+------------+-----------+-----------+--------+--------+
| . . . | | | | |
+------------+-----------+-----------+--------+--------+
此函数将任何数值数据类型或任何可以隐式转换为数值数据类型的非数值数据类型作为参数。KingbaseES确定具有最高数值优先级的参数，将其余参数隐式转换为该数据类型，并返回该数据类型。
n1如果和的乘积n2为负，则此函数的行为与经典数学模函数不同。经典模数可以使用MOD具有以下公式的函数表示：
n2 - n1 * FLOOR(n2/n1)
mode() WITHIN GROUP (ORDER BY expr [ DESC | ASC | USING operator ] )
[ FILTER ( WHERE filter_clause ) ]
OVER ( [ query_partition_clause ] )
SELECT depname, empno, salary,
mode()
WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY depname)
FROM empsalary ORDER BY depname, salary;
SELECT months_between ('2007-02-28 111111', '2007-04-30 112121');
months_between
---------------------
-2.0647438769414577
(1 row)
NLSSORT返回字符值的排序规则键char和显式或隐式指定的排序规则。排序规则键是一串字节，用于char根据指定的排序规则进行排序。排序规则键的属性是，当根据它们的二进制顺序比较时，为给定排序规则生成的两个这样的键的相互排序与根据给定排序规则比较时源字符值的相互排序相同。
char和 ' nlsparam'都可以是任何数据类型CHAR、VARCHAR、NCHAR或NVARCHAR。
'nlsparam' 的值必须具有以下形式
'NLS_SORT = collation'
其中collation是语言排序规则的名称或BINARY. NLSSORT使用指定的排序规则生成排序规则键。如果省略 ' nlsparam'，则此函数使用参数的派生排序规则char。如果您指定BINARY，则此函数返回char值本身转换为RAW并可能被截断，如下所述。
如果您指定 ' lsparam'，那么您可以将后缀附加到语言排序规则名称_ai以请求不区分重音的排序规则或_ci请求不区分大小写的排序规则。不建议在查询子句中使用不区分重音或不区分大小写的排序规则，ORDER BY因为它会导致不确定的排序顺序。
返回的排序规则键是RAW数据类型。char由给定排序规则的给定值产生的排序规则键的长度可能超过RAW返回值的最大长度NLSSORT。在这种情况下，NLSSORT的行为取决于初始化参数 的值 MAX_STRING_SIZE。如果MAX_STRING_SIZE = EXTENDED，则返回值的最大长度为 32767 字节。如果归类键超过此限制，则函数将失败并出现错误“ORA-12742：无法创建归类键”。如果短输入字符串包含高比例的 Unicode 字符且分解率非常高，则也可能会报告此错误。
如果 MAX_STRING_SIZE = STANDARD，则返回值的最大长度为 2000 字节。如果要返回的值超过限制，则NLSSORT计算最大前缀或初始子字符串的排序规则键，char以便计算结果不超过最大长度。例如FRENCH，对于单语排序规则，前缀长度通常为 1000 个字符。例如GENERIC_M，对于多语言排序规则，前缀通常为 500 个字符。例如，对于 Unicode 归类算法 (UCA) 归类，UCA0610_DUCET前缀通常为 285 个字符。确切的长度可能会更低或更高，具体取决于排序规则和char.
当行为MAX_STRING_SIZE = STANDARD暗示两个字符值的排序规则键（NLSSORT结果）被比较以找到语言顺序时，如果它们在前缀上没有差异，即使它们可能在某些进一步的字符位置不同，则它们被认为是相等的。因为该NLSSORT函数被隐式用于查找比较条件的语言排序，所以BETWEEN条件、IN条件、ORDER BY、GROUP BY和COUNT(DISTINCT)，这些操作可能返回仅对长字符值近似的结果。如果您想保证这些操作的结果是准确的，那么将您的数据库迁移到使用MAX_STRING_SIZE = EXTENDED。
此函数不直接支持CLOB数据。但是，CLOBs 可以通过隐式数据转换作为参数传入。
此函数可用于指定基于语言排序序列而不是字符串的二进制值的排序和比较操作。下面的示例创建一个包含两个值的测试表，并显示函数如何对返回的值进行排序NLSSORT：
CREATE TABLE test (name VARCHAR(15));
INSERT INTO test VALUES ('Gaardiner');
INSERT INTO test VALUES ('Gaberd');
INSERT INTO test VALUES ('Gaasten');
SELECT * FROM test ORDER BY name;
+-----------+
| NAME |
+===========+
| Gaardiner |
+-----------+
| Gaasten |
+-----------+
| Gaberd |
+-----------+
SELECT * FROM test ORDER BY NLSSORT(name, 'NLS_SORT = XDanish');
+-----------+
| NAME |
+===========+
| Gaberd |
+-----------+
| Gaardiner |
+-----------+
| Gaasten |
+-----------+
no rows selected
SELECT * FROM test WHERE NLSSORT(name, 'NLS_SORT = XDanish') > NLSSORT('Gaberd', 'NLS_SORT = XDanish') ORDER BY name;
+-----------+
| NAME |
+===========+
| Gaardiner |
+-----------+
| Gaasten |
+-----------+
ALTER SESSION SET NLS_SORT = 'XDanish';
SELECT * FROM test WHERE name > 'Gaberd' ORDER BY name;
+-----------+
| NAME |
+===========+
| Gaardiner |
+-----------+
| Gaasten |
+-----------+
SELECT nth_value(ten, four + 1) OVER (PARTITION BY four), ten, four
FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten)s;
nth_value | ten | four
-----------+-----+------
0 | 0 | 0
0 | 0 | 0
0 | 4 | 0
1 | 1 | 1
1 | 1 | 1
1 | 7 | 1
1 | 9 | 1
| 0 | 2
| 1 | 3
| 3 | 3
(10 rows)
SELECT ntile(3) OVER (ORDER BY ten, four), ten, four FROM tenk1 WHERE unique2 < 10;
ntile | ten | four
-------+-----+------
1 | 0 | 0
1 | 0 | 0
1 | 0 | 2
1 | 1 | 1
2 | 1 | 1
2 | 1 | 3
2 | 3 | 3
3 | 4 | 0
3 | 7 | 1
3 | 9 | 1
(10 rows)
SELECT PERCENT_RANK(15000, .05) WITHIN GROUP (ORDER BY salary, commission_pct) "Percent-Rank" FROM employees;
+--------------+
| Percent-Rank |
+==============+
| .971962617 |
+--------------+
SELECT department_id, last_name, salary, PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS pr FROM employees ORDER BY pr, salary, last_name;
+---------------+-----------+------------+-------+------------+
| DEPARTMENT_ID | LAST_NAME | SALARY | PR | |
+===============+===========+============+=======+============+
| | 10 | Whalen | 4400 | 0 |
+---------------+-----------+------------+-------+------------+
| | 40 | Mavris | 6500 | 0 |
+---------------+-----------+------------+-------+------------+
| | Grant | 7000 | 0 | |
+---------------+-----------+------------+-------+------------+
| | 80 | Vishney | 10500 | .181818182 |
+---------------+-----------+------------+-------+------------+
| | 80 | Zlotkey | 10500 | .181818182 |
+---------------+-----------+------------+-------+------------+
| | 30 | Khoo | 3100 | .2 |
+---------------+-----------+------------+-------+------------+
| | 50 | Markle | 2200 | .954545455 |
+---------------+-----------+------------+-------+------------+
| | 50 | Philtanker | 2200 | .954545455 |
+---------------+-----------+------------+-------+------------+
| | 50 | Olson | 2100 | 1 |
+---------------+-----------+------------+-------+------------+
| . . . | | | | |
+---------------+-----------+------------+-------+------------+
percentile_cont({fraction | fractions}) WITHIN GROUP (ORDER BY expr [ DESC | ASC | USING operator ] )
[ FILTER ( WHERE filter_clause ) ]
OVER ( [ query_partition_clause ] )
percentile_disc({fraction | fractions}) WITHIN GROUP (ORDER BY expr [ DESC | ASC | USING operator ] )
[ FILTER ( WHERE filter_clause ) ]
OVER ( [ query_partition_clause ] )
p：可以表示为YYMM或YYYYMM格式的bigint、numeric等数值类型、double precision等浮点数类型。参数存在小数时，将其四舍五入转为bigint类型后再计算输出。若参数不能转换为YYMM或YYYYMM格式，函数运行会报错。
n: bigint、numeric等数值类型、double precision等浮点数类型。参数存在小数时，将其四舍五入转为bigint类型后再计算输出。
若参数p为YYMM格式，YY属于0到99之间，系统会自动补位。其若小于70，则系统以2000年后（即20YY）计算，其余情况以1900年后即（19YY）计算。
CREATE TABLE ranks( c VARCHAR(10));
INSERT INTO ranks(c) VALUES ('A'),('B'),('C'),('B'),('E');
SELECT c, RANK() OVER(ORDER BY c) RANK_NUM FROM ranks;
select name, score, ratio_to_report(score) over(partition by name) from t1 order by name;
name | score | ratio_to_report
------+-------+------------------------
joy | 93.5 | 0.48774126238914971309
joy | 98.2 | 0.51225873761085028691
lily | 86.2 | 0.47493112947658402204
lily | 95.3 | 0.52506887052341597796
lucy | 98.2 | 0.50102040816326530612
lucy | 97.8 | 0.49897959183673469388
tom | 91.6 | 0.48723404255319148936
tom | 96.4 | 0.51276595744680851064
Tony | 86.2 | 1.00000000000000000000
Tony | |
(10 rows)
select regexp_count('abc123', '[a-z]'), regexp_count('a1b2c3', '[a-z]') from dual;
regexp_count|regexp_count|
------------+------------+
3| 3|
select REGEXP_COUNT('wElcome','e',1,'i') , REGEXP_COUNT('wElcome','e',1,'c' ) from DUAL;
regexp_count|regexp_count|
------------+------------+
2| 1|
REPLACE char每次出现search_string替换为 时返回replacement_string。如果replacement_string省略或为 null，则search_string删除所有出现的 。如果search_string为空，则char返回。
search_string和replacement_string, 以及,都char可以是任何数据类型CHAR, VARCHAR, NCHAR, NVARCHAR, CLOB, 或NCLOB。返回的字符串与 的字符集相同char。VARCHAR如果第一个参数不是 LOB，则函数返回，如果第一个参数是 LOB，则返回CLOB。
REPLACE提供与该功能提供的功能相关的TRANSLATE功能。TRANSLATE提供单字符的一对一替换。REPLACE允许您用一个字符串替换另一个字符串以及删除字符串。
以下示例替换出现的Jwith BL：
SELECT REPLACE('JACK and JUE','J','BL') "Changes" FROM DUAL;
+----------------+
| Changes |
+================+
| BLACK and BLUE |
+----------------+
ROUND返回date四舍五入到格式模型指定的单位fmt。NLS_CALENDAR该函数对会话参数不敏感。它按照公历的规则运作。返回的值始终是数据类型DATE，即使您为 指定了不同的日期时间数据类型date。如果省略fmt，则date四舍五入到最近的一天。date表达式必须解析为一个DATE值 。
ROUND
TRUNC
日期函数用于允许的格式模型fmt。
n可以是任何数值数据类型或任何可以隐式转换为数值数据类型的非数值数据类型。如果省略integer，则函数返回与ROUND的数值数据类型相同的数据类型的值 (n, 0) n。如果包含integer，则函数返回NUMBER。
ROUND使用以下规则实现：
create table test_t1(id int,name varchar(20));
CREATE TABLE
insert into test_t1 values(1,'t1'),(2,'t2'),(3,'t3'),(4,'t4'),(5,'t5'),(6,'t6');
INSERT 0 6
create table test_t2 (like test_t1);select row_count();
CREATE TABLE
row_count
-----------
(1 row)
create table test_t3 as select * from test_t1;select row_count();
SELECT 6
row_count
-----------
(1 row)
SELECT
department_id, first_name, last_name, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary desc) rn
FROM employees
WHERE rn <= 3
ORDER BY department_id, salary DESC, last_name;
两者expr1和expr2都可以是任何数据类型CHAR、VARCHAR、NCHAR、NVARCHAR、CLOB或NCLOB。VARCHAR如果expr1是字符数据类型，NVARCHAR如果expr1是国家字符数据类型，则返回的字符串是数据类型，如果是expr1LOB 数据类型，则返回 LOB。返回的字符串与 的字符集相同expr1。参数n必须是NUMBER整数或可以隐式转换为NUMBER整数的值。
expr1不能为空。如果不指定expr2，则默认为一个空白。如果expr1长于n，则此函数返回expr1适合的部分n。
参数n是显示在终端屏幕上的返回值的总长度。在大多数字符集中，这也是返回值中的字符数。但是，在某些多字节字符集中，字符串的显示长度可能与字符串中的字符数不同。
以下示例通过用星号填充单个空格来创建一个简单的工资金额图表：
SELECT last_name, RPAD(' ', salary/1000/1, '*') "Salary" FROM employees WHERE department_id = 80 ORDER BY last_name, "Salary";
+-----------+-------------+
| LAST_NAME | Salary |
+===========+=============+
| Abel | ********** |
+-----------+-------------+
| Ande | ***** |
+-----------+-------------+
| Banda | ***** |
+-----------+-------------+
| Bates | ****** |
+-----------+-------------+
| Bernstein | ******** |
+-----------+-------------+
| Bloom | ********* |
+-----------+-------------+
| Cambrault | ********** |
+-----------+-------------+
| Cambrault | ****** |
+-----------+-------------+
| Doran | ****** |
+-----------+-------------+
| Errazuriz | *********** |
+-----------+-------------+
| Fox | ******** |
+-----------+-------------+
| Greene | ******** |
+-----------+-------------+
| Hall | ******** |
+-----------+-------------+
| Hutton | ******* |
+-----------+-------------+
| Johnson | ***** |
+-----------+-------------+
| King | ********* |
+-----------+-------------+
| . . . | |
+-----------+-------------+
SELECT RTRIM('<=====>BROWNING<=====>', '<>=') "RTRIM Example" FROM DUAL;
+-----------------+
| RTRIM Example |
+=================+
| <=====>BROWNING |
+-----------------+
----+-------+--------------------
1 | Tom | hebei shijiazhuang
2 | Toney | hebei zhangjiakou
3 | Lucy | henan luoyang
4 | Jim | henan zhengzhou
5 | Jack |
6 | Bob |
(6 rows)
select id, name, score(10), score(20) from stest where CONTAINS (adds, 'hebei', 10) OR CONTAINS (adds, 'henan', 20) ORDER BY SCORE(10), SCORE(20);
id | name | score | score
----+-------+------------+------------
3 | Lucy | 0 | 0.06079271
4 | Jim | 0 | 0.06079271
1 | Tom | 0.06079271 | 0
2 | Toney | 0.06079271 | 0
(4 rows)
SELECT SHA224('abc'::bytea);
+------------------------------------------------------------+
| SHA224 |
+============================================================+
| \x23097d223405d8228642a477bda255b32aadbce4bda0b3f7e36c9da7 |
+------------------------------------------------------------+
SELECT SHA256('abc'::bytea);
+--------------------------------------------------------------------+
| SHA256 |
+====================================================================+
| \xba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad |
+--------------------------------------------------------------------+
SELECT SHA384('abc'::bytea);
+----------------------------------------------------------------------------------------------------+
| SHA384 |
+====================================================================================================+
| \xcb00753f45a35e8bb5a03d699ac65007272c32ab0eded1631a8b605a43ff5bed8086072ba1e7cc2358baeca134c825a7 |
+----------------------------------------------------------------------------------------------------+
SELECT SHA512('abc'::bytea);
+------------------------------------------------------------------------+
| SHA512 |
+========================================================================+
| \xddaf35a193617abacc417349ae20413112e6fa4e89a97ea20a9eeee64b55d39a2192 |
| 992a274fc1a836ba3c23a3feebbd454d4423643ce80e2a9ac94fa54ca49f |
+------------------------------------------------------------------------+
+-------------------------------+
| STATEMENT_TIMESTAMP |
+===============================+
| 2022-06-28 16:02:08.521282+00 |
+-------------------------------+
STDDEV返回一组数字expr的样本标准差。这是一组数字，您可以将其用作聚合函数和分析函数。它与STDDEV_SAMP的不同之处在于，当只有一行输入时STDDEV返回零，而STDDEV_SAMP返回null不同。
KingbaseES数据库将标准差计算为为聚合函数VARIANCE定义的方差的平方根。
此函数将任何数值数据类型或任何可以隐式转换为数值数据类型的非数值数据类型作为参数。该函数返回与参数的数值数据类型相同的数据类型。
如果您指定DISTINCT，那么您只能指定analytic_clause字句的query_partition_clause字句。不允许使用order_by_clause字句和windowing_clause字句。
“分析函数”获取有关语法、语义和限制的信息。
SELECT last_name, salary, STDDEV(salary) OVER (ORDER BY hire_date) "StdDev" FROM employees WHERE department_id = 30 ORDER BY last_name, salary, "StdDev";
+------------+--------+------------+
| LAST_NAME | SALARY | StdDev |
+============+========+============+
| Baida | 2900 | 4035.26125 |
+------------+--------+------------+
| Clomenares | 2500 | 3362.58829 |
+------------+--------+------------+
| Himuro | 2600 | 3649.2465 |
+------------+--------+------------+
| Khoo | 3100 | 5586.14357 |
+------------+--------+------------+
| Raphaely | 11000 | 0 |
+------------+--------+------------+
| Tobias | 2800 | 4650.0896 |
+------------+--------+------------+
SELECT STDDEV_POP(amount_sold) "Pop", STDDEV_SAMP(amount_sold) "Samp" FROM sales;
+------------+------------+
| Pop | Samp |
+============+============+
| 896.355151 | 896.355592 |
+------------+------------+
SELECT department_id, last_name, salary, STDDEV_POP(salary) OVER (PARTITION BY department_id) AS pop_std FROM employees ORDER BY department_id, last_name, salary, pop_std;
+---------------+-----------+--------+------------+
| DEPARTMENT_ID | LAST_NAME | SALARY | POP_STD |
+===============+===========+========+============+
| 10 | Whalen | 4400 | 0 |
+---------------+-----------+--------+------------+
| 20 | Fay | 6000 | 3500 |
+---------------+-----------+--------+------------+
| 20 | Hartstein | 13000 | 3500 |
+---------------+-----------+--------+------------+
| 30 | Baida | 2900 | 3069.6091 |
+---------------+-----------+--------+------------+
| 100 | Urman | 7800 | 1644.18166 |
+---------------+-----------+--------+------------+
| 110 | Gietz | 8300 | 1850 |
+---------------+-----------+--------+------------+
| 110 | Higgins | 12000 | 1850 |
+---------------+-----------+--------+------------+
| | Grant | 7000 | 0 |
+---------------+-----------+--------+------------+
SELECT department_id, last_name, hire_date, salary, STDDEV_SAMP(salary) OVER (PARTITION BY department_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sdev FROM employees ORDER BY department_id, last_name, hire_date, salary, cum_sdev;
+---------------+------------+-----------+--------+------------+
| DEPARTMENT_ID | LAST_NAME | HIRE_DATE | SALARY | CUM_SDEV |
+===============+============+===========+========+============+
| 10 | Whalen | 17-SEP-03 | 4400 | |
+---------------+------------+-----------+--------+------------+
| 20 | Fay | 17-AUG-05 | 6000 | 4949.74747 |
+---------------+------------+-----------+--------+------------+
| 20 | Hartstein | 17-FEB-04 | 13000 | |
+---------------+------------+-----------+--------+------------+
| 30 | Baida | 24-DEC-05 | 2900 | 4035.26125 |
+---------------+------------+-----------+--------+------------+
| 30 | Colmenares | 10-AUG-07 | 2500 | 3362.58829 |
+---------------+------------+-----------+--------+------------+
| 30 | Himuro | 15-NOV-06 | 2600 | 3649.2465 |
+---------------+------------+-----------+--------+------------+
| 30 | Khoo | 18-MAY-03 | 3100 | 5586.14357 |
+---------------+------------+-----------+--------+------------+
| 30 | Raphaely | 07-DEC-02 | 11000 | |
+---------------+------------+-----------+--------+------------+
| 100 | Greenberg | 17-AUG-02 | 12008 | 2126.9772 |
+---------------+------------+-----------+--------+------------+
| 100 | Popp | 07-DEC-07 | 6900 | 1804.13155 |
+---------------+------------+-----------+--------+------------+
| 100 | Sciarra | 30-SEP-05 | 7700 | 1929.76233 |
+---------------+------------+-----------+--------+------------+
| 100 | Urman | 07-MAR-06 | 7800 | 1788.92504 |
+---------------+------------+-----------+--------+------------+
| 110 | Gietz | 07-JUN-02 | 8300 | 2621.95194 |
+---------------+------------+-----------+--------+------------+
| 110 | Higgins | 07-JUN-02 | 12008 | |
+---------------+------------+-----------+--------+------------+
| | Grant | 24-MAY-07 | 7000 | |
+---------------+------------+-----------+--------+------------+
SELECT string_agg(a,',') FROM (values('aaaa'),('bbbb'),('cccc')) g(a);
string_agg
----------------
aaaa,bbbb,cccc
(1 row)
Assume a double-byte database character set:
SELECT SUBSTRB('ABCDEFG',5,4.2) "Substring with bytes" FROM DUAL;
+----------------------+
| Substring with bytes |
+======================+
| CD |
+----------------------+
SUPPRESS_REDUNDANT_UPDATES_TRIGGER函数是一个内置的触发器函数，它将阻止任
何不会实际更改行中数据的更新发生，这与正常的行为不管数据是否改变始终执行更新相反。冗余更新会花费大量不必要的
时间，尤其是当如果有大量索引要改变时。SUPPRESS_REDUNDANT_UPDATES_TRIGGER 可以跳过不改变数据的更新。但需要注意的是，触
发器需要很短但不能忽略的时间来处理每条记录，所以如果大多数被一个更新影响的记录确实被更改，此触
发器的使用将实际上使更新运行得更慢。
sys_event_trigger_ddl_commands函数在在命令结束处捕捉更改。
当在一个 ddl_command_end 事件触发器的函数中调用时， sys_event_trigger_ddl_commands 返
回被每一个用户动作执行的 DDL 命令的列表。如果在其他任何环境中调用这个函数，会发生错误。
sys_event_trigger_ddl_commands 为每一个被执行的基本命令返回一行，某些只有一个单一 SQL
句子的命令可能会返回多于一行。
返回值说明：
CREATE FUNCTION test_event_trigger_for_drops()
RETURNS event_trigger LANGUAGE plsql AS $$
DECLARE
obj record;
BEGIN
FOR obj IN SELECT * FROM sys_event_trigger_dropped_objects()
RAISE NOTICE '% dropped object: % %.% %',
tg_tag,
obj.object_type,
obj.schema_name,
obj.object_name,
obj.object_identity;
END LOOP;
CREATE EVENT TRIGGER test_event_trigger_for_drops
ON sql_drop
EXECUTE FUNCTION test_event_trigger_for_drops();
CREATE FUNCTION test_event_trigger_table_rewrite_oid()
RETURNS event_trigger
LANGUAGE plsql AS
BEGIN
RAISE NOTICE 'rewriting table % for reason %',
sys_event_trigger_table_rewrite_oid()::regclass,
sys_event_trigger_table_rewrite_reason();
CREATE EVENT TRIGGER test_table_rewrite_oid
ON table_rewrite
EXECUTE FUNCTION test_event_trigger_table_rewrite_oid();
SELECT * from sys_stat_file('base',true);
SIZE | ACCESS | MODIFICATION | CHANGE | CREATION | ISDIR
------+------------------------+------------------------+------------------------+----------+-------
131 | 2022-06-27 14:14:02+08 | 2022-06-17 20:39:48+08 | 2022-06-17 20:39:48+08 | | t
(1 row)
begin
select * into r from sys_attrdef where oid = 16485;
a = sys_get_expr(r.adbin, r.adrelid);
RAISE NOTICE 'a = %', a;
NOTICE: a = nextval('mytest_id_seq'::regclass)
\set SQLTERM /
declare
r record;
a text;
begin
select * into r from sys_attrdef where oid = 16485;
a = sys_get_expr(r.adbin, r.adrelid, true);
RAISE NOTICE 'a = %', a;
NOTICE: a = nextval('mytest_id_seq'::regclass)
sys_get_functiondef
--------------------------------------------------------
CREATE OR REPLACE FUNCTION public.increment(i integer)+
RETURNS integer +
AS +
BEGIN +
RETURN i + 1; +
END +
(1 row)
CREATE UNIQUE INDEX title_idx ON films (title);
select oid from sys_class where relname = 'title_idx';
--------
247564
(1 row)
SELECT sys_get_indexdef(247564);
sys_get_indexdef
-------------------------------------------------------------------
CREATE UNIQUE INDEX title_idx ON public.films USING btree (title)
(1 row)
SELECT sys_get_indexdef(247564, 0, true);
sys_get_indexdef
-------------------------------------------------------------------
CREATE UNIQUE INDEX title_idx ON public.films USING btree (title)
(1 row)
sys_get_keywords
------------------------------------------------------------------
(abort,U,unreserved)
(absolute,U,unreserved)
(access,U,unreserved)
(account,U,unreserved)
(action,U,unreserved)
(add,U,unreserved)
... ...
(569 rows)
select sys_get_object_address('function','{public,func_test}','{int}');
sys_get_object_address
-----------------------
(1255,41016,0)
(1 row)
SELECT * FROM t1;
select sys_get_ruledef((select oid from sys_rewrite where rulename = '_return' and ev_class= (select oid from sys_class where relname='t2')));
sys_get_ruledef
----------------------------------------------------
CREATE RULE _return AS +
ON SELECT TO public.t2 DO INSTEAD SELECT t1.i+
FROM t1;
(1 row)
select sys_get_ruledef((select oid from sys_rewrite where rulename = '_return' and ev_class= (select oid from sys_class where relname='t2')), true);
sys_get_ruledef
---------------------------------------------
CREATE RULE _return AS +
ON SELECT TO t2 DO INSTEAD SELECT t1.i+
FROM t1;
(1 row)
select sys_get_ruledef((select oid from sys_rewrite where rulename = '_return' and ev_class= (select oid from sys_class where relname='t2')), false);
sys_get_ruledef
----------------------------------------------------
CREATE RULE _return AS +
ON SELECT TO public.t2 DO INSTEAD SELECT t1.i+
FROM t1;
(1 row)
INSERT INTO serialTest1 VALUES ('foo');
INSERT INTO serialTest1 VALUES ('bar');
INSERT INTO serialTest1 VALUES ('force', 100);
SELECT * FROM serialTest1;
SELECT currval(sys_get_serial_sequence('serialTest1', 'f2'));
currval
---------
(1 row)
SELECT sys_get_serial_sequence('serialTest1', 'f2');
sys_get_serial_sequence
---------------------------
public.serialtest1_f2_seq
(1 row)
\set SQLTERM ;
CREATE TABLE ab1 (a INTEGER, b INTEGER, c INTEGER);
CREATE STATISTICS IF NOT EXISTS ab1_a_b_stats ON a, b FROM ab1;
CREATE SCHEMA regress_schema_2;
CREATE STATISTICS regress_schema_2.ab1_a_b_stats ON a, b FROM ab1;
SELECT sys_get_statisticsobjdef(oid) FROM sys_statistic_ext WHERE stxname = 'ab1_a_b_stats';
sys_get_statisticsobjdef
-------------------------------------------------------------------
CREATE STATISTICS public.ab1_a_b_stats ON a, b FROM ab1
CREATE STATISTICS regress_schema_2.ab1_a_b_stats ON a, b FROM ab1
(2 rows)
\set SQLTERM ;
CREATE TABLE test_table(a INT, b INT, c INT, d INT);
CREATE TABLE test_result(name VARCHAR(10), c INT);
\set SQLTERM /
CREATE OR REPLACE TRIGGER tri_test_1 AFTER UPDATE OF c ON test_table
FOR EACH ROW AS
BEGIN
INSERT INTO test_result VALUES('t1:new.c', :new.c);
\set SQLTERM ;
SELECT sys_get_triggerdef(oid) from sys_trigger where tgname = 'tri_test_1';
sys_get_triggerdef
-------------------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER tri_test_1 AFTER UPDATE OF c ON public.test_table FOR EACH ROW +
BEGIN +
INSERT INTO test_result VALUES('t1:new.c', :new.c); +
END +
(1 row)
SELECT sys_get_triggerdef(oid, true) from sys_trigger where tgname = 'tri_test_1';
sys_get_triggerdef
------------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER tri_test_1 AFTER UPDATE OF c ON test_table FOR EACH ROW +
BEGIN +
INSERT INTO test_result VALUES('t1:new.c', :new.c); +
END +
(1 row)
SELECT sys_get_triggerdef(oid, false) from sys_trigger where tgname = 'tri_test_1';
sys_get_triggerdef
-------------------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER tri_test_1 AFTER UPDATE OF c ON public.test_table FOR EACH ROW +
BEGIN +
INSERT INTO test_result VALUES('t1:new.c', :new.c); +
END +
(1 row)
这些函数的大部分都有两种变体，一种可以可选地“优质打印”结果。
优质打印的格式可读性更强，但是默认格式更可能被未来版本的KingbaseES以相同的方式解释。
在转出目的中避免使用优质打印输出。为pretty_bool参数传递
false
将得到和不带该参数的变体相同的结果。
wrap_column_int为指定长度的列宽，并隐含了优质打印。
以下示例为重建触发器命令：
\set SQLTERM ;
create table t1(i int, j text);
create view v1 as select * from t1;
select sys_get_viewdef('v1'::regclass);
sys_get_viewdef
----------------
SELECT t1.i, +
t1.j +
FROM t1;
(1 row)
通过guc参数guid_default_return_type（取值bytea或name）来控制SYS_GUID的返回值。
该参数只能在kingbase.conf中设置。默认值是'name'，返回NAME类型。
如修改为'bytea'，需要修改kingbase.conf配置文件，重启后再执行select alter_sys_guid()才能生效，返回BYTEA类型。
以下示例将一列添加到示例表中hr.locations，将唯一标识符插入每一行，并返回RAW全局唯一标识符的 16 字节值的 32 个字符的十六进制表示：
ALTER TABLE locations ADD (uid_col RAW(16));
UPDATE locations SET uid_col = SYS_GUID();
SELECT location_id, uid_col FROM locations ORDER BY location_id, uid_col;
+-------------+----------------------------------+
| LOCATION_ID | UID_COL |
+=============+==================================+
| 1000 | 09F686761827CF8AE040578CB20B7491 |
+-------------+----------------------------------+
| 1100 | 09F686761828CF8AE040578CB20B7491 |
+-------------+----------------------------------+
| 1200 | 09F686761829CF8AE040578CB20B7491 |
+-------------+----------------------------------+
| 1300 | 09F68676182ACF8AE040578CB20B7491 |
+-------------+----------------------------------+
| 1400 | 09F68676182BCF8AE040578CB20B7491 |
+-------------+----------------------------------+
| 1500 | 09F68676182CCF8AE040578CB20B7491 |
+-------------+----------------------------------+
| . . . | |
+-------------+----------------------------------+
select * from sys_identify_object(1259, 14886, 0);
+------+-------------+-----------+-----------------------+
| type | schema | name | identity |
+======+=============+===========+=======================+
| view | sys_catalog | sys_class | sys_catalog.sys_class |
+------+-------------+-----------+-----------------------+
返回一行数据，包含由参数目录OID、对象OID和一个（可能为零的）子对象ID所唯一标识的数据库对象的信息。返回的信息独立于当前服务器，也就是说，它可以被用来在另一个服务器中标识一个具有相同命名的对象。
标识数据库对象的类型；
object_names
object_args
是文本数组，它们一起构成了对对象的引用。这三个值可以被传递给
sys_get_object_address
以获得该对象的内部地址。这个函数是
sys_get_object_address
的逆函数。
select * from sys_identify_object_as_address(1259, 14886, 0);
+------+-------------------------+-------------+
| type | object_names | object_args |
+======+=========================+=============+
| view | {sys_catalog,sys_class} | {} |
+------+-------------------------+-------------+
+-------------------------------+
| sys_kingbase_start_time |
+===============================+
| 2022-06-28 14:03:47.041437+08 |
+-------------------------------+
+---------------------------------------------------------------+
| sys_ls_logdir |
|---------------------------------------------------------------|
| (kingbase-2022-06-28_140347.log,562,"2022-06-28 16:15:28+08") |
+---------------------------------------------------------------+
+--------------------------------------------------------------+
| sys_ls_waldir |
|--------------------------------------------------------------|
| (000000010000000000000001,16777216,"2022-06-28 16:19:57+08") |
+--------------------------------------------------------------+
SELECT sys_relation_filenode(oid) FROM sys_class WHERE relname='test';
+-----------------------+
| sys_relation_filenode |
+=======================+
| 24878 |
+-----------------------+
SELECT sys_relation_filepath(oid) FROM sys_class WHERE relname='test';
+-----------------------+
| sys_relation_filepath |
+=======================+
| base/16078/24878 |
+-----------------------+
SELECT size, sys_size_bytes(size) FROM (VALUES ('1'), ('123bytes'), ('1kB'), ('1MB'), (' 1 GB'), ('1.5 GB '),('1TB'), ('3000 TB'), ('1e6 MB')) x(size);
+----------+------------------+
| size | sys_size_bytes |
+==========+==================+
| 1 | 1 |
| 123bytes | 123 |
| 1kB | 1024 |
| 1MB | 1048576 |
| 1 GB | 1073741824 |
| 1.5 GB | 1610612736 |
| 1TB | 1099511627776 |
| 3000 TB | 3298534883328000 |
| 1e6 MB | 1048576000000 |
+----------+------------------+
+----------------------------+
| sys_tablespace_databases |
|----------------------------|
| 1 |
| 16051 |
| 16052 |
| 16053 |
+----------------------------+
SELECT TAN(135 * 3.14159265359/180) "Tangent of 135 degrees" FROM DUAL;
+------------------------+
| Tangent of 135 degrees |
+========================+
| - 1 |
+------------------------+
-24:00:00
SELECT TIMEDIFF(timestamp'2020-01-01 12:00:00.1',timestamp'2020-01-02 22:23:02.9') "TIMEDIFF";
TIMEDIFF
--------------
-34:23:02.8
SELECT TIMEDIFF(interval'1 12:00:00',interval'84:00:00') "TIMEDIFF";
TIMEDIFF
--------------
-48:00:00
SELECT TIMEDIFF(date'2020-01-01',interval'01:00:00') "TIMEDIFF";
TIMEDIFF
--------------
unit：间隔单位，text类型，值域为：microsecond, second, minute, hour, day, week, month, quarter, 和 year。
interval：间隔值，float类型。
n：date、timestamp、time等时间日期类型。
2023-01-01 00:00:00
SELECT TIMESTAMPADD('second',1.40000 ,date'2022-01-01') "TIMESTAMPADD";
TIMESTAMPADD
-----------------------
2022-01-01 00:00:01.4
SELECT TIMESTAMPADD('second',1.40001 ,date'2022-01-01') "TIMESTAMPADD";
TIMESTAMPADD
---------------------------
2022-01-01 00:00:01.40001
SELECT TIMESTAMPADD('second',NULL,date'2022-01-01') "TIMESTAMPADD";
TIMESTAMPADD
--------------
SELECT TIMESTAMPDIFF('year',date'2022-01-01',date'2000-01-01') "TIMESTAMPDIFF";
TIMESTAMPDIFF
------------------
SELECT TIMESTAMPDIFF('second',timestamp'2022-01-01 12:00:00',timestamp'2022-01-01 12:12:23') "TIMESTAMPDIFF";
TIMESTAMPDIFF
------------------
SELECT TIMESTAMPDIFF(NULL,timestamp'2022-01-01 12:00:00',timestamp'2022-01-01 12:12:23') "TIMESTAMPDIFF";
TIMESTAMPDIFF
----------------
time_format(time,format) 将时间串按照格式输出。格式字符串值只包含时间的小时、分钟、秒、微秒部分。其他格式字符串将返回NULL值或者0。如果时间值中的小时部分大于23，则%H和%K小时格式说明符会产生一个比通常范围0..23大的值。其他小时格式说明符会产生为模的小时值。
-> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');
-> '100 100 04 04 4'
参数说明：
time：时间串。
format: 输出格式串，如下说明：
select time_format('13:56:09','%H %h %k %l %I %i %S %s %p %r %T %f');
'13 01 13 1 01 56 09 09 PM 01:56:09 PM 13:56:09 000000'
select time_format('09:56:09','%H %h %k %l %I %i %S %s %p %r %T %f');
'09 09 9 9 09 56 09 09 AM 09:56:09 AM 09:56:09 000000'
select timesub('2017-2-28 23:59:1'::timestamptz, '2017-3-1 1:1:1'::timestamptz);
timesub
---------
(1 row)
函数将传入的字符串转换为 base64 编码的形式，返回结果为具有连接字符集和排序规则的字符串。输入参数为null时，返回结果为null；输入为非字符串时，若参数为纯数字，默认转为字符串处理，其他会返回null。
to_base64
编码的字符串可以通过
from_base64
函数进行解码。
to_base64
from_base64
函数使用的编码加密规则：
TO_CHAR(bfile|blob) 将数据BFILE或BLOB数据转换为数据库字符集。返回的值始终是VARCHAR。如果返回的值太大而无法适应VARCHAR数据类型，则数据将被截断。
对于csid，指定BFILE或者BLOB数据的字符集 ID 。如果BFILE或者BLOB数据的字符集是数据库字符集，则可以为csid 指定值 0，或完全省略csid。
下面的假设示例将media_table 中的一个BFILE列media_col列作为其输入，该列使用 ID 为 873 的字符集。该示例返回一个使用数据库字符集VARCHAR的值。
SELECT TO_CHAR(media_col, 873) FROM media_tab;
SELECT TO_CHAR(ad_sourcetext) FROM print_media WHERE product_id = 2268;
TO_CHAR(AD_SOURCETEXT)
----------------------------------------+
TIGER2 2268...标准 Hayes 兼容调制解调器
产品编号：2268
宇宙中销量第一的调制解调器！Tiger2 的调制解调器包括呼叫管理和互联网配音。同时拨打实时全双工电话你在线。
CREATE TABLE empl_temp (employee_id NUMBER(6),
first_name VARCHAR(20),
last_name VARCHAR(25),
email VARCHAR(25),
hire_date DATE DEFAULT SYSDATE,
job_id VARCHAR(10),
clob_column CLOB );
INSERT INTO empl_temp VALUES(111,'John','Doe','example.com','10-JAN-2015','1001','ExperiencedEmployee');
INSERT INTO empl_temp VALUES(112,'John','Smith','example.com','12-JAN-2015','1002','JuniorEmployee');
INSERT INTO empl_temp VALUES(113,'Johnnie','Smith','example.com','12-JAN-2014','1002','Mid-CareerEmployee');
INSERT INTO empl_temp VALUES(115,'Jane','Doe','example.com','15-JAN-2015','1005','ExecutiveEmployee');
SELECT To_char(clob_column) "CLOB_TO_CHAR" FROM empl_temp WHERE employee_id IN ( 111, 112, 115 );
+----------------------+
| CLOB_TO_CHAR |
+======================+
| Experienced Employee |
+----------------------+
| Junior Employee |
+----------------------+
| Executive Employee |
+----------------------+
ALTER SESSION SET TIME_ZONE = '-8:00';
INSERT INTO date_tab VALUES ( TIMESTAMP'1999-12-01 10:00:00', TIMESTAMP'1999-12-01 10:00:00', TIMESTAMP'1999-12-01 10:00:00');
INSERT INTO date_tab VALUES ( TIMESTAMP'1999-12-02 10:00:00 -8:00', TIMESTAMP'1999-12-02 10:00:00 -8:00', TIMESTAMP'1999-12-02 10:00:00 -8:00');
SELECT TO_CHAR(ts_col, 'DD-MON-YYYY HH24:MI:SSxFF') AS ts_date, TO_CHAR(tstz_col, 'DD-MON-YYYY HH24:MI:SSxFF TZH:TZM') AS tstz_date FROM date_tab ORDER BY ts_date, tstz_date;
+-----------------------------+------------------------------------+
| TS_DATE | TSTZ_DATE |
+=============================+====================================+
| 01-DEC-1999 10:00:00.000000 | 01-DEC-1999 10:00:00.000000 -08:00 |
+-----------------------------+------------------------------------+
| 02-DEC-1999 10:00:00.000000 | 02-DEC-1999 10:00:00.000000 -08:00 |
+-----------------------------+------------------------------------+
SELECT SESSIONTIMEZONE, TO_CHAR(tsltz_col, 'DD-MON-YYYY HH24:MI:SSxFF') AS tsltz FROM date_tab ORDER BY sessiontimezone, tsltz;
+------------------+-----------------------------+
| SESSIONTIM TSLTZ | |
+==================+=============================+
| -08:00 | 01-DEC-1999 10:00:00.000000 |
+------------------+-----------------------------+
| -08:00 | 02-DEC-1999 10:00:00.000000 |
+------------------+-----------------------------+
ALTER SESSION SET TIME_ZONE = '-5:00';
SELECT TO_CHAR(ts_col, 'DD-MON-YYYY HH24:MI:SSxFF') AS ts_col, TO_CHAR(tstz_col, 'DD-MON-YYYY HH24:MI:SSxFF TZH:TZM') AS tstz_col FROM date_tab ORDER BY ts_col, tstz_col;
+-----------------------------+------------------------------------+
| TS_COL | TSTZ_COL |
+=============================+====================================+
| 01-DEC-1999 10:00:00.000000 | 01-DEC-1999 10:00:00.000000 -08:00 |
+-----------------------------+------------------------------------+
| 02-DEC-1999 10:00:00.000000 | 02-DEC-1999 10:00:00.000000 -08:00 |
+-----------------------------+------------------------------------+
SELECT SESSIONTIMEZONE, TO_CHAR(tsltz_col, 'DD-MON-YYYY HH24:MI:SSxFF') AS tsltz_col FROM date_tab ORDER BY sessiontimezone, tsltz_col;
+------------+-----------------------------+
| SESSIONTIM | TSLTZ_COL |
+============+=============================+
| -05:00 | 01-DEC-1999 13:00:00.000000 |
+------------+-----------------------------+
| -05:00 | 02-DEC-1999 13:00:00.000000 |
+------------+-----------------------------+
WITH dates AS (
SELECT date'2015-01-01' d FROM dual union
SELECT date'2015-01-10' d FROM dual union
SELECT date'2015-02-01' d FROM dual )
SELECT d "Original Date",
to_char(d, 'dd-mm-yyyy') "Day-Month-Year",
to_char(d, 'hh24:mi') "Time in 24-hr format",
to_char(d, 'iw-iyyy') "ISO Year and Week of Year"
FROM dates;
WITH dates AS (
SELECT date'2015-01-01' d FROM dual union
SELECT date'2015-01-10' d FROM dual union
SELECT date'2015-02-01' d FROM dual union
SELECT timestamp'2015-03-03 23:44:32' d FROM dual union
SELECT timestamp'2015-04-11 12:34:56' d FROM dual )
SELECT d "Original Date",
to_char(d, 'dd-mm-yyyy') "Day-Month-Year",
to_char(d, 'hh24:mi') "Time in 24-hr format",
to_char(d, 'iw-iyyy') "ISO Year and Week of Year",
to_char(d, 'Month') "Month Name",
to_char(d, 'Year') "Year"
FROM dates;
WITH dates AS (
SELECT date'2015-01-01' d FROM dual union
SELECT date'2015-01-10' d FROM dual union
SELECT date'2015-02-01' d FROM dual union
SELECT timestamp'2015-03-03 23:44:32' d FROM dual union
SELECT timestamp'2015-04-11 12:34:56' d FROM dual )
SELECT extract(minute from d) minutes,
extract(hour from d) hours,
extract(day from d) days,
extract(month from d) months,
extract(year from d) years
FROM dates;
SELECT 10 n FROM dual union
SELECT 9.99 n FROM dual union
SELECT 1000000 n FROM dual --one million )
SELECT n "Input Number N",
to_char(n),
to_char(n, '9,999,999.99') "Number with Commas",
to_char(n, '0,000,000.000') "Zero-padded Number",
to_char(n, '9.9EEEE') "Scientific Notation"
FROM nums;
SELECT 9.99 n FROM dual union
SELECT .99 n FROM dual union
SELECT 1000000 n FROM dual --one million )
SELECT n "Input Number N",
to_char(n),
to_char(n, '9,999,999.99') "Number with Commas",
to_char(n, '0,000,000.000') "Zero_padded Number",
to_char(n, '9.9EEEE') "Scientific Notation",
to_char(n, '$9,999,990.00') Monetary,
to_char(n, 'X') "Hexadecimal Value" FROM nums;
SELECT 9.99 n FROM dual union
SELECT .99 n FROM dual union
SELECT 1000000 n FROM dual --one million )
SELECT n "Input Number N",
to_char(n),
to_char(n, '9,999,999.99') "Number with Commas",
to_char(n, '0,000,000.000') "Zero_padded Number",
to_char(n, '9.9EEEE') "Scientific Notation",
to_char(n, '$9,999,990.00') Monetary,
to_char(n, 'XXXXXX') "Hexadecimal Value"
FROM nums;
clob_column CLOB );
INSERT INTO empl_temp VALUES(111,'John','Doe','example.com','10-JAN-2015','1001','Experienced Employee');
INSERT INTO empl_temp VALUES(112,'John','Smith','example.com','12-JAN-2015','1002','Junior Employee');
INSERT INTO empl_temp VALUES(113,'Johnnie','Smith','example.com','12-JAN-2014','1002','Mid-Career Employee');
INSERT INTO empl_temp VALUES(115,'Jane','Doe','example.com','15-JAN-2015','1005','Executive Employee');
SELECT hire_date "Default",
TO_CHAR(hire_date,'DS') "Short",
TO_CHAR(hire_date,'DL') "Long"FROM empl_temp
WHERE employee_id IN (111, 112, 115);
+-----------+-----------+----------------------------+
| Default | Short | Long |
+===========+===========+============================+
| 10-JAN-15 | 1/10/2015 | Saturday, January 10, 2015 |
+-----------+-----------+----------------------------+
| 12-JAN-15 | 1/12/2015 | Monday, January 12, 2015 |
+-----------+-----------+----------------------------+
| 15-JAN-15 | 1/15/2015 | Thursday, January 15, 2015 |
+-----------+-----------+----------------------------+
使用可选的数字格式fmt将n转换为VARCHAR数据类型的值。n类型为NUMBER、BINARY_FLOAT或BINARY_DOUBLE，如果省略了fmt，那么n将转换为VARCHAR值，其长度恰好足以容纳其有效数字。
如果n为负数，则在应用格式后应用符号。因此TO_CHAR(-1, '$9')返回 -$1，而不是 $-1。
有关数字格式的信息， 请参阅
该'nlsparam'参数指定由数字格式元素返回的这些字符：
SELECT TO_CHAR(-10000,'L99G999D99MI',
'NLS_NUMERIC_CHARACTERS = '',.''
NLS_CURRENCY = ''AusDollars'' ') "Amount"
FROM DUAL;
+----------------------+
| Amount |
+======================+
| AusDollars10.000,00- |
+----------------------+
SELECT TO_CHAR(-10000,'99G999D99C',
'NLS_NUMERIC_CHARACTERS = '',.''
NLS_ISO_CURRENCY=POLAND') "Amount"
FROM DUAL;
+---------------+
| Amount |
+===============+
| -10.000,00PLN |
+---------------+
clob_column CLOB );
INSERT INTO empl_temp VALUES(111,'John','Doe','example.com','10-JAN-2015','1001','Experienced Employee');
INSERT INTO empl_temp VALUES(112,'John','Smith','example.com','12-JAN-2015','1002','Junior Employee');
INSERT INTO empl_temp VALUES(113,'Johnnie','Smith','example.com','12-JAN-2014','1002','Mid-Career Employee');
INSERT INTO empl_temp VALUES(115,'Jane','Doe','example.com','15-JAN-2015','1005','Executive Employee');
SELECT To_char(employee_id) "NUM_TO_CHAR" FROM empl_temp WHERE employee_id IN ( 111, 112, 113, 115 );
+-------------+
| NUM_TO_CHAR |
+=============+
| 111 |
+-------------+
| 112 |
+-------------+
| 113 |
+-------------+
| 115 |
+-------------+
可选子句DEFAULT return_value ON CONVERSION ERROR允许您指定此函数在char转换为DATE时发生错误时返回的值。如果在评估char时发生错误，则此子句无效。return_value可以是表达式或绑定变量，它的计算结果必须为VARCHAR、NCHAR、NVARCHAR数据类型的CHAR字符串，或者为 null。该函数将char转换为DATE使用与return_value转换为DATE相同的方法。如果return_value无法转换为 DATE，则函数返回错误。
是指定格式的fmt日期时间模型格式char。如果省略fmt，则char必须采用默认日期格式。默认日期格式由NLS_TERRITORY初始化参数隐式确定，也可以由NLS_DATE_FORMAT参数显式设置。如果fmt是J，对于朱利安，则char必须是整数。
'January 15, 1989, 11:00 A.M.',
'Month dd, YYYY, HH:MI A.M.',
'NLS_DATE_LANGUAGE = American')
FROM DUAL;
+-----------+
| TO_DATE |
+===========+
| 15-JAN-89 |
+-----------+
'January 15, 1989, 11:00 A.M.',
'Month dd, YYYY, HH:MI A.M.',
'NLS_DATE_LANGUAGE = American')
FROM DUAL;
+----------+
| TO_DATE |
+==========+
| 89/01/15 |
+----------+
SELECT TO_DATE('Febuary 15, 2016, 11:00 A.M.'
DEFAULT 'January 01, 2016 12:00 A.M.' ON CONVERSION ERROR,
'Month dd, YYYY, HH:MI A.M.') "Value"
FROM DUAL;
+-----------+
| Value |
+===========+
| 01-JAN-16 |
+-----------+
SELECT to_datetime('1991-07-09 10:08:55', 'YYYY-MM-DD HH24:MI:SS');
+---------------------+
| TO_DATETIME |
+=====================+
| 1991-07-09 10:08:55 |
+---------------------+
SELECT to_datetime('1991-07-09 10:08:55');
+--------------------------+
| TO_DATETIME |
+==========================+
| Tue Jul 09 10:08:55 1991 |
+--------------------------+