茉莉网
当前位置:首页»其它

oracle sql脚本编写 ORACLE+SQL+高级编程脚本

2018年02月08日 来源:oracle sql脚本编写 大字体小字体

   8-3SELECT  country,region,product,YEAR,week,sale,        MAX(sale)OVER(PARTITIONBYproduct,country,region,YEARORDERBYweekROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)                                                                    max_sale   FROMsales_fact  WHEREcountry='Australia'ANDproduct='XtendMemory'ORDERBYproduct,country,YEAR,week

  SELECT  country,region,product,YEAR,week,sale,        LAG(sale,1,sale)OVER(PARTITIONBYcountry,region,productORDERBYYEAR,         week)prion_wk_sale   FROMsales_fact  WHEREcountry='Australia'ANDproduct='XtendMemory'ORDERBYproduct,country,YEAR,week

  8-3/*Formattedon2013/02/0213:54(FormatterPlusv4.8.8)*/SELECTsales_fact.*,      SUM(sale)OVER(PARTITIONBYcountry,product,region,YEARORDERBYweekROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)                                                                    sum_sale FROMsales_fact WHEREcountry='Australia'  /*Formattedon2013/01/2815:21(FormatterPlusv4.8.8)*/SELECTcountry_namecountry,country_subregionregion,prod_nameproduct,      calendar_yearYEAR,calendar_week_numberweek,AMOUNT_SOLD, FROMsalesa1,timesa2,customersa3,countriesa4,productsa5 WHEREa1.time_id=a2.time_id  ANDa1.prod_id=a5.prod_id  ANDa1.cust_id=a3.cust_id  ANDa3.country_id=a4.country_id    selectSUM(sale)OVER(PARTITIONBYproduct,country,region,YEAR),sales_fact.*fromsales_factWHEREcountry='Australia'ANDproduct='XtendMemory'

   publicvoidimp(StringfileName){  try{   System.out.println("导入开始-----");   //登网站   Processprocess=Runtime     .getRuntime()     .exec(       "sqlplustest/[email protected]@g:/ccc.sqlcommiton");   System.out.println("导入结束-----");  }catch(Exceptione){   System.out.println("导入异常-----");  } }

  8-1/*Formattedon2013/01/2815:42(FormatterPlusv4.8.8)*/createtablesales_factasSELECT  country_namecountry,country_subregionregion,prod_nameproduct,        calendar_yearYEAR,calendar_week_numberweek,SUM(amount_sold)sale,        SUM( amount_sold             *(CASE                   WHENMOD(ROWNUM,10)=0                      THEN1.4                   WHENMOD(ROWNUM,5)=0                      THEN0.6                   WHENMOD(ROWNUM,2)=0                      THEN1.2                   ELSE1                END               )            )receipts   FROMsalesa1,timesa2,customersa3,countriesa4,productsa5  WHEREa1.time_id=a2.time_id    ANDa1.prod_id=a5.prod_id    ANDa1.cust_id=a3.cust_id    ANDa3.country_id=a4.country_idGROUPBYcountry_name,        country_subregion,        prod_name,        calendar_year,        calendar_week_number                        select*fromsales_fact  /*Formattedon2013/02/0117:29(FormatterPlusv4.8.8)*/SELECTsales_fact.*,      SUM(sale)OVER(PARTITIONBYcountry,product,region,YEARORDERBYweekrowsbetweenunboundedprecedingandcurrentrow)sum_sale FROMsales_fact WHEREcountry='Australia' 8-2/*Formattedon2013/02/0117:29(FormatterPlusv4.8.8)*/SELECTsales_fact.*,      SUM(sale)OVER(PARTITIONBYcountry,product,region,YEARORDERBYweek) FROMsales_fact WHEREcountry='Australia'

  实际上c1102000.sql脚本会在升级数据字典即catupgrd.sql脚本运行时被调用,而e1102000.sql则会在降级数据字典版本即catdwgrd.sql脚本运行过程中被调用。

  SELECT  country,region,product,YEAR,week,sale,        first_value(sale)OVER(PARTITIONBYcountry,region,product,yearORDERBYsaledesc)top_sale_week   FROMsales_fact  WHEREcountry='Australia'ANDproduct='XtendMemory'ORDERBYproduct,country,YEAR,week

  8-2/*Formattedon2013/01/2816:22(FormatterPlusv4.8.8)*/SELECTregion,YEAR,week,sale,product,      SUM(sale)OVER(PARTITIONBYproduct,country,region,YEARORDERBYweekrowsbetweenunboundedprecedingandcurrentrow)running_sum_ytd FROMsales_fact WHEREcountry='Australia'ANDproduct='XtendMemory'orderbyyear,week

  如:在c:temp目录下有文件start.sql和nest_start.sql,start.sql脚本文件的内容为:

  在$ORACLE_HOME/bin下有个dbstart脚本可以启动数据库,但是执行之后提示:

  8-9/*Formattedon2013/02/0214:35(FormatterPlusv4.8.8)*/SELECT  sales_fact.*,        LAST_VALUE(sale)OVER(PARTITIONBYcountry,product,region,YEARORDERBYsaleDESCROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)                                                              low_sale_value,        LAST_VALUE(week)OVER(PARTITIONBYcountry,product,region,YEARORDERBYsaleDESCROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)                                                               low_sale_week   FROMsales_fact  WHEREcountry='Australia'ORDERBYproduct,YEAR,week

  8-4/*Formattedon2013/02/0213:54(FormatterPlusv4.8.8)*/SELECTsales_fact.*,      max(sale)OVER(PARTITIONBYcountry,product,region,YEARORDERBYweekROWSBETWEEN2PRECEDINGAND2FOLLOWING)                                                                    sum_sale FROMsales_fact WHEREcountry='Australia'  8-6 /*Formattedon2013/02/0214:10(FormatterPlusv4.8.8)*/SELECTsales_fact.*,      LAG(sale,10,sale)OVER(PARTITIONBYcountry,product,regionORDERBYyear,week)                                                                    pre_sale FROMsales_fact WHEREcountry='Australia'  8-7 /*Formattedon2013/02/0214:10(FormatterPlusv4.8.8)*/SELECTsales_fact.*,      LEAD(sale,10,sale)OVER(PARTITIONBYcountry,product,regionORDERBYyear,week)                                                                    prior_wk_sale FROMsales_fact WHEREcountry='Australia'  8-8 /*Formattedon2013/02/0214:35(FormatterPlusv4.8.8)*/SELECT  sales_fact.*,        FIRST_VALUE(sale)OVER(PARTITIONBYcountry,product,region,YEARORDERBYsaleDESCROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)                                                              top_sale_value,        FIRST_VALUE(week)OVER(PARTITIONBYcountry,product,region,YEARORDERBYsaleDESCROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)                                                               top_sale_week   FROMsales_fact  WHEREcountry='Australia'ORDERBYproduct,YEAR,week

相关内容

精选文章

Copyright © 2015 茉莉网 http://www.szmlwh.cn. All rights reserved.