请教高手一个问题,对我十分重要!

Question

就是根据表名到数据字典里面找到相关项
组合成建表语句
例如：
select * from user_cons_columns
where table_name = 'TBNAME';

Answer 1

表名已经知道了，可以通过select * from user_tab_columns
where table_name = 'TBNAME';然后根据字段名称和类型写DDL语句
SQL> desc user_tab_columns
 名称                                                                                 
TABLE_NAME                                                                                             
 COLUMN_NAME                                                                                            
 DATA_TYPE                                                                                              
 DATA_TYPE_MOD                                                                                          
 DATA_TYPE_OWNER                                                                                        
 DATA_LENGTH                                                                                            
 DATA_PRECISION                                                                                         
 DATA_SCALE                                                                                             
 NULLABLE                                                                                               
 COLUMN_ID                                                                                              
 DEFAULT_LENGTH                                                                                         
 DATA_DEFAULT                                                                                           
 NUM_DISTINCT                                                                                           
 LOW_VALUE                                                                                              
 HIGH_VALUE                                                                                             
 DENSITY                                                                                                
 NUM_NULLS                                                                                              
 NUM_BUCKETS                                                                                            
 LAST_ANALYZED                                                                                          
 SAMPLE_SIZE                                                                                            
 CHARACTER_SET_NAME                                                                                     
 CHAR_COL_DECL_LENGTH                                                                                   
 GLOBAL_STATS                                                                                           
 USER_STATS                                                                                             
 AVG_COL_LEN         

Answer 2

從表col根據表名得到字段的名稱、數據類型和長度，以及是否為空等，
動態拼接成sql語句，這樣就得到了建立表的sql語句了。

Answer 3

access里面調用？
老板是不是腦子有問題啊，呵呵。

Answer 4

create or replace procedure CREATESQL(tname in user_tab_columns.table_name%type) is
  column_name user_tab_columns.column_name%type;
   data_type   user_tab_columns.data_type%type;
   data_length user_tab_columns.data_length%type;
   file_handle UTL_FILE.FILE_TYPE;
 cursor emp_cursor is select COLUMN_NAME,DATA_TYPE,DATA_LENGTH from user_tab_columns  where table_name = tname;
 
Begin
     file_handle := UTL_FILE.FOPEN('c:\tmp', 'temp.txt', 'w');  
    open emp_cursor;
    loop
       fetch  emp_cursor into column_name,data_type,data_length ;
       exit when emp_cursor%notfound ;       
       UTL_FILE.PUTF(file_handle, column_name+'...');      
       
    end  loop ;             
Close emp_cursor;
UTL_FILE.FCLOSE(file_handle);
   If sql%notfound then
      Raise_application_error(-20020,'employee does not exist');
   End if;
end CREATESQL;

Answer 5

加分请加给miliangping(小米）这个用户，谢谢，我今天花了好久才给你调试好所有代码！

Answer 6

file_handle := UTL_FILE.FOPEN('c:\tmp', 'temp.txt', 'w');
 UTL_FILE.PUTF(file_handle, column_name+'...');
这两句的具体意思是什么?能说明一下吗?

Answer 7

加分请加给miliangping(小米）这个用户，谢谢，我今天花了好久才给你调试好所有代码！

Answer 8

你可以从网上查查!第一句诗：通过Oracle一个包打开C:\tmp\temp.txt文件,然后把SQL语句拼接在一起就可以了。UTL_FILE.PUTF的第二个参数是写入文件的内容。fetch出来的数据，你SQL语句格式拼接在一起填入。