<七> 常用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)
as
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][,...] screen.width/2)this.style.width=screen.width/2;" border=0>
[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;
-- Exit condition - when the loop counter > 50 we will
-- break out of the loop.
IF v_Counter > 50 THEN
EXIT;
END IF;
END LOOP;
END;
示例2:
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;
-- Exit condition - when the loop counter > 50 we will
-- break out of the loop.
EXIT WHEN v_Counter > 50;
END LOOP;
END;
(2)WHILE循环语句:
语法:
WHILE condition LOOP
{ statement | statement_block } ;
END LOOP ;
示例1:
v_Counter BINARY_INTEGER := 1;
BEGIN
-- Test the loop counter before each loop iteration to
-- insure that it is still less than 50.
WHILE v_Counter <= 50 LOOP
INSERT INTO temp_table
VALUES (v_Counter, 'Loop index');
v_Counter := v_Counter + 1;
END LOOP;
END;
示例2:
v_Counter BINARY_INTEGER;
BEGIN
-- This condition will evaluate to NULL, since v_Counter
-- is initialized to NULL by default.
WHILE v_Counter <= 50 LOOP
INSERT INTO temp_table
VALUES (v_Counter, 'Loop index');
v_Counter := v_Counter + 1;
END LOOP;
END;
(3)数字式FOR循环语句:
语法:
FOR loop_counter IN [REVERSE] low_bound..high_bound LOOP
{ statement | statement_block } ;
END LOOP ;
这里,loop_counter是隐式声明的索引变量。
示例1:
FOR循环的循环索引被隐式声明为BINARY_INTEGER。在循环前面没有
必要声明它,如果对它进行了声明,那么循环索引将屏蔽外层的声明,
如下所示
v_Counter NUMBER := 7;
BEGIN
-- Inserts the value 7 into temp_table.
INSERT INTO temp_table (num_col)
VALUES (v_Counter);
-- This loop redeclares v_Counter as a BINARY_INTEGER, which
-- hides the NUMBER declaration of v_Counter.
FOR v_Counter IN 20..30 LOOP
-- Inside the loop, v_Counter ranges from 20 to 30.
INSERT INTO temp_table (num_col)
VALUES (v_Counter);
END LOOP;
-- Inserts another 7 into temp_table.
INSERT INTO temp_table (num_col)
VALUES (v_Counter);
END;
示例2:
如果在FOR循环中有REVERSE关键字,那么循环索引将从最大值向最
小值进行循环。请注意语法是相同的——仍然首先书写的是最小值,
如下所示
BEGIN
FOR v_Counter IN REVERSE 10..50 LOOP
-- v_Counter will start with 50, and will be decremented
-- by 1 each time through the loop.
NULL;
END LOOP;
END;
示例3:
FOR循环中的最大值和最小值没有必要必须是数字型文字,它们可以
是能够被转换为数字值的任何表达式,如下所示
v_LowValue NUMBER := 10;
v_HighValue NUMBER := 40;
BEGIN
FOR v_Counter IN REVERSE v_LowValue..v_HighValue LOOP
INSER INTO temp_table
VALUES (v_Counter, ‘Dynamically sqecified loop range’);
END LOOP;
END;
4> GOTO语句
语法:
GOTO label;
...
<<label>>
...
示例:
v_Counter BINARY_INTEGER := 1;
BEGIN
LOOP
INSERT INTO temp_table
VALUES (v_Counter, 'Loop count');
v_Counter := v_Counter + 1;
IF v_Counter > 50 THEN
GOTO l_EndOfLoop;
END IF;
END LOOP;
<<l_EndOfLoop>>
INSERT INTO temp_table (