Model语句是Oracle 10g的新功能之一。 本文通过一些简单的例子帮助理解Model语句的用法,复杂使用场景请参考其他文章。
环境:当然需要Oracle 10g以上,本人是在11g上测试的。
1. 什么是model语句
model语句是Oracle10g的新功能,可以在select语句里面像其他编程语言操作数组一样,对SQL的结果集进行处理。执行顺序是位于Having之后。
select的解析执行顺序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语句
model的好处
Oracle 9i为止,需要使用各种计算分析函数,union all等,以及借助其他开发语言(C#及Java等)进行复杂计算统计合并等。使用Model之后,这些都可以在SQL里面进行了。
model典型使用场景。
合计行追加
行列变换
使用当前行的前后行
RegExp_Replace函数的循环执行
2. HelloWorld
先看一个简单的例子。
例句1selectArrValue,soejifrom(select'abcdefghijklmn'asArrValue,1assoejifromdual)model
dimensionby(soeji)measures(ArrValue)rules(ArrValue[1]='Hello World');
结果:
ArrValue
soeji
Hello World
1
说明:
model model语句的关键字,必须。
dimension by dimension维度的意思,可以理解为数组的索引,必须。
measures 指定作为数组的列
rules 对数组进行各种操作的描述。
例句1的理解:
select'abcdefghijklmn'asArrValue,1assoejifromdual;
上面子查询的结果:
ArrValue soeji
abcdefghijklmn
1
根据下面语句
model
dimensionby(soeji)measures(ArrValue)
soeji作为索引对数组ArrValue进行操作,rules(ArrValue[1] = 'Hello World')就是说用Hello World覆盖ArrValue[1]里面的值。
在看一个例子,例句2:
selectArrValue,soejifrom(select'abcdefghijklmn'asArrValue,1assoejifromdual)model
dimensionby(soeji)measures(ArrValue)rules(ArrValue[1]='Hello World',ArrValue[2]='Hello model');
执行结果:
ArrValue soeji
Hello World
1
Hello model
2
rules的缺省行为是存在就更新,不存在则追加,因此,ArrValue[1] = 'Hello World'是更新一条,ArrValue[2] = 'Hello Model'insert一条。
再看一个例子,例句3:
selectArrValue,soejifrom(select'abcdefghijklmn'asArrValue,1assoejifromdual)model
dimensionby(soeji)measures(ArrValue)rules(ArrValue[3]='Hello Oracle');
结果是:
ArrValue soeji
abcdefghijklmn
1
Hello Oracle
3
model语句里面,索引可以是不连续的。
再看一个例子,例句4:
selectArrValue,soejifrom(select'abcdefghijklmn'asArrValue,1assoejifromdual)modelreturnupdated rows
dimensionby(soeji)measures(ArrValue)rules(ArrValue[4]='Hello CodeZine');
结果是:
ArrValue soeji
Hello CodeZine
4
使用model return updated rows的话,被rules更新或者插入的行才显示,没有更新过的行不再作为SQL的结果。
再来一个,有下表,希望在检索结果后面加一个合计行:
addTotal
ID
Val
1
30
2
100
3
50
4
300
selectID,ValfromaddTotal
model
dimensionby(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等实现。
rollup方式:selectID,sum(Val)asValfromaddTotalgroupbyrollup(ID);
union all方式:selectID,ValfromaddTotalunionallselectnull,Sum(Val)fromaddTotal;