从SQL Server向Oracle迁移的技术实现方案（七）常用SQL语法与函数

<七> 常用SQL语法与函数

<1>、SQL SERVER端常用语法说明

1、使用局部变量：

1> 变量定义：

DECLARE @variable_name datatype [,…]

例：

declare

@name varchar(30),

@type int

2> 给变量赋值：

方法一：

例：

declare @int_var int

select @int_var = 12

方法二：

例：

declare

@single_auth varchar(40),

@curdate datetime

select @single_auth = au_lname,

@curdate = getdate()

from authors

where au_id = ‘123-45-6789’

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)

4> GOTO语句

语法：

GOTO label

label:

示例：

begin transaction

insert tiny(c1) values(1)

if @@error != 0 goto error_handler

commit transaction

return

error_handler:

rollback transaction

return

5> RETURN语句

语法：

RETURN

（1）用于无条件退出一个批处理、存储过程或触发器。

示例：

if not exists(select 1 from inventory

where item_num = @item_num)

begin

raiseerror 51345 ‘Not Found’

return

end

print ‘No error found’

return

（2）用于存储过程中返回状态值。

示例：

create procedure titles_for_a_pub

(@pub_name varchar(40) = null)

if @pub_name is null

return 15

if not exists(select 1 from publishers

where pub_name = @pub_name)

return –101

select t.tile from publishers p, titles t

where p.pub_id = t.pub_id

and pub_name = @pub_name

return 0

3、T-SQL中的游标提取循环语句：

（1）FETCH [NEXT FROM] cursor_name INTO @variable_1,

@variable_n

（2）WHILE @@FETCH_STATUS = 0

BEGIN

Other_statements

FETCH [NEXT FROM] cursor_name INTO @variable_1,

@variable_n

END

（3）CLOSE cursor_name

4、T-SQL中的事务处理语句：

1> 开始一个事务：

BEGIN TRAN[SACTION [transaction_name]]

2> 提交一个事务：

COMMIT TRAN[SACTION [transaction_name]]

3> 回滚一个事务：

ROLLBACK TRAN[SACTION [transaction_name]]

4> 使用事务保存点：

BEGIN TRAN[SACTION [transaction_name]]

SAVE TRAN[SACTION] savepoint_name

ROLLBACK TRAN[SACTION] savepoint_name

COMMIT TRAN[SACTION [transaction_name]]

5、T-SQL中可用于错误判断或其它处理的全局变量：

1> @@rowcount：前一条命令处理的行数

2> @@error：前一条SQL语句报告的错误号

3> @@trancount：事务嵌套的级别

4> @@transtate：事务的当前状态

5> @@tranchained：当前事务的模式（链接的（chained）或非链接的）

6> @@servername：本地SQL SERVER的名称

7> @@version ： SQL SERVER和O/S的版本级别

8> @@spid：当前进程的id

9> @@identity：上次insert操作中使用的identity值

10> @@nestlevel：存储过程/触发器中的嵌套层

11> @@fetch_status：游标中上条fetch语句的状态

6、使用标准内置错误消息发送函数：

函数说明：

RAISERROR ({msg_id | msg_str}, severity, state

[, argument1 [，argument2][，

] )

[WITH LOG]

其中，msg_id表示错误号，用户定义错误消息的错误号在50001到2147483647之

间，特定的消息会引起错误50000。msg_str是错误消息正文，最多可有255个字

符。Severity描述了与这个消息关联的用户定义的严重性级别，取值包括0和10

至25之间的任何整数。State描述了错误的“调用状态”，它是1到127之间的整

数值。Argument定义用于代替在msg_str中定义的变量或对应与msg_id的消息的

参数。WITH LOG表示要在服务器错误日志和事件日志中记录错误。

例1：

RAISEERROR( ‘Invalid customer id in order.’, 16, 1)

则返回：

Msg 50000, Level 16, State 1

Invalid customer id in order.

例2：

sp_addmessage 52000, 16, ‘Invalid customer id %s in order’

RAISEERROR( 52000, 16, 1, ‘ID52436’)

则返回：

Msg 52000, Level 16, State 1

Invalid customer id ID52436 in order.

<2>、ORACLE端常用语法说明

1、使用局部变量：

1> 定义变量：

VARIABLE_NAME DATA TYPE [ := INITIAL VALUE ] ;

例：定义变量

v_Num number;

v_string varchar2(50);

例：定义变量并赋初值

v_Num number := 1 ;

v_string varchar2(50) := ‘Hello world!’ ;

2> 给变量赋值：

方法一：

例：

v_Num := 1;

v_string := ‘Hello world!’;

方法二：

例：

SELECT first_name INTO v_String

FROM students

WHERE id = v_Num ;

2、使用PL/SQL标准控制结构：

1> 定义语句块

语法：

BEGIN

Statements ；

END ；

2> IF

THEN

ELSE语句

语法：

IF boolean_expression THEN

{ statement | statement_block } ；

[ELSIF boolean_expression THEN /*注意此处的写法—— ELSIF */

{ statement | statement_block } ；]

[ELSE

{ statement | statement_block } ；]

END IF ；

示例：

v_NumberSeats rooms.number_seats%TYPE;

v_Comment VARCHAR2(35);

BEGIN

/* Retrieve the number of seats in the room identified by ID 99999.

Store the result in v_NumberSeats. */

SELECT number_seats

INTO v_NumberSeats

FROM rooms

WHERE room_id = 99999;

IF v_NumberSeats < 50 THEN

v_Comment := 'Fairly small';

ELSIF v_NumberSeats < 100 THEN

v_Comment := 'A little bigger';

ELSE

v_Comment := 'Lots of room';

END IF;

END;

3> 循环语句：

（1）简单循环语句：

语法：

LOOP

{ statement | statement_block } ；

[EXIT [WHEN condition] ；]

END LOOP ；

其中，语句EXIT [WHEN condition];等价于

IF condition THEN

EXIT ；

END IF ；

示例1：

v_Counter BINARY_INTEGER := 1;

BEGIN

LOOP

-- Insert a row into temp_table with the current value of the

-- loop counter.

INSERT INTO temp_table

VALUES (v_Counter, 'Loop index');

v_Counter := v_Counter + 1;