HANA SQLScript

CREATE PROCEDURE [<schema_name>.]<proc_name> [(<parameter_clause>)] [LANGUAGE <lang>] [SQL SECURITY <mode>]
[DEFAULT SCHEMA <default_schema_name>][READS SQL DATA [WITH RESULT VIEW <view_name>]] AS

BEGIN [SEQUENTIAL EXECUTION]

<procedure_body>

END

The CREATE PROCEDURE statement creates a procedure using the specified programming language <lang>.

3.1.1 <parameter_clause>

<parameter_clause> ::= <parameter> [{,<parameter>}...]

<param_inout> ::= IN|OUT|INOUT

<param_type> ::= <sql_type> | <table_type> | <table_type_definition>

<table_type_defintion> ::= TABLE (<column_list_definition>)

<column_list_definition> ::= <column_elem>[{, <column_elem>}...]

<column_elem> ::= <column_name> <data_type>

输入或输出参数类型可以是标准的基础数据类型与表类型，但INOUT参数只能是标准数据类型

<param_inout>：默认为IN

<table_type_defintion>：参数类型如果是表类型，这个表类型可以是预先使用Create Type…As Table语句创建的类型对象，也可以在指定存储过程参数类型时才定义表类型

3.1.2 LANGUAGE <lang>

<lang> ::= SQLSCRIPT | R

默认为: SQLSCRIPT。指定存储过程实现的程序语言

3.1.3 SQL SECURITY <mode>

<mode> ::= DEFINER | INVOKER

默认: DEFINER，指定存储过程的安全模式

DEFINER：Specifies that the execution of the procedure is performed with the privileges of the definer of the procedure. 只有存储过程的定义者才能执行？

INVOKER：Specifies that the execution of the procedure is performed with the privileges of the invoker of the procedure.

3.1.4 READS SQL DATA

存储过程为只读的，不能包含DDL与DML（INSERT、UPDATE、DELETE）语句（即只能使用查询SQL与DCL语句），如果调用其他存储过程，则被调用过程也是只读的。设置参数会有特定的优化

3.1.5 WITH RESULT VIEW <view_name>

Specifies the result view to be used as the output of a read-only procedure.将只读取存储过程的输出看做结果视图

When a result view is defined for a procedure, it can be called by an SQL statement in the same way as a tableor view. See Example 2 - Using a result view below.定义了结果视图的存储过程，可以被其他查询SQL用来查询，此时存储过程就像一个表或视图

3.1.6 SEQUENTIAL EXECUTION

This statement will force sequential execution of the procedure logic. No parallelism takes place.不允许存储过程并行执行

3.1.7 <procedure_body>

<procedure_body> ::= [<proc_decl_list>]                            定义块
[<proc_handler_list>]                        异常处理块
<proc_stmt_list>                                 存储体语句块

Defines the main body of the procedure according to the programming language selected.过程的主体由设定的程序语言来定义

3.1.7.1 <proc_decl_list> 定义变量

<proc_decl_list> ::= <proc_decl> [{<proc_decl>}…]

<proc_decl> ::= DECLARE {<proc_variable>|<proc_table_variable>|<proc_cursor>|<proc_condition>} ;

<proc_variable>::= <variable_name_list> [CONSTANT] {<sql_type>|<array_datatype>}[NOT NULL][<proc_default>]

<variable_name_list> ::= <variable_name>[{, <variable_name}...]

<array_datatype> ::= <sql_type> ARRAY [ = <array_constructor> ]

<array_constructor> ::= ARRAY (<expression> [ { , <expression> }...] )

<proc_default> ::= (DEFAULT | '=' ) <value>|<expression>

<proc_table_variable> ::= <variable_name_list> {<table_type_definition>|<table_type>}

<variable_name_list> ::= <variable_name>[{, <variable_name}...]

<table_type_definition> ::= TABLE(<column_list_elements>)

<column_list_elements> ::= (<column_definition>[{,<column_definition>}...])

<proc_cursor> ::= CURSOR <cursor_name> [ ( proc_cursor_param_list ) ] FOR <subquery> ;

<proc_cursor_param_list> ::= <proc_cursor_param> [{,<proc_cursor_param>}...]

<proc_cursor_param> ::= <param_name> <datatype>

<proc_condition> ::= <variable_name> CONDITION | <variable_name> CONDITION FOR <sql_error_code>

CREATEprocedure proc() LANGUAGE SQLSCRIPT AS

BEGIN

declare a intdefault 2;--基本类型变量使用前一定要定义

-- 如果某个变量是表类型的变量，可以不用声明，直接就可以使用，这与基本类型变量是不一样的

-- ——基本类型变量使用前需要定义

--declare tab_var1 table(a int,b int); 表类型变量可不定义就使用

a := 1;

-- a = 1; 基本类型变量赋值时，等号前一定要加冒号，这与表类型变量恰好相反

tab_var1 = select 1 as a,2 as b from dummy;

--tab_var1 := select 1 as a,2 as b from dummy; 这是错误，表类型变量赋值时，只能使用等号，不能在等号前加冒号，这与基本类型变量赋值相反

END;

3.1.7.2 <proc_handler_list> 异常处理

<proc_handler_list> ::= <proc_handler> [{, <proc_handler>}...]

<proc_handler>::= DECLARE EXIT HANDLER FOR <proc_condition_value_list> <proc_stmt> ;

<proc_condition_value_list> ::= <proc_condition_value>{,<proc_condition_value>}...]

<proc_condition_value> ::= SQLEXCEPTION | SQLWARNING | <sql_error_code> | <condition_name>

3.1.7.3 <proc_stmt_list> 过程体

<proc_stmt_list> ::= {<proc_stmt>}...

3.1.7.3.1 <proc_block> 内嵌块

<proc_block> ::= BEGIN <proc_block_option>[<proc_decl_list>][<proc_handler_list>] <proc_stmt_list> END ;

<proc_block_option> ::= [SEQUENTIAL EXECUTION] [AUTONOMOUS TRANSACTION]| [AUTONOMOUS TRANSACTION] [SEQUENTIAL
EXECUTION]

内嵌块， BEGIN 、 END是可以内嵌的

3.1.7.3.2 <proc_assign> 给变量赋值

<proc_assign> ::= <variable_name> = { <expression> | <array_function> } ;| <variable_name> '[' <expression> ']' = <expression> ;

<array_function> = ARRAY_AGG ( <table_variable>.<column_name> [ ORDER BY <sort_spec_list> ] ) | CARDINALITY ( <array_variable_name>) | TRIM_ARRAY
( <array_variable_name> , <array_variable_name>) | ARRAY ( <array_variable_name_list> )

<array_variable_name_list> ::=< array_variable_name > [{, < array_variable_name >}...] 这个是自己加的，是否正确？

Assign values to variables. An <expression> can be either a simple expression, such as a character, a date, or a number, or it can be a scalar function or a scalar user-defined function.将值赋给变量。<expression>可以是一个简单的表达式（如字符、日期、数字表达式），或者是标准函数，或用户自定义函数

有关 ARRAY 、ARRAY_AGG、CARDINALITY、TRIM_ARRAY函数请参考后面数组函数

3.1.7.3.3 <proc_single_assign> 单个赋值

<proc_single_assign> ::= <variable_name> = <subquery> | <variable_name> = <proc_ce_call> | <variable_name> = <proc_apply_filter>| <variable_name> =
<unnest_function>

<proc_ce_call> ::= TRACE ( <variable_name> ) ;| CE_LEFT_OUTER_JOIN ( <table_variable> , <table_variable> ,'[' <expr_alias_comma_list> ']' <expr_alias_vector>] ) ;
| CE_RIGHT_OUTER_JOIN ( <table_variable> , <table_variable> ,'[' <expr_alias_comma_list> ']' [ <expr_alias_vector>] ) ; | CE_FULL_OUTER_JOIN ( <table_variable> , <table_variable> ,'[' <expr_alias_comma_list> ']' [ <expr_alias_vector>] ); | CE_JOIN ( <table_variable> , <table_variable> , '['<expr_alias_comma_list> ']' [<expr_alias_vector>] ) ;| CE_UNION_ALL ( <table_variable> , <table_variable> ) ;| CE_COLUMN_TABLE ( <table_name> [ <expr_alias_vector>] ) ; | CE_JOIN_VIEW ( <table_name> [ <expr_alias_vector>] ) ;| CE_CALC_VIEW ( <table_name> [ <expr_alias_vector>] ) ; | CE_OLAP_VIEW ( <table_name> [ <expr_alias_vector>] ) ; | CE_PROJECTION ( <table_variable> , '['<expr_alias_comma_list> ']' <opt_str_const> ) ; | CE_PROJECTION ( <table_variable> <opt_str_const> ) ; | CE_AGGREGATION ( <table_variable> , '['<agg_alias_comma_list> ']' [ <expr_alias_vector>] ); | CE_CONVERSION ( <table_variable> , '['<proc_key_value_pair_comma_list> ']' [ <expr_alias_vector>] ) ;| CE_VERTICAL_UNION ( <table_variable> , '['<expr_alias_comma_list> ']' <vertical_union_param_pair_list> ) ;| CE_COMM2R ( <table_variable> , <int_const> , <str_const> ,<int_const> , <int_const> , <str_const> ) ;

<table_name> ::= [<schema_name>.]<identifier>

<proc_apply_filter> ::= APPLY_FILTER ( {<table_name> | <table_variable>},<variable_name> ) ;

<unnest_function> ::= UNNEST ( <variable_name_list> ) [ WITH ORDINALITY ][<as_col_names>] ;

<variable_name_list> ::= <variable_name> [{, <variable_name>}...]

<as_col_names> ::= AS [table_name] ( <column_name_list> )

<column_name_list> ::= <column_name>[{, <column_name>}...]

WITH ORDINALTIY：Appends an ordinal column to the return values.

3.1.7.3.4 <proc_multi_assign> 多个赋值

<proc_multi_assign> ::= (<var_name_list>) = <function_expression>

Assign values to a list of variables with only one function evaluation. For example, <function_expression>must be a scalar user defined function and the number of elements in <var_name_list> must be equal to the number of output parameters of the scalar UDF（用户定义函数）.

<function_expression>：用户自定义函数

函数的输入参数个数需与<var_name_list>中的参数个数相同

3.1.7.3.5 <proc_if>

<proc_if> ::= IF <condition> THEN [SEQUENTIAL EXECUTION][<proc_decl_list>] [<proc_handler_list>] <proc_stmt_list> [<proc_elsif_list>] [<proc_else>] END IF ;

<proc_elsif_list> ::= ELSEIF <condition> THEN [SEQUENTIAL EXECUTION][<proc_decl_list>] [<proc_handler_list>] <proc_stmt_list>

<proc_else> ::= ELSE [SEQUENTIAL EXECUTION][<proc_decl_list>][<proc_handler_list>] <proc_stmt_list>

You use IF - THEN - ELSE IF to control execution flow with conditionals.

3.1.7.3.6 <proc_loop>

<proc_loop> ::= LOOP [SEQUENTIAL EXECUTION][<proc_decl_list>] [<proc_handler_list>] <proc_stmt_list> END LOOP ;

You use loop to repeatedly execute a set of statements.

3.1.7.3.7 <proc_while>

<proc_while> ::= WHILE <condition> DO [SEQUENTIAL EXECUTION][<proc_decl_list>] [<proc_handler_list>] <proc_stmt_list> END WHILE ;

You use while to repeatedly call a set of trigger statements while a condition is true.

3.1.7.3.8 <proc_for>

<proc_for> ::= FOR <column_name> IN [ REVERSE ] <expression> [...] <expression> DO [SEQUENTIAL EXECUTION]

[<proc_decl_list>]

[<proc_handler_list>]

<proc_stmt_list>

END FOR ;

You use FOR - IN loops to iterate over a set of data.

3.1.7.3.9 <proc_foreach>

<proc_foreach> ::= FOR <column_name> AS <column_name> [<open_param_list>] DO [SEQUENTIAL EXECUTION]

[<proc_decl_list>]

[<proc_handler_list>]

<proc_stmt_list>

END FOR ;

<open_param_list> ::= ( <expression> [ { , <expression> }...] )

You use FOR - EACH loops to iterate over all elements in a set of data.

3.1.7.3.10 <proc_exit>

<proc_exit> ::= BREAK ;

Terminates a loop.结束循环

3.1.7.3.11 <proc_continue>

<proc_continue> ::= CONTINUE ;

Skips a current loop iteration and continues with the next value.结束当前循环继续下一次循环

3.1.7.3.12 <proc_signal>

<proc_signal> ::= SIGNAL <signal_value> [<set_signal_info>] ;

You use the SIGNAL statement to explicitly raise an exception from within your trigger procedures.

3.1.7.3.13 <proc_resignal>

<proc_resignal> ::= RESIGNAL [<signal_value>] [<set_signal_info>] ;

You use the RESIGNAL statement to raise an exception on the action statement in an exception handler. If an error code is not specified, RESIGNAL will throw the caught exception.重新抛出异常？

<signal_value> ::= <signal_name> | <sql_error_code>

<signal_name> ::= <identifier>

<sql_error_code> ::= <unsigned_integer>

You can SIGNAL or RESIGNAL a signal name or an SQL error code.

<set_signal_info> ::= SET MESSAGE_TEXT = '<message_string>'

<message_string> ::= <any_character>

You use SET MESSAGE_TEXT to deliver an error message to users when specified error is thrown during procedure execution.

3.1.7.3.14 <proc_sql>

<insert_stmt>, <delete_stmt>,<update_stmt>,<replace_stmt> and <upsert_stmt>请参考HANA SQL中INSERT , DELETE, UPDATE, REPLACE and UPSERT SQL语句

<select_into_stmt> ::= SELECT <select_list> INTO <var_name_list> <from_clause > [<where_clause>] [<group_by_clause>] [<having_clause>] [{<set_operator>
<subquery>, ... }] [<order_by_clause>] [<limit>] ;

<var_name_list> ::= <var_name>[{, <var_name>}...] <var_name>为基础类型变量

3.1.7.3.15 <proc_open>、<proc_fetch>、<proc_close>

<proc_open> ::= OPEN <cursor_name> [ <open_param_list>] ;

<proc_fetch> ::= FETCH <cursor_name> INTO <column_name_list> ;

<proc_close> ::= CLOSE <cursor_name> ;

Cursor operations

3.1.7.3.16 <proc_call>

<proc_call> ::= CALL <proc_name> (<param_list>) ;

Calling a procedure

3.1.7.3.17 <proc_exec>

<proc_exec> ::= {EXEC | EXECUTE IMMEDIATE} <proc_expr> ;

You use EXEC to make dynamic SQL calls.

3.1.7.3.18 <proc_return>

<proc_return> ::= RETURN [<proc_expr>] ;

Return a value from a procedure.

3.1.8 Examples

Example 1 - Creating an SQL Procedure

You create an SQLScript procedure with the following definition.

CREATECOLUMNTABLE"SYSTEM"."T" ("ID"INTEGER CS_INT,

"NAME"VARCHAR(30),

"PAYMENT"INTEGER CS_INT) UNLOADPRIORITY 5 AUTO MERGE;

insertinto"SYSTEM"."T"values(1,'a',10);

insertinto"SYSTEM"."T"values(2,'b',20);

insertinto"SYSTEM"."T"values(3,'c',30);

CREATEPROCEDURE orchestrationProc LANGUAGE SQLSCRIPT AS

BEGIN

DECLARE v_id INT;

DECLARE v_name VARCHAR(30);

DECLARE v_pmnt INT;

DECLARE v_msg VARCHAR(200);

DECLARECURSOR c_cursor1 (p_payment INT) FORSELECT id, name, PAYMENT FROM t

WHERE payment >= p_payment ORDERBY id ASC;

OPEN c_cursor1(20);

FETCH c_cursor1 INTO v_id, v_name, v_pmnt;

v_msg := v_name || ' (id ' || v_id || ') earns ' || v_pmnt || ' $.';

select v_msg from dummy;

CLOSE c_cursor1;

end;

Example 2 - Using a result view

创建带返回视图结果的存储过程：

CREATEPROCEDURE ProcWithResultView(IN id INT, OUT o1 t)

LANGUAGE SQLSCRIPT

READS SQL DATA WITH RESULT VIEW ProcView AS

BEGIN

o1 = SELECT * FROM t WHERE id = :id;

END;

调用存储视图：

select * from ProcView with parameters ('placeholder' = ('$$id$$' ,'2' ));

CREATEPROCEDURE Proc_test(IN id INT,IN name varchar(3),OUT o1 "SYSTEM"."T_COLUMN")

LANGUAGE SQLSCRIPT

READS SQL DATA WITH RESULT VIEW Proc_View AS

BEGIN

outt = CE_COLUMN_TABLE("SYSTEM"."T_COLUMN",[id,name,payment]);

--注：过滤条件一定要使用单引号整体引起来；列名一定要使用双引号引起来；

--如果是字符串，则要使用单引号引起来，因为外层有单引号，所以里面要使用两个单引号代表一个单引号

--o1 = CE_PROJECTION(:outt,[id,name,payment],'"ID" = :id AND "NAME" = '':name''');

--上面这句等效于下面两句

o1 = CE_PROJECTION(:outt,[id,name,payment],'"ID" = :id');

o1 = CE_PROJECTION(:o1,[id,name,payment],'"NAME" = '':name''');

END;

select * from Proc_View with parameters ('placeholder' = ('$$id$$' ,'2' ),'placeholder' = ('$$name$$' ,'b' ));

Note

Procedures and result views produced by procedures are not connected from the security perspective and therefore do not inherit privileges from each other. The security aspects of each object must be handled separately. For example, you must grant the SELECT privilege on a result view and EXECUTE privilege on a connected procedure.

3.2DROP PROCEDURE

DROPPROCEDURE [<schema_name>.]<proc_name> [<drop_option>]

<drop_option> ::= CASCADE | RESTRICT

如果未指定删除选项，则默认为非级联删除，这将只会删除存储过程本身，这会导致相关联的其他对象无效，但不会被删除。如果存储过程重建则相关联失效的对应将重新生效

CASCADE：存储过程与相关联对象一起被删除

RESTRICT：如果有相关联的对象没有被删除，则删除时被报错，不让被删除。只有当没有关联的对象时，才可以删除

使用非级联删除：DROPPROCEDURE my_proc;

3.3ALTER PROCEDURE RECOMPILE

ALTERPROCEDURE [<schema_name>.]<proc_name> RECOMPILE [WITHPLAN]

WITH PLAN：

Specifies that internal debug information should be created during execution of the procedure.

Description

The ALTER PROCEDURE RECOMPILE statement manually triggers a recompilation of a procedure by generating an updated execution plan. For production code a procedure should be compiled without the WITH PLAN option to avoid overhead during compilation and execution of the procedure.

Example

You trigger the recompilation of the my_proc procedure to produce debugging information.

ALTERPROCEDURE my_proc RECOMPILEWITHPLAN;

3.4Procedure Calls

CALL <proc_name> (<param_list>) [WITH OVERVIEW] [INDEBUGMODE]

<param_list> ::= <proc_param>[{, <proc_param>}...]

Parameters passed to a procedure are scalar constants and can be passed either as IN, OUT or INOUT parameters. Scalar parameters are assumed to be NOT NULL. Arguments for IN parameters of table type can either be physical tables or views. The actual value passed for tabular OUT parameters must be`?`.

IN DEBUG MODE

When specified additional debug information will be created during the execution of the procedure. This information can be used to debug the instantiation of the internal execution plan of the procedure.

WITH OVERVIEW

Defines that the result of a procedure call will be stored directly into a physical table.

Calling a procedure WITH OVERVIEW will return one result set that holds the information of which table contains the result of a particular table's output variable. Scalar outputs will be represented as temporary tables with only one cell. When you pass existing tables to the output parameters WITH OVERVIEW will insert the result set tuples of the procedure into the provided tables. When you pass '?' to the output parameters, temporary tables holding the result sets will be generated. These tables will be dropped automatically once the database session is closed.

CALL conceptually returns list of result sets with one entry for every tabular result. An iterator can be used to iterate over these results sets. For each result set you can iterate over the result table in the same way as for query results. SQL statements that are not assigned to any table variable in the procedure body will be added as result sets at the end of the list of result sets. The type of the result structures will be determined during compilation time but will not be visible in the signature of the procedure.

CALL when executed by the client the syntax behaves in a way consistent with the SQL standard semantics, for example, Java clients can call a procedure using a JDBC CallableStatement. Scalar output variables will be a scalar value that can be retrieved from the callable statement directly.

Note：

Unquoted identifiers are implicitly treated as upper case. Quoting identifiers will respect capitalization and allow for using white spaces which are normally not allowed in SQL identifiers.

Examples：

For the examples, consider the following procedure signature:

CREATEPROCEDURE proc(INvalueinteger,IN currency nvarchar(10),OUT outTable typeTable,OUT valid integer) AS

BEGIN

…

END;

Calling the proc procedure:

CALL proc (1000, 'EUR', ?, ?);

Calling the proc procedure in debug mode:

CALL proc (1000, 'EUR', ?, ?) INDEBUGMODE;

Calling the proc procedure using the WITH OVERVIEW option:

CALL proc(1000, 'EUR', ?, ?) WITH OVERVIEW;

It is also possible to use scalar user defined function as parameters for procedure call:

CALL proc( udf(),’EUR’,?,?);

CALL proc( udf() * udf() - 55,’EUR’, ?, ?);

In this example, udf() is a scalar user-defined function. For more information about scalar user-defined functions, see CREATE FUNCTION

WITH OVERVIEW实例：

CREATETABLE COL_COSTS

( CHANNEL_ID INTEGER

, PROD_ID INTEGER

, UNIT_COST DOUBLE

, UNIT_PRICE DOUBLE

);

CREATEPROCEDURE PR_SAMPLE_OVERVIEW ( OUT P_COL_COSTS COL_COSTS )--输出参数为物理表

LANGUAGE SQLSCRIPT AS

BEGIN

P_COL_COSTS =

SELECT 123 as CHANNEL_ID,

555 as PROD_ID,

7.5 as UNIT_COST,

9.99 as UNIT_PRICE

FROM DUMMY;

END;

CALL PR_SAMPLE_OVERVIEW( null );-- 如果不存储结果时，需传入NULL或者?

SELECT * FROM col_costs; -- COL_COSTS表中没有数据

CALL PR_SAMPLE_OVERVIEW( ? );-- 如果不存储结果时，需传入NULL或者?

SELECT * FROM col_costs; --COL_COSTS表中没有数据

CALL PR_SAMPLE_OVERVIEW( COL_COSTS ) WITH OVERVIEW; --将结插入到COL_COSTS物理表中了

SELECT * FROM col_costs; --COL_COSTS表中有数据

CALL PR_SAMPLE_OVERVIEW( ? ) with overview;-- 如果加上了 with overview选项，但没有传入物理表，则会将结果存储到临时表中

3.4.1 Internal Procedure Call

在存储过程内部调用

CALL <proc_name > (<param_list>)

<param_list> ::= <param>[{, <param>}...]

<in_table_param> ::= <read_variable_identifier>|<sql_identifier>

<in_scalar_param>::=<read_variable_identifier>|<scalar_value>|<expression>

<in_param> ::=:<identifier> 请注意在输入参数明有个冒号

<out_param> ::= <identifier>

Description:

For an internal procedure, where one procedure calls another procedure, all existing variables of the caller or literals are passed to the IN parameters of the callee and new variables of the caller are bound to the OUT parameters of the callee. That is to say, the result is implicitly bound to the variable that is given in the function call.

Example:

CALL addDiscount (:lt_expensive_books, lt_on_sale);

When procedure addDiscount is called, the variable <:lt_expensive_books> is assigned to the function and the variable <lt_on_sales> is bound by this function call.

CREATEPROCEDURE addDiscount(in old_pric int , out dic_pric int) as

begin

dic_pric := old_pric * 2;

--dic_pric := :old_pric * 2; 这样好像也可以

end;

createprocedure callerProc() as

begin

declare pric int;

pric := 10;

call addDiscount(pric,pric);

--call addDiscount(:pric,:pric); 这样好像也可以

select pric from dummy;

end;

call callerProc();--20

3.4.2 Call with Named Parameters

参数传递时，可以不按声明的顺序

You can call a procedure passing named parameters by using the token =>.

For example:

CALL myproc (i => 2)

When you use named parameters you can ignore the order of the parameters in the procedure signature. Run the following commands and you can try some examples below.

createtype mytab_t astable (i int);

createtable mytab (i int);

insertinto mytab values (0);

insertinto mytab values (1);

insertinto mytab values (2);

insertinto mytab values (3);

insertinto mytab values (4);

insertinto mytab values (5);

createprocedure myproc (in intab mytab_t,in i int, out outtab mytab_t) as

begin

outtab = select i from :intab where i > :i;

end;

Now you can use the following CALL possibilities:

createtype mytab_t astable (i int);

createtable mytab (i int);

insertinto mytab values (0);

insertinto mytab values (1);

insertinto mytab values (2);

insertinto mytab values (3);

insertinto mytab values (4);

insertinto mytab values (5);

createprocedure myproc (in intab mytab_t,in i int, out outtab mytab_t) as

--create procedure myproc (in intab mytab,in i int, out outtab mytab) as 类型可以直接是物理表类型

begin

outtab = select i from :intab where i > :i;

end;

call myproc(intab=>mytab, i=>2, outtab =>?);-- mytab 实参为物理表。问号表示不传此参数，但即使不传实参，型参在过程里一样使用，只是结果不能传出来而已

--call myproc(intab=>mytab, i=>2, outtab =>null); 也可以使用NULL，但运行会警告，并且不推荐使用，以后新版本会使用问号逐渐代替NULL

call myproc( i=>2, intab=>mytab, outtab =>?)

Both call formats will produce the same result.

3.5Procedure Parameters

Parameter Modes

The following table lists the parameters you can use when defining your procedures.

INOUT类型的参数仅支持基本类型的参数，不支持表类型？

Supported Parameter Types

Both scalar and table parameter types are supported. For more information on datatypes, see Datatype Extension

3.5.1 Value Binding during Call

Scalar Parameters基本类型

Consider the following procedure:

CREATEPROCEDURE test_scalar (IN i INT, IN a VARCHAR)

BEGIN

SELECT i AS"I", a AS"A"FROM DUMMY;

END;

You can pass parameters using scalar value binding:

CALL test_scalar (1, 'ABC');

You can also use expression binding.

CALL test_scalar (1+1, upper('abc'))

Table parameters表类型

Consider the following procedure:

CREATETYPE tab_type ASTABLE (I INT, A VARCHAR);

CREATETABLE tab1 (I INT, A VARCHAR);

CREATEPROCEDURE test_table (IN tab tab_type)

--CREATE PROCEDURE test_table (IN tab tab1) 参数类型也可以直接是物理表

BEGIN

SELECT * FROM :tab;

END;

You can pass tables and views to the parameter of this function.

CALL test_table (tab1);

You should always use sql special identifiers when binding a value to a table variable.在将表做为参数传递时，一般你应该使用表名，所以使用又引号引导起来再传递

CALL test_table ("TAB1");-- 也可以直接写上表名或视图名，但要注意的是：使用引号引起来后，要全部大写

--CALL test_table ("tab1") 由于是小写，所以找不到所对应的表或视图

3.5.2 Default values for procedure parameters

In the procedure signature you can define default values for input parameters by using the DEFAULT keyword.

Consider the following procedure:

CREATEPROCEDURE MYPROC(IN P1 INT, IN P2 INTDEFAULT 1, OUT out1 DUMMY) AS

BEGIN

out1 = SELECT :P1 + :P2 AS DUMMY FROM DUMMY;

END;

You can see that the second parameter has a default value of 1.

To use the default values in the procedure signature, you need to pass in procedure parameters using Named Parameters.

You can call this procedure in the following ways:

With all input values specified:

CALL MYPROC(3, 4,?);

Using the default value via named parameters:

CALL MYPROC(P1 => 3, out1 => ?)

3.6Procedure Metadata

存储过程元数据

当一个存储过程在创建时，关系这个存储过程的创建信息（元数据）会被存储到一系列相关视图中

When a procedure is created, information about the procedure can be found in the database catalog. You can use this information for debugging purposes.

The procedures observable in the system views vary according to the privileges that a user has been granted.

The following visibility rules apply:

● • CATALOG READ or DATA ADMIN – All procedures in the system can be viewed.

● • SCHEMA OWNER, or EXECUTE – Only specific procedures where the user is the owner, or they have execute privileges, will be shown.

Procedures can be exported and imported like tables, see the SQL Reference documentation for details. For more information see Data Import Export Statements.

The system views for procedures are summarized below:

下面是一些存储过程元数据信息所在的视图，具体视图的结构可打开HANA查看

SYS.PROCEDURES：可用存储过程视图，

SYS. PROCEDURE_PARAMETERS：存储过程的参数

SYS.OBJECT_DEPENDENCIES：Dependencies between objects, for example, views which refer to a specific table。所依赖的对象

3.6.1 Object Dependencies View Examples

In this section we explore the ways in which you can query the OBJECT_DEPENDENCIES system view.

You create the following database objects and procedures.

dropschema deps CASCADE;

dropTYPE mytab_t;

dropTABLE mytab1;

dropTABLE mytab2 ;

CREATESCHEMA deps;

CREATETYPE mytab_t ASTABLE (id int, key_val int, val int);

CREATETABLE mytab1 (id INTPRIMARYKEY, key_val int, val INT);

CREATETABLE mytab2 (id INTPRIMARYkey, key_val int, val INT);

CREATEPROCEDURE deps.get_tables(OUT outtab1 mytab_t, OUT outtab2 mytab_t)

LANGUAGE SQLSCRIPT READS SQL DATA AS

BEGIN

outtab1 = SELECT * FROM mytab1;

outtab2 = SELECT * FROM mytab2;

END;

CREATEPROCEDURE deps.my_proc (IN val INT, OUT outtab mytab_t) LANGUAGE

SQLSCRIPT READS SQL DATA AS

BEGIN

CALL deps.get_tables(tab1, tab2);

IF :val > 1 THEN

outtab = SELECT * FROM :tab1;

ELSE

outtab = SELECT * FROM :tab2;

ENDIF;

END;

Object dependency examination对象依赖测试：

Firstly you will find all the (direct and indirect) base objects of the procedure DEPS.MY_PROC. You execute the following statement.

SELECT * FROM OBJECT_DEPENDENCIES WHERE dependent_object_name = 'MY_PROC'and dependent_schema_name = 'DEPS';;

The result obtained is as follows:

Let’s examine(看看) the DEPENDENCY_TYPE column in more detail. As you obtained the results in the table above via a select on all the base objects of the procedure, the objects show include both persistent and transient objects. You can distinguish(区分) between these object dependency types using the DEPENDENCY_TYPE column, as shown below:

1. EXTERNAL_DIRECT: base object is directly used in the dependent procedure.直接依赖

2. EXTERNAL_INDIRECT: base object is not directly used in the dependent procedure.间接依赖

查看MY_PROC在哪被使用了：

SELECT * FROM OBJECT_DEPENDENCIES WHERE base_object_name = 'MY_PROC'and base_schema_name = 'DEPS'

4 User Defined Function

There are two different kinds of user defined function (UDF): Table User Defined Function and Scalar User Defined Function in the following table are referred to as Table UDF and Scalar UDF. They differ by input/output parameter, supported functions in the body, and the way they are consumed in SQL statements.

两种不同类型的用户自定义函数：表类型用户定义函数（返回结果为表类型）、基本类型用户定义函数（返回结果为基本类型）

l 表类型函数只能在Select…From…后面使用，而基本类型函数则在Select…语句或Where…语句后面使用

l 表类型函数的输入参数可以是基本类型，也可以是表类型，但基本类型函数的输入参数只能是基本类型

l 表类型函数的输出只能表类型，而基本类型函数的输出只能是标量类型，两都不能混用

l 表类型函数体是只读的，不支持DDL与DML语句，且如果要调用Functon也要是只读的。而基本类型函数也是只读的，并且此种类型的函数不支持任何SQL语句

	Table UDF	Scalar UDF
Functions Calling	A table UDF can only be called in the FROM –clause of an SQL statement in the same parameter positions as table names. For example, SELECT * FROM myTableUDF(1)	A scalar UDF can be called in SQL statements in the same parameter positions as table column names. These occur in the SELECT and WHERE clauses of SQL statements. For example, SELECT myScalarUDF(1) AS myColumn FROM DUMMY
Input Parameter	Primitive SQL type Table types	Primitive SQL type
Output	Must return a table whose type is defined in <return_type>.	Must return scalar values specified in <return_parameter_list>
Supported functionality	The function is tagged as read only by default. DDL, DML are not allowed and only other read-only functions can be called	The function is tagged as read only function by default. This type of function does not support any kind of SQL – Statements.

4.1CREATE FUNCTION

This SQL statement creates read-only user defined functions that are free of side-effects. This means that neither DDL nor DML statements (INSERT, UPDATE, and DELETE) are allowed in the function body. All functions or procedures selected or called from the body of the function must be read-only.

Syntax

CREATEFUNCTION [<schema_name>.]<func_name> [(<parameter_clause>)] RETURNS <return_type>

[LANGUAGE <lang>] [SQL SECURITY <mode>][DEFAULTSCHEMA <default_schema_name>]

BEGIN

<function_body>

END

4.1.1 <parameter_clause>

<parameter_clause> ::= <parameter> [{,<parameter>}...]

<param_type> ::= <sql_type> | <table_type> | <table_type_definition>

Scalar UDF only supports primitive SQL types as input, whereas Table UDF also supports table types as input.标量用户函数的输入参数仅支持SQL基本类型，表类型函数除SQL基本类型外，还支持表类型

Currently the following primitive SQL types are allowed in scalar UDF:

Table UDF allows a wider range(扩展范围的基本类型) of primitive SQL types:

To look at a table type previously defined with the CREATE TYPE command, see CREATE TYPE.

<table_type_defintion> ::= TABLE (<column_list_definition>)

<column_list_definition > ::= <column_elem>[{, <column_elem>}...]

<column_elem> ::= <column_name> <data_type>

<table_type_defintion> ：A table type implicitly defined within the signature.在定义函数时，直接在函数参数签名中定义表类型，而不用Create Type…Table 预先定义

4.1.2 <return_type>

<return_type> ::= <return_parameter_list> | <return_table_type>

Table UDF must return a table whose type is defined by <return_table_type>. And scalar UDF must return scalar values specified in <return_parameter_list>.表用户函数必须返回通过<return_table_type>定义的表类型，标量用户函数必须返回由<return_parameter_list>中指定的标量值，两者不能混用

<return_parameter_list> ::= <return_parameter>[{, <return_parameter>}...]

<return_parameter> ::= <parameter_name> <sql_type>

<return_table_type> ::= TABLE ( <column_list_definition> )

标量函数可以返回多个值？

4.1.3 LANGUAGE

LANGUAGE <lang>

<lang> ::= SQLSCRIPT

Default: SQLSCRIPT

Defines the programming language used in the function.

Note：Only SQLScript UDF can be defined.

4.1.4 SQL SECURITY

SQL SECURITY <mode>

<mode> ::= DEFINER | INVOKER

Default: DEFINER (Table UDF) / INVOKER (Scalar UDF)

Specifies the security mode of the function.

DEFINER：Specifies that the execution of the function is performed with the privileges of the definer of the function.

INVOKER：Specifies that the execution of the function is performed with the privileges of the invoker of the function.

4.1.5 DEFAULT SCHEMA

DEFAULT SCHEMA <default_schema_name>

<default_schema_name> ::= <identifier>

Specifies the schema for unqualified objects in the function body. If nothing is specified, then the current_schema of the session is used.

4.1.6 <function_body>

<function_body> ::= <scalar_function_body>|<table_function_body>

<scalar_function_body> ::= [DECLARE <func_var>] <proc_assign>

<table_function_body> ::= [<func_block_decl_list>]

[<func_handler_list>]

<func_stmt_list>

<func_return_statement>

Defines the main body of the table UDF and scalar UDF. As the function is flagged as read-only, neither DDL nor DML statements (INSERT, UPDATE, and DELETE) are allowed in the function body. A scalar UDF does not support table-typed variables as its input and table operations in the function body.由于函数被标识为只读的，所以DDL和DML语句都是不允许的。标量用户函数不支持表类型变量做为输入参数，并且在函数中也不支持表操作。

For the definition of <proc_assign>, see CREATE PROCEDURE.

4.1.6.1<func_block_decl_list>

<func_block_decl_list> ::= DECLARE { <func_var>|<func_cursor>|<func_condition> }

<func_var> ::= <variable_name_list> [CONSTANT] { <sql_type>|<array_datatype> } [NOT NULL][<func_default>];

<array_datatype> ::= <sql_type> ARRAY [ = <array_constructor> ]

<array_constructor> ::= ARRAY ( <expression> [{,<expression>}...] )

<func_default> ::= { DEFAULT | = } <func_expr>

<func_expr> ::= !!An element of the type specified by <sql_type>

Defines one or more local variables with associated scalar type or array type.

An array type has <type> as its element type. An Array has a range from 1 to 2,147,483,647, which is the limitation of underlying structure.

You can assign default values by specifying <expression>s. See Expressions in the SAP HANA SQL and System Views Reference .

4.1.6.2<func_handler_list>

<func_handler_list> ::= <proc_handler_list>

See CREATE PROCEDURE .

4.1.6.3<func_stmt_list>

<func_stmt_list> ::= <func_stmt>| <func_stmt_list> <func_stmt>

For further information of the definitions in <func_stmt>, see CREATE PROCEDURE .

4.1.6.4<func_return_statement>

<func_return_statement> ::= RETURN <function_return_expr>

<func_return_expr> ::= <table_variable> | <subquery>

A table function must contain a return statement.表类型函数必须要有一个return语句

4.1.7 Example

You create a table UDF with the following definition.创建返回表类型的函数

dropFUNCTION scale;

CREATEFUNCTION scale(val INT) RETURNSTABLE (a INT, b INT) LANGUAGE SQLSCRIPT AS

BEGIN

RETURNSELECT 20 as a, :val * 2 AS b FROM dummy;

END;

You use the scale function like a table. See the following example由于返回的表类型，所以直接可以用在Select语句中:

SELECT * FROM scale(10);

SELECT * FROM scale(10) AS a, scale(10) AS b where a.a = b.a

You also create a scalar UDF with the following definition.创建返回基本类型变量的函数

CREATEFUNCTION func_add_mul(x Double, y Double) RETURNS result_add Double, result_mul Double--标量函数可以返回多个值

LANGUAGE SQLSCRIPT READS SQL DATA AS

BEGIN

--标量函数体里不能有SQL语句，且返回的值直接使用 := 返回，而不是Return

result_add := :x + :y;

result_mul := :x * :y;

END;

You use the func_add_mul function like a built-in function. See the following example:

CREATETABLE TAB (a Double, b Double);

INSERTINTO TAB VALUES (1.0, 2.0);

INSERTINTO TAB VALUES (3.0, 4.0);

--在使用时，使用点号来提取不同的返回参数值

SELECT a, b, func_add_mul(a, b).result_add asADD, func_add_mul(a,b).result_mul as MUL FROM TAB ORDERBY a;

You create a function func_mul which is assigned to a scalar variable in the func_mul_wrapper function.标量函数调用标题函数：

CREATEFUNCTION func_mul(input1 INT) RETURNS output1 INTLANGUAGE SQLSCRIPT

BEGIN

output1 := :input1 * :input1;

END;

CREATEFUNCTION func_mul_wrapper(input1 INT) RETURNS output1 INTLANGUAGE SQLSCRIPT AS

BEGIN

output1 := func_mul(:input1);

END;

SELECT func_mul_wrapper(2) as RESULT FROM dummy;--4

4.2DROP FUNCTION

DROPFUNCTION[<schema_name>.]<func_name> [<drop_option>]

<drop_option> ::= CASCADE | RESTRICT

When <drop_option> is not specified a non-cascaded drop will be performed. This will only drop the specified function, dependent objects of the function will be invalidated but not dropped.

The invalidated objects can be revalidated when an object that has same schema and object name is created.

CASCADE：Drops the function and dependent objects.级联删除

RESTRICT：Drops the function only when dependent objects do not exist. If this drop option is used and a dependent object exists an error will be thrown.如果有关联对象删除时会报错

Examples

You drop a function called my_func from the database using a non-cascaded drop.

DROPFUNCTION my_func;

4.3Function Parameters

The following tables list the parameters you can use when defining your user-defined functions.

4.4Function Metadata

When a function is created, information about the function can be found in the database catalog. You can use his information for debugging purposes. The functions observable in the system views vary according to the privileges that a user has been granted. The following visibility rules apply:

● CATALOG READ or DATA ADMIN – All functions in the system can be viewed.

● SCHEMA OWNER, or EXECUTE – Only specific functions where the user is the owner, or they have execute privileges, will be shown.

元数据视图：

SYS.FUNCTIONS A list of available functions

SYS.FUNCTIONS_PARAMETERS A list of parameters of functions

4.5Default Values for Parameters

注：只有输入参数有默认值，输出是没有的

在创建函数时，参数可以定义默认值：

IN <param_name> (<sql_type>|<table_type>|<table_type_definition>) DEFAULT (<value>|<table_name>)

droptableNAMES;

CREATECOLUMNTABLENAMES(Firstname NVARCHAR(20), LastName NVARCHAR(20));

INSERTINTONAMESVALUES('JOHN', 'DOE');

droptable MYNAMES;

CREATECOLUMNTABLE MYNAMES(Firstname NVARCHAR(20), LastName NVARCHAR(20));

INSERTINTO MYNAMES VALUES('ALICE', 'DOE');

dropfunction FULLNAME;

CREATEFUNCTION FULLNAME(

--表参数INTAB默认值为 NAMES，在不输入此参数值时，默认使用INTAB表

IN INTAB TABLE(FirstName NVARCHAR (20), LastName NVARCHAR (20)) DEFAULTNAMES,

--基本类型参数delimiter的默认值，在不输入时，默认使用逗号分隔

IN delimiter VARCHAR(10) DEFAULT', ')

returnsTABLE(fullname NVarchar(50)) AS

BEGIN

returnSELECT lastname||:delimiter|| firstname AS FULLNAME FROM :intab;

END;

--都不传时，使用默认输入参数值，从names表里取值，表使用逗号将姓与名联接起来

SELECT * FROM FULLNAME();

--下面从MYNAMES表里取数，但还是想用默认的逗号进行分隔，则要使用Named Parameters名称参数来指定具体输入参数：

SELECT * FROM FULLNAME(INTAB=> MYNAMES);

在标量函数（返回值为基本类型的函数）中，也可以使用默认参数：

dropfunction GET_FULLNAME;

CREATEFUNCTION GET_FULLNAME(

firstname NVARCHAR(20),

lastName NVARCHAR(20),

delimiter NVARCHAR(10) DEFAULT','

)

RETURNS fullname NVARCHAR(50) AS

BEGIN

fullname := :lastname||:delimiter|| :firstname;

END;

SELECT GET_FULLNAME(firstname=>firstname, lastname=>lastname)

AS FULLNAME FROMNAMES;

5 Declarative SQLScript Logic

5.1Table Parameter

[IN|OUT] <param_name> {<table_type>|<table_type_definition>}

<table_type_definition> ::= TABLE(<column_list_elements>)

Description：

Table parameters that are defined in the Signature are either input or output. They must be typed explicitly.

This can be done either by using a table type previously defined with the CREATE TYPE command or by writing it directly in the signature without any previously defined table type.

Example：

(IN inputVar TABLE(I INT),OUT outputVar TABLE (I INT, J DOUBLE))

Defines the tabular structure directly in the signature.直接在签名中定义表类型

(IN inputVar tableType, OUT outputVar outputTableType)

Using previously defined tableType and outputTableType table types.使用预定义的表类型

The advantage of previously defined table type is that it can be reused by other procedure and functions. The disadvantage is that you must take care of its lifecycle.使用预先定义好的表类型的好处是可以在不同的过程与函数中重用它，不好的是要小心其生命周期（如删除后，导致使用的的过程函数不能再用）

The advantage of a table variable structure that you directly define in the signature is that you do not need to take care of its lifecycle. In this case, the disadvantage is that it cannot be reused.在过程与函数定义时，在签名中临时定义局部表类型的好处是不有担心其生命周期，不好的是不能重用

6.2 Local Table Variables

Local table variables are, as the name suggests, variables with a reference to tabular data structure. This data

structure originates from an SQL Query.

5.2Table Variable Type Definition

The type of a table variable in the body of procedure or table function is either derived from the SQL Query or it can be declared explicitly.

表类型的变量可以在过程（或表类型函数）中从SQL Query赋值获得，或明确定义

CREATEprocedure proc() LANGUAGE SQLSCRIPT AS

BEGIN

declare a intdefault 2;--基本类型变量使用前一定要定义

--declare tab_var1 table(a int,b int); 明确定义表类型，但表类型变量可不定义就使用，请看后面直接使用

-- 基本类型变量使用前需要定义

a := 1;

-- a = 1; 基本类型变量赋值时，等号前一定要加冒号，这与表类型变量恰好相反，所以此语句编译出错

-- 如果某个变量是表类型的变量，可以不用声明，直接就可以使用，这与基本类型变量是不一样的

tab_var1 = select 1 as a,2 as b from dummy;

--tab_var1 := select 1 as a,2 as b from dummy; 这是错误，表类型变量赋值时，只能使用等号，不能在等号前加冒号，这与基本类型变量赋值相反

END;

If the table variable derived its type from the SQL Query, the SQLScript compiler determines the type from the first assignments of that variable. This provides a great deal of flexibility. One disadvantage however, is that it also lead to many type conversions in the background. This is because sometimes the derived table type does not match the typed table parameters at the signature. This can lead to additional conversion costs, which are unnecessary.如果表类型变量是从某个SQL Query派生而来，在第一次给变量赋值时SQLScript编译器就决定变量的类型。这样有很大的灵活性。然而一个不好的是，这样会导致许多类型在后台发生转换。这是因为有时候派生表类型与签名中的表类型参数不匹，这会导致额外的转换成本

To avoid this unnecessary cost, you can declare the type of a table variable explicitly.避免转换成本，则可以明确定义

Signature定义：

DECLARE <sql_identifier> [{,<sql_identifier> }...] {TABLE (<column_list_definition>)|<table_type>}

Sample Code：

DECLARE temp TABLE (n int);

DECLARE temp MY_TABLE_TYPE;

Description:

Local table variables are declared using the DECLARE keyword. A table variable var can be referenced by using :var. For more information, see Referencing Variables. The <sql_identifier> must be unique among all other scalar variables and table variables in the same code block. You can, however, use names that are identical to the name of another variable in a different code block. Additionally, you can reference these identifiers only in their local scope.局部表变量使用是DECLARE关键进行定义。如表变量var在引用时，:var 这样使用。变量的名不能与其他变量名重名。但，你可以在不同的块中定义相同的变量名，但也只限于本块中使用

CREATEPROCEDURE exampleExplicit (OUT outTab TABLE(n int))

LANGUAGE SQLScript READS SQL DATA AS

BEGIN

DECLARE temp TABLE (n int);

temp = SELECT 1 as n FROM DUMMY ;

BEGIN

DECLARE temp TABLE (n int);--可以在不同的块中定义同名的变量，屏蔽了外层同名变量

temp = SELECT 2 as n FROM DUMMY ;

outTab = Select * from :temp;--执行时，该Select不会显示，原因是外层块又重新给表输出变量outTab重新赋值了

END;

outTab = Select * from :temp;

END;

call exampleExplicit(?);

In each block there are table variables declared with identical names. However, since the last assignment to the output parameter <outTab> can only have the reference of variable <temp> declared in the same block, the result is as follows:

CREATEPROCEDURE exampleDerived (OUT outTab TABLE(n int))

LANGUAGE SQLScript READS SQL DATA

BEGIN

temp = SELECT 1 as n FROM DUMMY ;

BEGIN

temp = SELECT 2 as n FROM DUMMY ;--由于temp是派生而来，在内层块并没有重新Declare，所以与外层的temp是同一个

outTab = Select * from :temp;--与前面实例一样，在执行时，这个Select也不会显示出来

END;

outTab = Select * from :temp;

END;

call exampleDerived (?);

In this code example, there is no explicit table variable declaration where done, that means the <temp> variable is visible among all blocks. For this reason, the result is as follows:

Table变量派生法与明确定义的区别（经测试好像不是这样，新版本没有这样的限制了？）：

The following declarations are not supported (compare with scalar variable declaration): CONSTANT, NOT NULL option, Default Value.与基本变量声明对比，表类型变量声明时不能使用 CONSTANT, NOT NULL, Default这些

5.3Binding(赋值) Table Variables

Table variables are bound using the equality operator(表变量只能使用等号赋值=，而不是冒号加等号 :=，这与基本变量赋值恰好相反).

5.4Referencing Variables

如果是在赋值左边时，直接就是变量名，如果是在赋值右边或其它表达式中时使用该变量时，则使用 :var方式来引用

lt_expensive_books = SELECT title, price, crcy FROM :it_books WHERE price > :minPrice AND crcy = :currency;

In this assignment, the variable <lt_expensive_books>(明确定义的表类型变量是或者是派生的表变量) is bound. The <:it_books> variable in the FROM clause refers to an IN parameter of a table type. It would also be possible to consume variables of type table in the FROM clause which were bound by an earlier statement. <:minPrice> and <:currency> refer to IN parameters of a scalar type.

5.5Column View Parameter Binding

SELECT * FROM <column_view> ( <named_parameter_list> );

<named_parameter_list> ::= <named_parameter> [{,<named_parameter>}…}]

A list of parameters to be used with the column view.

<named_parameter> ::= <parameter_name> => <expression>

Defines the parameter used to refer to the given expression.

<parameter_name> ::= {PLACEHOLDER.<identifier> | HINT.<identifier> |<identifier>}

The parameter name definition. PLACEHOLDER is used for place holder parameters and HINT for hint parameters.

Description：

Using column view parameter binding it is possible to pass parameters from a procedure/scripted calculation view to a parameterized column view e.g. hierarchy view, graphical calculation view, scripted calculation view.

Examples:

Example 1 - Basic example

In the following example, assume(假设) you have the calculation view CALC_VIEW(计算视图) with placeholder parameters "client" and "currency". You want to use this view in a procedure and bind the values of the parameters during the execution of the procedure.

CREATEPROCEDURE my_proc_caller (IN in_client INT, IN in_currency INT, OUT outtab mytab_t) LANGUAGE SQLSCRIPT READS SQL DATA AS

BEGIN

outtab = SELECT * FROM CALC_VIEW (PLACEHOLDER."$$client$$" => :in_client , PLACEHOLDER."$$currency$$" => :in_currency );

END;

Example 2 - Using a Hierarchical View

The following example assumes that you have a hierarchical column view "H_PROC"(派生列视图？) and you want to use this view in a procedure. The procedure should return an extended expression that will be passed via a variable.

CREATEPROCEDURE"EXTEND_EXPRESSION"(IN in_expr nvarchar(20),OUT out_result "TTY_HIER_OUTPUT")

LANGUAGE SQLSCRIPT READS SQL DATA AS

BEGIN

DECLARE expr VARCHAR(256) := 'leaves(nodes())';

IF :in_expr <> ''THEN

expr := 'leaves(' || :in_expr || ')';

ENDIF;

out_result = SELECT query_node, result_node FROM h_proc ("expression" => :expr ) as h orderby h.result_node;

END;

You call this procedure as follows.

CALL"EXTEND_EXPRESSION"('',?);

CALL"EXTEND_EXPRESSION"('subtree("B1")',?);

5.6HINTS: NO_INLINE and INLINE

The SQLScript compiler combines(合并) statements to optimize code. Hints enable you to block or enforce the inlining of table variables.

Note：Using a HINT needs to be considered carefully. In some cases, using a HINT could end up being more expensive.

Block Statement-Inlining(阻止内联)

The overall optimization guideline in SQLScript states that dependent statements are combined if possible.

For example, you have two table variable assignments as follows:

tab = select A, B, C from T where A = 1;

tab2 = select C from :tab where C = 0;

The statements are combined to one statement(会合并成一条语句) and executed:

select C from (select A,B,C from T where A = 1) where C=0;

There can be situations, however, when the combined statements lead to a non-optimal plan and as a result, to less-than-optimal performance of the executed statement. In these situations it can help to block the combination of specific statements. Therefore SAP has introduced a HINT called NO_INLINE. By placing that HINT at the end of select statement, it blocks the combination (or inlining) of that statement into other statements. An example of using this follows:

tab = select A, B, C from T where A = 1 WITH HINT(NO_INLINE);

tab2 = select C from :tab where C = 0;

By adding WITH HINT (NO_INLINE) to the table variable tab, you can block the combination(阻止合并) of that statement and ensure that the two statements are executed separately.

Enforce Statement-Inlining(迫使内联)

Using the hint called INLINE helps in situations when you want to combine the statement of a nested procedure into the outer procedure.

Currently statements that belong to nested procedure are not combined into the statements of the calling procedures. In the following example, you have two procedures defined.

CREATEPROCEDURE procInner (OUT tab2 TABLE(I int))

LANGUAGE SQLSCRIPT READS SQL DATA

BEGIN

tab2 = SELECT I FROM T;

END;

CREATEPROCEDURE procCaller (OUT table2 TABLE(I int))

LANGUAGE SQLSCRIPT READS SQL DATA

BEGIN

call procInner (outTable);

tab = select I from :outTable where I > 10;

END;

By executing the procedure, ProcCaller, the two table assignments are executed separately. If you want to have both statements combined, you can do so by using WITH HINT (INLINE) at the statement of the output table variable. Using this example, it would be written as follows:

CREATEPROCEDURE procInner (OUT tab2 TABLE(I int))

LANGUAGE SQLSCRIPT READS SQL DATA

BEGIN

tab2 = SELECT I FROM T WITH HINT (INLINE);

END;

Now, if the procedure, ProcCaller, is executed, then the statement of table variable tab2 in ProcInner is combined into the statement of the variable, tab, in the procedure, ProcCaller:

SELECT I FROM (SELECT I FROM T WITH HINT (INLINE)) where I > 10;

6 Imperative SQLScript Logic

In this section we will focus on imperative language constructs such as loops and conditionals. The use of
imperative logic splits the logic among several dataflows.

6.1Local Scalar Variables

Syntax
DECLARE <sql_identifier> [CONSTANT] <type> [NOT NULL] [<proc_default>]
Syntax Elements
<proc_default> ::= (DEFAULT | '=' ) <value>|<expression>
Default value expression assignment.
<value> !!= An element of the type specified by <type>
The value to be assigned to the variable.
Description
Local variables are declared using DECLARE keyword and they can optionally be initialized with their declaration. By default scalar variables are initialized with NULL. A scalar variable var can be referenced the same way as described above using :var.
Tip
If you want to access(访问) the value of the variable, then use :var(注：如果是在Select…into 后面，则只能是var，因为into相当于赋值，而不是访问，所以不能在前加冒号)in your code. If you want to assign a value to the variable, then use var in your code.

Assignment is possible multiple times, overwriting the previous value stored in the scalar variable. Assignment(赋值) is performed using the=(注：这是错误的，基本变量赋值要使用:= 的方式来赋值，如果是赋值给表变量，则才只能使用= ) operator.
Recommendation
SAP recommends that you use only the = operator in defining scalar variables. (The := operator is still available, however.)

Example
CREATEPROCEDURE proc (OUT z INT) LANGUAGE SQLSCRIPT READS SQL DATA

BEGIN

DECLARE a int;

DECLARE b int := 0;--定义时，如果需指定默认值，除了使用DEFAULT关键字外，可以使用等号，但前面需要加上冒号:=

DECLARE c intDEFAULT 0;

t = select * from dummy ;

selectcount(*) into a from :t;

b := :a + 1;

z := :b + :c;

end;

In the example you see the various ways of making declarations and assignments.
Note
Before the SAP HANA SPS 08 release, scalar UDF(标量用户函数) assignment to the scalar variable was not supported. If you wanted to get the result value from a scalar UDF and consume it in a procedure, the scalar UDF had to be used in a SELECT statement, even though this was expensive. Now you can assign a scalar UDF to a scalar variable with 1 output or more than 1 output, as depicted in the following code examples.Consuming the result using an SQL statement:
CREATEFUNCTION SUDF_ADD(input1 INT,input2 INT) RETURNS s INTLANGUAGE SQLSCRIPT AS

BEGIN

s := :input1 + :input2;

END;

CREATEPROCEDURE caller(input1 INT,input2 INT) LANGUAGE SQLSCRIPT AS

BEGIN

DECLARE i INTEGERDEFAULT 0;

SELECT SUDF_ADD(:input1, :input2) into ifrom dummy;--以前版本只能通过Select语句将标量函数返回值赋值给基本变量

select :i from dummy;

END;

call caller(1,2);

Assign the scalar UDF to the scalar variable:
CREATEPROCEDURE caller(input1 INT,input2 INT) LANGUAGE SQLSCRIPT AS

BEGIN

DECLARE i INTEGERDEFAULT 0;

i := SUDF_ADD(:input1, :input2);--现在可以将标量用户函数的返回值通过等号直接赋值给基本变量

select :i from dummy;

END;

call caller(1,2);

Assign the scalar UDF with more than 1 output to scalar variables:
CREATEFUNCTION xy(input1 INT,input2 INT) RETURNS x INT,y INTLANGUAGE SQLSCRIPT AS

BEGIN

x := input1;

y := input2;

END;

CREATEPROCEDURE caller(input1 INT,input2 INT) LANGUAGE SQLSCRIPT AS

BEGIN

DECLARE i INTEGERDEFAULT 0;

DECLARE j NVARCHAR(5);

--(i,j) := xy(:input1,:input2); --好像不支持这种赋值方法

DECLARE a INTEGERDEFAULT 0;

--a := xy(:input1,:input2).x; -- 这样也不行

select xy(:input1,:input2).x into a from dummy; --这样才可啊，好像只能在Select语句中才能使用点号来取不现的返回值

select a from dummy;

END;

call caller(1,2);

6.2Variable Scope Nesting

SQLScript supports local variable declaration in a nested block. Local variables are only visible in the scope of the block in which they are defined. It is also possible to define local variables inside LOOP / WHILE /FOR / IFELSE control structures.
Consider the following code:
CREATEPROCEDURE nested_block(OUT val INT) LANGUAGE SQLSCRIPT READS SQL DATA AS

BEGIN

DECLARE a INT := 1;

BEGIN

DECLARE a INT := 2;

BEGIN

DECLARE a INT;

a := 3;

END;

val := a;

END;

When you call this procedure the result is:
call nested_block(?)-- 2
From this result you can see that the inner most nested block value of 3 has not been passed to the val variable. Now let's redefine the procedure without the inner most DECLARE statement:
CREATEPROCEDURE nested_block(OUT val INT) LANGUAGE SQLSCRIPT READS SQL DATA AS

BEGIN

DECLARE a INT := 1;

BEGIN

DECLARE a INT := 2;

BEGIN

a := 3;

END;

val := a;

END;

Now when you call this modified procedure the result is:
call nested_block(?)-- 3
From this result you can see that the innermost nested block has used the variable declared in the second level nested block.

6.2.1 Local Variables in Control Structures

6.2.1.1 IF条件块

CREATEPROCEDURE nested_block_if(IN inval INT, OUT val INT) LANGUAGE SQLSCRIPT

READS SQL DATA AS

BEGIN

DECLARE a INT := 1;

DECLARE v INT := 0;

--对当前（所在）BEGIN...END块中出现的异常进行捕获，如这里的除0，如不捕获取，则抛异常

--这会导致存储过程不能正常执行完成，得不到运行结果

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

val := :a;--赋 1

END;

v := 1 /(1-:inval);

IF :a = 1 THEN

DECLARE a INT := 2;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

val := :a;--赋 2

END;

v := 1 /(2-:inval);

IF :a = 2 THEN

DECLARE a INT := 3;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

val := :a;--赋 3

END;

v := 1 / (3-:inval);

ENDIF;

v := 1 / (4-:inval);--如果程序走到这里，则会为 val = 2,不会是3，因为a = 3所在的IF…END IF块已结束，所以使用外层IF里定义的 a = 2

ENDIF;

v := 1 / (5-:inval);--如果程序走到这里，则会为 val = 1

END;

call nested_block_if(1, ?);-- 1

call nested_block_if(2, ?);-- 2

call nested_block_if(3, ?);-- 3

call nested_block_if(4, ?);-- 2

call nested_block_if(5, ?);-- 1

6.2.1.2 While Loop循环块

CREATEPROCEDURE nested_block_while(OUT val INT) LANGUAGE SQLSCRIPT READS SQL

DATA AS

BEGIN

DECLARE v int := 2;

val := 0;

WHILE v > 0 DO

DECLARE a INT := 0;

a := :a + 1;

val := :val + :a;

v := :v - 1;

ENDWHILE;

END;

call nested_block_while(?) -- 2

6.2.1.3 For Loop循环块

CREATETABLE mytab1(a int);

CREATETABLE mytab2(a int);

CREATETABLE mytab3(a int);

INSERTINTO mytab1 VALUES(1);

INSERTINTO mytab2 VALUES(2);

INSERTINTO mytab3 VALUES(3);

CREATEPROCEDURE nested_block_for(IN inval INT, OUT val INT) LANGUAGE SQLSCRIPT

READS SQL DATA AS

BEGIN

DECLARE a1 intdefault 0;

DECLARE a2 intdefault 0;

DECLARE a3 intdefault 0;

DECLARE v1 intdefault 1;

DECLARE v2 intdefault 1;

DECLARE v3 intdefault 1;

DECLARECURSOR C FORSELECT * FROM mytab1;

FOR R as C DO

DECLARECURSOR C FORSELECT * FROM mytab2;

a1 := :a1 + R.a;

FOR R as C DO

DECLARECURSOR C FORSELECT * FROM mytab3;

a2 := :a2 + R.a;

FOR R as C DO

a3 := :a3 + R.a;

ENDFOR;

IF inval = 1 THEN

val := :a1;

ELSEIF inval = 2 THEN

val := :a2;

ELSEIF inval = 3 THEN

val := :a3;

ENDIF;

END;

call nested_block_for(1, ?);--1

call nested_block_for(2, ?);--2

call nested_block_for(3, ?);--3

6.2.1.4 Loop循环

Note
The example below uses tables and values created in the For Loop example above.
CREATETABLE mytab1(a int);

CREATETABLE mytab2(a int);

CREATETABLE mytab3(a int);

INSERTINTO mytab1 VALUES(1);

INSERTINTO mytab2 VALUES(2);

INSERTINTO mytab3 VALUES(3);

CREATEPROCEDURE nested_block_loop(IN inval INT, OUT val INT) LANGUAGE

SQLSCRIPT READS SQL DATA AS

BEGIN

DECLARE a1 int;

DECLARE a2 int;

DECLARE a3 int;

DECLARE v1 intdefault 1;

DECLARE v2 intdefault 1;

DECLARE v3 intdefault 1;

DECLARECURSOR C FORSELECT * FROM mytab1;

OPEN C;

FETCH C into a1;

CLOSE C;

LOOP

DECLARECURSOR C FORSELECT * FROM mytab2;

OPEN C;

FETCH C into a2;

CLOSE C;

LOOP

DECLARECURSOR C FORSELECT * FROM mytab3;

OPEN C;

FETCH C INTO a3;

CLOSE C;

IF :v2 = 1 THEN

BREAK;

ENDIF;

END LOOP;

IF :v1 = 1 THEN

BREAK;

ENDIF;

END LOOP;

IF :inval = 1 THEN

val := :a1;

ELSEIF :inval = 2 THEN

val := :a2;

ELSEIF :inval = 3 THEN

val := :a3;

ENDIF;

END;

call nested_block_loop(1, ?);--1

call nested_block_loop(2, ?);--2

call nested_block_loop(3, ?);--3

6.3Control Structures控制语句

6.3.1 IF

Syntax:

IF <bool_expr1> THEN

<then_stmts1>

[{ELSEIF <bool_expr2> THEN

<then_stmts2>}...]

[ELSE

<else_stmts3>]

ENDIF
Syntax elements:
<bool_expr1> ::= <condition>
<bool_expr2> ::= <condition>
               <condition> ::= <comparison> | <null_check>
                              <comparison> ::= <comp_val> <comparator> <comp_val>
                                             <comp_val> ::= <scalar_expression>|<scalar_udf>
                                                            <scalar_expression> ::=<scalar_value>[{+|-|/|*}<scalar_value>…]
                                                             <scalar_value> ::= <numeric_literal> | <exact_numeric_literal>|<unsigned_numeric_literal>

<comparator> ::=< | > | = | <= | >= | !=
<null_check> ::= <comp_val> IS [NOT] NULL

Note：NULLis the default value for all local variables.NULL是所有局部变量的默认值

CREATEPROCEDURE nullTest() LANGUAGE

SQLSCRIPT READS SQL DATA AS

BEGIN

DECLARE a1 int;--整型没赋值时，也可看作是NULL，只要赋值了，不管是否是0，则会看作是非空

if a1 isnullthen

select'null'from dummy;-- 经测试会输出这个

else

select'not null'from dummy;

endif;

END;

call nullTest();

Examples:
Example 1
You use the IF statement to implementing the functionality of the SAP HANA database`s UPSERT statement.

CREATEPROCEDURE upsert_proc (IN v_isbn VARCHAR(20))

LANGUAGE SQLSCRIPT AS

BEGIN

DECLAREfoundINT = 1;

SELECTcount(*) INTOfoundFROM books WHERE isbn = :v_isbn;

IF :found = 0 THEN

INSERTINTO books VALUES (:v_isbn, 'In-Memory Data Management', 1, 1, '2011', 42.75, 'EUR');

ELSE

UPDATE books SET price = 42.75 WHERE isbn =:v_isbn;

ENDIF;

END;
Example 2
You use the IF statement to check if variable :found is NULL.
SELECTcount(*) INTOfoundFROM books WHERE isbn = :v_isbn;

IF :found IS NULLTHEN

CALLins_msg_proc('result of count(*) cannot be NULL');

ELSE

CALLins_msg_proc('result of count(*) not NULL - as expected');

ENDIF;

Example 3
It is also possible to use a scalar UDF in the condition, as shown in the following example.
CREATEPROCEDURE proc (in input1 INTEGER, out output1 TYPE1)

BEGIN

DECLARE i INTEGERDEFAULT :input1;

IFSUDF(:i) = 1 THEN

output1 = SELECTvalueFROM T1;

ELSEIF SUDF(:i) = 2 THEN

output1 = SELECTvalueFROM T2;

ELSE

output1 = SELECTvalueFROM T3;

ENDIF;

END;

6.3.2 WHILE

Syntax:
WHILE <condition> DO

<proc_stmts>

ENDWHILE
Syntax elements:
<condition> ::= <comparison> | <null_check>
                   <comparison> ::= <comp_val> <comparator> <comp_val>
                                  <comp_val> ::= <scalar_expression>|<scalar_udf>
                                                  <scalar_expression> ::=<scalar_value>[{+|-|/|*}<scalar_value>…]
                                                  <scalar_value> ::= <numeric_literal> | <exact_numeric_literal>|<unsigned_numeric_literal>

<comparator> ::=< | > | = | <= | >= | !=
<null_check> ::= <comp_val> IS [NOT] NULL

Example 1

You use WHILE to increment the :v_index1 and :v_index2 variables using nested loops.
CREATEPROCEDURE procWHILE (OUT V_INDEX2 INTEGER) LANGUAGE SQLSCRIPT

READS SQL DATA

BEGIN

DECLARE v_index1 INT := 0;

WHILE :v_index1 < 5 DO

v_index2 := 0;

WHILE :v_index2 < 5 DO

v_index2 := :v_index2 + 1;

ENDWHILE;

v_index1 := :v_index1 + 1;

ENDWHILE;

END;

Example 2
You can also use scalar UDF for the while condition as follows.
CREATEPROCEDURE proc (in input1 INTEGER, out output1 TYPE1)

BEGIN

DECLARE i INTEGERDEFAULT :input1;

DECLARE cnt INTEGERDEFAULT 0;

WHILE SUDF(:i) > 0 DO

cnt := :cnt + 1;

i = :i - 1;

ENDWHILE;

output1 = SELECTvalueFROM T1 where id = :cnt ;

END;

6.3.3 FOR

FOR <loop-var> IN [REVERSE] <start_value> .. <end_value> DO

<proc_stmts>

ENDFOR
<loop-var> ：用来接收当前循环值的变量
REVERSE：以降序方式循环（从end_value到start_value递减）

<start_value>：大于等于0的整数

<end_value>：大于等于<start_value>
每次循环时，步进值为1

Example 1
CREATEPROCEDURE proc() LANGUAGE SQLSCRIPT

READS SQL DATA

BEGIN

DECLARE i INTEGER;

FOR i IN REVERSE 0..1 DO

select :i from dummy;-- 1 , 0

ENDFOR;

END;

Example 2
You can also use scalar UDF in the FOR loop, as shown in the following example.
CREATEPROCEDURE proc (out output1 TYPE1)LANGUAGE SQLSCRIPT

READS SQL DATA

BEGIN

DECLARE pos INTEGERDEFAULT 0;

DECLARE i INTEGER;

FOR i IN 1..SUDF_ADD(1, 2) DO

pos = :pos + 1;

ENDFOR;

output1 = SELECTvalueFROM T1 whereposition = :i ;

END;

6.3.4 Break 、Continue

BREAK;

CONTINUE;

BREAK：跳出整个循环
CONTINUE：跳出当前循环，继续下次循环

Example:
You defined the following loop sequence. If the loop value :x is less than 3 the iterations will be skipped. If :x is 5 then the loop will terminate.
CREATEPROCEDURE proc() LANGUAGE SQLSCRIPT

READS SQL DATA

BEGIN

DECLARE x integer;

FOR x IN 0 .. 10 DO

IF :x < 3 THEN

CONTINUE;

ENDIF;

IF :x = 5 THEN

BREAK;

ENDIF;

ENDFOR;

END;

6.4Cursors

Cursors are used to fetch single rows from the result set returned by a query. When the cursor is declared it is bound to a query. It is possible to parameterize the cursor query.

6.4.1 Define Cursor

Syntax:
CURSOR <cursor_name> [({<param_def>{,<param_def>} ...)] FOR <select_stmt>
Syntax elements:
<param_def> = <param_name> <param_type>
Defines an optional SELECT parameter.
<param_type> ::= DATE | TIME | SECONDDATE | TIMESTAMP | TINYINT| SMALLINT | INTEGER | BIGINT | SMALLDECIMAL | DECIMAL| REAL | DOUBLE | VARCHAR | NVARCHAR | ALPHANUM| VARBINARY | BLOB | CLOB | NCLOB
Defines the datatype of the parameter.
<select_stmt> !!= SQL SELECT statement.
Defines an SQL select statement. See SELECT.

Description:
Cursors can be defined either after the signature of the procedure and before the procedure’s body or at the beginning of a block with the DECLARE token. The cursor is defined with a name, optionally a list of parameters,and an SQL SELECT statement. The cursor provides the functionality to iterate through a query result row-byrow. Updating cursors is not supported.
Note：
Avoid using cursors when it is possible to express the same logic with SQL. You should do this as cursors cannot be optimized the same way SQL can.

Example:
You create a cursor c_cursor1 to iterate over results from a SELECT on the books table. The cursor passes one parameter v_isbn to the SELECT statement.
DECLARECURSOR c_cursor1 (v_isbn VARCHAR(20)) FOR

SELECT isbn, title, price, crcy FROM books WHERE isbn = :v_isbn ORDERBY isbn;

6.4.2 Open Cursor

Syntax:
OPEN <cursor_name>[(<argument_list>)]
Syntax elements:
<argument_list> ::= <arg>[,{<arg>}...]
Specifies one or more arguments to be passed to the select statement of the cursor.
<arg> ::= <scalar_value>
Specifies a scalar value to be passed to the cursor.

Description:
Evaluates the query bound to a cursor and opens the cursor so that the result can be retrieved. When the cursor definition contains parameters then the actual values for each of these parameters must be provided when the cursor is opened.

Example:
You open the cursor c_cursor1 and pass a string '978-3-86894-012-1' as a parameter.
OPEN c_cursor1('978-3-86894-012-1');

6.4.3 Close Cursor

Syntax:
CLOSE <cursor_name>

Description:
Closes a previously opened cursor and releases all associated state and resources. It is important to close all cursors that were previously opened.
Example:
You close the cursor c_cursor1.
CLOSE c_cursor1;

6.4.4 Fetch Query Results of a Cursor

Syntax:
FETCH <cursor_name> INTO <variable_list>
Syntax elements:
<variable_list> ::= <var>[,{<var>}...]
Specifies the variables where the row result from the cursor will be stored.
<var> ::= <identifier>
Specifies the identifier of a variable.

Description:
Fetches a single row in the result set of a query and advances(使前进) the cursor to the next row. This assumes(前提) that the cursor was declared and opened before. One can use the cursor attributes to check if the cursor points to a valid row. See Attributes of a Cursor
Example:
You fetch a row from the cursor c_cursor1 and store the results in the variables shown.
FETCH c_cursor1 INTO v_isbn, v_title, v_price, v_crcy;

6.4.5 Attributes of a Cursor

A cursor provides a number of methods to examine its current state. For a cursor bound to variable c_cursor1, the attributes summarized in the table below are available.

Example:
The example below shows a complete procedure using the attributes of the cursor c_cursor1 to check if fetching a set of results is possible.
CREATEPROCEDURE cursor_proc LANGUAGE SQLSCRIPT AS

BEGIN

DECLARE v_isbn VARCHAR(20);

DECLARE CURSOR c_cursor1 (v_isbn VARCHAR(20)) FOR

SELECT isbn, title, price, crcy FROM books WHERE isbn = :v_isbn ORDERBY isbn;

OPEN c_cursor1('978-3-86894-012-1');

IF c_cursor1::ISCLOSEDTHEN

CALLins_msg_proc('WRONG: cursor not open');

ELSE

CALLins_msg_proc('OK: cursor open');

ENDIF;

FETCH c_cursor1 INTO v_isbn, v_title, v_price, v_crcy;

IF c_cursor1::NOTFOUNDTHEN

CALLins_msg_proc('WRONG: cursor contains no valid data');

ELSE

CALLins_msg_proc('OK: cursor contains valid data');

ENDIF;

CLOSE c_cursor1;

END

6.4.6 Looping over Result Sets循环获取光标结果集

Syntax:
FOR <row_var> AS <cursor_name>[(<argument_list>)] DO

<proc_stmts> | {<row_var>.<column>}

ENDFOR

该语句会自动打开（Open）光标、读取（Fetch）光标、并且循环完后关闭（Close）光标
Syntax elements:
<row_var> ::= <identifier>
Defines an identifier to contain the row result.
<cursor_name> ::= <identifier>
Specifies the name of the cursor to be opened.
<argument_list> ::= <arg>[,{<arg>}...]
Specifies one or more arguments to be passed to the select statement of the cursor.
<arg> ::= <scalar_value>
Specifies a scalar value to be passed to the cursor.
<proc_stmts> ::= !! SQLScript procedural statements
Defines the procedural statements that will be looped over.
<row_var>.<column> ::= !! Provides attribute access
To access the row result attributes in the body of the loop you use the syntax shown.

Description:
Opens a previously declared cursor and iterates over each row in the result set of the query bound to the cursor. For each row in the result set the statements in the body of the procedure are executed. After the last row from the cursor has been processed, the loop is exited and the cursor is closed.
Tip
As this loop method takes care of opening and closing cursors, resource leaks(资源泄漏) can be avoided. Consequently(因此) this loop is preferred(首先) to opening and closing a cursor explicitly and using other loop-variants. Within the loop body, the attributes of the row that the cursor currently iterates over can be accessed like an attribute of the cursor. Assuming假设<row_var> is a_row and the iterated data contains a column test, then the value of this column can be accessed using a_row.test.

Example:
The example below demonstrates using a FOR-loop to loop over the results from c_cursor1 .
CREATEPROCEDURE foreach_proc() LANGUAGE SQLSCRIPT AS

BEGIN

DECLARE v_isbn VARCHAR(20) := '';

DECLARECURSOR c_cursor1 (v_isbn VARCHAR(20)) FOR

SELECT isbn, title, price, crcy FROM books ORDERBY isbn;

FOR cur_row as c_cursor1 DO

CALLins_msg_proc('book title is: ' || cur_row.title);

ENDFOR;

END;

6.5Autonomous Transaction

Syntax:
BEGIN AUTONOMOUS TRANSACTION

[<proc_decl_list>]

[<proc_handler_list>]

[<proc_stmt_list>]

END;
Description:
The autonomous transaction(独立事务) is independent(不依赖) from the main procedure(主存储过程块). Changes made and committed by an autonomous transaction can be stored in persistency regardless of commit/rollback of the main procedure transaction（不管主存储过程是否提交或回滚，独立事务块都会受其影响，而是按自己内存）. The end of the autonomous transaction block has an implicit commit.

BEGIN AUTONOMOUS TRANSACTION

…(some updates) –(1)

COMMIT;

…(some updates) –(2)

ROLLBACK;

…(some updates) –(3)

END;

The examples show how commit and rollback work inside the autonomous transaction block. The first updates (1) are committed, whereby the updates made in step (2) are completely rolled back. And the last updates (3)are committed by the implicit commit at the end of the autonomous block.

createtable ERR_TABLE(PARAMETER int,SQL_ERROR_CODE int,SQL_ERROR_MESSAGE VARCHAR(5000));

CREATEPROCEDURE PROC1( IN p INT,OUT outtab TABLE (A DECIMAL) ) LANGUAGE SQLSCRIPT

BEGIN

DECLARE errCode INT;

DECLARE errMsg VARCHAR(5000);

DECLARE EXIT HANDLER FOR SQLEXCEPTION --外层BEGIN…END块的异常处理块，即如果外层中的脚本语句执行出异常后，就会走这段（内层BEGIN…END块处理逻辑），如果传进来的是0，则会抛被0除的异常，该异常处理块会捕获到并处理（将异常信息存储到ERR_TABLE表中）

BEGIN AUTONOMOUS TRANSACTION -- 异常处理块

errCode := ::SQL_ERROR_CODE; --这个好像是全局的变量，但只能是 AUTONOMOUS TRANSACTION 块里使用，用来捕获异常号

errMsg := ::SQL_ERROR_MESSAGE;

INSERTINTO ERR_TABLE (PARAMETER,SQL_ERROR_CODE, SQL_ERROR_MESSAGE) VALUES ( :p, :errCode, :errMsg);

END;

outtab = SELECT 1/:p as A FROM DUMMY; -- DIVIDE BY ZERO Error if p=0

END;

call PROC1(0,?);
In the example above, an autonomous transaction is used to keep the error code in the ERR_TABLE stored in persistency.
If the exception handler block were not an autonomous transaction, then every insert would be rolled back because they were all made in the main transaction. In this case the result of the ERR_TABLE is as shown in the following example.

It is also possible to have nested autonomous transactions.
CREATEPROCEDURE P2()

ASBEGIN

BEGIN AUTONOMOUS TRANSACTION

INSERTINTO LOG_TABLE VALUES ('MESSAGE');

BEGIN AUTONOMOUS TRANSACTION

ROLLBACK;

END;

The LOG_TABLE table contains 'MESSAGE', even though the inner autonomous transaction rolled back.

Supported statements inside the block独立事务块里支持的语句
● SELECT, INSERT, DELETE, UPDATE, UPSERT, REPLACE
● IF, WHILE, FOR, BEGIN/END
● COMMIT, ROLLBACK, RESIGNAL, SIGNAL
● Scalar variable assignment
Unsupported statements inside the block不支持的语句
● DDL
● Cursor
● Table assignments

6.6COMMIT and ROLLBACK

The COMMIT and ROLLBACK commands are supported natively in SQLScript.
The COMMIT command commits the current transaction and all changes before the COMMIT command is written to persistence.
The ROLLBACK command rolls back the current transaction and undoes all changes since the last COMMIT.

在COMMIT、ROLLBACK执行的一，都会重新开启一个新的事务
Example 1:
CREATEPROCEDURE PROC1() AS

BEGIN

UPDATE B_TAB SET V = 3 WHERE ID = 1;

COMMIT; --上一更新语句会生效

UPDATE B_TAB SET V = 4 WHERE ID = 1;

ROLLBACK; --上一更新语句不会生效

END;

Example 2:
CREATEPROCEDURE PROC2() AS

BEGIN

UPDATE B_TAB SET V = 3 WHERE ID = 1;

COMMIT;

END;

CREATEPROCEDURE PROC1() AS

BEGIN

UPDATE A_TAB SET V = 2 WHERE ID = 1;

CALL PROC2();--PROC2存储过程里的更新一并会被提交

UPDATE A_TAB SET V = 3 WHERE ID = 1;

ROLLBACK;

END;

6.7Dynamic SQL

Dynamic SQL allows you to construct an SQL statement during the execution time of a procedure. While dynamic SQL allows you to use variables where they might not be supported in SQLScript and also provides more flexibility in creating SQL statements, it does have the disadvantage缺点 of an additional cost at runtime:
● Opportunities for optimizations are limited.性能优化受到限制
● The statement is potentially recompiled every time the statement is executed.每次执行时需要重新编译
● You cannot use SQLScript variables in the SQL statement.不能使用SQL脚本变量
● You cannot bind the result of a dynamic SQL statement to a SQLScript variable.不能将动态SQL的结果赋值给SQL脚本变量
● You must be very careful to avoid SQL injection bugs that might harm the integrity or security of the database.有SQL注入漏洞风险
Note
You should avoid dynamic SQL wherever possible as it can have a negative impact on security or performance.因为性能与安全，尽量少使用动态SQL

6.7.1 EXEC

Syntax:
EXEC '<sql-statement>'
Description:
EXEC executes the SQL statement passed in a string argument.
Example:
You use dynamic SQL to insert a string into the message_box table.
v_sql1 = 'Third message from Dynamic SQL';

EXEC 'INSERT INTO message_box VALUES (''' || :v_sql1 || ''')';

6.7.2 EXECUTE IMMEDIATE

Syntax:
EXECUTEIMMEDIATE'<sql-statement>'
Description:
EXECUTE IMMEDIATE executes the SQL statement passed in a string argument. The results of queries executed with EXECUTE IMMEDIATE are appended to the procedures result iterator.

EXECUTE IMMEDIATE一般用来执行动态查询，让结果附加到存储过程迭代器中

Example:
You use dynamic SQL to delete the contents of table tab, insert a value and finally to retrieve all results in the table.
CREATETABLE tab (i int);

CREATEPROCEDURE proc_dynamic_result2(i int) AS

BEGIN

EXEC 'DELETE from tab';

EXEC 'INSERT INTO tab VALUES (' || :i || ')';

EXECUTEIMMEDIATE'SELECT * FROM tab ORDER BY i';

END;

6.7.3 APPLY_FILTER函数

该函数可以动态查询某个表（物理表或表变量），条件是动态的

Syntax
<variable_name> = APPLY_FILTER(<table_or_table_variable>,<filter_variable_name>);
Syntax Elements
<variable_name> ::= <identifier>
The variable where the result of the APPLY_FILTER function will be stored. <variable_name>应该是一个表变量，用来接收APPLY_FILTER函数返回的结果
<table_or_table_variable> ::= <table_name> | <table_variable>
You can use APPLY_FILTER with persistent tables(物理表) and table variables.
<table_name> :: = <identifier>
The name of the table(物理表) that is to be filtered.
<table_variable> ::= :<identifier>
The name of the table variable(表变量) to be filtered.
<filter_variable_name> ::= <string_literal>
The filter command to be applied.

Description
The APPLY_FILTER function applies a dynamic filter on a table or table variable. Logically it can be considered a partial dynamic sql statement. The advantage of the function is that you can assign it to a table variable and will not block sql – inlining. Despite this all other disadvantages of a full dynamic sql yields also for the APPLY_FILTER.

Examples
Example 1 - Apply a filter on a persistent table（在物理表上应用过滤器）
You create the following procedure
CREATEPROCEDURE GET_PROCEDURE_NAME (IN filter NVARCHAR(100),

OUT procedures table(SCHEMA_NAME NVARCHAR(256), PROCEDURE_NAME NVARCHAR(256))) AS

BEGIN

temp_procedures = APPLY_FILTER(SYS.PROCEDURES,:filter);-- SYS.PROCEDURES为物理表，APPLY_FILTER函数返回的是结果集。由于接收的表结果，所以temp_procedures可以不定义直接使用。Filter是传进来的条件串

procedures = SELECT SCHEMA_NAME, PROCEDURE_NAME FROM :temp_procedures;

END;

You call the procedure with two different filter variables.
CALL GET_PROCEDURE_NAME(' PROCEDURE_NAME like ''TREX%''', ?);

CALL GET_PROCEDURE_NAME(' SCHEMA_NAME = ''SYS''', ?);

Example 2 - Using a table variable（在表变量上应用过滤器）
CREATEPROCEDURE GET_PROCEDURE_NAME (IN filter NVARCHAR(100),

OUT procedures table(SCHEMA_NAME NVARCHAR(256), PROCEDURE_NAME NVARCHAR(256))) AS

BEGIN

temp_procedures = SELECT SCHEMA_NAME, PROCEDURE_NAME FROM SYS.PROCEDURES;

procedures = APPLY_FILTER(:temp_procedures,:filter);-- temp_procedures为表变量，这里即从表变量查询，查询条件为传进来的filter查询串

END;

CALL GET_PROCEDURE_NAME(' PROCEDURE_NAME like ''TREX%''', ?);

CALL GET_PROCEDURE_NAME(' SCHEMA_NAME = ''SYS''', ?);

结果与前面例子是一样的

Note
The following constructs are not supported in the filter string <filter_variable_name>:

● • sub-queries, for example: CALL GET_PROCEDURE_NAME('PROCEDURE_NAME in (SELECT object_name FROM SYS.OBJECTS)', ?); 过滤器中不支持子查询
● • fully-qualified column names, for example:CALL GET_PROCEDURE_NAME('PROCEDURE.PROCEDURE_NAME = ''DSO''', ?); 过滤器中不能使用full-qualified column names全限定的列名称（带表名的前缀列名）

6.8Exception Handling

Exception handling is a method for handling exception and completion结束 conditions in an SQLScript procedure.

6.8.1 DECLARE EXIT HANDLER

Syntax
<proc_handler>::= DECLARE EXIT HANDLER FOR <proc_condition_value_list> <proc_stmt>
Note
This is a syntax fragment from the CREATE PROCEDURE statement. For the full syntax see, CREATE PROCEDURE.

Description
The DECLARE EXIT HANDLER parameter allows you to define exception handlers to process exception conditions in your procedures. You can explicitly signal an exception and completion condition within your code using SIGNAL and RESIGNAL.

6.8.2 DECLARE CONDITION

Syntax
DECLARE <condition name> CONDITION [ FOR <sqlstatevalue> ]
Note
This is a syntax fragment from the CREATE PROCEDURE statement. For the full syntax see, CREATE PROCEDURE.

Description
You use the DECLARE CONDITION parameter(参数化) to name exception conditions, and optionally, their associated SQL state values.

6.8.3 SIGNAL and RESIGNAL

Syntax
SIGNAL <signal value> [<set signal information>]

RESIGNAL [<signal value>] [<set signal information>]

Note
This is a syntax fragment from the CREATE PROCEDURE statement. For the full syntax see, CREATE PROCEDURE.
Description
You use the SIGNAL and RESIGNAL directives in your code to trigger exception states.
You can use SIGNAL or RESIGNAL with specified error code in user-defined error code range. A user-defined exception can be handled by the handler declared in the procedure. Also it can be also handled by the caller which can be another procedure or client.

6.8.4 Exception Handling Examples

General exception handling通用异常捕获
General exception can be handled with exception handler declared at the beginning of statements which make an explicit or implicit signal exception.
CREATETABLE MYTAB (I INTEGERPRIMARYKEY);

CREATEPROCEDURE MYPROC AS

BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION -- 处理异常，SQLEXCEPTION能捕获所有异常，相当于Java中的Excepton？

SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;

INSERTINTO MYTAB VALUES (1);

INSERTINTO MYTAB VALUES (1); -- expected unique violation error: 301

-- will not be reached

END;

CALL MYPROC();

Error code exception handling通过异常码
An exception handler can be declared that catches exceptions with a specific error code numbers.
CREATETABLE MYTAB (I INTEGERPRIMARYKEY);

CREATEPROCEDURE MYPROC AS

BEGIN

--具体指定要抓取哪一种异常代码

DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 301 --301：违返唯一约束异常。如果要具体捕获某一种异常，则使用SQL_ERROR_CODE+异常码

SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;

INSERTINTO MYTAB VALUES (1);

INSERTINTO MYTAB VALUES (1); -- expected unique violation error: 301

END;

CREATETABLE MYTAB (I INTEGERPRIMARYKEY);

CREATEPROCEDURE MYPROC AS

BEGIN

DECLARE myVar INT;

DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 1299 --使用SELECT I INTO为某个量赋值时，如果没有值，则会抛出异常

BEGIN

SELECT 0 INTO myVar FROM DUMMY;

SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;

SELECT :myVar FROM DUMMY;

END;

SELECT I INTO myVar FROM MYTAB; --如果没有数据，这里会抛出 NO_DATA_FOUND exception

SELECT'NeverReached_noContinueOnErrorSemantics'FROM DUMMY;

END;

Conditional Exception Handling根据异常条件变量捕获
Exceptions can be declared using a CONDITION variable. The CONDITION can optionally be specified with an error code number.
CREATETABLE MYTAB (I INTEGERPRIMARYKEY);

CREATEPROCEDURE MYPROC AS

BEGIN

DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 301;--将异常代码定义成条件变量

DECLARE EXIT HANDLER FOR MYCOND --直接使用上面定义的异常条件变量

SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;

INSERTINTO MYTAB VALUES (1);

INSERTINTO MYTAB VALUES (1); -- expected unique violation error: 301

-- will not be reached

END;

Signal an exception手动抛出自定义异常
The SIGNAL statement can be used to explicitly raise an exception from within your procedures.
Note
The error code used must be within the user-defined range of 10000 to 19999.
CREATETABLE MYTAB (I INTEGERPRIMARYKEY);

CREATEPROCEDURE MYPROC AS

BEGIN

DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 10001;

--DECLARE EXIT HANDLER FOR MYCOND SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;

INSERTINTO MYTAB VALUES (1);

SIGNAL MYCOND SET MESSAGE_TEXT = 'my error';--手动抛出异常，如果去掉上面定义的异常处理块，则数据不会插入到表中，因为产生了异常并未捕获

-- will not be reached

END;

CALL MYPROC();

Resignal an exception重新抛出异常
The RESIGNAL statement raises an exception on the action statement in exception handler. If error code is not specified, RESIGNAL will throw the caught exception.
CREATETABLE MYTAB (I INTEGERPRIMARYKEY);

CREATEPROCEDURE MYPROC AS

BEGIN

DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 10001;

DECLARE EXIT HANDLER FOR MYCOND RESIGNAL;--捕获但不处理异常，捕获到后重新抛出，注：最后数据还是没有插入，因为异常未被处理

INSERTINTO MYTAB VALUES (1);

SIGNAL MYCOND SET MESSAGE_TEXT = 'my error';--抛异常

-- will not be reached

END;

CALL MYPROC();

Nested block exceptions.嵌套块异常
You can declare exception handlers for nested blocks.
CREATETABLE MYTAB (I INTEGERPRIMARYKEY);

CREATEPROCEDURE MYPROC AS

BEGIN

--内嵌异常层层向上抛出，最后从这里（最上层）抛出，重抛前设置错误消息。最终因为异常未被处理，所以数据最终还是没有被插入到表中

DECLARE EXIT HANDLER FOR SQLEXCEPTION RESIGNAL SET MESSAGE_TEXT = 'level 1';

BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION RESIGNAL SET MESSAGE_TEXT = 'level 2';

INSERTINTO MYTAB VALUES (1);

BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION RESIGNAL SET MESSAGE_TEXT = 'level 3';

INSERTINTO MYTAB VALUES (1); -- expected unique violation error: 301

-- will not be reached

END;

CALL MYPROC();

上面实例最终因为异常抛到最上层也没有处理，导致数据最终没有插入到表中，但如果MYPROC在另一过程中调用，并且处理重抛出的异常，则数据能插入到表中，如：

CREATEPROCEDURE MYPROC2 AS

BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;

CALL MYPROC();

END;

call MYPROC2();

6.9ARRAY

6.9.1 ARRAY CONSTRUCTOR数组构造

Syntax
ARRAY(<value_expression> [{, <value_expression>}...])
Syntax Elements
<value_expression> ::= <string_literal> | <number>
The array can contain strings or numbers.

Description
The ARRAY function returns an array whose elements are specified in the list of value expressions.

Examples
You define an integer array that contains the numbers 1,2 and 3.
DECLARE array_id INTEGER ARRAY := ARRAY(1, 2, 3);

6.9.2 DECLARE ARRAY-TYPED VARIABLE数组变量定义

Syntax
<array_name> <type> ARRAY [= <array_constructor>]
Syntax Elements
<array_variable> ::= <identifier>
The variable that will contain the array.
<type> ::= DATE | TIME| TIMESTAMP | SECONDDATE | TINYINT | SMALLINT | INTEGER | BIGINT | DECIMAL | SMALLDECIMAL | REAL | DOUBLE| VARCHAR | NVARCHAR | ALPHANUM | VARBINARY | CLOB | NCLOB |BLOB
The data type for the array elements.
<array_constructor> ::= ARRAY(<value_expression> [{, <value_expression>}...])
Defines the array elements. For more information, see ARRAY CONSTRUCTOR
Description
Declare an array variable whose element type is <type>, which represents one of the SQL types.
Currently only an unbounded ARRAY is supported with a maximum cardinality of 2^31. You cannot define a static-size for an array.

Examples
Example 1
You define an empty array of type INTEGER.
DECLARE array_int INTEGER ARRAY;--定义一个空的数组
Example 2
You define an INTEGER array with values 1,2 and 3.
DECLARE array_int INTEGER ARRAY := ARRAY(1, 2, 3);

6.9.3 SET AN ELEMENT OF AN ARRAY给数组赋值

Syntax
:<array_variable> [ <array_index> ] := <value_expression> 注：这里的中括号是数组的一部分，而非语法中的可选项

Syntax Elements
<array_variable> ::= <identifier>
The array to be operated upon.
<array_index> ::= <unsigned_integer>
The index of the element in the array to be modified. <array_index> can be any value from 1 to 2^31.
Note：The array index starts with the index 1。注：数组索引是从1开始的
<value_expression> ::= <string_literal> | <number>
The value to which the array element should be set.

Description
The array element specified by <array_index> can be set to <value_expression>.

Examples
You create an array with the values 1,2,3. You add 10 to the first element in the array.
DECLARE id Integer ARRAY := ARRAY(1, 2, 3);

id[1] := :id[1] + 10;

6.9.4 RETURN AN ELEMENT OF AN ARRAY获取数组元素

Syntax
<scalar_variable> := <array_variable> [ <array_index>]

Syntax Elements
<scalar_variable> :: = <identifier>
The variable where the array element will be assigned.
<array_variable> ::= <identifier>
The target array where the element is to be obtained from.
<array_index> ::= <unsigned_integer>
The index of the element to be returned. <array_index> can be any value from 1 to 2,147,483,646.

Description
The value of the array element specified by <array_index given_index> can be returned. The array element can be referenced in SQL expressions.

Example
You create and call the following procedure.
CREATEPROCEDURE ReturnElement (OUToutputINT) AS

BEGIN

DECLARE id INTEGER ARRAY := ARRAY(1, 2, 3);

DECLARE n INTEGER := 1;

output := :id[:n];

END;

call ReturnElement(?);

6.9.5 UNNEST函数

将某个（多个）数组转换为Table

Syntax
UNNEST(:<array_variable> [ {, :array_variable} ...] )[WITH ORDINALITY] [AS <return_table_specification>)]

Syntax Elements
<array_variable> ::= <identifier>
The array to be operated upon.
WITH ORDINALITY
Specifies that an ordinal column will be appended to the returned table（在合并结果表格中加一列序列表用来在存储元素在数组中的顺序，即索引？）. When you use this, you must explicitly specify an alias for the ordinal column（如果指定了这个选项，则必须明确指定这个序列列名）. For more information, see Example 2 where "SEQ" is specified as the alias.
<return_table_specification> ::= (<column_name> [ {, column_name}… ])
The column names of the returned table 指定返回的结果表格每列的列名.
<column_name> ::= <identifier>
The name of a column in the returned table.

Description
The UNNEST function converts an array into a table.UNNEST returns a table including a row for each element of the array specified. If there are multiple arrays given, the number of rows will be equal to the largest cardinality among the cardinalities of the arrays. In the returned table, the cells that are not corresponding to the elements of the arrays are filled with NULL values.
Note
The UNNEST function cannot be referenced directly in FROM clause of a SELECT statement.

Examples
Example 1
You use UNNEST to obtain the values of an ARRAY id and name in which the cardinality differs.
CREATEPROCEDURE ARRAY_UNNEST_SIMPLE()

LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS

BEGIN

DECLARE id INTEGER ARRAY;

DECLARE name VARCHAR(10) ARRAY;

id[1] := 1;

id[2] := 2;

name[1] := 'name1';

name[2] := 'name2';

name[3] := 'name3';

name[5] := 'name5';

rst = UNNEST(:id, :name) AS ("ID", "NAME");

SELECT * FROM :rst;

END;

CALL ARRAY_UNNEST_SIMPLE();

如果不指定返回表格中的列名：

rst = UNNEST(:id, :name) ;

结果为：

Example 2
You use UNNEST with the WITH ORDINALITY directive to generate a sequence column along with the results set .
CREATEPROCEDURE ARRAY_UNNEST()

LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS

BEGIN

DECLARE amount INTEGER ARRAY := ARRAY(10, 20);

rst = UNNEST( :amount) WITH ORDINALITY AS ( "AMOUNT", "SEQ");

select SEQ, AMOUNT from :rst;

END;

6.9.6 ARRAY_AGG函数

将表中的列转换为数组

Syntax
ARRAY_AGG (:<table_variable>.<column_name> [<order_by_clause>])

Syntax Elements
<table_variable> ::= <identifier>
The name of the table variable to be converted.
<column_name>::= <identifier>
The name of the column, within the table variable, to be converted.
<order_by_clause> ::= ORDER BY { <order_by_expression>, ... }
The ORDER BY clause is used to sort records by expressions or positions.
<order_by_expression> ::= <expression> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
Specifies the ordering of data.
ASC | DESC
ASC sorts records in ascending order. DESC sorts records in descending order. The default is ASC.
NULLS FIRST | NULLS LAST
Specifies where in the results set NULL values should appear(设定NULL如何参与排序). By default for ascending ordering NULL values are returned first, and for descending they are returned last. You can override this behavior using NULLS FIRST or NULLS LAST to explicitly specify NULL value ordering.

Description
The ARRAY_AGG function converts a column of a table into an array.
Note
ARRAY_AGG function does not support using value expressions instead of table variables.

Examples
You create the following table and procedure.
CREATETABLE tab1 (a INT, b INT, c INT);

INSERTINTO tab1 VALUES (1, 4, 1);

INSERTINTO tab1 VALUES (2, 3, 2);

CREATEPROCEDURE ARRAY_AGG_TEST()

LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS

BEGIN

DECLARE id Integer ARRAY;

tab = SELECT * FROM tab1;

id := ARRAY_AGG(:tab.a ORDERBY c asc , b DESC);--将表中的列转换为数组

rst = UNNEST(:id);--将数组转换为表格

SELECT * FROM :rst;

END;

CALL ARRAY_AGG_TEST();

6.9.7 TRIM_ARRAY函数

从尾部删除指定个数的数组元素，并返回删除过的新数组

Syntax
TRIM_ARRAY(:<array_variable>, <trim_quantity>)
Syntax Elements
<array_variable> ::= <identifier>
The array to be operated upon.
<trim_quantity> ::= <unsigned_integer>
The number of elements to be removed.要删除多少个（从尾部删）
Description
The TRIM_ARRAY function removes elements from the end of an array. TRIM_ARRAY returns a new array with a <trim_quantity> number of elements removed from the end of the array, <array_variable>.

Examples
You create the following procedure.
CREATEPROCEDURE ARRAY_TRIM()

LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS

BEGIN

DECLARE array_id Integer ARRAY := ARRAY(0, 1, 2);

array_id := TRIM_ARRAY(:array_id, 1);

rst1 = UNNEST(:array_id) as ("ID");

select * from :rst1 orderby"ID";

END;

6.9.8 CARDINALITY函数

返回数组的长度

Syntax
CARDINALITY(:<array_variable>)
Syntax Elements
<array_variable> ::= <identifier>
The array to be operated upon.

Description
The CARDINALITY function returns the number of elements in the array <array_variable>. It returns N (>= 0) if the index of the N-th element is the largest among the indices.

Example
Example 1
CREATEPROCEDURE CARDINALITY_1() AS

BEGIN

DECLARE array_id Integer ARRAY := ARRAY(1, 2, 3);

DECLARE n Integer;

n := CARDINALITY(:array_id);

select :n as card from dummy;

END;

CALL CARDINALITY_1(); -- 3

Example 2
CREATEPROCEDURE CARDINALITY_2() AS

BEGIN

DECLARE array_id Integer ARRAY;

DECLARE n Integer;

n := CARDINALITY(:array_id);

select :n as card from dummy;

END;

CALL CARDINALITY_2();-- 0

Example 3
CREATEPROCEDURE CARDINALITY_3() AS

BEGIN

DECLARE array_id Integer ARRAY;

DECLARE n Integer;

array_id[20] := NULL;

n := CARDINALITY(:array_id);

select :n as card from dummy;

END;

CALL CARDINALITY_3();-- 20

6.9.9 CONCATENATE TWO ARRAYS

合并数组，将两个数组合并起来

Syntax
:<array_variable_left> || :<array_variable_right>
or
CONCAT(<array_variable_left> , <array_variable_right> )
Syntax Elements
<array_variable_left> ::= <identifier>
The first array to be concatenated.
<array_variable_right> ::= <identifier>
The second array to be concatenated.

Description
The concat function concatenates two arrays. It returns the new array that contains a concatenation of <array_variable_left> and <array_variable_right>.

Examples
CREATEPROCEDURE ARRAY_COMPLEX_CONCAT3()

LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS

BEGIN

DECLARE id1 INTEGER ARRAY;

DECLARE id2 INTEGER ARRAY;

DECLARE id3 INTEGER ARRAY;

DECLARE id4 INTEGER ARRAY;

DECLARE id5 INTEGER ARRAY;

DECLARE card INTEGER ARRAY;

id1[1] := 0;

id2[1] := 1;

id3 := CONCAT(:id1, :id2);-- 0,1

id4 := :id1 || :id2;-- 0,1

rst = UNNEST(:id3) WITH ORDINALITY AS ("id", "seq");-- 0,1

id5 := :id4 || ARRAY_AGG(:rst."id"ORDERBY"seq");-- 0,1,0,1

rst1 = UNNEST(:id5 || CONCAT(:id1, :id2) || CONCAT(CONCAT(:id1, :id2),CONCAT(:id1, :id2))) WITH ORDINALITY AS ("id", "seq");

SELECT"seq", "id"FROM :rst1 ORDERBY"seq";

END;

CALL ARRAY_COMPLEX_CONCAT3();

7 CE Founction(Calculation Engine Plan Operators)

CE Function性能比直接写SQL要好，因为CE Funciton是经过引擎预处理了的。Ce Function 与SQL不要混用，性能会更低

Recommendation建议
SAP recommends that you use SQL rather than Calculation Engine Plan Operators with SQLScript（推荐使用SQL方式，而不是SQLScript，如CE Founction）.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.

Table 18: Overview: CE_* Function 与 SQL对应关系

CE Operator CE函数

CE Syntax 用法

SQL Equivalent等效SQL

数据访问

Data Source Access operators

CE_COLUMN_TABLE

CE_COLUMN_TABLE(<table_name>[,<attributes>])

<table_name> ::= [<schema_name>.]<identifier>

<attributes> ::= '[' <attrib_name>[{, <attrib_name> }…] ']' 注：语法中的单引号引起来的中括号表示不是语法可先部分，而是语句中需要使用的中括号

从column table表（行存储表与视图都不可以）中Select数据

注：不能使用AS改名Select出来的列

标示名（表名、列名）需要使用双引号引起来，名称是大写还是小写，则要看你在使用CREATE TABLE语句创建表时，列名是否使用了双引号引起来，如果没有使用双引号引起来，则创建的表列名会自动转换为大写；如果使用了双引号，则列名会大小写敏感，即你输入的是什么，创建的表的列名就是什么，这就可能即有大写也有小写，所以在SQL或CE Founction中使用该表时，要注意原始表列名是否有大小写之分（因为使用了双引号将列名引起来了）。总之，使用双引号引起来就会区分大小写的，大小写敏感

该函数一般从列式存储表中Select数据

droptable T_COLUMN;

CREATECOLUMNTABLE"SYSTEM"."T_COLUMN" ("ID"INTEGER CS_INT,

"NAME"VARCHAR(30),

"PAYMENT"INTEGER CS_INT) ;

insertinto"SYSTEM"."T_COLUMN"values(1,'a',10);

insertinto"SYSTEM"."T_COLUMN"values(2,'b',20);

insertinto"SYSTEM"."T_COLUMN"values(3,'c',30);

droptable T_ROW;

CREATETABLE"SYSTEM"."T_ROW" ("ID"INTEGER CS_INT,

"NAME"VARCHAR(30),

"PAYMENT"INTEGER CS_INT) ;

insertinto"SYSTEM"."T_ROW"values(1,'a',10);

insertinto"SYSTEM"."T_ROW"values(2,'b',20);

insertinto"SYSTEM"."T_ROW"values(3,'c',30);

DROPPROCEDURE Proc_test;

CREATEPROCEDURE Proc_test(IN id INT,IN name varchar(3),OUT o1 "SYSTEM"."T_COLUMN")

LANGUAGE SQLSCRIPT

READS SQL DATA WITH RESULT VIEW Proc_View AS

BEGIN

--下面是从列式表里读取。列名也可以不使用双引号引起来，但这意味着表里列名全是大写

outt = CE_COLUMN_TABLE("SYSTEM"."T_COLUMN",["ID",naMe,pAyment]);

--注：下面语句是错的，因为T_ROW为行式存储表，而CE_COLUMN_TABLE只能用于列式存储，并且也不能用于其它视图（表视图——即通过Create View语句创建的视图、属性分析计算视图）

--outt = CE_COLUMN_TABLE("SYSTEM"."T_ROW",[id,name,payment]);

--下面也是错误的，因为该函数Select列表不能使用AS

--outt = CE_COLUMN_TABLE("SYSTEM"."T_COLUMN",["ID" as "ID",name,payment]);

--注：过滤条件一定要使用单引号整体引起来；列名一定要使用双引号引起来；

--如果是字符串，则要使用单引号引起来，因为外层有单引号，所以里面要使用两个单引号代表一个单引号

--o1 = CE_PROJECTION(:outt,[id,name,payment],'"ID" = :id AND "NAME" = '':name''');

--上面这句等效于下面两句

o1 = CE_PROJECTION(:outt,[id,name,payment],'"ID" = :id');

o1 = CE_PROJECTION(:o1,[id,name,payment],'"NAME" = '':name''');

--或者参数name不使用单引号引起来，而是在查询视图时将单引号一并传进来也可以，如下

--o1 = CE_PROJECTION(:outt,[id,name,payment],'"ID" = :id AND "NAME" = :name');

END;

--测试

select * from Proc_View with parameters ('placeholder' = ('$$id$$' ,'2' ),'placeholder' = ('$$name$$' ,'b' ));

--查询时参数Name一并传入

--select * from Proc_View with parameters ('placeholder' = ('$$id$$' ,'2'),'placeholder' = ('$$name$$' ,'''b''' ));

SELECT [<attributes>] FROM<table_name>

CE_JOIN_VIEW

CE_JOIN_VIEW(<column_view_name>[,<attributes>])

<attributes> ::= '[' <attrib_name>[{, <attrib_name> }…] ']' 注：语法中的单引号引起来的中括号表示不是语法可先部分，而是语句中需要使用的中括号
<attrib_name> ::= <string_literal> [AS <column_alias>] 注：这个AS好像还是不支持（测试环境为SPS09版本， SPS10版本的HANA应该支持！！？？）

该函数一般从属性视图中Select数据

outt = CE_JOIN_VIEW("PRODUCT_SALES", ["PRODUCT_KEY", "PRODUCT_TEXT", "SALES"]);

经测试，该函数也可以作用于列式表（但行式存储表一样也不支持），另外，还可以作用于属性视图、分析视图、计算视图，但也不能用于表视图（使用create view语句创建的，注：没有create column view语句，即列视图是不能使用语句来创建，它是由属性视图、分析视图、计算视图在激活时自动生成的，另外，在使用CREATE PROCEDURE...WITH RESULT VIEW...语句创建带返回视图的存储过程也是会自动生成Column View 列视图，并且会放在Column Views文件夹下：。而通过Create View语句创建的表视图则会放在Views文件夹下：）。虽然支持列式表、以及三大视图，但推荐使用该函数只用于Join View（即属性视图，因为一般属性视图是将多个基础主数据表关联起来的视图，所以又叫Join View）上，因为不同对象都有相应的CE Function取数

droptable T_COLUMN;

CREATECOLUMNTABLE"SYSTEM"."T_COLUMN" ("ID"INTEGER CS_INT,

"NAME"VARCHAR(30),

"PAYMENT"INTEGER CS_INT) ;

insertinto"SYSTEM"."T_COLUMN"values(1,'a',10);

insertinto"SYSTEM"."T_COLUMN"values(2,'b',20);

insertinto"SYSTEM"."T_COLUMN"values(3,'c',30);

DROPPROCEDURE Proc_test;

CREATEPROCEDURE Proc_test(IN id INT,IN name varchar(3),OUT o1 "SYSTEM"."T_COLUMN")

LANGUAGE SQLSCRIPT

READS SQL DATA WITH RESULT VIEW Proc_View AS

BEGIN

--下面是从列式表里读取。列名也可以不使用双引号引起来，但这意味着表里列名全是大写

outt = CE_JOIN_VIEW("_SYS_BIC"."demo/ATTR_T_COLUMN",["ID",naMe,pAyment]);

o1 = CE_PROJECTION(:outt,[id,name,payment],'"ID" = :id AND "NAME" = '':name''');

END;

--测试

select * from Proc_View with parameters

('placeholder' = ('$$id$$' ,'2' ),'placeholder' = ('$$name$$' ,'b' ));

SELECT [<attributes>] FROM <column_view_name>

outt = SELECT product_key,product_text, sales FROM product_sales;

CE_OLAP_VIEW

CE_OLAP_VIEW (<olap_view_name>,'['<attributes>'] ')

<attributes> ::= <aggregate_exp> [{, <dimension>}…] [{, <aggregate_exp>}…] attributes是Select选择列表，可以是维度dimension，也可以是带聚合函数的关键值列aggregate_exp

<aggregate_exp> ::= <aggregate_func>(<aggregate_column> [AS <column_alias>]) 注：AS只能用在聚合列上，非聚合列上好像不能使用的

<aggregate_func> ::= COUNT | SUM | MIN | MAX

<dimension> ::= <string_literal> 不能用别名（测试环境为SPS09版本， SPS10版本的HANA是否支持！！？？）

Note：you must have at least one <aggregation_exp> in the attributes.即必须要有一个<aggregation_exp>（从后面的测试好像可以省略，使用视图中默认指定的聚合）

Supported aggregation functions are:支持以下聚合函数
● count("column")
● sum("column")
● min("column")
● max("column")
● use sum("column") / count("column") to compute the average 计算平均值

该函数一般从OLAP view，即Analytical View分析视图中Select数据

outt = CE_OLAP_VIEW("OLAP_view", ["DIM1", SUM("KF")]);

droptable T_COLUMN;

CREATECOLUMNTABLE"SYSTEM"."T_COLUMN" ("ID"INTEGER CS_INT,

"NAME"VARCHAR(30),

"PAYMENT"INTEGER CS_INT) ;

insertinto"SYSTEM"."T_COLUMN"values(1,'a',10);

insertinto"SYSTEM"."T_COLUMN"values(2,'b',20);

insertinto"SYSTEM"."T_COLUMN"values(3,'c',30);

insertinto"SYSTEM"."T_COLUMN"values(1,'a',10);

insertinto"SYSTEM"."T_COLUMN"values(5,'b',20);

insertinto"SYSTEM"."T_COLUMN"values(3,'c',30);

DROPPROCEDURE Proc_test;

CREATEPROCEDURE Proc_test(OUT o1 "SYSTEM"."T_COLUMN")

LANGUAGE SQLSCRIPT

READS SQL DATA WITH RESULT VIEW Proc_View AS

BEGIN

--注：中括号里列表为要Select出的字段以及聚合运算，除开聚合列外，其他的列都会被作为Group By。聚合函数里的列为需要汇总的关键指标

o1 = CE_OLAP_VIEW("_SYS_BIC"."demo/ANAL_T_COLUMN",["ID","NAME",sum("PAYMENT") as PAYMENT ]);

--注：可以省略SUM聚合函数，因为分析视图中设置了SUM聚合，所以这里如果没有指定时，会使默认使用视图中指定的聚合

--o1 = CE_OLAP_VIEW("_SYS_BIC"."demo/ANAL_T_COLUMN",["ID","NAME","PAYMENT"]);

END;

--测试

select * from Proc_View;

聚合函数可以使用其他支持的函数，不一定要使用视图中指定的聚合函数：

o1 = CE_OLAP_VIEW("_SYS_BIC"."demo/ANAL_T_COLUMN",["ID","NAME",MIN("PAYMENT")]);

SELECT [<attributes>] FROM <olap_view_name>

outt = select dim1, SUM(kf) FROM OLAP_view GROUP BY dim1;

CE_CALC_VIEW

CE_CALC_VIEW(<calc_view_name>,[<attributes>])

<attributes> ::= ‘[’ <attrib_name>[{, <attrib_name> }…] ‘]’ 注：也是不能使用AS的（测试环境为SPS09版本， SPS10版本的HANA是否支持！！？？）
<attrib_name> ::= <string_literal>

该函数一般从calculation view计算视图中Select数据

outt = CE_CALC_VIEW("TESTCECTABLE", ["CID", "CNAME"]);

droptable T_COLUMN;

CREATECOLUMNTABLE"SYSTEM"."T_COLUMN" ("ID"INTEGER CS_INT,

"NAME"VARCHAR(30),

"PAYMENT"INTEGER CS_INT) ;

insertinto"SYSTEM"."T_COLUMN"values(1,'a',10);

insertinto"SYSTEM"."T_COLUMN"values(2,'b',20);

insertinto"SYSTEM"."T_COLUMN"values(3,'c',30);

insertinto"SYSTEM"."T_COLUMN"values(1,'a',10);

insertinto"SYSTEM"."T_COLUMN"values(5,'b',20);

insertinto"SYSTEM"."T_COLUMN"values(3,'c',30);

DROPPROCEDURE Proc_test;

CREATEPROCEDURE Proc_test(OUT o1 "SYSTEM"."T_COLUMN")

LANGUAGE SQLSCRIPT

READS SQL DATA WITH RESULT VIEW Proc_View AS

BEGIN

o1 = CE_CALC_VIEW("_SYS_BIC"."demo/CAL_T_COLUMN",["ID","NAME","PAYMENT"]);

--注：不能使用聚合函数,也就是说如果计算视图里使用了Aggregation，则会根据主里的设定的聚合返回汇总值

--CE_CALC_VIEW("_SYS_BIC"."demo/CAL_T_COLUMN",["ID","NAME",SUM("PAYMENT")]);

END;

--测试

select * from Proc_View;

SELECT [<attributes>] FROM <calc_view_name>

outt = SELECT cid, cname FROM "TESTCECTABLE";

Relational operators

关联操作

CE_JOIN

CE_JOIN(<left_table>,<right_table>,<join_attributes>[<projection_list>])

<join_attributes> ::= '[' <join_attrib>[{, <join_attrib> }…] ']'
<join_attrib> ::= <string_literal>
Specifies a list of join attributes. Since CE_JOIN requires equal attribute names, one attribute name per pair of join attributes is sufficient. The list must at least have one element. 指定联接属性，并且左右表的联接属性名是相同的（只能通过两表相同列名进行联接啊，如果两表联接字段列名不同，如何联接啊？），并且必须要有一个联接属性
<projection_list> ::= '[' {, <attrib_name> }… ']'
<attrib_name> ::= <string_literal>注：也是不能使用AS的（测试环境为SPS09版本， SPS10版本的HANA是否支持！！？？）

Specifies a projection list for the attributes that should be in the resulting table.

Note
If the optional projection list is present, it must at least contain the join attributes.

内联

ot_pubs_books1 = CE_JOIN (:lt_pubs, :it_books,["PUBLISHER"]);

注：<projection_list>可以省略，如果省略的话，会将两个表的所有字段都返回出来（如果这时除了相同联接属性列外，其他非联接属性列名相同的话，会有问题的，因为这个返回表类型你不可能在里面创建两个相同名字的列啊），并且返回列表的顺序是左表+右表，并表字段是按表定义时顺序返回。<projection_list>这里面列出的属性除了联接列名两表必须相同外，其他列出的列名一定不能在两个表里都有啊（那如果两个表有相同的列名怎么办啊？）

CE_JOIN联接的只能是表内存变量，而不能直接是某个物理表？

droptable t_column;

CREATECOLUMNTABLE t_column(

id INTEGERprimarykey,

name1 VARCHAR(30),

payment INTEGER) ;

insertinto t_column values(1,'a',10);

insertinto t_column values(2,'b',20);

insertinto t_column values(3,'c',30);

droptable t_row;

CREATETABLE t_row (id INTEGERprimarykey,

name2 VARCHAR(30),

payment INTEGER) ;

insertinto t_row values(1,'a',10);

insertinto t_row values(2,'b',20);

droptype column_row;

createtype column_row astable(

id INTEGER,

name2 VARCHAR(30)

);

DROPPROCEDURE Proc_test;

CREATEPROCEDURE Proc_test(INcolumn t_column,Inrow t_row, out column_row column_row)

LANGUAGE SQLSCRIPT

READS SQL DATA WITH RESULT VIEW Proc_View AS

BEGIN

--选择列表里列出的列名，一定要有联接属性列在里面，这里为"ID"，除联接属性列外，其他列名必须是两个

--表里不同的列名，否则出错，请看后面贴图

column_row = ce_join(:column,:row,["ID"],["ID","NAME2"]);

--注：好像不能直接使用物理表，要借助于表类型变量才行，所以下面语句编译会出错

--column_row = ce_join("T_COLUMN","T_ROW",["ID"],["ID","NAME2"]);

--上面这个语句编译会出错，所以只能通过下面这种方式先将物理表转换为表变量再进行联接

--tt_COLUMN = CE_COLUMN_TABLE(t_column);

--tt_ROW = select * from t_row; --由于表t_row为行式存储，所以不能直接使用CE_COLUMN_TABLE函数

--column_row = ce_join(:Tt_COLUMN,:Tt_ROW,["ID"],["ID","NAME2"]);

END;

select * from Proc_View with parameters ('placeholder' = ('$$column$$' ,'system.t_column' ),

'placeholder' = ('$$row$$' ,'t_row' ));

如果两个表的非联接属性有相同的，则只能如下这样做：

droptable t_column;

CREATECOLUMNTABLE t_column(

id INTEGERprimarykey,

name VARCHAR(30),

payment INTEGER) ;

insertinto t_column values(1,'a',10);

insertinto t_column values(2,'b',20);

insertinto t_column values(3,'c',30);

droptable t_row;

CREATETABLE t_row (id INTEGERprimarykey,

name VARCHAR(30),

payment INTEGER) ;

insertinto t_row values(1,'d',40);

insertinto t_row values(2,'e',50);

droptype column_row;

createtype column_row astable(

id INTEGERprimarykey,

name VARCHAR(30),

payment INTEGER

);

DROPPROCEDURE Proc_test;

CREATEPROCEDURE Proc_test(INcolumn t_column,Inrow t_row, out column_row column_row)

LANGUAGE SQLSCRIPT

READS SQL DATA WITH RESULT VIEW Proc_View AS

BEGIN

--使用AS将相同的不需要列出的列名重命名

tmp_row = CE_PROJECTION(:row,[id,name as name1,payment as payment1]);

--上语相当于下面语句

--tmp_row = select id,name as name1,payment as payment1 from t_row;

--这样NAME和payment为t_column表列

column_row = ce_join(:column,:tmp_row,["ID"],["ID","NAME",payment]);

END;

select * from Proc_View with parameters ('placeholder' = ('$$column$$' ,'system.t_column' ),

'placeholder' = ('$$row$$' ,'t_row' ));

如果修改成下面样子，则会取T_ROW表里的数：

createtype column_row astable(

id INTEGERprimarykey,

name1 VARCHAR(30),

payment1 INTEGER

);

column_row = ce_join(:column,:tmp_row,["ID"],["ID","NAME1",payment1]);

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>])

左联，与CE_JOIN一样，非联接列不能相同

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>])

右联，与CE_JOIN一样，非联接列不能相同

SELECT [<projection_list>] FROM <left_table> RIGHT OUTER JOIN <right_table> ON <join_attributes>

CE_PROJECTION

CE_PROJECTION(<table_variable>,<projection_list>[,<filter>])

<table_variable>：为内表变量，不能直接是物理表

<projection_list> ::= [ <attrib_name>[{, <attrib_name> }…] ] 选择列还可以是计算表达式
<attrib_name> ::= <string_literal> [AS <column_alias>] 投影可以重命名列名
<filter>：请参考CE_CALC中的<filter>

投影

ot_books1 = CE_PROJECTION (:it_books,["TITLE","PRICE", "CRCY" AS "CURRENCY"], '"PRICE" > 50');

CE_PROJECTION只能从内表变量中取数，不能直接作用于物理表，如果是物理表，则可以通过Select语句、CE_COLUMN_TABLE（只支持列式存储的物理表，如果是行式的，则只能通过Select成内表变量或者是通过存储过程参数传递进来后转换成内表变量在使用）、存储过程输入参数传递进来的物理表（实则在传递过程中已转换为内表变量）

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>)

与SQL里面的 UNION ALL语句相当，要求两个内表变量的列名相同（类型好像可以不同）

ot_all_books1 = CE_UNION_ALL (:lt_books, :it_audiobooks);

droptable t_column;

CREATECOLUMNTABLE t_column(

id INTEGERprimarykey,

name VARCHAR(30),

payment INTEGER) ;

insertinto t_column values(1,'a',10);

insertinto t_column values(2,'b',20);

insertinto t_column values(3,'c',30);

droptable t_row;

CREATETABLE t_row (id INTEGERprimarykey,

name VARCHAR(30),

payment INTEGER) ;

insertinto t_row values(1,'a',10);

insertinto t_row values(2,'b',20);

droptype column_row;

createtype column_row astable(

id INTEGER,

name VARCHAR(30),

payment INTEGER);

DROPPROCEDURE Proc_test;

CREATEPROCEDURE Proc_test(INcolumn t_column,Inrow t_row, out column_row column_row)

LANGUAGE SQLSCRIPT

READS SQL DATA WITH RESULT VIEW Proc_View AS

BEGIN

column_row = CE_UNION_ALL(:column,:row);

END;

select * from Proc_View with parameters ('placeholder' = ('$$column$$' ,'system.t_column' ),

'placeholder' = ('$$row$$' ,'t_row' ));

SELECT * FROM <table_variable1> UNION ALL SELECT * FROM <table_variable2>

ot_all_books2 = SELECT * FROM :lt_books UNION ALL SELECT * FROM :it_audiobooks;

CE_AGGREGATION

CE_AGGREGATION(<table_variable>,<aggregate_list>[,<group_columns>])

<table_variable>：也是只能针对内表变量进行聚合，不能直接是物理表

NOTE：CE_AGGREGATION cannot handle tables directly as input.不能支持直接是物理表

<aggregate_list> ::= '['<aggregate_exp>[{, <aggregate_exp>}] ']' 指定聚合列表，如 [SUM ("A"), MAX("B")]

<aggregate_exp> ::= <aggregate_func>(<aggregate_column>[AS <column_alias>]) 聚合列支持重命名，如果没有重命名，则使用原聚合列名，如[SUM("A"),MAX("B")]，将会返回 A 与 B两列的结构列表

<aggregate_func> ::= COUNT | SUM | MIN | MAX

支持以下聚合函数:

count("column")

sum("column")

min("column")

max("column")

使用 sum("column") / count("column") 计算平均值

<group_columns> ::= '['<group_column_name> [{,<group_column_name>}...]']'

根据哪些列进行分组，可以省略，如果省略，则会将所有数据进行汇总

聚合

ot_books1 = CE_AGGREGATION (:it_books, [COUNT ("PUBLISHER") AS "CNT"], ["YEAR"]);

count(*)：包含null值

count(字段)：不计算null值

droptable t_column;

CREATECOLUMNTABLE t_column(

id INTEGERprimarykey,

name VARCHAR(30),

payment INTEGER) ;

insertinto t_column values(1,'a',10);

insertinto t_column values(2,'b',20);

insertinto t_column values(3,'c',30);

droptable t_row;

CREATETABLE t_row (id INTEGERprimarykey,

name VARCHAR(30),

payment INTEGER) ;

insertinto t_row values(1,'d',40);

insertinto t_row values(2,'e',50);

droptype column_row;

createtype column_row astable(

countINTEGER,

name1 VARCHAR(30),

payment1 INTEGER

);

DROPPROCEDURE Proc_test;

CREATEPROCEDURE Proc_test(INcolumn t_column,Inrow t_row, out column_row column_row)

LANGUAGE SQLSCRIPT

READS SQL DATA WITH RESULT VIEW Proc_View AS

BEGIN

tmp_row = CE_PROJECTION(:row,[id,name as name1,payment as payment1]);

tmp_column_row = CE_LEFT_OUTER_JOIN(:column,:tmp_row,["ID"],["ID","NAME1",payment1]);

--count(name1)不包括NULL。返回的结构按照统计列表+Group列表顺序返回

column_row = CE_AGGREGATION(:tmp_column_row,[count(name1) ascount],["NAME1",payment1]);

--count(*)包括NULL

--column_row = CE_AGGREGATION(:tmp_column_row,[count(*) as count],["NAME1",payment1]);

END;

select * from Proc_View with parameters ('placeholder' = ('$$column$$' ,'system.t_column' ),

'placeholder' = ('$$row$$' ,'t_row' ));

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>)

对某个表达式进行计算，有点像Javascript中的eval()函数用来动态执行脚本，具体支持哪些函数，请参数后面表格

计算表达式运算

TEMP = CE_PROJECTION(:table_var, ["ID" AS "KEY", CE_CALC('rownum()', INTEGER) AS "T_ID"] );

droptable t_row;

CREATETABLE t_row (id INTEGERprimarykey,

name VARCHAR(30),

payment INTEGER) ;

insertinto t_row values(4,'d',40);

insertinto t_row values(5,'e',50);

droptype column_row;

createtype column_row astable(

rownum INTEGER,

name VARCHAR(30),

payment INTEGER);

DROPPROCEDURE Proc_test;

CREATEPROCEDURE Proc_test(Inrow t_row, out column_row column_row)

LANGUAGE SQLSCRIPT

READS SQL DATA WITH RESULT VIEW Proc_View AS

BEGIN

column_row = CE_PROJECTION(:row,[ CE_CALC('rownum()', INTEGER) AS rownum,name,payment]);

END;

select * from Proc_View with parameters

('placeholder' = ('$$row$$' ,'t_row' ));

PRE_ORDER_PROJ_2 = CE_PROJECTION(:PRE_ORDER_TAB,

["DOC_NUMBER",

"/BIC/ZBSTDK_E",

CE_CALC('TO_DATE("/BIC/ZBSTDK_E")',DATE) AS"BSTDK_E",

"SUBTOTAL_2"AS"ORDER_SUM","DOC_CURRCY" ],

'"DOC_NUMBER" = '':PRM_ORDER''');

SQL Function：

TEMP = SELECT "ID" AS "KEY", ROW_NUMBER() OVER () AS "T_ID" FROM :table_var

Special operators

特定操作

CE_CONVERSION

CE_CONVERSION(<table_variable>,<conversion_params>,[<rename_clause>])

单位转换

在转换前需要配置，具体配置参数参考后面表格

下面示例是金额转换

--Start creating the input table for the procedure CE_CONVERSION

PRICES = select

id as id,

in_amount as in_amount, --金额

source_unit as source_unit, --源货币单位

target_unit as target_unit, --目标货币单位

ref_date as ref_date --汇率

from :it_conv_prices;

--Here we call the CE_CONVERSION method and specifying that the result should be stored in the variable lt_uom_std_conv

lt_uom_std_conv = CE_CONVERSION(:PRICES,

[family = 'currency', --货币转换

method = 'ERP',

client = '900',

conversion_type = 'M',

steps = 'shift,convert,shift_back',

target_unit_column = target_unit,

source_unit_column = source_unit,

reference_date_column = ref_date],

[ in_amount AS out_amount ] );

--The select statement for the output table is an inner join using id.

et_amount = select

lt.id as id,

dm.in_amount as in_amount,

lt.out_amount as out_amount,

dm.source_unit as source_unit,

dm.target_unit as target_unit,

dm.ref_Date as ref_date

from :lt_uom_std_conv as lt

innerjoin :it_conv_prices as dm on dm.id=lt.id;

SQL-Function：
CONVERT_CURRENCY

CE_VERTICAL_UNION

CE_VERTICAL_UNION(<var_table>, <projection_list> [{,<var_table>,<projection_list>}...])

<var_table> ::= :<identifier> 只能针对内表这是进行操作
<projection_list> ::= ‘[’ <attrib_name>[{, <attrib_name> }…] ‘]’
<attrib_name> ::= <string_literal> [AS <column_alias>] 可以使用AS重命名选择列名

垂直合并

这个是垂直合并，即将多个列拼合成一个表，是一列列的拼接起来的；而不是CE_UNION_ALL那样水平合并，是一行行合并的

droptable t_column;

CREATECOLUMNTABLE t_column(

id INTEGERprimarykey,

name VARCHAR(30),

payment INTEGER) ;

insertinto t_column values(1,'a',10);

insertinto t_column values(2,'b',20);

insertinto t_column values(3,'c',30);

droptable t_row;

CREATETABLE t_row (id INTEGERprimarykey,

name VARCHAR(30),

payment INTEGER) ;

insertinto t_row values(4,'d',40);

insertinto t_row values(5,'e',50);

droptype column_row;

createtype column_row astable(

id INTEGER,

name VARCHAR(30),

payment INTEGER);

DROPPROCEDURE Proc_test;

CREATEPROCEDURE Proc_test(INcolumn t_column,Inrow t_row, out column_row column_row)

LANGUAGE SQLSCRIPT

READS SQL DATA WITH RESULT VIEW Proc_View AS

BEGIN

column_row = CE_VERTICAL_UNION(:column,[id],:row,[name as name,payment]);

END;

select * from Proc_View with parameters

('placeholder' = ('$$column$$' ,'system.t_column' ),

'placeholder' = ('$$row$$' ,'t_row' ));

TRACE

TRACE(<var_input>)
Syntax elements:
<var_input> ::= :<identifier>
Identifies the SQLScript variable to be traced.

Description:
The TRACE operator is used to debug SQLScript procedures. It traces the tabular data passed as its argument into a local temporary table and returns its input unmodified. The names of the temporary tables can be retrieved from the SYS.SQLSCRIPT_TRACE monitoring view. See SQLSCRIPT_TRACE below.

Example:
You trace the content of variable input to a local temporary table.
out = TRACE(:input);

Note:This operator should not be used in production code as it will cause significant runtime overhead(会影响性能).
Additionally此外, the naming conventions used to store the tracing information may change. Thus, this operator should only be used during development for debugging purposes.

CE Function可以在存储过程与存储函数中使用，不在推荐直接使用SQL语句，这些函数是在计算引擎中运行的.

CE Function分三种
● Data Source Access operators that bind a column table or a column view to a table variable.数据抽取、访问：即将列式存储表、列视图中的数据抽取到内表变量中

² CE_COLUMN_TABLE

² CE_JOIN_VIEW

² CE_OLAP_VIEW

² CE_CALC_VIEW
● Relational operators that allow a user to bypass the SQL processor during evaluation and to directly interact with the calculation engine.关联操作

² CE_JOIN

² CE_LEFT_OUTER_JOIN

² CE_RIGHT_OUTER_JOIN

² CE_PROJECTION

² CE_CALC

² CE_AGGREGATION

² CE_UNION_ALL
● Special extensions that implement functions.特定扩展函数

² CE_VERTICAL_UNION

² CE_CONVERSION

² TRACE

7.1 CE_CALC

Description:
CE_CALC is used inside other relational operators. It evaluates an expression and is usually then bound to a new column. An important use case is evaluating expressions in the CE_PROJECTION operator. The CE_CALC function takes two arguments: CE_CALC函数通常在CE_PROJECTION函数中使用，它计算出表达式做为什么的选择列，有两个参数，下面是支持的表达式：
Table 19: Expression Functions

Name		Description	Syntax
类型转换函数		处理数据类型转换
float		convert arg to float type	float float(arg)
double		convert arg to double type	double double(arg)
decfloat		convert arg to decfloat type	decfloat decfloat(arg)
fixed		convert arg to fixed type	fixed fixed(arg, int, int)
string		convert arg to string type	string string(arg)
date		convert arg to date type daydate¹	daydate(stringarg), daydate daydate(fixedarg)
字符器函数		处理字符
charpos		returns the one-based position of the nth character in a string. The string is interpreted as using a UTF-8 character encoding 从指定的位置取一个字符	charpos(string, int)
chars		returns the number of characters in a UTF-8 string. In a CESU-8 encoded string this function returns the number of 16-bit words utilized by the string, just the same as if the string is encoded using UTF-16.返回字符串个数	chars(string)
strlen		returns the length of a string in bytes, as an integer number¹ 返回字符串所占字节数	int strlen(string)
midstr		returns a part of the string starting at arg2, arg3 bytes long. arg2 is counted from 1 (not 0) ² 截取字符串	string midstr(string, int, int)
leftstr		returns arg2 bytes from the left of the arg1. If arg1 is shorter than the value of arg2, the complete string will be returned. ¹ 从左边截取字符串	string leftstr(string, int)
rightstr		returns arg2 bytes from the right of the arg1. If arg1 is shorter than the value of arg2, the complete string will be returned. ¹ 从右边截取字符	string rightstr(string, int)
instr		returns the position of the first occurrence of the second string within the first string (>= 1) or 0, if the second string is not contained in the first. ¹ 查找字符串位置	int instr(string, string)
hextoraw		converts a hexadecimal representation of bytes to a string of bytes. The hexadecimal string may contain 0-9, upper or lowercase a-f and no spaces between the two digits of a byte; spaces between bytes are allowed.将十六进字符串转换为二进制串	string hextoraw(string)
rawtohex		converts a string of bytes to its hexadecimal representation. The output will contain only 0-9 and (upper case) A-F, no spaces and is twice as many bytes as the original string. 将二进制字符串转换为十六进制串	string rawtohex(string)
ltrim		removes a whitespace prefix from a string. The Whitespace characters may be specified in an optional argument. This functions operates on raw bytes of the UTF8-string and has no knowledge of multi byte codes (you may not specify multi byte whitespace characters). 去除前面空格（包括非格，可以在第二个可选参数中指定）	string ltrim(string) string ltrim(string, string)
rtrim		removes trailing whitespace from a string. The Whitespace characters may be specified in an optional argument. This functions operates on raw bytes of the UTF8-string and has no knowledge of multi byte codes (you may not specify multi byte whitespace characters).	string rtrim(string) string rtrim(string, string)
trim		removes whitespace from the beginning and end of a string. The following statements are functionally: trim(s) = ltrim(rtrim(s)) trim(s1, s2) = ltrim(rtrim(s1, s2), s2)	string trim(string) string trim(string, string)
lpad		adds whitespace to the left of a string. A second string argument specifies the whitespace which will be added repeatedly until the string has reached the intended length. If no second string argument is specified, chr(32) (' ') will be added. 在字符串前面加上前缀	string lpad(string, int) string lpad(string, int, string)
rpad		adds whitespace to the end of a string. A second string argument specifies the whitespace which will be added repeatedly until the string has reached the intended length. If no second string argument is specified, chr(32) (' ') will be added.	string rpad(string, int) string rpad(string, int, string)
数学函数		下面这些函数都是针对浮点数的，输入与输入参数都是double
l double log(double) l double exp(double) l double log10(double) l double sin(double) l double cos(double) l double tan(double) l double asin(double) l double acos(double) l double atan(double) l double sinh(double) l double cosh(double) l double floor(double) l double ceil(double)		These functions have the same functionality as in the C programming language.与C语句中的函数是一样的
sign		sign returns -1, 0 or 1 depending on the sign of its argument. Sign is implemented for all numeric types, date, and time. 返回数据符号	int sign(double), etc. int sign(date) int sign(time)
abs		Abs returns arg, if arg is positive or zero, -arg else. Abs is implemented for all numeric types and time.	int abs(int). double abs(double) decfloat abs(decfloat) time abs(time)
日期时间函数		Functions operating on date or time data
utctolocal		interpret datearg (a date, without timezone) as utc and convert it to the timezone named by timezonearg (a string)	iutctolocal(datearg, timezonearg)
localtoutc		convert the local datetime datearg to the timezone specified by the string timezonearg, return as a date	localtoutc(datearg, timezonearg)
weekday		returns the weekday as an integer in the range 0..6, 0 is Monday.	weekday(date)
now		returns the current date and time (localtime of the server timezone) as date	now()
daysbetween		returns the number of days (integer) between date1 and date2. This is an alternative to date2 - date1	daysbetween(date1, date2)
Further Functions
if	return arg2 if intarg is considered true (not equal to zero), else return arg3. Currently, no shortcut evaluation is implemented, meaning that both arg2 and arg3 are evaluated in any case. This means you cannot use if to avoid a divide by zero error which has the side effect of terminating expression evaluation when it occurs.当条件为真（非0）时，返回arg2的值		if(intarg, arg2, arg3)
case	return value1 if arg1 == cmp1, value2 if arg1 == cmp2 etc, default if there no match		case(arg1, default) case(arg1, cmp1, value1, cmp2, value2, ..., default)
isnull	return 1 (= true), if arg1 is set to null and null checking is on during evaluator run		isnull(arg1)
rownum	returns the number of the row in the currently scanned table structure. The first row has number 0返回当前内表循环到的数据行号		rownum()

1 Due to calendar variations with dates earlier that 1582, the use of the date data type is deprecated; youshould use the daydate data type instead.
Note: date is based on the proleptic Gregorian calendar. daydate is based on the Gregorian calendar which is also the calendar used by SAP HANA SQL.
2 These Calculation Engine string functions operate using single byte characters标注2的函数只能用于单字节字符. To use these functions with multi-byte character strings please see section: Using String Functions with Multi-byte Character Encoding below. Note, this limitation限制 does not exist for the SQL functions of the SAP HANA database which support Unicode encoded strings natively.
Using String Functions with Multi-byte Character Encoding
To allow the use of the string functions of Calculation Engine with multi-byte character encoding you can use the charpos and chars (see table above for syntax of these commands) functions. An example of this usage for the single byte character function midstr follows below:-
midstr(<input_string>, charpos(<input_string>, 32), 1)

7.2 CE_CONVERSION

Syntax:
CE_CONVERSION(<var_table>, <conversion_params>, [<rename_clause>])
<var_table> ::= :<identifier> 需要转换的内表变量
<conversion_params> ::= '['<key_val_pair>[{,<key_val_pair>}...]']'
Specifies the parameters for the conversion. The CE_CONVERSION operator is highly configurable via a list of key-value pairs. For the exact conversion parameters permissible, see the Conversion parameters table.
<key_val_pair> ::= <key> = <value>

Specify the key and value pair for the parameter setting.
<key> ::= <identifier>
Specifies the parameter key name.
<value> ::= <string_literal>
Specifies the parameter value.
<rename_clause> ::= <rename_att>[{,<rename_att>}]
Specifies new names for the result columns.
<rename_att> ::= <convert_att> AS <new_param_name>
<convert_att> ::= <identifier>
<new_param_name> ::= <identifier>
Specifies the new name for a result column.

Description:
Applies a unit conversion to input table <var_table> and returns the converted values. Result columns can optionally be renamed. The following syntax depicts valid combinations. Supported keys with their allowed domain of values are:
Table 20: Conversion parameters

Key	Values	Type	Mandatory	Default	Documentation
'family'	'currency'	key	Y	none	The family of the conversion to be used.
'method'	'ERP'	key	Y	none	The conversion method.
‘error_handling’	'fail on error', 'set to null', 'keep unconverted'	key	N	'fail on error'	The reaction if a rate could not be determined for a row.
'output'	combinations of 'input', 'unconverted', 'converted', 'passed_through', 'output_unit', 'source_unit', 'target_unit', 'reference_date'	key	N	'converted, passed_through, output_unit'	Specifies which attributes should be included in the output.
'source_unit'	Any	Constant	N	None	The default source unit for any kind of conversion.
'target_unit'	Any	Constant	N	None	The default target unit for any kind of conversion.
'reference_date'	Any	Constant	N	None	The default reference date for any kind of conversion.
'source_unit_column'	column in input table	column name	N	None	The name of the column containing the source unit in the input table.
'target_unit_column'	column in input table	column name	N	None	The name of the column containing the target unit in the input table.
'reference_date_column'	column in input table	column name	N	None	The default reference date for any kind of conversion.
'output_unit_column'	Any	column name	N	"OUTPUT_UNIT"	The name of the column containing the target unit in the output table.

For ERP conversion specifically:

Key	Values	Type	Mandatory	Default
'client'	Any	Constant		None	The client as stored in the tables.
'conversion_type'	Any	Constant		'M'	The conversion type as stored in the tables.
'schema'	Any	schema name		current schema	The default schema in which the conversion tables should be looked-up.

8 Calling SQLScript From Clients

8.1 Calling SQLScript from ABAP

Using CALL DATBASE PROCEDURE

The best way to call SQLScript from ABAP is to create a procedure proxy which can be natively called from ABAP by using the built in command CALL DATABASE PROCEDURE.
The SQLScript procedure has to be created normally in the SAP HANA Studio with the HANA Modeler. After this a procedure proxy can be creating using the ABAP Development Tools for Eclipse. In the procedure proxy the type mapping between ABAP and HANA data types can be adjusted. The procedure proxy is transported normally with the ABAP transport system while the HANA procedure may be transported within a delivery unit as a TLOGO object.

Calling the procedure in ABAP is very simple. The example below shows calling a procedure with two inputs (one scalar, one table) and one (table) output parameter:

CALL DATABASE PROCEDURE z_proxy
EXPORTING iv_scalar = lv_scalar
it_table = lt_table
IMPORTING et_table1 = lt_table_res.

Using the connection clause of the CALL DATABASE PROCEDURE command, it is also possible to call a database procedure using a secondary database connection. Please consult参考 the ABAP help for detailed instructions of how to use the CALL DATABASE PROCEDURE command and for the exceptions may be raised.
It is also possible to create procedure proxies with an ABAP API programmatically. Please consult the documentation of the class CL_DBPROC_PROXY_FACTORY for more information on this topic.

Using ADBC（原生SQL接口API）

ABAP 三种操作数据库的方法 OPEN SQL, EXEC SQL, ADBC

OPEN SQL这里就不多说了，可以执行大部分DML语句，但是却不支持DDL,DCL，UNIT，也没办法执行数据内嵌的函数。EXEC SQL 和 ADBC 是所谓的Native SQL，这种方式直接进入指定数据库，不涉及到DBI，这样就没有table buffer。相对EXEC SQL来说，更推荐ADBC的方式执行native sql，这种方式的好处是更加容易追踪错误（ADBC也是基于native SQL，只是对它进行了重新封装，是面像OO的，与JDBC一样）。

REPORT zrs_native_sqlscript_call.
PARAMETERS:
con_name TYPE dbcon-con_name DEFAULT 'DEFAULT'.
TYPES:
  BEGIN OF result_t,
    key   TYPE i,
    value TYPE string,
  END OF result_t.
DATA:
  sqlerr_ref TYPE REF TO cx_sql_exception,
  con_ref    TYPE REF TO cl_sql_connection,
  stmt_ref   TYPE REF TO cl_sql_statement,
  res_ref    TYPE REF TO cl_sql_result_set,
  d_ref      TYPE REF TO data,
  result_tab TYPE TABLE OF result_t,
  row_cnt    TYPE i.

START-OF-SELECTION.
  TRY.
      con_ref = cl_sql_connection=>get_connection( con_name ).
      stmt_ref = con_ref->create_statement( ).
*************************************
** Setup test and procedure
*************************************
* Create test table
      TRY.
          stmt_ref->execute_ddl( 'CREATE TABLE zrs_testproc_tab( key INT PRIMARY KEY, value NVARCHAR(255) )' ).
          stmt_ref->execute_update( 'INSERT INTO zrs_testproc_tab VALUES(1, ''test value'' )' ).
        CATCH cx_sql_exception.
      ENDTRY.
* Create test procedure
      TRY.
          stmt_ref->execute_ddl( 'DROP PROCEDURE zrs_testproc' ).
        CATCH cx_sql_exception.
      ENDTRY.
      TRY.
          stmt_ref->execute_ddl( 'DROP VIEW zrs_testproc_view' ).
        CATCH cx_sql_exception.
      ENDTRY.
      stmt_ref->execute_ddl( 'CREATE PROCEDURE zrs_testproc( OUT t1 zrs_testproc_tab ) '
                           &'READS SQL DATA WITH RESULT VIEW zrs_testproc_view AS BEGIN t1 = select * from zrs_testproc_tab; end' ).
* Create transfer table for output parameter
* this table is used to transfer data for parameter 1 of proc zrs_testproc
* for each procedure a new transfer table has to be created when the procedure is executed via result view, this table is not needed
* If the procedure has more than one table type parameter, a transfer table is needed for each parameter
* Transfer tables for input parameters have to be filled first before the call is executed
      TRY.
          stmt_ref->execute_ddl( 'DROP TABLE zrs_testproc_p1' ).
          stmt_ref->execute_ddl( 'CREATE GLOBAL TEMPORARY COLUMN TABLE zrs_testproc_p1( key int, value NVARCHAR(255) )' ).
        CATCH cx_sql_exception.
      ENDTRY.
*************************************
** Execution time
*************************************
      PERFORM execute_with_transfer_table.
      PERFORM execute_with_result_view.
      con_ref->close( ).
    CATCH cx_sql_exception INTO sqlerr_ref.
      PERFORM handle_sql_exception USING sqlerr_ref.
  ENDTRY.
FORM execute_with_result_view.
  CLEAR result_tab.
* execute procedure call by selecting from the result view
* additional input parameters have to be passed in via the WITH PARAMETERS clause
  res_ref = stmt_ref->execute_query( 'SELECT * FROM zrs_testproc_view' ).
* set output table
  GET REFERENCE OF result_tab INTO d_ref.
  res_ref->set_param_table( d_ref ).
* get the complete result set in the internal table
  row_cnt = res_ref->next_package( ).
  WRITE: / 'EXECUTE WITH RESULT VIEW: row count: ', row_cnt.
ENDFORM.
FORM execute_with_transfer_table.
  CLEAR result_tab.
* clear output table in session
* should be done each time before the procedure is called
  stmt_ref->execute_ddl( 'TRUNCATE TABLE zrs_testproc_p1' ).
* execute procedure call
  res_ref = stmt_ref->execute_query( 'CALL zrs_testproc( zrs_testproc_p1 ) WITH OVERVIEW' ).
  res_ref->close( ).
* read result for output parameter from output transfer table
  res_ref = stmt_ref->execute_query( 'SELECT * FROM zrs_testproc_p1' ).
* set output table
  GET REFERENCE OF result_tab INTO d_ref.
  res_ref->set_param_table( d_ref ).
* get the complete result set in the internal table
  row_cnt = res_ref->next_package( ).
  WRITE: / 'EXECUTE WITH TRANSFER TABLE: row count: ', row_cnt.
ENDFORM.
FORM handle_sql_exception
USING p_sqlerr_ref TYPE REF TO cx_sql_exception.
  FORMAT COLOR COL_NEGATIVE.
  IF p_sqlerr_ref->db_error = 'X'.
    WRITE: / 'SQL error occured:', p_sqlerr_ref->sql_code,  "#EC NOTEXT
    / p_sqlerr_ref->sql_message.
  ELSE.
    WRITE:
    / 'Error from DBI (details in dev-trace):',             "#EC NOTEXT
    p_sqlerr_ref->internal_error.
  ENDIF.
ENDFORM.

8.2 Calling SQLScript from Java

package tes;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.CallableStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

publicclass TT {

publicstaticvoid main(String[] args) {

CallableStatement cSt = null;

String sql = "call SqlScriptDocumentation.getSalesBooks(?,?,?,?)";

ResultSet rs = null;

Connection conn = getDBConnection(); // establish connection to database

// using jdbc

try {

cSt = conn.prepareCall(sql);

if (cSt == null) {

System.out.println("error preparing call: " + sql);

return;

}

cSt.setFloat(1, 1.5f);

cSt.setString(2, "'EUR'");

cSt.setString(3, "books");

int res = cSt.executeUpdate();

System.out.println("result: " + res);

do {

rs = cSt.getResultSet();

while (rs != null && rs.next()) {

System.out.println("row: " + rs.getString(1) + ", "

+ rs.getDouble(2) + ", " + rs.getString(3));

}

} while (cSt.getMoreResults());

} catch (Exception se) {

se.printStackTrace();

} finally {

if (rs != null)

rs.close();

if (cSt != null)

cSt.close();

}

9 ins_msg_proc

该代码片段前面某些实例用到过

CREATETABLE message_box (p_msg VARCHAR(200), tstamp TIMESTAMP);

CREATEPROCEDURE ins_msg_proc (p_msg VARCHAR(200)) LANGUAGE SQLSCRIPT AS

BEGIN

INSERTINTO message_box VALUES (:p_msg, CURRENT_TIMESTAMP);

END;