Oracle基础进阶

其实
Oracle数据库的分页还是比较容易理解的。此文以oracle数据库中的SCOTT用户的EMP表为例,用PL/SQL
Developer编写一个分页存储过程,要求是:可以输入表名,每页显示记录数,当前页,返回总记录数,总页数和返回的结果集。

PL/SQL

由于需要返回查询出来的结果集,需要在PL/SQL中创建一个package,这个包里面定义一个refcursor类型,用于记录sql语句查询出来的结果集。创建包的代码如下:

  是Oracle对sql语言的过程化扩展,指在sql命令语言中增加了过程处理语句,是sql语言具有过程处理能力.
语法:
  [declare
    –声明变量
  ]
  begin
  –逻辑代码
  [exception
    –异常处理
  ]

create or replace package pagingPackage as type paging_cursor is ref cursor; end pagingPackage; 

变量:

接下来开始Oracle的分页过程,我们可以用select emp.*,rownum from
emp;来显示地表示出每行的行标。然后可以根据行标对内容进行分页,下面这个SQL语句可以作为Oracle分页的模板。

  声明变量的语法:变量名 类型(长度)
  变量赋值的语法:变量名:=变量值

select * from (select t1.*,rownum rn from (select * from emp) t1 where rownum=12) where rn=8;

例如

有了上面的refcursor类型和分页模板,下面开始编写分页的存储过程,代码如下:

declare
  v_price number(10,2);--单价
  v_usenum number;--水费数字
  v_usenum2 number(10,2);--吨数
  v_money number(10,2);--总价
begin
  v_price:=2.45;
  v_usenum:=3306;
  v_usenum2:=round(v_usenum/1000,2);
  v_money:=v_usenum2*v_price;
  DBMS_output.put_line('总价:'||v_money);
end;
create procedure paging (tableName in varchar2 ,--表名 pageSizes in number,--每页显示记录数 pageNow in number,--当前页 rowNums out number,--总记录数 pageNum out number,--总页数 paging_cursor out pagingPackage.paging_cursor) is --定义部分 --定义sql语句,字符串 v_sql varchar2(1000); --定义两个整数,用于表示每页的开始和结束记录数 v_begin number:=(pageNow-1)*pageSizes+1; v_end number:=pageNow*pageSizes; begin --执行部分 v_sql:='select * from (select t1.*,rownum rn from (select * from '||tableName||') t1 where rownum='||v_end||') where rn='||v_begin; --把游标和sql语句关联 open paging_cursor for v_sql; --计算rowNums和pageNum --组织一个sql语句 v_sql:='select count(*) from '||tableName; --执行该sql语句,并赋给rowNums execute immediate v_sql into rowNums; --计算pageNum if mod(rowNums,pageSizes)=0 then pageNum := rowNums/pageSizes; else pageNum := rowNums/pageSizes+1; end if; end; 

 

在Java中编写代码测试分页,代码如下:

对于变量的赋值,还 可以用:select into方式赋值 select 列名 into
变量名 from 表名 where 条件

但是这种方式的结果必须是一条记录,有多条记录和没有记录都会报错

package com.test.oracletest; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; //测试分页 public class OraclePaging { public static void main(String[] args) { try { // 加载驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); // 获取连接 Connection connection = DriverManager.getConnection( "jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "SCOTT", "tiger"); // 创建CallableStatement,调取数据库的存储过程 CallableStatement cst = connection .prepareCall("{call paging(,,,,,)}"); // 给?赋值 cst.setString(1, "emp"); cst.setInt(2, 6); cst.setInt(3, 2); // 注册存储过程的输出项 cst.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER); cst.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER); cst.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR); // 执行 cst.execute(); // 获取输出项 int rowNums = cst.getInt(4); int pageNum = cst.getInt(5); ResultSet rs = (ResultSet) cst.getObject(6); System.out.println("总记录数rowNums = " + rowNums); System.out.println("总页数pageNum = " + pageNum); System.out.println("EMPNO" + '\t' + "ENAME" + '\t' + "ROWNUM"); while (rs.next()) { System.out.println(rs.getInt("EMPNO") + " " + '\t' + rs.getString("ENAME") + '\t' + rs.getInt("RN")); } } catch (Exception e) { e.printStackTrace(); } finally { // 关闭资源 } } } 

这里就可以吧上面的水费的数字改成从已经有的表中查找出来

执行的结果如图所示:

select usernum into v_usenum from t_account where owneruuid=1;

我们还可以通过修改模板最内侧的视图来满足其他一些基本的排序要求。Oracle的分页思想就是如此。。#

属性类型分为引用型和记录型,首先介绍引用型
  %type(引用型):引用某表某列的字段类型
还是上面的例子,水费数字的number可以改为指定表中指定列的引用型类型

v_usenum t_account.usenum%type;

%rowtype(记录型):表示某个表的行记录类型
依旧上面的例子,水费数字等数据可以被替代为:

v_account t_account%rowtype;

这个赋值可以看做是为一个对象赋值一行数据,而这个对象在这里就是v_account

赋值语句:

select * into v_account from t_account where owneruuid = 1;

这样在后面使用的时候直接点调用里面需要的数据
比如要调用水费数字:
v_account.usenum;这就得到了水费数字在原表中的数据

异常:

  在运行程序时出现的错误叫做异常
发生异常后,语句将停止执行,控制权转移到PL/SQL块的异常处理部分.
在这里介绍两个预定义异常,预定义异常就是当PL/SQL程序违反Oracle规则或超越系统限制时隐式引发

首先介绍第一个:

NO_DATA_FOUND:使用select into未返回行
TOO_MANY_ROWS:执行select into时,结果集超过一行

exception
  when 异常类型 then
    异常处理逻辑

还是以上面的为例子,模拟在给水费数字赋值的时候,select into未返回行

...
begin
exception
    when NO_DATA_FOUND then
    dbms_output.put_line('没有返回数据');
end;
如果在再此基础上添加一个超过结果集的异常,那么
...
begin
exception
    when NO_DATA_FOUND then
    dbms_output.put_line('没有返回数据');
    when TOO_MANY_ROWS then
    dbms_output.put_line('返回信息过多');
end;

条件判断

  基本语法1:

if 条件 then
  业务逻辑
end if;

 

  基本语法2:

if 条件 then
  业务逻辑
else
  业务逻辑
end if;

 

基本语法3:

if 条件 then
  业务逻辑
elsif 条件 then
  业务逻辑
else
  业务逻辑
end if;

下面是一个前面综合的完整列子:

  5 吨以下 2.45 元/吨 5 吨到 10 吨部分 3.45 元/吨 ,超过 10 吨部分
4.45 ,根据使用水费的量来计算阶梯水费。

declare
    v_price1 number(10,2);-- 不足 5 吨的单价
    v_price2 number(10,2);-- 超过 5 吨不足 10 吨单价
    v_price3 number(10,2);-- 超过 10 吨单价
    v_account T_ACCOUNT%ROWTYPE;-- 记录型
    v_usenum2 number(10,2);-- 使用吨数
    v_money number(10,2);-- 水费金额
begin
-- 对单价进行赋值
    v_price1:=2.45;
    v_price2:=3.45;
    v_price3:=4.45;
    -- 给v_account赋值
    select * into v_account from T_ACCOUNT
    where year='2012' and month='01' and owneruuid=1;
    -- 使用吨数
    v_usenum2:= round(v_account.usenum/1000,2);
-- 计算金额 ( 阶梯水费 )
    if v_usenum2<=5 then-- 第一个阶梯
        v_money:=v_price1*v_usenum2;
    elsif v_usenum2>5 and v_usenum2<=10 then -- 第二个阶梯
        v_money:=v_price1*5 + v_price2*( v_usenum2-5);
    else -- 第三个阶梯
        v_money:=v_price1*5 +v_price2*5 + v_price3*( v_usenum2-10 );
    end if;
DBMS_OUTPUT.put_line('金额' || v_money);

exception
    when NO_DATA_FOUND then
        DBMS_OUTPUT.put_line('没有找到数据');
    when TOO_MANY_ROWS then
        DBMS_OUTPUT.put_line('返回的数据有多行');
end;

循环:

下面是用1-100的循环例子解释三种不同的循环

  无条件循环(语法):

loop
  – 循环语句
end loop;

第一种解法:

declare
    v_num number:=1;
begin
    loop
        dbms_output.put_line(v_num);
        v_num:=v_num+1;
        exit when v_num>100;
    end loop;
end ;

条件循环(语法):

while 条件
  loop
end loop;