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档案
6232
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
