一步一个脚印的专栏

一步一个脚印

Oracle数据库LONG类型移植

LONG类型字段很原始使用时限制也很多,与LONG类型相对应的类型有CLOBLONG类型虽然已经不建议使用但还是可以发现在Oracle数据字典相关的视图中还是有LONG类型的身影。

LONG类型的移植
  1. 可以直接通过DDL方式显示的修改成LONG类型:
  2. 可以通过TO_LOB相关函数,将原始LONG类型的列通过TO_LOB处理后放到新的列,再把LONG类型的列删除。
  3. 相关SQ

--建表

CREATE TABLE TEST_LONGTYPE_TABLE(

       ID VARCHAR2(20) NOT NULL,

       TEXT_LONG LONG,

       TEXT_CLOB CLOB

)

--直接显示更改类型,注意此种办法是不可逆的(变LONGCLOB后不可以再改回来)

ALTER TABLE TEST_LONGTYPE_TABLE MODIFY TEXT_LONG CLOB;

--LONG的列插入数据

INSERT INTO TEST_LONGTYPE_TABLE(ID, TEXT_LONG) VALUES('001', 'LONG类型');

--TO_LOB转换后数据放到CLOB

INSERT INTO TEST_LONGTYPE_TABLE(ID, TEXT_CLOB) SELECT ID, TO_LOB(TEXT_LONG) FROM TEST_LONGTYPE_TABLE

带有LONG类型列不能使用UNION但可以使用UNION ALL

合法

SELECT * FROM TEST_LONGTYPE_TABLE UNION ALL SELECT * FROM TEST_LONGTYPE_TABLE

非法

SELECT * FROM TEST_LONGTYPE_TABLE UNION SELECT * FROM TEST_LONGTYPE_TABLE

在存储过程中使用了LONG

CREATE OR REPLACE PROCEDURE WYC_LONGTYPE_TEST IS

  A1_LONG LONG;

  A2_LONG LONG;

BEGIN

  FOR C IN (SELECT A_DATADF, B_DATADF

              FROM (SELECT A.TABLE_NAME,

                           A.COLUMN_NAME,

                           A.DATA_TYPE      A_DT,

                           B.DATA_TYPE      B_DT,

                           A.DATA_LENGTH    A_DLEN,

                           B.DATA_LENGTH    B_DLEN,

                           A.DATA_PRECISION A_DPREC,

                           B.DATA_PRECISION B_DPREC,

                           A.DATA_SCALE     A_DSCA,

                           B.DATA_SCALE     B_DSCA,

                           A.NULLABLE       A_NULLA,

                           B.NULLABLE       B_NULLA,

                           A.DEFAULT_LENGTH A_DEFLEN,

                           B.DEFAULT_LENGTH B_DEFLEN,

                           A.DATA_DEFAULT   A_DATADF,

                           B.DATA_DEFAULT   B_DATADF

                      FROM (SELECT TABLE_NAME,

                                   COLUMN_NAME,

                                   DATA_TYPE,

                                   DATA_LENGTH,

                                   DATA_PRECISION,

                                   DATA_SCALE,

                                   NULLABLE,

                                   DEFAULT_LENGTH,

                                   DATA_DEFAULT

                              FROM ALL_TAB_COLS ACT1

                             WHERE OWNER = 'USER_A'

                               AND EXISTS

                             (SELECT 1

                                      FROM ALL_TAB_COLS ATC2

                                     WHERE ATC2.OWNER = 'USER_B'

                                       AND ATC2.TABLE_NAME = ACT1.TABLE_NAME)) A,

                           (SELECT TABLE_NAME,

                                   COLUMN_NAME,

                                   DATA_TYPE,

                                   DATA_LENGTH,

                                   DATA_PRECISION,

                                   DATA_SCALE,

                                   NULLABLE,

                                   DEFAULT_LENGTH,

                                   DATA_DEFAULT

                              FROM ALL_TAB_COLS ACT1

                             WHERE OWNER = 'USER_B'

                               AND EXISTS

                             (SELECT 1

                                      FROM ALL_TAB_COLS ATC2

                                     WHERE ATC2.OWNER = 'USER_A'

                                       AND ATC2.TABLE_NAME = ACT1.TABLE_NAME)) B

                     WHERE A.TABLE_NAME = B.TABLE_NAME

                       AND A.COLUMN_NAME = B.COLUMN_NAME) C

             WHERE C.A_DEFLEN <> C.B_DEFLEN) LOOP

             A1_LONG := C.A_DATADF;

             A2_LONG := C.B_DATADF;

    DBMS_OUTPUT.put_line('Step_1_A1_LONG/A2_LONG 长度:' || LENGTH(A1_LONG) || '/' || LENGTH(A2_LONG));

             A1_LONG := TRIM(A1_LONG);

             A2_LONG := TRIM(A2_LONG);

    DBMS_OUTPUT.put_line('Step_2_A1_LONG/A2_LONG 长度:' || LENGTH(A1_LONG) || '/' || LENGTH(A2_LONG));        

    DBMS_OUTPUT.put_line('Step_3_A1_LONG/A2_LONG 内容:' || TO_CHAR(A1_LONG) || '/' || TO_CHAR(A2_LONG));

    IF (C.A_DATADF = C.B_DATADF) THEN

      DBMS_OUTPUT.put_line('相等性');

    ELSE

      DBMS_OUTPUT.put_line('不相等');

    END IF;

  END LOOP;

END WYC_LONGTYPE_TEST;

/

 

阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/ybygjy/article/details/6871667
个人分类: 4001_数据库
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭