Returning Updated Rows 返回更新的行
(page 280)
In Listing 9-7, just four rows were returned even though there are rows for other weeks. The clause
RETURN UPDATED ROWS controls this behavior and provides the ability to limit the cells returned by the
SQL statement. Without this clause, all rows are returned regardless of whether the rules updates the
cells or not.
The rule in Listing 9-7 updates only four cells and other cells are untouched, and so just
four rows are returned.
在列表9-7中,只返回了四行,尽管有其它week的行。子句RETURN UPDATED
ROWS控制该行为且为SQL语句提供限制返回单元格的能力。没有这个子句,将返回所有行,不论规则是否更新了单元格。
在列表9-7的规则只更新了四行而其它的单元格没有触及,因此只返回了四行。
What happens if you don’t specify the clause return updated rows ? Listing 9-9 shows the output
without the return updated rows clause. The output in this listing shows that both updated and non-
updated rows are returned from the SQL statement. The rule updates cells for the weeks 1, 52, and 53
only, but the output rows in Listing 9-9 shows rows with other column values such as 2, 3, 4, too.
如果你没有指定子句 return updated rows 会怎样?列表9-9展示了没有 return updated rows
子句的输出。这个列表的输出显示从SQL语句中更新和没有更新的行都返回了。规则只更新单元格weeks
1,52和53,但是列表9-9的输出显示其它的列值如2,3,4的行也输出了。
Listing 9-9. SQL without RETURN UPDATED ROWS
1 select product, country, year, week, sale
2 from sales_fact
3 where country in ('Australia') and product ='Xtend Memory'
4 model
5 partition by (product, country)
6 dimension by (year, week)
7 measures ( sale)
8 rules(
9 sale [ year in (2000,2001), week in (1,52,53) ] order by year, week
10 = sale [cv(year), cv(week)] * 1.10
11 )
12* order by product, country,year, week
PRODUCT COUNTRY YEAR WEEK SALE
------------ ---------- ----- ---- ----------
...
Xtend Memory Australia 2000 50 21.19
Xtend Memory Australia 2000 52 74.20
Xtend Memory Australia 2001 1 101.49
Xtend Memory Australia 2001 2 118.38
Xtend Memory Australia 2001 3 47.24
Xtend Memory Australia 2001 4 256.70
...
The clause RETURN UPDATED ROWS is applicable to statements using positional notation also.
In the
Listing 9-10, a rule using a positional notation is shown, inserting a row. Note that there are more
rows in the table matching with the predicate country in ('Australia') and product ='Xtend
Memory'. But, just one row is returned as only one cell is inserted by the rule in line 9. Essentially,
RETURN UPDATED ROWS clause is a limiting clause—it only fetches the rows modified by the rule.
子句RETURN UPDATED
ROWS也可以应用于使用位置标记的语句。
在列表9-10中,展示了一使用位置标记的规则,插入一行。注意表中有多行匹配谓词country in
('Australia') and product ='Xtend
Memory'。但是,只返回了一行,作为按第9行规则插入的唯一单元格。本质上,RETURN UPDATED
ROWS子句是一限定子句--它仅取按规则修改的行。
Listing 9-10. RETURN UPDATED ROWS and UPSERT
1 select product, country, year, week, sale
2 from sales_fact
3 where country in ('Australia') and product ='Xtend Memory'
4 model return updated rows
5 partition by (product, country)
6 dimension by (year, week)
7 measures ( sale)
8 rules(
9 sale [2002, 1] = 0
10 )
11 * order by product,country,year, week
/
PRODUCT COUNTRY YEAR WEEK SALE
------------ ---------- ----- ---- ----------
Xtend Memory Australia 2002 1 .00
分享到:
相关推荐
NULL 博文链接:https://caohong286.iteye.com/blog/1493368
NULL 博文链接:https://caohong286.iteye.com/blog/1490004
NULL 博文链接:https://caohong286.iteye.com/blog/1488059
NULL 博文链接:https://caohong286.iteye.com/blog/1485710
NULL 博文链接:https://caohong286.iteye.com/blog/1482168
NULL 博文链接:https://caohong286.iteye.com/blog/1504194
NULL 博文链接:https://caohong286.iteye.com/blog/1260817
Walking the Tree: From the Top Down 5-9 Ranking Rows with the LEVEL Pseudocolumn 5-10 Formatting Hierarchical Reports Using LEVEL and LPAD 5-11 Pruning Branches 5-13 Summary 5-14 Practice 5 Overview 5...
Pro Oracle SQL unlocks the power of SQL in the Oracle Database—one of the most potent SQL implementations on the market today. To master it requires a three-pronged approach: learn the language ...
Clause-view个人中心、设置常用的itemview
资源分类:Python库 所属语言:Python 资源全名:clause-1.1.2.tar.gz 资源来源:官方 安装方法:https://lanzao.blog.csdn.net/article/details/101784059
(b) the name of the table, the names of the table's attributes, the data types of the table's attributes, the formats of the table's attributes, and the maximum number of rows that the table can have...
Considered the best Oracle PL/SQL programming guide by the Oracle community, this definitive guide is precisely what you need to make the most of Oracle’s powerful procedural language. The sixth ...
运算符、表达式、Conditions、函数、Common sql ddl c、Clause
Chatopera 语义理解系统:机器学习,聊天机器人,意图识别
10g-including new features such as regular expressions and the MODEL SQL clause-as well as versions 8, 8i, and 9i <br>The authors are well-known as Oracle gurus-Greenwald is the author of Oracle ...
1000BASE-X IEEE 802.3-2008 Clause 36 - Physical Coding Sublayer (PCS)_latest.tar.gz
关于xeus-sql 主页: : 软件包许可证:BSD-3-Clause 原料许可证: 简介:基于xeus和SOCISQL的Jupyter内核文档: : 当前构建状态蔚蓝 变体状态linux_64 osx_64 win_64当前发行信息姓名资料下载版本平台类安装xeus-sql...