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;