ORACLE批量⽣成表，类似mysql的showcreatetable
最近有个需求，需要将1-200张表上传给第三⽅，每天定时抽取前⼀天的数据，⽣成TXT⽂件上传到FTP上，还需要将建表语句给他们。
。
oracle没有show create table能够直接显⽰建表语句，有个
_ddl('TABLE','TABLE_NAME') 这个存过能⽣成，但是⾥⾯还有表空间等信息，还有些多余的信息，似乎不太满⾜，类似这样
于是重新⽤java编写了下。
废话不多说，直接上代码
package ftp.util;
import java.sql.Connection;
import java.util.Hashtable;
import java.util.List;
calibre drc 教程public class ShowCreateTable extends DBUtils{
public static final String password = "xxx";// test#1234  ct04_a1234
jquery 下载wordpublic static final String username = "xxxx";
public static final String url = "jdbc:oracle:thin:@ip:1621:xxx";
public static final String driver = "oracle.jdbc.driver.OracleDriver";
public static void main(String[] args) throws Exception {
ShowCreateTable t = new ShowCreateTable();javascript switch语句
Connection conn = t.getConnection(driver,url,username,password);
String sql = t.createTableSql(conn, "owner", "tablename");
System.out.println(sql);
t.closeConn(conn);
}
private  String createTableSql(Connection conn,String owner,String tablename) throws Exception{
StringBuffer sb = new StringBuffer("create table ");
sb.append(owner).append(".").append(tablename);
sb.append("(");
sb.append("\r");
//查询表所有列，按照顺序⽣成
String sql = "SELECT * FROM DBA_TAB_COLS R  WHERE r.TABLE_NAME = '"+tablename+"' AND R.OWNER='"+owner+"' ORDER BY R.COLUMN_ID";        List<Hashtable<String, String>> list = ResultBySQL(conn, sql, null);
int index = 0;
sb.("COLUMN_NAME")).append(" ").("DATA_TYPE"));
("DATA_TYPE").equals("DATE") || h.get("DATA_TYPE").equals("TIMESTAMP")){
mysql语句转oracle}else ("DATA_TYPE").equals("NUMBER")){
sb.append("(").("DATA_PRECISION")).append(")");
}else ("DATA_TYPE").equals("VARCHAR")||h.get("DATA_TYPE").equals("CHAR") || h.get("DATA_TYPE").equals("VARCHAR2")){
sb.append("(").("DATA_LENGTH")).append(")");
}
("NULLABLE").equals("N")){
sb.append(" not null ");
}
index++;
if(index<list.size()){
sb.append(",");
}
sb.append("\r");
}
sb.append(");");
sb.append("\r");十六进制转十进制对照表
//查询表所有的comment
sql = "SELECT COMMENTS FROM DBA_TAB_COMMENTS R  WHERE r.TABLE_NAME = '"+tablename+"' AND R.OWNER='"+owner+"'";
List<String> comments = ListStrBySQL(conn, sql, null);
if(comments!=null && comments.size()>0){
sb.append(" COMMENT ON TABLE ").append(owner).append(".").append(tablename).append(" is '").(0)).append("'");
sb.append(";");
sb.append("\r");
}
//查询列的comment
sql = "SELECT COLUMN_NAME,comments FROM dba_col_comments R  WHERE  r.TABLE_NAME = '"+tablename+"' AND R.OWNER='"+owner+"'";
List<Hashtable<String, String>> colComments = ResultBySQL(conn, sql, null);学校用的编程软件是什么
if(colComments!=null && colComments.size()>0){
for(int i=0;i<colComments.size();i++){
Hashtable<String, String> h = (i);
sb.append(" COMMENT ON COLUMN ").append(owner).append(".").append(tablename).append(".").("COLUMN_NAME")).append(" is '").append    sb.append(";");
sb.append("\r");
}
}
//⽣成索引
sql = "select index_name,COLUMN_NAME FROM dba_ind_columns  r  WHERE r.TABLE_NAME = '"+tablename+"' AND R.TABLE_OWNER='"+owner+"'";
List<Hashtable<String, String>> indexList = ResultBySQL(conn, sql, null);
if(indexList!=null && indexList.size()>0){
for(int i=0;i<indexList.size();i++){
Hashtable<String, String> h = (i);
if(!h.get("INDEX_NAME").startsWith("PK_")){
sb.append("create index ").append(owner).append(".").("INDEX_NAME")).append(" ON ").append(owner).append(".").append(tablename).appen      sb.append("\r");
}
}
}
//⽣成主键
sql = "select  a.constraint_lumn_name from DBA_cons_columns a, DBA_constraints b straint_name = b.constraint_name and A.table_na  List<Hashtable<String, String>> pkList = ResultBySQL(conn, sql, null);
if(pkList!=null && pkList.size()>0){
String str = "";
String constraintName = "";
for(int i=0;i<pkList.size();i++){
if(i==pkList.size()-1){
str += h.get("COLUMN_NAME");
}else{
str += h.get("COLUMN_NAME")+",";
}
constraintName = h.get("CONSTRAINT_NAME");
}
sb.append("alter table ").append(owner).append(".").append(tablename).append(" add constraint ").append(constraintName).append(" primary key  (").append    sb.append("\r");
}
String();
}
//外键没有⽣成，懒得⽣成了
}
最终⽣成的效果就⽐较清爽，包含了主键，索引，comment，⾮空等信息

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。