Java调用Oracle过程与函数

二 .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);     } }