Oracle存储过程及调用(2)-木庄网络博客

要求：编写一个根据部门编号查找部门所有员工信息的存储过程，并用java调用该存储过程

/*定义游标*/
create package my_package as
type emp_cursor is ref cursor;
end my_package;
/*存储过程*/
create procedure procedure_7(v_deptno in emp.deptno%type,emp_cursor out my_package.emp_cursor)
is
begin
    open emp_cursor for select * from emp where deptno=v_deptno;
end;
//java调用存储过程
public static void main(String[] args) {
  Connection conn=null;
  CallableStatement cs=null;
  ResultSet rs=null;
  try {
    Class.forName("oracle.jdbc.OracleDriver");
    conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.01:1521:orcl", "scott","tiger");
    cs=conn.prepareCall("{call procedure_7(?,?)}");
    cs.setInt(1, 20);//给输入参数赋值
    cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR );//指定输出参数的数据类型
    cs.execute();
    rs=(ResultSet) cs.getObject(2);//获取输出参数的值
    while(rs.next()){
      //顺序为数据库中字段前后顺序，例如数据库emp表中第5列为hiredate，数据类型为Date，所以获取第5列值时就应该用rs.getDate(5)
      System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getDate(5));
    }
  } catch (Exception e) {
    e.printStackTrace();
  }finally{
    closeResource(conn,cs,rs);//关闭资源
  }  
}

/*以下就是20号部门所有员工的信息，这里为方便我们只打印了编号、姓名和入职时间

运行结果，控制台打印：*/

7369 SMITH 1980-12-17
7566 JONES 1981-04-02
7788 SCOTT 1987-04-19
7876 ADAMS 1987-05-23
7902 FORD 1981-12-03

这是上面java调用存储过程代码中关闭资源方法的代码

public static void closeResource(Connection conn,CallableStatement cs,ResultSet rs){
    if(rs!=null){
      try {
        rs.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
    if(cs!=null){
      try {
        cs.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
    if(conn!=null){
      try {
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }

最后给个应用，分页的存储过程

分页存储过程：

/*定义游标*/
create package page_package as
type page_cursor is ref cursor;
end page_package;
/*存储过程*/
create procedure pro_paging (
    v_page_size in number,--每页显示多少条
    v_page_count out number,--总页数
    v_current_page in number,--当前页
    v_total_count out number,--记录总条数
    emp_cursor out page_package.page_cursor--返回查询结果集的游标
    )
is
    v_begin number(5):=v_page_size*(v_current_page-1)+1;--查询起始位置
    v_end number(5):=v_page_size*v_current_page;--查询结束位置
    v_sql varchar2(1000):='select empno,ename from
       (select a.empno,a.ename,rownum rn from 
           (select empno,ename from emp) a 
       where rownum<='|| v_end ||') b 
    where b.rn>='||v_begin;
    /*不能像下面这么写，不然调用该存储过程时会报类型不一致的错，因为最里面查的只有empno,ename，因此外面也要和里面保持一致
    v_sql varchar2(1000):=\'select * from
       (select a.*,rownum rn from 
           (select empno,ename from emp) a 
       where rownum<=\'|| v_end ||\') b 
    where b.rn>='||v_begin;*/
    v_ename varchar2(10);
    v_empno number(4);
begin
    open emp_cursor for v_sql;
    loop
     fetch emp_cursor into v_empno,v_ename;
     exit when emp_cursor%notfound;
     dbms_output.put_line(v_empno||' '||v_ename);
    end loop;
    v_sql:='select count(empno) from emp';
    execute immediate v_sql into v_total_count;
    if(mod(v_total_count,v_page_size)=0) then
       v_page_count:=v_total_count/v_page_size;
    else
       v_page_count:=trunc(v_total_count/v_page_size)+1;
    end if;
    dbms_output.put_line('共 '||v_total_count||' 条记录');
    dbms_output.put_line('共 '||v_page_count||' 页');
    dbms_output.put_line('当前页： '||v_current_page);
    dbms_output.put_line('每页显示 '||v_page_size||' 条');
end;

Java调用的话和上面java调用存储过程的例子一样。这里为了方便，就直接在pl/sql中调用了

/*调用分页存储过程*/
declare
   v_page_count number(5);
   v_cursor page_package.page_cursor;
   v_total_count number(5);
begin
   dbms_output.put_line('第一页数据。。。。。。。。。');
   pro_paging(5,--每页显示5条
   v_page_count,--总页数
   1,--当前页
   v_total_count,--记录总条数
   v_cursor--游标
   );
   dbms_output.put_line('--------------------------');
   dbms_output.put_line('第二页数据。。。。。。。。。');
   --显示第二页数据
   pro_paging(5,--每页显示5条
   v_page_count,--总页数
   2,--当前页
   v_total_count,--记录总条数
   v_cursor--游标
   );
end;
/*运行结果：*/
第一页数据。。。。。。。。。
6666 张三
20 empSu2
19 empSave2
7369 SMITH
7499 ALLEN
共 17 条记录
共 4 页
当前页： 1
每页显示 5 条
--------------------------
第二页数据。。。。。。。。。
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
共 17 条记录
共 4 页
当前页： 2
每页显示 5 条

以上所述是小编给大家介绍的Oracle存储过程及调用，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对网站的支持！

标签：Oracle

返回前面的内容