SAP HANA SQLScript Reference（参考文档）
This reference describes how to use the SQL extension SAP HANA SQLScript to embed data-intensive application logic into SAP HANA
SQLScript is a collection of extensions to the Structured Query Language (SQL). The extensions include:
- Data extension, which allows the definition of table types without corresponding tables
- Functional extension, which allows the definition of (side-effect free) functions which can be used to express and encapsulate complex data flows
- Procedural extension, which provides imperative constructs executed in the context of the database process.
上文是对改参考文档的简介，该文档描述了如何使用SAP HANA SQLScript(SQL 扩展)将数据密集型应用程序逻辑嵌入到SAP HANA 中。SQLScript 是SQL 扩展的集合，改扩展包括：
Data extension(数据扩展),允许在没有对应表的情况下定义表类型
Functional extension（函数扩展）,它允许定义可用于表达和封装复杂数据流的（无副作用）函数
Procedural extension（存储过程扩展）,它提供了在数据库进程的上下文中执行的命令式结构。
what is SQLScript? 什么是SQLScript
The motivation behind SQLScript is to embed data-intensive application logic into the database. Currently, applications only offload very limited functionality into the database using SQL, most of the application logic is normally executed on an application server. The effect of that is that data to be operated upon needs to be copied from the database onto the application server and vice versa. When executing data-intensive logic, this copying of data can be very expensive in terms of processor and data transfer time. Moreover, when using an imperative language like ABAP or JAVA for processing data, developers tend to write algorithms which follow a one-tuple-at-a-time semantics (for example, looping over rows in a table). However, these algorithms are hard to optimize and parallelize compared to declarative set-oriented languages like SQL.
The SAP HANA database is optimized for modern technology trends and takes advantage of modern hardware, for example, by having data residing in the main memory and allowing massive parallelization on multi-core CPUs. The goal of the SAP HANA database is to support application requirements by making use of such hardware. The SAP HANA database exposes a very sophisticated interface to the application, consisting of many languages. The expressiveness of these languages far exceeds that attainable with OpenSQL. The set of SQL extensions for the SAP HANA database, which allows developers to push data-intensive logic to the database, is called SQLScript. Conceptually SQLScript is related to stored procedures as defined in the SQL standard, but SQLScript is designed to provide superior optimization possibilities. SQLScript should be used in cases where other modeling constructs of SAP HANA, for example analytic views or attribute views are not sufficient. For more information on how to best exploit the different view types, see "Exploit Underlying Engine".
The set of SQL extensions are the key to avoiding massive data copies to the application server and to leveraging sophisticated parallel execution strategies of the database. SQLScript addresses the following problems:
- Decomposing an SQL query can only be performed by using views. However, when decomposing complex queries by using views, all intermediate results are visible and must be explicitly typed. Moreover, SQL views cannot be parameterized, which limits their reuse. In particular they can only be used like tables and embedded into other SQL statements.
- SQL queries do not have features to express business logic (for example a complex currency conversion). As a consequence, such business logic cannot be pushed down into the database (even if it is mainly based on standard aggregations like SUM(Sales), and so on).
- An SQL query can only return one result at a time. As a consequence, the computation of related result sets must be split into separate, usually unrelated, queries.
- As SQLScript encourages developers to implement algorithms using a set-oriented paradigm and not using a one-tuple-at-a-time paradigm, imperative logic is required, for example by iterative approximation algorithms. Thus, it is possible to mix imperative constructs known from stored procedures with declarative ones.
上文大致是说：
SQLScript 原本的动机是将数据密集型应用程序逻辑嵌入到数据库中。目前，应用程序仅使用 SQL 将非常有限的功能加载到数据库中，大部分应用程序逻辑通常在应用程序服务器上执行。其影响是需要将要操作的数据从数据库复制到应用程序服务器，反之亦然。在执行数据密集型逻辑时，这种数据复制在处理器和数据传输时间方面可能非常耗时耗费服务器性能。此外，当使用 ABAP 或 JAVA 等命令式语言处理数据时，开发人员倾向于编写遵循一次一个元组语义的算法（例如，循环表中的行）。然而，与 SQL 等声明式面向集合的语言相比，这些算法难以优化和并行化。
SAP HANA 数据库针对现代技术趋势进行了优化，并利用了现代硬件，例如，通过将数据驻留在主内存中并允许在多核 CPU 上进行大规模并行化。SAP HANA 数据库的目标是通过使用此类硬件来支持应用程序需求。 SAP HANA 数据库向应用程序公开了一个非常复杂的接口，由多种语言组成。 这些语言的表现力远远超过了 OpenSQL。 SAP HANA 数据库的一组 SQL 扩展称为 SQLScript，它允许开发人员将数据密集型逻辑推送到数据库。 从概念上讲，SQLScript 与 SQL 标准中定义的存储过程相关，但 SQLScript 旨在提供卓越的优化可能性。 在 SAP HANA 的其他建模构造（例如分析视图或属性视图）不足的情况下，应使用 SQLScript。 有关如何最好地利用不同视图类型的更多信息，请参阅“利用底层引擎”。
SQL 扩展集是避免向应用程序服务器复制大量数据和利用数据库的复杂并行执行策略的关键。 SQLScript 解决了以下问题：
只能使用视图来分解 SQL 查询。 但是，在使用视图分解复杂查询时，所有中间结果都是可见的并且必须显式键入。 此外，SQL 视图不能参数化，这限制了它们的重用。 特别是它们只能像表一样使用并嵌入到其他 SQL 语句中。
SQL 查询没有表达业务逻辑的功能（例如复杂的货币转换）。 因此，此类业务逻辑无法下推到数据库中（即使它主要基于 SUM(Sales) 等标准聚合）。
SQL 查询一次只能返回一个结果。 因此，相关结果集的计算必须拆分为单独的、通常不相关的查询。
由于 SQLScript 鼓励开发人员使用面向集合的范例而不是一次一个元组的范例来实现算法，因此需要命令式逻辑，例如迭代逼近算法。 因此，可以将存储过程中已知的命令式结构与声明式结构混合使用。
SQLScript Security Considerations（SQLScript的安全注意事项）
You can develop secure procedures using SQLScript in SAP HANA by observing the following recommendations.
- Mark each parameter using the keywords IN or OUT. Avoid using the INOUT keyword.
- Use the INVOKER keyword when you want the user to have the assigned privileges to start a procedure. The default keyword, DEFINER, allows only the owner of the procedure to start it.
- Mark read-only procedures using READS SQL DATA whenever it is possible. This ensures that the data and the structure of the database are not altered. TipAnother advantage to using READS SQL DATA is that it optimizes performance.
- Ensure that the types of parameters and variables are as specific as possible. Avoid using VARCHAR, for example. By reducing the length of variables you can reduce the risk of injection attacks.
- Perform validation on input parameters within the procedure.
上文大致是说：遵循以下建议，你可以在SAP HANA 中使用SQLScript 开发出安全的存储过程。
使用SQLSctipt, 可以读取和更新数据库中的信息。 在某些情况下（取决于于你选择的命令和参数），你的操作可能会造成数据泄漏或数据篡改的情况。为防止出现这种情况，SAP 建议在所有过程中采用以下做法。
- 使用关键字 IN 或 OUT 标记每个参数。避免使用INOUT关键字。
- 当您希望用户拥有启动过程的分配权限时，请使用 INVOKER 关键字。 默认关键字 DEFINER 只允许过程的所有者启动它
- 尽可能使用 READS SQL DATA 标记只读过程。 这确保了数据库的数据和结构不被改变。 Tip 提示使用 READS SQL DATA 的另一个优点是它优化了性能.
- 确保参数和变量的类型尽可能具体。 例如，避免使用 VARCHAR。 通过减少变量的长度，您可以降低注入攻击的风险.
- 在过程中对输入参数执行验证.
Dynamic SQL
In SQLScript you can create dynamic SQL using one of the following commands: EXEC and EXECUTE IMMEDIATE. Although these commands allow the use of variables in SQLScript where they might not be supported. In these situations you risk injection attacks unless you perform input validation within the procedure. In some cases injection attacks can occur by way of data from another database table.
- Use static SQL statements. For example, use the static statement, SELECT instead of EXECUTE IMMEDIATE and passing the values in the WHERE clause.
- Use server-side JavaScript to write this procedure instead of using SQLScript.
- Perform validation on input parameters within the procedure using either SQLScript or server-side JavaScript.
- Use APPLY_FILTER if you need a dynamic WHERE condition
- Use the SQL Injection Prevention Function
上文的大致意思是：在SQLScript 中你可以创建动态SQL，方法是使用以下命令EXEC and EXECUTE IMMEDIATE。尽管这些命令允许在 SQLScript 中使用可能不支持的变量。在这些情况下，除非您在过程中执行输入验证，否则您将面临注入攻击的风险。在某些情况下，注入攻击可能会通过来自另一个数据库表的数据发生。为避免注入攻击的潜在漏洞，请考虑使用以下方法代替动态 SQL：
- 使用静态 SQL 语句。 例如，使用静态语句 SELECT 而不是 EXECUTE IMMEDIATE 并在 WHERE 子句中传递值。
- 使用服务器端 JavaScript 编写此过程，而不是使用 SQLScript。
- 使用 SQLScript 或服务器端 JavaScript 对过程中的输入参数执行验证。
- 如果需要动态 WHERE 条件，请使用 APPLY_FILTER
- 使用SQL防注入功能。
Escape Code
You might need to use some SQL statements that are not supported in SQLScript, for example, the GRANT statement. In other cases you might want to use the Data Definition Language (DDL) in which some <name> elements, but not <value> elements, come from user input or another data source. The CREATE TABLE statement is an example of where this situation can occur. In these cases you can use dynamic SQL to create an escape from the procedure in the code.
- Use server-side JavaScript to write this procedure instead of using SQLScript.
- Perform validation on input parameters within the procedure using either SQLScript or server-side JavaScript.
TipFor more information about security in SAP HANA, see the SAP HANA Security Guide.
上文大致说：您可能需要使用 SQLScript 中不支持的一些 SQL 语句，例如 GRANT 语句。 在其他情况下，您可能希望使用数据定义语言 (DDL)，其中某些 <name> 元素而非 <value> 元素来自用户输入或其他数据源。 CREATE TABLE 语句是可能发生这种情况的示例。 在这些情况下，您可以使用动态 SQL 在代码中创建过程转义。
- 使用服务器端 JavaScript 编写此过程，而不是使用 SQLScript。
- 使用 SQLScript 或服务器端 JavaScript 对过程中的输入参数执行验证。
SQLScript Processing Overview
To better understand the features of SQLScript and their impact on execution, it can be helpful to understand how SQLScript is processed in the SAP HANA database.
When a user defines a new procedure, for example using the CREATE PROCEDURE statement, the SAP HANA database query compiler processes the statement in a similar way it processes an SQL statement. A step-by-step analysis of the process flow follows below:
- Parse the statement: detect and report simple syntactic errors.
- Check the statements semantic correctness: derive types for variables and check if their use is consistent.
- Optimize the code: optimization distinguishes between declarative logic, displayed in the upper branch, and imperative logic, displayed in the lower branch. We shall discuss how the SAP HANA database recognizes them below.
When the procedure starts, the invoke activity can be divided into two phases:
- Compilation
- Code generation - for declarative logic the calculation models are created to represent the data flow defined by the SQLScript code. It is optimized further by the calculation engine, when it is instantiated. For imperative logic the code blocks are translated into L-nodes.
- The calculation models generated in the previous step are combined into a stacked calculation model.
- Execution - the execution commences with binding actual parameters to the calculation models. When the calculation models are instantiated they can be optimized based on concrete input provided. Optimizations include predicate or projection embedding in the database. Finally, the instantiated calculation model is executed by using any of the available parts of the SAP HANA database.
With SQLScript you can implement applications by using both imperative orchestration logic and (functional) declarative logic, and this is also reflected in the way SQLScript processing works for those two coding styles. Imperative logic is executed sequentially and declarative logic is executed by exploiting the internal architecture of the SAP HANA database and utilizing its potential for parallelism.
上文大致是说：为了更好地理解 SQLScript 的特性及其对执行的影响，了解 SQLScript 在 SAP HANA 数据库中的处理方式会很有帮助。当用户定义新过程时，例如使用 CREATE PROCEDURE 语句，SAP HANA 数据库查询编译器以类似于处理 SQL 语句的方式处理该语句。 流程的逐步分析如下：
- 解析语句：检测并报告简单的语法错误。
- 检查语句语义正确性：派生变量类型并检查它们的使用是否一致。
- 优化代码：优化区分声明式逻辑，显示在上层分支，命令式逻辑，显示在下层分支。 我们将在下面讨论 SAP HANA 数据库如何识别它们。
当程序启动时，调用活动可以分为两个阶段：
编译
- 代码生成 - 对于声明性逻辑，创建计算模型以表示由 SQLScript 代码定义的数据流。 它在实例化时由计算引擎进一步优化。 对于命令式逻辑，代码块被翻译成 L 节点。
- 将上一步生成的计算模型组合成堆叠式计算模型。
执行
- 执行从将实际参数绑定到计算模型开始。 当计算模型被实例化时，它们可以根据提供的具体输入进行优化。 优化包括数据库中的谓词或投影嵌入。 最后，使用 SAP HANA 数据库的任何可用部分执行实例化计算模型。
使用 SQLScript，您可以使用命令式编排逻辑和（功能）声明性逻辑来实现应用程序，这也反映在 SQLScript 处理这两种编码风格的方式中。 命令式逻辑按顺序执行，声明式逻辑通过利用 SAP HANA 数据库的内部架构并利用其并行性潜力来执行。
Orchestration Logic
Orchestration logic is used to implement data-flow and control-flow logic using imperative language constructs such as loops and conditionals. The orchestration logic can also execute declarative logic, which is defined in the functional extension by calling the corresponding procedures. In order to achieve an efficient execution on both levels, the statements are transformed into a dataflow graph to the maximum extent possible. The compilation step extracts data-flow oriented snippets out of the orchestration logic and maps them to data-flow constructs. The calculation engine serves as execution engine of the resulting dataflow graph. Since the language L is used as intermediate language for translating SQLScript into a calculation model, the range of mappings may span the full spectrum – from a single internal L-node for a complete SQLScript script in its simplest form, up to a fully resolved data-flow graph without any imperative code left. Typically, the dataflow graph provides more opportunities for optimization and thus better performance.
To transform the application logic into a complex data-flow graph two prerequisites have to be fulfilled:
- All data flow operations have to be side-effect free, that is they must not change any global state either in the database or in the application logic.
- All control flows can be transformed into a static dataflow graph.
In SQLScript the optimizer will transform a sequence of assignments of SQL query result sets to table variables into parallelizable dataflow constructs. The imperative logic is usually represented as a single node in the dataflow graph, and thus it is executed sequentially.
上文大致是说：编排逻辑用于使用命令式语言结构（例如循环和条件）来实现数据流和控制流逻辑。 编排逻辑也可以执行声明式逻辑，通过调用相应的过程在功能扩展中定义。 为了实现两个层面的高效执行，将语句尽可能转化为数据流图。 编译步骤从编排逻辑中提取面向数据流的片段，并将它们映射到数据流结构。 计算引擎充当结果数据流图的执行引擎。 由于语言 L 用作将 SQLScript 转换为计算模型的中间语言，因此映射的范围可能跨越整个范围——从用于最简单形式的完整 SQLScript 脚本的单个内部 L 节点，到完全解析的数据 - 没有留下任何命令式代码的流程图。 通常，数据流图提供了更多的优化机会，从而提供了更好的性能。
要将应用程序逻辑转换为复杂的数据流图，必须满足两个先决条件：
- 所有数据流操作都必须无副作用，即它们不得更改数据库或应用程序逻辑中的任何全局状态。
- 所有控制流都可以转换为静态数据流图。
在 SQLScript 中，优化器会将一系列 SQL 查询结果集分配给表变量转换为可并行化的数据流结构。 命令式逻辑通常表示为数据流图中的单个节点，因此它是按顺序执行的。
Example of Orchestration Logic
CREATE PROCEDURE orchestrationProc LANGUAGE SQLSCRIPT READS SQL DATA AS BEGIN DECLARE v_id BIGINT; DECLARE v_name VARCHAR(30); DECLARE v_pmnt BIGINT; DECLARE v_msg VARCHAR(200); DECLARE CURSOR c_cursor1 (p_payment BIGINT) FOR SELECT id, name, payment FROM control_tab WHERE payment > :p_payment ORDER BY id ASC; CALL init_proc(); OPEN c_cursor1(250000); FETCH c_cursor1 INTO v_id, v_name, v_pmnt; v_msg = :v_name || ' (id ' || :v_id || ') earns ' || :v_pmnt || ' $.'; CALL ins_msg_proc(:v_msg); CLOSE c_cursor1; END
This procedure features a number of imperative constructs including the use of a cursor (with associated state) and local scalar variables with assignments.
此过程具有许多命令式结构，包括使用游标（具有关联状态）和具有赋值的局部标量变量。
Declarative Logic
Declarative logic is used for efficient execution of data-intensive computations. This logic is represented internally as data flows which can be executed in a parallel manner. As a consequence, operations in a data-flow graph have to be free of side effects. This means they must not change any global state neither in the database, nor in the application. The first condition is ensured by only allowing changes to the data set that is passed as input to the operator. The second condition is achieved by allowing only a limited subset of language features to express the logic of the operator. If those prerequisites are fulfilled, the following types of operators are available:
- SQL SELECT Statement
- Custom operators provided by SAP
Logically each operator represents a node in the data-flow graph. Custom operators have to be implemented manually by SAP.
上文大致说：
声明式逻辑用于有效执行数据密集型计算。 该逻辑在内部表示为可以并行方式执行的数据流。 因此，数据流图中的操作必须没有副作用。 这意味着它们不得更改数据库或应用程序中的任何全局状态。 第一个条件是通过只允许更改作为输入传递给操作员的数据集来确保的。 第二个条件是通过只允许有限的语言特征子集来表达运算符的逻辑来实现的。 如果满足这些先决条件，则可以使用以下类型的运算符：
- SQL 选择语句
- SAP 提供的自定义运算符
从逻辑上讲，每个运算符代表数据流图中的一个节点。 自定义运算符必须由 SAP 手动实施。
Backus Naur Form Notation
This document uses BNF (Backus Naur Form) which is the notation technique used to define programming languages. BNF describes the syntax of a grammar by using a set of production rules and by employing a set of symbols.
本文档使用 BNF（Backus Naur 形式），这是用于定义编程语言的符号技术。 BNF 通过使用一组产生式规则和一组符号来描述文法的句法。
Symbols Used in BNF
| Symbol | Description |
| < > | Angle brackets are used to surround the name of a syntax element (BNF non-terminal) of the SQL language. |
| ::= | The definition operator is used to provide definitions of the element appearing on the left side of the operator in a production rule. |
| [ ] | Square brackets are used to indicate optional elements in a formula. Optional elements may be specified or omitted. |
| { } | Braces group elements in a formula. Repetitive elements (zero or more elements) can be specified within brace symbols. |
| | | The alternative operator indicates that the portion of the formula following the bar is an alternative to the portion preceding the bar. |
| ... | The ellipsis indicates that the element may be repeated any number of times. If ellipsis appears after grouped elements, the grouped elements enclosed with braces are repeated. If ellipsis appears after a single element, only that element is repeated. |
| !! | Introduces normal English text. This is used when the definition of a syntactic element is not expressed in BNF. |
BNF Lowest Terms Representations
Throughout the BNF used in this document each syntax term is defined to one of the lowest term representations shown below.
在本文档中使用的整个 BNF 中，每个语法术语都定义为下面所示的最低术语表示之一。
<digit> ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 <letter> ::= a | b | c | d | e | f | g | h | i | j | k | l | m | n | o | p | q | r | s | t | u | v | w | x | y | z | A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z <any_character> ::= !!any character. <comma> ::= , <dollar_sign> ::= $ <double_quotes> ::= " <greater_than_sign> ::= > <hash_symbol> ::= # <left_bracket> ::= [ <left_curly_bracket> ::= { <lower_than_sign> ::= < <period> ::= . <pipe_sign> ::= | <right_bracket> ::= ] <right_curly_bracket> ::= } <sign> ::= + | - <single_quote> ::= ' <underscore> ::= _ <apostrophe> ::= <single_quote> <approximate_numeric_literal> ::= <mantissa>E<exponent> <cesu8_restricted_characters> ::= <double_quote> | <dollar_sign> | <single_quote> | <sign> | <period> | <greater_than_sign> | <lower_than_sign> | <pipe_sign> | <left_bracket> | <right_bracket> | <left_curly_bracket> | <right_curly_bracket> | ( | ) | ! | % | * | , | / | : | ; | = | ? | @ | \ | ^ | ` <exact_numeric_literal> ::= <unsigned_integer>[<period>[<unsigned_integer>]] | <period><unsigned_integer> <exponent> ::= <signed_integer> <hostname> ::= {<letter> | <digit>}[{ <letter> | <digit> | <period> | - }...] <identifier> ::= simple_identifier | special_identifier <mantissa> ::= <exact_numeric_literal> <numeric_literal> ::= <signed_numeric_literal> | <signed_integer> <password> ::= {<letter> | <underscore> | <hash_symbol> | <dollar_sign> | <digit>}... | <double_quotes> <any_character>...<double_quotes> <port_number> ::= <unsigned_integer> <schema_name> ::= <unicode_name> <simple_identifier> ::= {<letter> | <underscore>} [{<letter> | <digit> | <underscore> | <hash_symbol> | <dollar_sign>}...] <special_identifier> ::= <double_quotes><any_character>...<double_quotes> <signed_integer> ::= [<sign>] <unsigned_integer> <signed_numeric_literal> ::= [<sign>] <unsigned_numeric_literal> <string_literal> ::= <single_quote>[<any_character>...]<single_quote> <unicode_name> ::= !! CESU-8 string excluding any characters listed in <cesu8_restricted_characters> <unsigned_integer> ::= <digit>... <unsigned_numeric_literal> ::= <exact_numeric_literal> | <approximate_numeric_literal> <user_name> ::= <unicode_name>
Recommendation,
SAP recommends that you use SQL rather than Calculation Engine Plan Operators with SQLScript.
The execution of Calculation Engine Plan Operators currently is bound to processing within the calculation engine and does not allow a possibility to use alternative execution engines, such as L native execution. As most Calculation Engine Plan Operators are converted internally and treated as SQL operations, the conversion requires multiple layers of optimizations. This can be avoided by direct SQL use. Depending on your system configuration and the version you use, mixing Calculation Engine Plan Operators and SQL can lead to significant performance penalties when compared to to plain SQL implementation.
上文大致是说，SAP 官方更推荐使用SQL 数据库语言而不是使用CE 已经封装好的运算逻辑。CEPO 的执行 目前绑定在计算引擎内部的处理过程上，不允许使用替代引擎，像L 本机执行。由于大多数COEP在内部转换并被视为 SQL 操作,这种转换需要多层优化。这种优化的需求可以通过直接使用SQL 来避免（简单来说，用SQL 可以忽略优化的工作）。根据系统配置和版本的不同，与纯SQL 相比，组合使用CEPO 和SQL 会导致显著的性能损失。
| CE Operator | CE Syntax | SQL Equivalent |
|---|---|---|
| CE_COLUMN_TABLE | CE_COLUMN_TABLE(<table_name>[,<attributes>]) | SELECT [<attributes>] FROM <table_name> |
| CE_JOIN_VIEW | CE_JOIN_VIEW(<column_view_name>[,<attributes>])
out = CE_JOIN_VIEW("PRODUCT_SALES", ["PRODUCT_KEY", "PRODUCT_TEXT", "SALES"]); |
SELECT [<attributes>] FROM <column_view_name>
out = SELECT product_key, product_text, sales FROM product_sales; |
| CE_OLAP_VIEW | CE_OLAP_VIEW (<olap_view_name>[,<attributes>])
out = CE_OLAP_VIEW("OLAP_view", ["DIM1", SUM("KF")]); |
SELECT [<attributes>] FROM <olap_view_name>
out = select dim1, SUM(kf) FROM OLAP_view GROUP BY dim1; |
| CE_CALC_VIEW | CE_CALC_VIEW(<calc_view_name>,[<attributes>])
out = CE_CALC_VIEW("TESTCECTABLE", ["CID", "CNAME"]); |
SELECT [<attributes>] FROM <calc_view_name>
out = SELECT cid, cname FROM "TESTCECTABLE"; |
| CE_JOIN | CE_JOIN(<left_table>,<right_table>,<join_attributes>[<projection_list>])
ot_pubs_books1 = CE_JOIN (:lt_pubs, :it_books,["PUBLISHER"]); |
SELECT [<projection_list>] FROM <left_table>,<right_table> WHERE <join_attributes>
ot_pubs_books1 = SELECT P.publisher AS publisher, name, street,post_code, city, country, isbn, title, edition, year, price, crcy FROM :lt_pubs AS P, :it_books AS B WHERE P.publisher = B.publisher; |
| CE_LEFT_OUTER_JOIN | CE_LEFT_OUTER_JOIN(<left_table>,<right_table>,<join_attributes>[<projection_list>]) | SELECT [<projection_list>] FROM <left_table> LEFT OUTER JOIN <right_table> ON <join_attributes> |
| CE_RIGHT_OUTER_JOIN | CE_RIGHT_OUTER_JOIN(<left_table>,<right_table>,<join_attributes>[<projection_list>]) | SELECT [<projection_list>] FROM <left_table> RIGHT OUTER JOIN <right_table> ON <join_attributes> |
| CE_PROJECTION | CE_PROJECTION(<table_variable>,<projection_list>[,<filter>])
ot_books1 = CE_PROJECTION (:it_books,["TITLE","PRICE", "CRCY" AS "CURRENCY"], '"PRICE" > 50'); |
SELECT <projection_list> FROM <table_variable> where [<filter>]
ot_book2= SELECT title, price, crcy AS currency FROM :it_b ooks WHERE price > 50; |
| CE_UNION_ALL | CE_UNION_ALL(<table_variable1>,<table_variable2>)
ot_all_books1 = CE_UNION_ALL (:lt_books, :it_audiobooks); |
SELECT * FROM <table_variable1> UNION ALL SELECT * FROM <table_variable2>
ot_all_books2 = SELECT * FROM :lt_books UNION ALL SELECT * FROM :it_audiobooks; |
| CE_CONVERSION | CE_CONVERSION(<table_variable>,<conversion_params>,[<rename_clause>]) | SQL-Function CONVERT_CURRENCY |
| CE_AGGREGATION | CE_AGGREGATION(<table_variable>,<aggregate_list> [,<group_columns>])
ot_books1 = CE_AGGREGATION (:it_books, [COUNT ("PUBLISHER") AS "CNT"], ["YEAR"]); |
SELECT <aggregate_list> FROM <table_variable> [GROUP BY <group_columns>]
ot_books2 = SELECT COUNT (publisher) AS cnt, year FROM :it_books GROUP BY year; |
| CE_CALC | CE_CALC(‘<expr>’, <result_type>)
TEMP = CE_PROJECTION(:table_var, ["ID" AS "KEY", CE_CALC('rownum()', INTEGER) AS "T_ID"] ); |
SQL Function
TEMP = SELECT "ID" AS "KEY", ROW_NUMBER() OVER () AS "T_ID" FROM :table_var |
Calculation engine plan operators encapsulate data-transformation functions and can be used in the definition of a procedure or a table user-defined function. They constitute a no longer recommended alternative to using SQL statements. Their logic is directly implemented in the calculation engine, which is the execution environments of SQLScript.
CEPO 封装了数据转换函数，CEPO 可以用于定义存储过程和用户自定义表函数。他们不再是使用SQL语句的推荐替代方法。他们的逻辑直接在计算引擎中实现，也就是SQLScript 的执行环境。
There are different categories of operators.
以下是不同类型的运算：
- Data Source Access operators that bind a column table or a column view to a table variable.将列表或列视图绑定到表变量的数据源访问运算符
- Relational operators that allow a user to bypass the SQL processor during evaluation and to directly interact with the calculation engine.允许用户在评估期间绕过 SQL 处理器并直接与计算引擎交互的关系运算符
- Special extensions that implement functions.实现功能的特殊扩展
