oracle 块结构

1.块基本语法
首先输出命令: set serveroutput on;为了可以让输出从窗口中输出来
基本语法：

declare 
   v_width integer;
   v_height integer := 2;
   v_area integer := 6;
begin
   --set the width equal to the area divided by the height;
   v_width := v_area / v_height;
   DBMS_OUTPUT.put_line('V_width = '  || v_width );
exception
   when ZERO_DIVIDE THEN
   DBMS_OUTPUT.put_line('division by zero');
end;

条件逻辑:

   declare 
      tmp integer := 52;
   begin
      if tmp  > 10 then
         DBMS_OUTPUT.put_line(' TMP 是大于  10 的数');
      else 
         DBMS_OUTPUT.put_line('TMP 并不大于 10');
      end if;
   end;

简单循环

declare 
   tmp integer;
begin
   tmp := 0;
   loop 
      tmp := tmp + 1;
      DBMS_OUTPUT.put_line('TMP ==' || tmp);
      exit when tmp = 5;
   end loop;
end;

while循环

declare
   tmp integer := 0;
begin
   while tmp < 6 loop
      tmp := tmp + 1;
      DBMS_OUTPUT.put_line('TMP =='|| tmp);
   END LOOP;
END;

for循环

declare
   tmp integer := 0;
begin
   for tmp in 1..5 loop
      DBMS_OUTPUT.put_line('tmp ='||tmp);
   end loop;
END;

使用游标来获取查询数据

declare
    v_name test1.name %type;
    v_age test1.age %type;
    cursor  test1_cursor IS  select NAME,age from test1 t;
begin
    open test1_cursor;
    loop
        -- fetch the rows from the cursor
        fetch test1_cursor  into v_name,v_age;
        --exit the loop when there are no more rows,as indicated by the boolean variable test1_cursor%notfound( = true when there are no more rows)
        exit when test1_cursor%notfound;

        DBMS_OUTPUT.put_line('NAME = ' || v_name || ' age = ' || v_age);
    end loop;
    close test1_cursor;
end;

游标与for循环 ：可以显示的开 关游标

declare
    v_name test1.name %type;
    v_age test1.age %type;
    cursor  test1_cursor IS  select NAME,age from test1 t;
begin
   for t in test1_cursor loop
      DBMS_OUTPUT.put_line('NAME =' || T.NAME || 'AGE = '|| T.AGE);
   end loop;
end;