ORACLE通过存储过程生成建表SQL

DEPT_NAME	TABLE_NAME	COLUMN_ID	COLUMN_COMMENT	COLUMN_NAME	COLUMN_TYPE
表A	TABLE_A	1	序号	XH	NUMBER
表A	TABLE_A	2	单位名称	DWMC	VARCHAR2(100)
表A	TABLE_A	3	招聘岗位	ZPGW	VARCHAR2(100)
表A	TABLE_A	4	薪金待遇	XZDY	VARCHAR2(100)
表B	TABLE_B	1	序号	XH	NUMBER
表B	TABLE_B	2	姓名	XM	VARCHAR2(100)
表B	TABLE_B	3	性别	XB	NUMBER

需要生成 ->

TABLE_NAME	SQL_TABLE	SQL_COMMENT
TABLE_A	CREATE TABLE TABLE_A (   XH NUMBER,   DWMC VARCHAR2(100),   ZPGW VARCHAR2(100),   XZDY VARCHAR2(100) )	COMMENT ON TABLE TABLE_A IS ‘表A’; COMMENT ON COLUMN TABLE_A.XH IS ‘序号’; COMMENT ON COLUMN TABLE_A.DWMC IS ‘单位名称’; COMMENT ON COLUMN TABLE_A.ZPGW IS ‘招聘岗位’; COMMENT ON COLUMN TABLE_A.XZDY IS ‘薪金待遇’;
TABLE_B	CREATE TABLE TABLE_B (   XH NUMBER,   XM VARCHAR2(100),   XB VARCHAR2(100) )	COMMENT ON TABLE TABLE_B IS ‘表A’; COMMENT ON COLUMN TABLE_B.XH IS ‘序号’; COMMENT ON COLUMN TABLE_B.XM IS ‘姓名’; COMMENT ON COLUMN TABLE_B.XB IS ‘性别’;

---

相关建表语句

--配置表
CREATE TABLE Z_TABLE_INFO 
(
       DEPT_NAME VARCHAR2(20),
       TABLE_NAME VARCHAR2(30),
       COLUMN_ID NUMBER,
       COLUMN_COMMENT VARCHAR2(50),
       COLUMN_NAME VARCHAR2(30),
       COLUMN_TYPE VARCHAR2(50)
)

--目标表
CREATE TABLE Z_TABLE_DDLSQL
(
       TABLE_NAME VARCHAR2(30),
       SQL_TABLE CLOB,
       SQL_COMMENT CLOB
)

---

通过LISTAGG(XMLAGG)实现

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;

---

通过游标循环实现

CREATE OR REPLACE PROCEDURE SP_GET_DDLSQL(PI_TABLE_NAME IN VARCHAR2) IS
  V_SQL_TABLE   CLOB;
  V_SQL_COMMENT CLOB;
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;

  FOR C IN (SELECT TABLE_NAME,
                   DEPT_NAME,
                   COLUMN_NAME,
                   COLUMN_TYPE,
                   COLUMN_COMMENT,
                   COLUMN_ID,
                   CASE
                     WHEN COLUMN_ID = MIN(COLUMN_ID)
                      OVER(PARTITION BY TABLE_NAME) THEN
                      1
                     ELSE
                      0
                   END AS FMIN,
                   CASE
                     WHEN COLUMN_ID = MAX(COLUMN_ID)
                      OVER(PARTITION BY TABLE_NAME) THEN
                      1
                     ELSE
                      0
                   END AS FMAX
              FROM Z_TABLE_INFO
             WHERE 1 = (CASE
                     WHEN PI_TABLE_NAME IS NULL THEN
                      1
                     ELSE
                      0
                   END)
                OR TABLE_NAME = PI_TABLE_NAME
             ORDER BY TABLE_NAME, COLUMN_ID) LOOP
  
    --第一个字段
    IF C.FMIN = 1 THEN
      V_SQL_TABLE   := 'CREATE TABLE ' || C.TABLE_NAME || CHR(13) ||
                       CHR(10) || '(';
      V_SQL_COMMENT := 'COMMENT ON TABLE ' || C.TABLE_NAME || ' IS ''' ||
                       C.DEPT_NAME || ''';';
    END IF;
  
    V_SQL_TABLE := V_SQL_TABLE || CHR(13) || CHR(10) || '  ' || C.COLUMN_NAME || ' ' ||
                   C.COLUMN_TYPE || CASE
                     WHEN C.FMAX = 0 THEN
                      ','
                   END;
  
    V_SQL_COMMENT := V_SQL_COMMENT || CHR(13) || CHR(10) ||
                     'COMMENT ON COLUMN ' || C.TABLE_NAME || '.' ||
                     C.COLUMN_NAME || ' IS ''' || C.COLUMN_COMMENT || ''';';
  
    --最后一个字段
    IF C.FMAX = 1 THEN
    
      V_SQL_TABLE := V_SQL_TABLE || CHR(13) || CHR(10) || ');';
    
      DBMS_OUTPUT.PUT_LINE(V_SQL_TABLE);
      DBMS_OUTPUT.PUT_LINE(V_SQL_COMMENT);
    
      INSERT INTO Z_TABLE_DDLSQL
        (TABLE_NAME, SQL_TABLE, SQL_COMMENT)
      VALUES
        (C.TABLE_NAME, V_SQL_TABLE, V_SQL_COMMENT);
    
    END IF;
  
  END LOOP;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END SP_GET_DDLSQL;