1. 什么是model语句
Model语句是Oracle10g的新功能,可以在select语句里面像其他编程语言操作数组一样,对SQL的结果集进行处理。
(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 ()
说明:
model model语句的关键字,必须
。
dimension by dimension维度的意思,可以理解为数组的索引,必须
。
measures 指定作为数组的列
rules 对数组进行各种操作的描述。
2、实例
select ArrValue, soeji
from (select 'qqqqqqqqqqqq' as ArrValue, 1 as soeji from dual)
model dimension by(soeji)
measures(ArrValue)
rules(ArrValue[1] = 'Hello World',
ArrValue[2] = 'Hello Model');