HANA SQL Script学习(5): Imperative SQLScript Logic


5. Imperative SQLScript Logic

5.1 Scalar Variables

/*

4. Imperative SQLScript Logic

4.1 Scalar Variables

DECLARE [{, }...] [CONSTANT] | AUTO [NOT NULL]

::= (DEFAULT | '=' ) |

   !!= An element of the type specified by

定义常量

*/

CREATE PROCEDURE test_proc13(OUT z INT) LANGUAGE SQLSCRIPT READS SQL DATA
 AS BEGIN   
    DECLARE a int;   
    DECLARE b int = 0;   
    DECLARE c int DEFAULT 0;            

    t = select * from mytab4;      
    select count(*) into a from :t;      
    b = :a + 1;      
    z = :b + :c;
 end;

5.2 SELECT INTO with DEFAULT Values

/*

4.2 SELECT INTO with DEFAULT Values

SELECT INTO [DEFAULT ]

 [] []

 [] [{ , ... }]

 [] [] ;

[EXEC | EXECUTE IMMEDIATE]

 [ INTO  [DEFAULT ] ]

 [ USING ]

::= [{, }...]

::= | '[' ']'

SQL in Scalar Expressions

SQL子查询语句当查询返回单个值时,可以作为常量计算

*/

--指定default错误???
CREATE TABLE TEST_SEL_DEFAULT(A INT NOT NULL, B VARCHAR(10));
DO BEGIN   
    DECLARE A_COPY INT ARRAY;   
    DECLARE B_COPY VARCHAR(10) ARRAY;   
    SELECT A, B INTO A_COPY[1],B_COPY[1] DEFAULT -2+1, NULL FROM TEST_SEL_DEFAULT;   
    --(A_COPY[1],B_COPY[1]) = (-1,?), use default value   
    SELECT :A_COPY[1], :B_COPY[1] from dummy;
    --使用EXEC执行sql语句指定default值  

    EXEC 'SELECT A FROM TEST_SEL_DEFAULT' INTO A_COPY[1] DEFAULT 2;
    --(A_COPY[1]) = (2), exec into statement with default value      
    SELECT :A_COPY[1], :B_COPY[1] from dummy;   

    INSERT INTO TEST_SEL_DEFAULT VALUES (0, 'sample0');   
    SELECT A, B INTO A_COPY[1], B_COPY[1] DEFAULT 5, NULL FROM TEST_SEL_DEFAULT;  
    --(A_COPY[1],B_COPY[1]) = (0,'sample0'), executed as-is
    SELECT :A_COPY[1], :B_COPY[1] from dummy;   
END;

--指定default语句错误
DO BEGIN
     DECLARE A_COPY INT;   
     DECLARE B_COPY VARCHAR(10);   
     CREATE ROW TABLE TEST_SEL_DEFAULT(A INT NOT NULL, B VARCHAR(10)); 
     --不能指定default???
     SELECT A, B INTO A_COPY, B_COPY DEFAULT -2+1, NULL FROM TEST_SEL_DEFAULT;   
     --(A_COPY,B_COPY) = (-1,?), use default value   
     EXEC 'SELECT A FROM TEST_SEL_DEFAULT' INTO A_COPY DEFAULT 2;   

     --(A_COPY) = (2), exec into statement with default value   
     INSERT INTO TEST_SEL_DEFAULT VALUES (0, 'sample0');   
     SELECT A, B INTO A_COPY, B_COPY DEFAULT 5, NULL FROM TEST_SEL_DEFAULT;   
     --(A_COPY,B_COPY) = (0,'sample0'), executed as-is
END;

--子查询计算
--feature not supported: Subquery is not allowed
DO BEGIN
    DECLARE n INT;
    n = ( select count(*) from mytab4 ) + 1;
END

-- fetch returns more than requested number of rows
do begin 
    declare n int; 
    select i into n from mytab4;
end;

5.3 Table Variables

/*

4.3 Table Variables

1.Table Variable Operators

通过下标方式获取表值

.[]

2.Inserting Data Records into Table Variables

:.INSERT(( ])

限制插入的column

:.(,…, ).INSERT((,…, ), [ ])

3.Inserting Table Variables into Other Table Variables

:[.()].INSERT(:[, ])

4.Updating Data Records in Table Variables

:.UPDATE((,…, )

[] = (,…, )

5.Deleting Data Records from Table Variables

:.DELETE([ ])

--删除范围

:.DELETE(..)

6.UNNEST FUNCTION

= UNNEST( [ {, } ...] )

    [WITH ORDINALITY]

    [AS (

    [ {, }... ]) ]

  ::= :table_variable                 

    | :array_variable                

    | :array_function

  ::= '*'        

    | '(' ')'                    

    |

::= [AS ] [, ]

7.Emptiness Check for Tables and Table Variables

检查是否table参数为空

IS_EMPTY( | )

8.Get Number of Records for Tables and Table Variables

获取表参数记录数

RECORD_COUNT( | )

9.Search in Table Variables

position = .SEARCH((), () [, ])

,大小需对应,

默认1

10.SQL DML Statements on Table Variables

使用DML限制:

DML statements on table variables cannot be used in autonomous transactions and parallel execution blocks.

Neither input, nor output procedure or function parameters can be manipulated with DML statements.

11.Sorted Table Variables

定义sorted table variables

--定义search key,排序字段

CREATE TYPE AS TABLE () SQLSCRIPT SEARCH KEY()

DECLARE TABLE() SEARCH KEY()

DECLARE

SEARCH KEY()

--定义procedure指定排序key

CREATE PROCEDURE (IN TABLE() SEARCH KEY())

CREATE PROCEDURE (IN

SEARCH KEY())

注意:使用

中不能嵌套定义search key

*/

--1.Table Variable Operators
--创建table
create table intab( a integer,b varchar(20));
create table outtab( a integer,b varchar(20));

--这里select * form :intab,必须加:,不然对intab数据操作无效
--select * from intab,默认直接使用输入参数,之前对intab数据操作无效
create procedure test_proc13(
    IN intab TABLE( a integer,b varchar(20)),
    OUT outtab TABLE(a integer,b varchar(20))
    )
AS BEGIN
    intab.A[2] = 5;
    outtab = select * from :intab;
END;

--intab插入数据
insert into intab values(1,'test1');
insert into intab values(2,'test2');
insert into intab values(3,'test3');
--调用procedure
do begin
    declare outtab_temp table(a integer,b varchar(20));
    intab_temp = select * from intab; 
    --call test_proc13(:intab_temp,:outtab_temp);
    --call test_proc13(:intab_temp,?);
    call test_proc13(:intab_temp,outtab=>outtab_temp);
    select * from :outtab_temp;
end;

--2.Inserting Data Records into Table Variables
--创建procedure
create procedure test_proc14(
    IN intab table(a integer,b varchar(20)),
    OUT outtab table(a integer,b varchar(20))
    )
as begin
    declare i int;
    declare max_count int = 10;
    declare idx int;
    declare str_temp varchar(20);
    for i in 1..max_count DO
         str_temp = :intab.b[i] || 'ins';
         idx = max_count + i;
         --这句insert有问题
         --:intab.INSERT((:idx,:str_temp));
         --限制column
         --:intab.(a,b).insert((:idx,:str_temp));
    end for;
    --outtab = select * from :intab;
    --直接插入目标table variables
    :outtab.insert(:intab,1);
end;
/* 文档示例出错:OT1,incorrect syntax CREATE TABLE SOURCE(K VARCHAR(20), PCT DECIMAL(5, 2), V DECIMAL(10, 2)); CREATE TABLE TARGET(K VARCHAR(20), V DECIMAL(10, 2)); INSERT INTO SOURCE VALUES ('A', 5.99, 734.42); INSERT INTO SOURCE VALUES ('A', 50.83, 422.26); INSERT INTO SOURCE VALUES ('B', 75.07, 362.53); INSERT INTO SOURCE VALUES ('C', 87.21, 134.53); INSERT INTO SOURCE VALUES ('C', 80.72, 2722.49); CREATE PROCEDURE SPLIT(IN IT SOURCE, OUT OT1 TARGET, OUT OT2 TARGET) AS BEGIN DECLARE IDX INT; DECLARE MAXIDX INT = RECORD_COUNT(:IT); FOR IDX IN 1..MAXIDX DO DECLARE V1 DECIMAL(10, 2) = :IT.V[IDX] * :IT.PCT[IDX] / 100; DECLARE V2 DECIMAL(10, 2) = :IT.V[IDX] - V1; :OT1.INSERT((:IT.K[IDX], V1)); :OT2.INSERT((:IT.K[IDX], V2)); END FOR; END; CALL SPLIT(SOURCE, ?, ?); */ --4.Updating Data Records in Table Variables --创建procedure create procedure test_proc15( IN intab table(a integer,b varchar(20)), OUT outtab table(a integer,b varchar(20)) ) as begin declare i int; declare max_count int = 2; for i in 1..max_count DO str_temp = :intab.b[i] || 'ins'; idx = max_count + i; --update语句有问题 :intab.(a,b).update((:idx,:str_temp),i); end for; outtab = select * from :intab; end; --5.Deleting Data Records from Table Variables --创建procedure create procedure test_proc16( IN intab table(a integer,b varchar(20)), OUT outtab table(a integer,b varchar(20)) ) as begin --delete语句报错 :intab.delete(1); outtab = select * from :intab; end; --6.unnest function --创建procedure create procedure test_proc17(OUT rst outtab) AS BEGIN DECLARE arr_id INTEGER ARRAY= ARRAY(1,2); DECLARE arr_name VARCHAR(20) ARRAY = ARRAY('name1', 'name2', 'name3'); rst = UNNEST(:arr_id, :arr_name) AS (a,b); END; --调用procedure --结果显示 --1 name1 --2 name2 --? name3 call test_proc17(?); --使用ordinary --ordinality,添加排序列 create procedure test_proc18(OUT rst table(a integer,b varchar(20),"order_column" integer)) AS BEGIN DECLARE arr_id INTEGER ARRAY= ARRAY(1,2,5,3,7); DECLARE arr_name VARCHAR(20) ARRAY = ARRAY('name1', 'name2', 'name3','name4','name5'); rst = UNNEST(:arr_id, :arr_name) with ordinality AS (a,b,"order_column"); END; --调用procedure /* 执行结果: A B order_column 1 name1 1 2 name2 2 5 name3 3 3 name4 4 7 name5 5 */ call test_proc18(?); --使用*代表所有列 --语法有错* create procedure test_proc19(OUT rst table(a integer,b varchar(20))) AS BEGIN DECLARE arr_id INTEGER ARRAY= ARRAY(1,2,7); DECLARE arr_name VARCHAR(20) ARRAY = ARRAY('name1', 'name2', 'name3'); rst = UNNEST(:arr_id, :arr_name) AS (*, (a as "id", b as "text")); END; --7.Emptiness Check for Tables and Table Variables --is_empty()判断表参数是否为空 create procedure test_proc20(IN itab intab,OUT rst outtab) AS BEGIN if IS_EMPTY(:itab) THEN RETURN; ELSE rst = select * from :itab; END IF; END; --调用procedure do begin declare intab_temp table( a integer ,b varchar(20) ); declare outtab_temp table( a integer ,b varchar(20) ); intab_temp = select * from intab; call test_proc20(:intab_temp,outtab_temp); select * from :outtab_temp; end --8.Get Number of Records for Tables and Table Variables --RECORD_COUNT()不存在? create procedure test_proc21(IN itab intab) AS BEGIN declare i int; IF RECORD_COUNT(:itab) > 0 THEN select '大于0' from dummy; END IF; for i in 1 .. RECORD_COUNT(:itab) DO select * from :itab where a = :i; END FOR; END; --9.Search in Table Variables --search有问题 create procedure test_proc22(IN itab intab) AS BEGIN declare i int; i = :itab.search((a, b), (1, "test1")); END; --10.DML statements on Table Variables --INSERT --UPDATE --DELETE create procedure test_proc23(OUT otab outtab) AS BEGIN declare itab_temp table(a integer,b varchar(20)); --有问题? INSERT INTO :itab_temp VALUES(4,"test4"); --UPDATE :itab_temp SET b = 'test udp' where a = 2; --DELETE FROM :itab_temp where a = 1; outtab = select * from :itab_temp; END; --创建表类型table types create type tt_test1 as table( a int primary key,b int not null,c varchar(20)); --直接使用table types do begin declare t_test tt_test1; --这句有问题 declare t_test2 table(a int,b int,c varchar(20) not null,primary key(a,b)); end

5.4 Auto Type Derivation

/*

4.4 Auto Type Derivation

自动类型派生

不显示指定声明数据类型,让SQL Script自动确定类型

可以用于scalar variables,tables和arrays

DECLARE AUTO =

必须指定默认值

scalar variables:

DECLARE [{, }...] [CONSTANT] AUTO [NOT NULL]

talbes:

DECLARE [{, }...] [CONSTANT] AUTO

使用限制:

Auto type cannot be used inside a trigger

Auto type cannot be used for row-type variables

Auto type cannot be used,

if the default value contains one of the following:

    System variables

    Scalar access of any table or auto-type table

*/

--示例:
--创建了AUTO表
create table auto (a bigint);
do begin
    --可以定义AUTO表类型变量
    declare tab1 "AUTO" = select 1 a from dummy;
    --这里AUTO相等于关键词,自动类型派生?
    -- return type mismatch: TAB2[ A:INT B:INT ] != expected result [ A:BIGINT ]
    --这里关键词和定义表名有冲突
    --declare tab2 AUTO = select 1 a,2 b from dummy;
end
do begin
    --不支持auto
    declare var1 auto = 1.0;
    declare arr1 auto = array(1, 2);
    declare tab1 auto = select 1 as x from dummy;
end

5.5 Global Session Variables

/*

4.5 Global Session Variables

全局会话变量

SET = ;

::= |

::=

查询全局会话变量

SESSION_CONTEXT ()

重置全局会话变量

UNSET

注意:全局会话变量

max_session_variables参数配置最大数量全局会话变量,默认1024;

全局会话变量不能再只读function,procedure中使用;

*/

--procedure 设置全局会话变量
CREATE PROCEDURE CHANGE_SESSION_VAR (IN NEW_VALUE NVARCHAR(50)) AS
BEGIN   
    SET 'MY_VAR' = :new_value;
END

--function 获取全局会话变量
CREATE FUNCTION GET_SESSION_VALUE ()
 RETURNS var NVARCHAR(50) AS
BEGIN   
    var = SESSION_CONTEXT('MY_VAR');
END;

do begin
    call change_session_var('hello world');
    select get_session_value() as a from dummy;
end

5.6 Variable Scope Nesting

/*

4.6 Variable Scope Nesting

变量作用域

本地变量可以定义在嵌套代码块中,只在本代码块中生效

*/

CREATE PROCEDURE test_proc_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;
--out = 2
--因为val赋值等于第二个代码块的a
call test_proc_nested_block(?);

CREATE PROCEDURE test_proc_nested_block1(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; 
--out = 3
--子作用域没有定义同名变量,就会去父作用域中找
call test_proc_nested_block1(?);

--if语句作用域
CREATE PROCEDURE test_proc_nested_block_if(IN inval INT, OUT val INT)
LANGUAGE SQLSCRIPT READS SQL DATA AS
BEGIN   
    DECLARE a INT = 1;    
    BEGIN       
         val = :inval;   
    END;      
    IF :a = 1 THEN
         declare a INT = 2;      
         val =  :a;
    END IF;   
END;
--out = 2
--if语句相当于一个代码块
call test_proc_nested_block_if(10,?);

--while loop语句
CREATE PROCEDURE test_proc_nested_block_while(OUT val INT)
LANGUAGE SQLSCRIPT READS SQL DATA AS
BEGIN   
    DECLARE v int = 2; 
    DECLARE a int = 10; 
    val = 0;   
    WHILE v > 0   DO   
         DECLARE a INT = 0;       
         a = :a + 1;       
         val = :val + :a;       
         v = :v - 1;   
    END WHILE;
END;
--out = 2
--while相等于一个代码块
call test_proc_nested_block_while(?);

--for循环语句
CREATE PROCEDURE test_proc_nested_block_for(OUT val INT)
LANGUAGE SQLSCRIPT READS SQL DATA AS
BEGIN   
    DECLARE a1 INT default 0;
    --mytab4多笔记录字段 I 1~10   
    DECLARE CURSOR C FOR SELECT * FROM mytab4;   
    FOR R as C DO     
         --注意用于计算的变量必须初始化默认值 
         a1 = :a1 + R.I;
    END FOR;
    val = :a1;    
END;
--out = 55
call test_proc_nested_block_for(?);

--loop循环语句
CREATE PROCEDURE test_proc_nested_block_loop(OUT val INT)
LANGUAGE SQLSCRIPT READS SQL DATA AS
BEGIN   
    DECLARE a1 int default 0;
    DECLARE i1 int default 0;   
    DECLARE CURSOR C FOR SELECT * FROM mytab2;        
    LOOP      
         --cursor只赋值第一笔记录   
        DECLARE CURSOR C FOR SELECT * FROM mytab4;
         OPEN C;       
         FETCH C into a1;       
         CLOSE C; 
         --循环结束条件,一定要有不然死循环
         i1 = :i1 + 1;
         IF :i1 = 5 THEN
             BREAK;
         END IF; 
    END LOOP;
    val = :a1;
END;
--out = 1
--mytab4的第一笔记录,mytab2第一笔记录为2
call test_proc_nested_block_loop(?);

5.7 Control Structures

/*

4.7 Control Structures

控制结构

if,while,loop等控制语句

比较条件不支持between and

1.Conditionals

条件语句

语法格式:

IF THEN

    [{ELSEIF THEN }...]

    [ELSE ]

END IF

  ::=

  ::=

   ::= |

  ::=

  ::= IS [NOT] NULL

所有本地变量的默认值都为NULL,所以定义变量用于计算时,需要初始化;

2.While Loop

while循环语句

语法格式:

WHILE DO

    

END WHILE

3.For Loop

for循环语句

语法格式:

FOR IN [REVERSE] .. DO

    

END FOR

4.跳转控制语句

BREAK

跳出循环

CONTINUE

跳过本次循环

5.Operators

操作符

IN NOTIN使用示例:

-- single expression on the left-hand side

IF :i IN (1, 2, 3, 6) THEN [...] END IF;

-- multiple expressions on the left-hand side

IF (:key, :val) NOT IN ((1, 'H2O'), (2, 'H2O'), (3, 'abc')) THEN [...] END IF;

-- subquery on the right-hand side

IF :i NOT IN (SELECT a FROM mytable) THEN [...] END IF;

-- subquery using table variable

IF (:a, :b, :c) IN (SELECT id, city, date from :lt where :id < :d) THEN [...] END IF; 

-- subquery using table function

FOR i IN 1 .. CARDINALITY(:arr) DO 

    IF :arr[:i] IN (SELECT b FROM tfunc()) THEN  [...] END IF;

END FOR;

注:测试时,目前不支持IN

EXISTS使用示例:

当查询语句返回非空子集时为true,否则false

IF EXISTS (SELECT * FROM mytab WHERE date = :d) THEN ... END IF;

IF NOT EXISTS (SELECT * FROM SYS.TABLES WHERE schema_name = :schema AND table_name = :table) THEN ... END IF;

WHILE :i < 100 AND EXISTS (SELECT * FROM mytab WHERE a = :i) DO  i = :i + 1; ... END WHILE;

WHILE NOT EXISTS (SELECT * FROM mytab WHERE a > sfunc(:z).r2) DO ... END WHILE;

BETWEEN使用示例:

IF :i [NOT] BETWEEN 1 AND 2 THEN ... END IF;

WHILE :I [NOT] BETWEEN 1 AND 2 DO ... END WHILE;

注:测试时,目前不支持between

*/

--示例1:
--SUDF:scalar user defined function
CREATE FUNCTION test_func_sudf(a integer)
RETURNS res_a integer
LANGUAGE SQLSCRIPT READS SQL DATA AS
BEGIN
    --原样返回
    res_a = :a;
END;
--创建procedure,if语句
CREATE PROCEDURE test_proc_control_if (in input1 INTEGER, out output1 table(a integer))
AS BEGIN   
    DECLARE i INTEGER DEFAULT :input1;    
    IF test_func_sudf(:i) = 1 THEN        
         output1 = SELECT a FROM mytab1;  
    ELSEIF test_func_sudf(:i) = 2 THEN        
         output1 = SELECT a FROM mytab2;   
    ELSE         
         output1 = SELECT i as a FROM mytab4;   
    END IF;
END;
--通过不同参数到不同if分支逻辑,查询不同表
call test_proc_control_if(1,?);
call test_proc_control_if(2,?);
call test_proc_control_if(3,?);

--示例2:
--创建procedure,while语句
--不支持between and语句
CREATE PROCEDURE test_proc_control_while (in input1 INTEGER, out output1 table(a integer))
AS BEGIN   
    DECLARE i INTEGER DEFAULT :input1; 
    --feature not supported: Range comparison is not supported  
    --WHILE test_func_sudf(:i) between 1 and 5 DO    
    WHILE test_func_sudf(:i) = 1 DO 
         output1 = SELECT a FROM mytab1; 
         i = :i - 1;
    END WHILE;
END;
call test_proc_control_while(1,?);

--示例3
--创建procedure,for loop语句
CREATE PROCEDURE test_proc_control_for (in input1 INTEGER, out output1 INTEGER)
AS BEGIN   
    DECLARE i INTEGER; 
    DECLARE var1 INTEGER default 0;
    FOR i IN 1..test_func_sudf(:input1)  DO 
         var1 = :var1 + :i;
    END FOR;
    output1 = :var1;
END;
call test_proc_control_for(10,?);

--示例4
--创建procedure,in,exists等语句
CREATE PROCEDURE test_proc_control_operator (in input1 INTEGER,out output1 INTEGER,out output2 INTEGER)
AS BEGIN   
    DECLARE i INTEGER default :input1; 
    DECLARE var1 INTEGER default 0;
    DECLARE var2 INTEGER default 0;
    --报错:Subquery is not allowed
    --IF i IN (select I from mytab4) THEN
    --  var1 = :i;
    --END IF;

    --报错:feature not supported: IN comparison is not supported in SQLScript.
    --IF I IN (1,2,3,4,5) THEN
    --  var1 = :i;
    --END IF;
    --output1 = :var1;

    --报错:feature not supported: Subquery is not allowed
    IF EXISTS (select * from mytab4 where i = :i ) THEN
         var2 = :i;
    END IF;
    output2 = :var2;
END;

5.8 Cursors

/*

4.8Cursors

光标,用来逐行遍历查询结果集

Cursors are used to fetch single rows from the result set returned by a query.

When a cursor is declared, it is bound to the query.

It is possible to parameterize the cursor query.

1.define cursor

定义游标

语法结构:

DECLARE CURSOR [({{,} ...)] [ HOLD]

FOR

::=

=

::= DATE | TIME | SECONDDATE | TIMESTAMP | TINYINT

| SMALLINT | INTEGER | BIGINT | SMALLDECIMAL | DECIMAL              

| REAL | DOUBLE | VARCHAR | NVARCHAR | ALPHANUM

| VARBINARY | BLOB | CLOB | NCLOB

:= WITH | WITHOUT

光标逐行访问查询结果集,是不支持更新光标的

2.open cursor

OPEN [()]

准备光标,如果有参数,这里执行计算查询操作

3.获取将光标当前行值,然后光标到下一行

FETCH INTO

4.close cursor

CLOSE

关闭之前打开游标,释放关联资源

5.cursor attributes

cursor参数属性:

c_cursor1::ISCLOSED  返回true,c_cursor1 closed;

c_cursor1::NOTFOUND  返回true,没有有效行;

c_cursor1::ROWCOUNT  返回目前光标所在行数;

6.Looping Over Result Sets

循环使用光标访问数据

这种循环方法,可以隐式打开,关闭光标,比显式控制更加方便安全

语法结构:

FOR AS [()] DO

  | {.}

END FOR

7.Updatable Cursor

使用光标数据更新数据库表

语法结构:

UPDATE [ [ AS ] ]

    SET    

    WHERE CURRENT OF

DELETE FROM [ [ AS ] ]

    WHERE CURRENT OF

注意:

1.The cursor has to be declared with a SELECT statement having the FOR UPDATE clause

 in order to prevent concurrent WRITE on tables (without FOR UPDATE, the cursor is not updatable)

2.The updatable cursor may be used only for UPDATE and DELETE operations.

3.Using an updatable cursor in a single query instead of SQLScript is prohibited.

4.Only persistent tables (both ROW and COLUMN tables) can be updated with an updatable cursor.

5.UPDATE or DELETE operations performed on a table by means of an updatable cursor are allowed only one time per row.

8.Cursor Holdability

语法结构:

DECLARE CURSOR cursor_name [()] [ HOLD] FOR ...

:= WITH | WITHOUT

在定义时指定Holdability,比配置时指定的优先级更高;

DECLARE CURSOR cursor_name WITH HOLD FOR …

Declares a cursor with holdability for both commit and rollback

DECLARE CURSOR cursor_name WITHOUT HOLD FOR …

Declares a cursor without holdability for both commit and rollback

DECLARE CURSOR cursor_name FOR …

Declares a cursor with holdability for commit and without holdability for rollback

*/

--示例1:
--创建游标
CREATE PROCEDURE test_proc_cursor
AS BEGIN   
    DECLARE i INT;  
    --WITH HOLD不支持
    --DECLARE CURSOR mycur WITH HOLD FOR SELECT * FROM mytab1;
    DECLARE CURSOR mycur FOR SELECT * FROM mytab1;       
    OPEN mycur;      
    FETCH mycur INTO i;   
    CLOSE mycur;   
    SELECT :i FROM DUMMY;
END;
call test_proc_cursor;

--示例2:
--获取多值
CREATE PROCEDURE test_proc_cursor1
AS BEGIN   
    DECLARE v_id string;  
    DECLARE v_name string;
    DECLARE v_price DECIMAL(10,2);
    DECLARE v_category string;
    DECLARE v_quantity INTEGER;
    DECLARE CURSOR mycur FOR SELECT * FROM "MyProducts";       
    OPEN mycur;
    --默认第一行数据
    --参数数量和表列数保持一致
    --报错:wrong number of values in the INTO list of a FETCH statement    
    FETCH mycur INTO v_id,v_name,v_category,v_quantity,v_price;   
    CLOSE mycur;   
    SELECT :v_id,:v_name,:v_category,:v_quantity,:v_price FROM DUMMY;
END;
call test_proc_cursor1;

--示例3:
--循环获取查询结果集值
CREATE PROCEDURE test_proc_cursor2(IN input_category string)
AS BEGIN   
    DECLARE v_id string;  
    DECLARE v_name string;
    DECLARE v_price DECIMAL(10,2);
    DECLARE v_category string default :input_category;
    DECLARE v_quantity INTEGER;
    DECLARE CURSOR mycur(in_category string) FOR SELECT * FROM "MyProducts"
         WHERE "Category" = :in_category;       
    FOR cur_row AS mycur(:v_category)  DO
         v_id = cur_row."Product_ID";
         v_name = cur_row."Product_Name";
         v_category = cur_row."Category";
         v_quantity = cur_row."Quantity";
         v_price = cur_row."Price";   
         SELECT :v_id,:v_name,:v_category,:v_quantity,:v_price FROM DUMMY;
    END FOR;
END;
call test_proc_cursor2('Clothes');

--示例4:通过cursor数据更新数据库表
--目前不支持
CREATE PROCEDURE test_proc_cursor3
AS BEGIN   
    DECLARE CURSOR mycur FOR SELECT * FROM "MyProducts";       
    FOR cur_row AS mycur DO
         IF cur_row."Product_Name" = 'Coats' THEN
             UPDATE "MyProducts" SET "Price" = "Price" + 100 WHERE CURRENT OF mycur;
         END IF;
         IF cur_row."Product_Name" = 'Purse' THEN
             DELETE "MyProducts" WHERE CURRENT OF mycur;
         END IF;
    END FOR;
END;
call test_proc_cursor3;

--示例5:
--holdability
CREATE PROCEDURE test_proc_cursor4
AS BEGIN   
    DECLARE i INT;
    --WITH HOLD不支持   
    DECLARE CURSOR mycur WITH HOLD FOR SELECT * FROM mytab1;   
    OPEN mycur;   
    ROLLBACK;   
    FETCH mycur INTO i;   
    CLOSE mycur;   
    SELECT :i as i FROM DUMMY;
END;

5.9 Autonomous Transaction

/*

4.9 Autonomous Transaction

自主事务,独立于主进程事务,保留所做更改,隐式提交

语法结构:

:: =

BEGIN AUTONOMOUS TRANSACTION

    []       

    []       

    []

END;

*/

--autonomous transaction
CREATE PROCEDURE test_proc_auto_transaction
AS BEGIN   
    DECLARE a INTEGER default 0;
    DECLARE b INTEGER default 1;
    BEGIN AUTONOMOUS TRANSACTION
         INSERT INTO MYTAB1 (A) VALUES(10);
    END;
    --出现exception
    b = :b / :a;
END;
--报错:division by zero undefined: cannot be divided by zero
--但是数据已经插入进去了
call test_proc_auto_transaction;

5.10 Transactional Statements

/*

4.10 Transactional Statements

1.COMMIT OR ROLLBACK

提交事务,回滚事务

注意:

    1.If you used DSQL in the past to execute these commands (for example, EXEC ‘COMMIT’, EXEC ’ROLLBACK’),

       SAP recommends that you replace all occurrences with the native commands COMMIT/ROLLBACK because they are more secure.

    2.The COMMIT/ROLLBACK commands are not supported in Scalar UDF or in Table UDF.

一旦有Rollback语句,之前所有没有Commit的操作都会回滚!

*/

--commit or rollback
CREATE PROCEDURE test_proc_commit_rollback
AS BEGIN   
    --插入操作提交了
    INSERT INTO MYTAB1 (A) VALUES(11);
    COMMIT;
    --插入操作回滚
    INSERT INTO MYTAB1 (A) VALUES(12);
    ROLLBACK;
END;
call test_proc_commit_rollback;

5.11  SAVEPOINT

/*

4.11  SAVEPOINT

SQLScript支持savepoint

语法规则:

the definition of a SAVEPOINT: SAVEPOINT

the rollback to a specific SAVEPOINT: ROLLBACK TO SAVEPOINT

the releasing of a SAVEPOINT: RELEASE SAVEPOINT

*/

--savepoint
--目前不支持
CREATE PROCEDURE test_proc_savepoint
AS BEGIN   
    --插入操作提交了
    INSERT INTO MYTAB1 (A) VALUES(15);
    SAVEPOINT save1;
    --插入操作回滚
    INSERT INTO MYTAB1 (A) VALUES(16);
    ROLLBACK TO SAVEPOINT save1;
    --释放save1
    RELEASE SAVEPOINT save1;
    SELECT * FROM mytab1;
END;
call test_proc_savepoint;

5.12  Dynamic SQL

/*

4.12  Dynamic SQL

动态SQL语句,能够在执行期间构造SQL语句

缺点:

1.Opportunities for optimizations are limited.

2.The statement is potentially recompiled every time the statement is executed.

3.You cannot use SQLScript variables in the SQL statement.

4.You cannot bind the result of a dynamic SQL statement to an SQLScript variable.

5.You must be very careful to avoid SQL injection bugs that might harm the integrity or security of the database.

EXEC 执行SQL,不返回select结果集

EXECUTE IMMEDIATE 执行SQL,返回select结果集

语法规则:

EXEC ''

[INTO [DEFAULT ]]

[USING ]

[READS SQL DATA]

语法规则:

EXECUTE IMMEDIATE ''

[INTO [DEFAULT ]]

[USING ]

[READS SQL DATA]

APPLY_FILTER:

语法规则:

= APPLY_FILTER(, );

*/

--示例1:
--dynamic SQL
CREATE PROCEDURE test_proc_exec(IN i_tabname nvarchar(20))
AS BEGIN  
    DECLARE A INTEGER;
    DECLARE B INTEGER DEFAULT 10;
    --报错:incorrect syntax near "INTO"
    --EXEC 'SELECT MAX(A) FROM MYTAB1' INTO A;
    --SELECT :A FROM DUMMY;
    --指定默认值,
    --EXEC 'SELECT MAX(A) FROM MYTAB1' INTO A DEFAULT 2;
    --USING,INTO都不支持
    --EXECUTE IMMEDIATE 'SELECT MAX(A) FROM MYTAB1 WHERE A > ?' INTO A USING :B;
    --没有执行结果
    EXEC 'SELECT * FROM MYTAB1';
    EXEC 'SELECT * FROM ' || :i_tabname;
    --使用立即执行,可以看到查询结果
    EXECUTE IMMEDIATE 'SELECT * FROM MYTAB2';
END;
call test_proc_exec('MYTAB1');

--示例2:
--filter
/*
表:PRODUCTSALES内容
Tee Shirt;Plain;21
Tee Shirt;Lettered;22
Tee Shirt;Team logo;30
Hoodie;Plain;60
Hoodie;Lettered;65
Hoodie;Team logo;80
Ballcap;Plain;8
Ballcap;Lettered;40
Ballcap;Team logo;27
*/
CREATE PROCEDURE test_proc_filter (
    IN filter NVARCHAR(100),
    OUT products table(prodname NVARCHAR(50),description NVARCHAR(20)))
AS BEGIN
    temp_products = APPLY_FILTER(productsales,:filter);
    products = SELECT prodname,description FROM :temp_products;
END;
call test_proc_filter('PRODNAME LIKE ''Tee%''',?);

5.13 Exception Handling

/*

4.13 Exception Handling

异常处理

1.DECLARE EXIT HANDLER

DECLARE EXIT HANDLER FOR {,}...]

::= SQLEXCEPTION  

| SQL_ERROR_CODE

|   

系统View:M_ERROR_CODES

所有异常代码及描述

2.DECLARE CONTINUE HANDLER

支持处理错误后,继续执行程序

DECLARE CONTINUE HANDLER FOR {,}...]

::= SQLEXCEPTION  

| SQL_ERROR_CODE

|

3.DECLARE CONDITION

自定义error code

注:Please note the user-defined error codes must be within the range of 10000 to 19999.

语法规则:

DECLARE CONDITION [ FOR SQL_ERROR_CODE ];

4. SIGNAL and RESIGNAL

显示触发抛出异常

SIGNAL ( | SQL_ERROR_CODE )[SET MESSAGE_TEXT = '']

修改显示信息

RESIGNAL [ | SQL_ERROR_CODE ] [SET MESSAGE_TEXT = '']

*/

--异常代码及描述
SELECT * FROM M_ERROR_CODES;

--示例1:
--exception handler
CREATE PROCEDURE test_proc_exception_handler
AS BEGIN
    DECLARE A INTEGER default 0;
    DECLARE B INTEGER default 1;
    --捕捉所有SQL EXCEPTION
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    --只捕捉目标EXCEPTIOn
    --DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 304
    --两个系统参数,获取异常代码,描述 
    SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;  
    B = :B / :A;
END;
--执行procedure,不会异常报错
--结果显示:
--:1 :2
--304 division by zero undefined: cannot be divided by zero
call test_proc_exception_handler;

--示例2:
--不支持continue handler
CREATE PROCEDURE test_proc_exception_handler1
AS BEGIN
    DECLARE A INTEGER default 0;
    DECLARE B INTEGER default 1;
    --捕捉所有SQL EXCEPTION
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
         --两个系统参数,获取异常代码,描述 
         SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;  
    END;
    B = :B / :A;
    --捕获处理异常后,可以继续执行逻辑
    SELECT * FROM MYTAB1;
END;
--执行procedure,不会异常报错
call test_proc_exception_handler1;

--示例3:
--自定义异常
DO BEGIN
    --定义异常condition
    DECLARE invalid_input CONDITION FOR SQL_ERROR_CODE 11111;
END

DO BEGIN
    DECLARE a INTEGER default 2;
    --异常捕捉
    DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 11111
    SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;  
    IF :a = 2 THEN
         --抛出异常
         SIGNAL SQL_ERROR_CODE 11111 SET MESSAGE_TEXT = 'invalid input param';
         --SIGNAL invalid_input SET MESSAGE_TEXT = 'invalid input param';
    END IF;
END
--使用resignal,重设message
DO BEGIN
    DECLARE a INTEGER default 2;
    --异常捕捉
    DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 11111
    RESIGNAL SET MESSAGE_TEXT = 'error code:' || ::SQL_ERROR_CODE || ' error msg:invalid input param';
    IF :a = 2 THEN
         --抛出异常
         --error exception: error code:11111 error msg:invalid input param
         SIGNAL SQL_ERROR_CODE 11111 SET MESSAGE_TEXT = 'invalid input param';
    END IF;
END

5.14 Array Variables

/*

4.14 Array Variables

单个数据类型索引数组

1.定义array类型

语法结构:

DECLARE ARRAY;

::=

 DATE | TIME| TIMESTAMP | SECONDDATE | TINYINT

| SMALLINT | INTEGER | BIGINT | DECIMAL | SMALLDECIMAL

| REAL | DOUBLE | VARCHAR | NVARCHAR | VARBINARY | CLOB | NCLOB |BLOB

不支持对ARRAY指定静态大小,最大数组2^31次方

使用Array构造方法定义Array

DECLARE [{, }...] ARRAY

= ARRAY ( [{, }...] );

2.设置array值

’[’ ’]’ =

3.获取array值

必须使用:访问到

: ‘[‘ ’]’;  

4.ARRAY_AGG function

= ARRAY_AGG ( :.

    [ORDER BY { [ {, }… ]

    [ ASC | DESC ]

    [ NULLS FIRST | NULLS LAST ] , ... } ] )

5.TRIM_ARRAY Function

移除array后面记录

TRIM_ARRAY”(“:, ”)”

::=

::=

6.CARDINALITY Function

返回当前array的size,

CARDINALITY(:)

7. Concatenate Two Arrays

: “||” :

 | CONCAT'(': , : ')'

两个array连接

8.Array Parameters for Procedures and Functions

在procedure,function中使用数组类型参数

    1.Array input/output/inout parameter for procedures

    2.Array input parameter for SUDF/TUDF

    3.Array return type for SUDF

    4.Array parameter for library procedures/functions

    5.Array input parameter for anonymous block/embedded SQL function

    6.Array variables in DML/queries.

语法结构:

CREATE [OR REPLACE] PROCEDURE

[()]

[LANGUAGE ]

[SQL SECURITY ]

[DEFAULT SCHEMA ]

[READS SQL DATA ]

[WITH ENCRYPTION]

AS BEGIN

    [SEQUENTIAL EXECUTION]  

   

END

::= [{,}...]

::= [IN | OUT | INOUT]

::= [ARRAY] | |

语法结构:

CREATE FUNCTION

[()]

RETURNS

[LANGUAGE ]

[SQL SECURITY ]

[DEFAULT SCHEMA [DETERMINISTIC]]

[WITH ENCRYPTION]

AS BEGIN

     

END

::= [{,}...]

::= [IN]

::= [ARRAY] | |

::=

::= [{, }...]

::= [ARRAY]

使用array参数限制:

    1.LOB type array parameter is not supported. 

    2.DEFAULT VALUE for an array parameter is not supported.

    3.Using an array parameter in the USING clause of Dynamic SQL is not supported.

*/

--创建function,两个数相加
CREATE FUNCTION test_funct_sudf_add(IN in_val1 INTEGER,IN in_val2 INTEGER)
RETURNS res_val INTEGER
AS BEGIN
    res_val = :inval1 + :inval2;
END;

DO BEGIN
    --定义array值
    DECLARE array1 INTEGER ARRAY;
    DECLARE array2 INTEGER ARRAY = ARRAY(1,2,3);
    DECLARE tab table(I INTEGER);
    DECLARE numb INTEGER;
    --设置array值
    array1[1] = 10;
    array1[test_func_sudf_add(1,2)] = 20;

    --获取array值
    select :array1[3] FROM dummy;

    --array_agg
    tab = SELECT * FROM MYTAB4;

    --array1 = ARRAY_AGG(:tab.I);
    --使用ORDER BY,
    array1 = ARRAY_AGG(:tab.I ORDER BY I DESC);

    --trim array
    --移除后面5个array记录
    array1 = TRIM_ARRAY(:array1,5);

    --获取当前array size
    numb = CARDINALITY(:array1);

    --连接两个array
    array1 = :array1 || :array2;
    --array1 = CONCAT(:array1,:array2);

    select :numb from dummy;
    --UNNEST function,将array转换为table
    tab = UNNEST(:array1) AS (I);
    select * from :tab;
END;

5.15 SQL Injection Prevention Functions

/*

4.15 SQL Injection Prevention Functions

SQL注入检查function

ESCAPE_SINGLE_QUOTES(string_var)

ESCAPE_DOUBLE_QUOTES(string_var)

IS_SQL_INJECTION_SAFE(string_var[, num_tokens])

检查字符串中单词数是否符合num_tokens,以空格分隔算一个单词

num_tokens:允许在string_var中最大tokens目标字符数,默认为1;

没有SQL注入返回1,否则返回0;

*/

DO BEGIN
    SELECT ESCAPE_SINGLE_QUOTES('Str''ing') FROM DUMMY;
    SELECT ESCAPE_DOUBLE_QUOTES('TAB"LE')  FROM DUMMY;
END;

DO BEGIN
    --return 0
    SELECT IS_SQL_INJECTION_SAFE('select * from mytab1 where a = 1 or 1 = 1') FROM DUMMY;
    --return 0
    SELECT IS_SQL_INJECTION_SAFE('select * from mytab1 where a = 1') FROM DUMMY;
    --return 1
    SELECT IS_SQL_INJECTION_SAFE('able book',2) FROM DUMMY;
    --return 1
    SELECT IS_SQL_INJECTION_SAFE('able',2) FROM DUMMY;
END;

5.16 Explicit Parallel Execution

/*

4.16 Explicit Parallel Execution

显示并行执行

到目前为止,隐式并行化已经应用于表变量赋值以及相互独立的只读procedure调用。

DML语句和读写procedure调用必须按顺序执行。从现在起,可以使用并行执行块来并行执行独立的DML语句和读写procedure调用

语法规则:

BEGIN PARALLEL EXECUTION

   

END

DML语句并行执行只支持column类型table

注:只有以下操作可以在读写procedure,parallel execution块中使用

1.DML

2.Imperative logic

3.Autonomous transaction

4.Implicit SELECT and SELECT INTO scalar variable

*/

--创建column类型table
CREATE COLUMN TABLE TEST_COL_TAB1(A INT);
CREATE COLUMN TABLE TEST_COL_TAB2(A INT);

--插入数据
INSERT INTO TEST_COL_TAB1(A) VALUES(11);
INSERT INTO TEST_COL_TAB1(A) VALUES(10);
INSERT INTO TEST_COL_TAB2(A) VALUES(11);
INSERT INTO TEST_COL_TAB2(A) VALUES(12);

CREATE PROCEDURE test_proc_parallel_exec
AS BEGIN
    BEGIN PARALLEL EXECUTION
         --报错:Concurrently two or more write operations to the same object are not allowed
         --对同一表不能有两个操作
         --INSERT INTO TEST_COL_TAB2(A) VALUES(11);
         --INSERT INTO TEST_COL_TAB2(A) VALUES(12);
         --UPDATE TEST_COL_TAB2 SET A = A + 1;
         --UPDATE TEST_COL_TAB2 SET A = A + 2;
         UPDATE TEST_COL_TAB1 SET A = A + 1;
         UPDATE TEST_COL_TAB2 SET A = A + 1;
    END;
END;

5.17 Recursive SQLScript Logic

/*

4.17 Recursive SQLScript Logic

递归逻辑

SQLScript支持function,procedure递归调用本身

注:最大递归层数32

*/

--示例1:
--创建递归function
CREATE FUNCTION test_func_recursive(i INTEGER)
RETURNS j INTEGER
AS BEGIN
    IF  :i < 1 THEN
         j = 1;
    ELSE
         i = :i - 1;
         --不支持报错:invalid name of function or procedure: TEST_FUNC_RECURSIVE
         j = :i + test_func_recursive(:i);
    END IF;
END;
SELECT test_func_recursive(2) FROM DUMMY;

--示例2:
--使用function header 定义递归
--定义function header
CREATE FUNCTION test_func_recursive(i INTEGER) RETURNS j INTEGER AS HEADER ONLY;
--使用alter,实现function
--报错:recursive procedure/function not allowed
ALTER FUNCTION test_func_recursive(i INTEGER)
RETURNS j INTEGER
AS BEGIN
    IF  :i < 1 THEN
         j = 1;
    ELSE
         i = :i - 1;
         j = :i + test_func_recursive(:i);
    END IF;
END;
SELECT test_func_recursive(2) FROM DUMMY;