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

原创 2013年12月02日 11:18:29

采用CECalculation Engine)执行SAP HANA 8相同的功能,比较两者区别,执行效率。

 

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

 

2Local 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)
)


计算机生成了可选文字: 舜sAp日ANAoevelopment二。:\弘哗习资料气日ANA\clouds卜areRepository\Workshop\、essiona\00\models\get_bp_dddresses_b又role--ceprocedure一s^p日ANAstudi(FileEditNavigateSearchprojectRun;r全,周履自石杯石。xP一叱争,O,;甲,WindowHelpi乞,御,令中,中,gApHANA丫。sApHANAS跳get-bp_addre,se,_b犯role-sql.procedure了get-bp_addre,,es_b失role一e.procedure且曰马甚!口四困后每。息HDB(sysTEM)sApHANAClouds卜areTest幽Backup,仑Catalog卜臼publicSynonyms卜侣HE比。一DATA。侣SAP--日ANA--EpM_DEMO卜已ColumnView,卜臼procedures‘公,Tab!e,四sap.hana.democontent.epm.data::addresse卿,ap.hana.democontent.epm.data::bu,ine,51吁,ap.卜ana.democontent.epm.data::con、tant:甘,ap.hana.democontent.epm.data::employe.开sao.hana.democontent.eom.data::messaaeHDB(SYSTEM)uvolbawcub45qagptgu.vm.cld.,rooLocalTableTypes…YOUC己nC广e己tenewtabletypesandusethemasinputandoutputparametersin之Thetabletypesoustbedefinedusing义Lsyntax,forexample:cREATEWPE<TypeName>ASTABLE(<filedNamel><FiledTypel>,<filedNameZ><fcreatetypett_bp_addressesastable(partner工dnvarchar(le),partnerRolenvarchar(3),巨mai1Addressnvarchar(255),coopanyNaoenvarchar(80),Address工dnvarchar(1。),citynvarchar(叨),postalcodenvarchar(1。),Streetnvarchar(6e))

 

3SQLScript

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;


 

计算机生成了可选文字: 履sAp日ANAoevelopment一。:\sAp学习资料\日ANA\cloudshareRepository\Workshop\sessiona\00\models\get--b几addresses_by--role--ce.procedure一sAp日ANAstudio.Filer当EditNavigate、励自Searc卜projectRunWindowHelp争,O,:甲,笙,。,令中,,目争黔ectExpll吃sApHANA'?。sApHANAS跳竺卫比getPn-a州res一也ro'e-‘。‘·p竺垫re,了get-bp一ddr一es--tix-ro,e-ce·procedore汀石巴·HDB一sQLco一‘eZ回厄路,口四困后斗,。息HDB(SYSTEM)SApHANACloudshareTe,t幽Backup臼Catalog卜吩publlcSynonyms卜侣HELLO-00ATA。指sAp_HANA--印M_o〔Mo卜它,ColumnViews卜亿乡procedures‘它,Tables胭.p.卜ana.democonter:te:r,1.」ata::addre,,e圈sap.卜ana.democon七。}:七。F::r一ldat。::bu、ines、F圈sap.hana.democonteot.epm.data;:con:tant:圈sap.卜ana.democontent.epm.data::employe。胭.p.hana.democontent.epm.data::message胭sap.hana.democontent.epm.data::products胭、ap.hana.democontent.epm.data::purchase胭,ap.卜ana.democontent.epm.data::purc卜a,e胭sap.卜ana.democontent.epm.data::salesord胭:ap.卜ana.democontent.epm.data::、ale,ord皿sap.卜ana.democontent.epm.data::Too6胭:ap.卜ana.democontent.epm.data::Too6A皿sap.卜ana.democontent.epm.data::丁cuRcHDB(SYSTEM)uvolbawcub4Sqagptgu.vm.cld.,rOO而骊阂Loc。l丁。bl。Type、lcREATEPRocEDUREget_bp_add尸esses_by--role_ce(inpartnerrolenvarchar(3),outbp_addressestt_bp_addresses)LAN6UAGESQLSCRIPTSQLSECURITYINVOKERREADSSQLDATAASBE6IN/*****************************衬riteyourprocedurelogic*****************************/It_bp=CE一oL幽N_TASLE("SAP_HANA--EP性DE阳”.'"sap.hana.de帕content.ePm.data::businessPartner'",['"Partnerld'',"PartnerRole'","Eoai1Address'","coopanyName'","Addressld"])jIt_bp_proj=cE_PRo〕EcTIoN(:It_bp,["Partnerld","PartnerRole","Eoai1Address","c,panyNaoe","Addressld"],"'PartnerRole"=:partnerrole');Itaddress=cE一oL明N_TASLE("SAP_HANA--EP性DE阳”."sap.hana.de帕content.epm.data::addre,ses",['"Addressld","city'","Postalcode","Street"]);bp_addresses=cE_〕oIN(:It_bp_proj,:It_address,['"Addressld'"],['"Partnerld'',"PartnerRole","Emai1Addres、’","coopanyName'",,,Addressld","city,,,"Postalcode","Street"])二END;

 

4TeamCommit& 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 => ? )
 



计算机生成了可选文字: 畸sAp日ANAoevelopment一。:\sAp学习资料川ANA\clouds卜areRepository\workshop\se、siona\00\models\get--bn--addresses_by-role-ce.procedur。一sAp日ANAstudi。I口i邑冲抽}FIIeEditNavigatesearchprojectRunwindowHelp:己,周履自争,O,:甲,l乞,广,令中,!留参Debug19sApHANA…!奋”一/印,aetb。。ddre,,e,面向。。品辰edure止、,*而百污6此品soleZ、一一乃汤。etb。。ddre,,e,byrol。,ai.orocedur。穿\曰石1丫勺:LK匕从I匕尸KUt匕UUK七get_Dp_aaaresses_DyeerOle_ce吸奋卜仁K七只l七尸KU仁七UUK匕get_Dp_aaare5Ses_DyeerOle_Sqi吸…,蒸。:二:。:;:::::p:t一叩一’。。r""‘…,艺。:二:。:;:::::户:‘一。p一”。。re""SQLSECURITYINVOKERREADSSQLDATAASBEGIN/*****************************讨riteyourprocedurelogic*****************************/It_bp=cE一oL明N_TASLE("SAP_HANAesEP"DE阳,"."sap.hana.democontent.epm.data::businessPartner",,"partnerldt_bp_proj.="Partnerldl',"PartnerRole'",cE_PRo〕EcTIoN(:,"PartnerRole",1tEmai1Address",_bp,,,Emai1AddreSS""co,pan州ame'",'"Addressld"]);,"c,panyNaoe","Addressld"1,PartnerRole"=:partnerrole');address=SQLSECURITYINVOKERREADSSQLDATAASBEGIN/*****************************树riteyourprocedurelogic*****************************/bp_addresses=selecta."Partner工d",a."PartnerRole",a."Emai认ddress",a."companyName'",a."Addressld",b."city'",b.'"Postalcode",b."Street"fr俪”SAP_队触-EP性DE阳”."sap.hana.de帕content.epm.data::businessPartner"asainnerjoin"SAP--HANAeeEPHeeDE阳”."sap.hana.de.content.e卿.data::addresses"asbona."Addressld"=b。”Addressld"wherea."PartnerRole”二:partnerrole二,.t二二tr.LI人r.L-}lcE一oL渊N_TASLE("SAP_HANA--EP性DE阳”."sap.hana.democontent.epm.data::addres,es",["Addressld","city","Postalcode'","Street"]);END;bp_addresses=CE_〕OIN(:It_bp_proj,:It_address,['"Addressld"],['"Partnerld'","PartnerRole",'"EmallAddress","coopanyNaoe",,,Addressld","city","Postalcode","Street"])二END;甲

 

计算机生成了可选文字: 履sAp日ANAoevelopment一c:\Windows\即劝em32\sQLcon、oleZ一sAp日ANAstudioFiler当EditNavigateSearchprojectRun,周履自争,O,:甲,WindowHelp;勿,。,令中,,砂ect闯s瞥资痴一婴姑,口一息HDB(sysTEM)sApHANA(了*get-bp_addre:,e,_b犯role-ce·procedure区皿tHDB.匕山叠恤些叫醋参。ebug厦画巫画习奋”一sQLcon,o,eZ备穿get-bp一d“『e',e,一by-『o'e-,q‘·p『ocedu『e二四曰每,C!ouds卜areTes·,__一一二异HDB(sYSTEM)u’。lb'w'"b45q'g州“''m''ld'’『oo_、{{。s二{画R·而{黔嫉磊。,。、n。.*._d廊少p_。ddre、、e、户、一q,二(殴}0{邑中,中,幽BackupJ亡Catalog[>己publlcSynonyms·侣HELLO-00ATA。指sAp_HANA--印M_o〔Mot》〔卜ColumnViews仁户procedures‘气厂~Tables1partnerrole=>’。2',bp_addresses=>矛)partnerldpartnerRoleEmaiIAddres、CompanyName01O(犯00D0102dag尸,r.schulze@beckerberlindeBeckerBerlin01O(犯0001002franklin.jones@pear一computing.compearComputingService,01以沁Do02002igor.taras、ow@retc.ruRu,,ianElectronicTrad…0lO(沁0002902ant卜ony.lebouef@crtu.caC.R.T.U.010(沁0003902,unita一kapoor@it一trade.inIndianFrTradingCom…010(沁0004002pawel一lewando、ki@catf.plChemiaATechnicznieF…Addre'sld1伽卫兀心00352131一41,ap.卜ana.democontent.epm.c,ap.卜ana.democontent.epm.csap.卜ana.democontent.epm.c5161CityBerlinAtlantaGeorgiaMo,kauMontrealMumbaiWarschaupo'talCode1346730338125719HZYIN3400021564Stre改CalvinstrasseDunwoodyXingElektrosawod…AvenueduM…NarimanpointKo、Zykowasap.卜ana.democontent.sap.卜ana.democontent.sap.卜ana.democontent.sap.hana.democontent.epm·Cepm.Cepm·CStateoent'call"_SYs--BIc".'”吻rkshop.sessiona.ee.阳dels/get_bp_addresses_by--role_sql'"(partnerrole二>'eZ',…‘successfullypreparedepm·C‘一sap.卜ana.democontent.epm.c铭p.hana.democontent.epm.csap.hana.democontent.epm.c"p.hana.democontent.epm.csap.卜ana.democontent.epm.c铭p.卜ana.democontent.epm.c"p.hana.democontent.epm.csap.卜ana.democontent.epm.c,ap.卜ana.democontent.epm.cStatement'call',SYSsuccessfullyexecutedFetChed6r.(s),call'.ino_Blc".”吻rkshop.sessiona.的.odels/get_bp_addresses_by--role_sql"(partnerrole=>'eZ',…in3解。5427林s(serverprocessingtioe:22ms333卜s)。572林s(serverprocessingt玩e:。。50卜s)St己tementSYS_BIC".'”吻rkshop.sessiona.的.阳dels/get_bp_addresses_by--role_ce"(partnerrole=>'02',…successfullypreparedStateoent'call"_SYS_Blc".”吻rkshop.sessiona.的.odels/get_bp_addresses_by--role_ce"(partnerrole,>'eZ',…一successfullyexecutedin6900549甘s(serverprocessingtioe:39805692林s)Durationof2statements:994msFetched6r洲(s)in0ms55拼s(serverprocessingtime:。ms0ps)胭胭圈胭胭胭胭胭胭胭胭胭胭胭翻皿

 

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

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

 

PSCE封装了很多的内置函数,如下

计算机生成了可选文字: SQLScriptCE(CaICulationEngine)Built一InFunctions一,月尸r日匕SELECToncolllr川飞。u七tdbleSELECTonau七attribUteVie甘,口口r,阅pr=昌艺乙三之T人,匕,:f二am'r:O上了)2二,月尸尸日臼T3三二三”ou二=:ECC二丁比1血:共三乙三哎”CO工口仁了:燕三乙艺”人,弓,二二3rLECTA,B,C三二om',ATT众:B口T艺叮I匕讨,,ou二=:艺JO:对Vl艺衬."AT:获:BrT艺廿I巴讨一,〔A,B,ClSELECTonolapVle勺官份BEREHAVINGou七=3艺LECTA,B,C,S甘H(亡1Vl三贫一G只C仃夕已丫A,B三ro思ou七二之EO上岛二V上艺Wl'I.簇的AIY丁ICV:艺甘.,[A,B,C〕)一侣困人工丫TICOU七=5三LE.2困人工丫TIC”叮a1Ue'CTA,B,C,3UH(U}士ra盆Vl艺蔺一甘艺艺R艺乃=,,alue’共改亡C=co二七ab二CE:OL饮曰召TABLEL一CO乙口》川TABL三一1C艺pRCJECTI伪若I:二a二tab,〔A,B,Cl,'”万-洪改D.C一="?alue"'J奋;OU七‘二,',盈工ue'GRODPBYOU七二3七LECTr只OM.CO毛住曰口人,B,C,3U封(U}TABL匕.二ROU夕BYA,B,CCO二七二b二CE:QCEAG石R艺G五T:ONL勺序口心TA月Lr「.COL勺飞口召TABL艺”奋:A,B,OU七=Ical七ab,SDH王D》,Cl)INNERJOINou七二昌E毛E二丁A,已,Y,5习H〔刃J万二0黑”CC毛TA万工”:之丁N艺RJOIN一COLTA卫2一臼卫匕只巴一COL二A乃1”。.从万Yl.="COIT乌卫二”。”从艺Yl,洛山七.COLrAB工.。”式艺Y:"=.COL丁AB二”_”只E丫:"OU七=B,Y,于气D1.JC二1于二C口LT乌卫l"'.二OLTA卫二”工KE丫二,下五丫:〕L只FTOllTERJOINau七=3乙二E:TA,已,丫,3口H(刃{士二a巴”CO工T人卫工-工ErTOUT万RJO:N"COLT乌卫:”肺H已R七.COL:五日1"_”下三了二”二”COL了入乃二.。”只艺了二‘,乌王孑刀.COL:入日二l'_'.下三了:”二.CC乙:入日二.'.式E丫二’.OU七=代乞丫21,:乳[AL三rrC万:匕只JO工,t.CO二TA乃二”,一CO工:人万:.B,Y,口])二只E丫1,SQLElpressionsou七二3匕乙ECTA,已,C,3可63TR工卜G{O,:,5:rRO只.COL口曰召TABL艺.p二。口少山=:乙_Cc工切心万_T蕊卫比("CO工。创_T蕊卫比”C£_PRC泥CTIoN(:praJ_乞ab,[.A,,,.B一。”C一,C艺_CAL:(’也d,tr‘一。.,:,5)',,tr二n叮,]);OU七二仃N工OHAl工col七盈bl=3艺迁CTA,B,C,D.COL切阴weT”比二”奋二。二_七ab:=D犷只派.CoL切纽esTA月乙£:.;o。七:C01七abl口召I倪才人工LS艺LECT:col二ab:;r获O丫昌匕乙三:TA,已=SE乙ECTr获O鱿C,rRC丫ca二七abl二C乙CCL切田心T入卫L三《.COL切田心:五乃L三1.,〔A二:艺COL创哑万:几五LE.:"COL创旧丁丁人卫乙艺:",C£一,10凡杜1《:。0二少abl,:co气七ab::',B,c,D〕‘奋‘01_二曲::A,习,C,口1】;ou二二








                    

相关文章推荐

SAP HANA 10. Create an SQLScript Procedure with Imperative Logic

SAP HANA SQLScript 的 Imperative Logic 包括 IF+ELSE+ENDIF 、while循环、For循环、 Case等。   1、新建文件NewFil...

SAP HANA 8. SQLScript Basic 之 Create an SQLScript Procedure with SELECT Statement

1、新建文件NewFile 创建get_bp_addresses_by_role_sql.procedure   2、Local Table Type中定义本地类型 /* You can ...

HANA 基础SQL和SQLScript

本文由一位前辈总结的sql语法  希望对大家有用 第一天:(sql) 1. DDL(定义),DML(操作),DCL(控制) 2. DATA TYPE 和操作符 3. DDL ·表名应尽量为大...

In-memory Computing with SAP HANA读书笔记 - 第一章:Basic concepts of in-memory

本文为In-memory Computing with SAP HANA on Lenovo X6 Systems第一章Basic concepts of in-memory computing的读书...

CE function 在 SAP HANA 中的介绍及使用

CE function,即Calculation Engine Plan Operators。因为类似函数的形式,包含输入参数和输出参数,并且封装了对数据的转化的操作,常被称作CE函数,或者CE fu...

CORBA Programming with TAO - 4.Basic Functions(常用基本方法解析)

摘要:简要介绍CORBA规范定义的几个常用基本方法的功能及应用中需要注意的问题。正文:sidl编译器会为每个在idl中声明的interface生成一个对应的代理基类:class InterfaceNa...

In-memory Computing with SAP HANA读书笔记 - 第三章:Software components and data replication methods

本文为In-memory Computing with SAP HANA on Lenovo X6 Systems第三章Software components and data replication...

SAP HANA之异军突起 (数据库各厂商发展历史.9. )

2012.11.27 iihero于CSDN,如若转载,请注明出处,谢谢。HANA, 最近一直被热炒的数据库产品。从2011年6月27日由SAP公司正式提供给客户使用开始算起,其用户出现爆炸式增长。一...
  • iihero
  • iihero
  • 2012年11月27日 06:58
  • 4483
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SAP HANA 9. SQLScript Basic 之 Create an SQLScript Procedure with Calculation Engine (CE) Functions
举报原因:
原因补充:

(最多只允许输入30个字)