Oracle数据库LONG类型移植


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

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

    --建表

    CREATE TABLE TEST_LONGTYPE_TABLE(

           ID VARCHAR2(20) NOT NULL,

           TEXT_LONG LONG,

           TEXT_CLOB CLOB

    )

    --直接显示更改类型，注意此种办法是不可逆的（变LONG为CLOB后不可以再改回来）

    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

    1. 带有LONG类型列不能使用UNION但可以使用UNION ALL
    1. 合法

    SELECT * FROM TEST_LONGTYPE_TABLE UNION ALL SELECT * FROM TEST_LONGTYPE_TABLE

    非法

    SELECT * FROM TEST_LONGTYPE_TABLE UNION SELECT * FROM TEST_LONGTYPE_TABLE

    1. 在存储过程中使用了LONG
    1. 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;

    /

    • 相关内容