oracle 里面present,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文档