1、存储过程

1.1、准备sql

--定义存储过程
createorreplaceprocedureget_rax(salaryinnumber,raxoutnumber)
as
--需要交税的钱
balnumber;
begin
bal:=salary-3500;
ifbal<=1500then
rax:=bal*0.03-0;
elsifbal<=4500then
rax:=bal*0.1-105;
elsifbal<=9000then
rax:=bal*0.2-555;
elsifbal<=35000then
rax:=bal*0.25-1005;
elsifbal<=55000then
rax:=bal*0.3-2755;
elsifbal<=80000then
rax:=bal*0.35-5505;
else
rax:=bal*0.45-13505;
endif;
end;
/

setserveroutputon;

--调用存储过程
declare
salnumber:=&salary;
raxnumber;
begin
get_rax(sal,rax);
dbms_output.put_line(sal||'元工资应该交税'||rax||'元');
end;
/

1.2、准备JAR包

c3p0-config.xml

<c3p0-config>
<default-config>
<propertyname="jdbcUrl">jdbc:oracle:thin:@127.0.0.1:1521:orcl</property>
<propertyname="driverClass">oracle.jdbc.driver.OracleDriver</property>
<propertyname="user">scott</property>
<propertyname="password">tiger</property>
<propertyname="initialPoolSize">3</property>
<propertyname="maxPoolSize">6</property>
<propertyname="maxIdleTime">1000</property>
</default-config>
</c3p0-config>

1.3、编写工具类

JDBCUtils.java

packagecom.rk.utils;

importjava.sql.Connection;

importcom.mchange.v2.c3p0.ComboPooledDataSource;

publicclassJDBCUtils{
privatestaticComboPooledDataSourcedataSource=newComboPooledDataSource();
publicstaticConnectiongetConnection()throwsException{
returndataSource.getConnection();
}

publicstaticvoidcloseQuietly(AutoCloseableac){
if(ac!=null){
try{
ac.close();
}catch(Exceptione){
e.printStackTrace();
}
}
}
}

1.4、JDBC程序调用存储过程

CallProc.java

packagecom.rk.test;

importjava.sql.CallableStatement;
importjava.sql.Connection;
importjava.sql.Types;

importcom.rk.utils.JDBCUtils;

/**
*演示java-jdbc调用oracle过程
*/
publicclassCallProc{
publicstaticvoidmain(String[]args)throwsException{
Stringsql="{callget_rax(?,?)}";
Connectionconn=JDBCUtils.getConnection();
CallableStatementcstmt=conn.prepareCall(sql);
//为第一个?号设置值，从1开始
cstmt.setInt(1,7000);
//为第二个？注册输出类型
cstmt.registerOutParameter(2,Types.INTEGER);
//执行调用过程
cstmt.execute();
//接收过程的返回值，即第二个？号
intrax=cstmt.getInt(2);
//显示
System.out.println("7000元工资应该交税"+rax+"元");
JDBCUtils.closeQuietly(cstmt);
JDBCUtils.closeQuietly(conn);
}
}

2、存储函数

2.1、准备sql

--定义函数
createorreplacefunctionfindEmpNameAndJobAndSal(pempnoinnumber,pjoboutvarchar2,psaloutnumber)
returnvarchar2
as
penameemp.ename%type;
begin
selectename,job,salintopename,pjob,psalfromempwhereempno=pempno;
returnpename;
end;
/

--调用函数
declare
penameemp.ename%type;
pjobemp.job%type;
psalemp.sal%type;
begin
pename:=findEmpNameAndJobAndSal(7788,psal);
dbms_output.put_line('7788'||'--'||pename||'--'||pjob||'--'||psal);
end;
/

2.2、JDBC程序调用存储函数

packagecom.rk.test;

importjava.sql.CallableStatement;
importjava.sql.Connection;
importjava.sql.Types;

importcom.rk.utils.JDBCUtils;

/**
*演示java-jdbc调用oracle函数
*/
publicclassCallFunc{
publicstaticvoidmain(String[]args)throwsException{
Stringsql="{?=callfindEmpNameAndJobAndSal(?,?,?)}";
Connectionconn=JDBCUtils.getConnection();
CallableStatementcstmt=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占位符的值
Stringename=cstmt.getString(1);
Stringjob=cstmt.getString(3);
intsal=cstmt.getInt(4);
//显示
System.out.println("7788--"+ename+"--"+job+"--"+sal);
JDBCUtils.closeQuietly(cstmt);
JDBCUtils.closeQuietly(conn);
}
}