Day60-Oracle04 - PLSQL编程、游标cursor、例外exception、存储过程（procedure）、存储函数（function）、java调用存储过程、触发器（trigger）

PLSQL编程

 本质上是将业务逻辑封装在数据库中 ，其实这样是不好的，加大了数据库的负担，一般我们的业务逻辑都是封装到了三层结构里面的。

语法：

         declare
             -- 声明部分
             变量名  变量类型  := 初始值
             变量名     表名.字段%type   引用型的变量
             变量名     表名 %rowtype: 记录型变量
         begin
             -- 业务逻辑
         exception
             -- 处理异常
         end;

1）if语句：
语法：

    if 条件1 then
      //TODO
    elsif 条件2 then
      //TODO
    else

    end if;

例子：

declare
   vage number:=&年龄;    --这种写法可以弹出对话框，动态输出年龄
begin
  if vage<=16 then
    dbms_output.put_line('儿童');
  elsif vage>16 and vage<25 then
    dbms_output.put_line('青年');
  else
    dbms_output.put_line('老年人');
  end if;
end;

2） 循环输出
语法：

   while 满足条件 loop

   end loop;

   for 变量名 in [reverse] 起始值..结束值 loop

   end loop;


--用for循环，可以不用定义变量，不用写变量的自增

   loop
     exit when 退出的条件
   end loop;

– 输出1-10
方式一：while

declare
  vindex number:=1;
begin
  while vindex<=10 loop
    dbms_output.put_line('vindex:'||vindex);
    vindex:=vindex+1;
  end loop;
end;

方式二：for

declare
begin
  for vindex in 1..10 loop
    dbms_output.put_line('index:'||vindex);
  end loop;
end;

方式三：exit when

declare
   vindex number:=1;
begin
   loop
     exit when vindex>10;
       dbms_output.put_line('vindex:'||vindex);
       vindex:=vindex+1;
   end loop;
end;

3）案例： 输出一个菱形

declare
  vindex number:=&参数;
begin
  for y in -vindex..vindex loop
    for x in -vindex..vindex loop
      if abs(x)+abs(y)<=vindex then
        dbms_output.put('*');
      else
        dbms_output.put(' ');
      end if;
    end loop;
    --dbms_output.put_line('');
      dbms_output.new_line();
  end loop;
end;

---

游标

 理解成为光标、指针；实际上是用来操作结果集的，相当于jdbc中的ResultSet

1）分类：
普通游标：
语法：

cursor  游标名  is   查询结果集[ where 条件 = 变量名 ]  -- 不带参数
cursor  游标名（参数名 参数类型）  is   查询结果集[ where 条件 = 变量名 ] 
                                    -- 不带参数
open 游标名；  -- 不带参数
open 游标名（参数值）；  -- 带参数

系统游标：也叫系统引用游标
语法：

变量名  sys_refcursor;
open 变量名 for select 语句

2）主要的使用方法：

fetch  游标名  into  变量名
游标名 % fount ：代表找到数据
游标名 % notfount：代表没有找到数据

3）开发步骤：

    *      声明游标
    *      打开游标 open游标名
    *      在游标中提取数据
    *      关闭游标  colse 游标名

案例一：输出所有员工的姓名和工资 – 利用游标和循环

declare
  cursor vemps is select * from emp;    -- 声明普通游标
  vrow emp%rowtype;  
begin
  open vemps;   -- 打开游标
  loop
    fetch vemps into vrow;  -- 获取游标中的数据
    exit when vemps%notfound;
      dbms_output.put_line('姓名：'||vrow.ename||'  工资：'||vrow.sal);
  end loop;
  close vemps;  -- 关闭游标
end;

案例二：输出指定部门员工的姓名和工资 – 带参数的游标

declare
  cursor vemps(vdeptno number) is select * from emp where deptno = vdeptno;
  vrow emp%rowtype;
begin
  open vemps(10);
  loop
    fetch vemps into vrow;
    exit when vemps%notfound;
    dbms_output.put_line('部门编号：'||vrow.deptno||'  姓名：'||vrow.ename||'   工资：'||vrow.sal);
  end loop;
  close vemps;
end;

案例三：使用for循环遍历出所有员工的姓名和工资
使用for的时候不用打开和关闭游标，不用复制给记录性变量vrow；

declare
  cursor vemps is select * from emp;
begin
  for vrow in vemps loop
    dbms_output.put_line('员工姓名：'||vrow.ename||'   工资:'||vrow.sal);
  end loop;
end;

案例四： 按照员工工作给所有员工涨工资,总裁涨1000,经理涨800,其他人涨400

写法一： 用exit when

declare
  cursor vemps is select * from emp;
  vrow emp%rowtype;
begin
  open vemps;
  loop
    fetch vemps into vrow;
    exit when vemps%notfound;
      if vrow.job='PRESIDENT' then
        update emp set sal = vrow.sal + 1000 where empno = vrow.empno;
      elsif vrow.job='MANAGER' then
        update emp set sal = vrow.sal + 800 where empno = vrow.empno;
      else
        update emp set sal = vrow.sal + 400 where empno = vrow.empno;
      end if;
  end loop;
  commit;
  close vemps;
end;

写法二：用for语句 ，使用for的时候不用打开游标，不用关闭游标，不用声明vrow记录型变量

declare
  cursor vemps is select * from emp;
begin
  for vrow in vemps loop
      if vrow.job='PRESIDENT' then
        update emp set sal = vrow.sal + 1000 where empno = vrow.empno;
      elsif vrow.job='MANAGER' then
        update emp set sal = vrow.sal + 800 where empno = vrow.empno;
      else
        update emp set sal = vrow.sal + 400 where empno = vrow.empno;
      end if;
  end loop;
  commit;
end;

案例：输出10号部门的员工信息
方式一：普通游标方式

declare
  cursor vemps10(vdeptno number) is select * from emp where deptno = vdeptno;
  vrow emp%rowtype;
begin
  open vemps10(10);
  loop

    fetch vemps10 into vrow;
    exit when vemps10%notfound;
    dbms_output.put_line('部门编号：'||vrow.deptno||'  姓名：'||vrow.ename||'   工资：'||vrow.sal);
  end loop;
  close vemps10;
end;

方式二：系统引用游标方式

declare
  vemps sys_refcursor;
  vrow emp%rowtype;
begin
  open vemps for select * from emp where deptno = 10;
  loop
    fetch vemps into vrow;
    exit when vemps%notfound;
    dbms_output.put_line('部门编号：'||vrow.deptno||'  姓名：'||vrow.ename||'   工资：'||vrow.sal);
  end loop;
  close vemps;
end;

---

例外

意外, 相当于是java中的异常
常用的系统例外：

*      others  ： 其他的所有例外
*      zero_divide :除零例外
*      value_error:类型转换例外
*      no_data_found：没有找到数据，类似空指针
*      too_many_rows：找到了太多行记录，查询出是多行记录，但是赋值给了一行记录

使用语法：

      declare
       -- 声明
      begin
       -- 业务逻辑
      exception
       -- 捕获例外
       when 例外1 then
           // TODO
       when 例外2 then
           //TODO
       when others then
           //TODO
      end;

自定义例外:

例外名称  exception;

抛出例外:

raise 例外名称

使用样板：

declare
  --i number;
  vrow emp%rowtype;
begin
  -- i := 5/0;
  -- i := 'abc';
  -- select * into vrow from emp where empno=1234567;
  select * into vrow from emp;
exception
  when too_many_rows then
    dbms_output.put_line('查询出是多行记录,但是赋值给了一行记录');
  when no_data_found then
    dbms_output.put_line('空指针例外');
  when value_error then
    dbms_output.put_line('类型转换例外');
  when zero_divide then
    dbms_output.put_line('除零例外');
  when others then
    dbms_output.put_line('发生了未知的例外');
end;

声明自定义例外

declare
  myexception exception;
begin
  raise myexception;
exception
  when myexception then
    dbms_output.put_line('发生了自定义的例外');
  when others then
    dbms_output.put_line('发生了其他例外');
end;

案例：查询指定编号的员工,如果没有找到,则抛出自定义的例外

declare
  myempno number:=&指定员工编号;
  cursor vemps(vempno number) is select * from emp where empno = vempno;
  vrow emp%rowtype;
  myexception exception;
begin
  open vemps(myempno);
  fetch vemps into vrow;
  if vemps%notfound then
    raise myexception;
  else

    dbms_output.put_line('员工编号：'||vrow.empno||'  员工姓名：'||vrow.ename);
  end if;
  close vemps;
exception
  when myexception then
    dbms_output.put_line('发生了自定义的例外');
  when others then

    dbms_output.put_line('发生了未知的例外');
end;

---

存储过程

实际上将一段已经编译好了PLSQL代码片断,存储在数据库中,方便其它人调用
作用:
1.提高代码的复用性
2.提高执行效率

语法：

create [or replace] procedure 存储过程的名称(参数名称 in|out 参数类型,参数名称 in|out 参数类型)
is | as
--声明部分
begin
--业务逻辑部分
end;
                in : 输入参数
                out : 输出参数    

使用方式：

-- 方式1:直接调用
call 存储过程名字；
call proc_updatesal(7369,10);

-- 方式2：在PLSQL中调用
declare

begin
  proc_updatesal(7369,-100);
end;

案例：给指定员工涨薪,并打印涨薪前和涨薪后的工资

create or replace procedure proc_updatesal(myempno in number,mycount in number)
is
  mysal number;
begin
  select sal into mysal from emp where empno = myempno;
  dbms_output.put_line('涨薪前的工资是：'||mysal);
  update emp set sal = sal + mycount where empno = myempno;
  dbms_output.put_line('涨薪后的工资是：'||(mysal+mycount));
  commit;
end;

--调用方式一：直接调用
call proc_updatesal(7369,10);
--调用方式二：在PLSQL代码块中调用
declare
begin
  proc_updatesal(7369,10);
end;

案例：封装一个获取指定员工年薪的存储过程
注意：有返回参数的存储过程的调用，需要传入变量，只能够在PLSQL代码中调用了。

create or replace procedure proc_getyearsal(myempno in number,yearsal out number)
is
begin
  select sal*12+nvl(comm,0) into yearsal from emp where empno = myempno;
  dbms_output.put_line('员工工号：'||myempno||'   年薪：'||yearsal);
end;

declare
  yearsal number;
begin
  proc_getyearsal(7369,yearsal);
  dbms_output.put_line('工号:7369   年薪：'||yearsal);
end;

---

存储函数

实际上将一段已经编译好的PLSQL代码片断,封装在数据库中,方便调用
通常:函数给过程调的，现在一般不推荐使用存储函数了。
作用:
1.提高代码复用性
2.提高执行效率

语法：

create [or replace] function 函数名称(参数 in|out 参数类型,参数 in|out 参数类型) return 返回类型
as|is

begin

end;

存储过程和存储函数的区别:

            1. 函数有返回值, 过程没有
            2. 过程能实现功能,函数能实现
            3. 函数能实现功能,过程也能实现
            4. 函数可以在SQL语句中直接调用
            5. 函数和过程本质上没有区别
       在Oracle新版本中已经不推荐使用存储函数了,函数最开始是方便过程去调用的         

案例：查询指定员工的年薪

create or replace function func_getyearsal(myempno in number) return number
is
  yearsal number;
begin
  select sal*12+nvl(comm,0) into yearsal from emp where empno = myempno;
  return yearsal;
end;

declare
  yearsal number;
begin
  yearsal := func_getyearsal(7369);
  dbms_output.put_line('年薪是：'||yearsal);
end;

JAVA调用存储过程

   回顾JDBC开发步骤:
      1.注册驱动
      2.获取连接
      3.创建执行SQL对象
      4.封装参数
      5.执行SQL
      6.处理结果
      7.释放资源   

使用样板一：java工程中：

使用java调用oracle的存储过程的时候需要先导入jar包：ojdbc.jar

     @Test
      public void test01() throws Exception{
           //注册驱动
           Class.forName("oracle.jdbc.driver.OracleDriver");
           //获得连接
           String url = "jdbc:oracle:thin:@192.168.64.100:1521:orcl";
           String username = "zhangsan";
           String password = "zhangsan";
           Connection connection = DriverManager.getConnection(url, username, password);
           //创建执行sql语句的对象
           String sql = "{call proc_getyearsal(?,?)}";
           CallableStatement call = connection.prepareCall(sql);
           //封装参数
           //in类型，输入类型的参数
           call.setObject(1, 7369);
           //注册out类型，注册输出类型的参数
           call.registerOutParameter(2, OracleTypes.NUMBER);
           //执行sql语句
           call.execute();
           //处理结果
           Object object = call.getObject(2);
           System.out.println(object);
           //关闭资源
           call.close();
           connection.close();
      }

案例：调用输出类型为游标的存储过程 – 所有员工信息

create or replace procedure proc_findall(vemps out sys_refcursor)
is
begin
  -- 打开游标. 谁用谁关闭
  open vemps for select * from emp;
end;

java代码：

      @Test
      public void test02() throws Exception {
           // 注册驱动
           Class.forName("oracle.jdbc.driver.OracleDriver");
           // 获得连接
           String url = "jdbc:oracle:thin:@192.168.64.100:1521:orcl";
           String username = "zhangsan";
           String password = "zhangsan";
           Connection connection = DriverManager.getConnection(url, username, password);
           // 创建执行sql语句的对象
           String sql = "{call proc_findall(?)}";
           CallableStatement call = connection.prepareCall(sql);
           // 封装参数
           // 注册out类型，注册输出类型的参数
           call.registerOutParameter(1, OracleTypes.CURSOR);
           // 执行sql语句
           call.execute();
           // 处理结果
           ResultSet resultSet = (ResultSet) call.getObject(1);
           while(resultSet.next()){
                 System.out.print(resultSet.getObject("ename")+"    ");
                 System.out.println(resultSet.getObject("sal"));
                 System.out.println("======================");
           }
     //释放资源
     resultSet.close();   //也体现出了谁打开游标谁关闭的原则
     call.close();
     connection.close();
      }

---

触发器 – trigger

   当用户执行了insert | update | delete 这些操作的时候, 可以去触发一段PLSQL代码片断
   作用:
        数据校验
   语法:
      create [or replace] trigger 触发器的名称
        before | after
        insert | update | delete
        on 表名
        [for each row]
        declare
           -- 声明
        begin
           -- 业务逻辑
        end;

  触发器的分类:
        1.语句级触发器
              一条SQL操作,影响了多少行记录,只触发一次
        2.行级触发器:只有行级触发器可以拿到：new和：old两个对象
              一条SQL操作,影响了多少行,就触发多少次
              :new   代表的新的记录
              :old   代表的旧的记录       

例子：向表中插入数据时, 输出hello trigger

create or replace trigger tri_test1
before
insert
on emp
declare
begin
   dbms_output.put_line('hello 触发器.');
end;

insert into emp(empno,ename) values(9527,'huaan');

案例：校验周一不能插入员工

create or replace trigger tri_mondy
before insert
on emp
declare
  today varchar2(20);
begin
  select trim(to_char(sysdate,'day')) into today from dual;
  if today = 'tuesday' then
    raise_application_error('-20000','今天周二，不能够插入员工');
    --手动抛出一个异常，异常代码在-20000,-20999之间
  end if;
end;

insert into emp(empno,ename) values(1234,'zhangsan');

– 语句级触发器

create or replace trigger tri_wordlevel
before update
on emp
declare
begin
  dbms_output.put_line('语句级别的触发器触发了');
end;

update emp set sal = sal + 1 where empno = 7369;

– 行级触发器

create or replace trigger tri_line
before update
on emp
for each row
declare
begin
  dbms_output.put_line('行级触发器触发了');
end;

update emp set sal = sal + 1 ;

– 判断员工涨工资后的工资一定要大于涨工资前的工资

create or replace trigger tri_raisesal
before update
on emp
for each row
declare
begin
    if :new.sal<:old.sal then
    raise_application_error(-20001,'明明降薪了');
    end if;
end;

update emp set sal = sal - 100;

案例：模拟mysql中ID的自增属性:auto_increment

create table stu(
    sid number primary key
);
create sequence stu_id;

create or replace trigger tri_autincrement
before insert
on stu
for each row
declare
begin
  if :new.sid is null then
    select stu_id.nextval into :new.sid from dual;
    dbms_output.put_line('自增了');
  end if;
end;

insert into stu values(null);
select * from stu;