常用SQL/oracle循环语句

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 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_seats

                  INTO 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标准控制结构之循环语句说明部分。