oracle存储过程学习（二）

       最近在学习oracle存储过程，在渐渐的学习过程中，慢慢也了解了为什么项目中要用存储过程？

             当你建立完存储过程之后，在数据库已经编译完，然后后台代码调用存储过程就可以了，这样做可以提高效率。如果你在代码中写sql代码，1）耗费编译时间2）维护繁琐3）不容易复用。

        存储过程分为三部分。

           1）参数部分：此部分有三个关键字 in，out，in out 。in为输入参数，out为输出参数，in out既可以做输入也可以做输出参数，为引用传递。

           2）变量声明部分：在as / is 之后，这部分定义需要知名数据类型以及长度。1）中只需要知名参数类型即可。

           3）过程块，这部分就是最重要的了。具体根据业务逻辑来做处理。

           4）异常块。

           5）结束，由end关键字。

----------下面是几个demo，可以进一步了解下存储过程。（写存储过程一定要细心，比如输入输出参数的类型要和涉及表中的字段类型一致）

---------------分页存储

--建立结果集游标
    create or replace package myPage is
    TYPE my_cursor is ref cursor;
    end myPage;

--分页存储

          create or replace procedure mypagePro
(
--需要分页的表
tablename in varchar2,
--一页显示的条数
pageSize in number,
--第几页
pageNow in number,
--总记录
myRows out number,
--总的页数
myPageCount out number,
--遍历结果集游标
p_cursor out myPage.my_cursor
)
is
/*变量声明快*/
v_sql varchar2(100);
v_begin number:=(pageNow-1)*pageSize+1;
v_end number:=pageSize*pageNow;
begin
/*过程语句块*/
v_sql:='select * from (select t1.*,rownum rw from '||tablename||' t1 where rownum <='||v_end||') where rw>='||v_begin;

open p_cursor for v_sql;

--计算myrows 和myPageCount
v_sql:='select count(*) from '||tablename;
execute immediate v_sql into myrows;
if mod(myRows,pageSize)=0 then
myPageCount:=myRows/pageSize;
else
myPageCount:=1+myRows/pageSize;
end if ;
--关闭游标 此处如果注释了，在java代码会报错，说游标已关闭
--close p_cursor;
end mypagePro;

--有多个返回值的存储过程 注意out参数的类型一定要和表中的类型一致
create or replace procedure findColumns
(empno in number,
 ename out varchar2,
 dname out number,
 job out varchar2,
 sal out number
)
is
begin
select e.first_name,e.department_id,e.job_id,e.salary into ename,dname,job,sal from employees e where e.employee_id = empno;
end findColumns;

create table book(
    bookId number ,
    bookName varchar2(50),
    publishHous varchar2(50));
    
--建插入的存储过程 不写in的话，默认为in 存储过程中一般都会出现哪些dml语句？
create or replace procedure addRecordBook
(bookId in number,bookName in varchar2,publishHous varchar2 )
is
begin
insert into book values(bookId,bookName,publishHous);
end addRecordBook;


--有返回值的存储过程
create or replace procedure findDname
(empno in number,dname out varchar2)
is
begin
select e.department_id into dname  from employees e where e.employee_id = empno;
end findDname;

-------------------------------java调用部分

/**
* 调用新增记录的存储过程
*/
public static void callInsertProcedure()
{
try {
CallableStatement call = conn.prepareCall("{call addRecordBook (?,?,?)}");
call.setInt(1, 10);
call.setString(2, "java");
call.setString(3, "version");
call.execute();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

/**
* 调用带有返回值的存储过程
*/
public static void callReturnProcedure()
{
try {
CallableStatement call = conn.prepareCall("{ call findDname (?,?)}");
call.setInt(1, 100);
//对返回值需要注册oracle中的数据类型
call.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
call.execute();

System.out.println(call.getString(2));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 调用有多个返回值的存储过程
*/
public static void callReturnColumns()
{
try {
CallableStatement call = conn.prepareCall("{call findColumns(?,?,?,?,?)}");

call.setInt(1, 100);

//注册返回值类型
call.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
call.registerOutParameter(3, oracle.jdbc.OracleTypes.NUMBER);
call.registerOutParameter(4, oracle.jdbc.OracleTypes.VARCHAR);
call.registerOutParameter(5, oracle.jdbc.OracleTypes.NUMBER);

call.execute();
System.out.println(call.getString(2)+" ，"+call.getString(3)+" ，"+call.getString(4)+" ，"+call.getString(5));

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}
/**
* 调用分页存储过程
* 
*/
public static void callPageProcedure()
{
try {
CallableStatement call = conn.prepareCall("{call mypagePro (?,?,?,?,?,?)}");
//查询表
call.setString(1, "employees");
//每页显示条数
call.setInt(2, 10);
//第二页
call.setInt(3, 2);
//总记录数
call.registerOutParameter(4, oracle.jdbc.OracleTypes.NUMBER);
//总页数
call.registerOutParameter(5, oracle.jdbc.OracleTypes.NUMBER);
//遍历结果集
call.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);

call.execute();

System.out.println("总记录数："+call.getInt(4)+"总页数："+call.getInt(5));
System.out.println("-----遍历-----");
ResultSet rs = (ResultSet) call.getObject(6);
while(rs.next())
{
System.out.println(rs.getInt(1)+"        "+rs.getString(2));
}

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}