ORACLE 表去掉空格的函數

CREATE OR REPLACE PROCEDURE ZZSJ.TRIM_TABLE(V_TABLE_NAME IN VARCHAR2) IS
BEGIN
  DECLARE
    OUT_STRING VARCHAR2(9999);
  BEGIN
    IF V_TABLE_NAME IS NOT NULL THEN
      BEGIN
        --CREATE TABLE
        SELECT SQL_CREATE
          INTO OUT_STRING
          FROM V_TRIM_SYBASE
         WHERE TABLE_NAME = UPPER(V_TABLE_NAME)
           AND ROWNUM = 1;
        EXECUTE IMMEDIATE 'CREATE TABLE ' || OUT_STRING ||'';
        -- DROP TABLE
        SELECT SQL_DROP
          INTO OUT_STRING
          FROM V_TRIM_SYBASE
         WHERE TABLE_NAME = UPPER(V_TABLE_NAME)
           AND ROWNUM = 1;
        EXECUTE IMMEDIATE 'DROP TABLE ' || OUT_STRING ||'';
        -- RENAME
        SELECT SQL_RENAME
          INTO OUT_STRING
          FROM V_TRIM_SYBASE
         WHERE TABLE_NAME = UPPER(V_TABLE_NAME)||'_TEMP'
           AND ROWNUM = 1;
        EXECUTE IMMEDIATE 'RENAME ' || OUT_STRING ||'';
      END;
    END IF;
    IF V_TABLE_NAME IS NULL THEN
      FOR CUR_TABLE IN (SELECT TABLE_NAME FROM V_TRIM_SYBASE) LOOP
        BEGIN
          --CREATE TABLE
          SELECT SQL_CREATE
            INTO OUT_STRING
            FROM V_TRIM_SYBASE
           WHERE TABLE_NAME = UPPER(CUR_TABLE.TABLE_NAME)
             AND ROWNUM = 1;
          EXECUTE IMMEDIATE 'CREATE TABLE ' || OUT_STRING ||'';
          --DROP TABLE
          SELECT SQL_DROP
            INTO OUT_STRING
            FROM V_TRIM_SYBASE
           WHERE TABLE_NAME = UPPER(CUR_TABLE.TABLE_NAME)
             AND ROWNUM = 1;
          EXECUTE IMMEDIATE 'DROP TABLE ' || OUT_STRING ||'';
          --RENAME
          SELECT SQL_RENAME
            INTO OUT_STRING
            FROM V_TRIM_SYBASE
           WHERE TABLE_NAME = UPPER(CUR_TABLE.TABLE_NAME)||'_TEMP'
             AND ROWNUM = 1;
          EXECUTE IMMEDIATE 'RENAME ' || OUT_STRING ||'';
        END;
      END LOOP;
    END IF;
  END;
END TRIM_TABLE;

===============================================
 CREATE OR REPLACE VIEW ZZSJ.V_TRIM_SYBASE AS
SELECT TABLE_NAME, owner||'.'||TABLE_NAME ||'_TEMP  AS SELECT '||fun_get_columns(upper('zzsj'),table_name)||' FROM
'||owner||'.'||TABLE_NAME SQL_CREATE,
owner||'.'||TABLE_NAME ||' PURGE' SQL_DROP, TABLE_NAME ||' TO '||substr(TABLE_NAME,1,instr(TABLE_NAME,'_TEMP')-1) SQL_RENAME   FROM all_tables WHERE owner=upper('zzsj')

===================================================

CREATE OR REPLACE FUNCTION FUN_GET_COLUMNS(/*V_OWNER      IN VARCHAR2,*/
                                           V_TABLE_NAME IN VARCHAR2)
  RETURN VARCHAR2 IS
  S_TEMP VARCHAR2(2000);
BEGIN
    S_TEMP := '';
/*  IF UPPER(V_OWNER) = 'HZSJ' THEN*/
    FOR CUR_COLUMN IN (SELECT  COLUMN_NAME
                         FROM All_Tab_Columns
                        WHERE table_name = LOWER(V_TABLE_NAME);
                       ) LOOP
      S_TEMP := S_TEMP || 'trim("' || LOWER(CUR_COLUMN.COLUMN_NAME) ||
                '") ' || CUR_COLUMN.COLUMN_NAME || ',';
    END LOOP;
    RETURN SUBSTR(S_TEMP, 1, LENGTH(S_TEMP) - 1);
  /*
  ELSIF UPPER(V_OWNER) = 'SYBASE OLD' THEN
    FOR CUR_COLUMN IN (SELECT 'trim("' || B."name" || '") ' ||
                              UPPER(B."name") COLUMN_NAME
                         FROM "dbo"."sysobjects"@TG4SYBS A,
                              "dbo"."syscolumns"@TG4SYBS B
                        WHERE A."name" = V_TABLE_NAME
                          AND A."id" = B."id"
                        ORDER BY B."colid") LOOP
      S_TEMP := S_TEMP || CUR_COLUMN.COLUMN_NAME || ',';
    END LOOP;
    RETURN SUBSTR(S_TEMP, 1, LENGTH(S_TEMP) - 1);

  ELSE
    FOR CUR_COLUMN IN (SELECT '"' || COLUMN_NAME || '" ' ||
                              UPPER(COLUMN_NAME) COLUMN_NAME
                         FROM ALL_TAB_COLUMNS
                        WHERE TABLE_NAME = V_TABLE_NAME
                          AND OWNER = V_OWNER
                        ORDER BY COLUMN_ID) LOOP
      S_TEMP := S_TEMP || CUR_COLUMN.COLUMN_NAME || ',';
    END LOOP;
    RETURN SUBSTR(S_TEMP, 1, LENGTH(S_TEMP) - 1);
  END IF;*/
END;