PL/SQL基础

1、 基本形式

declare

       <declarations section>                 --------------------------声明部分

begin

       <executable commands>                     -------------------------执行部分

exception

       <exception handing>                    -------------------------异常处理部分

end;

2、 声明部分

a)         游标声明

Cursor 游标名称 is 查询语句            -----------------------------右边声明

变量        游标名称%ROWTYPE(TYPE)      -----------------------用变量引用游标的值

3、 执行部分

a)         条件逻辑

if <some condition>

       then <some command>

elsif <some condition>

       then <some command>

else <some command>

end if;

b)        循环

                         i.              简单循化

loop

       <some command>

       exit when <some condition>

end loop;

                       ii.              游标循化

open **cursor

       exit when **cursor%FOUND(NOTFOUND ISOPEN ROWCOUNT)

                      iii.              For循环

for var in 1 … n  loop

       <some command>

end loop;

                     iv.              游标for 循环

for var in **cursor

       loop

              <some command>

       end loop;

end;

不需要提供open 和fetch 命令

                       v.              While循环

While <some condition>

       Loop

              <some command>

       End loop;

c)        Case语句（oracle9i）

Case

       When <some condition>

              Then

                     <some command>

When <some condition>

              Then

                     <some command>

Else

              End case

4、 异常处理部分