oracle 11g model子句的用法

zangqianglei發表於2015-05-29
Oracle
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/25954236/viewspace-1676605/，如需轉載，請註明出處，否則將追究法律責任。

相關文章