SAP HANA 6. 创建schema、table、view、sequence、role

1、创建schemaWORKSHOPA_00.hdbschema

计算机生成了可选文字: 西:Ap日ANAoeve.opment二opensApi/data阴oRKs日opAj元蕊,c,,e橱一sAp薛而悄stud;o气口叮FIIeEditNavigatesearchprojectRunwindowHelpi肖,图履已}争,O,%,甲,}回图}乞,御,令中,中,石proj石〔币.霜百盆吃痴而丽耐。,it。‘e户?。。Ap日ANAsystemg‘一口l值丽疏Ks日opA-oo.hdbs。卜e小、甲日oB-!卜嵘卜eIloodata[HDB(SySTEM,'WorkS卜op.helloodata')]引一。佛openSApl[HDB(SYSTEM,'Workshop.sessiona.OO')]l一:,峨JavascriptRe:ource,WorkS卜op.sessi引引引,身data_〕header.hdbtable_〕item.hdbtable_〕orderid.卜dbsequence心ordersExt.hdbview.te,t.卜dbview.。。w0RKsHOPA--oo.卜dbsc卜ema昆work,hopAdmin.hdbrole昆work、卜opU,er.卜dbrole口·Xsaccess心·XSapp昆·x,privileges;·巴RetrieveData·xsjs

代码:

schema_name="WORKSHOPA_00";


 

2、创建tableheader.hdbtableitem.hdbtable

计算机生成了可选文字: Filer全EditNavigatesearchprojectRunwindowHelp,图履自}争,O,飞,;矛,}回图}翻,扣,今巾,中,醋19sAp日A一.‘毛。roj石。x--n.。rer小吃sAp日ANARep。,ito'e二)?。sAp日ANAsystem,曼卜·幽·卜dbt·b,·厦,t一卜dbt·b,·‘-·碟helloodata_HDB(sysTEM,'Workshop.卜elloodata')]。唠opensApl[HDB(sYSTEM,'Workshop.,essiona.oo')]:·目JavascriptRe,ouroe,日身data:。卜eader.hdbtable昆item.hdbt。ble匕乙orderid.卜db,equence岛ordersExt.hdbviewj〕te,t.卜dbview‘。WORKSHOPAeeOO.卜dbschema昆workshopAdmin.hdbrole昆workshopU,er.hdbrole口·XSaccess岛·xsapp昆·x,privileges〔巴RetrieveData.xsjs卜abie.scheoaName=”嘟RKs旧PA--的”;table.tableType=coL明NSToRE;table.description=”吻rkshoporderItem";table.columns=[{name="orderld'";sq1Type=NvARCHAR;n娜11abl母=false;length=10)co.ent="orderID";},{name,"orderlte。”二sq1Type,NvARcHAR;nullable,false;len酥h,le;co.ent,"orderIte。”;},{nome="Productld";sq1Type=NVARcHAR;nullable=false;len酥h=le;co.ent="ProductID";},{name="Quontity";sq1Type二DEcl拟L,nulloble=false;precision二13;scale=3jdefaultValue='"0,”二co,ent="Quantity'";},{name="QuantityUnit”二sq1Type=NvARcHAR二n以11able=false二length=3二co.ent="Quantityunit";},{name,"DeliveryDate";sq1Type,DATE;nollable,true;c~ent,"scheduledoeliveryDate";}];table.primaryKey.pkcoluons二["orderld'',"orderlte。”]二匕pr。blem,蔽不已propertiesl百日isto,l卜~~~'..J口口口口口口口口口弋夕仁0itemsDe,cription

Header.hdbtable代码:

table.schemaName ="WORKSHOPA_00";
table.tableType =COLUMNSTORE;
table.description ="Workshop Order Header";
table.columns = [
{name ="OrderId"; sqlType = NVARCHAR; nullable = false; length = 10; comment= "Order ID"; },
{name ="CreatedBy"; sqlType = NVARCHAR; nullable = false; length = 10;comment = "Created By"; },
{name ="CreatedAt"; sqlType = DATE; nullable = false; comment ="Created At Date and Time"; },
{name ="Currency"; sqlType = NVARCHAR; nullable = false; length = 5; comment= "Currency Code"; },
{name ="GrossAmount"; sqlType = DECIMAL; nullable = false; precision = 15;scale = 2; defaultValue = "0";
comment ="Total Gross Amount"; }
];
table.primaryKey.pkcolumns= ["OrderId"];


 

Item.hdbtable代码:

table.schemaName ="WORKSHOPA_00";
table.tableType =COLUMNSTORE;
table.description ="Workshop Order Item";
table.columns = [
{name ="OrderId"; sqlType = NVARCHAR; nullable = false; length = 10; comment=
"OrderID"; },
{name ="OrderItem"; sqlType = NVARCHAR; nullable = false; length = 10;comment =
"OrderItem"; },
{name ="ProductId"; sqlType = NVARCHAR; nullable = false; length = 10;comment =
"ProductID"; },
{name ="Quantity"; sqlType = DECIMAL; nullable = false; precision = 13;scale = 3;
defaultValue ="0";
comment ="Quantity"; },
{name ="QuantityUnit"; sqlType = NVARCHAR; nullable = false; length = 3;comment =
"QuantityUnit"; },
{name ="DeliveryDate"; sqlType = DATE; nullable = true; comment =
"ScheduledDelivery Date"; }];
table.primaryKey.pkcolumns= ["OrderId","OrderItem"];


 

3、创建sequenceorderid.hdbsequence

计算机生成了可选文字: 肠:Ap日ANAoeve,opment一opensApi/dat。/order'd·hdb,equence巴,Ap日ANA,tudi刃口夕口口口气口口口口口口口口口‘l三1Ie互dit丝aviqates胆rchZroiect旦un业indow旦elp:肖,图履自}争,O,飞,;矛,}回图}翻,扣,今巾,中,’石proj石〔x--n.。rer小吃sAp日ANARep。,it。‘e二,?。。Ap日ANAsy、tem,圃卜二d·恤但·b,·韭吵m五dbt·b‘·媳Orde而·hdb,equence任奋口口磷helloodat‘州二二。荡OPenSApl〔日:三】‘·二目JavagcriptRe,ources。身data:。卜eader.hdbtable昆item·hdbtable飞orderid·hdb,equence岛ordersExt.hdbviewj〕te,t.卜dbview‘。w0RKsHOPAeeoo·卜dbschema昆workshopAdmin.hdbrole昆workshopU,er.hdbrole口·XSaccess心·xsapp困·xsprivileges;·巴RetrieveData·xsjs」气马schema=”嘟RKs旧pA--阅”;start少ith二2。。0。。。。。。;depends_on_table=”叨rkshop.sessiona.的.data::header";匕prob.ems不画propertoesl百日。sto,l弋夕叹二二口0itemsDe,cription;'l

代码:

schema="WORKSHOPA_00";
start_with=2000000000;
depends_on_table="workshop.sessiona.00.data::header";


 

4、创建viewordersExt.hdbview

计算机生成了可选文字: 肠‘ApHANADeve'opmen,一opensApl/)元画还画弃启宙砂一SApHANAs,u"‘。习口口尸口口口网口口日口口口口三1Ie互dit丝aviqates胆rchZroiect旦un业indow旦elp:肖,图履自}争,O,飞,;矛,}回图}翻,扣,今巾,中,‘毛。roj石。x--n.。rer小吃sAp日ANARep。,ito'e二)?。sAp日ANAsystem,曼卜二d一卜dbt·b,·但少哑dbt·b‘·追:·d·“d·卜赢碗·…为Ord一〔xt.卜dbvlew·碟helloodata_HDB(sysTEM,'Workshop.卜elloodata')]。唠opensApl[HDB(sYSTEM,'Workshop.,essiona.oo')]:·目JavascriptRe,ouroe,日身data‘。卜eader.hdbtable昆item.hdbt。ble昆orderid.卜db,equence心ordersExt.hdbview〕te,t.卜dbview‘。WORKSHOPAeeOO.卜dbschema昆workshopAdmin.hdbrole昆workshopU,er.hdbrole口·XSaccess心·xsapp困·x,privileges;·巴RetrieveData.xsjs卜che,a=',嘟RKs旧PA--的”;query="selectT。八”orderld\",Tl.\"orderltem\",下e.\"created日y\'",TO.\"CreatedAt\'",TI.\"Productld\",Tl.\"Quantity\",Tl.\"QuantityUnit\"fro.\”、幻RKs日。户Aeeoe\”八”吻rkshop.sessiono.的.data::header\"Teleftouterjoin\”嘟RKS旧PA--OS\”八”吻rkshop.sessiona.的.data::ite。\"TlonTO.\"orderld\”二Tl.\"orderld\"orderbyTe.\"orderld\"ASc";depends一n_table=['”吻rkshop.sessiona.的.data::header",”吻rkshop.sessiona.的.data口巨匕prob.ems不画propertoesl百日。sto,l弋夕叹二二巨0itemsDe,cription

代码:

schema="WORKSHOPA_00";
query="selectT0.\"OrderId\",
T1.\"OrderItem\",
T0.\"CreatedBy\",
T0.\"CreatedAt\",
T1.\"ProductId\",
T1.\"Quantity\",
T1.\"QuantityUnit\"
from\"WORKSHOPA_00\".\"WorkShop.sessiona.00.data::header\" T0
left outer join\"WORKSHOPA_00\".\"WorkShop.sessiona.00.data::item\" T1
onT0.\"OrderId\" = T1.\"OrderId\"
order byT0.\"OrderId\" ASC";
depends_on_table=["WorkShop.sessiona.00.data::header","WorkShop.sessiona.00.data::item"];

 

activate过程中发生错误如下:

计算机生成了可选文字: 履sAp日ANAoevelopment一opensApi/data/ordersExt.卜dbview一sAp日ANAstudio口哪脚File:r当Z奋~EditNavigateSearchprojectRunWindowHelp争,O,:甲,醋l曰sAp图一叼projectEx叨压SAp日ANA?。sAp日ANA口口甲~。,令中,图ltem.卜dbtableorderod.卜db,equencef图orders〔xt.卜dby;ew小逻俪一woRKs日opA--00.w气〔二」曰斗,0sche帕=”嘟RKs旧pA--Oe"';query="selectT。八”orderld\",Tl.\"orderltem\",Te.\"createdBy\,,,TO.\'"CreatedAt\",Tl.\"Productld\",Tl.\"Quantity\",0Ti.\"Quantityunit\"fr俪\”嘟RKs旧pA--ee\”八”workshop.sessiona.的.data::header\"Teleftouterjoin\”嘟RKS旧PA--Oe\”八”.rkshop.sessiona.Oe.data::ite。\"TlonTO.\"orderld\"=Tl八”orderld\"orderbyTe.\"orderld\'"ASc";depends一n_table=['"workshop.sessiona.的.data::header'",'".rkshop.sessiona.的.data::ite。”];一/―一占L‘七尸}一一na,一一n一VJn一一‘一nl卜一一一olle二心一nLQ一一,一ya二一S否Ll二一JOJD日aVVnn.抽口口口.自口口口;.山口SS‘嵘helloodata[HDB(sysTEM,'Workshop·helloodata')]卜麟JavascriptResources爵·x,access昆·xsapp昆HELLO一DATA·卜dbschema昆卜e110·xsodata昆otable.hdbtable。磷OpenSApl[HDB(SysTEM,'Workshop·,es,iona·oo')]「心JavascriptRe,ource,。肠data昆卜eader.hdbtable昆item·hdbtable昆orderid·卜dbsequence面orders〔xt.hdbview昆w0RKsH0pA-oo·hdbsc卜ema曲.xsaccess』·xsapp民RetrieveData·XsjS….如b·二不::二rties}乙errors,Uw日rnln95,Uotners白l日isto心De,criPtion'0Error。(,;全户m。、ACtivate。atementexecution,pleaseexecution,plea,eSeethedatabaseerrortracesforadditionalddait卜edatabaoeerrortrace'foradditionaldetaiOr陌Repo,ito心:Internalerrorduring:tatementSeeOO

经检查是package大小写问题,因建packageWorkShop命名中有大写,workshop.session<session>.<group>大小写调整后,程序运行成功。

 

5、创建roleworkshopUser.roleworkshopAdmin.role

计算机生成了可选文字: 履SApHANADevelop塑丝全一opensApl/data/wro!e一SApHANAStudio刀r飞【勺吐月吐州口口口口口口口口口口口口口三1Ie互dit丝avigates胆rchZroject旦un巡indow旦elp:肖,图瘾自}参,O甲%,;甲,}勿,别,令巾,中甲毛project〔xpl。rer吃弘p日ANARepo,;to'e,'?。sApHANASy,tems夕石石’l厂画wor。,卜。p。ser.卜dbr。.百涓卜rk,卜opAdm,n·卜““『o,e邑.‘一一口亡曰每,卜磷helloodata[HDB(sysTEM,'workshop·helloodata')]·潇opensApl[HDB(SYsTEM.'Workshop.sessiona.oo')],吐JavascrlptRe:ource:日身data:。卜eader.hdbtable二〕item.hdbtable匕。orderid.hdb,equence岛ordersExt.hdbviewj〕te,t.卜dbview‘。WORKSH0pAee00.卜dbschema昆workshopAdmin.hdbrole昆workshopU,er.hdbrole口·xsaccess心·xsapp困·x,privilege,;·巴RetrieveData.xsjs一role吻rkshop.sessiona.的.data::哟rkshopAdmineXtendsrole吻rkshop.sessiona.OO.data::叨rkshopUser{catalogsch饰a”阴RKS旧PA--的”:SELEcT,INSERT,UPDATE,DELETE,DRoP;applicationprivilege:吻rkshop.sessiona.的::Admin;}prooerties弋夕匕二[

workshopUser.role代码:

roleWorkShop.sessiona.00.data::workshopUser {
catalogschema "WORKSHOPA_00": SELECT;
    application privilege:WorkShop.sessiona.00::Basic;
 
}


 

workshopAdmin.role代码:

roleWorkShop.sessiona.00.data::workshopAdmin
extends roleWorkShop.sessiona.00.data::workshopUser
{
catalogschema "WORKSHOPA_00": SELECT, INSERT, UPDATE, DELETE, DROP;
    application privilege:WorkShop.sessiona.00::Admin;
 
}


 

6、将role权限授权给username

计算机生成了可选文字: 履‘ApHANADeve‘叩画二D:\SAp学习资料,HANA\HANASQL乙不诚ha厩。:er.,。益扩‘ApHANA',udi峨月口口已月口口口口口口口口口口口口口口口File:r全Edit闪。vigoteSearch尸:c!j。:士RunWindow日elp,图励自i争,O,飞,:矛,i,御,令中,牵,’石proj石〔x--n.。rer小吃sAp日ANARep。,it。‘磊gApHANASystems、丽·追WOrk·卜OpAdm‘·上d"ro,·ntodatabase醋!百sAp日ANA口将role冤给username.,ql一乞一夕磷helloodat‘州二二。疏OPensApl〔日:三”目JavagcriptRe,ources。身data:。卜eader.hdbtable昆item·hdbtable昆orderid·卜db,equence岛ordersExt.hdbviewj〕te,t.卜dbview匕〕w0RKsHOPAeeoo.卜dbschema匕。workshopAdmin·hdbroie匕〕workshopU,er.hdbrole口·XSaccess心·xsapp困·xsprivileges;·巴RetrieveData·xsjs翻奋·O…巳口巨」气马血SQL一11SYSREPo"."6RANT一cTIvATED_RoLE"(’吻rkshop..SeSSIOna.OO.data::啪rkshopAdmin','SYSTE州’);卜”匕pr。b.em,蔽燕西propertoesl百日。sto,l弋夕叹二二[0itemsDe,cription

SQL语句:

call "_SYS_REPO"."GRANT_ACTIVATED_ROLE"('WorkShop.sessiona.00.data::workshopAdmin','SYSTEM');


注意:username必须为已经存在的用户,不能自动创建。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值