SAP HANA 9. SQLScript Basic 之 Create an SQLScript Procedure with Calculation Engine (CE) Functions

采用CE（Calculation Engine）执行SAP HANA 8相同的功能，比较两者区别，执行效率。

 

1、新建文件NewFile创建get_bp_addresses_by_role_ce.procedure

 

2、Local Table Type

create typett_bp_addresses as table (
PartnerIdnvarchar(10),
PartnerRolenvarchar(3),
EmailAddressnvarchar(255),
CompanyNamenvarchar(80),
AddressIdnvarchar(10),
City nvarchar(40),
PostalCodenvarchar(10),
Street nvarchar(60)
)

 

3、SQLScript

CREATE PROCEDURE get_bp_addresses_by_role_ce ( 
    in partnerrole nvarchar(3),
    out bp_addresses tt_bp_addresses
) 
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER 
	READS SQL DATA AS
BEGIN
/***************************** 
	Write your procedure logic 
 *****************************/
lt_bp =
CE_COLUMN_TABLE("SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::businessPartner",
["PartnerId", "PartnerRole", "EmailAddress", "CompanyName", "AddressId" ]);
lt_bp_proj = CE_PROJECTION(:lt_bp,
["PartnerId", "PartnerRole", "EmailAddress" , "CompanyName", "AddressId" ],
'"PartnerRole" = :partnerrole' );
lt_address =
CE_COLUMN_TABLE("SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::addresses",
["AddressId", "City", "PostalCode", "Street"]);

bp_addresses = CE_JOIN(:lt_bp_proj, :lt_address, ["AddressId"],
["PartnerId", "PartnerRole", "EmailAddress", "CompanyName",
"AddressId", "City", "PostalCode", "Street"]);

END;

 

 

4、Team→Commit& Activate之后，在SQL Console中调用代码

call"_SYS_BIC"."WorkShop.sessiona.00.models/get_bp_addresses_by_role_sql"(
partnerrole =>'02', bp_addresses => ? )
 
call"_SYS_BIC"."WorkShop.sessiona.00.models/get_bp_addresses_by_role_ce"(
partnerrole =>'02', bp_addresses => ? )
 

 

 

执行结果304 ms 427µs   VS   690 ms 49µs

因采用CloudShare的服务器，速度比较慢，貌似纯SQL写的Procedure胜出了，CE写的Procedure速度偏慢。

 

PS：CE封装了很多的内置函数，如下