1）在oracle的scott下，创建一个存储过程（查询工资最高人的信息，假设只有一个）
create or replace procedure proc_querysal( v_empno out emp.empno%type , v_ename out emp.ename%type , v_sal out emp.ename%type )
as
begin
select empno , ename , sal into v_empno ,v_ename , v_sal from emp where sal = (select max(sal) from emp);
end;
/

2）java 调用 proc_querymax
package com.wp.oracle;

import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;

public class OracleConn
{
    private final static String DB_URL = "jdbc:oracle:thin:@192.168.0.22:1521:simple";
    private final static String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
    private final static String DB_USERNAME = "scott";
    private final static String DB_PASSWORD = "scott";

    public Connection getConnection()
    {
        Connection conn = null;
        try
        {
            /** 使用Class.forName()方法自动创建这个驱动程序的实例且自动调用DriverManager来注册它 */
            Class.forName(DB_DRIVER);
            /** 通过DriverManager的getConnection()方法获取数据库连接 */
            conn = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
            /*
            if (conn != null)
            {
                System.out.print("OK");
            };
            */
        } catch (Exception ex)
        {
            ex.printStackTrace();
        }
        return conn;
    }

    public void closeConnection(Connection conn)
    {
        try
        {
            if (conn != null)
            {
                /** 判断当前连接连接对象如果没有被关闭就调用关闭方法 */
                if (!conn.isClosed())
                {
                    conn.close();
                }
            }
        } catch (Exception ex)
        {
            ex.printStackTrace();
        }
    }




    public static void main(String[] args)
    {
        OracleConn racleConn = new OracleConn();

        Connection conn = oracleConn.getConnection();

        try
        {
            /*
            Statement proc = conn.createStatement();

            proc.execute("call scott.dept_proc() ");
            */

            CallableStatement proc = conn.prepareCall("{call scott.proc_querysal(?,?,?)}");

            proc.registerOutParameter(1, Types.INTEGER);
            proc.registerOutParameter(2, Types.VARCHAR);
            proc.registerOutParameter(3, Types.INTEGER);

            proc.execute();

            String result = "工资最高人的信息：编号："+proc.getInt(1)+"，姓名："+proc.getString(2)+"，工资:"+proc.getInt(3);


            System.out.print(result);



        } catch (SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }


        oracleConn.closeConnection(conn);

    }

















}

ITPub博客

java 调用oracle 存储过程

最新文章