SAP HANA 10. Create an SQLScript Procedure with Imperative Logic

原创 2013年12月02日 21:37:13

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',

 

 

 

 

 

相关文章推荐

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_...

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 ...

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...

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的读书...

The Top 10 Reasons SAP HANA Is Disrupting Larry Ellison's Grand Plans

http://www.forbes.com/sites/sap/2011/06/23/the-top-10-reasons-sap-hana-is-disrupting-larry-ellisons-...
  • sony315
  • sony315
  • 2011年10月12日 22:51
  • 635

using sap businessobjects predictive analytics with sap hana express edition

https://blogs.sap.com/2017/03/07/using-sap-businessobjects-predictive-analytics-with-sap-hana-expres...

HANA 基础SQL和SQLScript

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

Preview of BI 2012 with Speaker Dan Kearnan: Expect SAP NetWeaver BW to Run Soon on SAP HANA

Right now, you might think of SAP HANA as a means to speed up information analysis — but by mid-year...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SAP HANA 10. Create an SQLScript Procedure with Imperative Logic
举报原因:
原因补充:

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