再上一篇:12.3二进制串:RAW类型
上一篇:12.4数值类型
主页
下一篇:12.6DATE、TIMESTAMP和 INTERVAL类型
再下一篇:12.7 LOB类型
文章列表

12.5LONG类型

Oracle 9i 10g编程艺术:深入数据库体系结构

Oracle中的LONG类型有两种:
LONG文本类型,能存储2GB的文本。与VARCHAR2或CHAR类型一样,存储在LONG类型中的 文本要进行字符集转换。
LONG RAW类型,能存储2GB的原始二进制数据(不用进行字符集转换的数据)。
从Oracle 6开始就已经支持LONG类型,那时限制为只能存储64KB的数据。在Oracle 7中,提升为 可以存储多达2GB的数据,但是等发布Oracle 8时,这种类型被LOB类型所取代,稍后将讨论LOB类型。
在此并不解释如何使用LONG类型,而是会解释为什么你不希望在应用中使用 LONG(或LONG RAW)类 型。首先要注意的是,Oracle文档在如何处理LONG类型方面描述得很明确。Oracle SQL Reference手册 指出:
不要创建带LONG列的表,而应该使用LOB列(CLOB、NCLOB、BLOB)。支持 LONG列只是为了保证向后 兼容性。

12.5.1LONG和 LONG RAW类型的限制

LONG和LONG RAW类型存在很多限制,如表12.-2所列。尽管这可能有点超前(现在还没有讲到LOB 类型),不过在此我还是增加了一列,指出相应的 LOB类型(用以取代LONG/LONG RAW)类型是否也有同样 的限制。
表12.-2 LONG类型与LOB类型的比较
LONG/LONG RAW类型 CLOB/BLOB类型

每个表中只能有一个 LONG 或 LONG RAW列 每个表可以有最多 12.000个 CLOB 或 BLOB类型的列 定义用户定义的类型时,不能有 LONG/LONG 用户定义的类型完成可以使用 CLOB 和 BLOB类型 RAW类型的属性

不能在 WHERE子句中引用 LONG类型 WHERE子句中可以引用 LOB类型,而且 DBMS_LOB包

中提供了大量函数来处理 LOB类型

除了 NOT NULL之外,完整性约束中不能引用 完整性约束中可以引用 LOB类型 LONG类型

LONG类型不支持分布式事务 LOB确实支持分布式事务 LONG类型不能使用基本或高级复制技术来复制 LOB完全支持复制

LONG列不能在 GROUP BY、ORDER BY或 只要对 LOB应用一个函数,将其转换为一个标量 SQL类型, CONNECT BY子句中引用,也不能在使用了 如 VARCHAR2、NUMBER或 DATE,LOB就可以出现在 DISTINCT、UNIQUE、INTERSECT、MINUS 这些子句中

或 UNION的查询中使用

PL/SQL 函数/过程不能接受 LONG类型的输入 PL/SQL可以充分处理 LOB类型 SQL内置函数不能应用于 LONG列(如 SUBSTR) SQL函数可以应用于 LOB类型

CREATE TABLE AS SELECT 语句中不能使用 LOB支持 CREATE TABLE AS SELECT LONG类型

在包含 LONG类型的表上不能使用 ALTER TABLE MOVE 可以移动包含 LOB的表

可以看到,表12.-2 很长;如果表中有一个LONG列,那么很多事情都不能做。对于所有新的应用, 甚至根本不该考虑使用LONG类型。相反,应该使用适当的LOB类型。对于现有的应用,如果受到表 12.-2 所列的某个限制,就应该认真地考虑将LONG类型转换为相应的LOB类型。由于已经做了充分考虑来提供向 后兼容性,所以编写为使用LONG类型的应用也能透明地使用LOB类型。
注意 无须多说,将生产系统从LONG修改为LOB类型之前,应当对你的应用执行一个全面的字典测试 。

12.5.2处理遗留的LONG类型


经常会问到这样一个问题:“那如何考虑Oracle中的数据字典呢?“数据字典中散布着LONG列,这 就使得字典列的使用很成问题。例如,不能使用SQL搜索ALL_VIEWS字典视图来找出包含文本HELLO的所 有视图:
ops$tkyte@ORA10G> select *
2 from all_views
3 where text like '%HELLO%';
where text like '%HELLO%'
*
ERROR at line 3:


ORA-00932: inconsistent datatypes: expected NUMBER got LONG
这个问题并不只是ALL_VIEWS视图才有,许多视图都存在同样的问题:
ops$tkyte@ORA10G> select table_name, column_name
2 from dba_tab_columns
3 where data_type in ( 'LONG', 'LONG RAW' )
4 and owner = 'SYS'
5 and table_name like 'DBA%';
TABLE_NAME COLUMN_NAME
------------------------------ ----------------
--------------
DBA_VIEWS TEXT DBA_TRIGGERS TRIGGER_BODY DBA_TAB_SUBPARTITIONS HIGH_VALUE DBA_TAB_PARTITIONS HIGH_VALUE DBA_TAB_COLUMNS DATA_DEFAULT DBA_TAB_COLS DATA_DEFAULT DBA_SUMMARY_AGGREGATES MEASURE
DBA_SUMMARIES QUERY DBA_SUBPARTITION_TEMPLATES HIGH_BOUND DBA_SQLTUNE_PLANS OTHER DBA_SNAPSHOTS QUERY DBA_REGISTERED_SNAPSHOTS QUERY_TXT DBA_REGISTERED_MVIEWS QUERY_TXT
DBA_OUTLINES SQL_TEXT

DBA_NESTED_TABLE_COLS DATA_DEFAULT DBA_MVIEW_ANALYSIS QUERY DBA_MVIEW_AGGREGATES MEASURE
DBA_MVIEWS QUERY DBA_IND_SUBPARTITIONS HIGH_VALUE DBA_IND_PARTITIONS HIGH_VALUE DBA_IND_EXPRESSIONS COLUMN_EXPRESSION DBA_CONSTRAINTS SEARCH_CONDITION
DBA_CLUSTER_HASH_EXPRESSIONS HASH_EXPRESSION
那么问题到底出在哪里呢?如果你想在SQL中使用这些列,就需要将它们转换为一种对SQL友好的类

型。可以使用一个用户定义的函数来做到这一点。以下例子展示了如何使用一个LONG SUBSTR函数来达到 这个目的,这个函数允许将任何4,000字节的LONG类型转换为一个VARCHAR2,以便用于SQL。完成后,就 能执行以下查询:
ops$tkyte@ORA10G> select *
2 from (
3 select owner, view_name,
4 long_help.substr_of( 'select text
5 from dba_views
6 where owner = :owner
7 and view_name = :view_name',
8 1, 4000,
9 'owner', owner,
12. 'view_name', view_name ) substr_of_view_text
12. from dba_views
12. where owner = user
12. )

12. where upper(substr_of_view_text) like '%INNER%'
12. /
你已经将VIEW_TEXT列的前4,000字节由LONG转换为VARCHAR2,现在可以对它使用谓词了。使用同
样的技术,你还可以对LONG类型实现你自己的INSTR、LIKE函数。在这本书里,我只想说明如何得到一个 LONG类型的子串。

我们要实现的包有以下规范:
ops$tkyte@ORA10G> create or replace package long_help
2 authid current_user
3 as
4 function substr_of
5 ( p_query in varchar2,
6 p_from in number,
7 p_for in number,
8 p_name12.in varchar2 default NULL,
9 p_bind12.in varchar2 default NULL,
12. p_name2 in varchar2 default NULL,
12. p_bind2 in varchar2 default NULL,
12. p_name3 in varchar2 default NULL,
12. p_bind3 in varchar2 default NULL,
12. p_name4 in varchar2 default NULL,
12. p_bind4 in varchar2 default NULL )
12. return varchar2;
12. end;
12. /
Package created.
注意在第 2行上,我们指定了AUTHID CURRENT_USER。 这使得这个包会作为调用者运行,拥有所有
角色和权限。这一点很重要,原因有两个。首先,我们希望数据库安全性不要受到破坏,这个包只返回允 许我们(调用 者)看到的列子串。其次,我们希望只在数据库中将这个包安装一次,就能一直使用它的功 能;使用调用者权限可以保证这一点。如果我们使用PL/SQL的默认安全模型(定义者权限,define right), 这个包会以所有者的权限来运行,这样一来,它就只能看到包所有者能看到的数据,这可能不包括允许调 用者看到的数据集。
函数SUBSTR_OF的基本思想是取一个查询,这个查询最多只选择一行和一列:即我们感兴趣的LONG 值。如果需要,SUBSTR_OF会解析这个查询,为之绑定输入,并通过查询获取结果,返回 LONG值中必要的 部分。

包体(实现)最前面声明了两个全局变量。G_CURSOR变量保证一个持久游标在会话期间一直打开。 这是为了避免反复打开和关闭游标,并避免不必要地过多解析SQL。第二个全局变量G_QUERY用于记住这 个包中已解析的上一个SQL查询的文本。只要查询保持不变,就只需将其解析一次。因此,即使一个查询 中查询了5,000 行,只要我们传入这个函数的SQL查询不变,就只会有一个解析调用:
ops$tkyte@ORA10G> create or replace package body long_help
2 as
3
4 g_cursor number := dbms_sql.open_cursor;
5 g_query varchar2(32765);
6
这个包中接下来是一个私有过程:BIND_VARIABLE,我们用这个过程来绑定调用者传入的输入。在此

把它实现为一个单独的私有过程,这只是为了更容易一些;我们希望只在输入名不为NULL(NOT NULL)时 才绑定。并非在代码中对输入参数执行4次检查,而是只在这个过程中执行1次检查:
7 procedure bind_variable( p_name in varchar2, p_value in varchar2 )
8 is
9 begin
12. if ( p_name is not null )
12. then
12. dbms_sql.bind_variable( g_cursor, p_name, p_value );
12. end if;
12. end;
12.

下面是包体中SUBSTR_OF的具体实现。这个例程首先是包规范中指定的一个函数声明,以及一些局部 变量的声明。L_BUFFER用于返回值,L_BUFFER_LEN用于保存长度(这是由一个Oracle提供的函数返回的):
12.
12. function substr_of
12. ( p_query in varchar2,
12. p_from in number,
20 p_for in number,
21 p_name12.in varchar2 default NULL,
22 p_bind12.in varchar2 default NULL,
23 p_name2 in varchar2 default NULL,
24 p_bind2 in varchar2 default NULL,
25 p_name3 in varchar2 default NULL,
26 p_bind3 in varchar2 default NULL,
27 p_name4 in varchar2 default NULL,
28 p_bind4 in varchar2 default NULL )
29 return varchar2
30 as
31 l_buffer varchar2(4000);
32 l_buffer_len number;
33 begin
现在,代码所做的第一件事是对P_FROM和P_FOR输入执行一个合理性检查(sanity check)。P_FROM

必须是一个大于或等于1 的数,P_FOR必须介于1~4,000之间,这与内置函数SUBSTR的参数限制是类似 的:
34 if ( nvl(p_from,0) <= 0 )
35 then
36 raise_application_error

37 (-20002, 'From must be >= 1 (positive numbers)' );
38 end if;
39 if ( nvl(p_for,0) not between 12.and 4000 )
40 then
41 raise_application_error
42 (-20003, 'For must be between 12.and 4000' );
43 end if;
44
接下来,我们要查看是否得到一个需要解析的新查询。如果已解析的上一个查询与当前查询相同,就

可以跳过这一步。必须指出有一点很重要,在第47行上验证传入的P_QUERY必须是一个SELECT,我们只 用这个包执行SQL SELECT语句。以下检查为我们完成了这个验证:
45 if ( p_query <> g_query or g_query is NULL )
46 then
47 if ( upper(trim(nvl(p_query,'x'))) not like 'SELECT%')
48 then
49 raise_application_error
50 (-20001, 'This must be a select only' );
51 end if;
52 dbms_sql.parse( g_cursor, p_query, dbms_sql.native );
53 g_query := p_query;
54 end if;
我们已经准备好将输入绑定到这个查询。插入的所有非NULL名都会“绑定”到查询,所以当执行查

询时,它会找到正确的行:
55 bind_variable( p_name1, p_bind1 );
56 bind_variable( p_name2, p_bind2 );
57 bind_variable( p_name3, p_bind3 );

58 bind_variable( p_name4, p_bind4 );
59
现在执行查询,并获取行。然后使用 DBMS_SQL.COLUMN_VALUE_LONG,从而抽取出LONG中必要的子串 ,

并将其返回:
60 dbms_sql.define_column_long(g_cursor, 1);
61 if (dbms_sql.execute_and_fetch(g_cursor)>0)
62 then
63 dbms_sql.column_value_long
64 (g_cursor, 1, p_for, p_from-1,
65 l_buffer, l_buffer_len );
66 end if;
67 return l_buffer;
68 end substr_of;
69
70 end;
71 /
Package body created.
大功告成,你现在可以对数据库中如何遗留的LONG列使用这个包,这样就能执行很多以前不可能执

行的WHERE 子句操作。例如,现在你可以找出模式中HIGH_VALUE包含2003年的所有分区:
ops$tkyte@ORA10G> select *
2 from (
3 select table_owner, table_name, partition_name,
4 long_help.substr_of
5 ( 'select high_value
6 from all_tab_partitions
7 where table_owner = :o

8 and table_name = :n
9 and partition_name = :p',
12. 1, 4000,
12. 'o', table_owner,
12. 'n', table_name,
12. 'p', partition_name ) high_value
12. from all_tab_partitions
12. where table_name = 'T'
12. and table_owner = user
12. )
12. where high_value like '%2003%' TABLE_OWN TABLE PARTIT HIGH_VALUE
--------- ----- ------ ---------------------------
---
OPS$TKYTE T PART1 TO_DATE(' 2003-03-12. 00:00:00'
HH24:MI:SS', 'N
, 'SYYYY-MM-DD
RIAN')
LS_CALENDAR=GREGO
OPS$TKYTE T PART2 TO_DATE(' 2003-03-12. 00:00:00'
HH24:MI:SS', 'N
, 'SYYYY-MM-DD
RIAN')
LS_CALENDAR=GREGO
通过使用同样的技术,即取一个返回一行和一列(LONG列)的查询,并在一个函数中处理该查询的
结果,你就能根据需要实现自己的INSTR、LIKE等函数。
这个实现在LONG类型上能很好地工作,但是在LONG RAW类型上却不能工作。LONG RAW不能分段地 访问(DBMS_SQL包中没有COLUMN_VALUE_LONG_RAW之类的函数)。幸运的是,这个限制不算太严重,因为
字典中没有用LONG RAW,而且很少需要对LONG RAW“取子串”来完成搜索。不过,如果确实需要这么做, 你可能根本不会使用PL/SQL来实现,除非LONG RAW小于或等于32KB,因为PL/SQL本身中没有任何方法 来处理超过32KB的LONG RAW。对此,必须使用Java、C、C++、Visual Basic或某种其他语言。

还有一种方法,可以使用TO_LOB内置函数和一个全局临时表,将LONG或LONG RAW临时地转换为CLOB 或BLOB。为此,PL/SQL过程可以如下:
Insert into global_temp_table ( blob_column )
select to_lob(long_raw_column) from t where...
这在偶尔需要处理单个LONG RAW值的应用中能很好地工作。不过,你可能不希望不断地这样做,原
因是为此需要做的工作太多了。如果你发现自己需要频繁地求职于这种技术,就应该干脆将LONG RAW一次 性转换为BLOB,然后处理BLOB。