常用SQL/oracle循环语句
Jun 07, 2016 pm 03:44 PM2、使用T-SQL标准控制结构: 1 定义语句块 2 IF ... ELSE语句 3 IF EXISTS语句 语法: declare select @lname = ‘Smith’ if exists(select * from titles where au_lname = @lname) begin select @msg = ‘There are authors named’ @lname print @msg en
2、使用T-SQL标准控制结构:
1> 定义语句块
2> IF ... ELSE语句3> IF EXISTS语句
语法:
declare select @lname = ‘Smith’if exists(select * from titles where au_lname = @lname)
begin
select @msg = ‘There are authors named’ + @lname
print @msg
end
4> 循环语句:
示例:
while @avg_price
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
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
SELECT number_seatsINTO v_NumberSeats
FROM rooms
WHERE room_id = 99999;
IF v_NumberSeats
v_Comment := 'Fairly small';
ELSIF v_NumberSeats
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
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
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
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;
...
>
...
示例:
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;
>
INSERT INTO temp_table (char_col)
VALUES ('Done!');
END;
5> EXIT语句
语法:
EXIT;
参见上面的PL/SQL标准控制结构之循环语句说明部分。

Artikel Panas

Alat panas Tag

Artikel Panas

Tag artikel panas

Notepad++7.3.1
Editor kod yang mudah digunakan dan percuma

SublimeText3 versi Cina
Versi Cina, sangat mudah digunakan

Hantar Studio 13.0.1
Persekitaran pembangunan bersepadu PHP yang berkuasa

Dreamweaver CS6
Alat pembangunan web visual

SublimeText3 versi Mac
Perisian penyuntingan kod peringkat Tuhan (SublimeText3)

Topik panas

Berfungsi untuk mengira bilangan hari antara dua tarikh dalam oracle

Berapa lama log pangkalan data Oracle akan disimpan?

Urutan langkah permulaan pangkalan data oracle ialah

Keperluan konfigurasi perkakasan pelayan pangkalan data Oracle

Bagaimana untuk melihat bilangan kemunculan watak tertentu dalam Oracle

Berapa banyak memori yang diperlukan oleh oracle?

Bagaimana untuk menggantikan rentetan dalam oracle
