Java Learn（二十三）

PL/SQL和分页功能

分页

OracleSQL的 top n 问题和分页的解决方案：rownum

rownum

1.rownum是 Oracle 数据库提供的解决方案，但很别扭；

查询工资前五的员工信息：

select empno,ename,sal from emp order by sal desc;

select empno,ename,sal from emp where rownum<6 order by sal desc;  — 先 where 后排序，结果与预期不符

select * from (select empno,ename,sal from emp order by sal desc) where rownum<6;

查询工资6-10的员工信息：

select * from (select empno,ename,sal from emp order by sal desc) where rownum between 6 and 10; — 没有结果，但有数据

select * from (select empno,ename,sal from emp order by sal desc) where rownum>5; — 没有结果

2.rownum 很特殊，只有小于（小于等于）可以用，等于或者大于都查不出数据。分页问题，Oracle 需要用别名的特殊写法。

select * from (

select rownum row_num,empno,ename,sal from 

（select empno,ename,sal from emp order by sal desc) 

) where row_num between 6 and 10;

PL/SQL

不同的数据库都是不一样的，没有通用性

PL/SQL 是数据库的开发，是数据库的程序。

软件的结构：

客户端 => 应用服务器 => 数据库服务器

PL/SQL 主要是数据库服务器的开发

Pl/SQL 的重点：游标、存储过程、函数

常见的访问数据库的技术：

|数据库类型|描述|
|：-------|：---|
|PL/SQL | 过程化的 sql|
|proc/c++ | 用 c 或者 c++语言访问Oracle|
|ODBC/ADO | VC 访问数据库的技术|
|OCI | Oracle 底层的连接接口|
|SQLJ/JDBC | Java 语言访问数据库|
|O/R Mapping | 对象和关系映射技术，用面向对象的方式去操作关系型数据库|
|Hibernate 和 Mybatis |框架都是 O/R Mapping 的实现|

PL/SQL 的内容

变量 类型（sql 的类型均包含）

和控制语句 循环语句 其他的对象以及函数

PL/SQL 程序的组成

declare       

— 声明区

begin         

— 执行区

exception   

— 异常处理函数

end；

示例：

begin

dbms_out.put_line("hello!");

end；

输出默认是关闭的，需要打开才能看到输出。打开方式：set serveroutput on

初始化 赋值是通过 := 来表达的

declare

var_i  number;

/*初始化*/

var_j number := 100;

begin

/*赋值*/

var_i :=1;

dbms_output.put_line(var_i || ':' || var_j);

ends;

constant 定义常量

not null 就是表达不能为空

当一个变量定义之后 如果没有赋初值则变量的值是null

变量的类型

标量类型

数字 binary_integer number

字符串 char(n) varchar2(n)

布尔 boolean ---- true false null

日期 date

组合类型

record(记录) table(表) cursor(游标)

引用类型

ref cursor （参考类型）

大类型

blob

clob

bfile

在申明区定义两个变量

id

name

把emp 表中的id为1的empno,ename

数据查询出来放入变量中

然后输出

declare

    id    number;

    name  varchar2(10); 

begin

    select empno,ename into id,name -- into 就是把结果放入变量中

    from emp where empno=7369;

    dbms_output.put_line(id||':'||name);

end;

PL/SQL 中用来取表中字段类型的语法

%type 代表 的类型

declare

    id    emp.empno%type;

    name  emp.ename%type; 

begin

    select empno,ename into id,name from emp where empno=7369;

    dbms_output.put_line(id||':'||name);

end;

把emp 表中empno为7369的empno ename sal

查询出来放入一个类型的变量中

结构体类型

record 类型

在申明区中定义一个类似于结构体类型(类)的类型

declare 

    /*定义一个记录类型 叫emprectype*/

    TYPE  emprectype  IS RECORD(

        id     emp.empno%type,

name   emp.ename%type,

salary emp.sal%type

    );

    /*用上面的类型定义一个变量*/

    var_emp   emprectype;

begin

    select empno,ename,sal  into var_emp -- 次序不可混乱

    from emp where empno=7369;

    dbms_output.put_line(var_emp.id||':'||

    var_emp.name||':'||var_emp.salary);

end;

declare 

    /*定义一个记录类型 叫emprectype*/

    TYPE  emprectype  IS RECORD(

        id     emp.empno%type,

name   emp.ename%type,

salary emp.sal%type

    );

    /*用上面的类型定义一个变量*/

    var_emp   emprectype;

begin

    select empno,ename  into var_emp.id,var_emp.name from emp where empno=7369;

    dbms_output.put_line(var_emp.id||':'||

    var_emp.name||':'||var_emp.salary);

end;

备注:如果查询的字段 少于记录类型的字段

则可以把记录类型的字段取出来单独赋值

不赋值的字段就是null值.

记录类型的整体赋值

declare  

    /*定义一个记录类型 叫emprectype*/

    TYPE  emprectype  IS RECORD(

        id     emp.empno%type,

name   emp.ename%type,

salary emp.sal%type

    );

    /*用上面的类型定义一个变量*/

    var_emp    emprectype;

    var_emp2   emprectype;

begin

    select empno,ename,sal  into var_emp2 from emp where empno=7369;

   var_emp:=var_emp2;

    dbms_output.put_line(var_emp.id||':'||

    var_emp.name||':'||var_emp.salary);

end;

除了整体赋值之外 也支持单个字段的赋值

declare  

    /*定义一个记录类型 叫emprectype*/

    TYPE  emprectype  IS RECORD(

        id     emp.empno%type,

name   emp.ename%type,

salary emp.sal%type

    );

    /*用上面的类型定义一个变量*/

    var_emp    emprectype;

    var_emp2   emprectype;

begin

    select empno,ename,sal  into var_emp2 from emp where empno=7369;

    var_emp.id:=var_emp2.id;

    var_emp.name:=var_emp2.name;

    dbms_output.put_line(var_emp.id||':'||

    var_emp.name||':'||var_emp.salary);

end;

如果把emp 表中empno为1的所有数据 都放入一个记录类型定义的变量中?

%rowtype 取得一个表的一行对应的类型

emp%rowtype dept%rowtype

一个表中的所有字段对应的一个记录类型

字段排放顺序 和desc 表名得到的顺序相同并且字段名和record的成员名也一致。

declare

   /*用记录类型定义变量*/

   var_emp  emp%rowtype;

begin

   select * into var_emp  from emp where empno=7369;

   dbms_output.put_line(var_emp.empno|| ':' ||var_emp.ename);

end;

%rowtype 就是全字段的记录类型 除了语法上和记录类型有差别之外，定义完变量后的操作和记录类型完全相同。

如:

declare

   /*用记录类型定义变量*/

   var_emp   emp%rowtype;

begin

   select empno,ename,sal  into var_emp.empno,var_emp.ename,var_emp.sal from emp where empno=7369;

   dbms_output.put_line(var_emp.empno || ':' || var_emp.ename);

end;

表

table 可以放多行记录

--------和Java中的数组/Map很相似

table类型的数据

如何定义table类型

怎么使用类型定义变量

怎么操作table类型的变量

table像数组，但下标没有任何的限制，随便用，不连续，也可以为负数，没有定义过的下标直接报错。

declare

    /*定义一个table类型 放整数类型*/

    TYPE  tablenumbertype  IS TABLE  OF emp.empno%type index by  binary_integer;

    /*定义一个table 类型的变量*/

    var_nums  tablenumbertype;

begin

    var_nums(0):=100;

    var_nums(3):=200;

    var_nums(4):=400;

    dbms_output.put_line('var_nums(4):' || var_nums(4));

end;

/*把这个table类型的变量中所包含的所有数据输出*/


declare

    /*定义一个table类型 放整数类型*/

    TYPE  tablenumbertype  IS TABLE   OF s_emp.id%type index by  binary_integer;

    /*定义一个table 类型的变量*/

    var_nums  tablenumbertype;

begin

    var_nums(0):=100;

    var_nums(3):=200;

    var_nums(4):=400;

    dbms_output.put_line('var_nums(2):'  || var_nums(2));

end;

访问没有赋值的下标

ERROR at line 1:

ORA-01403: no data fou

ORA-06512: at line 11

迭代器思想

table 类型提供的函数

first() 得到table类型的第一个元素对应的下标

next(下标) 得到一个下标的下一个元素的下标

last() 得到最后一个元素对应的下标

declare

   /*定义一个table类型 放整数类型*/

    TYPE  tablenumbertype  IS TABLE   OF emp.empno%type index by binary_integer;

    /*定义一个table 类型的变量*/

    var_nums  tablenumbertype;

    /*定义一个元素下标变量*/

    var_ind   binary_integer;

begin

    var_nums(0):=100;

    var_nums(3):=200;

    var_nums(-4):=400; --  按下标排序，不按先后次序。

    var_ind:=var_nums.first();

    dbms_output.put_line(var_nums(var_ind)||':'||var_ind); -- 100

    var_ind:=var_nums.next(var_ind); 

    dbms_output.put_line(var_nums(var_ind)||':'||var_ind);-- 200

    var_ind:=var_nums.next(var_ind); 

    dbms_output.put_line(var_nums(var_ind)||':'||var_ind);-- 400

    dbms_output.put_line(var_nums(var_nums.last()));

end;

把 empno 是7369，7900，7902的所有员工信息 放入一个table类型的变量中

使用迭代器思想 输出这些数据的 id first_name salary

declare

    /*定义一个table类型 放整数类型*/

    TYPE  tablenumbertype  IS TABLE   OF emp%rowtype index by  binary_integer;    

    var_nums  tablenumbertype; /*定义一个table 类型的变量*/

    var_ind   binary_integer;  /*定义一个元素下标变量*/

begin

    select *  into var_nums(0) from emp where empno=7369;

    select *  into var_nums(1) from emp where empno=7900;

    select *  into var_nums(2) from emp where empno=7902;

    var_ind:=var_nums.first();

    dbms_output.put_line(var_nums(var_ind).empno || ',' || var_nums(var_ind).ename || ',' || var_nums(var_ind).sal);

    var_ind:=var_nums.next(var_ind);

    dbms_output.put_line(var_nums(var_ind).empno || ',' || var_nums(var_ind).ename || ',' || var_nums(var_ind).sal);

    var_ind:=var_nums.next(var_ind);

    dbms_output.put_line(var_nums(var_ind).empno || ',' || var_nums(var_ind).ename || ',' || var_nums(var_ind).sal);

end;

变量的作用域和可见性

PL/SQL块的嵌套

declare

     var_i   number:=1;

  begin

      declare

          var_j   number:=10;

      begin

          /*可不可以访问到全局的var_i*/

      exception 

 

      end;

      /*不能访问var_j*/

  exception 

  end;


  declare

     var_i   number:=1;

  begin

      declare

          var_i   number:=10;

      begin

          /*访问的是局部的*/

          dbms_output.put_line(var_i);  

  end;

end;


<<abc>>

  declare

     var_i   number:=1;

  begin

      declare

          var_i   number:=10;

      begin

          /*访问的是局部的*/

          dbms_output.put_line(var_i);  

          dbms_output.put_line(abc.var_i);  

      end;

      

  end;

控制语句、条件语句

if   表达式   then

    满足条件

elsif  表达式 then

    

elsif  表达式 then


else


end if;


if   表达式   then

    满足条件

end if;



if   表达式   then

    满足条件

else

    其它情况

end if;


if   表达式   then

    满足条件

elsif  表达式 then


elsif  表达式 then

end if;

针对于boolean 类型 NULL 相当于false

判断NULL的运算特点

定义两个变量 不赋值

var_x var_y

如果var_x>var_y

就输出 'var_x>var_y'

var_x is var_y //error

var_x is null and var_y is null

declare

    var_x   number:=5;

    var_y   number;

begin

    if  var_x>var_y then

dbms_output.put_line('var_x>var_y');

    elsif var_x<var_y then

        dbms_output.put_line('var_x<var_y');

    elsif var_x=var_y then

        dbms_output.put_line('var_x=var_y'); 

    elsif var_x is null and var_y is null then

        dbms_output.put_line('var_x is null var_y is null');

    else

        dbms_output.put_line('var_x other var_y'); 

    end if;

end;

循环语句

1.简单循环

输出1到10

declare

  var_i   number:=1;

  begin

      dbms_output.put_line(var_i);

      var_i:=var_i+1;

  end;

/*死循环  无限循环 不要执行*/

  declare

  var_i   number:=1;

  begin

  loop

     dbms_output.put_line(var_i);

     var_i:=var_i+1;

  end loop;

  end; 


/*循环的退出*/

exit   when   退出条件;

等价于

if   退出条件  then

    exit;

end if;


  declare

  var_i   number:=1;

  begin

  loop

     exit when var_i=11;

     dbms_output.put_line(var_i);

     var_i:=var_i+1;

  end loop;

  end; 


  declare

  var_i   number:=1;

  begin

  loop

     if var_i=11  then exit;

     end if;

     dbms_output.put_line(var_i);

     var_i:=var_i+1;

  end loop;

  end;

while 循环

while 条件 loop

end loop;

满足条件就进入循环

输出1到10

declare

    var_i   number:=1;

begin

    while var_i<11 loop

        dbms_output.put_line(var_i);

var_i:=var_i+1;

    end loop;

end;


declare

    var_i   number:=1;

begin

    while var_i<11   loop

        /*循环体中满足循环退出*/       

        dbms_output.put_line(var_i);

var_i:=var_i+1;

        exit  when  var_i=6;

    end loop;

end;

for 循环