Oracle Learning Notes (continued)

Follow the Oracle study notes above

PL/SQL data paging

Java calls a stored procedure with no return value

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) isbegin insert into book values (BookId,BookName,PublishHouse); end

Calling stored procedures in Java (no return value)

CallabelStatement cs = connection.prepareCall ("{call pro (,)}"); cs.setInt (1, 10); cs.setString (2, "The Smiling、Proud Wanderer"); cs.setString (3, "people's Publishing House"); cs.execute ()

/ * /

Java calls a stored procedure with a return value

Create or replace procedure pro1 (no in number,name out varchar2) isbegin select ename into name from SCOTT.Emp where empno = no;end

Call stored procedure in Java (with return value)

CallabelStatement cs = connection.prepareCall ("{call pro1 (,)}"); cs.setInt (1jjdbc.OracleTypes.VARCHAR); cs.execute (); String name = cs.getString (2); / / fetch the return value

/ * /

A stored procedure that returns a result set

Step1. Create a package that defines the type test_cursor in the package

Create or replace package testpackage astype test_cursor is refcursor;end testpackage

Step2. Creation process

Create or replace procedure pro (no in number,v_cursor out testpackage.test_cursor) begin open v_cursor forselect * from SCOTT.emp where deptno = no;end

Step3. Call in Java

CallabelStatement cs = connection.prepareCall ("{call pro (,)}"); cs.setInt (1d10); cs.registerOutParameter (2recorder oracle.jdbc.OracleTypes.CURSOR); cs.execute (); ResultSetrs = (ResultSet) cs.getObject (2); while (rs.next ()) {}

Paging process

Select t1.century select rn from (select*fromSCOTT.Student) T1 where rownum-sort by numbering Rownum rn from (select*fromSCOTT.Student) T1 where rownum