pl/sql存储过程及实用案例（建议收藏）

   声明部分

    //执行部分
        异常，事物，语句块等

    oracle变量类型，oracle数据类型：integer varchar2等
    自定义数据类型
        1. 定义和列的类型保持一致
            v_sal emp.sal%type;-- 和emp表sal列的类型保持一致
        2. 定义和表达类型保持一致
            v_emp emp%rowtype; -- 和emp表的结构一致
        3. 定义自己的封装类（对象）
              --声明的是类型
                type type_emp_name_sal is record(v_empname EMP.ENAME%type,v_empsal EMP.SAL%type);
                --变量名是v_name_sal 类型是type_emp_name_sal
                v_name_sal type_emp_name_sal;
        4. 数组
             --声明数组类型
            type int_array is table of integer index by BINARY_integer;
            --int类型数组的变量
            v_numbers int_array;

1. if语句

      if v_id = 1 then
         dbms_output.put_line(v_id);
      elsif v_id = 2 then
         dbms_output.put_line('elsif'); 
      else 
         dbms_output.put_line(v_id);
      end if;  

2. switch语句  case 语句

      case 
      when v_id = 1 then
        dbms_output.put_line(v_id);
      when  v_id = 2 then
        dbms_output.put_line('elsif'); 
      else 
        dbms_output.put_line(v_id);

      end case;

3. 循环语句

    1. for循环
         -- for循环
          for v_i in reverse 1..10 loop
            SYS.DBMS_OUTPUT.PUT_LINE(v_i);
          end loop;

    2. while循环
         --while循环
          while v_id < 10 loop
             SYS.DBMS_OUTPUT.PUT_LINE(v_id);
             --条件的改变
             v_id := v_id + 1;
          end loop;
    3. loop循环
             loop
             SYS.DBMS_OUTPUT.PUT_LINE(v_id);
             --条件的改变
             v_id := v_id + 1;
             exit when v_id = 10;
          end loop;
    4. 通过goto语句完成循环
        <<a>>
         SYS.DBMS_OUTPUT.PUT_LINE(v_id);
          v_id := v_id + 1;

          if v_id < 10 then
            goto a;
          end if;

  *
 ***
*****
 ***
  *

declare
  kong integer := 0;
  xing integer :=0;
begin
  for i in 1..5 loop

            --每行由空格和星号组成

            if i < 4 then
                -- 1. 上半
                kong := 3 - i;
                xing := 2 * i - 1;
            else
                -- 2.下半
                kong := i - 3;
                xing := -2 * i + 11;
            end if;

            --输出空格
            for k in 1..kong loop
                dbms_output.put(' ');
            end loop;
            --输出星号
                for k in 1..xing loop
                dbms_output.put('*');
            end loop;
            --换行
            dbms_output.new_line();

        end loop;
end;

declare
  -- 数据全部取出
  --数组存放数据（相当于jdbc中的结果集封装）
  --员工编号和员工的薪水
  type type_empno_sal is record (v_empno EMP.empno%type,v_sal EMP.SAL%type);

  --定义员工数组
  type type_emps_list is table of type_empno_sal index by binary_integer;

  --定义保存员工信息的容器
  v_emps_no_sals type_emps_list;

  v_rows integer;--存放多少条数据

begin
  select count(1) into v_rows from emp;
  --去员工表每行信息，存储到容器中

  for r in 1..v_rows loop
    select empno,sal into v_emps_no_sals(r - 1) from (select rownum num,emp.* from emp) e where  e.num = r;
  end loop;

  for r in 1..v_rows loop
      case 
        when v_emps_no_sals(r-1).v_sal <= 1000 then
           update emp set sal = sal * 1.4 where empno = v_emps_no_sals(r-1).v_empno;
         when v_emps_no_sals(r-1).v_sal > 1000 and  v_emps_no_sals(r-1).v_sal <= 2000 then
           update emp set sal = sal * 1.3 where empno = v_emps_no_sals(r-1).v_empno;
         when v_emps_no_sals(r-1).v_sal > 2000 and  v_emps_no_sals(r-1).v_sal <= 3000 then
           update emp set sal = sal * 1.2 where empno = v_emps_no_sals(r-1).v_empno;
         else
           update emp set sal = sal * 1.1 where empno = v_emps_no_sals(r-1).v_empno;
      end case;
  end loop;

  commit;

end;

1.声明游标
2.打开游标
3.循环提前游标
4.关闭游标（释放游标占用的空间）

declare
  --声明游标
  cursor v_emp_cur is select empno,sal from emp;

  --信息封装
  type type_empno_sal is record (v_empno EMP.empno%type,v_sal EMP.SAL%type);

  v_temp_empno_sal type_empno_sal;

begin
  -- 打卡游标

  open v_emp_cur;

  -- 循环提取游标
  loop
     --去当前游标所在行的数据
     fetch v_emp_cur into v_temp_empno_sal;

      --判断提取成功或失败

     if v_emp_cur%found then 
       --游标有数据
      -- SYS.DBMS_OUTPUT.PUT_LINE(v_temp_empno_sal.v_empno||'<>'||v_temp_empno_sal.v_sal);
      case 
        when v_temp_empno_sal.v_sal > 3000 then
           update emp set sal = sal * 0.6 where empno = v_temp_empno_sal.v_empno;
         when v_temp_empno_sal.v_sal > 2000 and v_temp_empno_sal.v_sal<= 3000 then
           update emp set sal = sal * 0.7 where empno = v_temp_empno_sal.v_empno;
         when v_temp_empno_sal.v_sal > 1500 and  v_temp_empno_sal.v_sal <= 2000 then
           update emp set sal = sal * 0.8 where empno = v_temp_empno_sal.v_empno;
         else
           update emp set sal = sal * 0.99 where empno = v_temp_empno_sal.v_empno;
      end case;
     else
        --游标提取完毕
        exit;
     end if;

     commit;

  end loop;

  --关闭游标
  close v_emp_cur;
end;

declare
   --声明游标
   cursor cur_emp_no_sal(v_deptno emp.deptno%type) is select empno,sal from emp where deptno= v_deptno;
begin
   --for循环提取游标
   for v_empno_sal in cur_emp_no_sal(10) loop
      SYS.DBMS_OUTPUT.PUT_LINE(v_empno_sal.empno||'<>'||v_empno_sal.sal);
   end loop;
end;

declare
   my_exec exception;
   my_exec2 exception;

   pragma EXCEPTION_INIT (my_exec2, -9527);
begin
   if 3 > 2 then
      --抛出异常 throw new 对象 -20000 到 -20999
      RAISE_APPLICATION_ERROR(-20000, '我的异常');
   end if;

   exception
       when my_exec then
          SYS.DBMS_OUTPUT.PUT_LINE('自定义异常'||sqlcode);
        when my_exec2 then
          SYS.DBMS_OUTPUT.PUT_LINE('自定义异常2<>'||sqlcode);
       when others then
          SYS.DBMS_OUTPUT.PUT_LINE('其他异常'||sqlcode);
end;