Oracle学习 DAY_04

一、存储过程

1. 存储过程：

1. 存储过程就是提前以及编译好的一段pl/sql语言，放置在数据库端。
2. 可以直接被调用。这一段pl/sql一般都是固定步骤的业务。

1.1 给指定员工涨100块钱

--or replace: 不写的话，不能覆盖。(最好写上，因为不能保证一次性写对这个存储过程)
create or replace procedure p1(eno emp.empno%type)
is

begin
  update emp set sal=sal+100 where empno = eno;
  commit;
end;

--调用存储过程
declare

begin
  p1(7788);
end;

1.2 通过存储函数实现计算指定员工的年薪

--存储过程和存储函数的参数都不能带长度
--存储函数的返回值类型不能带长度
create or replace function f_yearsal(eno emp.empno%type) return number --eno: 参数
is
  s number(10);
begin
  select sal*12+nvl(comm,0) into s from emp where empno =eno;
  return s;
end;

--测试f_yearsal
--存储函数在调用的时候，返回值需要接收
declare
  s number(10);
begin
  s := f_yearsal(7788);
  dbms_output.put_line(s);
end;

1.3out类型参数如何使用

--使用存储过程来算年薪
create or replace procedure p_yearsal(eno emp.empno%type,yearsal out number)
is
   s number(10);
   c emp.comm%type;
begin
  select sal*12,nvl(comm,0) into s,c from emp where empno = eno;
  yearsal := s+c;
end;

--测试p_yearsal
declare
  yearsal2 number(10);
begin
  p_yearsal(7788,yearsal2);
  dbms_output.put_line(yearsal2);
end;

1.4 in和out类型参数的区别是什么？

1. 凡是涉及到into查询语句赋值或者有:=操作的参数，都必须使用out来修饰。
2. 存储过程和存储函数有什么区别
3. 语法区别：关键字不一样，存储函数比存储过程多了两个return语句。
4. 本质区别：存储函数有返回值，而存储过程没有返回值。
   如果存储过程想实现有返回值的业务，我们必须使用out类型的参数。即便是存储过程实现了有返回值的业务，其本质也不是真的有了返回值，而是在存储过程内部给out类型参数赋值，在执行完毕后，我们直接拿参数的值。

1.5我们可以利用存储函数有返回值的特性，来自定义函数，而存储过程不能用来自定义函数。

1.5.1 案例需求：查询出员工姓名，员工所在的部门。

1.5.1.1 复制scott用户下的dept表到itheima超级管理员用户下。

create table dept as (select * from scott.dept)

1.5.1.2

--原始查询
select
  e.ename,d.dname
from
  emp e,dept d
where
  e.deptno=d.deptno;

1.5.1.3 使用存储函数来实现提供一个部门编号，输出一个部门名称

create or replace function fdna2(dno dept.deptno%type) return dept.dname%type
is
  dna dept.dname%type;
begin
  select dname into dna from dept where deptno=dno;
  return dna;
end;

1.5.1.4 使用定义好的fdna函数来实现案例需求。

select e.ename,fdna2(e.deptno)
from emp e;

1.6 触发器：制定一个规则，在我们进行增删改操作的时候，只要满足改规则，自动触发，无需调用。

1. 语句级触发器：不包含有for each row的触发器
2. 行级触发器：包含for each row的就是行级触发器。
3. 加for each row是为了使用：old或者：new对象或者一行记录

1.6.1 语句级触发器

例子：插入一条数据，输出一个新员工入职

create or replace trigger t1
after
insert
on person
declare

begin
  dbms_output.put_line('一个新员工入职');
end;
--触发t1
select * from person;
insert into person(pid,name) values(1,'小红');
commit;

1.6.2 行级触发器

例子1：不能给员工降工资
raise_application_error(-20001~-20999之间，‘错误提示信息’)

create or replace trigger g2
before
update
on emp
for each row
declare

begin
  if :old.sal>:new.sal then
     raise_application_error(-20001,'不能给员工降薪资');
  end if;
end;

--触发t2
select * from emp where empno = 7788;
update emp set sal=sal+1 where empno = 7788;
commit

例子2：触发器实现主键自增。【行级触发器】

--分析：在用户做插入操作之前，拿到即将插入的数据，
--给该数据中的主键列赋值
create or replace trigger auid
before
insert
on person
for each row
declare

begin
    select s_person.nextval into :new.pid from dual;
end;

--测试插入能否触发主键自增触发器
insert into person(name) values('小城');
commit;

--这个也会触发自增，pid不是1，而是s_person.nextva。
insert into person values(1,'老牛');
commit;

二、Java中调用存储过程、存储函数

2.1 新建一个Maven工程

<!-- Oracle数据库Jar包 -->
<dependency>
     <groupId>com.oracle</groupId>
     <artifactId>ojdbc14</artifactId>
     <version>10.2.0.4.0</version>
     <scope>runtime</scope>
</dependency>

<!-- junit测试Jar包 -->
<dependency>
	  <groupId>junit</groupId>
	  <artifactId>junit</artifactId>
	  <version>4.12</version>
	  <scope>test</scope>
</dependency>

2.2 Java调用Oracle

    @Test
    public void javaCallOracle() throws Exception {
        //加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //获取连接
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@Oracle主机名:端口号:数据库名称", "用户名", "密码");
        //("jdbc:oracle:thin:@192.168.123.1:1521:orcl", "itheima", "itheima")
        PreparedStatement preparedStatement = connection.prepareStatement("select * from emp where empno = ?");
        preparedStatement.setInt(1,7788);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            System.out.println("empno："+resultSet.getInt("empno"));
            System.out.println("ename："+resultSet.getString("ename"));
        }
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

2.3 Java调用存储过程

 @Test
    public void javaCallProdure() throws Exception {
        //加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //获取连接
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.110.2:1521:orcl", "itheima", "itheima");
        /**
             {call <procedure-name>[(<arg1>,<arg2>, ...)]}  --调用存储过程
         */
        CallableStatement pstm = connection.prepareCall("{call p_yearsal(?,?)}");
        //参数赋值
        pstm.setInt(1,7788);
        pstm.registerOutParameter(2, OracleTypes.NUMBER);
        pstm.execute();
        //输出结果
        System.out.println(pstm.getInt(2));
        pstm.close();
        connection.close();
    }

2.4 Java调用存储函数

@Test
    public void javaCallFunction() throws Exception {
        //加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //获取连接
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.110.2:1521:orcl", "itheima", "itheima");
        /**
         *   {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}   --调用存储函数
         */
        CallableStatement pstm = connection.prepareCall("{?= call f_yearsal(?)}");
        //参数赋值
        pstm.setInt(2,7788);
        pstm.registerOutParameter(1, OracleTypes.NUMBER);
        pstm.execute();
        //输出结果
        System.out.println(pstm.getInt(1));
        pstm.close();
        connection.close();
    }