Oracle 流程控制

/*
流程控制
*/

---条件判断
declare
  u_userage mytable.name%type;
begin
  --通过 select ... into ... 语句为变量赋值
  select age into u_userage from mytable where user_id='user_id_7';
  dbms_output.put_line('u_userage:'||u_userage);
  dbms_output.put_line('以上是通过 select ... into ... 语句为变量赋值 打印的值。。。');
   -- 打印变量的值
  if u_userage > 27 then
    dbms_output.put_line('u_userage is > 27 :'||u_userage);
  elsif u_userage > 26 then
    dbms_output.put_line('u_userage is > 26:'||u_userage);
  else
    dbms_output.put_line('u_userage:'||u_userage);
  end if;
end;

---条件判断 方法二
declare
  u_userage mytable.name%type;
begin
 
  select age into u_userage from mytable where user_id='user_id_7';
 
 
   -- 打印变量的值
  if(u_userage > 27) then
    u_userage := 27+5;
  elsif u_userage > 26 then
     u_userage := 26+5;
  else
    u_userage := u_userage;
  end if;
  dbms_output.put_line('u_userage:'||u_userage);
end;


---使用 CASE ... WHEN ... THEN ...ELSE ... END
declare
  u_userage mytable.name%type;
  u_msg varchar2(32);
begin
 
  select  age into u_userage from mytable where user_id='user_id_7';
  u_msg :=
             case trunc(u_userage / 3)
               when 0 then 'u_userage/3 == 0'
               when 1 then 'u_userage/3 == 1'
               when 2 then 'u_userage/3 == 2'
               when 3 then 'u_userage/3 == 3'
               when 4 then 'u_userage/3 == 4'
               when 5 then 'u_userage/3 == 5'
               when 6 then 'u_userage/3 == 6'
               when 7 then 'u_userage/3 == 7'
               when 8 then 'u_userage/3 == 8'
               else u_userage
             end;
  dbms_output.put_line(u_userage||','||u_msg);
end;

----循环结构
----使用循环语句打印 1 - 100.（三种方式）
---1).  LOOP ... EXIT WHEN ... END LOOP
declare
  --初始化条件
  v_i number(3) := 1;
begin
  loop
    --循环体
    dbms_output.put_line(v_i);
    --循环条件
    exit when v_i = 100;
    --迭代条件
    v_i := v_i +1;
  end loop;
end;

---2). WHILE ... LOOP ... END LOOP
declare
  --初始化条件
  v_i number(3) := 1;
begin
  --循环条件
  while v_i <= 100 loop
    --循环体
    dbms_output.put_line(v_i);
    ---迭代条件
    v_i := v_i+1;
  end loop;
end;

---3)for循环这种是最简洁的一种----------------------------
begin
  for i in 1 .. 100 loop
    dbms_output.put_line(i);
  end loop;
end;

-----综合使用 if, while 语句, 打印 1 - 100 之间的所有素数--
---(素数: 有且仅用两个正约数的整数, 2, 3, 5, 7, 11, 13, ...).
declare
  v_flag number(1) := 1;
  v_i number(3) := 2;
  v_j number(2) := 2;
begin
  while(v_i <= 100) loop
     while(v_j <= sqrt(v_i)) loop
          if(mod(v_i,v_j) = 0) then v_flag := 0;
          end if;
          v_j := v_j + 1;
     end loop;
     if(v_flag = 1) then dbms_output.put_line(v_i);
     end if;
     v_flag := 1;
     v_j := 2;
     v_i := v_i + 1;
  end loop;
end;

---(法二)使用for循环实现1-100之间的素数的输出
declare
  --标记值，若为1，则是素数，否则不是
  v_flag number(1) := 0;
begin
  for i in 2 .. 100 loop
    v_flag := 1;
    for j in 2 .. sqrt(i) loop
      if i mod j = 0 then
        v_flag := 0;
      end if;
    end loop;
    if(v_flag = 0) then
       dbms_output.put_line(i);
    end if;
  end loop;
end;

---打印1——100的自然数，当打印到50时，跳出循环，输出“打印结束”
--(方法一)
begin
  for i in 1 .. 100 loop
    dbms_output.put_line(i);
    if(i = 50) then
         goto lable;
    end if;
  end loop;
  <<lable>>
  dbms_output.put_line('打印结束。。。');
end;

---(方法二)
begin
  for i in 1 .. 100 loop
      dbms_output.put_line(i);
      if(i = 50) then
           dbms_output.put_line('打印到50结束。。。');
           exit;
      end if;
  end loop;
end;