SAP Hana存储过程开发

Hana 存储过程

与纯粹的sql语句相比 存储过程的性能优于前者。并且存储过程简化了查询常见子表达式的步骤，并且提高了SQLScript的可读性。

1、创建&更新语法

{ CREATE [ OR REPLACE ] | ALTER } PROCEDURE [schema_name.]<proc_name>
   [ ( [ IN|OUT|INOUT VAR_NAME VAR_TYPE ] ) ]
   [ 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 [ OR REPLACE ] | ALTER } PROCEDURE [schema_name.]<proc_name>

定义存储过程的名称proc_name，及所在的schema_name。

[ ( [ IN|OUT|INOUT VAR_NAME VAR_TYPE ] ) ]

定义存储过程的出入参数，多个参数之间用,相隔。
注意：定义了with result view 必须有out参数，且必须为table类型。

[ LANGUAGE <lang> ]

指定存储过程实现的程序语言，一般为SQLScript，也可以是其他语言，比如R语言。
默认为: SQLSCRIPT。

[ SQL SECURITY <mode> ]

指定存储过程执行的安全模式，可以是以存储过程DEFINER定义者的权限去执行，也可以设定为以调用者INVOKER权限去执行，默认: DEFINER。

[ DEFAULT SCHEMA <default_schema_name> ]

指定存储过程中引用的数据库对象的默认SCHEMA。

[READS SQL DATA]

指定该存储过程为只读存储过程，没有更新数据的相关的DML语句（INSERT、UPDATE、DELETE）或者DDL语句（CREATE、ALTER、DROP、TRUNCATE），如果调用其他存储过程，则被调用过程也是只读的。READS SQL DATA可以用于优化。

[ WITH RESULT VIEW <view_name> ]

将只读取存储过程的输出写入指定的视图 view_name 中，可以被其他查询SQL用来查询，此时存储过程就像一个表或视图。

[ WITH ENCRYPTION ]

加密存储过程体脚本，查看存储过程定义时，存储过程体脚本隐藏。

AS
BEGIN [ SEQUENTIAL EXECUTION ]
	<statement_body>
END

定义存储过程体脚本，增加 SEQUENTIAL EXECUTION 会限制该存储过程并行执行。

2、删除语法

DROP PROCEDURE [schema_name.]<proc_name>;

3、调用语法

CALL [schema_name.]<proc_name> [(<param_list>)] [WITH OVERVIEW] [IN DEBUG MODE]

语法解析：

[WITH OVERVIEW]

指定该参数，将存储过程调用的结果，存入指定的物理表。

[IN DEBUG MODE]

以调试模式执行存储过程。

4、实例演示

准备工作：新增账号 NEWUSER，授权只读访问 SAPHANADB，需要访问《SAP Flight航班数据模型》。

-- 创建用户 NEWUSER（在SAPHANADB所在租户的SYSTEM账号下操作）
CREATE USER NEWUSER PASSWORD Yourpas0 NO FORCE_FIRST_PASSWORD_CHANGE;				
ALTER USER NEWUSER DISABLE PASSWORD LIFETIME;		-- 设置密码永不过期
-- 给 NEWUSER 授权（（在SAPHANADBM账号下操作）
GRANT SELECT, SELECT METADATA ON SCHEMA SAPHANADB to NEWUSER WITH GRANT OPTION;

在这里插入图片描述

（1）存储过程入门
-- 定义
create or replace procedure newuser.zp_scarr
as
begin
	select top 3 * from saphanadb.scarr where mandt = 300;
end;
-- 调用
call newuser.zp_scarr;

在这里插入图片描述

（2）指定 DEFAULT SCHEMA
-- 定义
create or replace procedure newuser.zp_scarr_d
default schema saphanadb
as
begin
	select top 3 * from scarr where mandt = 300;	--单独执行报错
end;
-- 调用
call newuser.zp_scarr_d;

在这里插入图片描述

（3）定义内部变量，执行多个查询
create or replace procedure newuser.zp_scarr2
as
	--本地变量
	count int default 3;
begin
	--注意事项：内部变量，必须在最前面先定义，后使用
	declare client int;
	--注意事项：内部变量引用，需要使用：“ :变量名 ”
	select top :count * 
	from saphanadb.scarr 
	where mandt = 300;			--第1个查询
	
	--注意事项：此处定义变量，脚本报错
	--declare client int default 300;
	
	--注意事项：内部变量赋值，必须要用：“ 变量名 := 值 ” 
	client := 300;
	--存储过程内部可以定义临时表，不需要声明即可使用
	tmp_t000 = 	 
	select top 3 *
	from saphanadb.t000
	where mandt = :client;
	
	--注意事项：临时表引用，需要使用：“ :临时表名 ”
	select * from :tmp_t000;	--第2个查询
end;
-- 调用
call newuser.zp_scarr2;

在这里插入图片描述
在这里插入图片描述

（4）定义值类型入参
create or replace procedure newuser.zp_scarr3
(
	IN I_CLIENT int default 300,
	IN I_COUNT int default 3
)
as
begin
	--注意事项：入参引用，需要使用：“ :入参名 ”
	select top :I_COUNT * from saphanadb.scarr where mandt = :I_CLIENT;
end;
-- 匿名参数调用：顺序赋值
call newuser.zp_scarr3(200);

在这里插入图片描述

-- 命名参数调用：按名赋值
call newuser.zp_scarr3( I_COUNT => 5);

在这里插入图片描述

（5）定义值类型出参
create or replace procedure newuser.zp_scarr4
(
	IN I_CLIENT int default 300,
	OUT O_CARRID varchar(10),
	OUT O_CARRNAME varchar(50)
)
as
begin
	select top 1 CARRID, CARRNAME 
	--注意事项：使用select into给参数或内部变量赋值，不需要 “:”
	into O_CARRID, O_CARRNAME
	from saphanadb.scarr 
	where mandt = :I_CLIENT;
end;
-- 调用
call newuser.zp_scarr4(200,?,?);

在这里插入图片描述

-- 调用，获取存储过程的输出值（必须在do begin ··· end中执行）
do begin
	declare O_CARRID varchar(10);
	declare O_CARRNAME varchar(50);
	call newuser.zp_scarr4(200, O_CARRID, O_CARRNAME);
	select O_CARRID as O_CARRID , O_CARRNAME as O_CARRNAME from dummy;
end;

在这里插入图片描述

（6）定义表类型出参
--定义表类型，用于存储过程出参
create TYPE newuser.tt_scarr AS TABLE 
(
	carrid 		NVARCHAR(3) primary key,
	carrname 	NVARCHAR(20),
	URL 		NVARCHAR(255)
);

--定义物料表（带Key），用于接收存储过程执行结果
CREATE column table newuser.zt_scarr LIKE tt_scarr;
--定义物料表（不带Key），用于接收存储过程执行结果
CREATE column table newuser.zt_scarr_nokey LIKE tt_scarr;
alter table newuser.zt_scarr_nokey drop primary key;

--定义存储过程，带表类型输出参数
create procedure newuser.zp_scarr_out
(
	in 	i_carrid 	NVARCHAR(3),
	out o_scarr 	tt_scarr
)
as
begin
	o_scarr =
	select carrid, carrname, url
	from saphanadb.scarr 
	where mandt = 300
	  and carrid = :i_carrid;
end;
--执行调用
call newuser.zp_scarr_out('AA',?);

在这里插入图片描述

--执行调用，输出结果（带关键字表，执行多次报错）
call newuser.zp_scarr_out('AA',zt_scarr) with OVERVIEW;
select * from newuser.zt_scarr;

--执行调用，输出结果（不带关键字表，执行多次则插入多条记录）
call newuser.zp_scarr_out('AA',zt_scarr_nokey) with OVERVIEW;
select * from newuser.zt_scarr_nokey;

在这里插入图片描述

（7）指定 RESULT VIEW
create or replace procedure newuser.zp_scarr_out2
(
	in 	i_carrid 	NVARCHAR(3),
	out o_scarr 	tt_scarr
)
READS SQL DATA WITH RESULT VIEW zp_scarr_out_view
as
begin
	o_scarr =
	select carrid, carrname, url
	from saphanadb.scarr 
	where mandt = 300
	  and carrid = :i_carrid;
end;
--执行调用
call newuser.zp_scarr_out2('AA',?);		--同上
--执行调用，输出结果
call newuser.zp_scarr_out2('AA',zt_scarr_nokey) with OVERVIEW;	--同上
select * from newuser.zt_scarr_nokey;
--执行，查询RESULT VIEW
truncate table newuser.zt_scarr_nokey;		--清空记录
SELECT * FROM newuser.zp_scarr_out_view 
WITH PARAMETERS ( 	
	'placeholder' = ('$$i_carrid$$', 'AA'), 
	'placeholder' = ('$$o_scarr$$', 'zt_scarr_nokey') 
);											--有记录
select * from newuser.zt_scarr_nokey;		--没有记录

在这里插入图片描述
在这里插入图片描述

（8）定义表类型入参
create or replace procedure newuser.zp_scarr_in
(
	in 	it_table 	zt_scarr_nokey
)
READS SQL DATA
as
begin
	select * from :it_table;
end;
--执行调用
truncate table newuser.zt_scarr_nokey;							--清空数据
call newuser.zp_scarr_out2('AA',zt_scarr_nokey) with OVERVIEW;	
call newuser.zp_scarr_out2('AA',zt_scarr_nokey) with OVERVIEW;	--执行2遍
call newuser.zp_scarr_in(zt_scarr_nokey);

在这里插入图片描述

（9）综合实例
-- 创建表：消息日志
create column table newuser.message_box(message	nvarchar(255));
--日志初始化
CREATE or replace PROCEDURE newuser.init_proc()
as
begin
	truncate table newuser.message_box;
end;
--日志创建
CREATE or replace PROCEDURE newuser.ins_msg_proc(msg nvarchar(255))
as
begin
	insert into newuser.message_box values(:msg);
end;

CREATE or replace PROCEDURE newuser.zp_com_demo
(
	i_client	varchar(3),
	i_carrid	nvarchar(3) default 'AA'
)
 AS	
 	-- local variables
	v_CUR_DATE 	VARCHAR(10) := CURRENT_DATE;  
	v_count 	int;
	cursor cur1( v_client varchar(3) ) FOR
		select carrid, carrname, url 
		from saphanadb.scarr 
		where mandt = :v_client 
		order by carrid;	
BEGIN
	-- inner variables
	declare v_Last_DATE varchar(10) := YEAR(CURRENT_DATE)||'-12-31';
	declare v_carrid 	nvarchar(3);		-- for cur1
	declare v_carrname	nvarchar(20);
	declare v_url		nvarchar(255);
	declare cursor cur2( v_date varchar(10) ) FOR
		select carrid, count(1) as flight_count
		from saphanadb.sflight
		where mandt = ifnull(:i_client,'300')
		  --(case when :i_client is null then '300' else :i_client end)
		  and substring(fldate,1,4) = year(:v_date)
		  and substring(fldate,5,2) = month(:v_date)
		group by carrid
		order by carrid;
	
	init_proc();
	if :i_client is null then
		i_client := '300';
	end if;
	select i_client, v_CUR_DATE, v_Last_DATE, month(:v_CUR_DATE) as month_of_current from dummy;	-- show Parameter and variables
	
	ins_msg_proc('Parameter and variable List: i_carrid = ' 
		|| :i_carrid || ', i_client = ' || :i_client || ', v_CUR_DATE = ' 
		|| :v_CUR_DATE || ', v_Last_DATE = ' || :v_Last_DATE);
	
	select count(1) into v_count from saphanadb.scarr where mandt = :i_client;
	ins_msg_proc('There are ' || :v_count 
		|| case when :v_count > 0 then ' records' else ' record' end 
		|| ' in table SCARR of client-' || :i_client || '.');
		
	--使用参数打开游标 cur1
	open cur1(:i_client);
	if cur1::ISCLOSED then
		ins_msg_proc('WRONG: cur1 not open');
	else
		ins_msg_proc('OK: cur1 open');
	end if;
	
	Fetch cur1 into v_carrid, v_carrname, v_url;
	if cur1::NOTFOUND then
		ins_msg_proc('WRONG: cur1 contains no valid data');
	else
		ins_msg_proc('OK: cur1 contains valid data: carrid = ' || :v_carrid );
	end if;

	WHILE :v_CUR_DATE <= :v_Last_DATE DO --判断条件
		ins_msg_proc('WHILE PROCESSING: month = ' || month(:v_CUR_DATE) );
		--打开游标 cur1，使用 FOR R AS CUR1 DO 对游标的数据集合做LOOP循环
		FOR R AS CUR2(:v_CUR_DATE) DO
		ins_msg_proc('FOR CUR: ' || month(:v_CUR_DATE)  
			|| '月份，航空公司' || R.CARRID || '有' 
			|| R.flight_count || '条航班计划' );  
		END FOR;
		v_CUR_DATE := LAST_DAY(ADD_MONTHS(:v_CUR_DATE,1));  --变量CURR_DATE递增
	END WHILE;
END; 

call newuser.zp_com_demo('300');	
call newuser.zp_com_demo(null);		--同上

在这里插入图片描述

select * from newuser.message_box;

在这里插入图片描述
在这里插入图片描述
原创文章，转载请注明来源-X档案

# 数据库 # sql
Logo
开放原子开发者工作坊

开放原子开发者工作坊旨在鼓励更多人参与开源活动，与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动，如meetup、训练营等，主打技术交流，干货满满，真诚地邀请各位开发者共同参与！

更多推荐

cover

人工智能在库存管理中的应用

avatar 开放原子开发者工作坊
cover

【Windows】redis安装

avatar 开放原子开发者工作坊
cover

读写给大家的AI极简史笔记02人工智能简史

avatar 开放原子开发者工作坊