Oracle系列：（33）JDBC访问Oracle的存储过程和存储函数

1、存储过程

1.1、准备SQL

-- 定义存储过程create or replace procedure get_rax(salary in number,rax out number)as    --需要交税的钱    bal number;begin    bal := salary - 3500;    if bal<=1500 then       rax := bal * 0.03 - 0;    elsif bal<=4500 then       rax := bal * 0.1 - 105;    elsif bal<=9000 then       rax := bal * 0.2 - 555;    elsif bal<=35000 then       rax := bal * 0.25 - 1005;    elsif bal<=55000 then       rax := bal * 0.3 - 2755;    elsif bal<=80000 then       rax := bal * 0.35 - 5505;    else       rax := bal * 0.45 - 13505;    end if;end;/set serveroutput on;-- 调用存储过程declare    sal number := &salary;    rax number;begin    get_rax(sal,rax);    dbms_output.put_line(sal || '元工资应该交税' || rax || '元');end;/

1.2、准备JAR包

c3p0-config.xml

            jdbc:oracle:thin:@127.0.0.1:1521:orcl        oracle.jdbc.driver.OracleDriver        scott        tiger        3        6        1000    

1.3、编写工具类

JDBCUtils.java

package com.rk.utils;import java.sql.Connection;import com.mchange.v2.c3p0.ComboPooledDataSource;public class JDBCUtils {    private static ComboPooledDataSource dataSource = new ComboPooledDataSource();    public static Connection getConnection() throws Exception{        return dataSource.getConnection();    }        public static void closeQuietly(AutoCloseable ac){        if(ac != null){            try {                ac.close();            } catch (Exception e) {                e.printStackTrace();            }        }    }}

1.4、JDBC程序调用存储过程

CallProc.java

package com.rk.test;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.Types;import com.rk.utils.JDBCUtils;/** * 演示java-jdbc调用oracle过程 */public class CallProc {    public static void main(String[] args) throws Exception{        String sql = "{call  get_rax(?,?)}";        Connection conn = JDBCUtils.getConnection();        CallableStatement cstmt = conn.prepareCall(sql);        //为第一个?号设置值，从1开始        cstmt.setInt(1, 7000);        //为第二个？注册输出类型        cstmt.registerOutParameter(2, Types.INTEGER);        //执行调用过程        cstmt.execute();        //接收过程的返回值，即第二个？号        int rax = cstmt.getInt(2);        //显示        System.out.println("7000元工资应该交税"+rax+"元");        JDBCUtils.closeQuietly(cstmt);        JDBCUtils.closeQuietly(conn);    }}

2、存储函数

2.1、准备SQL

--定义函数create or replace function findEmpNameAndJobAndSal(pempno in number,pjob out varchar2,psal out number) return varchar2as    pename emp.ename%type;begin    select ename,job,sal into pename,pjob,psal from emp where empno = pempno;    return pename;end;/--调用函数declare    pename emp.ename%type;    pjob   emp.job%type;    psal   emp.sal%type;begin    pename := findEmpNameAndJobAndSal(7788,pjob,psal);    dbms_output.put_line('7788'||'--'||pename||'--'||pjob||'--'||psal);end;/

2.2、JDBC程序调用存储函数

package com.rk.test;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.Types;import com.rk.utils.JDBCUtils;/** * 演示java-jdbc调用oracle函数 */public class CallFunc {    public static void main(String[] args) throws Exception {        String sql = "{? = call findEmpNameAndJobAndSal(?,?,?)}";        Connection conn = JDBCUtils.getConnection();        CallableStatement cstmt = conn.prepareCall(sql);                //为第一个?注册输出类型        cstmt.registerOutParameter(1, Types.VARCHAR);        //为第二个?注入值        cstmt.setInt(2, 7788);        //为第三个?注册输出类型        cstmt.registerOutParameter(3, Types.VARCHAR);        //为第四个?注册输出类型        cstmt.registerOutParameter(4, Types.INTEGER);                //执行函数调用        cstmt.execute();                //分别获取1,3,4占位符的值        String ename = cstmt.getString(1);        String job = cstmt.getString(3);        int sal = cstmt.getInt(4);        //显示        System.out.println("7788--"+ename+"--"+job+"--"+sal);        JDBCUtils.closeQuietly(cstmt);        JDBCUtils.closeQuietly(conn);    }}