HANA SQLScript

 

1       数据类型... 5

1.1        日期时间类型... 5

1.1.1        DATE(日期)... 5

2       数据类型扩展... 5

2.1        标准数据类型... 5

2.2        创建Table类型... 5

2.3        删除类型... 6

3       存储过程... 6

3.1        CREATE PROCEDURE. 6

3.1.1        <parameter_clause>. 6

3.1.2        LANGUAGE <lang>. 7

3.1.3        SQL SECURITY <mode>. 7

3.1.4        READS SQL DATA.. 7

3.1.5        WITH RESULT VIEW <view_name>. 7

3.1.6        SEQUENTIAL EXECUTION.. 7

3.1.7        <procedure_body>. 8

3.1.8        Examples. 13

3.2        DROP PROCEDURE. 14

3.3        ALTER PROCEDURE RECOMPILE. 14

3.4        Procedure Calls. 15

3.4.1        Internal Procedure Call17

3.4.2        Call with Named Parameters. 18

3.5        Procedure Parameters. 19

3.5.1        Value Binding during Call20

3.5.2        Default values for procedure parameters. 20

3.6        Procedure Metadata. 21

3.6.1        Object Dependencies View Examples. 21

4       User Defined Function. 23

4.1        CREATE FUNCTION.. 23

4.1.1        <parameter_clause>. 24

4.1.2        <return_type>. 24

4.1.3        LANGUAGE. 25

4.1.4        SQL SECURITY. 25

4.1.5        DEFAULT SCHEMA.. 25

4.1.6        <function_body>. 25

4.1.7        Example. 26

4.2        DROP FUNCTION.. 28

4.3        Function Parameters. 28

4.4        Function Metadata. 28

4.5        Default Values for Parameters. 29

5       Declarative SQLScript Logic. 30

5.1        Table Parameter. 30

5.2        Table Variable Type Definition. 31

5.3        Binding(赋值) Table Variables. 33

5.4        Referencing Variables. 33

5.5        Column View Parameter Binding. 34

5.6        HINTS: NO_INLINE and INLINE. 35

6       Imperative SQLScript Logic. 36

6.1        Local Scalar Variables. 36

6.2        Variable Scope Nesting. 38

6.2.1        Local Variables in Control Structures. 39

6.3        Control Structures控制语句... 43

6.3.1        IF. 43

6.3.2        WHILE. 44

6.3.3        FOR. 45

6.3.4        Break Continue. 46

6.4        Cursors. 47

6.4.1        Define Cursor. 47

6.4.2        Open Cursor. 47

6.4.3        Close Cursor. 48

6.4.4        Fetch Query Results of a Cursor. 48

6.4.5        Attributes of a Cursor. 48

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

6.5        Autonomous Transaction. 50

6.6        COMMIT and ROLLBACK. 52

6.7        Dynamic SQL. 52

6.7.1        EXEC. 53

6.7.2        EXECUTE IMMEDIATE. 53

6.7.3        APPLY_FILTER函数... 53

6.8        Exception Handling. 55

6.8.1        DECLARE EXIT HANDLER. 55

6.8.2        DECLARE CONDITION.. 55

6.8.3        SIGNAL and RESIGNAL. 55

6.8.4        Exception Handling Examples. 56

6.9        ARRAY. 58

6.9.1        ARRAY CONSTRUCTOR数组构造... 58

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

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

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

6.9.5        UNNEST函数... 60

6.9.6        ARRAY_AGG函数... 62

6.9.7        TRIM_ARRAY函数... 63

6.9.8        CARDINALITY函数... 63

6.9.9        CONCATENATE TWO ARRAYS. 64

7       CE Founction(Calculation Engine Plan Operators). 65

7.1        CE_CALC. 79

7.2        CE_CONVERSION.. 83

8       Calling SQLScript From Clients. 84

8.1        Calling SQLScript from ABAP. 84

8.2        Calling SQLScript from Java. 88

9       ins_msg_proc. 89

 


1   数据类型

1.1日期时间类型

1.1.1     DATE(日期)

DATE 数据类型由年、月、日信息组成,表示一个日期值。 DATA 类型的默认格式为‘YYYY-MM-DD’。 YYYY 表示年, MM 表示月而 DD 表示日。时间值的范围从 0001-01-01 9999-12-31    

2                            数据类型扩展

除了内置的SQL数据类型,在SQLScript里用户可以自定义一些表类型的数据类型

2.1标准数据类型

SQLScript类型系统是基于SQL-92类型系统的,它支持以下基础数据类型:

Numeric types TINYINT SMALLINT INT BIGINT DECIMAL SMALLDECIMAL REAL DOUBLE

Character String Types VARCHAR NVARCHAR ALPHANUM

Datetime Types TIMESTAMP SECONDDATE DATE TIME

Binary Types VARBINARY

Large Object Types CLOB NCLOB BLOB

注:除了TEXT SHORTTEXT类型外,与SQL语句的类型相同

2.2创建Table类型

SQLScript里可以定义Table数据类型。表类型一般用于存储过程的参数类型

CREATE TYPE用来创建自定义数据类型的

CREATE TYPE <type_name> AS TABLE (<column_list_definition>)

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

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

<column_elem> ::= <column_name> <data_type>[<column_store_data_type>][<ddic_data_type>]

<data_type> ::= DATE | TIME | SECONDDATE | TIMESTAMP | TINYINT | SMALLINT |INTEGER | BIGINT | SMALLDECIMAL | DECIMAL| REAL |
DOUBLE | VARCHAR | NVARCHAR | ALPHANUM | SHORTTEXT |VARBINARY | BLOB | CLOB | NCLOB | TEXT

<column_store_data_type> ::= CS_ALPHANUM | CS_INT | CS_FIXED | CS_FLOAT |CS_DOUBLE | CS_DECIMAL_FLOAT | CS_FIXED(p-s, s)|
CS_SDFLOAT | CS_STRING | CS_UNITEDECFLOAT |CS_DATE | CS_TIME | CS_FIXEDSTRING | CS_RAW| CS_DAYDATE | CS_SECONDTIME | CS_LONGDATE |CS_SECONDDATE

<ddic_data_type> ::= DDIC_ACCP | DDIC_ALNM | DDIC_CHAR | DDIC_CDAY | DDIC_CLNT| DDIC_CUKY | DDIC_CURR | DDIC_D16D| DDIC_D34D |
DDIC_D16R | DDIC_D34R | DDIC_D16S | DDIC_D34S| DDIC_DATS | DDIC_DAY | DDIC_DEC| DDIC_FLTP | DDIC_GUID | DDIC_INT1 | DDIC_INT2 | DDIC_INT4| DDIC_INT8 | DDIC_LANG | DDIC_LCHR| DDIC_MIN | DDIC_MON | DDIC_LRAW | DDIC_NUMC | DDIC_PREC| DDIC_QUAN | DDIC_RAW | DDIC_RSTR| DDIC_SEC | DDIC_SRST | DDIC_SSTR | DDIC_STRG | DDIC_STXT| DDIC_TIMS | DDIC_UNIT | DDIC_UTCM| DDIC_UTCL | DDIC_UTCS | DDIC_TEXT | DDIC_VARC | DDIC_WEEK

 

CREATETYPE tt_publishers ASTABLE (

       publisher INTEGER,

       name VARCHAR(50),

       price DECIMAL,

       cnt INTEGER);

2.3删除类型

DROP TYPE <type_name> [<drop_option>]

删除用户自定义类型

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

<drop_option> ::= CASCADE | RESTRICT

如果未指定<drop_option>则将会执行一个非级联删除,仅删除指定的类型,相关联的类型不会被删除且不能被再使用,除非该自定义类型重新被创建

CREATETYPE my_type ASTABLE ( column_a DOUBLE );

DROPTYPE my_type;

3                            存储过程

3.1CREATE PROCEDURE

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>}...]

<parameter> ::= [<param_inout>] <param_name> <param_type>

                <param_inout> ::= IN|OUT|INOUT        

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

<sql_type> ::= DATE | TIME| TIMESTAMP | SECONDDATE | TINYINT | SMALLINT |INTEGER | BIGINT | DECIMAL | SMALLDECIMAL | REAL | DOUBLE
| VARCHAR | NVARCHAR | ALPHANUM | VARBINARY | CLOB | NCLOB | BLOB

<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,指定存储过程的安全模式

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

INVOKERSpecifies that the execution of the procedure is performed with the privileges of the invoker of the procedure.

3.1.4     READS SQL DATA

存储过程为只读的,不能包含DDLDMLINSERTUPDATEDELETE)语句(即只能使用查询SQLDCL语句),如果调用其他存储过程,则被调用过程也是只读的。设置参数会有特定的优化

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>}...

<proc_stmt> ::= <proc_block>| <proc_assign>| <proc_single_assign>| <proc_multi_assign>| <proc_if>| <proc_loop>| <proc_while>| <proc_for>|
<proc_foreach>| <proc_exit>| <proc_continue>| <proc_signal>| <proc_resignal>| <proc_sql>| <proc_open>| <proc_fetch>| <proc_close>| <proc_call>| <proc_exec>| <proc_return>

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_AGGCARDINALITYTRIM_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 ORDINALTIYAppends 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>

<proc_sql> ::= <subquery> | <select_into_stmt> | <insert_stmt> | <delete_stmt> | <update_stmt> | <replace_stmt> | <call_stmt> | <create_table> | <drop_table>

<insert_stmt>, <delete_stmt>,<update_stmt>,<replace_stmt> and <upsert_stmt>请参考HANA SQLINSERT , 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>}...]

<proc_param> ::= <identifier> | <string_literal> | <unsigned_integer> |<signed_integer>| <signed_numeric_literal> | <unsigned_numeric_literal>
|<expression>

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>}...]

<param>::= <in_table_param> | <in_scalar_param> |<out_scalar_param> |<out_table_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

or

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)

AS

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) 参数类型也可以直接是物理表

AS

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_DEPENDENCIESDependencies 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  表类型函数体是只读的,不支持DDLDML语句,且如果要调用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>]

AS

BEGIN

       <function_body>

END

4.1.1      <parameter_clause>

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

<parameter> ::= [IN] <param_name> <param_type>

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

<sql_type> ::= DATE | TIME | TIMESTAMP | SECONDDATE | TINYINT | SMALLINT | INTEGER | BIGINT | DECIMAL | SMALLDECIMAL | REAL | DOUBLE | VARCHAR | NVARCHAR

 

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

<sql_type> ::= DATE | TIME | TIMESTAMP | SECONDDATE | TINYINT | SMALLINT |INTEGER | BIGINT | DECIMAL | SMALLDECIMAL | REAL | DOUBLE | VARCHAR | NVARCHAR |ALPHANUM | VARBINARY | CLOB | NCLOB | BLOB

 

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.

NoteOnly 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.

DEFINERSpecifies that the execution of the function is performed with the privileges of the definer of the function.

INVOKERSpecifies 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.由于函数被标识为只读的,所以DDLDML语句都是不允许的。标量用户函数不支持表类型变量做为输入参数,并且在函数中也不支持表操作。

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>

<func_stmt> ::= <proc_block>| <proc_assign>| <proc_single_assign>| <proc_if>| <proc_while>| <proc_for>| <proc_foreach>| <proc_exit>|
<proc_signal>| <proc_resignal>| <proc_open>| <proc_fetch>| <proc_close>

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

AS

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.

CASCADEDrops the function and dependent objects.级联删除

RESTRICTDrops 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

AS

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.

NoteUsing 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

AS

BEGIN

tab2 = SELECT I FROM T;

END;

 

CREATEPROCEDURE procCaller (OUT table2 TABLE(I int))

LANGUAGE SQLSCRIPT READS SQL DATA

AS

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

AS

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

AS

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;

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;

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所在的IFEND 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;

              ENDFOR;

       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


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

AS

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

AS

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)

AS

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_valuestart_value递减)

<start_value>大于等于0的整数

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


Example 1
CREATEPROCEDURE proc() LANGUAGE SQLSCRIPT

READS SQL DATA

AS

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

AS

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

AS

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

AS

BEGIN

       DECLARE errCode INT;

       DECLARE errMsg VARCHAR(5000);

       DECLARE EXIT HANDLER FOR SQLEXCEPTION --外层BEGINEND块的异常处理块,即如果外层中的脚本语句执行出异常后,就会走这段(内层BEGINEND块处理逻辑),如果传进来的是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;

       END;

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.

COMMITROLLBACK执行的一,都会重新开启一个新的事务
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;

       END;

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.
NoteThe 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语句创建表时,列名是否使用了双引号引起来,如果没有使用双引号引起来,则创建的表列名会自动转换为大写;如果使用了双引号,则列名会大小写敏感,即你输入的是什么,创建的表的列名就是什么,这就可能即有大写也有小写,所以在SQLCE 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>}…]    attributesSelect选择列表,可以是维度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是否支持!!??

Noteyou 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>可以省略,如果省略的话,会将两个表的所有字段都返回出来(如果这时除了相同联接属性列外,其他非联接属性列名相同的话,会有问题的,因为这个返回表类型你不可能在里面创建两