【Oracle】Oracle生成建表语句-SQL匿名块
匿名块SQL:
DECLARE
PI_TABLE_NAME VARCHAR(8000);
BEGIN
PI_TABLE_NAME:='INFO';
IF TRIM(PI_TABLE_NAME) IS NULL THEN
--EXECUTE IMMEDIATE 'TRUNCATE TABLE Z_TABLE_DDLSQL';
DELETE FROM Z_TABLE_DDLSQL;
ELSE
DELETE FROM Z_TABLE_DDLSQL WHERE TABLE_NAME = PI_TABLE_NAME;
END IF;
INSERT INTO Z_TABLE_DDLSQL
(TABLE_NAME, SQL_TABLE, SQL_COMMENT)
SELECT TABLE_NAME,
'CREATE TABLE ' || TABLE_NAME || CHR(13) || CHR(10) || '(' ||
CHR(13) || CHR(10) ||
LISTAGG(' ' || COLUMN_NAME || ' ' || COLUMN_TYPE,
',' || CHR(13) || CHR(10)) WITHIN GROUP(ORDER BY COLUMN_ID) || CHR(13) || CHR(10) || ');' AS SQL_TABLE,
/*'COMMENT ON TABLE ' || TABLE_NAME || ' IS ''' || MAX(DEPT_NAME) ||
''';' || CHR(13) || CHR(10) ||
REPLACE(LISTAGG('_##' || COLUMN_NAME || ' IS ''' ||
COLUMN_COMMENT || ''';',
CHR(13) || CHR(10)) WITHIN
GROUP(ORDER BY COLUMN_ID),
'_##',
'COMMENT ON COLUMN ' || TABLE_NAME || '.') AS SQL_COMMENT*/
'COMMENT ON TABLE ' || TABLE_NAME || ' IS ''' || MAX(DEPT_NAME) ||
''';' || CHR(13) || XMLAGG(XMLPARSE(CONTENT'COMMENT ON COLUMN ' || TABLE_NAME ||'.' || COLUMN_NAME ||' IS ''' || COLUMN_COMMENT ||''';' || CHR(13) WELLFORMED) ORDER BY COLUMN_ID)
.GETCLOBVAL() AS COLUMN_COMMENT
FROM Z_TABLE_INFO
WHERE 1 = (CASE
WHEN PI_TABLE_NAME IS NULL THEN
1
ELSE
0
END)
OR TABLE_NAME = PI_TABLE_NAME
GROUP BY TABLE_NAME;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.put_line(SQLERRM);
END;
/*CREATE OR REPLACE PROCEDURE SP_GET_DDLSQL1(PI_TABLE_NAME IN VARCHAR2) IS
BEGIN
IF TRIM(PI_TABLE_NAME) IS NULL THEN
--EXECUTE IMMEDIATE 'TRUNCATE TABLE Z_TABLE_DDLSQL';
DELETE FROM Z_TABLE_DDLSQL;
ELSE
DELETE FROM Z_TABLE_DDLSQL WHERE TABLE_NAME = PI_TABLE_NAME;
END IF;
INSERT INTO Z_TABLE_DDLSQL
(TABLE_NAME, SQL_TABLE, SQL_COMMENT)
SELECT TABLE_NAME,
'CREATE TABLE ' || TABLE_NAME || CHR(13) || CHR(10) || '(' ||
CHR(13) || CHR(10) ||
LISTAGG(' ' || COLUMN_NAME || ' ' || COLUMN_TYPE,
',' || CHR(13) || CHR(10)) WITHIN GROUP(ORDER BY COLUMN_ID) || CHR(13) || CHR(10) || ');' AS SQL_TABLE,
\*'COMMENT ON TABLE ' || TABLE_NAME || ' IS ''' || MAX(DEPT_NAME) ||
''';' || CHR(13) || CHR(10) ||
REPLACE(LISTAGG('_##' || COLUMN_NAME || ' IS ''' ||
COLUMN_COMMENT || ''';',
CHR(13) || CHR(10)) WITHIN
GROUP(ORDER BY COLUMN_ID),
'_##',
'COMMENT ON COLUMN ' || TABLE_NAME || '.') AS SQL_COMMENT*\
'COMMENT ON TABLE ' || TABLE_NAME || ' IS ''' || MAX(DEPT_NAME) ||
''';' || CHR(13) || XMLAGG(XMLPARSE(CONTENT'COMMENT ON COLUMN ' || TABLE_NAME ||'.' || COLUMN_NAME ||' IS ''' || COLUMN_COMMENT ||''';' || CHR(13) WELLFORMED) ORDER BY COLUMN_ID)
.GETCLOBVAL() AS COLUMN_COMMENT
FROM Z_TABLE_INFO
WHERE 1 = (CASE
WHEN PI_TABLE_NAME IS NULL THEN
1
ELSE
0
END)
OR TABLE_NAME = PI_TABLE_NAME
GROUP BY TABLE_NAME;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.put_line(SQLERRM);
END SP_GET_DDLSQL1;*/