Oracle的model用法简介.docx

Oracle的model用法简介2010-04-22 10:15Model语句是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典型使用场景。1. 合计行追加 2. 行列变换 3. 使用当前行的前后行 4. RegExp_Replace函数的循环执行 2. HelloWorld先看一个简单的例子。例句1select ArrValue,soeji from (select abcdefghijklmn as ArrValue, 1 as soeji from dual) model dimension by(soeji) measures(ArrValue) rules(ArrValue1 = Hello World);结果：ArrValuesoejiHello World1说明：model model语句的关键字，必须。dimension bydimension维度的意思，可以理解为数组的索引，必须。measures 指定作为数组的列rules对数组进行各种操作的描述。例句1的理解：select abcdefghijklmn as ArrValue, 1 as soeji from dual;上面子查询的结果：ArrValue soejiabcdefghijklmn1根据下面语句model dimension by (soeji) measures(ArrValue)soeji作为索引对数组ArrValue进行操作，rules(ArrValue1 = Hello World)就是说用Hello World覆盖ArrValue1里面的值。在看一个例子，例句2：select ArrValue,soeji from (select abcdefghijklmn as ArrValue, 1 as soeji from dual) model dimension by(soeji) measures(ArrValue) rules(ArrValue1 = Hello World, ArrValue2 = Hello model);执行结果：ArrValue soejiHello World1Hello model2rules的缺省行为是存在就更新，不存在则追加，因此，ArrValue1 = Hello World是更新一条，ArrValue2 = Hello Modelinsert一条。再看一个例子，例句3：select ArrValue,soeji from (select abcdefghijklmn as ArrValue, 1 as soeji from dual) model dimension by(soeji) measures(ArrValue) rules(ArrValue3 = Hello Oracle);结果是：ArrValue soejiabcdefghijklmn1Hello Oracle3model语句里面，索引可以是不连续的。再看一个例子，例句4：select ArrValue,soeji from (select abcdefghijklmn as ArrValue, 1 as soeji from dual) model return updated rows dimension by(soeji) measures(ArrValue) rules(ArrValue4 = Hello CodeZine);结果是：ArrValue soejiHello CodeZine4使用model return updated rows的话，被rules更新或者插入的行才显示，没有更新过的行不再作为SQL的结果。再来一个，有下表，希望在检索结果后面加一个合计行：addTotalIDVal13021003504300select ID,Val from addTotal model dimension by(ID) measures(Val) rules( Valnull = Val1+Val2+Val3+Val4);结果IDVal13021003504300null480不使用model的话可以使用rollup，union all等实现。rollup方式：select ID,sum(Val) as Val fr