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;