程序博客网 > xbox是什么软件

pl/sql之分页

来源：互联网 发布：xbox是什么软件 编辑：程序博客网 时间：2024/05/18 16:18

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

 无返回值的存储过程
古人云：欲速则不达，为了让大家伙比较容易接受分页过程编写，我还是从简单到复杂，循序渐进的给大家讲解。首先是掌握最简单的存储过程，无返回值的存储过程：
案例：现有一张表book，表结构如下：看图：

书号 书名 出版社

请写一个过程，可以向book表添加书，要求通过java程序调用该过程

--in:表示这是一个输入参数，不写in的话默认就为in
--out:表示一个输出参数

Sql代码
  1. create or replace procedure sp_pro7(spBookId in number,spbookName in varchar2,sppublishHouse in varchar2) is  
  2. begin  
  3.     insert into book values(spBookId,spbookName,sppublishHouse);   
  4. end;   
  5. /  

--在java中调用
--在java中调用

Java代码:
[java] view plaincopyprint?
  1. //调用一个无返回值的过程   
  2. import java.sql.*;  
  3. public class Test2{  
  4.     public static void main(String[] args){  
  5.           
  6.         try{  
  7.             //1.加载驱动   
  8.             Class.forName("oracle.jdbc.driver.OracleDriver");  
  9.             //2.得到连接   
  10.             Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");  
  11.   
  12.             //3.创建CallableStatement   
  13.             CallableStatement cs = ct.prepareCall("{call sp_pro7(?,?,?)}");  
  14.             //4.给?赋值   
  15.             cs.setInt(1,10);  
  16.             cs.setString(2,"笑傲江湖");  
  17.             cs.setString(3,"人民出版社");  
  18.             //5.执行   
  19.             cs.execute();              
  20.         } catch(Exception e){  
  21.             e.printStackTrace();  
  22.         } finally{  
  23.             //6.关闭各个打开的资源   
  24.             cs.close();  
  25.             ct.close();  
  26.         }  
  27.     }  
  28. }  

执行，记录被加进去了
有返回值的存储过程（非列表）
再看如何处理有返回值的存储过程：
案例：编写一个过程，可以输入雇员的编号，返回该雇员的姓名。
案例扩张：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。

Sql代码 :
  1. --有输入和输出的存储过程   
  2. create or replace procedure sp_pro8   
  3. (spno in number, spName out varchar2) is  
  4. begin  
  5.     select ename into spName from emp where empno=spno;   
  6. end;   
  7. /  
Java代码:
  1. import java.sql.*;   
  2. public class Test2{   
  3.     public static void main(String[] args){   
  4.            
  5.         try{   
  6.             //1.加载驱动   
  7.             Class.forName("oracle.jdbc.driver.OracleDriver");   
  8.             //2.得到连接   
  9.             Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");   
  10.   
  11.             //3.创建CallableStatement   
  12.             /*CallableStatement cs = ct.prepareCall("{call sp_pro7(?,?,?)}"); 
  13.             //4.给?赋值  
  14.             cs.setInt(1,10);  
  15.             cs.setString(2,"笑傲江湖");  
  16.             cs.setString(3,"人民出版社");*/  
  17.   
  18.             //看看如何调用有返回值的过程   
  19.             //创建CallableStatement   
  20.             /*CallableStatement cs = ct.prepareCall("{call sp_pro8(?,?)}");   
  21.   
  22.             //给第一个？赋值   
  23.             cs.setInt(1,7788);   
  24.             //给第二个？赋值   
  25.             cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);   //通过这个注册的值返回来
  26.   
  27.             //5.执行   
  28.             cs.execute();      
  29.             //取出返回值,要注意？的顺序   
  30.             String name=cs.getString(2);    
  31.             System.out.println("7788的名字"+name);           
  32.         } catch(Exception e){   
  33.             e.printStackTrace();   
  34.         } finally{   
  35.             //6.关闭各个打开的资源   
  36.             cs.close();   
  37.             ct.close();   
  38.         }   
  39.     }   
  40. }  

运行，成功得出结果。。

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

Sql代码:
  1. --有输入和输出的存储过程   
  2. create or replace procedure sp_pro8   
  3. (spno in number, spName out varchar2,spSal out number,spJob out varchar2) is  
  4. begin  
  5.     select ename,sal,job into spName,spSal,spJob from emp where empno=spno;   
  6. end;   
  7. /  

JAVA代码:

  1. import java.sql.*;   
  2. public class Test2{   
  3.     public static void main(String[] args){   
  4.            
  5.         try{   
  6.             //1.加载驱动   
  7.             Class.forName("oracle.jdbc.driver.OracleDriver");   
  8.             //2.得到连接   
  9.             Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");   
  10.   
  11.             //3.创建CallableStatement   
  12.             /*CallableStatement cs = ct.prepareCall("{call sp_pro7(?,?,?)}"); 
  13.             //4.给?赋值  
  14.             cs.setInt(1,10);  
  15.             cs.setString(2,"笑傲江湖");  
  16.             cs.setString(3,"人民出版社");*/  
  17.   
  18.             //看看如何调用有返回值的过程   
  19.             //创建CallableStatement   
  20.             /*CallableStatement cs = ct.prepareCall("{call sp_pro8(?,?,?,?)}");   
  21.   
  22.             //给第一个？赋值   
  23.             cs.setInt(1,7788);   
  24.             //给第二个？赋值   
  25.             cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);   
  26.             //给第三个？赋值   
  27.             cs.registerOutParameter(3,oracle.jdbc.OracleTypes.DOUBLE);   
  28.             //给第四个？赋值   
  29.             cs.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR);   
  30.            //注意上面的那几个都应该给他们进行赋值,否则是会出错的
  31.   
  32.             //5.执行   
  33.             cs.execute();      
  34.             //取出返回值,要注意？的顺序   
  35.             String name=cs.getString(2);    
  36.             String job=cs.getString(4);   
  37.             System.out.println("7788的名字"+name+" 工作："+job);           
  38.         } catch(Exception e){   
  39.             e.printStackTrace();   
  40.         } finally{   
  41.             //6.关闭各个打开的资源   
  42.             cs.close();   
  43.             ct.close();   
  44.         }   
  45.     }   
  46. }  

运行，成功找出记录

有返回值的存储过程（列表[结果集]）
案例：编写一个过程，输入部门号，返回该部门所有雇员信息。
对该题分析如下：
  由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了。所以要分两部分：
返回结果集的过程
1.建立一个包，在该包中，我定义类型test_cursor,是个游标。 如下：

Sql代码:
  1. create or replace package testpackage as  //注意这里是as而不是is
  2.   TYPE test_cursor is ref cursor;   
  3. end testpackage; 

2.建立存储过程。如下：

Sql代码:
  1. create or replace procedure sp_pro9(spNo in number,p_cursor out testpackage.test_cursor) is  
  2. begin  
  3.   open p_cursor for  
  4.     select * from emp where deptno = spNo;   
  5. end sp_pro9;  

3.如何在java程序中调用该过程
Java代码:
  1. import java.sql.*;   
  2. public class Test2{   
  3.     public static void main(String[] args){   
  4.            
  5.         try{   
  6.             //1.加载驱动   
  7.             Class.forName("oracle.jdbc.driver.OracleDriver");   
  8.             //2.得到连接   
  9.             Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");   
  10.   
  11.             //看看如何调用有返回值的过程   
  12.             //3.创建CallableStatement   
  13.             /*CallableStatement cs = ct.prepareCall("{call sp_pro9(?,?)}");   
  14.   
  15.             //4.给第一个？赋值   
  16.             cs.setInt(1,10);   
  17.             //给第二个？赋值   
  18.             cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);   //注意这里的这个类型
  19.   
  20.             //5.执行   
  21.             cs.execute();      
  22.             //得到结果集 ,在这里取出来的时候就是结果集了
  23.             ResultSet rs=(ResultSet)cs.getObject(2);        
  24.             while(rs.next()){   
  25.                 System.out.println(rs.getInt(1)+" "+rs.getString(2));   
  26.             }    
  27.         } catch(Exception e){   
  28.             e.printStackTrace();   
  29.         } finally{   
  30.             //6.关闭各个打开的资源   
  31.             cs.close();   
  32.             ct.close();   
  33.         }   
  34.     }   
  35. }  

运行，成功得出部门号是10的所有用户

 编写分页过程
  有了上面的基础，相信大家可以完成分页存储过程了。
  要求，请大家编写一个存储过程，要求可以输入表名、每页显示记录数、当前页。返回总记录数，总页数，和返回的结果集。
如果大家忘了oracle中如何分页，请参考第三天的内容。
先自己完成，老师在后面给出答案，并讲解。
--oracle的分页
Sql代码
  1. select t1.*, rownum rn from (select * from emp) t1 where rownum<=10;   
  2. --在分页时，大家可以把下面的sql语句当做一个模板使用   
  3. select * from  
  4.   (select t1.*, rownum rn from (select * from emp) t1 where rownum<=10)   
  5. where rn>=6;  
[sql] view plaincopyprint?
  1. --开发一个包 <BR>--建立一个包，在该包中，我定义类型test_cursor,是个游标。 如下： <BR><DIV class=dp-highlighter><DIV class=bar><DIV class=tools>Sql代码 </DIV></DIV><OL class=dp-sql><LI><SPAN class=keyword>create</SPAN> <SPAN class=op><SPAN style="COLOR: #808080">or</SPAN></SPAN> <SPAN class=func><SPAN style="COLOR: #ff1493">replace</SPAN></SPAN> package testpackage <SPAN class=keyword>as</SPAN>  <LI>  TYPE test_cursor <SPAN class=keyword>is</SPAN> ref <SPAN class=keyword>cursor</SPAN>;   <LI><SPAN class=keyword>end</SPAN> testpackage;   <LI><SPAN class=comment>--开始编写分页的过程 </SPAN>  <LI><SPAN class=keyword>create</SPAN> <SPAN class=op><SPAN style="COLOR: #808080">or</SPAN></SPAN> <SPAN class=func><SPAN style="COLOR: #ff1493">replace</SPAN></SPAN> <SPAN class=keyword>procedure</SPAN> fenye   <LI>    (tableName <SPAN class=op><SPAN style="COLOR: #808080">in</SPAN></SPAN> varchar2,   <LI>     Pagesize <SPAN class=op><SPAN style="COLOR: #808080">in</SPAN></SPAN> number,<SPAN class=comment>--一页显示记录数 </SPAN>  <LI>     pageNow <SPAN class=op><SPAN style="COLOR: #808080">in</SPAN></SPAN> number,   <LI>     myrows <SPAN class=keyword>out</SPAN> number,<SPAN class=comment>--总记录数 </SPAN>  <LI>     myPageCount <SPAN class=keyword>out</SPAN> number,<SPAN class=comment>--总页数 </SPAN>  <LI>     p_cursor <SPAN class=keyword>out</SPAN> testpackage.test_cursor<SPAN class=comment>--返回的记录集 </SPAN>  <LI>    ) <SPAN class=keyword>is</SPAN>  <LI><SPAN class=comment>--定义部分 </SPAN>  <LI><SPAN class=comment>--定义sql语句 字符串 </SPAN>  <LI>v_sql varchar2(1000);   <LI><SPAN class=comment>--定义两个整数 </SPAN>  <LI>v_begin number:=(pageNow-1)*Pagesize+1;   <LI>v_end number:=pageNow*Pagesize;   <LI><SPAN class=keyword>begin</SPAN>  <LI><SPAN class=comment>--执行部分 </SPAN>  <LI>v_sql:=<SPAN class=string>'select * from (select t1.*, rownum rn from (select * from '</SPAN>||tableName||<SPAN class=string>') t1 where rownum<='</SPAN>||v_end||<SPAN class=string>') where rn>='</SPAN>||v_begin;   <LI><SPAN class=comment>--把游标和sql关联 </SPAN>  <LI><SPAN class=keyword>open</SPAN> p_cursor <SPAN class=keyword>for</SPAN> v_sql;   <LI><SPAN class=comment>--计算myrows和myPageCount </SPAN>  <LI><SPAN class=comment>--组织一个sql语句 </SPAN>  <LI>v_sql:=<SPAN class=string>'select count(*) from '</SPAN>||tableName;   <LI><SPAN class=comment>--执行sql,并把返回的值，赋给myrows； </SPAN>  <LI><SPAN class=keyword><SPAN style="COLOR: #ff0000">execute</SPAN></SPAN><SPAN style="COLOR: #ff0000"> inmediate</SPAN> v_sql <SPAN class=keyword>into</SPAN> myrows;   <LI><SPAN class=comment>--计算myPageCount </SPAN>  <LI><SPAN class=comment>--if myrows%Pagesize=0 then这样写是错的 </SPAN>  <LI>if mod(myrows,Pagesize)=0 <SPAN class=keyword>then</SPAN>  <LI>  myPageCount:=myrows/Pagesize;   <LI><SPAN class=keyword>else</SPAN>  <LI>  myPageCount:=myrows/Pagesize+1   <LI><SPAN class=keyword>end</SPAN> if;   <LI><SPAN class=comment>--关闭游标 </SPAN>  <LI><SPAN class=keyword>close</SPAN> p_cursor;   <LI><SPAN class=keyword>end</SPAN>;   <LI>/  </LI></OL></DIV><PRE style="DISPLAY: none" class=sql title=20.PL/SQL分页 name="code">--使用java测试 <BR>//测试分页 <BR><DIV class=dp-highlighter><DIV class=bar><DIV class=tools>Java代码 </DIV></DIV><OL class=dp-j><LI><SPAN><SPAN class=keyword>import</SPAN><SPAN> java.sql.*;   </SPAN></SPAN><LI><SPAN class=keyword>public</SPAN><SPAN> </SPAN><SPAN class=keyword>class</SPAN><SPAN> FenYe{   </SPAN><LI><SPAN>    </SPAN><SPAN class=keyword>public</SPAN><SPAN> </SPAN><SPAN class=keyword>static</SPAN><SPAN> </SPAN><SPAN class=keyword>void</SPAN><SPAN> main(String[] args){   </SPAN><LI><SPAN>           </SPAN><LI><SPAN>        </SPAN><SPAN class=keyword>try</SPAN><SPAN>{   </SPAN><LI><SPAN>            </SPAN><SPAN class=comment>//1.加载驱动 </SPAN><SPAN>  </SPAN><LI><SPAN>            Class.forName(</SPAN><SPAN class=string>"oracle.jdbc.driver.OracleDriver"</SPAN><SPAN>);   </SPAN><LI><SPAN>            </SPAN><SPAN class=comment>//2.得到连接 </SPAN><SPAN>  </SPAN><LI><SPAN>            Connection ct = DriverManager.getConnection(</SPAN><SPAN class=string>"jdbc:oracle:thin@127.0.0.1:1521:MYORA1"</SPAN><SPAN>,</SPAN><SPAN class=string>"scott"</SPAN><SPAN>,</SPAN><SPAN class=string>"m123"</SPAN><SPAN>);   </SPAN><LI><SPAN>  </SPAN><LI><SPAN>            </SPAN><SPAN class=comment>//3.创建CallableStatement </SPAN><SPAN>  </SPAN><LI><SPAN>            CallableStatement cs = ct.prepareCall(</SPAN><SPAN class=string>"{call fenye(?,?,?,?,?,?)}"</SPAN><SPAN>);   </SPAN><LI><SPAN>  </SPAN><LI><SPAN>            </SPAN><SPAN class=comment>//4.给第？赋值 </SPAN><SPAN>  </SPAN><LI><SPAN>            cs.seString(</SPAN><SPAN class=number><SPAN style="COLOR: #c00000">1</SPAN></SPAN><SPAN>,</SPAN><SPAN class=string>"emp"</SPAN><SPAN>);   </SPAN><LI><SPAN>            cs.setInt(</SPAN><SPAN class=number><SPAN style="COLOR: #c00000">2</SPAN></SPAN><SPAN>,</SPAN><SPAN class=number><SPAN style="COLOR: #c00000">5</SPAN></SPAN><SPAN>);   </SPAN><LI><SPAN>            cs.setInt(</SPAN><SPAN class=number><SPAN style="COLOR: #c00000">3</SPAN></SPAN><SPAN>,</SPAN><SPAN class=number><SPAN style="COLOR: #c00000">2</SPAN></SPAN><SPAN>);   </SPAN><LI><SPAN>  </SPAN><LI><SPAN>            </SPAN><SPAN class=comment>//注册总记录数 </SPAN><SPAN>  </SPAN><LI><SPAN>            cs.registerOutParameter(</SPAN><SPAN class=number><SPAN style="COLOR: #c00000">4</SPAN></SPAN><SPAN>,oracle.jdbc.OracleTypes.INTEGER);   </SPAN><LI><SPAN>            </SPAN><SPAN class=comment>//注册总页数 </SPAN><SPAN>  </SPAN><LI><SPAN>            cs.registerOutParameter(</SPAN><SPAN class=number><SPAN style="COLOR: #c00000">5</SPAN></SPAN><SPAN>,oracle.jdbc.OracleTypes.INTEGER);   </SPAN><LI><SPAN>            </SPAN><SPAN class=comment>//注册返回的结果集 </SPAN><SPAN>  </SPAN><LI><SPAN>            cs.registerOutParameter(</SPAN><SPAN class=number><SPAN style="COLOR: #c00000">6</SPAN></SPAN><SPAN>,oracle.jdbc.OracleTypes.CURSOR);   </SPAN><LI><SPAN>  </SPAN><LI><SPAN>            </SPAN><SPAN class=comment>//5.执行 </SPAN><SPAN>  </SPAN><LI><SPAN>            cs.execute();    </SPAN><LI><SPAN>  </SPAN><LI><SPAN>            </SPAN><SPAN class=comment>//取出总记录数   /这里要注意，getInt(4)中4，是由该参数的位置决定的 </SPAN><SPAN>  </SPAN><LI><SPAN>            </SPAN><SPAN class=keyword>int</SPAN><SPAN> rowNum=cs.getInt(</SPAN><SPAN class=number><SPAN style="COLOR: #c00000">4</SPAN></SPAN><SPAN>);   </SPAN><LI><SPAN>  </SPAN><LI><SPAN>            </SPAN><SPAN class=keyword>int</SPAN><SPAN> pageCount = cs.getInt(</SPAN><SPAN class=number><SPAN style="COLOR: #c00000">5</SPAN></SPAN><SPAN>);               </SPAN><LI><SPAN>            ResultSet rs=(ResultSet)cs.getObject(</SPAN><SPAN class=number><SPAN style="COLOR: #c00000">6</SPAN></SPAN><SPAN>);    </SPAN><LI><SPAN>  </SPAN><LI><SPAN>            </SPAN><SPAN class=comment>//显示一下，看看对不对   </SPAN><SPAN>  </SPAN><LI><SPAN>            System.out.println(</SPAN><SPAN class=string>"rowNum="</SPAN><SPAN>+rowNum);   </SPAN><LI><SPAN>            System.out.println(</SPAN><SPAN class=string>"总页数="</SPAN><SPAN>+pageCount);   </SPAN><LI><SPAN>     </SPAN><LI><SPAN>            </SPAN><SPAN class=keyword>while</SPAN><SPAN>(rs.next()){   </SPAN><LI><SPAN>                System.out.println(</SPAN><SPAN class=string>"编号："</SPAN><SPAN>+rs.getInt(</SPAN><SPAN class=number><SPAN style="COLOR: #c00000">1</SPAN></SPAN><SPAN>)+</SPAN><SPAN class=string>" 名字："</SPAN><SPAN>+rs.getString(</SPAN><SPAN class=number><SPAN style="COLOR: #c00000">2</SPAN></SPAN><SPAN>)+</SPAN><SPAN class=string>" 工资："</SPAN><SPAN>+rs.getFloat(</SPAN><SPAN class=number><SPAN style="COLOR: #c00000">6</SPAN></SPAN><SPAN>));   </SPAN><LI><SPAN>            }    </SPAN><LI><SPAN>        } </SPAN><SPAN class=keyword>catch</SPAN><SPAN>(Exception e){   </SPAN><LI><SPAN>            e.printStackTrace();   </SPAN><LI><SPAN>        } </SPAN><SPAN class=keyword>finally</SPAN><SPAN>{   </SPAN><LI><SPAN>            </SPAN><SPAN class=comment>//6.关闭各个打开的资源 </SPAN><SPAN>  </SPAN><LI><SPAN>            cs.close();   </SPAN><LI><SPAN>            ct.close();   </SPAN><LI><SPAN>        }   </SPAN><LI><SPAN>    }   </SPAN><LI><SPAN>}  </SPAN></LI></OL></DIV></PRE>  
  2. <BR>运行，控制台输出： <BR>rowNum=19 <BR>总页数：4 <BR>编号：7369 名字：SMITH 工资：2850.0 <BR>编号：7499 名字：ALLEN 工资：2450.0 <BR>编号：7521 名字：WARD 工资：1562.0 <BR>编号：7566 名字：JONES 工资：7200.0 <BR>编号：7654 名字：MARTIN 工资：1500.0 <BR>  

--新的需要，要求按照薪水从低到高排序，然后取出6-10
过程的执行部分做下改动，如下：
Sql代码
  1. begin  
  2. --执行部分   
  3. v_sql:='select * from (select t1.*, rownum rn from (select * from '||tableName||' order by sal) t1 where rownum<='||v_end||') where rn>='||v_begin;  

重新执行一次procedure,java不用改变，运行，控制台输出：
rowNum=19
总页数：4
编号：7900 名字：JAMES 工资：950.0
编号：7876 名字：ADAMS 工资：1100.0
编号：7521 名字：WARD 工资：1250.0
编号：7654 名字：MARTIN 工资：1250.0
编号：7934 名字：MILLER 工资：1300.0

xbox是什么软件 xbox是什么软件
原创粉丝点击
热门IT博客
mac电量消耗变快mac电量消耗变快
数据采集监控系统数据采集监控系统
keep健身软件下载
js代码封装的好处
美食软件
编译原理 算法导论
php curl 超时设置
quicktime是什么软件
手机简单游戏推荐 知乎
软件更新说明
js array splice 添加
嘉靖 万历 知乎
oracle 去掉重复数据
linux last reboot
课表软件哪个好
python爬虫有什么用
南京人知乎
扫描服务器开放的端口
免费淘宝推广软件
常见的java面试题
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 电信电话卡怎么查话费 电信手机卡怎么查话费 电信小灵通怎么查话费 查询机主 机主查询 查询本机ip 机要查询 本机ip查询地址 linux查询本机ip 查询本机ip命令 本机ip查询工具 本机公网ip地址查询 atm机可以查询交易明细吗 42寸触摸查询一体机 柏帆净水机价格查询 甩脂机价格查询 触摸查询一体机价格 信息查询机 自助查询终端机 备机查询 如何查本机ip 查打印机ip 如何查打印机ip地址 如何查本机的ip imei码查询 梦码查询 vin码查询车辆信息 imei码在线查询 汽车vin码查询 手机串码查询 汽车vin码在线查询 车辆识别码查询中心 车辆识别码查询 耐克防伪码查询中心 pin2码怎么查询 dw手表防伪码查询网站 完美防伪码查询系统 取件码查询 中学标识码查询入口 商品码查询 puk码查询

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里