关闭

SAP HANA 10. Create an SQLScript Procedure with Imperative Logic

标签: SAPHANA内存计算列存储sql
1575人阅读 评论(0) 收藏 举报
分类:

SAP HANA SQLScript Imperative Logic 包括 IF+ELSE+ENDIF while循环、For循环、 Case等。

 

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

 

2Local Table Type

 

create typett_procuct_sale_price as table (
ProductIdnvarchar(10),
Categorynvarchar(40),
Price nvarchar(20),
SalePricenvarchar(20)
)


 

计算机生成了可选文字: 汤sAp日ANAoevelopment一。:\sAp学习资料\日ANA\cloudshareRepositol叭Workshop\、essiona\00\models\get_product_、ale--price.procedure一sAp日ANAstudioFileEditir当,日乙-Navigate喃_勺SearchprojectRunWindowHelp;争,O,飞,甲,!乞,,令中,,叼projectExpl吸句呀get-Prodoct--,"!畔JsAp日ANAR?。gApHANA91口e_prICe.pFOHDB一SQLConsoleZooedure叭鲤:匹下AP-HANA-EpM一DEMO”·”ap·卜ana·democon,en,·‘[HDB(SYSTEM,'Workshop.helloodata')][HDB(SYSTEM,'Workshop.sessiona.OO')]HDB(SYSTEM)uvolbawcub45qagptgu.vm.cld.,roo壑到丝Loca'Tab,e砂pesL/*Youcancreatene脚tabletypesandusethemasinputandoutputparametersinaprocedure.Thetabletypesoustbedefinedusing义Lsyntax,forexaople:cREATE,I-YPE<TypeName>ASTASLE(<filedNamel><FiledTypel>,<filedNaoeZ><FiledTypeZ>,…).*/createtypett_procuct_sale_priceastable(product工dnvarchar(le),categorynvarchar(帕),pricenvarchar(Ze),salepricenvarchar(20)),吐JavascriptRe沁urces卜身data,姗model,疡get--bp一addresses_by_role_ce.procedure比get-bp_addresse、_b犯role_:ql.procedure比get--Product--saIe--price.procedure一0·xsaccesS一〕·xsapp昆·x,privilege,卜图RetrieveData.xsjs

 

 

3SQLScript

CREATE PROCEDUREget_product_sale_price(
    in productid nvarchar(10),
    out product_sale_pricett_procuct_sale_price
)
LANGUAGESQLSCRIPT
SQLSECURITY INVOKER
READSSQL DATA AS
BEGIN
/*****************************
Writeyour procedure logic
 *****************************/
declare lv_categorynvarchar(40) := null;
declare lv_discountdecimal(15,2) := 0;
lt_product = select"ProductId", "Category", "Price"
from"SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::products"
where"ProductId" = :productid;
select"Category" into lv_category from :lt_product;
if :lv_category ='Notebooks' then
lv_discount := .20;
elseif :lv_category= 'Handhelds' then
lv_discount := .25;
elseif :lv_category= 'Flat screens' then
lv_discount := .30;
elseif :lv_categorylike '%printers%' then
lv_discount := .30;
else
lv_discount := 0.00;
end if;
product_sale_price =
select"ProductId", "Category", "Price",
"Price"-cast(("Price"* :lv_discount) as decimal(15,2)) as "SalePrice"
from :lt_product;
END;


 

计算机生成了可选文字: 汤sAp日ANAoevelopment一。:\sAp学习资料训ANA\cloudshareRepos汪Ol叭Wor.晰OP\Se,siona\00,、mode.s\get--product_sale_p「ice.procedure一sAp日ANAstudio三1Ie互dit丝avigates胆rchZroject旦un巡indow旦elp:肖,周履自}参,O甲%,;甲,}乞,御毛projectSApHANAR…?。SApHANA,令巾,中甲1‘宁·getxr。d。ct-,。l"'ce.pr。。edure·‘昭_返·SAP-HANA-Ep叼二石巴”HOB一“QLCon'oleZ醋国巫_DEMO':,ap.hana.democontent.eHDB(SYSTEM)uvolbawcub45qagptgu.vm.cld.sroo磷helloodata[HDB(sysTEM,'workshop·helloodata')]潇opensApl[HDB(SYsTEM.'Workshop.sessiona.oo')]二,公JavascriptRe沁urces卜身data,姗model,疡get--bp_addresses_b犯rol几ce.procedure比get-bp_addre,se,_b犯rol气,ql.procedure比get-product--sal却rice.proced。re昆.xs。。cess昆.xsapp昆·x,privilege,〔》吩RetrieveData.xsjsLocalTableTypescREATEPRocEDUREget_product_sale_price(inproductidnvarchar(le),outproduct_sale_pricett_procuct_sale_price)LAN6UA6ESQLSCRIPTSQLSECURITYI付VOKERREADSSQLDATAASBEGIN一ldeclareIv--categorynvarchar(妈):,null二declareIv--discountdecioal(15,2):=e;It_product=select"Productld","category","Price"fr阅”SAP_HANA--EP性DE阳”.'"sap.hana,de阳content.epm.data:树here"Productld"=:productid;select"category"intol七categoryfr俪:It_product二if:IV--category='Noteb00ks'thenIv--discount:=.20;elseif:Iv--category,'Handhelds"thenIv--discount:=.25;elseif:Iv--category='Flatscreens'thenIv_discount:=.30;elseif:Iv--categorylike'%printers%'thenIv--discount:=.30;e1SeIv_discount:=e.的;endif;product_sale_price二select"product工d'","category","Price'",",Price”一cast(("Price"*:Iv--discount)asdec玩al(15,z))fr阅:It_product;END二:products"as'.SalePrice'"

 

4TeamCommit& Activate之后,在SQL Console中调用代码,执行结果如图所示

call"_SYS_BIC"."WorkShop.sessiona.00.models/get_product_sale_price"(
productid =>'HT-1000',
product_sale_price=> ?)


 

计算机生成了可选文字: 汤sAp日ANAoevelopment一c:\Windows\s"tem32\sQLconsoleZ一sAp日ANAstudio巨卜,,.目口FIIeEditNavigatesearchprojectRunwindowHelp{肖,周履自}争,O,飞,;矛,}勿,御,‘石project〔xpl小吃弘pHANARl?。SAp"ANAS_县县卜冯一令中,ty"get-P中,roduct声ale-price.procedure区皿tHOB一SQLCon,o,eZ自己”SA瞥ANA-印M一D〔MO’·”,'p·卜‘n‘·democon,en,·e口巨卜el100data州二:openSApl〔H二三引气O皿井,‘一1,磷眺J?)l目JavagcriptRe,ources〔,身data,身model,比get--bp_addresses_b犯role_ce.proced。re眺get-bp_addres,e,_b犯role-,ql.proced。re比get--Product--sale--price.procedure_口·xsaccess昆.x,app昆·x,privilege,·图Retr;eveData.xsjsHoB(s丫sT〔M)uvolbawcub45qagptgu·vm·cld·Sroo血SQL尸Resultcall"_SYS_Blc".”吻rkshop.sessiona.Oe.models/get_product_sale_price"(productid=>'HT一1。。。product_sale_price=>lprodu出d日丁一1000CategoryNotebooksprice956.00Saleprice764.80Statement'call"_SYS_Blc".”吻rkshoP.:e:。iona.ee二dels/get_Product_:ale_price"(productidsuccessfullyexecutedin5260541卜s(serverprocessingtioe:82ms342甘s)Fetchedlr洲(s)inems18卜s(serverprocessingtime:0mse林s)二>'HT一leOS',

 

 

 

 

 

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:57349次
    • 积分:757
    • 等级:
    • 排名:千里之外
    • 原创:17篇
    • 转载:13篇
    • 译文:0篇
    • 评论:15条
    文章分类
    最新评论