[DECLARE] -- 声明部分 BEGIN -- 执行部分 [EXCEPTION] -- 异常处理部分 END
-- 参数 param_name datatype [{:=|default} param_value] -- 输入参数 param_name [in] datatype [{:=|default} param_value] -- 输出参数 param_name out datatype [{:=|default} param_value]
if <condition_expression1> then plsql_sentence_1; elsif <condition_expression2> then plsql_sentence_2; ... else plsql_sentence_n; end if;
注:是“elsif”,不是“elseif”,木有“e”
case <selector> when <expression_1> then plsql_sentence_1; when <expression_2> then plsql_sentence_2; ... when <expression_n> then plsql_sentence_n; [else plsql_sentence;] end case;
loop plsql_sentence; exit when end_condition_exp; end loop;
1 -- 计算前100个自然数之和 2 set serveroutput on 3 declare 4 sum_i int := 0; 5 i int := 0; 6 begin 7 loop 8 i := i + 1; 9 sum_i := sum_i + i; 10 exit when _i = 100; 11 end loop; 12 dbms_output.put_line('前100个自然数之和:' || sum_i); 13 end; 14 /
while condition_expression loop plsql_sentence; end loop;
1 -- 计算前100个自然数之和 2 set serveroutput on 3 declare 4 sum_i int := 0; 5 i int := 0; 6 begin 7 while 1<=99 loop 8 i := i + 1; 9 sum_i := sum_i + i; 10 end loop; 11 dbms_output.put_line('前100个自然数之和:' || sum_i); 12 end; 13 /
for variable_counter_name in [reverse] lower_limit..upper_limit loop plsql_sentence; end for;
1 -- 计算前100个自然数之和 2 declare 3 sum_i int := 0; 4 begin 5 for i in 1..100 loop 6 sum_i := sum_i + i; 7 end loop; 8 dbms_output.put_line('前100个自然数之和:' || sum_i); 9 end; 10 /
-- 声明游标 cursor cur_name[(input_param1[, input_param2]...)] [return ret_type] is select_sentence; -- 打开游标 open cur_name[(param_value1[, param_value2]...)]; -- 读取游标 fetch cur_name into {variable}; -- 关闭游标 close cur_name;
1 -- 2 set serveroutput on 3 declare 4 -- 声明游标 5 cursor cup_demo(var_valid in SMALLINT := '0') 6 is select id, name, type 7 from t_demo 8 where valid = var_valid; 9 -- 声明一个RECORD类型 10 type record_demo is record 11 ( 12 id t_demo.id%type, 13 name t_demo.name%type, 14 type t_demo.type%type 15 ); 16 -- 声明一个record_demo类型的变量 17 v_row record_demo; 18 begin 19 -- 打开游标 20 open cur_demo('1'); 21 -- 读取游标 22 fetch cur_demo into v_row; 23 while cur_demo%found loop 24 dbms_output.putline(v_row.name || ':' || v_row.type); 25 fetch cur_demo into v_row; 26 end loop; 27 -- 关闭游标 28 close cur_demo; 29 end; 30 /
使用FOR语句循环游标
1 -- 2 set serveroutput on 3 declare 4 -- 声明游标 5 cursor cup_demo(var_valid in SMALLINT := '0') 6 is select id, name, type 7 from t_demo 8 where valid = var_valid; 9 begin 10 -- 循环游标 11 for v_row in cur_demo loop 12 dbms_output.put_line(v_row.name || ':' || v_row.type); 13 end loop; 14 end; 15 /
注:在使用游标(包括显示、隐式)的FOR循环中,可以声明游标,但不用打开游标、读取游标、关闭游标,这些是由Oracle系统内部自动完成的。