java函数触发器_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 [(,, ...)]}

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 [(,, ...)]}

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);

}