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
?
?
?
?
?
已有 0 人發表留言，猛擊->>這里<<-參與討論
ITeye推薦