ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 11g model子句的用法

oracle 11g model子句的用法

原创 Linux操作系统 作者:0x0x0x 时间:2012-07-10 08:10:54 0 删除 编辑
oracle 11g model子句的用法
 
--1、语法
MODEL
[]
[]
[MAIN ]
  [PARTITION BY ()]
  DIMENSION BY ()
  MEASURES ()
  []
  [RULES] 
  (, ,.., )
  ::=
   ::= RETURN {ALL|UPDATED} ROWS
  ::=
  [IGNORE NAV | [KEEP NAV]
  [UNIQUE DIMENSION | UNIQUE SINGLE REFERENCE]
  ::=
  [UPDATE | UPSERT | UPSERT ALL]
  [AUTOMATIC ORDER | SEQUENTIAL ORDER]
  [ITERATE ()  [UNTIL ]]
  ::= REFERENCE ON ON ()
  DIMENSION BY () MEASURES ()

还是直接看例子吧!
 
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> select * from t order by years,months,product_name;
 
     YEARS     MONTHS PRODUCT_NA      SALES
---------- ---------- ---------- ----------
      2008          1 A                1000
      2008          1 B                1500
      2008          2 A                2000
      2008          2 B                3000
      2008          2 C                1000
      2008          3 A                3000
 
--用位置标记和符号标记访问数据单元
SQL> select years,months,product_name,sales from t
  2  model
  3  partition by (years)
  4  dimension by (months,product_name)
  5  measures (sales sales)
  6  rules
  7  (
  8  sales[months=4,product_name='A']=sales[months=3,product_name='A']
  9  );
     YEARS     MONTHS PRODUCT_NA      SALES
---------- ---------- ---------- ----------
      2008          1 A                1000
      2008          1 B                1500
      2008          2 A                2000
      2008          2 B                3000
      2008          2 C                1000
      2008          3 A                3000
 
已选择6行。
 
--不用位置标记和符号标记访问
SQL> select years,months,product_name,sale from t
  2  model
  3  partition by (years)
  4  dimension by (months,product_name)
  5  measures (sales sale)
  6  rules
  7  (
  8  sale[4,'A']=sale[2,'A']
  9  );
     YEARS     MONTHS PRODUCT_NA       SALE
---------- ---------- ---------- ----------
      2008          1 A                1000
      2008          1 B                1500
      2008          2 A                2000
      2008          2 B                3000
      2008          2 C                1000
      2008          3 A                3000
      2008          4 A                2000
 
已选择7行。
 
总结一:不用位置标记会将更新后的行也一起返回。
 
--使用return updated rows
SQL> select years,months,product_name,sales from t
  2  model
  3  return updated rows
  4  partition by (years)
  5  dimension by (months,product_name)
  6  measures (sales sales)
  7  rules
  8  (
  9  sales[months=4,product_name='A']=sales[months=3,product_name='A']
 10  );
 
未选定行
 
--不用位置标记和符号标记访问
SQL> select years,months,product_name,sale from t
  2  model
  3  return updated rows
  4  partition by (years)
  5  dimension by (months,product_name)
  6  measures (sales sale)
  7  rules
  8  (
  9  sale[4,'A']=sale[2,'A']
 10  );
 
     YEARS     MONTHS PRODUCT_NA       SALE
---------- ---------- ---------- ----------
      2008          4 A                2000
 
总结:使用return updated rows 以后,只返回有更新的行记录。
 
--使用ANY和IS ANY
--ANY和位置标记一起使用,IS ANY和符号标记一起使用。
SQL> select years,months,product_name,sale from t
  2  model
  3  partition by (years)
  4  dimension by (months,product_name)
  5  measures (sales sale)
  6  rules
  7  (
  8  sale[ANY,'A']=100
  9  );
 
     YEARS     MONTHS PRODUCT_NA       SALE
---------- ---------- ---------- ----------
      2008          1 B                1500
      2008          2 B                3000
      2008          2 C                1000
      2008          1 A                 100
      2008          2 A                 100
      2008          3 A                 100
 
已选择6行。
 
SQL> select years,months,product_name,sale from t
  2  model
  3  partition by (years)
  4  dimension by (months,product_name)
  5  measures (sales sale)
  6  rules
  7  (
  8  sale[months IS ANY,'A']=100
  9  );
     YEARS     MONTHS PRODUCT_NA       SALE
---------- ---------- ---------- ----------
      2008          1 B                1500
      2008          2 B                3000
      2008          2 C                1000
      2008          1 A                 100
      2008          2 A                 100
      2008          3 A                 100
 
已选择6行。
 
--总结:ANY和IS ANY简化了rules中的规则;
 
--使用currentv()或者CV()函数
SQL> select years,months,product_name,sale from t
  2  model
  3  partition by (years)
  4  dimension by (months,product_name)
  5  measures (sales sale)
  6  rules
  7  (
  8  sale[2,'A']=sale[cv(),'B']
  9  );
 
     YEARS     MONTHS PRODUCT_NA       SALE
---------- ---------- ---------- ----------
      2008          1 A                1000
      2008          1 B                1500
      2008          2 A                3000
      2008          2 B                3000
      2008          2 C                1000
      2008          3 A                3000
 
已选择6行。
 
SQL> select years,months,product_name,sale from t
  2  model
  3  partition by (years)
  4  dimension by (months,product_name)
  5  measures (sales sale)
  6  rules
  7  (
  8  sale[2,'A']=sale[cv(months)-1,'B']
  9  );
 
     YEARS     MONTHS PRODUCT_NA       SALE
---------- ---------- ---------- ----------
      2008          1 A                1000
      2008          1 B                1500
      2008          2 A                1500
      2008          2 B                3000
      2008          2 C                1000
      2008          3 A                3000
已选择6行。
 
--使用FOR循环
SQL> select years,months,product_name,sale from t
  2  model
  3  partition by (years)
  4  dimension by (months,product_name)
  5  measures (sales sale)
  6  rules
  7  (
  8  sale[for months from 1 to 3 increment 1,'A']=sale[cv(),'B']
  9  );
     YEARS     MONTHS PRODUCT_NA       SALE
---------- ---------- ---------- ----------
      2008          1 A                1500
      2008          1 B                1500
      2008          2 A                3000
      2008          2 B                3000
      2008          2 C                1000
      2008          3 A
 
已选择6行。
 
SQL> select years,months,product_name,sale from t
  2  model
  3  partition by (years)
  4  dimension by (months,product_name)
  5  measures (sales sale)
  6  rules
  7  (
  8  sale[for months in (1,2,3),'A']=sale[cv(),'B']
  9  );
 
     YEARS     MONTHS PRODUCT_NA       SALE
---------- ---------- ---------- ----------
      2008          1 A                1500
      2008          1 B                1500
      2008          2 A                3000
      2008          2 B                3000
      2008          2 C                1000
      2008          3 A
 
已选择6行。
 
--使用IS PRESENT
--如果数据单元指定的记录在MODEL子句执行之前就存在,那么IS PRESENT 返回TRUE
select years,months,product_name,sale from t
model
partition by (years)
dimension by (months,product_name)
measures (sales sale)
rules
(
sale[for months in (1,2,3),'A']=CASE WHEN sale[cv(),'B']
IS PRESENT THEN sale[cv(),'B'] ELSE 0 END
);
 
--使用PRESENTV()
--PRESENTV(CELL,EXPR1,EXPR2)如果cell引用的记录存在,则返回EXPR1,否则返回EXPR2;
SQL> select years,months,product_name,sale from t
  2  model
  3  partition by (years)
  4  dimension by (months,product_name)
  5  measures (sales sale)
  6  rules
  7  (
  8  sale[for months in (1,2,3),'A']=presentv(sale[cv(),'B'],sale[cv(),'B'],0)
  9  );
   
  YEARS     MONTHS PRODUCT_NA       SALE
---------- ---------- ---------- ----------
      2008          1 A                1500
      2008          1 B                1500
      2008          2 A                3000
      2008          2 B                3000
      2008          2 C                1000
      2008          3 A                   0
 
已选择6行。
 
--使用PRESENTNNV()
--PRESENTNNV(CELL,EXPR1,EXPR2)只有当cell单元引用的记录存在且不为空时才返回EXPR1,否则返回EXPR2;
SQL> select years,months,product_name,sale from t
  2  model
  3  partition by (years)
  4  dimension by (months,product_name)
  5  measures (sales sale)
  6  rules
  7  (
  8  sale[for months in (1,2,3),'A']=presentnnv(sale[cv(),'B'],sale[cv(),'B'],1)
  9  );
   
  YEARS     MONTHS PRODUCT_NA       SALE
---------- ---------- ---------- ----------
      2008          1 A                1500
      2008          1 B                1500
      2008          2 A                3000
      2008          2 B                3000
      2008          2 C                1000
      2008          3 A                   1
 
已选择6行。
-
-使用IGNORE NAV和KEEP NAV
IGNORE NAV 的返回值如下:
空值或缺失数字值时返回0;
空值或缺失字符串时返回空字符串;
空值或缺失日期值时返回01-JAN-2000;
其他数据库类型时返回空值;
KEEP NAV 对空值或缺失数字值返回空值,默认条件下是使用KEEP NAV
 
SQL> select years,months,product_name,sale from t
  2  model ignore nav
  3  partition by (years)
  4  dimension by (months,product_name)
  5  measures (sales sale)
  6  rules
  7  (
  8  sale[for months in (1,2,3),'A']=sale[cv(),'B']
  9  );
     YEARS     MONTHS PRODUCT_NA       SALE
---------- ---------- ---------- ----------
      2008          1 A                1500
      2008          1 B                1500
      2008          2 A                3000
      2008          2 B                3000
      2008          2 C                1000
      2008          3 A                   0
 
已选择6行。

点评:MODEL子句是Oracle 11g新推出的特性,这种递归方式简单易懂,比较好用。
 
参考自:oracle 11g sql开发指南及oracle 11gr2文档
 
 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26977915/viewspace-735017/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-06-26

  • 博文量
    35
  • 访问量
    91940