Oracle学习笔记(续)
接上文 Oracle学习笔记PL/SQL 数据分页
Java调用无返回值的存储过程
create table book
(
bookId number,
bookName varchar2(50),
publishHouse varchar2(50)
);
create or replace procedure pro_page
(BookId in number,BookName in varchar2,PublishHouse in varchar2)is
begin
insert into book values(BookId,BookName,PublishHouse);
end;
Java中调用存储过程(无返回值)
CallabelStatement cs = connection.prepareCall(“{call pro(?,?,?)}”);
cs.setInt(1,10);
cs.setString(2,”笑傲江湖”);
cs.setString(3,”人民出版社”);
cs.execute();
/******************************************************************************/
Java调用有返回值的存储过程
create or replace procedure pro1
(no in number,name out varchar2)is
begin
select ename into name from SCOTT.Emp where empno =no;
end;
Java中调用存储过程(有返回值)
CallabelStatement cs = connection.prepareCall(“{call pro1(?,?)}”);
cs.setInt(1,7788);
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
cs.execute();
String name = cs.getString(2);//取出返回值
/******************************************************************************/
返回结果集的存储过程
Step1.创建一个包，在包中定义类型test_cursor
create or replace package testpackage as
type test_cursor is refcursor;
end testpackage;
Step2.创建过程
create or replace procedure pro(no in number,v_cursor out testpackage.test_cursor)
begin open v_cursor for
select * from SCOTT.emp where deptno =no;
end;
Step3.在Java中调用
CallabelStatement cs = connection.prepareCall(“{call pro(?,?)}”);
cs.setInt(1,10);
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
ResultSetrs = (ResultSet)cs.getObject(2);
while(rs.next()){
}
分页过程
select t1.*,rownum rn from(select*fromSCOTT.Student) t1;--按照编号排序
select t1.*,rownum rn from(select*fromSCOTT.Student) t1 where rownum
页:
[1]