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

来源：互联网发布：淘宝网络游戏点卡类目编辑：程序博客网时间：2024/05/29 15:55

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

2、Local Table Type中定义本地类型

/*You can create new table types and use them as input and output parameters in a procedure.The table types must be defined using SQL syntax, for example:CREATE TYPE <Type Name> AS TABLE (<filed Name1> <Filed Type1>, <filed Name2> <Filed Type2>,...).*/create type tt_bp_addresses as table (PartnerId nvarchar(10),PartnerRole nvarchar(3),EmailAddress nvarchar(255),CompanyName nvarchar(80),AddressId nvarchar(10),City nvarchar(40),PostalCode nvarchar(10),Street nvarchar(60))

$计算机生成了可选文字: 乙‘ApHANADeve,opmen，瓜‘AP学习资料＼HAN从c'ouds卜areRepo、‘,o州orks卜op＼、e、，'ona\0o\mode's\get--bn--addre，、e、一呱ro,e一sq‘·prooedure一S丽HANAStud‘。1口『月口口口口口口口口FIIeEditNavigateSearchprojectRunWindowHelp:r当，履自｝争，O，甲，i勿甲xplore。小吃弘p日ANARep。，;tor;g'甲令中甲甲,'SAp"ANAs'"'m尽宁get-bp一dd『e,,e，一b二『o'e-,q’·p『oCedu『e备心巴HOB一“QL鱼n,o'elHDB(SYSTEM)uvolbawcub45qagPtgu.vm.cld.sroo司曰sAp日ANA口〔良sQLscri醉一Local丁able砂pes…/*'Youcancreatenewtabletypesandusethemasinputandoutputparametersinaprocedure.ThetabletypesoustbedefinedusingSQLsyntax,forexaople:cREATEWPE<TypeNaoe>ASTABLE(<filedNamel><FiledTypel>,<filedNameZ><FiledTypeZ>，…）.*/createtypett_bp_addressesastable(partner工dnvarchar(le),partnerRolenvarchar(3),巨。ai1Addressnvarchar(255),CoopanyNamenvarchar(se),Address工dnvarchar(10),citynvarchar（姆）,postalcodenvarchar(10),Streetnvarchar(6e))！蜡卜eIloodata〔Errorwhileinitializingteamprovider]」砺opensApl【HDB(sysTEM,'works卜op.se、siona.oo')]卜以JavascriptRe,ource,卜身data‘身model,比get--bp一ddresse,_b火role_,ql.procedure昆．x,acce,,盛己·xsapp比·x,privilege,。，吩RetrieveData.xsjs$

3、SQLScript Tab页中代码创建Procedure

CREATE PROCEDUREget_bp_addresses_by_role_sql (    in partnerrole nvarchar(3),    out bp_addresses tt_bp_addresses)LANGUAGESQLSCRIPTSQLSECURITY INVOKERREADSSQL DATA ASBEGIN/*****************************Writeyour procedure logic *****************************/ bp_addresses =selecta."PartnerId", a."PartnerRole", a."EmailAddress",a."CompanyName",a."AddressId",b."City", b."PostalCode", b."Street"from"SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::businessPartner"as ainner join"SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::addresses"as bona."AddressId" = b."AddressId"wherea."PartnerRole" = :partnerrole;END;

$计算机生成了可选文字: 圃瓜百日ANAoevelopment下石属药资料＼日ANA\clouds卜areRepository\Workshop\sessiona\0o\models\oet--b"ddresses_by--role--sol.procedur。一；Ap日ANAstudi。11一。FileEditNavigate；肖，，履自石project〔xpl。rerSearchproject代unwindowHelp｝争，O,：甲，l翻，御，令中，,醋｛9SAp日A,吃sAp日ANARepo,ito'e‘零SAp"ANAS""m粼l序get-bp_addresse，一犯ro!e-sql.procedure面荟巴”HD”一SQLCO·’o'e'·猎helloodata[Errorw卜ileinit;alizingteamprovider]。砺opensApl[HDB(sYSTEM.'Workshop.,essiona.oo')]、峨JavascriptRe,ouroe,〔，身data‘岛model,比get--bp_addresses_b犯role_sql.proced。reO·xsaccess以己．xsapp匕。·xsprivileges：·图RetrieveData.xsjsHDB(SYSTEM)uvolbawcub45qagptgu.vm.cld.,roosQLscr'p,l巨，Tab,e砂pes…cR〔A下〔pRoc〔ouREget_bp_addresses_by--role_sql(inpartnerrolenvarcha。（3),outbP--addressestt_bp_addresses)LAN石UAGESQLSCRIPTSQLSECURITYINVOKERREADSSQLDATAASBEGIN出出介右出水众水水出水水水水水冲‘犷尸iteyourprocedurelogic东沐．东军率率宋水木宋出＊************,bp_addresses=selecta."Partnerld'",a."PartnerRole'",a."Emai执ddress",a.'"Addressld",b."city'",b.'"Postalcode",b."Street'"fr俪”SAP_HANAeeEP州eeDE阳”."5ap.hana.de帕content.epm.data::innerjoin"SAP＿印幼叭‘EP性DE阳”."sap.hana.deocontent.epo.ona。”Addressld”二b。”Addressld"wherea."PartnerRole"=:partnerrole二a."company卜通ame",bUSineSSP己rtner'．己S己dat日：:addreSSeS'.asbEND;l$

4、Team→Commit& Activate之后，在SQL Console中调用代码，生成procedure，如下图所示：

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

$计算机生成了可选文字: Filer全EditNavigateSearchprojectRunWindowHelp，图履已ojectExplorer｝争，O,：甲，｛勿，御，令中，,弘p目ANARepo、；to'e}?.，。Ap日ANAsy耽em,奢东鲁砂i竺呼于‘碗’'p一ddres·es-bv－」面曰兰三圣1)HD'(SYST七M)uvolbawcub45qagptgu·Vm·cld·，r…entschema'HDB一SQLCon,olel跳气l回日，趾－SA几HANA--EpM_DEMO)。息HDB(SYSTEM)SApHANACloudS卜areTe、t幽Backup全Catalog．己publlcSynonyms少侣HELLO-OOATA[＞侣SAp一ANA--EpM_DEMO，侣sAp-XS_LM侣SYS侣SySTEM侣UIS名WORKSH0pA-00侣＿SYS_BI‘侣一YS_BIC[‘才ColumnViews己Functions已Indexe,‘匕二procedures己TableTypes咙workshop．、es、iona.oo.model,/get-bp_addres、e,_b犯role_,ql二，Sequences[，臼Synonym,[＞臼Tablest）臼Trigger,l;l~‘示eS网＿__1lllcall"_SYS_BIC".”湘rkshop.seSSiona矗！0…邑中，中，.OO.models/get_bp_addre：、es_by--role_sql"(partnerrole=>'02',bp_addresses=＞于partnerldpartnerRo!e123456EmaiiAddre、，CompanyNamedagmar.,chulze@beckerberlin.deBeckerBerlinfranklinjones@pear一computing.compearComputingServicesigor.tarassow@retc.ruRussianElectronicTrad…ant卜ony.lebouef@crtu.caC.R.T.U.sunita一kapoor@it一trade.inIndianITTradingCom…pawel一lewando,ki@ca任．plChemiaATechnicznieF…Addres'Id1创X心0D0351O（犯000）试1侧X心00DS41创X心000631侧X心000731创X沦00074CityBerlinAtlant之Mosk之Montr,MumbWar'cl一』一‘一successfullyexecutedretched6r洲（s)ineinlII5470ms82卜s111829ps(serverprocessingms331(serverprocessingt场e:et伽e:115ms。“s)目p『oblems跳＼＼旦propertiesH{story二J‘山一一压口、，竺＿_卜s)，一，口列$