从SQL SERVER 向ORACLE 8迁移的技术实现方案(2)

核心提示： 2、使用T-SQL标准控制结构：1> 定义语句块语法：BEGINStatementsEND2> IF ... ELSE语句语法：IF boolean_expression{ statement | statement_block }ELSE{ statement | state

2、使用T-SQL标准控制结构：

1> 定义语句块

语法：

BEGIN
Statements
END

2> IF ... ELSE语句

语法：

IF boolean_expression
{ statement | statement_block }
ELSE
{ statement | statement_block }

示例：

if (select avg(price) from titles where type = ‘business’) > $19.95
print ‘The average price is greater then $19.95’
else
print ‘The average price is less then $19.95’

3> IF EXISTS语句

语法：

IF [not] EXISTS (select_statement)
{ statement | statement_block }
[ELSE
{ statement | statement_block }]

示例：

declare
@lname varchar(40),
@msg varchar(255)
select @lname = ‘Smith’
if exists(select * from titles where au_lname = @lname)
begin
select @msg = ‘There are authors named’ + @lname
print @msg
end
else
begin
select @msg = ‘There are no authors named’ + @lname
print @msg
end

4> 循环语句：

WHILE

语法：

WHILE boolean_condition
[{ statement | statement_block }]
[BREAK]
[condition]

示例：

declare
@avg_price money,
@max_price money,
@count_rows int,
@times_thru_the_loop int
select @avg_price = avg(price),
@max_price = max(price),
@count_rows = count(*),
@times_thru_the_loop = 0
from titles
while @avg_price < $25 and (@count_rows < 10 or @max_price < $50)
begin
select @avg_price = avg(price) * 1.05,
@max_price = max(price) * 1.05,
@time_thru_the_loop = @time_thru_the_loop + 1
end
if @time_thru_the_loop = 0
select @time_thru_the_loop = 1
update titles
set price = price * power(1.05, @time_thru_the_loop)