全部博文(118)
分类: Oracle
2010-03-04 15:26:13
Model语句是Oracle 10g的新功能之一。 本文通过一些简单的例子帮助理解Model语句的用法,复杂使用场景请参考其他文章。
环境:当然需要Oracle 10g以上,本人是在11g上测试的。
model语句是Oracle10g的新功能,可以在select语句里面像其他编程语言操作数组一样,对SQL的结果集进行处理。执行顺序是位于Having之后。
1. from语句 2. where语句 (结合条件) 3. start with语句 4. connect by语句 5. where语句 6. group by语句 7. having语句 8. model语句 9. select语句 10. union、minus、intersect等集合演算演算 11. order by语句
Oracle 9i为止,需要使用各种计算分析函数,union all等,以及借助其他
开发语言(C#及Java等)进行复杂计算统计合并等。使用Model之后,这些都可以在SQL里面进行了。
model典型使用场景
。
RegExp_Replace函数的循环执行
先看一个简单的例子。
select ArrValue,soeji from (select 'abcdefghijklmn' as ArrValue, 1 as soeji from dual) model dimension by(soeji) measures(ArrValue) rules(ArrValue[1] = 'Hello World');
ArrValue | soeji |
Hello World | 1 |
说明:
model语句的关键字,必须
。dimension维度的意思,可以理解为数组的索引,必须
。rules 对数组进行各种操作的描述。
例句1的理解:
select 'abcdefghijklmn' as ArrValue,
1 as soeji from dual;
abcdefghijklmn | 1 |
根据下面语句
model
dimension by (soeji)
measures(ArrValue)
soeji作为索引对数组ArrValue进行操作,rules(ArrValue[1] = 'Hello World')就是说
用Hello World覆盖ArrValue[1]里面的值。
在看一个例子,例句2:
select ArrValue,soeji
from (select 'abcdefghijklmn' as ArrValue,
1 as soeji from dual)
model
dimension by(soeji)
measures(ArrValue)
rules(ArrValue[1] = 'Hello World',
ArrValue[2] = 'Hello model');
Hello World | 1 |
Hello model | 2 |
rules的缺省行为是存在就更新,不存在则追加
,因此,ArrValue[1] = 'Hello World'是更新一条,
ArrValue[2] = 'Hello Model'
insert一条。
再看一个例子,例句3:
select ArrValue,soeji from (select 'abcdefghijklmn' as ArrValue, 1 as soeji from dual) model dimension by(soeji) measures(ArrValue) rules(ArrValue[3] = 'Hello Oracle');
abcdefghijklmn | 1 |
Hello Oracle | 3 |
model语句里面,索引可以是不连续的。
再看一个例子,例句4:
select ArrValue,soeji from (select 'abcdefghijklmn' as ArrValue, 1 as soeji from dual) model return updated rows dimension by(soeji) measures(ArrValue) rules(ArrValue[4] = 'Hello CodeZine');
Hello CodeZine | 4 |
使用model return updated rows的话,被
rules
更新或者插入的行才显示,没有更新过的行不再作为SQL的结果。
ID | Val |
1 | 30 |
2 | 100 |
3 | 50 |
4 | 300 |
select ID,Val from addTotal
model
dimension by(ID)
measures(Val)
rules(
Val[null] = Val[1]+Val[2]+Val[3]+Val[4]);
ID | Val |
1 | 30 |
2 | 100 |
3 | 50 |
4 | 300 |
null | 480 |
不使用model的话可以使用
rollup
,union all等实现
。
select ID,sum(Val) as Val from addTotal group by rollup(ID);
select ID,Val from addTotal union all select null,Sum(Val) from addTotal;