oracle顺序控制语句goto、null和分页过程中输入输出存储、java程序的调用过程

顺序控制语句
1 goto建议不要使用

declare
i number:=1;
begin
loop
dbms_output.put_line(i);
if i=10 then 
goto end_loop;
end if;
i:=i+1;
end loop;
<<end_loop>>
dbms_output.put_line('循环结束')；
end;

 

2 null语句
null语句不会执行任何操作，提高程序的可读性

......
if ..then 
...
else
null
end if;
......

 

分页过程
分页是任何一个网站都会使用到的技术

--建一个book表

create table book(
bookId number,
bookName varchar2(50),
publishHouse varchar2(50));

--编写输入过程
--in：表示这是一个输入参数，默认为in
--out：表示一个输出参数
create or replace mypro(
spBookId in number, spbookName in varchar2, sppublishHouse in varchar2) is 
begin
insert into book values(spBookId,spbookName,sppublishHouse);
end;

--编写输入和输出过程
create or replace mypro1(
spno in number, spbookName out varchar2) is 
begin
select ename into spbookName from emp where empno=spno;
end;

对应的java程序：
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521","scott","m123");
CallableStatement cs = ct.prepareCall("{call mypro1(?,?)}");
cs.setInt(1,7788);
cs.registerOutParameter(2,oracle.jdbc.oracleTypes.VARCHAR);
cs.execute();
String name=cs.getString(2);
System.out.println(name);

 

--编写一个过程，输入部门编号，返回该部门的结果集
由于oracle存储过程没有返回值，它的返回值都是通过out参数代替，列表也不例外，但是由于是集合，所以不能用一般的参数，必须要用package了，所以要分两部门：

--1.创建一个包，在该包中，定义类型test_cursor
create or replace package testpackage as 
type test_cursor is ref cursor;
end testpackage;

--2.建立存储过程
create or replace procedure mypro2(spNo in number, p_cursor out testpackage.test_cursor) is
begin 
open p_cursor for select * from emp where deptno=spNo;
end;

--3.如何在java中调用过程
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521","scott","m123");
CallableStatement cs = ct.prepareCall("{call mypro2(?,?)}");
cs.setInt(1,10);
cs.registerOutParameter(2,oracle.jdbc.oracleTypes.CURSOR);
cs.execute();
ResultSet rs=(ResultSet)cs.getObject(2);
while(re.next()){
System.out.println(rs.getInt(1)+" "+rs.getString(2));
}

 分页案例

oracle的分页

select t1.* rownum rn from (select * from emp)  t1;

select t1.* rownum rn from (select * from emp)  t1 where rownum<=10;

select * from (select t1.* rownum rn from (select * from emp)  t1 where rownum<=10) where rn>=6;

在分页时，可以把上面的sql语句当做一个模板使用

编写分页的过程
--开发一个包
create or replace package tespackage as
type test_cursor is ref cursor;
end tespackage

--编写分页过程
create or replace procedure fenye 
(tableName in varchar2, 
pageSize in number, 
pageNow in numuber, 
myRows out number,
myPageCount out number,
p_cursor out tespackage.test_cursor --返回的记录集
) is 
--定义sql语句，字符串
v_sql varchar2(1000);
v_begin number:= (pageNow-1)*pageSize+1;
v_end number:= pageNow*pageSize;
begin 
v_sql := 'select * from (select t1.*, rownum rn from (select * from '||tableName||' t1 where 

rownum<='||v_end||') where rn >='||v_begin';

--把游标和sql关联
open p_cursor for v_sql;


--计算myrows和mypagecount
v_sql := 'select count(*) from '||tableName;
execute immediate v_sql into myrows; --执行sql语句并把返回的值赋给myrows；

--计算myPageCount
if mod(myrows,pageSize) = 0 then myPageCount := myrows/Pagesize ;
else myPageCount := myrows/Pagesize + 1;
end if;
close p_cursor;
end;