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文档