oracleday20(游标 分页 分页的扩展 让分页的构成更加实用 视图)

使用pl/sql编写分页过程

通过编写分页过程 目的是让大家把前面学习的各个技术综合运行

因为分页过程比较复杂 所以采用循序渐进的方式 讲解

1、如何在java程序中调用一个不含返回值的过程

在主方法中使用存储过程

String sql = "{call scott.mypackage1.pro1(?,?)}";
String paras [] = {"KING","8000"};
SQLHelper.executeProcedure(sql, paras);

//调用存储过程的方法
public static void executeProcedure(String sql , String[] parameters){
try {
ct = DriverManager.getConnection(url,username,password);//得到连接
cs = ct.prepareCall(sql);


if(parameters!=null&&"".equals(parameters)){//更加严谨
for (int i = 0; i < parameters.length; i++) {
cs.setString(i+1, parameters[i]);
}
}
//调用过程
cs.execute();
} catch (Exception e) {


e.printStackTrace();
throw new RuntimeException(e.getMessage());
} finally{
close(rs,cs,ct);
}
}

有返回值的存储过程(非列表)

在java中如何调用返回非列表的值输入雇员的编号 返回 雇员的姓名  单列

create or replace procedure pro1(v_in_empno number ,v_out_ename out varchar2) is --out 明确告诉oracle这个变量要被输出 要返回值给调用者

begin

select ename into  v_out_ename from emp where empno=v_in_empno;

end ; --pro1 可以写明确终止哪个过程

在java中 调用该过程 并接受返回的用户名

Connection ct = null;
CallableStatement cs= null;
ResultSet rs = null;
try {
//1、加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2、得到连接
ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","m123");
//3、创建callablestatement
cs = ct.prepareCall("{call pro1(?,?)}");
//4、给第一个?赋值
// cs.setString(1, "7839");//会自动转化 不成功就出问题
cs.setInt(1, 7839);
//5、给第二个问号注册因为是输出值
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);//要先导包 不能用shift+alt+o 
//6、执行
cs.execute();
//7、取出输出值
String ename =cs.getString(2);
System.out.println("用户的名字是"+ename);
} catch (Exception e) {


e.printStackTrace();
} finally{
//关闭资源
SQLHelper.close(rs, cs, ct);//需要加前缀才能使用它的方法
}

1、对于过程的输入值 使用setXXX 对于输出值 使用registerOutParameter 问号的顺序要对应

同时要考虑类型 

2、取出过程返回值的方法是CallableStatement 提供的getXXX (输出参数的位置) 同时考虑输出参数的类型

接受返回值 为列表结果集

编写一个过程 输入部门号 返回该部门所有雇员的信息

由于oracle存储过程没有返回值 他的所有返回值都是通过out参数来替代的 列表同样也不例外 但由于是集合 所以不能用一般的参数 必须要用package

1、建一个包

2、建立存储过程

3、如何在java程序中调用

1、创一个包 在包中定义一个游标类型

create or replace package pack1 is 

--定义一个游标数据类型
type my_cursor is ref cursor;

end;

2、编写过程

create or replace procedure pro1(v_in_deptno in number ,v_out_result out pack1.my_cursor) is --定一个变量输出类型是游标

begin

open v_out_result for select * from emp where deptno=v_in_deptno;

--游标应该放在java程序中关 如果现在关了 在java程序中就不能用

--close v_out_result;

end;

/

3、编写java程序

cs.setInt(1, 10);
//5、给第二个问号注册因为是输出值
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);//要先导包
//6、执行
cs.execute();
//7、取出输出值 这里是关键
rs = (ResultSet) cs.getObject(2);//rs指向了你的游标类型
while(rs.next()){
System.out.println(rs.getString("ename")+" "+rs.getString("sal"));
}

只返回一行 没有必要用游标 添加几个out就行

完成一个分页存储过程 

输入表名每页显示记录数 当前页 返回结果集 返回共有多少页 多少条记录

建包使用游标类型 

如果有这个游标类型 则可以使用 一标多用

create or replace package pack1 is 

--定义一个游标数据类型
type my_cursor is ref cursor;

end;

--编写过程

create or replace procedure fenyePro(v_in_table in varchar2,v_in_pagesize in number, v_in_pagenow number,v_out_result out pack1.my_cursor

v_out_rows out number,v_out_pagecount out number) is

--定义变量

v_sql varchar2(2000);

v_start number;

v_end number;

begin

--执行代码

--回忆分页语句 

计算v_start 和v_end 是多少

v_start :=v_in_pagesize*(v_in_pagenow-1)+1;

v_end :=v_inpagesize*v_in_pagenow;

v_sql:=' select t2.*from  (select t1.*,rownum rn from (select * from '||v_in_table||')  t1 where rownum<='||v_end||') t2 where rn>='||v_start;

--开始必须要加'  然后要加||因为要拼接

--打开游标 让游标指向结果集

open v_out_result for v_sql;

--查询共有多少条记录

select count(*) into v_out_rows from emp;

--查询有多少函数

if mod(v_out_rows,v_in_pagesize)=0 then

v_out_pagecount:=v_out_rows/v_in_pagesize;

else

v_out_pagecount:=v_out_rows/v_in_pagesize+1; 

end if;

end;

/

java中如何调用

cs = ct.prepareCall("{call fenyePro(?,?,?,?)}");
//4、给?赋值
//1?表名
//2?pagesize
//3?oagenow
//4?结果
cs.setString(1, "emp");
cs.setInt(2, 3);
cs.setInt(3, 1);
//5、给第四个问号注册因为是输出值
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.CURSOR);//要先导包
//6、执行
cs.execute();
//7、取出输出值 这里是关键
rs = (ResultSet) cs.getObject(2);//rs指向了你的游标类型
while(rs.next()){
System.out.println(rs.getString("ename")+" "+rs.getString("sal"));
}

增加一个输入参数 可以按照某列排序

cs = ct.prepareCall("{call fenyePro(?,?,?,?,?,?)}");
//4、给?赋值
//1?表名
//2?pagesize
//3?oagenow
//4?结果
cs.setString(1, "emp");
cs.setInt(2, 3);
cs.setInt(3, 1);
//5、给第四个问号注册因为是输出值
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.CURSOR);//要先导包
//给第五个问号注册
cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
//给第6个问号注册
cs.registerOutParameter(6, oracle.jdbc.OracleTypes.INTEGER);
//6、执行
cs.execute();
//7、取出输出值 这里是关键


rs = (ResultSet) cs.getObject(4);//rs指向了你的游标类型
while(rs.next()){
System.out.println(rs.getString("ename")+" "+rs.getString("sal"));
}
int rowCount=cs.getInt(5);
System.out.println(rowCount);
int pageCount=cs.getInt(6);
System.out.println(pageCount);

视图是一个虚拟表 其内容由查询定义 同真实的表一样 视图包含一系列的带有名称的列和行数据 但是 视图并不在数据库中以存储的

数据值集形式存在 行和列数据来自由定义视图的查询所引用的表 并且在引用视图时动态生成 

视图是oracle的又一种数据对象 视图的主要用处是简化操作 提高安全性 满足不同用户的查询需求 视图不是一个真正存在的物理表

是根据别的表动态生成的

视图管理

创建视图

create view 视图名 as select 语句 [with read only]

创建或修改视图

create or replace view 视图名 as select 语句 [with read only]

删除视图 drop view 视图名

创建 和emp 表(empno,ename,job) 完全一致的视图

如果我们创建视图的时候 带了with read only 则表示该视图只能读 而不能进行 其他操作 如果没有带with read only 则可以进行其他操作

会通过视图删除表中的数据 

create view empview as select empno,ename,job from emp;

如果创建视图时候 不希望用户通过视图 对源表进行其他操作 则建议带 with read only

视图可以简化我们的操作

希望查询雇员的名字和部门编号和部门名称

传统 查询两张表 

视图 

create or replace view myview as select emp.ename,dept.deptno,dept.dname from emp,dept where emp.deptno = dept.deptno with read only;

select * from myview ;

select * from myview where deptno=10;

create or replace view myview as sql语句[可以多表] [with read only]

视图和表的区别

1、表占用空间  视图不需要

2、视图不能加索引  动态存在 不能加索引 索引不能存放

3、视图可以简化操作

以后 可以直接查询视图得到结果

4、提高安全性 给不同的用户分配不同的视图 提高安全性