博主名取自《小羊肖恩》中的小羊肖恩,名字为:肖恩,音译为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;
如有任何问题,欢迎评论留言。