pl/sql存储过程及案例分析

plsql语句块：

set serveroutput on; //打开控制台输出的命令

语法：

declare

   声明部分

begin

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

end;

变量的类型

    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;

案例练习

1. 10个人围成圈，数到3退出圈，问最后退出的是谁
   //数数问题
   declare
   v_personNumber integer := 10;--人数
   v_number integer := 3;--数的数

    --int 类型的数组
    type int_array is table of integer index by BINARY_integer;
    v_data int_array;
   
    fang integer := 0;--放数据的下标
    qu  integer := 0;--取数据的下标
    numbers integer := 0;--存放数据的个数
    v_length integer := v_personNumber;--队列的大小
   
    v_count integer := 0;--计数器
   
    v_person integer := 0;--临时存放出队列的人

   begin
   -- 把人放入数组中
   for i in 1..v_personNumber loop
   v_data(fang) := i;
   fang := fang + 1;
   numbers := numbers + 1;
   end loop;

      -- 循环取数据判断
      while numbers <> 1 loop
          --超过1个人，没有退出圈
          --出队列
          v_person := v_data(mod(qu,v_length));
          qu := qu + 1;
          numbers := numbers - 1;
   
          --计数器加加
          v_count := v_count + 1;
          --判断是否是v_number倍数
          if mod(v_count,v_number) <> 0 then
             --不是，添加到队列
             v_data(mod(fang,v_length)) := v_person;
             fang := fang+1;
               numbers := numbers + 1;
           end if;
      end loop;
   
      --输出结果
      dbms_output.put_line(v_data(mod(qu,v_length)));

   end;

plsql操作数据

分析： 对emp集体涨工资，涨幅不一致， 1000以内 40% 1000-2000 30% 2000-3000 20 3000 10%

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;

案例2： for循环与游标

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;

异常处理

1. 根据异常名字
   exception
   when Too_many_rows then
   SYS.DBMS_OUTPUT.PUT_LINE('Too_many_rows');
   when others then
   注意： others只能写在最后面

2. 根据错误代号
   exception
   when others then
   case
   when sqlcode=-1476 then
   DBMS_OUTPUT.PUT_LINE('被0整除异常');
   when sqlcode = -1422 then
   DBMS_OUTPUT.PUT_LINE('返回多条记录赋值');
   else

     DBMS_OUTPUT.PUT_LINE('其他异常'); 
   end case;

   -- SYS.DBMS_OUTPUT.PUT_LINE('出现异常了'||sqlcode||'<>'||sqlerrm);
   -- rollback;

3. 自定义异常
   declare
   my_exec exception;
   my_exec2 exception;
   --把自定义异常和错误代号绑定
   pragma EXCEPTION_INIT (my_exec2, -9527);
   begin
   if 3 > 2 then
   --抛出异常 throw new 对象
   raise my_exec2;
   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('其他异常');
   end;

4.自定义异常2

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;