脚本小子

创建一个自增序列

SEQUENCE-序列

sql

CREATE SEQUENCE "SAPHANADB"."SEQ_ZEB_EXPLAIN" --创建一个名为SEQ_ZEB_EXPLAIN的序列
increment BY 1 --递增量
maxvalue 99999999 --最大值
minvalue 1 --最小值
NO CYCLE --是否为一个圈
start  WITH 1 --初始值

DROP SEQUENCE "SAPHANADB"."SEQ_ZEB_EXPLAIN" --删除序列

SELECT SEQ_ZEB_EXPLAIN.currval FROM DUMMY --查询当前序列

SELECT SEQ_ZEB_EXPLAIN.nextval FROM DUMMY --执行并查询下一个序列

TRIGGER-触发器

sql

CREATE OR REPLACE TRIGGER "SAPHANADB"."TRI_ZEB_EXPLAIN"  --创建或重新创建一个名为TRI_ZEB_EXPLAIN的触发器
	BEFORE INSERT ON "SAPHANADB"."ZEB_EXPLAIN" --在对ZEB_EXPLAIN表进行插入事件之前执行
	REFERENCING NEW ROW MYNEWROW --将单条记录重命名为MYNEWROW
	FOR EACH ROW --每条记录都适用
BEGIN 
	DECLARE NEW_ID NUMBER(10); --声明一个名为NEW_ID的数字变量
	DECLARE NEW_TIME TIMESTAMP; --声明一个名为NEW_TIME的时间戳变量
	SELECT SEQ_ZEB_EXPLAIN.NEXTVAL INTO NEW_ID FROM DUMMY; --序列的下一个值赋值到NEW_ID
	MYNEWROW.ID = :NEW_ID; --赋值
	SELECT CURRENT_TIMESTAMP INTO NEW_TIME FROM DUMMY; --当前时间戳赋值到NEW_TIME
	MYNEWROW.EDIT_TIME = :NEW_TIME; --赋值
END

DROP TRIGGER "SAPHANADB"."TRI_ZEB_EXPLAIN" --删除触发器

存储过程-DEMO

sql

CREATE OR REPLACE PROCEDURE PRO_SELECT_TABLE (
	IN tableName varchar(50) default 'ZEB_EXPLAIN',
	IN fieldsKey varchar(20) default NULL,
	IN status varchar(10) default '=',
	IN fieldsValue varchar(20) default NULL,
	IN fieldsKey1 varchar(20) default NULL,
	IN status1 varchar(10) default '=',
	IN fieldsValue1 varchar(20) default NULL
	--,OUT outTable tableType --与WITH result联动，tableType填写返回表的类型
	) --定义了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
	/*
	declare reserveFields varchar(20) := '';
	--WHILE循环，可用于批量添加数据
	WHILE :reserveFields <= CURRENT_DATE DO //判断条件
		---
		reserveFields := LAST_DAY(ADD_MONTHS(:CURR_DATE,1));  //变量CURR_DATE递增
	END WHILE;
	--FOR循环，可用于更改数据
	DECLARE CURSOR vernier FOR SELECT * FROM exampleTable;  //声明游标变量CUR
	FOR rowData AS vernier DO
		---
	END FOR;
	*/
	declare sqlString varchar(200) default CONCAT('SELECT * FROM ', :tableName);
	IF (:fieldsKey IS NOT NULL AND :fieldsValue IS NOT NULL) THEN --不能写成 <> NULL 或 != NULL
		sqlString := CONCAT(:sqlString, ' WHERE ');
		sqlString := CONCAT(:sqlString, '"');
		sqlString := CONCAT(:sqlString, :fieldsKey);
		sqlString := CONCAT(:sqlString, '" ');
		sqlString := CONCAT(:sqlString, :status);
		sqlString := CONCAT(:sqlString, ' '''); --双单引号变单引号
		sqlString := CONCAT(:sqlString, :fieldsValue);
		sqlString := CONCAT(:sqlString, '''');
		IF (:fieldsKey1 IS NOT NULL AND :fieldsValue1 IS NOT NULL) THEN
			sqlString := CONCAT(:sqlString, ' AND ');
			sqlString := CONCAT(:sqlString, '"');
			sqlString := CONCAT(:sqlString, :fieldsKey1);
			sqlString := CONCAT(:sqlString, '" ');
			sqlString := CONCAT(:sqlString, :status1);
			sqlString := CONCAT(:sqlString, ' '''); --双单引号变单引号
			sqlString := CONCAT(:sqlString, :fieldsValue1);
			sqlString := CONCAT(:sqlString, '''');
		--ELSE 写其他条件
		END IF;
	--ELSE 写其他条件
	END IF;
	--EXEC(:sqlString); --执行但不显示
   	EXECUTE IMMEDIATE :sqlString;
END;

CALL PRO_SELECT_TABLE(); --调用存储过程

DROP PROCEDURE PRO_SELECT_TABLE --删除存储过程

函数-DEMO

SQL

create function FT_UTIL_SPLIT
(
	in i_Text nvarchar(4000) DEFAULT '',		--	字符串
	in i_splitChar nvarchar(1) DEFAULT ','		--	分隔符
)
returns table
(
	FCODE nvarchar(100)
)
Language SQLScript
as
Begin
  declare _items varchar(100) ARRAY;
  declare _text varchar(4000);
  declare _index integer;
  _text := :i_Text;
  _index := 1;

  WHILE LOCATE(:_text,i_splitChar) > 0 DO
  _items[:_index] := SUBSTR_BEFORE(:_text,i_splitChar);
  _text := SUBSTR_AFTER(:_text,i_splitChar);
  _index := :_index + 1;
  END WHILE;
  _items[:_index] := :_text;

  rst = UNNEST(:_items) AS ("FCODE");	--	存入临时表rst

  return
	SELECT * FROM :rst;
end;

DROP FUNCTION FT_UTIL_SPLIT

视图-DEMO

分为图形视图和普通视图,视图可传参,普通视图可用:fieldName传参

引用方式👇

sql

SELECT * FROM "_SYS_BIC"."STL_01.LL/ZGC_MM_CPKCMX"(
    PLACEHOLDER."$$ZCALDAY$$" => REPLACE(LEFT(CURRENT_DATE,10),'-','')
)

SELECT * FROM "_SYS_BIC"."STL_01.LL/ZGC_SD_HTMX"(
    'PLACEHOLDER' = ('$$ZF_DATE$$','20230913'),
    'PLACEHOLDER' = ('$$ZF_DATE$$','20230913'),
)

实用例子

中文排序-函数

SQL

CREATE FUNCTION ZGA_GET_ZH_SPELL
       (
           in str varchar(300)
       )
RETURNS
           c NVARCHAR(300)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER 
AS
            chh NVARCHAR(300);
            chn NVARCHAR(300);
            chs NVARCHAR(300);
BEGIN

     c:='';

WHILE (LENGTH(str)>0) DO
  
 --依次取单个字符 GB2312 区位编码，
   chh := LEFT(str,1);
   chn := STRTOBIN(LEFT(str,1),'GB2312') ;
   
 --汉字字符，返回字符对应首字母,非汉字字符,返回原字符
 IF(Least(chn,'B0A1')='B0A1' AND Greatest(chn,'D7F9')='D7F9') 
 THEN

    select 
             case when Least(chn,'B0A1') = 'B0A1' then
     (     
           case when Greatest(chn,'B0C4') = 'B0C4'   then  'A'
     else  case when Greatest(chn,'B2C0') = 'B2C0'    then  'B'
     else  case when Greatest(chn,'B4ED') = 'B4ED'    then  'C'
     else  case when Greatest(chn,'B6E9') = 'B6E9'    then  'D'
     else  case when Greatest(chn,'B7A1') = 'B7A1'    then  'E'
     else  case when Greatest(chn,'B8C0') = 'B8C0'    then  'F'
     else  case when Greatest(chn,'B9FD') = 'B9FD'    then  'G'
     else  case when Greatest(chn,'BBF6') = 'BBF6'    then  'H'
    
     else  case when Greatest(chn,'BFA5') = 'BFA5'   then  'J'
     else  case when Greatest(chn,'C0AB') = 'C0AB'   then  'K'
     else  case when Greatest(chn,'C2E7') = 'C2E7'   then  'L'
     else  case when Greatest(chn,'C4C2') = 'C4C2'   then  'M'
     else  case when Greatest(chn,'C5B5') = 'C5B5'   then  'N'
     else  case when Greatest(chn,'C5BD') = 'C5BD'   then  'O'
     else  case when Greatest(chn,'C6D9') = 'C6D9'   then  'P'
     else  case when Greatest(chn,'C8BA') = 'C8BA'   then  'Q'
     else  case when Greatest(chn,'C8F5') = 'C8F5'   then  'R'
     else  case when Greatest(chn,'CBF9') = 'CBF9'   then  'S'
     else  case when Greatest(chn,'CDD9') = 'CDD9'   then  'T'
    
    
     else  case when Greatest(chn,'CEF4') = 'CEF4'   then  'W'
     else  case when Greatest(chn,'D1B8') = 'D1B8'   then  'X'
     else  case when Greatest(chn,'D4D0') = 'D4D0'   then  'Y'
     else  'Z'
     end 
     end 
     end 
     end 
     end 
     end 
     end 
     end 
     end 
     end 
     end 
     end 
     end 
     end 
     end 
     end 
     end 
     end 
     end 
     end 
     end 
     end )
 
           else ' '  end   
      INTO chs FROM  DUMMY;
 ELSE
      chs:=UPPER(chh);
 END IF;   

   str:=RIGHT(str,LENGTH(str)-1);  
   c:=c||chs ;
 
END  WHILE;

END;

指定排序-函数

sql

CREATE OR REPLACE FUNCTION ZGA_ORDER_BY
       (
           in str varchar(50),
           in param varchar(300)
       )
RETURNS
           c NVARCHAR(50)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER 
AS
            sb NVARCHAR(300);
BEGIN
	 sb := SUBSTR_BEFORE(CONCAT(',',:param),:str);
     c := LENGTH(:sb) - LENGTH(REPLACE(:sb,',',''));
	 IF (:c = '0') THEN C := '99999999999';
	 END IF;
END;

HANA数据库信息

sql

--对象依赖情况
SELECT
  D.BASE_OBJECT_NAME OBJECT_NAME,
  D.BASE_OBJECT_TYPE OBJECT_TYPE,
  D.DEPENDENT_OBJECT_NAME DEP_OBJECT_NAME,
  D.DEPENDENT_OBJECT_TYPE DEP_OBJECT_TYPE
FROM
( SELECT                                             /* MODIFICATION SECTION */
    'HCASHIER7' BASE_OBJECT_NAME,
    'TABLE' BASE_OBJECT_TYPE,
    '%' DEPENDENT_OBJECT_NAME,
    '%' DEPENDENT_OBJECT_TYPE
  FROM
    DUMMY
) BI,
  OBJECT_DEPENDENCIES D
WHERE
  D.BASE_OBJECT_NAME LIKE BI.BASE_OBJECT_NAME AND
  D.BASE_OBJECT_TYPE LIKE BI.BASE_OBJECT_TYPE AND
  D.DEPENDENT_OBJECT_NAME LIKE BI.DEPENDENT_OBJECT_NAME AND
  D.DEPENDENT_OBJECT_TYPE LIKE BI.DEPENDENT_OBJECT_TYPE
ORDER BY
  D.BASE_OBJECT_NAME,
  D.BASE_OBJECT_TYPE,
  D.DEPENDENT_OBJECT_NAME,
  D.DEPENDENT_OBJECT_TYPE;

  
--表压缩情况
SELECT CURRENT_TIMESTAMP
  --,COLUMN_NAME AS "COLUMN",LOADED
  ,SUM(ROUND(UNCOMPRESSED_SIZE/1024/1024)) AS "UNCOMPRESSED MB"
  ,SUM(ROUND(MEMORY_SIZE_IN_MAIN/1024/1024)) AS "MAIN MB"
  ,SUM(ROUND(MEMORY_SIZE_IN_DELTA/1024/1024)) AS "DELTA MB"
  ,SUM(ROUND(MEMORY_SIZE_IN_TOTAL/1024/1024)) AS "TOTAL USED MB"
  --,ROUND(COMPRESSION_RATIO_IN_PERCENTAGE/100,2) AS "COMPR.RATIO"
FROM M_CS_COLUMNS
WHERE TABLE_NAME='XXX'-- AND LOADED='TRUE'
GROUP BY TABLE_NAME;

--表分区情况
SELECT * 
  FROM M_CS_PARTITIONS 
  WHERE 1=1 
  AND (SCHEMA_NAME,TABLE_NAME) IN 
  (SELECT SCHEMA_NAME,TABLE_NAME FROM M_CS_PARTITIONS 
	GROUP BY SCHEMA_NAME,TABLE_NAME HAVING --COUNT(1)=1
	COUNT(1)=13 AND COUNT(1)!=1--13是按月的,1是没有分区的
  )
  ORDER BY 1,2,3,4,5,6;

--表DELTA MERGE情况
SELECT
       CURRENT_TIMESTAMP,A.SCHEMA_NAME,A.TABLE_NAME
       ,SUM(A.MEMORY_SIZE_IN_TOTAL/1024/1024/1024) MEMORY_SIZE_IN_TOTAL_GB
       ,SUM(A.MEMORY_SIZE_IN_DELTA/1024/1024/1024) MEMORY_SIZE_IN_DELTA_GB
       ,MAX(A.LAST_MERGE_TIME) LAST_MERGE_TIME 
       ,MAX(B.AUTO_MERGE_ON) AUTO_MERGE_ON
       ,SUM(A.RECORD_COUNT) RECORD_COUNT
       ,'MERGE DELTA OF ' || A.SCHEMA_NAME || '.' || A.TABLE_NAME || ';' MERGE_SQL
       ,'ALTER TABLE ' || A.SCHEMA_NAME || '.' || A.TABLE_NAME || ' DISABLE AUTOMERGE;' --ENABLE
  FROM M_CS_TABLES A
 LEFT JOIN TABLES B ON A.SCHEMA_NAME=B.SCHEMA_NAME AND A.TABLE_NAME=B.TABLE_NAME
 WHERE 1=1
   AND A.SCHEMA_NAME = 'XXX'
   --AND A.TABLE_NAME='XXX'
  GROUP BY A.SCHEMA_NAME,A.TABLE_NAME;

SELECT 
	CURRENT_TIMESTAMP,* 
	FROM M_DELTA_MERGE_STATISTICS 
WHERE 1=1 AND SCHEMA_NAME='XXX' AND TABLE_NAME='XXX'
ORDER BY SCHEMA_NAME,TABLE_NAME;

--列加载情况
SELECT SCHEMA_NAME
  ,TABLE_NAME
  ,IS_DELTA_LOADED
  ,IS_DELTA2_ACTIVE
  ,LOADED
  ,LAST_MERGE_TIME 
FROM M_CS_TABLES 
  WHERE 1=1
  --AND IS_DELTA2_ACTIVE='TRUE'
  AND SCHEMA_NAME='XXX' AND TABLE_NAME='XXX';

--列索引情况
SELECT CURRENT_TIMESTAMP
  ,SCHEMA_NAME
  ,TABLE_NAME
  ,INDEX_NAME
  ,CONSTRAINT
  ,STRING_AGG(COLUMN_NAME,',') INDEX_COLUMN_STRING
FROM INDEX_COLUMNS
  WHERE SCHEMA_NAME='XXX'
  GROUP BY SCHEMA_NAME,TABLE_NAME,INDEX_NAME,CONSTRAINT
  ORDER BY SCHEMA_NAME,TABLE_NAME,INDEX_NAME,CONSTRAINT;

创建一个自增序列 ​

SEQUENCE-序列 ​

TRIGGER-触发器 ​

存储过程-DEMO ​

函数-DEMO ​

视图-DEMO ​

实用例子 ​

中文排序-函数 ​

指定排序-函数 ​

HANA数据库信息 ​

创建一个自增序列

SEQUENCE-序列

TRIGGER-触发器

存储过程-DEMO

函数-DEMO

视图-DEMO

实用例子

中文排序-函数

指定排序-函数

HANA数据库信息