Oracle存储过程及其java程序调用

本文地址：http://blog.csdn.net/shanglianlm/article/details/76788280

• 存储过程和存储函数是指存储在数据库中的供用户程序调用的子程序。
• 两者间区别在于存储函数可以return 值。
• 如果只有一个返回值，选择存储函数，否则使用存储过程。

1 存储过程

1-1 创建语法

create [or replace] procedure 过程名（参数列表）
as
pl/sql子程序体；
-- as 相当于 declare 申明变量

1-2 示例

根据人员类型和人员编号获取人员姓名及其生日

create or replace procedure getXmAndCsrq(rylx in varchar2, rybh in varchar2,xm out varchar2,csrq out date) is
begin
  select xm,csrq into xm,csrq from bas_employee where rylx=rylx and rybh=rybh and rownum <=1; 
end getXmAndCsrq;

rownum <=1 可能含有多条数据

1-3 调用

    //测试存储过程
    @Test
    public void testProdecure(){
        String sql = "{call getXmAndCsrq(?,?,?,?)}";
        Connection conn = null;
        CallableStatement cst = null;
        ResultSet rs = null;
        try {
            conn = DbUtils.getConnection();
            cst = conn.prepareCall(sql);
            cst.setString(1, "1");
            cst.setString(2, "seg179");
            cst.registerOutParameter(3, OracleTypes.VARCHAR);
            cst.registerOutParameter(4, OracleTypes.VARCHAR);
            cst.execute();

            String xm = cst.getString(3);
            String csrq = cst.getString(4);
            System.out.println("姓名："+xm+",出生日期："+csrq);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DbUtils.release(conn, cst, rs);
        }
    }

2 存储函数

2-1 创建语法

create [or replace] function 函数名（参数列表）
return 函数值类型
as
pl/sql子程序体；
-- as 相当于 declare 申明变量

2-2 示例

获取成员中最大的年龄

create or replace function getMaxAge(bmdm in varchar2) 
 return  varchar2
 is
 mincsrq bas_employee.csrq%type;
begin
  select min(csrq) into mincsrq from bas_employee;
  return (sysdate-mincsrq)/365;
end getMaxAge;

返回值包含小数点后若干位。

2-3 调用

//测试函数
    @Test
    public void testFunction(){
        String sql = "{?=call getMaxAge(?)}";
        Connection conn = null;
        CallableStatement cst = null;
        ResultSet rs = null;
        try {
            conn = DbUtils.getConnection();
            cst = conn.prepareCall(sql);
            cst.registerOutParameter(1, OracleTypes.VARCHAR);
            cst.setString(2, "610581002400");
            cst.execute();

            String maxAge = cst.getString(1);
            maxAge = String.valueOf(Math.ceil(Double.valueOf(maxAge)));
            System.out.println("最大年龄为："+maxAge);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DbUtils.release(conn, cst, rs);
        }
    }

3 游标

3-1 创建语法

包

CREATE OR REPLACE PACKAGEpackage_name /*包头名称*/
IS|AS pl/sql_package_spec         /*定义过程,函数以及返回类型,变量,常量及数据类型定义*/

包体

 CREATE OR REPLACE PACKAGE BODY package_name/*包名必须与包头的包名一致*/
 IS | AS pl/sql_package_body    /*游标,函数,过程的具体定义*/

3-2 示例

包

create or replace package mypackage is

  -- Author  : ADMINISTRATOR
  -- Created : 2017/8/6 18:22:18
  -- Purpose : 

  -- Public type declarations
  type empcursor is ref cursor;

  -- Public function and procedure declarations
  procedure getAllperson(bmdm in varchar2,empList out empcursor);

end mypackage;

包体

create or replace package body mypackage is

    -- Public function and procedure declarations
  procedure getAllperson(bmdm in varchar2,empList out empcursor) as 
  begin
    open empList for select * from bas_employee where bmdm = bmdm;
  end getAllperson;
end mypackage;

3-3 调用

//测试游标
    @Test
    public void testCursor(){
        String sql = "{call mypackage.getAllperson(?,?)}";
        Connection conn = null;
        CallableStatement cst = null;
        ResultSet rs = null;
        try {
            conn = DbUtils.getConnection();
            cst = conn.prepareCall(sql);
            cst.setString(1, "610527002200");
            cst.registerOutParameter(2, OracleTypes.CURSOR);
            cst.execute();

            rs = ((OracleCallableStatement) cst).getCursor(2);
            while(rs.next()&&rs.getRow()<=5){
                String xm = rs.getString("xm");
                String rylx = rs.getString("rylx");
                String rybh = rs.getString("rybh");
                System.out.println("人员类型："+rylx+",人员编号："+rybh+",姓名："+xm);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DbUtils.release(conn, cst, rs);
        }
    }

4 代码

连接数据库类 DbUtils.java

public class DbUtils {

    //驱动
    private static String driver = "oracle.jdbc.OracleDriver";
    //数据库地址
    private static String url= "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
    //用户名
    private static String user="rm";
    //密码
    private static String password="rmadmin";

    //注册数据库驱动
    static{
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    //数据库连接
    public static Connection getConnection(){
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return conn;
    }

    //释放
    public static void release(Connection conn,Statement st,ResultSet rs){
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

        if(st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

}