欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle数据库基本操作 (五) —— 使用java调用存储过程

程序员文章站 2022-04-14 12:54:36
本文主要展示如何利用java代码连接Oracle数据并调用存储过程的简单方法。 ......

 一、环境准备

 登录Oracle数据库scott账号,利用emp进行操作。

1、创建 proc_getyearsal 存储过程

1 -- 获取指定员工年薪
2 create or replace procedure proc_getyearsal(vempno in number,vyearsal out number)
3 is
4        
5 begin
6    select sal*12+nvl(comm,0) into vyearsal from emp where empno=vempno;
7 end;

 2、创建 proc_gettemps 存储过程(游标)

1 create or replace procedure proc_gettemps(vemps out sys_refcursor)
2 is
3 BEGIN
4 open vemps for select * from emp where deptno = 20;
5 end;

3、导入数据库驱动包 —— ojdbc14.jar

 二、java代码示例

 1 package com.pri.test;
 2 
 3 public class TestProcedure {
 4 
 5   /*
 6     java调用存储过程模板(一)
 7     获取单值操作
 8   */
 9     @Test
10     public void test01() throws Exception {
11         //1.注册驱动
12         Class.forName("oracle.jdbc.driver.OracleDriver");
13         //2.获取连接
14         String url = "jdbc:oracle:thin:@10.211.55.29:1521/orcl";
15         String user = "zhangsan";
16         String password = "zs123";
17         Connection conn = DriverManager.getConnection(url, user, password);
18         //3.获取执行SQL的对象
19         String sql = "{call proc_getyearsal(?,?)}";
20         CallableStatement callableStatement = conn.prepareCall(sql);
21         //3.1 设置输出参数
22         callableStatement.setInt(1,7369);
23         //3.2 注册输出类型
24         callableStatement.registerOutParameter(2, Types.DOUBLE);
25         //4.执行SQL
26         callableStatement.execute();
27         //5.执行结果
28         double yearsal = callableStatement.getDouble(2);
29         System.out.println("年薪:"+yearsal);
30         //6.释放资源
31         callableStatement.close();
32         conn.close();
33     }
34   
35   /*
36      java调用存储过程模板(二)
37     多行记录(游标)操作
38   */
39     @Test
40     public void test03() throws Exception {
41         //1.注册驱动
42         Class.forName("oracle.jdbc.driver.OracleDriver");
43         //2.获取连接
44         String url = "jdbc:oracle:thin:@10.211.55.29:1521/orcl";
45         String user = "zhangsan";
46         String password = "zs123";
47         Connection conn = DriverManager.getConnection(url, user, password);
48         //3.获取执行SQL的对象
49         String sql = "{call proc_gettemps(?)}";
50         CallableStatement callableStatement = conn.prepareCall(sql);
51         //3.1 注册输出类型
52         callableStatement.registerOutParameter(1, OracleTypes.CURSOR);
53         //4.执行SQL
54         callableStatement.execute();
55         //5.获取结果
56         System.out.println(callableStatement.getClass().getName());
57 //        T4CCallableStatent call2 = () callableStatement;
58         OracleCallableStatement call2 = (OracleCallableStatement) callableStatement;
59         ResultSet rs = call2.getCursor(1);
60 
61         while(rs.next()){
62             System.out.println(rs.getObject("empno"));
63             System.out.println(rs.getObject("ename"));
64             System.out.println(rs.getObject("sal"));
65             System.out.println("------------------------");
66         }
67         //6.释放资源
68         rs.close();
69         callableStatement.close();
70         conn.close();
71     }
72 
73 }