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);

    }

}