金仓数据库 KingbaseES SQL 语言参考手册 (6. 表达式）

最新推荐文章于 2024-02-23 11:29:54 发布

沉舟侧畔千帆过_

最新推荐文章于 2024-02-23 11:29:54 发布

阅读量311

点赞数

分类专栏：数据库 KingbaseES产品手册文章标签：数据库 sql java

本文链接：https://blog.csdn.net/arthemis_14/article/details/125977941

版权

数据库同时被 2 个专栏收录

550 篇文章 29 订阅

订阅专栏

KingbaseES产品手册

486 篇文章 55 订阅

订阅专栏

6. 表达式

本章介绍如何将值、运算符和函数组合成表达式。

本章包括以下部分：

SQL表达式简介

简单表达式

复合表达式

条件表达式

列表达式

日期时间表达式

函数表达式

时间间隔表达式_

JSON对象访问表达式

对象访问表达式

占位符表达式

标量子查询表达式

类型构造表达式

表达式列表

6.1. SQL表达式简介

一个表达式可以包含一个或者多个值，操作符，以及返回标量值的SQL函数。表达式通常需要推测和假设其包含的成员的数据类型。

这个简单的表达式计算结果为 4 并且具有数据类型int（与其组件相同的数据类型）：

2*2

以下表达式是一个使用函数和运算符的更复杂表达式的示例。在当前日期增加7天，从总和中删除时间部分，并将结果转换为CHAR数据类型：

TO_CHAR (TRUNC (SYSDATE + 7))

您可以在以下位置使用表达式：

SELECT语句的目标列表

WHERE子句和HAVING子句中的条件中

CONNECT BY,START WITH和ORDER BY子句

INSERT语句中的VALUES 子句

UPDATE语句中的SET子句

例如，您可以在UPDATE的SET子句中使用'Smith' ：

SET last_name = 'Smith';

下面的语句中使用了表达式INITCAP( last_name) 代替了上面的 ' Smith'：

SET last_name = INITCAP(last_name);

表达式有多种形式，如以下列表所示：

simple_expression

compound_expression

calc_meas_expression

case_expression

cursor_expression

datetime_expression

function_expression

interval_expression

JSON_object_expression

object_access_expression

scalar_subquery_expression

type_constructor_expression

variable_expression

6.2. 简单表达式

简单表达式包括列，伪列，常量，序列和null。

除了用户模式之外，模式也可以是“PUBLIC”，在这种情况下，它必须限定表、视图或物化视图的公共同义词。只有数据操作语言（DML）语句支持将公共同义词限定为“PUBLIC”，而数据定义语言（DDL）语句不支持。

ROWID只能用表指定，不能用视图或物化视图指定。NCHAR和NVARCHAR2不是有效的伪列数据类型。

一些有效的简单表达式是：

employees.last_name

'this is a text string'

6.3. 复合表达式

复合表达式指定其他表达式的组合。

可以使用任何内置函数作为表达式 ( Function Expressions )。但是，不是所有的函数联合使用都是合理的。例如，该LENGTH函数在聚合函数中不合适。

PRIOR运算符用于CONNECT BY层次查询的子句中。

COLLATE运算符确定表达式的排序规则。此运算符覆盖数据库使用标准排序规则派生规则为表达式派生的排序规则。

一些有效的复合表达式是：

（'ZhangSan'||'LiSi'）

LENGTH（'WangWu'）* 57

SQRT（144）+ 72

my_fun(TO_CHAR(sysdate,'DD-MMM-YY'))

name COLLATE BINARY_CI

6.4. 条件表达式

CASE表达式允许您在 SQL 语句中使用IF... THEN...ELSE逻辑，而无需调用过程。语法是：

simple_case_expression

searched_case_expression

else_clause

在一个简单的CASE表达式中，KingbaseES 数据库搜索第一个WHEN...THEN对，使expr等于comparison_expr并返回return_expr。如果对WHEN... THEN都不满足此条件，并且存在ELSE子句，则 KingbaseES返回else_expr，否则，KingbaseES返回 null。

在搜索CASE表达式中，KingbaseES 从左到右搜索，直到找到一个condition为真的匹配项，然后返回return_expr。如果没有condition为真，并且存在ELSE子句，则 KingbaseES 返回else_expr。否则，KingbaseES 返回 null。

KingbaseES 数据库使用最小化计算。对于一个简单的CASE表达式，数据库comparison_expr仅在将每个值进行比较之前评估每个值expr，而不是再将其中任何一个值与comparison_expr比较。因此，如果前一个comparison_expr等于expr，KingbaseES 永远不会计算comparison_expr。对于搜索到的CASE表达式，数据库评估每个表达式condition以确定它是否为真，如果前一个表达式为真，则从不计算条件。

对于简单CASE表达式，expr和所有comparison_expr值必须具有相同的数据类型（CHAR、VARCHAR、NUMBER、BINARY_FLOAT或BINARY_DOUBLE）或都必须具有数字数据类型。如果所有表达式都具有数字数据类型，则 KingbaseES将确定具有最高数字优先级的参数，隐式的将其与参数转换为该数据类型，并返回该数据类型。

对于简单表达式和搜索CASE表达式，所有return_exprs 必须具有相同的数据类型（CHAR、VARCHAR、NUMBER、BINARY_FLOAT或BINARY_DOUBLE），或者都必须具有数字数据类型。如果所有返回表达式都具有数字数据类型，则 KingbaseES 确定具有最高数字优先级的参数，将其余参数隐式转换为该数据类型，并返回该数据类型。

CASE如果比较的参数具有字符数据类型（CHAR、VARCHAR），则简单表达式执行的比较是排序规则敏感的，排序规则核对确定规则确定要使用的排序规则。

简单的 CASE 示例

对于示例oe.customers表，表中的每个客户，以下语句将信用额度列为“Low”（如果等于 100 美元）、“High”（如果等于 5000 美元）和“Medium”（如果等于其他任何值）。

select cust_last_name,CASE credit_limit WHEN 100 THEN 'Low' when 5000 then“high” ELSE 'Medium' END As credit From customer ORDER BY cust_last_name，credit；

CUST_LAST_NAME

CREDIT

Adjani

Medium

Adjani

Medium

Alexander

Medium

Alexander

Medium

Altman

High

Altman

Medium

搜索案例示例

下面的语句找出样本表oe.employees中雇员的平均工资，使用 $2000 作为可能的最低工资：

SELECT AVG(CASE WHEN e.salary > 2000 THEN e.salary ELSE 2000 END) “Average Salary” FROM employees e；

Average Salary

6461.68224

CUST_LAST_NAME	CREDIT
Adjani	Medium
Adjani	Medium
Alexander	Medium
Alexander	Medium
Altman	High
Altman	Medium

Average Salary
6461.68224

6.5. 列表达式

列表达式（column_expression）在表达式可以是简单表达式、复合表达式、函数表达式或表达式列表，但它只能包含以下表达式形式：

表的列

常量（字符串或数字）

确定性函数——SQL 内置函数或用户定义函数

本章中描述的其他表达形式均无效。

您可以将列表达式用于以下目的：

创建基于函数的索引。

显式或隐式定义虚拟列。

列表达式的组合组件必须是确定性的。也就是说，同一组输入值必须返回同一组输出值。

6.6. 日期时间表达式

一个日期时间表达式（datetime_expresion）产生一种类型的时间值。

日期时间表达式:

产生日期时间值的三个组合在日期时间表达式中是有效的。

如果您指定AT LOCAL，则 KingbaseES会使用当前会话时区。

设置AT TIME ZONE的解释如下：

字符串'[+|-]hh: mi' 将时区指定为与 UTC 的偏移量。对于hh，指定小时数。对于mi，指定分钟数。

time_zone_name：KingbaseES datetime_value_expr在指示的时区返回time_zone_name。

expr：如果expr返回具有有效时区格式的字符串，则 KingbaseES 返回该时区的输入。否则，KingbaseES 返回错误。

以下示例将一个时区的日期时间值转换为另一个时区：

SELECT FROM_TZ(CAST(TO_DATE('1999-12-01 11:00:00','YYYY-MM-DD HH:MI:SS') AS TIMESTAMP), 'America/New_York') AT TIME ZONE 'America/Los_Angeles' "West Coast Time" FROM DUAL;

West Coast Time

01-DEC-99 08.00.00.000000 AM AMERICA/LOS_ANGELES

West Coast Time
01-DEC-99 08.00.00.000000 AM AMERICA/LOS_ANGELES

6.7. 函数表达式

您可以使用任何内置 SQL 函数或用户定义的函数作为表达式。一些有效的内置函数表达式是：

LENGTH（'BLAKE'）

ROUND(1234.567*43)

SYSDATE

用户定义的函数表达式可以调用：

KingbaseES 内置的包中的函数

用户定义的包或类型中的函数

用户定义的函数或运算符

一些有效的用户定义函数表达式是：

circle_area（radius）

payroll.tax_rate(empno)

hr.employees.comm_pct@remote（dependents，empno）

DBMS_LOB.getlength(column_name)

My_function（a_column）

在用作表达式的用户定义函数中，支持位置、命名和混合表示法。例如，以下所有符号都是正确的：

call my_function(arg1 => 3, arg2 => 4) ...

call my_function(3, 4) ...

call my_function(3, arg2 => 4) ...

6.8. JSON对象访问表达式

JSON 对象访问表达式仅在查询 JavaScript 对象表示法 (JSON) 数据列时使用。它生成一个字符串，其中包含在该数据中找到的一个或多个 JSON 值。这种类型的表达式的语法称为点符号语法。

JSON_object_access_expr:

array_step:

对于table_alias，指定包含 JSON 数据列的表的别名。此表别名是必需的，并且必须在 SQL 语句中的其他位置分配给该表。

对于JSON_column，指定 JSON 数据列的名称。该列必须是数据类型VARCHAR2, CLOB, 或BLOB并且IS JSON必须在该列上定义一个检查约束。

您可以选择指定一个或多个 JSON 对象键。对象键允许您定位 JSON 数据中的特定 JSON 值。第一个JSON_object_key必须是与 JSON 数据顶层对象成员的键（属性）名称区分大小写匹配。如果该对象成员的值是另一个 JSON 对象，那么您可以指定第二个JSON_object_key与该对象成员的键名匹配，依此类推。如果在任何这些迭代期间遇到 JSON 数组，并且您没有指定array_step，则该数组将被隐式解包，并使用JSON_object_key.

如果 JSON 值是一个数组，那么您可以选择指定一个或多个array_step 子句。这允许您访问 JSON 数组的特定元素。

用于integer指定integerJSON 数组中索引处的元素。用于指定两个索引值（含）之间的元素范围。如果指定的元素存在于正在计算的 JSON 数组中，则数组步骤会进行与这些元素匹配。否则，数组步骤不会进行匹配。JSON 数组中的第一个元素的索引为 0。

使用星号通配符 ( *) 指定 JSON 数组中的所有元素。如果正在评估的 JSON 数组包含至少一个元素，则数组步骤会匹配 JSON 数组中的所有元素。否则，数组步骤不会进行匹配。

JSON 对象访问表达式产生一个数据类型的字符串VARCHAR2(4000)，其中包含目标 JSON 值，如下所示：

对于单个目标值，字符串包含该值，无论它是 JSON 标量值、对象还是数组。

对于多个目标值，字符串包含一个 JSON 数组，其元素就是这些值。

如果省略JSON_object_key，则表达式会生成一个包含完整 JSON 数据的字符串。在这种情况下，字符串与被查询的JSON数据列的数据类型相同。

JSON 对象访问表达式不能返回大于 4K 字节的值。如果值超过此限制，则表达式返回 null。要获取实际值，请改用JSON_QUERY函数或JSON_VALUE函数，并使用子句指定适当的返回类型RETURNING。

JSON 对象访问表达式的排序规则派生规则与JSON_QUERY函数相同。

例子:

以下示例使用j_purchaseorder表，它是在创建包含 JSON 文档的表的示例中创建的表。此表包含一列名为 po_document 的 JSON 数据列。这些示例从列po_document中返回 JSON 值。

下面语句返回键为 PONumber的属性的值：

SELECT po.po_document.PONumber FROM j_purchaseorder po;

PONumber

1600

以下语句首先针对键名为ShippingInstructions的属性为目标，该属性的值为一个JSON 对象。然后，该语句针对该对象中键名为Phone的属性。该语句返回Phone的值，它是一个 JSON 数组。

SELECT po.po_document.ShippingInstructions.Phone FROM j_purchaseorder po;

SHIPPINGINSTRUCTIONS

[{"type":"Office","number":"909-555-7307"},{"type":"Mobile","number":"415-555-1234"}]

以下语句首先针对键名为LineItems的属性为目标，其值为 JSON 数组。该表达式隐式展开数组并计算其元素，即 JSON 对象。接下来，该语句以未包装对象中键名为Part的属性为目标，并找到两个对象。然后，该语句针对李啷个对象中键名为Description的属性为目标，并查找字符串值。因为返回了多个值，所以这些值作为 JSON 数组的元素返回。

SELECT po.po_document.LineItems.Part.Description FROM j_purchaseorder po;

LINEITEMS

[One Magic Christmas,Lethal Weapon]

PONumber
1600

SHIPPINGINSTRUCTIONS
[{"type":"Office","number":"909-555-7307"},{"type":"Mobile","number":"415-555-1234"}]

LINEITEMS
[One Magic Christmas,Lethal Weapon]

6.9. 对象访问表达式

对象访问表达式指定属性引用和方法调用。

object_access_expression:

列参数可以是对象或REF列。如果您指定expr，则它必须解析为对象类型。

在 SQL 语句的上下文中调用类型的成员函数时，如果SELF参数为 null，则 KingbaseES 返回 null 并且不调用该函数。

例子:

以下示例基于示例oe.order_item_typ对象类型创建一个表，然后显示如何更新对象列属性并从中进行选择。

CREATE TABLE short_orders (sales_rep VARCHAR2(25), item order_item_typ);

UPDATE short_orders SET sales_rep = 'Unassigned';

SELECT o.item.line_item_id，o.item.quantity FROM short_orders o；

6.10. 占位符表达式

占位符表达式在 SQL 语句中提供一个位置，第三代语言绑定变量将为该位置提供一个值。您可以使用可选的指示变量指定占位符表达式。这种形式的表达式只能出现在嵌入式 SQL 语句或在 KingbaseES 调用接口 (OCI) 程序中处理的 SQL 语句中。

placeholder_expression :

一些有效的占位符表达式是：

employee_name INDICATOR

employee_name_indicator_var

department_location

6.11. 标量子查询表达式

标量子查询表达式是从一行中准确返回一个列值的子查询。标量子查询表达式的值是子查询的目标列表项的值。如果子查询返回 0 行，则标量子查询表达式的值为NULL。如果子查询返回多于一行，则KingbaseES 返回错误。

您可以在大多数需要表达式 ( expr)的语法中使用标量子查询表达式。在所有情况下，一个标量子查询必须包含在它自己的括号中，即使它的句法位置已经将它定位在括号内（例如，当标量子查询用作内置函数的参数时）。

标量子查询在以下位置不是有效表达式：

作为列的默认值

作为集群的哈希表达式

在RETURNINGDML 语句的子句中

用于函数的索引

在CHECK约束中

在GROUP BY子句

在与查询无关的语句中，例如CREATE PROFILE

6.12. 类型构造表达式

类型构造函数表达式指定对构造函数方法的调用,类型构造函数的参数是任何表达式,可以在调用函数的任何地方调用类型构造函数。

type_constructor_expression :

该NEW关键字适用于对象类型的构造函数，但不适用于集合类型。它指示KingbaseES通过调用适当的构造函数来构造一个新对象。关键字的使用NEW是可选的，但最好指定它。

如果type_name是对象类型，则表达式必须是有序列表，其中第一个参数是类型匹配对象类型的第一个属性的值，第二个参数是类型匹配对象类型的第二个属性的值,以此类推。构造函数的参数总数必须与对象类型的属性总数匹配。

如果type_name是可变数组或嵌套表类型，则表达式列表可以包含零个或多个参数。零参数意味着构造一个空集合。否则，每个参数对应一个元素值，其类型是集合类型的元素类型。

类型构造函数调用的限制

在调用类型构造方法时，指定的参数 (expr) 的数量不能超过 999，即使对象类型具有超过 999 个属性。此限制仅适用于从 SQL 调用构造函数时。对于来自 PL/SQL 的调用，PL/SQL 限制适用。

表达式示例

此示例使用cust_address_typ示例oe模式中的类型来显示在调用构造函数方法中使用表达式（PL/SQL 以斜体显示）：

CREATE TYPE address_book_t AS TABLE OF cust_address_typ;

DECLARE

myaddr cust_address_typ := cust_address_typ('500 KingbaseES Parkway', 94065, 'Redwood Shores', 'CA','USA');

alladdr address_book_t := address_book_t();

BEGIN

INSERT INTO customers VALUES (666999, 'Joe', 'Smith', myaddr, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

END;

子查询示例

此示例使用warehouse_typ示例模式中的类型oe来说明在调用构造函数方法时使用子查询。

CREATE TABLE warehouse_tab OF warehouse_typ;

INSERT INTO warehouse_tab VALUES (warehouse_typ(101, 'new_wh', 201));

CREATE TYPE facility_typ AS OBJECT ( facility_id NUMBER, warehouse_ref REF warehouse_typ);

CREATE TABLE buildings (b_id NUMBER, building facility_typ);

INSERT INTO buildings VALUES (10, facility_typ(102, (SELECT REF(w) FROM warehouse_tab w WHERE warehouse_name = 'new_wh')));

SELECT b.b_id, b.building.facility_id "FAC_ID", DEREF(b.building.warehouse_ref) "WH" FROM buildings b;

B_ID

FAC_ID WH(WAREHOUSE_ID, WAREHOUSE_NAME, LOCATION_ID)

10

102 WAREHOUSE_TYP(101, 'new_wh', 201)

B_ID	FAC_ID WH(WAREHOUSE_ID, WAREHOUSE_NAME, LOCATION_ID)
10	102 WAREHOUSE_TYP(101, 'new_wh', 201)

6.13. 表达式列表

表达式列表是其他表达式的组合。

表达式列表:

表达式列表可以出现在比较和成员条件以及GROUP BY查询和子查询的子句中。比较或成员条件中的表达式列表有时称为行值构造函数或行构造函数。

比较和隶属条件出现在WHERE条款的条件中。它们可以包含一个或多个逗号分隔的表达式或一组或多组表达式，其中每组包含一个或多个逗号分隔的表达式。在后一种情况下（多组表达式）：

每个集合都由括号包围

每个集合必须包含相同数量的表达式

每个集合中的表达式数量必须与比较条件中运算符之前或IN成员条件中关键字之前的表达式数量相匹配。

以逗号分隔的表达式列表最多可以包含 1000 个表达式。以逗号分隔的表达式集合列表可以包含任意数量的集合，但每个集合最多可以包含 1000 个表达式。

以下是条件中的一些有效表达式列表：

(10, 20, 40)

('SCOTT', 'BLAKE', 'TAYLOR') ( ('Guy', 'Himuro', 'GHIMURO'),('Karen', 'Colmenares', 'KCOLMENA') )

在第三个示例中，每个集合中的表达式数必须等于条件第一部分中的表达式数。例如：

SELECT * FROM employees WHERE WHERE ( first_name, last_name, email ) IN (('Guy', 'Himuro', 'GHIMURO'),('Karen', 'Colmenares', 'KCOLMENA'))

在一个简单的GROUP BY子句中，您可以使用表达式列表的大写形式或小写形式：

SELECT department_id, MIN(salary) min, MAX(salary) max FROM employees GROUP BY department_id, salary ORDER BY department_id, min, max;

SELECT department_id, MIN(salary) min, MAX(salary) max FROM employees GROUP BY (department_id, salary) ORDER BY department_id, min, max;

在ROLLUP、CUBE和GROUPING SETS子句的GROUP BY子句中，您可以将单个表达式与同一表达式列表中的表达式集组合在一起。以下示例在一条 SQL 语句中显示了几个有效的分组集表达式列表：

SELECT prod_category、prod_subcategory、country_id、cust_city、count(*) FROM products、sales、customers WHERE sales.prod_id = products.prod_id AND sales.cust_id=customers.cust_id AND sales.time_id = '01-oct-00' AND customers.cust_year_of_birth BETWEEN 1960 和 1970 GROUP BY GROUPING SETS ( ( prod_category, prod_subcategory, country_id, cust_city ), ( prod_category, prod_subcategory, country_id ), ( prod_category, prod_subcategory ), country_id ) ORDER BY prod_category、prod_subcategory、country_id、cust_city ；

沉舟侧畔千帆过_

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
金仓数据库 KingbaseES SQL 语言参考手册 (6. 表达式）

一个表达式可以包含一个或者多个值，操作符，以及返回标量值的SQL函数。表达式通常需要推测和假设其包含的成员的数据类型。这个简单的表达式计算结果为4并且具有数据类型int（与其组件相同的数据类型）2*2以下表达式是一个使用函数和运算符的更复杂表达式的示例。在当前日期增加7天，从总和中删除时间部分，并将结果转换为CHAR数据类型您可以在以下位置使用表达式SELECT语句的目标列表WHERE子句和HAVING子句中的条件中。...
复制链接

扫一扫