Oracle之存储过程,存储函数和触发器,以及java代码调用过程和函数

/*
   存储过程  是一段封装的代码块 编译好放在服务器
             好处： 支持多处调用 提升开发效率
                   调用直接运行 提升运行效率
   创建存储过程 create [or replace] procedure 过程名(参数名 in|out 参数数据类型 )
                as|is
                  -声明部门
                begin
                   -处理逻辑代码
                end;
   存储过程的调用
       在begin 和 end之间 过程名传参调用
*/
--使用存储过程给某个员工工资增加100块钱 输出涨工资前后的数据
create or replace  procedure add_sal(eno in number)
as
   v_sal number ;
begin
   --输出原始工资
   select sal into v_sal from emp where empno=eno;
   dbms_output.put_line('原始工资==='||v_sal);
   --涨工资
   update emp set sal=sal+100 where empno=eno;
   commit;

 

 

 

   --输出修改后工资
   select sal into v_sal from emp where empno=eno;
   dbms_output.put_line('修改后工资==='||v_sal);
end;

--过程调用
declare
begin
   add_sal(7369);
end;
-----使用存储过程得到统计后的某个员工年薪
create or replace  procedure count_sal(eno number,year_sal out number)
as
begin
   select sal*12+nvl(comm,0) into year_sal  from emp where empno = eno;
end;

--调用过程统计年薪
declare
  v_year_sal number;
begin
   count_sal(7369,v_year_sal);
   dbms_output.put_line('年薪=='||v_year_sal);
end;
----使用存储过程得到某个部门下的所有员工信息
/*
   cursor 名 is select * from  表
   系统引用类型游标   声明时候不需要指定结果集  在open打开游标时候指定结果集
   emp_cursor sys_refcursor;  --
   open   emp_cursor for select 结果集
*/
create or replace  procedure dept_emp(dno number,dept_all_emp out sys_refcursor )
as
begin
     --给当前的系统引用游标装入数据
     open dept_all_emp for select * from emp where deptno=dno;
end;
--调用存储过程 遍历10号部门下的员工信息
declare
    all_emp sys_refcursor ; --声明系统引用游标 传参使用
    emp_row emp%rowtype;--记录类型接收游标的提取
begin
   dept_emp(10,all_emp);
   --提取游标
   loop
      --先提取后判断
      fetch all_emp into emp_row;
      exit when  all_emp%notfound;
      dbms_output.put_line('eno==='||emp_row.empno||'ename=='||emp_row.ename);
   end loop;
   close all_emp;
end;
----------------------------------
/*
存储函数  是一段封装的代码块 编译好放在服务器
             好处： 支持多处调用 提升开发效率
                   调用直接运行 提升运行效率
   创建函数过程 create [or replace] function 函数名(参数名 in|out 参数数据类型 ) return 数据类型
                as|is
                  -声明部门
                begin
                   -处理逻辑代码
                   return 变量
                end;
   存储函数的调用
       在begin 和 end之间 函数名传参调用 必须有变量接收函数的返回值
*/
--使用存储函数统计年薪
create or replace function fun_count_sal(eno number) return number
as
   v_sal number;
begin
   select sal*12+nvl(comm,0) into v_sal from emp where empno=eno;
   return v_sal;
end;
--函数的调用
declare
   v_sal number;
begin
  v_sal := fun_count_sal(7369);
  dbms_output.put_line(v_sal);
end;
---------out输出参数的函数获得年薪
create or replace function fun_emp_sal(eno number,emp_sal out number) return number
as
 
begin
   select sal*12+nvl(comm,0) into emp_sal from emp where empno=eno;
   return emp_sal;
end;
-----------
declare
   v_sal number;
   emp_year_sal number;
begin
  v_sal := fun_emp_sal(7369,emp_year_sal);
  dbms_output.put_line(v_sal);     --0
  dbms_output.put_line(emp_year_sal);--10800
end;
/*
   存储函数和过程的对比
       1.创建的关键字不一致  procedure function
       2.函数的创建 必须制定函数的返回数据类型
       3.函数在begin end直接必须返回变量
       4.函数的调用必须有变量接收返回值
       5.函数可以用在select 语句中
   使用场景  开发规范
       java调用过程  过程处理业务逻辑 ，如果在逻辑处理中
       用到某些功能性的封装 可以调用函数
       90%通用  函数同样可以调用过程  不是强制限制  
*/
select emp.*,fun_count_sal(empno) from emp;
/*
   触发器 一个监视器 对表中数据的操作监视
          insert  update  delete
          如果对表中数据的操作满足了触发器的执行条件
          触发器会自动执行
    创建语法  create or replace trigger 触发器名
              before|after  --执行时机
              insert|update|delete
              on 表
              declare
              begin
               
              end;
    行级触发器 针对每一行记录监视  for each row
                                  insert            update         delete
          :new  操作之后的记录  将要插入的记录   修改后的记录    nul
          :old  操作之前的记录     null           原始记录        原始记录
*/
---插入数据之后 输出欢迎语句
create or replace trigger insert_tri
after
insert
on dept
declare
begin
   --输出欢迎语句
   dbms_output.put_line('欢迎加入部门');
end;
--------------插入数据测试
insert into dept values(3,'test1','bj');
commit;
----使用触发器监视部门表 插入数据不能在休息日

/*
   数据库封装好的错误提示  raise_application_error(v1,v2)
                          v1是错误代码 -20000 -20999 v2是错误提示语
*/
create or replace trigger no_work_day
before
insert
on dept
declare
   v_day varchar(10);
begin
    --提取当前的星期 (saturday,sunday)
    select to_char(sysdate,'day') into v_day from dual;
    --判断当前是否是休息日
    if trim(v_day) in ('saturday','sunday') then
      --不允许插入
      raise_application_error(-20001,'不能在休息日插入数据');
    end if;
end;
select to_char(sysdate,'day')  from dual;
----再来一个实际应用 触发器限制表中数据操作 不能降低工资
create or replace trigger can_not_low
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-1 where empno=7369;
commit;

create table person(
   pid number(9),
   pname varchar(10),
   phone varchar(11)
)
insert into person(pname,phone) values('zs','123456');
commit;

/*
   触发器实现id 自增长
  
*/
create or replace trigger auto_incr_id
before
insert
on person
for each row   --行级触发器
declare
 
begin
   --给id赋值 id有要求 必须是自增长的数值
   select seq_class.nextval into :new.pid from dual;
   
end;

 

---------------------------------------------

//JDBC调用

public class TestJdbc {

String driverClass = "oracle.jdbc.driver.OracleDriver";
String url ="jdbc:oracle:thin:@192.168.17.128:1521:orcl";
String user= "itcast_03";
String password = "itcast_03";
/*
*测试jdbc连接数据库
*
* */
@Test
public void querEmp(){
try{
//加载驱动
Class.forName(driverClass);
//获取链接
Connection con = DriverManager.getConnection(url, user,password);
//获取预编译的statement
PreparedStatement pst= con.prepareStatement("select * from emp");
//执行查询
ResultSet rs = pst.executeQuery();
//处理结果
while(rs.next()){
System.out.println(rs.getInt(1)+"员工姓名"+rs.getString("ename"));
}
rs.close();
con.close();
//关闭连接
}catch(Exception e){
e.printStackTrace();
}

}
/*存储过程的调用
* {call <procedure-name>[(<arg1>,<arg2>, ...)]}
add_sal(eno number,addsal number)
* */
@Test
public void callAddSal(){
try{
//加载驱动
Class.forName(driverClass);
//获取链接
Connection con = DriverManager.getConnection(url, user,password);
//获取预编译的statement
CallableStatement pst= con.prepareCall("{call add_sal(?,?)}");
pst.setInt(1, 7499);
pst.setInt(2, 1000);
//执行查询
pst.execute();
con.close();
//关闭连接
}catch(Exception e){
e.printStackTrace();
}

}

/*存储过程的调用
* {call <procedure-name>[(<arg1>,<arg2>, ...)]}
count_yearsal(eno number,total_year_sal out number)
* */
@Test
public void callCountSal(){
try{
//加载驱动
Class.forName(driverClass);
//获取链接
Connection con = DriverManager.getConnection(url, user,password);
//获取预编译的statement
CallableStatement pst= con.prepareCall("{call count_yearsal(?,?)}");
pst.setInt(1, 7499);
//注册输出参数
pst.registerOutParameter(2, OracleTypes.NUMBER);
//执行查询
pst.execute();
int total = pst.getInt(2);
System.out.println(total);
con.close();
//关闭连接
}catch(Exception e){
e.printStackTrace();
}

}
/*
* pro_dept_emp(dno number,dept_emp out sys_refcursor)
* */
@Test
public void callProEmp(){
try{
//加载驱动
Class.forName(driverClass);
//获取链接
Connection con = DriverManager.getConnection(url, user,password);
//获取预编译的statement
CallableStatement pst= con.prepareCall("{call pro_dept_emp(?,?)}");
pst.setInt(1, 10);
//注册输出参数
pst.registerOutParameter(2, OracleTypes.CURSOR);
//执行查询
pst.execute();
OracleCallableStatement ocs = (OracleCallableStatement)pst;
ResultSet rs = ocs.getCursor(2);
while(rs.next()){
System.out.println(rs.getInt(1)+"员工姓名"+rs.getString("ename"));
}
rs.close();
ocs.close();
pst.close();
con.close();
//关闭连接
}catch(Exception e){
e.printStackTrace();
}

}
}

 ============================

hibernate5调用存储过程

@Test
public void fun2(){
	Session session = JDBCUtils.openSession();
	ProcedureCall pc = session.createStoredProcedureCall("proc_emp_yearsal");//存储过程的名称
	pc.registerParameter("v_no", Long.class, ParameterMode.IN).bindValue(7788l);
	//参数1：对应存储过程中的变量名1，参数2：类型，参数3：是in还是out，参数4：手动设置了一个查询参数
	pc.registerParameter("v_yearSal", Long.class, ParameterMode.OUT);
	String string = pc.getOutputs().getOutputParameterValue("v_yearSal").toString();
	System.out.println(string);
}