SAP-HANA 自定义存储过程_hana declare-优快云博客

本文链接：https://blog.youkuaiyun.com/bj_chengrong/article/details/113257689

本文介绍了一个使用SQLScript编写的存储过程示例，该存储过程用于从数据库中选取特定的数据记录并进行处理，最终返回处理后的结果。具体操作包括遍历记录、字符串拼接等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

DROP PROCEDURE ""."ZCONCAT_EKKO_EBN";
CREATE PROCEDURE ""."ZCONCAT_EKKO_EBN"(OUT RESULT ""."OUT_SPLIT_EK_EBN") 
LANGUAGE SQLSCRIPT AS
BEGIN
    DECLARE _ZHETONG NVARCHAR(1000) ARRAY;
    DECLARE _ZEBELN  NVARCHAR(5000) ARRAY;
    DECLARE _ZHT NVARCHAR(1000); 
    
    DECLARE _INDEX INTEGER := 1;
    DECLARE _IND1 INTEGER := 1;
    
    DECLARE CURSOR C_CURSOR1 FOR                
      SELECT DISTINCT ZHETONG,EBELN FROM "CTNT_ECC"."EKKO" WHERE BUKRS LIKE '21%' AND ZHETONG NOT IN ('','0','1','TS','无')
      ORDER BY ZHETONG,EBELN;           
    FOR CUR_ROW1 AS C_CURSOR1 DO 
    
      IF _IND1 = 1 THEN
        _ZHT := CUR_ROW1.ZHETONG;
        _ZHETONG[:_INDEX] := '';
        _ZEBELN[:_INDEX] := '';
      END IF; 
     
      IF CUR_ROW1.ZHETONG = :_ZHT THEN
       _ZHETONG[:_INDEX] := CUR_ROW1.ZHETONG;
       _ZEBELN[:_INDEX] := :_ZEBELN[:_INDEX]||CUR_ROW1.EBELN||',';
      ELSE
      _INDEX := :_INDEX + 1; 
      _ZHETONG[:_INDEX]  := CUR_ROW1.ZHETONG;
      _ZEBELN[:_INDEX] := CUR_ROW1.EBELN ||',';
      _ZHT := CUR_ROW1.ZHETONG;
      END IF;
      
       _IND1 := :_IND1 + 1;
    END FOR;  
        
      RST = UNNEST(:_ZHETONG, :_ZEBELN) AS ("ZHETONG", "EBELN");
      
      RESULT = SELECT ZHETONG,
                      LEFT(EBELN,LENGTH(EBELN)-1) AS EBELN  
                 FROM :RST;
          
END;

-- 测试函数

CALL "ZB_CHENWH"."ZCONCAT_EKKO_EBN"(?) ;