| ||
| ||
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 < $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 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 < 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 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 (char_col) VALUES ('Done!'); END;
5> EXIT语句 语法: EXIT; 参见上面的PL/SQL标准控制结构之循环语句说明部分。 |