精华内容
下载资源
问答
  • HANA 存储过程

    2018-03-29 15:34:00
    You can develop secure procedures using SQLScript in SAP HANA by observing the following recommendations. Using SQLScript, you can read and modify information in the database. In some cases, depen...

     

    You can develop secure procedures using SQLScript in SAP HANA by observing the following recommendations.

    Using SQLScript, you can read and modify information in the database. In some cases, depending on the commands and parameters you choose, you can create a situation in which data leakage or data tampering can occur. To prevent this, SAP recommends using the following practices in all procedures.
    • Mark each parameter using the keywordsINorOUT. Avoid using theINOUTkeyword.
    • Use theINVOKERkeyword when you want the user to have the assigned privileges to start a procedure. The default keyword,DEFINER, allows only the owner of the procedure to start it.
    • Mark read-only procedures usingREADS SQL DATAwhenever it is possible. This ensures that the data and the structure of the database are not altered.
      Tip Another advantage to usingREADS SQL DATAis that it optimizes performance.
    • Ensure that the types of parameters and variables are as specific as possible. Avoid usingVARCHAR, for example. By reducing the length of variables you can reduce the risk of injection attacks.
    • Perform validation on input parameters within the procedure.

    我是说HANA ,为什么每次后面都加那么一大串。  language sqlscript sql security definer reads sql data 

    这个可以直接创建的SQL窗口里写也行。不用那么麻烦。

     

    我都不知道这下面这个什么意思。奇奇怪怪的没明白LOGIC。 很可能是随便复制的。

    create procedure "_SYS_BIC"."DEMO/DEMO_SQL_CV/proc" ( IN IP_CGDDH VARCHAR(100),  OUT var_out "_SYS_BIC"."DEMO/DEMO_SQL_CV/proc/tabletype/VAR_OUT" ) 
    language sqlscript sql security definer reads sql data

    as /********* Begin Procedure Script ************/ BEGIN SELECT CURRENT_DATE,"_BIC_Z0CGDDH" AS DATE_V FROM "_SYS_BIC"."DEMO/DEMO_CV_IP" where "_BIC_Z0CGDDH"= 'ZCCG-1403270935500D'; var_out = SELECT A."/BIC/Z0CGDDH" AS CGDDH, A."/BIC/Z0DDZT" AS DDZT, B."/BIC/Z0SL" AS SL, B."/BIC/Z0JE" AS JE FROM "SAPABAP1"."/BIC/AZEZJTO0100" A LEFT JOIN "SAPABAP1"."/BIC/AZEZJTO0200" B ON A."/BIC/Z0CGDDH"=B."/BIC/Z0CGDDH" WHERE B."/BIC/Z0JE" IS NOT NULL AND A."/BIC/Z0CGDDH" IN(:IP_CGDDH) ; END /********* End Procedure Script ************/

     

     权限过滤,有空看看。

    CREATE PROCEDURE "_SYS_BIC"."Purchase::ZAUTH_DYN_FILTER" (IN AUTH_IOBJ NVARCHAR(12) , MD5_CODE NVARCHAR(50) , OUT AUTH_VALUE NVARCHAR(4000) ) 
        LANGUAGE SQLSCRIPT
        SQL SECURITY INVOKER 
        --DEFAULT SCHEMA <default_schema_name>
        READS SQL DATA AS
    BEGIN
    /***************************** 
        Write your procedure logic 
     *****************************/
    --根据MD5码 得到值列表。 
        DECLARE val NVARCHAR(5000) ; 
        --7380563cdbb1061c70bc76c19ef6d9a2
        DECLARE CURSOR c_cursor FOR
        select * from  "SAPABAP1"."/BIC/OHZBWOHD2" where "USERNAME_MD5" = :MD5_CODE  and
        FILTER_COLOMN = :AUTH_IOBJ ORDER BY "VALUE_LOW" asc ;
        val:= '';
        FOR r1 AS c_cursor DO 
        --val:= :val || '''' || '''' || r1.VALUE_LOW  || '''' || '''' || ',';
        val:=  :val || '''' ||  r1.VALUE_LOW  || ''''  || ',';
        IF r1.VALUE_LOW = '*' THEN 
        val:=  '''' || '*' || '''';
        BREAK ;
        END IF ;
        END FOR;
    --    AUTH_VALUE := :val ;
        select RTRIM (:val,',') INTO AUTH_VALUE from dummy;
        --AUTH_VALUE := :val || '''' ;
        CLOSE c_cursor; 
        
    END;

     

    这是一个权限判断的存储过程。想必还是有用,也没看懂。

    CREATE PROCEDURE "_SYS_BIC"."Purchase::ZAUTH_INPUT_CHECK" (IN AUTH_IP NVARCHAR(500) , ZIP_MD5 NVARCHAR(100) ,ZIP_IOBJ NVARCHAR(20) ,
                                                        OUT VAR_OUT NVARCHAR(4000) , OUT VAR_FLAG NVARCHAR(3)) 
        LANGUAGE SQLSCRIPT
        SQL SECURITY INVOKER 
        --DEFAULT SCHEMA <default_schema_name>
        READS SQL DATA AS
    BEGIN
    /***************************** 
        Write your procedure logic 
     *****************************/
     DECLARE SNO INT := 1  ;
     DECLARE ENO INT := 1;
     DECLARE FLAG INT := 1 ;
     DECLARE SVAR NVARCHAR(4000) ;
     DECLARE SCONST NVARCHAR(10);
    ------------------------------------------------------------------------------------
    DECLARE CUSTOMCONDITION CONDITION FOR SQL_ERROR_CODE 10001;/* Custom Error Code = 10001*/
     /*User Defined exception handler */
    DECLARE EXIT HANDLER FOR CUSTOMCONDITION RESIGNAL;
     --SELECT ::SQL_ERROR_CODE AS "Error Code", ::SQL_ERROR_MESSAGE AS "Error Message" FROM DUMMY;
    /*权限检查段,根据输入的MD5获取权限值*/
    ------------------------------------------------------------------------------------
    SCONST := '''' || '*' || '''';
    VAR_OUT := '';
    
    call "_SYS_BIC"."Purchase::ZAUTH_DYN_FILTER"(:ZIP_IOBJ,:ZIP_MD5,:SVAR);
    
     IF :SVAR = '' THEN
     SIGNAL CUSTOMCONDITION SET MESSAGE_TEXT = '^_^权限不足,请联系管理员增加权限!^_^';
     return;
     END IF;
    
    IF :AUTH_IP = '' OR :AUTH_IP = :SCONST  THEN  
      VAR_OUT := :SVAR ;  
    ELSE 
    /*对比用户输入和存储过程查到的数据*/
    IF :SVAR = :SCONST THEN VAR_OUT := :AUTH_IP;
    ELSE
     AUTH_IP := :AUTH_IP || ',';
     WHILE :ENO  <> 0   DO  
     ENO := LOCATE(:AUTH_IP , ',',:SNO) ;
     FLAG := LOCATE(:SVAR , SUBSTRING(:AUTH_IP , :SNO , :ENO-:SNO)) ;
     IF :FLAG = 0 THEN
     SIGNAL CUSTOMCONDITION SET MESSAGE_TEXT = '^_^权限不足,请联系管理员增加权限!^_^';
     return;
     END IF;
     SNO := :ENO+1 ;
     END WHILE;
     
     select RTRIM (:AUTH_IP,',') INTO VAR_OUT from dummy;
     END IF;
     END IF ;
    
     
     
     IF :VAR_OUT = :SCONST THEN
     VAR_FLAG :=  '%'  ;
     END IF ;
     
     
    END;

     

    转载于:https://www.cnblogs.com/sakura3/p/8670124.html

    展开全文
  • ABAP调用hana存储过程
  • SAP HANA存储过程

    千次阅读 2016-12-16 12:48:38
    SAP HANA存储过程

    闲着写着玩

    别在意


    展开全文
  • 最近测试了用HANA存储过程写取数SQL,SAP中调用获取数据再做处理。 这种方式能大幅优化提升代码运行效率。

    最近测试了用HANA存储过程写取数SQL,SAP中调用获取数据再做处理。
    这种方式能大幅优化提升代码运行效率。
    首先是存储过程代码

    
    CREATE TYPE "SAPHANADB"."tt_structure" 
    AS TABLE ( "BUKRS" VARCHAR(4) CS_STRING,
    	 "GJAHR" VARCHAR(4) CS_STRING,
    	 "BELNR" VARCHAR(10) CS_STRING,
    	 "BUZEI" VARCHAR(3) CS_STRING,
    	 "POPER" VARCHAR(3) CS_STRING,
    	 "BUDAT" VARCHAR(8) CS_STRING,
    	 "LIFNR" VARCHAR(10) CS_STRING,
    	 "RACCT" VARCHAR(10) CS_STRING,
    	 "FISCYEARPER" VARCHAR(7) CS_STRING,
    	 "HSL" DECIMAL(23,2) CS_FIXED );
    	 
    CREATE Procedure P_TEST01
    (   I_BUKRS         varchar(4),
        I_BUDAT         varchar(8),
        I_AUGDT         varchar(8),
        i_MANDT		    varchar(3),
        OUT O_TAB1      tt_structure
    ) Language SQLScript
    as
    Begin
    
    O_TAB1 = 
    WITH  ACD AS (
      SELECT A.RCLNT,
             A.RBUKRS,
             B.gjahr,
             B.belnr,
             B.buzei,
             A.poper,
             A.fiscyearper,
             A.budat,
             '00000000' AS augdt,
             A.zuonr,
             A.lifnr,
             A.racct,
             CASE WHEN ifnull(A.zuonr,'')<>'' THEN lpad( ltrim( A.zuonr,' ' ), 10, '0' )
             ELSE '' END AS LIFNR_L,
             substring( A.zuonr,1,10 )             as EBELN_L,
             A.EBELP,
             A.HSL
        FROM ACDOCA AS A 
        INNER JOIN BSIS AS B
          ON A.RBUKRS = B.BUKRS  AND A.gjahr  = B.gjahr
         AND A.belnr  = B.belnr  AND A.buzei  = B.buzei
         AND A.RCLNT  = B.MANDT
       WHERE A.RBUKRS = :I_BUKRS AND A.budat <= :I_BUDAT
         AND A.RACCT LIKE '260%' AND A.RCLNT = :I_MANDT
      UNION
      SELECT A.RCLNT,
             A.RBUKRS,
             B.gjahr,
             B.belnr,
             B.buzei,
             A.poper,
             A.fiscyearper,
             A.budat,
             B.augdt,
             A.zuonr,
             A.lifnr,
             A.racct,
             CASE WHEN ifnull(A.zuonr,'')<>'' THEN lpad( ltrim( A.zuonr,' ' ), 10, '0' )
             ELSE '' END AS LIFNR_L,
             substring( A.zuonr,1,10 )             as EBELN_L,
             A.EBELP,
             A.HSL
        FROM ACDOCA AS A 
        INNER JOIN bsas AS B
          ON A.RBUKRS = B.BUKRS  AND A.gjahr  = B.gjahr
         AND A.belnr  = B.belnr  AND A.buzei  = B.buzei
         AND A.RCLNT  = B.MANDT
      WHERE A.RBUKRS = :I_BUKRS AND A.budat <= :I_BUDAT
         AND B.AUGDT  > :I_AUGDT
         AND A.RACCT LIKE '260%' AND A.RCLNT = :I_MANDT
      ),
       DT AS ( 
        SELECT A.rbukrs,
               A.gjahr,
               A.belnr,
               A.buzei,
               A.poper,
               A.fiscyearper,
               A.budat,
               A.racct,
               A.hsl,
               CASE WHEN ifnull(a.lifnr,'')<>'' THEN a.lifnr
                    WHEN ifnull(e1.lifnr,'')<>'' THEN e1.lifnr
                    WHEN ifnull(ek.lifnr,'')<>'' THEN ek.lifnr
                    ELSE a.lifnr_l
               END AS lifnr
          FROM  ACD AS a
                    INNER JOIN bkpf ON a.rbukrs = bkpf.bukrs
                                   AND a.belnr = bkpf.belnr
                                   AND a.gjahr = bkpf.gjahr
                                   AND A.RCLNT = bkpf.MANDT
             LEFT JOIN acdoca AS e1 ON a.rbukrs = e1.rbukrs
                                   AND a.belnr  = e1.belnr
                                   AND a.gjahr  = e1.gjahr
                                   AND ifnull(e1.lifnr,'')<>''
                                   AND a.RCLNT  = e1.RCLNT
               LEFT JOIN ekko AS ek ON a.ebeln_l = ek.ebeln 
         WHERE A.RCLNT = :I_MANDT
       )
       SELECT distinct
               DT.rbukrs AS bukrs,
               DT.GJAHR,
               DT.BELNR,
               DT.BUZEI,
               DT.POPER,
               DT.BUDAT,
               DT.lifnr,
               DT.RACCT,
               DT.FISCYEARPER,
               DT.HSL
         FROM  DT
         ORDER BY  DT.rbukrs, DT.gjahr, DT.belnr, DT.buzei;
    
    END;
    

    调用测试:

    call P_TEST01('1000','20210101','20210101','300',NULL);
    

    测试数据后在SAP层写调用

        DATA(lv_mandt) = sy-mandt.
        DATA: ls_data TYPE ty_menge.
    
        DATA: lv_option TYPE string.
        DATA: lv_sts TYPE string.
    
        lv_option = ' CALL "P_TEST01" (  '.
        lv_sts = '  ' && i_bukrs && ' ,'.
        lv_option = lv_option && lv_sts.
        lv_sts = '  ' && i_budat && ' ,'.
        lv_option = lv_option && lv_sts.
        lv_sts = '  ' && i_augdt && ' ,'.
        lv_option = lv_option && lv_sts.
        lv_sts = '  ' && sy-mandt && ' ,'.
        lv_option = lv_option && lv_sts.
        lv_sts = ' NULL ) '.
        lv_option = lv_option && lv_sts.
    
        TRY.
            DATA(lo_stmt) = NEW cl_sql_statement(  ).
    
            DATA(lo_res) = lo_stmt->execute_query( statement = lv_option  ).
            lo_res->set_param_table( itab_ref = REF #( e_tab ) ).
            "read the result into the internal table
            lo_res->next_package( ).
          CATCH cx_sql_exception INTO DATA(lx_02).
            DATA(lv_exception) = | An exception occurred with SQL_MESSAGE = { lx_02->sql_message }|.
            e_message = lv_exception.
        ENDTRY.
        
    
    展开全文
  • SAP Hana存储过程

    2021-03-20 17:06:10
    SAP Hana存储过程,循环,实例

    官方文档

    SAP Hana存储过程官方链接

    标准语法

    CREATE [ OR REPLACE ] PROCEDURE <proc_name> 
       [ (<input_output_parameter_clause>) ]
       [ LANGUAGE <lang> ]
       [ SQL SECURITY <mode> ]
       [ DEFAULT SCHEMA <default_schema_name> ]
       [ READS SQL DATA [ WITH RESULT VIEW <view_name> ] ] 
       [ <variable_cache_clause> ]
       [ DETERMINISTIC ] 
       [ WITH ENCRYPTION ]
       [ AUTOCOMMIT DDL { ON | OFF } ]
       AS 
         { BEGIN [ SEQUENTIAL EXECUTION ]
            <statement_body>
           END
           | HEADER ONLY }
    

    案例说明

    create procedure sp_test (in date varchar(10),out a1 t_a1)  --参数定义,格式为:(IN|OUT|INOUT VAR_NAME VAR_TYPE),多个参数之间用,相隔,可不定义;注意:定义了with result view  必须有out参数,且必须为table类型
    	language sqlscript 		--指定存储过程实现的程序语言,默认为: SQLSCRIPT,可不定义
    	sql security invoker 	--指定存储过程的安全模式,默认: DEFINER,可不定义
    	reads sql data 			--存储过程为只读的,不能包含DDL与DML(INSERT、UPDATE、DELETE)语句,如果调用其他存储过程,则被调用过程也是只读的。可不定义
    	with result view test2 	--将只读取存储过程的输出看做结果视图,可以被其他查询SQL用来查询,此时存储过程就像一个表或视图。可不定义
    as 
    begin 	
    	a1 = 
    		with a as (
    		select * from a1 where date = :date  --引用变量需要使用: + 变量名称
    		)
    		SELECT * FROM a
    	;
    end;
    

    定义变量

    create procedure sp_test
    as 
    begin 	
    	declare a int default 2;  --标准变量使用前必须声明
    	a := 1;  --标准变量赋值必须使用:=
    	a1 = SELECT * FROM a1;  --表变量a1不需要声明即可使用,赋值必须使用=
    end;
    

    循环

    while循环

    CREATE PROCEDURE SP_TEST_WHILE
    LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS
    	 CURR_DATE VARCHAR(10) := YEAR(CURRENT_DATE)||'-01-31';  --这里不使用传参,直接赋值参数CURR_DATE
    BEGIN
    		DELETE FROM A1 ;  --目标表A1
    		WHILE :CURR_DATE <= CURRENT_DATE DO --判断条件
    			INSERT INTO A1 (
    				with a as (
    				select :CURR_DATE date from dummy
    				)
    				select date from a
    			);
    			CURR_DATE := LAST_DAY(ADD_MONTHS(:CURR_DATE,1));  --变量CURR_DATE递增
    		END WHILE;
    END;
    

    if

    CREATE PROCEDURE SP_TEST_IF
    LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS
    	 CURR_DATE VARCHAR(10) := '2021-09-30';  --这里不使用传参,直接赋值参数CURR_DATE
    BEGIN
    		DELETE FROM A1 ;  --目标表A1
    		IF :CURR_DATE <= CURRENT_DATE THEN --判断条件
    			INSERT INTO  A1 (
    			with a as (
    			select :CURR_DATE date from dummy
    			)
    			select date from a
    			);
    		ELSEIF :CURR_DATE = '2021-08-31' THEN 
    			INSERT INTO  A1 VALUES('2021-08-31');
    		ELSE INSERT INTO  A1 VALUES('2021-12-31');	
    		END IF;
    END;
    

    for循环

    CREATE PROCEDURE SP_TEST_FOR
    LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS
    BEGIN
    	DECLARE CURSOR CUR FOR SELECT * FROM A1;  --声明游标变量CUR
    	FOR R AS CUR DO
    		INSERT INTO  A1 VALUES(:R.DATE);  --R.DATE是因为A1的字段为DATE
    	END FOR;
    END;
    

    具体案例

    /*
    创建可直接查询的存储过程
    */
    --1、创建table类型数据类型
    drop type t_a1;   		--删除table类型数据类型
    create type t_a1 as 
    table(
    	date varchar(10)
    );
    --2、创建存储过程
    drop procedure sp_test;
    create procedure sp_test (in date varchar(10),out a1 t_a1)  --定义了with result view  必须有out参数,且必须为table类型
    	language sqlscript 		--指定存储过程实现的程序语言,默认为: SQLSCRIPT
    	sql security invoker 	--指定存储过程的安全模式,默认: DEFINER
    	reads sql data 			--存储过程为只读的,不能包含DDL与DML(INSERT、UPDATE、DELETE)语句,如果调用其他存储过程,则被调用过程也是只读的。设置参数会有特定的优化
    	with result view test 	--将只读取存储过程的输出看做结果视图,可以被其他查询SQL用来查询,此时存储过程就像一个表或视图
    as 
    begin 	
    	a1 = --表变量类型赋值不需要加:
    		--具体逻辑
    		with a as (
    		select * from a1 where date = :date
    		)
    		SELECT * FROM a
    	;
    end;
    call sp_test('2021-05-31',null);--如果不存储结果时,需传入NULL或者?
    --3、查询结果视图
    select * from test with parameters ('placeholder' = ('$$date$$' ,'2021-04-30' )
    --,'placeholder' = ('$$date1$$' ,'2021-04-30' ) 若有多个参数,加逗号即可
    );
    

    相关定义在hana studio中查看

    • 存储过程:实例-catalog-用户-procedures
      在这里插入图片描述
    • 表变量:实例-catalog-用户-procedures-table types
      在这里插入图片描述
    • 结果视图:实例-catalog-用户-column view
      在这里插入图片描述
    展开全文
  • 通过创建代码、生成的结果对hana存储过程和计算视图进行对比和说明,更深入理解hana存储过程及计算视图
  • HANA 存储过程 YTD

    2021-07-14 12:43:31
    建立一个存储过程,每个月计算YTD,如何计划执行该存储过程。 输入时间参数201406-201506 求YTD YTD的概念:YTD是year to date的缩写)意思是:到现在为止的一年,最近的一年。 用图形的方式: 首先建立时间表根据...
  • SAP HANA存储过程参数使用默认值

    千次阅读 2017-06-02 14:11:58
    SAP HANA存储过程参数使用默认值 CREATE PROCEDURE MYPROC(IN P1 INT, IN P2 INT DEFAULT 1, OUT out1 DUMMY) AS  BEGIN   out1 = SELECT :P1 + :P2 AS DUMMY FROM DUMMY;  END;   调用方式: ...
  • ABAP使用AMDP调用HANA存储过程

    千次阅读 2018-12-14 12:29:53
    更多内容关注公众号:...编写存储过程以从HANA视图读取数据并将数据插入HANA表。 存储库存储过程'DATA_PERSIST'是在TEST包下通过HANA开发透视图创建的。此存储过程HANA视图读取数据,并将数据进入“XYZ”模式下...
  • SAP HANA存储过程样例(Table Type输出) CREATE PROCEDURE getOutput( IN cnt INTEGER, IN currency VARCHAR(3),   OUT output_pubs tt_publishers,   OUT
  • HANA 存储过程 基础篇

    万次阅读 2017-07-21 16:03:07
    下面是我学习hana 存储过程时的一些总结 希望能与大家探讨一番  如果有错误的 希望各位能指出 我会修订好的 1. table_type 1.1 自定义table_type  Synatx: CREATE TYPE <type_name> AS TABLE ([{...
  • 那么这篇文章我们一起看看在NW 7.4里通过ABAP直接调用HANA存储过程。   HANA的一个利器是各种模型视图,但是HANA的主要编程范式为将逻辑推向HANA一层,所以很多复杂的处理逻辑就要借助于存储过程。在NW 7.4之前...
  • ALTER SESSION SET CURRENT_SCHEMA = SqlScriptDocumentation; DROP PROCEDURE addDiscount; CREATE PROCEDURE addDiscount( IN it_books tt_sales_books, OUT ot_books tt_sales_books) LANGUAGE SQ...
  • -- run 1_datatype_data.sql before running this script ALTER SESSION SET CURRENT_SCHEMA = SqlScriptDocumentation; drop procedure while_proc; CREATE PROCEDURE while_proc LANGUAGE SQLSCRIPT AS ...
  • -- run 1_datatype_data.sql before running this script ALTER SESSION SET CURRENT_SCHEMA = SqlScriptDocumentation; drop procedure cursor_proc; CREATE PROCEDURE cursor_proc LANGUAGE SQLSCRIPT AS...
  • -- run 1_datatype_data.sql before running this script ALTER SESSION SET CURRENT_SCHEMA = SqlScriptDocumentation; drop procedure dynamic_sql_proc; CREATE PROCEDURE dynamic_sql_proc LANGUAGE S...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 3,136
精华内容 1,254
关键字:

hana存储过程