HANA SQL Script学习（4）：Declarative SQLScript Logic

4. Declarative SQLScript Logic

4.1 Table Parameter

/*

3.Declarative SQLScript Logic

3.1Table Parameter

::= [IN|OUT] {|| }

::=

::= TABLE()

::= TABLE(...)

*/

--Table Parameter
--使用
create procedure test_proc6( IN inputtab TABLE(I INT),OUT outputtab TABLE (I INT,J DOUBLE))
as begin
    outputtab = select i,i*1.2 as j from :inputtab;
end

--代码块
do begin
    declare outtab table(I int,J double);
    declare intab table(I int);
    intab = select I from mytab4;
    call test_proc6(:intab,outputtab=>outtab);
    select * from :outtab;
end

--使用
create type tt_inputtab as table(I int);
create type tt_outputtab as table(I int,j double);
create procedure test_proc7(IN inputtab tt_inputtab, OUT outputtab tt_outputtab)
as begin
    outputtab = select i,i*1.5 as j from :inputtab;
end

--代码块
do begin
    declare outtab table(I int,J double);
    declare intab table(I int);
    intab = select I from mytab4;
    call test_proc7(:intab,outputtab=>outtab);
    select * from :outtab;
end

--使用
/*
使用限制：
1.the any_table_type parameter cannot be used within anonymous blocks, other languages or outside the scope of SQLScript
2.any_table_type parameters are supported only as input parameter of table UDFs, but not as return parameters
3.scalar UDFs do not support any_table_type parameters.
4.If an output any table type parameter cannot be resolved during procedure creation
(for example, out_any_table = select * from in_any_table), the procedure cannot be called inside SQLScript.
*/
create function func_test1
as begin
    --在function中创建procedure
    create procedure test_proc8(in ott table(...))
    as begin   
     ott = select * from mytab4;
    end;
end
--创建不了？
create procedure test_proc9( IN inputtab table(...),OUT outputtab table(...) )
as begin
    outputtab = select * from :inputtab;
end

--代码块
do begin
    declare intab table(I int,J double);
    intab = select I,I*1.3 AS J from mytab4;
    call test_proc8(:intab,outputtab=>outtab);
    select * from :outtab;
end

4.2 Table Variable Type Definition

/*

3.2Table Variable Type Definition

DECLARE [{, }...] [CONSTANT] {TABLE ()|} [ ]

::= { DEFAULT | '=' } { | | | }

*/

drop procedure test_proc10;
--,之前已经创建tt_inputtab
--create type tt_inputtab as table(I int);
create procedure test_proc10()
as begin
    --table()
    declare t_test1 table(n int);
    --
    declare t_test2 tt_inputtab;
    --default,=，设置默认值
    declare t_test3 tt_inputtab default select I from mytab4;
    declare t_test4 tt_inputtab = select I from mytab4;
    t_test1 = select I as n from mytab4;
    t_test2 = select I from mytab4;
    select * from :t_test1;
    select * from :t_test2;
    select * from :t_test3;
    select * from :t_test4;
end
call test_proc10();

--使用constant定义常量table类型,必须指定默认值
--创建出错
DECLARE t_test5 CONSTANT TABLE(I INT) DEFAULT SELECT * FROM mytab4;
--作用域，定义table变量的作用域
drop procedure test_proc11;
CREATE PROCEDURE test_proc11 (OUT outTab TABLE(n int))
 LANGUAGE SQLScript READS SQL DATA
AS BEGIN   
    --Binding Table Variables
    temp = SELECT 1 as n FROM DUMMY ;       
    BEGIN      
     temp = SELECT 2 as n FROM DUMMY ;           
     outTab = Select * from :temp;   
    END;  

    --Referencing Variables,使用<:var>
    outTab = Select * from :temp;
END;
--输出2
call test_proc11(?);

CREATE PROCEDURE test_proc12(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  
     --定义temp只在block中有效
     DECLARE temp TABLE (n int);   
     temp = SELECT 2 as n FROM DUMMY ;           
     outTab = Select * from :temp;   
    END;   
    outTab = Select * from :temp;
END;
--输出1
call test_proc12(?);

4.3 Column View Parameter Binding

/*

3.3 Column View Parameter Binding

SELECT * FROM ( );

::=

::= [{,}…}]

::= =>

::= {PLACEHOLDER. | HINT. | }

*/

--创建column view??
CREATE PROCEDURE 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;

4.4 Map Merge Operator

/*

3.4 Map Merge Operator

= MAP_MERGE(,

    (. [ {,.} … ] [, ])

::= [{, } …]

= | | |

*/

drop function test_mapper;
--mapperfunction
CREATE FUNCTION test_mapper (IN a INT)
 RETURNS TABLE (COL_A INT)
AS BEGIN   
 ot = SELECT :a AS COL_A from dummy;   
 RETURN :ot;
END;

--使用for循环
--执行报错？
DO (OUT ret_tab TABLE(col_a INT)=>?)
BEGIN     
    DECLARE i int;     
    DECLARE varb int;    
    t = SELECT * FROM mytab4;     
    FOR i IN 1 .. 10 DO     
         varb = :t.i[:i];
        CALL test_mapper(:varb,out_tab);           
        ret_tab = SELECT * FROM :out_tab           
        UNION SELECT * FROM :ret_tab;     
    END FOR;
END;

--使用map merge
--?invalid name of map_merge,不支持
DO (OUT ret_tab TABLE(col_a INT)=>?)
BEGIN  
    t = SELECT * FROM mytab4;   
    ret_tab = MAP_MERGE(:t, test_mapper(:t.col_a));
END;

4.5 Map Reduce Operator

/*

3.5 Map Reduce Operator

MAP_REDUCE(, , )

::= () group by as

::= () | ()

::=

--SQL 语句
tab   = select A, B, C from T where A = 1;
tab2  = select C from :tab where  C = 0;

--执行时，组合成下面语句：
select C from (select A,B,C from T where A = 1) where C=0;

--使用HINT(NO_INLINE),阻止表变量内联，
tab = select A, B, C from T where A = 1 WITH HINT(NO_INLINE);
tab2  = select C from :tab where  C = 0;

--创建procInner,procCaller
--调用procCaller时，两个表独立执行
CREATE PROCEDURE procInner (OUT tab2 TABLE(I int))
 LANGUAGE SQLSCRIPT READS SQL DATA
AS BEGIN    
    tab2 = SELECT I FROM mytab4;
END;

CREATE PROCEDURE procCaller (OUT table2 TABLE(I int))
LANGUAGE SQLSCRIPT READS SQL DATA
AS BEGIN         
  call procInner (outTable);    
  table2 = select I from :outTable where I > 10;
END;

--procInner定义为HINT(INLINE)
--调用时两个表连接执行
drop procedure procInner;
CREATE PROCEDURE procInner (OUT tab2 TABLE(I int))
 LANGUAGE SQLSCRIPT READS SQL DATA
AS BEGIN    
 tab2 = SELECT I FROM mytab4 WITH HINT (INLINE);
END;

--procInner参数tab2链接procCaller中outtable中执行
SELECT I FROM (SELECT I FROM T WITH HINT (INLINE)) where I > 10;

--invalid hint: incorrect syntax near "indexserver"
select * from mytab4 with hint(ROUTE_TO('indexserver'));
select * from mytab4 with hint(ROUTE_TO('indexserver','computeserver')); 
select * from mytab4 with hint(NO_ROUTE_TO('indexserver'));
select * from mytab4 with hint(NO_ROUTE_TO('indexserver','computeserver'));

--error:insufficient privilege: Not authorized
select preferred_routing_volumes, * from sys.m_sql_plan_cache_ where statement_string like '%select * from mytab%';