本文共 3093 字,大约阅读时间需要 10 分钟。
根据上一篇中的TableKind类型可以写相应的show table 语句。
Teradata中TableKind与show语句对应 TableKind | 类型 | show语句 |
T | SET Table | show table 表名; |
O | MULTISET Table | show table 表名; |
V | View | show view 视图名; |
M | Macro | show macro 名; |
P | SQL Procedure | show procedure 名; |
E | External Stored Procedure | show procedure 名; |
D | JAR | |
R | Table function | show function 名; |
F | Standard function | show function 名; |
G | TRIGGER | SHOW TRIGGER trigger_name; |
I | Join Index | SHOW JOIN INDEX join_index_name; |
N | Hash Index | SHOW HASH INDEX hash_index_name; |
其他 | | |
下面是一段示例代码,代码实现使用show语句获取各种Table(Object)的DDL(Create语句等)以及对应的Drop语句:
-
-
-
-
-
-
-
- private String showTable(String TableName, String TableKind)
- throws SQLException {
- String sqlCREATE = "";
- PreparedStatement ps = null;
- ResultSet rs = null;
- String sql = null;
- switch(TableKind) {
- case "T":
- sql = CommonConfig.sqlShowTable + "\"" + TableName + "\"";
- this.sqlListDrop.add(CommonConfig.sqlDropTable +
- "\"" + TableName + "\"");
- break;
- case "V":
- sql = CommonConfig.sqlShowView + "\"" + TableName + "\"";
- this.sqlListDrop.add(CommonConfig.sqlDropView +
- "\"" + TableName + "\"");
- break;
- case "M":
- sql = CommonConfig.sqlShowMacro + "\"" + TableName + "\"";
- this.sqlListDrop.add(CommonConfig.sqlDropMacro +
- "\"" + TableName + "\"");
- break;
- case "P":
- case "E":
- sql = CommonConfig.sqlShowProcedure + "\"" + TableName + "\"";
- this.sqlListDrop.add(CommonConfig.sqlDropProcedure +
- "\"" + TableName + "\"");
- break;
- case "D":
- logger.info(" -- TableKind is D, SKIP. -- ");
- break;
- case "R":
- case "F":
- sql = CommonConfig.sqlShowFunction + "\"" + TableName + "\"";
- this.sqlListDrop.add(CommonConfig.sqlDropFunction +
- "\"" + TableName + "\"");
- break;
- default:
- break;
- }
- logger.info(sql);
- if(sql == null) return null;
- ps = conn.prepareStatement(sql);
-
- rs = ps.executeQuery();
- while(rs.next()) {
-
- sqlCREATE += rs.getString(1);
- }
-
-
- sqlCREATE = sqlCREATE.replace(DBConn.getDatabase() + ".", "");
- sqlCREATE = sqlCREATE.replace(DBConn.getDatabase().toLowerCase() + ".", "");
- sqlCREATE = sqlCREATE.replace(DBConn.getDatabase().toUpperCase() + ".", "");
- sqlCREATE = sqlCREATE.replace("\"" + DBConn.getDatabase() + "\".", "");
- sqlCREATE = sqlCREATE.replace("\"" + DBConn.getDatabase().toLowerCase() + "\".", "");
- sqlCREATE = sqlCREATE.replace("\"" + DBConn.getDatabase().toUpperCase() + "\".", "");
- rs.close();
- ps.close();
-
- return sqlCREATE;
- }
注意:
1. CommonConfig.sqlShowTable是字符串"show table ",其他类似。
2. 最值得注意的情况是
- sqlCREATE += rs.getString(1);
这句必须使用+=,而不能使用=;因为存在show procedure的结果集(ResultSet)有可能是当作多行返回的。即如下图的情况(使用=时的返回值):
- INFO [main] (DDLTransfer.java:93) - show procedure "wikiproc"
- **************************************
- replace procedure
- **************************************
- wikiproc
- **************************************
- (IN RUNID INTEGER)
- dynamic result sets 1
- main:begin
-
- declare cur_report cursor with return only for
- sel *
- from pct_run r
- where r.run_id=RUNID;
-
- open cur_report;
- end;
每行用一行星号隔开,可见返回了三行。 转载地址:http://xurli.baihongyu.com/