PL/SQL的进阶----编写分页过程-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

PL/SQL的进阶----编写分页过程

简介: PL/SQL的进阶----编写分页过程 介绍 分页是任何一个网站(bbs,网上商城, blog)都会使用到的技术,因此学习 pl/sql 编程开发就一定要掌握该技术。   ①无返回值的存储过程 古人云:欲速则不达。首先是掌握最简单的存储过程,无返回值的存储过程: 案例:现在有一张表,字段有“书号,书名,出版社”。请编写一个存储过程,可以向book表添加书,要求通过JAVA程序调

PL/SQL的进阶----编写分页过程

介绍

分页是任何一个网站(bbs,网上商城, blog)都会使用到的技术,因此学习 pl/sql 编程开发就一定要掌握该技术。

 

①无返回值的存储过程

古人云:欲速则不达。首先是掌握最简单的存储过程,无返回值的存储过程:

案例:现在有一张表,字段有“书号,书名,出版社”。请编写一个存储过程,可以向book表添加书,要求通过JAVA程序调用该过程。

----book表

SQL>create table book (boodId number, bookName varchar2(50), publishHouse varchar2(50));

----编写存储过程

----in:表示这是一个输入参数!不写in,默认就是in!

----out:表示是一个输出参数

create or replace procedure sp_pro7

(spBookId in number, spbookName in varchar2, sppublishHouse in varchar2) is

begin

insert into book values(spBookId, spbookName, sppublishHouse);

end;                     

 

----在JAVA中调用

package com.sp;

//调用一个无返回值的过程

import java.sql.*;

public class Test1 {

public static void main(String []args){

try{

//1.加载驱动

Class.forName("oracle.jdbc.driver.OracleDriver");

//2.得到连接

Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:MYORA1","SCOTT","m123");

//3.创建CallableStatement

CallableStatement cs=ct.prepareCall("{call sp_pro7(?,?,?)}");

//4.给?赋值

cs.setInt(1,10);

cs.setString(2,"笑傲江湖");

cs.setString(3,"人民出版社");

//5.执行

cs.execute();

}catch (Exception e){

e.printStackTrace();

}finally{

////6.关闭各种打开的资源

}

}

}

 

②.1>有返回值的存储过程(非列表)

案例:编写一个过程,可以输入雇员的编号,返回该雇员的姓名。

案例扩张:编写一个过程,可以输入雇员的编号,返回该雇员的姓名,工资和岗位。

-----有输入和输出的存储过程:

create or replace procedure sp_pro8

(spNo in varchar2, spName out varchar2) is

begin

select ename into spName from emp where empno=spNo;

end;

 

在JAVA中调用:

package com.sp;

//调用一个有返回值的过程

import java.sql.*;

public class Test1 {

public static void main(String []args){

try{

//1.加载驱动

Class.forName("oracle.jdbc.driver.OracleDriver");

//2.得到连接

Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:MYORA1","SCOTT","m123");

//3.创建CallableStatement

CallableStatement cs=ct.prepareCall("{call sp_pro8(?,?)}");

//4.1给第一个?赋值

cs.setInt(1,7788);

//4.2给第二个?赋值

cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);

//5.执行

cs.execute();

//6.取出返回值,要注意?的顺序

String name=cs.getString(2);

System.out.println("7788的名字是:"+name);

}catch (Exception e){

e.printStackTrace();

}finally{

//7.关闭各种打开的资源

}

}

}

 

若有多个返回值!

create or replace procedure sp_pro8

(spNo in varchar2, spName out varchar2, spSal out number, spJob out varchar2) is

begin

select ename,sal,job into spName,spSal,spJob from emp where empno=spNo;

end;

 

package com.sp;

//调用一个有多个返回值的过程

import java.sql.*;

public class Test1 {

public static void main(String []args){

try{

//1.加载驱动

Class.forName("oracle.jdbc.driver.OracleDriver");

//2.得到连接

Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:MYORA1","SCOTT","m123");

//3.创建CallableStatement

CallableStatement cs=ct.prepareCall("{call sp_pro8(?,?,?,?)}");

//4.1给第一个?赋值

cs.setInt(1,7788);

//4.2给第二个?赋值

cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);

//4.3给第三个?赋值

cs.registerOutParameter(3, oracle.jdbc.OracleTypes.DOUBLE);

//4.4给第四个?赋值

cs.registerOutParameter(4, oracle.jdbc.OracleTypes.VARCHAR);

//5.执行

cs.execute();

//6.取出返回值,要注意?的顺序

String name=cs.getString(2);

String job=cs.getString(4);

System.out.println("7788的名字是:"+name+" 工作:"+job);

}catch (Exception e){

e.printStackTrace();

}finally{

//7.关闭各种打开的资源

}

}

}

 

②.2>有返回值的存储过程(列表[结果集])

案例:编写一个过程,输入部门号,返回该部门所有雇员信息。

分析:由于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 sp_pro9

(spNo in number, p_cursor out testpackage.test_cursor) is

begin

open p_cursor for select * from emp where deptno=spNo;

end;

<3>在JAVA中调用

package com.sp;

//调用一个返回结果集的过程

import java.sql.*;

public class Test1 {

public static void main(String []args){

try{

//1.加载驱动

Class.forName("oracle.jdbc.driver.OracleDriver");

//2.得到连接

Connection ct=DriverManager.getConnection

("jdbc:oracle:thin:@127.0.0.1:1521:MYORA1","SCOTT","m123");

//3.创建CallableStatement

CallableStatement cs=ct.prepareCall("{call sp_pro9(?,?)}");

//4.1给第一个?赋值

cs.setInt(1,10);

//4.2给第二个?赋值(注册一个问号)

cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);

//5.执行

cs.execute();

//6.得到结果集

ResultSet rs=(ResultSet)cs.getObject(2);

while(rs.next()){

System.out.println(rs.getInt(1)+" "+rs.getString(2));

}

}catch (Exception e){

e.printStackTrace();

}finally{

//7.关闭各种打开的资源

}

}

}

 

③:编写分页过程:

要求,编写一个存储过程:要求可以输入表名,每页显示记录数,当前页。返回总记录数,总页数,和返回的结果集。

ORACLE的分页 

SQL>select t1.*, rownum rn from (select * from emp) t1    //select * from emp=>内嵌视图

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

SQL>select * from 

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

>where rn>=6;

[在分页的时候,可以将上面的SQL语句当做一个模板使用]

分页过程

---a>开发一个包

create or replace package testpackage as

type test_cursor is ref cursor;

end testpackage;

 

---b>开始编写分页过程

create or replace procedure fenye

(tableName in varchar2,

pageSize in number, ---一页显示的记录数

pageNow in number, ---当前页数

myrows out number,   ---总记录数

myPageCount out number, ---总页数

p_cursor out testpackage.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

---组织一个sql

v_sql:='select count(*) from '||tableName;

---执行一个sql,并把返回的值,赋给myrows;

execute imediate v_sql into myrows;

---计算myPageCount

if mod(myrows,pageSize)=0 then

myPageCount:=myrows/pageSize;

else

myPageCount:=myrows/pageSize+1;

end if;

---关闭游标

close p_cursor;

end;

 

--c> 在JAVA中测试!

package com.sp;

import java.sql.*;

//测试分页

public class FenYe {

public static void main(String[] args) {

try{

//1.加载驱动

Class.forName(oracle.jdbc.driver.OracleDriver");

//2.得到连接

Connection ct=Drivermanager.getConnection

("jdbc:oracle:thin:@127.0.0.1:1521:MYORA1","SCOTT","m123");

//3.创建CallableStatement

CallableStatement cs=ct.prepareCall("{call fenye(?,?,?,?,?,?}");

//4.1.给前三个输入参数的?赋值

cs.setString(1,"emp");

cs.setInt(2,5);

cs.setInt(3,1);

//4.2.给后面三个输出参数的?赋值

//4.2.1:注册总记录数

cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);

//4.2.2: 注册总页数

cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);

//4.2.3: 注册返回的结果集

cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);

//5.执行

cs.execute();

//6.取出总记录数/这儿要注意,getInt(4)中的"4"是由该参数的位置决定的

int rowNum=cs.getInt(4);

int pageCount=cs.getInt(5);

ResultSet rs=(ResultSet)cs.getObject(6);

//显示一下,阶段检测

System.out.println("rowNum="+rowNum);

System.out.println("页数:"+pageCount);

while(rs.next()){

System.out.println("编号:"+rs.getInt(1)+" 名字:"+rs.getString(2)); 

//这里面的"1"是结果集的第一列!

}

}catch (Exception e){

e.printStackTrace();

}finally{

//关闭资源

}

}

}

 

-----新的需求,要求按薪水的从低到高排序!

结果:修改一下存储过程即可,添加一个 order by sal ///默认是 asc 升序!从低到高!

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享: