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