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

核心提示： 示例：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

示例：

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;