oracle存储函数和存储过程及java调用

存储过程

/*
      存储过程: 
              实际上将一段已经编译好了的PLSQL代码片断,封装在Oracle数据库
              1.提高业务逻辑执行效率
              2.将复杂的业务逻辑就相当于是封装成了一个过程
          语法:
              create [or replace] procedure 过程的名称(参数1 in|out 参数类型,参数2 in|out 参数类型)
              is|as
                  -- 声明的部分   
              begin
                  -- 业务逻辑
              end;
          in : 代表的是输入参数
          out : 代表的输出参数
          
          u = new User()
          function test(int i,User u){
            u.setUsername("zs");   输出参数是需要重新赋值的,输入参数可以不必
            syso(i)
          }         
*/
-- 给指定员工涨薪,并打印涨薪前和涨薪后的工资
/*
   员工编号?
   涨多少 ?
*/
create or replace procedure proc_updatesal(eno in number,scount in number)
is
  vsal number;
begin
  -- 查询涨薪前的工资
  select sal into vsal from emp where empno = eno;
  -- 打印涨薪前的工资
  dbms_output.put_line('涨薪前:'||vsal);
  -- 更新工资
  update emp set sal = vsal+scount where empno = eno;
  -- 打印涨薪后的工资
  dbms_output.put_line('涨薪后:'||(vsal+scount));
  commit;
end;
//在下面调用执行过程
方式1:call proc_updatessal(7369,10)//给7368涨薪10元
方式2: declare  begin proc_updatetesal   end;

存储函数

/*
     存储函数:   
           实际上将一段已经编译好了的PLSQL代码片断,封装在Oracle数据库
              1.提高业务逻辑执行效率
              2.将复杂的业务逻辑就相当于是封装成了一个函数
              
           语法:
              create [or replace] function 函数名(参数1 in|out 参数类型) return 返回值类型
              is|as            
              begin          
              end;  
           过程和函数的区别:
              1. 函数一定要有返回值,过程可以没有
              2. 函数可以直接在sql语句中使用,过程不行
              3. 过程能够的实现的功能,函数能够实现
              4. 函数能实现的功能,过程也能够实现
              5. 函数存在意义是给过程调用,在工作中通常调用的是过程
              6. 函数和过程本质上木有区别     
          输入类型 in 默认可以不写  
*/
-- 查询指定员工的年薪//存储函数/////////////////////////////////////////////////////////////
-- 员工编号 ? 
-- 返回 : 年薪
create or replace function func_getyearsal(eno number) return number
is
  -- 声明一个变量,接收年薪
  vyearsal number;     
begin
  select sal*12 + nvl(comm,0) into vyearsal from emp where empno = eno; 
  -- 返回结果
  return vyearsal;
end;
//调用函数
declare
  vsum number;
begin
  vsum := func_getyearsal(7369);
  dbms_output.put_line('年薪:'||vsum);  //输出编号是7369的年薪
end;
-- 调用统计年薪的--//存储过程/////////////////////////////////////////////////////////////////////////
-- 输入: 员工编号
-- 输出: 年薪
create or replace procedure proc_getyearsal(eno in number,vyearsal out number)
is  
begin
  -- 查询员工年薪
  select sal*12 + nvl(comm,0) into vyearsal from emp where empno = eno; 
end;
// 调用有输出参数的存储过程
declare
  vsum number;
begin
  proc_getyearsal(7369,vsum);
  dbms_output.put_line('年薪:'||vsum);
end;

java代码的调用

//接上边的存储过程和存储函数
public class TestProcedure {
    
    /*
     * create or replace procedure proc_getyearsal(eno in number,vyearsal out number)
        is  
        begin
          -- 查询员工年薪
          select sal*12 + nvl(comm,0) into vyearsal from emp where empno = eno; 
        end;
     * */
    @Test
    public void test1() throws Exception{
//        1.注册驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
//        2.创建连接
        String url ="jdbc:oracle:thin:@192.168.80.100:1521:orcl";
        Connection conn = DriverManager.getConnection(url, "xidada", "root");
//        3.获取执行SQL的对象
        String sql ="{call pro_updatesal(?,?)}";
        CallableStatement call = conn.prepareCall(sql);
//          封装参数
        call.setInt(1, 7369);
//        注册输出类型的参数
        call.registerOutParameter(2, OracleTypes.NUMBER);
//        4.执行SQL
        call.execute();  // 如果执行的是查询操作就返回true , 增删改的操作就是false
//        5.处理结果
        int sum = call.getInt(2);
        System.out.println("年薪:"+sum);
//        6.释放资源
        call.close();
        conn.close();
    }
    @Test
    /*
     * create or replace procedure proc_getemp(dno in number,vrows out sys_refcursor)
        is
               
        begin
          open vrows for select * from emp where deptno = dno;
        end;
     * */
    public void test2() throws Exception{
//        1.注册驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
//        2.创建连接
        String url ="jdbc:oracle:thin:@192.168.42.100:1521:orcl";
        Connection conn = DriverManager.getConnection(url, "xidada", "root");
//        3.获取执行SQL的对象
        String sql ="{call proc_getemp(?,?)}";
        CallableStatement call = conn.prepareCall(sql);
//        4.封装输入类型的参数
        call.setInt(1, 10);
//        5.注册输出类型的参数
        call.registerOutParameter(2, OracleTypes.CURSOR);
//        6.执行SQL
        call.execute();
//        7.处理结果
        System.out.println(call.getClass().getName());
        OracleCallableStatement call2 = (OracleCallableStatement)call;
        ResultSet rs = call2.getCursor(2);
        while(rs.next()){
            System.out.println(rs.getObject("empno"));
            System.out.println(rs.getObject("ename"));
            System.out.println(rs.getObject("job"));
            System.out.println(rs.getObject("sal"));
            System.out.println("==================================");
        }
//        8.释放资源
        rs.close();
        call2.close();
        conn.close();
    }
    
}