- 浏览: 502697 次
- 性别:
- 来自: 惠州
文章分类
- 全部博客 (255)
- ant (1)
- springMVC (2)
- ajax (4)
- oracle (12)
- SSH (13)
- struts1 (2)
- Hibernate (14)
- spring (5)
- jstl (1)
- 连接池 (1)
- acegi (4)
- java (17)
- jquery (11)
- div+css (4)
- drupal (1)
- php (8)
- freemaker调模板生成静态页面 (1)
- xml (1)
- json (2)
- javascript (9)
- 正则表达式 (4)
- Ext (8)
- jdbc (1)
- sql server (2)
- perl (5)
- db4o (1)
- webservice (4)
- flex (13)
- it资讯 (1)
- joomla (0)
- 设计模式 (1)
- struts2 (4)
- s2sh (8)
- linux (3)
- ejb (2)
- android旅途 (24)
- android (36)
- C/C++ (16)
- mysql (1)
最新评论
-
fengyuxing168:
IBelyService bs = IBelyService. ...
为 Android 添加 Java 层服务也就是添加自定义的aidl服务到serviceManager 通过ServiceManager.getService取 -
dengzhangtao:
"由于ActivityManagerService是 ...
binder理解 -
yzyspy:
ActivityManagerService:startHom ...
Android的Launcher成为系统中第一个启动的,也是唯一的 -
Matchstick:
使用SELECT DISTINCT alias FROM Po ...
hibernate 一对多表查询时fetchMode.join 生成left outer join 出来数据重复问题 -
dlheart:
没看懂你什么意思啊,我遇到的问题是一对多,设了fetch = ...
hibernate 一对多表查询时fetchMode.join 生成left outer join 出来数据重复问题
20.PL/SQL分页
文章分类:数据库
编写分页过程
介绍
分页是任何一个网站(bbs,网上商城,blog)都会使用到的技术,因此学习pl/sql编程开发就一定要掌握该技术。看图:
无返回值的存储过程
古人云:欲速则不达,为了让大家伙比较容易接受分页过程编写,我还是从简单到复杂,循序渐进的给大家讲解。首先是掌握最简单的存储过程,无返回值的存储过程:
案例:现有一张表book,表结构如下:看图:
书号 书名 出版社
请写一个过程,可以向book表添加书,要求通过java程序调用该过程。
--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./
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中调用
Java代码
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.}
//调用一个无返回值的过程
import java.sql.*;
public class Test2{
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.关闭各个打开的资源
cs.close();
ct.close();
}
}
}执行,记录被加进去了
有返回值的存储过程(非列表)
再看如何处理有返回值的存储过程:
案例:编写一个过程,可以输入雇员的编号,返回该雇员的姓名。
案例扩张:编写一个过程,可以输入雇员的编号,返回该雇员的姓名、工资和岗位。
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./
--有输入和输出的存储过程
create or replace procedure sp_pro8
(spno in number, spName out varchar2) is
begin
select ename into spName from emp where empno=spno;
end;
/
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.}
import java.sql.*;
public class Test2{
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,"人民出版社");*/
//看看如何调用有返回值的过程
//创建CallableStatement
/*CallableStatement cs = ct.prepareCall("{call sp_pro8(?,?)}");
//给第一个?赋值
cs.setInt(1,7788);
//给第二个?赋值
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
//5.执行
cs.execute();
//取出返回值,要注意?的顺序
String name=cs.getString(2);
System.out.println("7788的名字"+name);
} catch(Exception e){
e.printStackTrace();
} finally{
//6.关闭各个打开的资源
cs.close();
ct.close();
}
}
}运行,成功得出结果。。
案例扩张:编写一个过程,可以输入雇员的编号,返回该雇员的姓名、工资和岗位。
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./
--有输入和输出的存储过程
create or replace procedure sp_pro8
(spno in number, 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;
/
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. //5.执行
32. cs.execute();
33. //取出返回值,要注意?的顺序
34. String name=cs.getString(2);
35. String job=cs.getString(4);
36. System.out.println("7788的名字"+name+" 工作:"+job);
37. } catch(Exception e){
38. e.printStackTrace();
39. } finally{
40. //6.关闭各个打开的资源
41. cs.close();
42. ct.close();
43. }
44. }
45.}
import java.sql.*;
public class Test2{
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,"人民出版社");*/
//看看如何调用有返回值的过程
//创建CallableStatement
/*CallableStatement cs = ct.prepareCall("{call sp_pro8(?,?,?,?)}");
//给第一个?赋值
cs.setInt(1,7788);
//给第二个?赋值
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
//给第三个?赋值
cs.registerOutParameter(3,oracle.jdbc.OracleTypes.DOUBLE);
//给第四个?赋值
cs.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR);
//5.执行
cs.execute();
//取出返回值,要注意?的顺序
String name=cs.getString(2);
String job=cs.getString(4);
System.out.println("7788的名字"+name+" 工作:"+job);
} catch(Exception e){
e.printStackTrace();
} finally{
//6.关闭各个打开的资源
cs.close();
ct.close();
}
}
}运行,成功找出记录
有返回值的存储过程(列表[结果集])
案例:编写一个过程,输入部门号,返回该部门所有雇员信息。
对该题分析如下:
由于oracle存储过程没有返回值,它的所有返回值都是通过out参数来替代的,列表同样也不例外,但由于是集合,所以不能用一般的参数,必须要用pagkage了。所以要分两部分:
返回结果集的过程
1.建立一个包,在该包中,我定义类型test_cursor,是个游标。 如下:
Sql代码
1.create or replace package testpackage as
2. TYPE test_cursor is ref cursor;
3.end testpackage;
create or replace package testpackage as
TYPE test_cursor is ref cursor;
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;
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 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.}
import java.sql.*;
public class Test2{
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.给第?赋值
cs.setInt(1,10);
//给第二个?赋值
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
//5.执行
cs.execute();
//得到结果集
ResultSet rs=(ResultSet)cs.getObject(2);
while(rs.next()){
System.out.println(rs.getInt(1)+" "+rs.getString(2));
}
} catch(Exception e){
e.printStackTrace();
} finally{
//6.关闭各个打开的资源
cs.close();
ct.close();
}
}
}运行,成功得出部门号是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;
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;
--开发一个包
--建立一个包,在该包中,我定义类型test_cursor,是个游标。 如下:
Sql代码
1.create or replace package testpackage as
2. TYPE test_cursor is ref cursor;
3.end testpackage;
4.--开始编写分页的过程
5.create or replace procedure fenye
6. (tableName in varchar2,
7. Pagesize in number,--一页显示记录数
8. pageNow in number,
9. myrows out number,--总记录数
10. myPageCount out number,--总页数
11. p_cursor out testpackage.test_cursor--返回的记录集
12. ) is
13.--定义部分
14.--定义sql语句 字符串
15.v_sql varchar2(1000);
16.--定义两个整数
17.v_begin number:=(pageNow-1)*Pagesize+1;
18.v_end number:=pageNow*Pagesize;
19.begin
20.--执行部分
21.v_sql:='select * from (select t1.*, rownum rn from (select * from '||tableName||') t1 where rownum<='||v_end||') where rn>='||v_begin;
22.--把游标和sql关联
23.open p_cursor for v_sql;
24.--计算myrows和myPageCount
25.--组织一个sql语句
26.v_sql:='select count(*) from '||tableName;
27.--执行sql,并把返回的值,赋给myrows;
28.execute inmediate v_sql into myrows;
29.--计算myPageCount
30.--if myrows%Pagesize=0 then这样写是错的
31.if mod(myrows,Pagesize)=0 then
32. myPageCount:=myrows/Pagesize;
33.else
34. myPageCount:=myrows/Pagesize+1
35.end if;
36.--关闭游标
37.close p_cursor;
38.end;
39./
create or replace package testpackage as
TYPE test_cursor is ref cursor;
end testpackage;
--开始编写分页的过程
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 inmediate v_sql into myrows;
--计算myPageCount
--if myrows%Pagesize=0 then这样写是错的
if mod(myrows,Pagesize)=0 then
myPageCount:=myrows/Pagesize;
else
myPageCount:=myrows/Pagesize+1
end if;
--关闭游标
close p_cursor;
end;
/
语句有错,更改为
--使用java测试
//测试分页
Java代码
1.import java.sql.*;
2.public class FenYe{
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 fenye(?,?,?,?,?,?)}");
13.
14. //4.给第?赋值
15. cs.seString(1,"emp");
16. cs.setInt(2,5);
17. cs.setInt(3,2);
18.
19. //注册总记录数
20. cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);
21. //注册总页数
22. cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);
23. //注册返回的结果集
24. cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);
25.
26. //5.执行
27. cs.execute();
28.
29. //取出总记录数 /这里要注意,getInt(4)中4,是由该参数的位置决定的
30. int rowNum=cs.getInt(4);
31.
32. int pageCount = cs.getInt(5);
33. ResultSet rs=(ResultSet)cs.getObject(6);
34.
35. //显示一下,看看对不对
36. System.out.println("rowNum="+rowNum);
37. System.out.println("总页数="+pageCount);
38.
39. while(rs.next()){
40. System.out.println("编号:"+rs.getInt(1)+" 名字:"+rs.getString(2)+" 工资:"+rs.getFloat(6));
41. }
42. } catch(Exception e){
43. e.printStackTrace();
44. } finally{
45. //6.关闭各个打开的资源
46. cs.close();
47. ct.close();
48. }
49. }
50.}
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.给第?赋值
cs.seString(1,"emp");
cs.setInt(2,5);
cs.setInt(3,2);
//注册总记录数
cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);
//注册总页数
cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);
//注册返回的结果集
cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);
//5.执行
cs.execute();
//取出总记录数 /这里要注意,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)+" 工资:"+rs.getFloat(6));
}
} catch(Exception e){
e.printStackTrace();
} finally{
//6.关闭各个打开的资源
cs.close();
ct.close();
}
}
}运行,控制台输出:
rowNum=19
总页数:4
编号:7369 名字:SMITH 工资:2850.0
编号:7499 名字:ALLEN 工资:2450.0
编号:7521 名字:WARD 工资:1562.0
编号:7566 名字:JONES 工资:7200.0
编号:7654 名字:MARTIN 工资:1500.0
--新的需要,要求按照薪水从低到高排序,然后取出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;
begin
--执行部分
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
文章分类:数据库
编写分页过程
介绍
分页是任何一个网站(bbs,网上商城,blog)都会使用到的技术,因此学习pl/sql编程开发就一定要掌握该技术。看图:
无返回值的存储过程
古人云:欲速则不达,为了让大家伙比较容易接受分页过程编写,我还是从简单到复杂,循序渐进的给大家讲解。首先是掌握最简单的存储过程,无返回值的存储过程:
案例:现有一张表book,表结构如下:看图:
书号 书名 出版社
请写一个过程,可以向book表添加书,要求通过java程序调用该过程。
--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./
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中调用
Java代码
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.}
//调用一个无返回值的过程
import java.sql.*;
public class Test2{
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.关闭各个打开的资源
cs.close();
ct.close();
}
}
}执行,记录被加进去了
有返回值的存储过程(非列表)
再看如何处理有返回值的存储过程:
案例:编写一个过程,可以输入雇员的编号,返回该雇员的姓名。
案例扩张:编写一个过程,可以输入雇员的编号,返回该雇员的姓名、工资和岗位。
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./
--有输入和输出的存储过程
create or replace procedure sp_pro8
(spno in number, spName out varchar2) is
begin
select ename into spName from emp where empno=spno;
end;
/
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.}
import java.sql.*;
public class Test2{
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,"人民出版社");*/
//看看如何调用有返回值的过程
//创建CallableStatement
/*CallableStatement cs = ct.prepareCall("{call sp_pro8(?,?)}");
//给第一个?赋值
cs.setInt(1,7788);
//给第二个?赋值
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
//5.执行
cs.execute();
//取出返回值,要注意?的顺序
String name=cs.getString(2);
System.out.println("7788的名字"+name);
} catch(Exception e){
e.printStackTrace();
} finally{
//6.关闭各个打开的资源
cs.close();
ct.close();
}
}
}运行,成功得出结果。。
案例扩张:编写一个过程,可以输入雇员的编号,返回该雇员的姓名、工资和岗位。
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./
--有输入和输出的存储过程
create or replace procedure sp_pro8
(spno in number, 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;
/
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. //5.执行
32. cs.execute();
33. //取出返回值,要注意?的顺序
34. String name=cs.getString(2);
35. String job=cs.getString(4);
36. System.out.println("7788的名字"+name+" 工作:"+job);
37. } catch(Exception e){
38. e.printStackTrace();
39. } finally{
40. //6.关闭各个打开的资源
41. cs.close();
42. ct.close();
43. }
44. }
45.}
import java.sql.*;
public class Test2{
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,"人民出版社");*/
//看看如何调用有返回值的过程
//创建CallableStatement
/*CallableStatement cs = ct.prepareCall("{call sp_pro8(?,?,?,?)}");
//给第一个?赋值
cs.setInt(1,7788);
//给第二个?赋值
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
//给第三个?赋值
cs.registerOutParameter(3,oracle.jdbc.OracleTypes.DOUBLE);
//给第四个?赋值
cs.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR);
//5.执行
cs.execute();
//取出返回值,要注意?的顺序
String name=cs.getString(2);
String job=cs.getString(4);
System.out.println("7788的名字"+name+" 工作:"+job);
} catch(Exception e){
e.printStackTrace();
} finally{
//6.关闭各个打开的资源
cs.close();
ct.close();
}
}
}运行,成功找出记录
有返回值的存储过程(列表[结果集])
案例:编写一个过程,输入部门号,返回该部门所有雇员信息。
对该题分析如下:
由于oracle存储过程没有返回值,它的所有返回值都是通过out参数来替代的,列表同样也不例外,但由于是集合,所以不能用一般的参数,必须要用pagkage了。所以要分两部分:
返回结果集的过程
1.建立一个包,在该包中,我定义类型test_cursor,是个游标。 如下:
Sql代码
1.create or replace package testpackage as
2. TYPE test_cursor is ref cursor;
3.end testpackage;
create or replace package testpackage as
TYPE test_cursor is ref cursor;
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;
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 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.}
import java.sql.*;
public class Test2{
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.给第?赋值
cs.setInt(1,10);
//给第二个?赋值
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
//5.执行
cs.execute();
//得到结果集
ResultSet rs=(ResultSet)cs.getObject(2);
while(rs.next()){
System.out.println(rs.getInt(1)+" "+rs.getString(2));
}
} catch(Exception e){
e.printStackTrace();
} finally{
//6.关闭各个打开的资源
cs.close();
ct.close();
}
}
}运行,成功得出部门号是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;
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;
--开发一个包
--建立一个包,在该包中,我定义类型test_cursor,是个游标。 如下:
Sql代码
1.create or replace package testpackage as
2. TYPE test_cursor is ref cursor;
3.end testpackage;
4.--开始编写分页的过程
5.create or replace procedure fenye
6. (tableName in varchar2,
7. Pagesize in number,--一页显示记录数
8. pageNow in number,
9. myrows out number,--总记录数
10. myPageCount out number,--总页数
11. p_cursor out testpackage.test_cursor--返回的记录集
12. ) is
13.--定义部分
14.--定义sql语句 字符串
15.v_sql varchar2(1000);
16.--定义两个整数
17.v_begin number:=(pageNow-1)*Pagesize+1;
18.v_end number:=pageNow*Pagesize;
19.begin
20.--执行部分
21.v_sql:='select * from (select t1.*, rownum rn from (select * from '||tableName||') t1 where rownum<='||v_end||') where rn>='||v_begin;
22.--把游标和sql关联
23.open p_cursor for v_sql;
24.--计算myrows和myPageCount
25.--组织一个sql语句
26.v_sql:='select count(*) from '||tableName;
27.--执行sql,并把返回的值,赋给myrows;
28.execute inmediate v_sql into myrows;
29.--计算myPageCount
30.--if myrows%Pagesize=0 then这样写是错的
31.if mod(myrows,Pagesize)=0 then
32. myPageCount:=myrows/Pagesize;
33.else
34. myPageCount:=myrows/Pagesize+1
35.end if;
36.--关闭游标
37.close p_cursor;
38.end;
39./
create or replace package testpackage as
TYPE test_cursor is ref cursor;
end testpackage;
--开始编写分页的过程
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 inmediate v_sql into myrows;
--计算myPageCount
--if myrows%Pagesize=0 then这样写是错的
if mod(myrows,Pagesize)=0 then
myPageCount:=myrows/Pagesize;
else
myPageCount:=myrows/Pagesize+1
end if;
--关闭游标
close p_cursor;
end;
/
语句有错,更改为
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 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; open p_cursor for v_sql; v_sql:='select count(*) from '||tableName; execute immediate v_sql into myrows; if mod(myrows,pageSize)=0 then myPageCount:=myrows/pageSize; else myPageCount:=myrows/pagesize+1; end if; close p_cursor; end;
--使用java测试
//测试分页
Java代码
1.import java.sql.*;
2.public class FenYe{
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 fenye(?,?,?,?,?,?)}");
13.
14. //4.给第?赋值
15. cs.seString(1,"emp");
16. cs.setInt(2,5);
17. cs.setInt(3,2);
18.
19. //注册总记录数
20. cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);
21. //注册总页数
22. cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);
23. //注册返回的结果集
24. cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);
25.
26. //5.执行
27. cs.execute();
28.
29. //取出总记录数 /这里要注意,getInt(4)中4,是由该参数的位置决定的
30. int rowNum=cs.getInt(4);
31.
32. int pageCount = cs.getInt(5);
33. ResultSet rs=(ResultSet)cs.getObject(6);
34.
35. //显示一下,看看对不对
36. System.out.println("rowNum="+rowNum);
37. System.out.println("总页数="+pageCount);
38.
39. while(rs.next()){
40. System.out.println("编号:"+rs.getInt(1)+" 名字:"+rs.getString(2)+" 工资:"+rs.getFloat(6));
41. }
42. } catch(Exception e){
43. e.printStackTrace();
44. } finally{
45. //6.关闭各个打开的资源
46. cs.close();
47. ct.close();
48. }
49. }
50.}
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.给第?赋值
cs.seString(1,"emp");
cs.setInt(2,5);
cs.setInt(3,2);
//注册总记录数
cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);
//注册总页数
cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);
//注册返回的结果集
cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);
//5.执行
cs.execute();
//取出总记录数 /这里要注意,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)+" 工资:"+rs.getFloat(6));
}
} catch(Exception e){
e.printStackTrace();
} finally{
//6.关闭各个打开的资源
cs.close();
ct.close();
}
}
}运行,控制台输出:
rowNum=19
总页数:4
编号:7369 名字:SMITH 工资:2850.0
编号:7499 名字:ALLEN 工资:2450.0
编号:7521 名字:WARD 工资:1562.0
编号:7566 名字:JONES 工资:7200.0
编号:7654 名字:MARTIN 工资:1500.0
--新的需要,要求按照薪水从低到高排序,然后取出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;
begin
--执行部分
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
发表评论
-
oracle 数据库 删除用户所有对象
2010-11-15 15:29 1156在命令行下执行sqlplus 用户名/密码 (不要删syste ... -
oracle角色权限 赋予用户
2010-09-09 09:13 3156oracle角色权限 一、权 ... -
Oracle 10g学习总结
2010-09-08 17:05 1035Oracle 10g学习总结 1.登 ... -
Oracle 交叉表 固定值 行转列
2010-05-11 17:18 2041Oracle 交叉表 固定值 如果速度固定只有0 ... -
oracle-总结 oracle函数 oracle用法 oracle基础知识
2010-05-11 17:16 1227有时间可以看看复习一 ... -
Oracle创建表空间、创建用户以及授权、查看权限
2010-03-26 14:51 869Oracle创建表空间、创建用户以及授权、查看权限 创建临 ... -
oracle开发的数据库管理工作
2009-11-05 11:31 848/** *author:erick *time:2009081 ... -
oracle操作
2009-09-29 15:25 9161.安装oracle 2.用sys登录 3.新建用户 4 ... -
rownum来说它是oracle
2008-04-24 20:17 1125对于rownum来说它是oracle ... -
Oracle语句优化53个规则详解(1)
2008-04-24 20:16 8631. 选用适合的ORACLE优化 ... -
sql常用语句和函数
2008-04-24 20:15 11721.求字持串的长度LENGTH 您可用LENGTH函数求字符串 ...
相关推荐
它没有使用PL/SQL语句(比如什么程序包、游标等),只是使用通用的、简单的SQL实现了数据库分页动作。 只要看懂了分页公式,那么这是一个通用的Oracle数据库分页动作(可以使用PHP, JAVA, ASP .NET等其他语言)。 ...
oracl函数 事物 游标 存储 mysql分页 sql语句拼写 pl/sql
该例子针对上次SQL实现分页公式使用的详细说明--说明具体在函数中怎样使用分页公式和SQL怎样在游标中返回的说明,然后JDBC怎样捞取数据,然后通过Servlet与JSP显示出来。当然该函数可以由读者进一步完成,我主要是...
13.1.2 PL/SQL的集合优化 355 13.1.3 PL/SQL的游标合并 361 13.1.4 动态SQL 364 13.1.5 使用10046 trace跟踪PL/SQL 368 13.2 PL/SQL优化其他相关扩展 369 13.2.1 编译无法成功 369 13.2.2 通用脚本分享 370 ...
13.1.2 PL/SQL的集合优化 355 13.1.3 PL/SQL的游标合并 361 13.1.4 动态SQL 364 13.1.5 使用10046trace 跟踪PL/SQL 368 13.2 PL/SQL优化其他相关扩展 369 13.2.1 编译无法成功 369 13.2.2 通用脚本分享 370 ...
存储过程是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。 1.9.2 准备 create table t_user ( username varchar2(20), ...
24、什么是PL/SQL? 49 25、序列的作用 50 26、表和视图的关系 50 27、oracle基本数据类型 50 28、drop、truncate、 delete区别 50 29、如何优化大数据量的访问? 51 30、oracle怎么去除去重 51 31、合并查询有哪些...
角色是一组权限的集合,将角色赋给一个用户,这个用户就拥有了这个角色中的所有权限。 系统预定义角色 预定义角色是在数据库安装后,系统自动创建的一些常用的角色。下面我们就简单介绍些系统角色: CONNECT...
16.3.2 存储过程的创建与调用 264 16.3.3 存储过程的参数 265 16.3.4 复合语句 267 16.3.5 变量 268 16.3.6 条件语句 269 16.3.7 循环语句 271 16.3.8 游标 273 16.3.9 存储过程的删除 275 16.4 触发器的设计 275 ...
14、查询从多少行到多少行的记录(可以用在web开发中的分页显示) select * from ( select rownum row_id,b.* from (select a.* from sys_oper a) b ) where row_id between 15 and 20 15、对公共授予访问权 ...
--(将sal的查询结果转化为字符串,与ename连接到一起,相当于Java中的字符串连接) 7、select ename||'afasjkj' from emp; --字符串的连接 8、select distinct deptno from emp; --消除deptno字段重复的值 9、...