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;*/