二 .Java调用Oracle过程与函数
写一个计算个人所得税的应用
--定义过程 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; /
--调用过程 declare --交税 rax number; salary number := &salary; begin get_rax(salary,rax); dbms_output.put_line(salary||'元工资需要交'||rax||'元税'); end; / |
public class TestCallOracleProc { public static void main(String[] args) throws Exception{ String sql = "{call get_rax(?,?)}"; Connection conn = JdbcUtil.getConnection(); CallableStatement cstmt = conn.prepareCall(sql); cstmt.setInt(1,10000); cstmt.registerOutParameter(2,Types.INTEGER); cstmt.execute(); Integer rax = cstmt.getInt(2); System.out.println("10000元需要交" + rax + "元税"); JdbcUtil.close(cstmt); JdbcUtil.close(conn); } } |
查询7788号员工的的姓名,职位,月薪
--定义函数 create or replace function findEmpNameAndJobAndSal(pempno in number,pjob out varchar2,psal out number) return varchar2 as 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; / |
public class TestCallOracleFunc { public static void main(String[] args) throws Exception{ String sql = "{?=call findEmpNameAndJobAndSal(?,?,?)}"; Connection conn = JdbcUtil.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(); String ename = cstmt.getString(1); String job = cstmt.getString(3); Integer sal = cstmt.getInt(4); System.out.println(ename+":"+job+":"+sal); JdbcUtil.close(cstmt); JdbcUtil.close(conn); } } |