java调用存储过程和存储函数

博主名取自《小羊肖恩》中的小羊肖恩，名字为：肖恩，音译为Sean，自己取的姓：阿奇，为符合我们的阅读习惯，连起来组成为ArchieSean。博主志在将博客打造成为个人线上笔记的技术栈，方便自己也方便他人。如博客中有任何错误，请各位指出，谢谢大家。

• 使用java语言调用存储过程：

 		//注册驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //获取连接
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.68.128:1521:orcl", "zhangsan", "zhangsan");
        //定义sql
        String sql = "{call p2(?,?)}";//调用存储过程 
        CallableStatement statement = connection.prepareCall(sql);
        statement.setInt(1, 7788);
     	statement.registerOutParameter(2, Types.INTEGER );      
        statement.execute();
      	System.out.println(statement.getObject(2));
        statement.close();
        connection.close();

• 存储过程sql

create or replace procedure p2(eno emp.empno%type,yearnum 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;
   yearnum:=s+c;
  
 end;

• java调用存储函数

		//注册驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //获取连接
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.68.128:1521:orcl", "zhangsan", "zhangsan");
        //定义sql
        String sql = "{?=call f_yearsal(?)}";//调用存储函数 
        CallableStatement statement = connection.prepareCall(sql);
        statement.setInt(2, 7788);
     	statement.registerOutParameter(1, Types.INTEGER );      
        statement.execute();
      	System.out.println(statement.getObject(1));
        statement.close();
        connection.close();

• 存储函数

 create  or replace function f_yearsal(eno emp.empno%type) return number
 as
 s number(10);
 begin
   select sal*12+nvl(comm,0) into s from emp where empno=eno;
   return s;
 end;

如有任何问题，欢迎评论留言。