oracle 11g model子句的用法

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26977915/viewspace-735017/