Oracle数据库LONG类型移植

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

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

   --建表
   
   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

    

2. 带有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

    

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