Oracle数据库LONG类型移植

龚娅超

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

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

  --建表 CREATE TABLETEST_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 INTOTEST_LONGTYPE_TABLE(ID, TEXT_LONG) VALUES('001', 'LONG类型'); --用TO_LOB转换后数据放到CLOB中 INSERT INTOTEST_LONGTYPE_TABLE(ID, TEXT_CLOB) SELECT ID, TO_LOB(TEXT_LONG) FROMTEST_LONGTYPE_TABLE

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

  合法 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; /