java代码调用oracle存储过程-阿里云开发者社区

开发者社区> 杰克.陈> 正文

java代码调用oracle存储过程

简介: 原文:java代码调用oracle存储过程 一、简介   与调用mysql存储过程类型过程都是如下   1、创建连接 Connection conn = DriverManager.getConnection(url, user, password);   2、创建CallableStatement CallableStatement statement = conn.
+关注继续查看
原文:java代码调用oracle存储过程

一、简介

  与调用mysql存储过程类型过程都是如下

  1、创建连接 Connection conn = DriverManager.getConnection(url, user, password);

  2、创建CallableStatement CallableStatement statement = conn.prepareCall(sql);

  3、设置参数

    statement.setInt(1, id);
    statement.registerOutParameter(2, Types.VARCHAR);
    statement.registerOutParameter(3, Types.INTEGER);
    statement.registerOutParameter(4, Types.VARCHAR);

   4、执行

    statement.execute(); 或 statement.executeUpdate();

  5、获取返回

    int age = statement.getInt(3);

  只是oracle存储过程有的结果集是以游标的方式返回,此时我们需要调用ResultSet rs = (ResultSet) statement.getObject(1);方法回去结果集

二、代码

  以下存储过程表结构如下:

DROP TABLE person ;
CREATE TABLE person (
id NUMBER(11) NOT NULL ,
username VARCHAR2(255 ) NULL ,
age NUMBER(11) NULL ,
password VARCHAR2(255) NULL ,
PRIMARY KEY (id)
)

  1、查询所有记录

  存储过程代码如下:

create or replace procedure pro_person_findall(
       p_cursor out pkg_const.r_cursor
)
is
begin
  open p_cursor for
  select *  from person;
  exception
  when others then
    DBMS_OUTPUT.PUT_LINE('获取信息发生错误');
end pro_person_findall;

  调用代码如下

public static void findAll() {
        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:wuxx";
        String user = "wuxx";
        String password = "wuxx";
        try {
            Class.forName(driver);
            Connection conn = DriverManager.getConnection(url, user, password);
            String sql = "{call pro_person_findall2(?)}";
            CallableStatement statement = conn.prepareCall(sql);
            statement.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
            statement.execute();
            ResultSet rs = (ResultSet) statement.getObject(1);
            ResultSetMetaData rmd = rs.getMetaData();
            System.out.print(rmd.getColumnName(1) + "    ");
            System.out.print(rmd.getColumnName(2) + "    ");
            System.out.print(rmd.getColumnName(3) + "    ");
            System.out.print(rmd.getColumnName(4) + "\n");
            while (rs.next()) {
                System.out.print(rs.getInt("id") + "    ");
                System.out.print(rs.getString("username") + "    ");
                System.out.print(rs.getInt("age") + "    ");
                System.out.print(rs.getString("password") + " \n");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

  2、查询一条记录

  存储过程如下

CREATE OR REPLACE PROCEDURE PRO_PERSON_FINDBYID(
    v_id IN NUMBER,
    v_username    OUT    VARCHAR2,
    v_age    OUT    NUMBER,
    v_password OUT    VARCHAR2,
   p_count out number
)
AS 
BEGIN
    SELECT username, age, password INTO v_username, v_age, v_password  from person where id = v_id;
  p_count := 1; 
  exception
    when others then
    p_count := 0;
END;

  调用代码如下:

public static void find(Integer id) {
        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:wuxx";
        String user = "wuxx";
        String password = "wuxx";
        try {
            Class.forName(driver);
            Connection conn = DriverManager.getConnection(url, user, password);
            String sql = "{call PRO_PERSON_FINDBYID(?,?,?,?,?)}";
            CallableStatement statement = conn.prepareCall(sql);
            BigDecimal rid = new BigDecimal(id);
            statement.setInt(1, id);
            statement.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
            statement.registerOutParameter(3, oracle.jdbc.OracleTypes.NUMBER);
            ; //
            statement.registerOutParameter(4, oracle.jdbc.OracleTypes.VARCHAR);
            statement.registerOutParameter(5, oracle.jdbc.OracleTypes.NUMBER);
            statement.execute();
            int flag = statement.getInt(5);
            if (flag != 0)
                System.out.println(statement.getString(2) + "  "
                        + statement.getInt(3) + "  " + statement.getString(4));
            else
                System.out.println("data not found!");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

  3、增加记录

  存储过程代码如下:

create or replace procedure pro_person_insert(
       p_id number,
       p_username varchar2,
       p_age number,
       p_password varchar2,
       p_count out number
)
is
begin
   insert into person (id, username, age, password) values(p_id, p_username, p_age, p_password);
   p_count := SQL%ROWCOUNT;  -- SQL%ROWCOUNT为 隐士游标的属性
   commit;
   exception
     when others then
     p_count := 0;
end pro_person_insert;

  调用代码如下:

public static void add(Integer id, String username, int age,
            String u_password) {
        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:wuxx";
        String user = "wuxx";
        String password = "wuxx";
        try {
            Class.forName(driver);
            Connection conn = DriverManager.getConnection(url, user, password);
            String sql = "{call pro_person_insert(?,?,?,?,?)}";
            CallableStatement statement = conn.prepareCall(sql);
            statement.setInt(1, id);
            statement.setString(2, username);
            statement.setInt(3, id);
            statement.setString(4, u_password);
            statement.registerOutParameter(5, oracle.jdbc.OracleTypes.NUMBER);// 增加记录是否成功的标记,1 成功,0失败
            statement.execute();
            System.out.println(statement.getInt(5));
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

  4、更新记录

  存储过程代码如下:

create or replace procedure pro_person_update(
       p_id number,
       p_age number,
       p_password varchar2,
       p_count out number
)
is
begin
  update person set age = p_age, password = p_password where id = p_id;
  p_count := SQL%ROWCOUNT;
  commit;
  exception
    when no_data_found then
      p_count := 0;
    when others then
      p_count := -1;
end pro_person_update;

  调用代码如下:

public static void update(Integer id, int age, String u_password) {
        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:wuxx";
        String user = "wuxx";
        String password = "wuxx";
        try {
            Class.forName(driver);
            Connection conn = DriverManager.getConnection(url, user, password);
            String sql = "{call pro_person_update(?,?,?,?)}";
            CallableStatement statement = conn.prepareCall(sql);
            statement.setInt(1, id);
            statement.setInt(2, age);
            statement.setString(3, u_password);
            statement.registerOutParameter(4, oracle.jdbc.OracleTypes.NUMBER);// 增加记录是否成功的标记
            statement.execute();
            System.out.println(statement.getInt(4));
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

  5、删除记录

  存储过程代码如下:

create or replace procedure pro_person_delete(
       p_id number,
       p_count out number
)
is
begin
  delete from person where id = p_id;
  p_count := SQL%ROWCOUNT;
  commit;
  exception
    when no_data_found then
      p_count := 0;
    when others then
      p_count := -1;
end pro_person_delete;

  调用代码如下:

public static void delete(Integer id) {
        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:wuxx";
        String user = "wuxx";
        String password = "wuxx";
        try {
            Class.forName(driver);
            Connection conn = DriverManager.getConnection(url, user, password);
            String sql = "{call pro_person_delete(?,?)}";
            CallableStatement statement = conn.prepareCall(sql);
            statement.setInt(1, id);
            statement.registerOutParameter(2, oracle.jdbc.OracleTypes.NUMBER);// 增加记录是否成功的标记
            statement.execute();
            System.out.println(statement.getInt(2));
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
C/C++封装库ocicpplib调用Oracle存储过程的方法
工作这么多年,一直使用Mysql, Oracle数据库没有接触过, 这次要使用C/C++语言来调用Oracle的存储过程, 懒得自己去看OCI的API了, 直接上网找别人封装的库, 最后发现了这个ocicpplib, 下载了看了一下, 还不错, 使用起来比较简单。
1069 0
OTL调用Oracle存储过程
OTL很早前用过,今天写东西要调存储过程,程序写完了,调试死活通不过,折腾了一早晨。 最后才发现错误,这里总结一下: 1、代码写的不规范。 有个参数后边少写了个“,”以至于总是抱错。而单独写的测试例子就没问题,后来一步一步跟踪了后才发现。
1367 0
竞彩足球混合过关赔率API调用示例代码
<?php// +----------------------------------------------------------------------// | 竞彩足球混合过关赔率API调用示例代码 - 麦趣数据// | 在线接口文档:http://www.
3395 0
+关注
杰克.陈
一个安静的程序猿~
10427
文章
2
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载