关于long类型的转换(r3笔记第84天)

在oracle中对于long类型的处理时很纠结的。最开始引入这个数据类型的时候是对原有数据类型的补充,但是后面发现还是碰到了一些问题,使用Lob类型代替了。但是long类型从兼容性上来说还得支持,而且从数据库的数据字典中还是能够看到Long类型的影子。 比如我们想查看一个表中某个列的默认值情况,可以查询user_tab_cols,或者dba_tab_cols等,字段data_default是Long类型。

SQL>  desc user_tab_cols
 Name                                      Null?     Type
 ----------------------------------------- --------  ----------------------------
 TABLE_NAME                                NOT  NULL VARCHAR2(30)
 COLUMN_NAME                               NOT NULL  VARCHAR2(30)
 DATA_TYPE                                           VARCHAR2(106)
 DATA_TYPE_MOD                                       VARCHAR2(3)
 DATA_TYPE_OWNER                                     VARCHAR2(120)
 DATA_LENGTH                               NOT NULL  NUMBER
 DATA_PRECISION                                      NUMBER
 DATA_SCALE                                          NUMBER
 NULLABLE                                            VARCHAR2(1)
 COLUMN_ID                                           NUMBER
 DEFAULT_LENGTH                                     NUMBER
 DATA_DEFAULT                                        LONG
 NUM_DISTINCT                                        NUMBER
 LOW_VALUE                                           RAW(32)
 HIGH_VALUE                                          RAW(32)
 DENSITY                                             NUMBER
 NUM_NULLS                                           NUMBER
 NUM_BUCKETS                                         NUMBER
 LAST_ANALYZED                                       DATE
 SAMPLE_SIZE                                         NUMBER
 CHARACTER_SET_NAME                                  VARCHAR2(44)
 CHAR_COL_DECL_LENGTH                                NUMBER
 GLOBAL_STATS                                        VARCHAR2(3)
 USER_STATS                                          VARCHAR2(3)
 AVG_COL_LEN                                         NUMBER
 CHAR_LENGTH                                         NUMBER
 CHAR_USED                                           VARCHAR2(1)
 V80_FMT_IMAGE                                       VARCHAR2(3)
 DATA_UPGRADED                                       VARCHAR2(3)
 HIDDEN_COLUMN                                       VARCHAR2(3)
 VIRTUAL_COLUMN                                      VARCHAR2(3)
 SEGMENT_COLUMN_ID                                   NUMBER
 INTERNAL_COLUMN_ID                        NOT NULL  NUMBER
 HISTOGRAM                                           VARCHAR2(15)
 QUALIFIED_COL_NAME                                  VARCHAR2(4000)

如果想使用Like来模糊匹配或者重新创建一个临时表,都会碰到Long类型的问题。

select *from user_tab_cols where data_default like  'a%'
                                 *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected  NUMBER got LONG 
SQL> create table t1 as select *from user_tab_cols;
create table t1 as  select *from user_tab_cols
                          *
ERROR at line  1:
ORA-00997: illegal use of LONG  datatype 

既然不支持,Oracle也提供了对应的一些方法来满足我们的需要。 在thomas kyte的书中,对这种实现方法做了详细的解释。 使用的代码如下,基本就是把Long类型转换为varchar2,按照每批4000个字节的容量进行转换。

create  or replace package long_help authid current_user as function substr_of(p_query  in varchar2, p_from in number, p_for in number, p_name1 in varchar2 default  NULL, p_bind1 in varchar2 default NULL, p_name2 in varchar2 default NULL,  p_bind2 in varchar2 default NULL, p_name3 in varchar2 default NULL, p_bind3 in  
varchar2 default NULL, p_name4 in varchar2 default NULL, p_bind4 in varchar2  default NULL) return varchar2;
end;
/
create or replace package body  long_help as
  g_cursor number := dbms_sql.open_cursor;
  g_query   varchar2(32765);
  procedure bind_variable(p_name in varchar2, p_value in  varchar2) is
  begin 
  if (p_name is not null) then  dbms_sql.bind_variable(g_cursor, p_name, p_value);
  end if;
    end;
 function substr_of(p_query in varchar2, p_from in number, p_for in  number, p_name1 in varchar2 default NULL, p_bind1 in varchar2 default NULL,  p_name2 in varchar2 default NULL, p_bind2 in varchar2 default NULL, p_name3 in  varchar2 default NULL, p_bind3 in varchar2 default NULL, p_name4 in varchar2  default NULL, p_bind4 in varchar2 default NULL) return varchar2 as
l_buffer  varchar2(4000); l_buffer_len number;
begin
if (nvl(p_from, 0) <= 0)  then raise_application_error(-20002, 'From must be >= 1 (positive  numbers)');
end if; 
if (nvl(p_for, 0) not between 1 and 4000) then  raise_application_error(-20003, 'For must be between 1 and 4000');
end if;  
if (p_query <> g_query or g_query is
NULL) then if  (upper(trim(nvl(p_query, 'x'))) not like 'SELECT%') then  raise_application_error(-20001, 'This must be a select only');
end if;  
dbms_sql.parse(g_cursor, p_query, dbms_sql.native); g_query :=  p_query;
end if;
 bind_variable(p_name1, p_bind1); bind_variable(p_name2,  p_bind2); bind_variable(p_name3, p_bind3); bind_variable(p_name4, p_bind4);  dbms_sql.define_column_long(g_cursor, 1); if  (dbms_sql.execute_and_fetch(g_cursor) > 0) then  dbms_sql.column_value_long(g_cursor, 1, p_for, p_from - 1, l_buffer,  l_buffer_len);
end if; 
return l_buffer;
end  substr_of;
end;
/

这个时候我们想查询data_default的值就可以使用如下的sql

SELECT  *
  FROM (SELECT OWNER,
               TABLE_NAME,
                COLUMN_NAME,
               DATA_TYPE,
                LONG_HELP.SUBSTR_OF('SELECT data_default FROM   DBA_TAB_COLS WHERE OWNER=:OWNER   AND TABLE_NAME=:TABLE_NAME AND  COLUMN_NAME=:COLUMN_NAME',
                                    1,
                                    4000,
                                    'OWNER',
                                    OWNER,
                                    'TABLE_NAME',
                                    TABLE_NAME,
                                    'COLUMN_NAME',
                                   COLUMN_NAME)  DATA_DEFAULT
          FROM  DBA_TAB_COLS);

查询结果如下:

OWNER                 TABLE_NAME                     COLUMN_NAME                     DATA_TYPE                      DATA_DEFAULT
--------------------  ------------------------------ ------------------------------  ------------------------------  ------------------------------
SYS                   RECO_SCRIPT_BLOCK$             CTIME                           DATE                           SYSDATE
SYS                   RECO_SCRIPT_BLOCK$             SPARE1                          NUMBER
SYS                  RECO_SCRIPT_BLOCK$              SPARE2                         NUMBER
SYS                   RECO_SCRIPT_BLOCK$             SPARE3                          NUMBER
SYS                  RECO_SCRIPT_BLOCK$              SPARE4                         VARCHAR2
SYS                   RECO_SCRIPT_BLOCK$             SPARE5                          VARCHAR2
SYS                  RECO_SCRIPT_BLOCK$              SPARE6                         DATE
SYS                   STREAMS$_COMPONENT_LINK        SOURCE_COMPONENT_ID             NUMBER
SYS                  STREAMS$_COMPONENT_LINK         DEST_COMPONENT_ID              NUMBER
SYS                   STREAMS$_COMPONENT_LINK        PATH_ID                         NUMBER
SYS                  STREAMS$_COMPONENT_LINK         POSITION                       NUMBER

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-12-14

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏逸鹏说道

sql server之数据库语句优化

一切都是为了性能,一切都是为了业务 一、查询的逻辑执行顺序 (1) FROM left_table (3) join_type JOIN right_table...

3657
来自专栏乐沙弥的世界

使用exchange方式切换普通表到分区表

      随着数据库数据量的不断增长,有些表需要由普通的堆表转换为分区表的模式。有几种不同的方法来对此进行操作,诸如导出表数据,然后创建分区表再导入数据到分区...

731
来自专栏杨建荣的学习笔记

关于物化视图疑问(32天)

--初始化操作, 创建两个用户一个,testo,一个test. 在testo上创建表,test上创建物化视图。 SQL> create user testo ...

3595
来自专栏Aloys的开发之路

DB2常用语句

CREATE TABLE STAFF_BAK LIKE STAFF; INSERT INTO STAFF_BAK SELECT * FROM STAFF; S...

1985
来自专栏沃趣科技

SQL优化案例-分区索引之无前缀索引(六)

无前缀索引:分区索引不包含分区字段就叫无前缀索引,那么什么时候用无前缀索引和前缀索引呢?

1302
来自专栏自由而无用的灵魂的碎碎念

oracle 10g 手动创建scott(tiger) schema

转自:http://cnhtm.itpub.net/post/39970/496967

1203
来自专栏Albert陈凯

理解SQL原理SQL调优你必须知道的10条铁律

原文地址: http://www.nowamagic.net/librarys/veda/detail/1502 我们做软件开发的,大部分人都离不开跟数据库打...

3895
来自专栏xiaoheike

为什么 EXISTS(NOT EXIST) 与 JOIN(LEFT JOIN) 的性能会比 IN(NOT IN) 好

网络上有大量的资料提及将 IN 改成 JOIN 或者 exist,然后修改完成之后确实变快了,可是为什么会变快呢?IN、EXIST、JOIN 在 MySQL 中...

4924
来自专栏程序员宝库

Mysql - JOIN 详解

一个完整的SQL语句中会被拆分成多个子句,子句的执行过程中会产生虚拟表(vt),但是结果只返回最后一张虚拟表。从这个思路出发,我们试着理解一下JOIN查询的执行...

3933
来自专栏杨建荣的学习笔记

MySQL和Oracle对比学习之数据字典元数据(r4笔记第33天)

MySQL和Oracle虽然在架构上有很大的不同,但是如果从某些方面比较起来,它们有些方面也是相通的。 毕竟学习的主线是MySQL,所以会从MySQL的角度来对...

3006

扫码关注云+社区

领取腾讯云代金券