plsql programming 13 其他数据类型

bolean 类型

raw 类型, 用来保存和操作少量的二进制数据.

urowid 和 rowid 类型, 这两种数据类型表示数据库的 rowid. 所谓 rowid 就是一个标识符-用来表示数据库中一行记录的物理地址的二进制值. 一个rowid值可以唯一的标识出数据库表中的一行数据, 即便这个表没有唯一键. 两行记录就算是所有列的值都完全相同, 但是会有不同的 rowid 或者 urowid.

从历史上说, rowid 类型要比 urowid 出现的早, 伴随着 Oracle 增加的新功能, 比如 索引组织表(IOT), 与其他类型数据库实现联通的网关, oracle 也退出了新的 rowid 类型, 因此也必须开发一个新的数据类型能够容纳这些rowid, 这就是 urowid数据类型, u代表通用的, 一个 urowid 变量可以用于任何类型表的任何rowid.

建议: 在所有新开发中都使用 urowid.

获得 rowid

declare
    employee_rowid    urowid;
    employee_salary    number;
begin
    select rowid, salary into employee_rowid, employee_salary
      from employees
     where last_name = 'Raphaely' AND first_name = 'Den';
    dbms_ouput.put_line('salary is : ' || employee_salary);
exception
    when no_data_found then
        dbms_output.put_line('There is no data what you want');
end;
/

使用 rowid

rowid 主要用于对一行数据的反复访问, 使用 rowid 作为查询条件, 可以跳过索引查找, 直接定位行, 速度奇快.

declare
    employee_rowid    urowid;
    employee_salary    number;
begin
    select rowid, salary into employee_rowid, employee_salary
      from employees
     where last_name = 'Raphaely' AND first_name = 'Den';

    update employees
        set salary = employee_salary
      where rowid = employee_rowid;

    dbms_output.put_line('salary modified: ' || employee_salary);
exception
    when no_data_found then
        dbms_output.put_line('There is no data what you want');
end;
/

危险, plsql 执行中, 如果多用户系统下, 某个用户对表进行了变更, 例如 dba 对表进行了联机重组, 那么这个rowid的值就会发生变化, 上面的plsql 执行的结果就是错误的, 所以更好的办法是使用显示游标提取数据, 然后使用 where current of cursor 语句来修改或者删除数据.

LOB 数据类型

可以声明为 LOB 变量 如下:

BFILE: 二进制文件. 这种变量的内容是一个指向数据库外部的操作系统文件的指针. 数据库把文件的数据按照二进制数据处理.

BLOB: 二进制大对象. 这种变量的内容也是一个指向保存在数据库内部的大二进制对象的指针.

CLOB: 字符型大对象. 这种变量保存的内容是一个指向保存在数据库内部的巨大的字符对象. 字符使用数据库字符集.

NCLOB: 国家字符集大对象, 同上, 只不过字符集使用的是国家字符集.

外部LOB(BFILE) 不参与到事务中, 换句话说, 我们不能把对 BFILE 的修改进行提交或者回滚.

使用 LOB

-- create table
create table waterfalls (
    falls_name    varchar2(80),
    falls_photo    BLOB,
    falls_directions    CLOB,
    falls_description    NCLOB,
    falls_web_page    BFILE
);

照片本身就是一个二进制文件, 所以定义为 BLOB 类型

理解 LOB 定位符( 指针 )

LOB 定位符是指向数据库中的大对象数据的指针.

declare
    photo blob;
begin
    select falls_photo
      into photo
      from waterfalls
     where falls_name = 'Dryer Hose';

这个 select 语句执行完毕, 这个 photo 变量到底是什么呢? 图片本身? 不, 只是一个指向要访问图片的指针.

注意, 大对象本身是保存在数据库内 BLOB 类型存储在数据库内, 但是 photo 变量本身只是一个指针.

要使用 LOB 数据, 我们必须先取出 LOB 定位符( 指针 ), 然后再通过内置的 DBMS_LOB 包来提取或者修改真正的 LOB 数据。流程可参考如下, 例子:

1. 用 select 语句取出要显示的图片的 LOB 定位符

2. 调用 DBMS_LOB.OPEN 打开LOB对象

3. 调用 DBMS_LOB.GETCHUNKSIZE 得到读写 LOB 值应该设置的最优的块大小

4. 调用 DBMS_LOG.GETLENGTH 得到 LOB 值得字节或者字符数量

5. 多次调用 DBMS_LOG.READ 取得 LOB 数据

6. 关闭 LOB

LOB 数据缺省不会在 Buffer Cache 中缓存, 也不会像普通数据一样产生 undo 信息, 不过 LOB 会像普通数据一样生成 redo 信息, 除非我们使用 NOLOGGING 选项.

LOB 变量, 我们在使用的时候要检查两项, 一个是 null , 另一个是 长度是 0 , 例如

vlob CLOB; -- 你定义了一个 clob 变量, 这时 vlob 是 null 的

vlob := EMPTY_CLOB();  这时的 vlob 已经不等于 null, 但是同样它也不可直接使用. 所以要判断两次, 如下:

IF some_clob IS NULL THEN
    -- 没有数据
ELSIF DBMS_LOB.GETLENGTH(some_lob) = 0 THEN
    -- 没有数据
ELSE
    -- 只有这样才有数据, 可以正常使用
END IF;

向 LOB 中写入数据

DBMS_LOB.WRITE : 向 LOB 中随机写入数据.

DBMS_LOB.WRITEAPPEND : 向 LOB 的末尾追加数据.

declare
    directions     CLOB;
    amount        BINARY_INTEGER;
    offset        INTEGER;
    first_direction    VARCHAR2(100);
    more_directions    VARCHAR2(500);
begin
    -- 先删除, 这样这个例子可以重复执行
    delete
      from waterfalls
     where falls_name = 'Munising Falls';

    -- 插入新的一行, 用 EMPTY_CLOB()创建一个LOB定位符
    insert into waterfalls(falls_name, falls_directions)
    values('Munising Falls', EMPTY_CLOB());

    -- 取出刚才这个 insert 语句创建的定位符
    select falls_directions
      into directions
      from waterfalls
     where falls_name = 'Munising Falls';

    -- 打开 LOB; 严格来说不是必须这么做, 不过最好这么做
    DBMS_LOB.OPEN(directions, DBMS_LOB.LOB_READWRITE);

    -- 开始 DBMS_LOB.WRITE
    first_direction := 'Follow I-75 across the Mackinac Birdge.';
    amount := LENGTH(first_direction);
    offset := 1;
    DBMS_LOB.WRITE(directions, amount, offset, first_direction);
    more_directions := 'asdfjdsajflkdasjklfdlkjsfkldas' ||
                       'fdajsfkldjasfldjlksafjkldsaljkfjldk' ||
                       'fdajsklfjdlajkfldjasklfjklda';
    DBMS_LOB.WRITEAPPEND(directions, LENGTH(more_directions), more_directions);

    -- 关闭 LOB, 结束工作
    DBMS_LOB.CLOSE(directions);
END;

读取 LOB 数据

读取数据, 与上面基本相同, 要使用 DBMS_LOB.READ 函数, 另外就是不需要 DBMS_LOB.OPEN 这个函数了.

declare
    directions     CLOB;
    directions_1        varchar2(300);
    directions_2        varchar2(300);
    chars_read_1        binary_integer;
    chars_read_2        binary_integer;
    offset                 integer;
begin

    -- 取出刚才这个 insert 语句创建的定位符
    select falls_directions
      into directions
      from waterfalls
     where falls_name = 'Munising Falls';

    offset := 1;
    chars_read_1 := 10;
    DBMS_LOB.READ(directions, chars_read_1, offset, directions_1);
    IF chars_read_1 = 10 then
        offset := offset + char_read_1;
        chars_read_2 := 50;
        dbms_lob.read(directions, chars_read_2, offset, directions_2);
    else
        chars_read_2 := 0;
        directions_2 := '';
    end if;

    dbms_output.put_line('Characters read = ' || to_char(chars_read_1 + chars_read_2));
    dbms_output.put_line(directions_1);
    dbms_output.put_line(directions_2);
END;
/

BFILE 有所不同, 因为它的实际文件存储在操作系统上, 一般而言, 在 PL/SQL 和 oracle 数据库内部, 我们对BFILE只能读取. 但是我们要读取 BFILE, 我们同样需要定位符指针, 这个指针由一个目录别名和文件名组成.

create directory bfile_data as ‘c:plsql bookCh13’ 这个就是一个目录别名.

grant read on directory bfile_data to hr;

一个BFILE定位符只不过是目录别名和文件名的组合.

创建一个 bfile 定位符

declare
    web_page    bfile;
begin
    -- 调用 bfilename 函数创建 bfile定位符
    web_page := bfilename('bfile_data', 'document.htm');

一旦我们拥有了定位符, 我们就可以用和 BLOB 一样的方式访问外部文件.

另外还有一些, xml 类型, any 类型, url 类型, 感觉不常用, 忽略了.