Oracle学习第四天（游标_异常_存储过程_存储函数_触发器_使用JDBC操作Oracle数据库）

一、游标

1.游标的概念

游标是用来操作结果集的，相当于JDBC中的ResultSet

2.游标的语法

语法：cursor 游标名[(参数名  参数类型)] is 查询结果集
游标中使用步骤：
	1.声明游标
	2.打开游标   open 游标名
	3.从游标中取出数据  fetch游标名  into  变量
		游标名%fond ：找到数据
		游标名%notfond:没有找到数据
	4.关闭游标： close 游标名

3.不带参数的游标

--输入所有的员工姓名和工资信息
/*
游标：所有的员工信息
声明一个变量，记录一行数据 %rowtype
*/
declare
  --定义游标
  cursor vrows is select * from emp;
  --声明一个变量，记录一行数据
  vrow emp%rowtype;
begin
  --打开游标
  open vrows;
  --从游标中提取数据
  loop
    fetch vrows into vrow;
    dbms_output.put_line('姓名：'||vrow.ename||'工资：'||vrow.sal);
    exit when vrows%notfound;
  end loop;
  --关闭游标
  close vrows;
end;

4.不带参数的游标

--带参数的游标 根据员工的编号查询员工姓名和工资
declare
  --声明游标
  cursor vrows(dno number) is select * from emp where deptno=dno;
  --声明一个变量存储数据
  vrow emp%rowtype;
begin
  --打开游标
  open vrows(10);
  loop
    --抓取数据
    fetch vrows into vrow;
    dbms_output.put_line('姓名：'||vrow.ename||'工资：'||vrow.sal);
    exit when vrows%notfound;
  end loop;
  --关闭游标
end;

5.系统引用游标

使用步骤
	1.声明游标：游标名 sys_refcursor;
	2.打开游标：open 游标名 for 结果集
	3.从游标中取数据
	4.关闭游标

--使用系统引用游标查询员工姓名和工资
declare
  --声明系统引用游标
  vrows sys_refcursor;
  --声明一个变量
  vrow emp%rowtype;
begin
  --打开游标
  open vrows for select * from emp;
  --取数据
  loop
    --抓取数据
    fetch vrows into vrow;
    dbms_output.put_line('姓名：'||vrow.ename||'工资：'||vrow.sal);
    exit when vrows%notfound;
  end loop;
  --关闭游标
  close vrows;
end;

6.使用游标进行emp表工资信息的更新

-- 依据不同的职位，涨工资  职位是PRESIDENT的工资涨10000，MANAGER涨800 其它人涨400
declare
 --声明游标
   cursor vrows is select * from emp;
   --声明变量
   vrow emp%rowtype;
begin 
  -- 打开游标
  open vrows;
  loop
    --从游标中取数据
    fetch vrows into vrow;
    if vrow.job='PRESIDENT' then
      update emp set sal=sal+800 where empno=vrow.empno;
    elsif vrow.job='MANAGER' then
      update emp set sal=sal+400 where empno=vrow.empno;
    else 
      update emp set sal=sal+200 where empno=vrow.empno;
    end if;
    exit when vrows%notfound;
  end loop;
  --关闭游标
  close vrows;
  --提交事务
  commit;
end;

二、例外(异常)

1.异常的基本概述：程序过程中发生的异常，相当于java中的异常
2.Oracle异常的声明格式：

3.Oracle中的常用异常类型：

declare
  value number;
  vrow emp%rowtype;
begin
  --value:=8/0;
  --value:='aaa';
  --select * into vrow from emp;
  select * into vrow from emp where empno=1;
exception
  when zero_divide then
    dbms_output.put_line('除数不能为0');
  when value_error then
    dbms_output.put_line('类型转换发生了异常');
  when too_many_rows then
    dbms_output.put_line('查询出多行记录，但是只赋值给一行数据');
  when no_data_found then
    dbms_output.put_line('没有查询到数据');
  when others then
    dbms_output.put_line('发生了其它异常');
end;

4.自定义异常: 查询指定编号的员工，如果没有找到，则抛出异常

--自定义异常
declare
  --声明游标
  cursor vrows is select * from emp where empno=0111;
  --声明变量
  vrow emp%rowtype;
  --声明一个自定义的异常
  no_emp exception;
begin
  --打开游标
  open vrows;
  --取数据
  fetch vrows into vrow;
  --判断游标是否有数据
  if vrows%notfound then
    raise no_emp; --抛出异常
  end if;
  --关闭游标
  close vrows;
  --捕获异常
  exception
    when no_emp then
      dbms_output.put_line('没有查询出对应的数据');
end;

三、存储过程

1.封装在服务器上的一段PLSQL代码片段。已经编译好了的代码。客户端在调用存储过程的时候，执行效率会非常高效。
2.存储过程的语法：

create procedure 存储过程的名称(参数名称 in/out 参数类型)
is /as
    声明部分：定义变量
begin
	业务逻辑
end;

3.需求：显示指定员工涨薪前和涨薪后的工资信息。

--存储过程
create procedure proc_emp(vempno in number,vsal in number)
is
  --声明变量记录当前工资
  money number;
begin
  --查询当前工资
  select sal into money from emp where empno=vempno;
  dbms_output.put_line('涨薪前的工资：'||money);
  --更新工资
  update emp set sal=sal+vsal where empno=vempno;
  dbms_output.put_line('涨薪前的工资：'||(money+vsal));
  commit;
end;

--调用存储过程函数
call proc_emp(7788,500);

四、存储函数

1.存储函数：一段封装到服务器中的一段PLSQL代码片段，它是已经编译好的代码片段
2.存储函数的语法：

create function存储函数的名称(参数名称 in/out 参数类型)  return 参数类型
is /as
    声明部分：定义变量
begin
	业务逻辑
end;

3.需求：查询指定员工的年薪。

--声明存储函数
create function fun_sal(vempno in number) return number
is
  vsal number;--记录年薪的变量
begin
  select sal*12+nvl(comm,0) into vsal from emp where empno=vempno;
  return vsal;
end;

--调用存储函数
declare
  money number;
begin
  money:=fun_sal(7788);
  dbms_output.put_line('年薪：'||money);
end;

--声明存储过程
create procedure proc_emp1(vempno in number,vsal out number) 
is
  
begin
  select sal*12+nvl(comm,0) into vsal from emp where empno=vempno;
end;

--调用存储过程
declare
  money number;
begin
  proc_emp1(7788,money);
  dbms_output.put_line('年薪：'||money);
end;

五、触发器

1.触发器：当用户执行了insert、update、delete、这些操作的时候，可以触发一系列其他的动作(业务逻辑)
2.作用：在动作执行之前或者之后触发一些业务逻辑。
3.触发器的分类：

语句级触发器：不管影响多少行记录，只会触发一次。
行级触发器：影响多少行记录，就触发多少次。

4.语法

create trigger 触发器名称
before/after
insert/update/delete
on  表名
[for each row]
declare
begin
   触发的业务逻辑
End;

--插入员工信息之后，输出一句话
create trigger tri_emp
after
insert
on emp
declare
begin
  dbms_output.put_line('欢迎你');
end;

insert into emp(empno,ename) values(7788,'zs');

5.需求：员工工资调整，调整后的工资一定要大于调整前的工资

--员工工资调整，调整前的工资一定要大于调整前的工资
create trigger tri_updateSal
before
update
on emp
for each row
declare
begin
  if :old.sal>:new.sal then
    raise_apptioncation_error(-20002,'旧的工资不能大于新的工资');
  end if;
end;

update emp set sal=sal-10;
select * from emp;

6.模拟mysql中的ID的自动增长属性 auto_incerment

--创建一张表
create table person(
  sid number primary key,
  sname varchar2(30)
)
--创建一个序列
create sequence sq_person_sid;
--创建一个触发器
create or replace trigger tri_person_sid
before
insert
on person
for each row
declare
begin
  --给新插入的新记录赋值
  select sq_person_sid.nextval into :new.sid from dual;
end;

insert into person(sname) values('tom');
select * from person;

六、使用JDBC调用存储过程、存储函数

导入依赖

<dependencies>
	<dependency>
	    <groupId>com.oracle</groupId>
	    <artifactId>ojdbc6</artifactId>
	    <version>11.2.0.3</version>
	    <scope>test</scope>
	</dependency>
	<dependency>
	    <groupId>com.oracle</groupId>
	    <artifactId>ojdbc6</artifactId>
	    <version>11.2.0.3</version>
	    <scope>compile</scope>
	</dependency>
</dependencies>

使用JDBC调用存储过程

import oracle.jdbc.internal.OracleTypes;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;

public class TestProcedure {
    public static void main(String[] args) throws Exception {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //获取数据库连接
        String url="jdbc:oracle:thin:@localhost:1521:orcl";
        String username="jerry";
        String password="123";
        Connection conn= DriverManager.getConnection(url,username,password);
        //获取执行sql语句的statement
        CallableStatement statement=conn.prepareCall("{call proc_emp1(?,?)}");
        //设置输入参数
        statement.setInt(1,7369);
        //注册输出参数
        statement.registerOutParameter(2, OracleTypes.NUMBER);
        //执行statement
        statement.execute();
        //获取执行结果
        int totalsal=statement.getInt(2);
        System.out.println(totalsal);
    }
}

使用JDBC调用存取函数

import oracle.jdbc.internal.OracleTypes;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;

public class TestFun {
    public static void main(String[] args) throws Exception {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //获取数据库连接
        String url="jdbc:oracle:thin:@localhost:1521:orcl";
        String username="jerry";
        String password="123";
        Connection conn= DriverManager.getConnection(url,username,password);
        //获取执行sql语句的statement
        CallableStatement statement=conn.prepareCall("{?=call fun_sal(?)}");
        //设置输入参数
        statement.setInt(2,7369);
        //注册输出参数
        statement.registerOutParameter(1, OracleTypes.NUMBER);
        //执行statement
        statement.execute();
        //获取执行结果
        int totalsal=statement.getInt(1);
        System.out.println(totalsal);
    }
}