PFMpFAyR

1-- Created on 2019-09-26 by USER 
2declare 
3  -- Local variables here
4  v_pre_space varchar2(10);
5  
6  v_tname varchar2(100);
7  v_sql varchar2(1000);
8  v_int_type varchar2(10);
9  v_comment varchar2(1000);
10  v_pk varchar2(1000);
11  
12begin
13  v_pre_space := '  ';
14  
15  v_tname := 'ACC_AGENCY_ACCOUNT';
16  -- Test statements here
17  v_sql := 'DROP TABLE IF EXISTS ' || v_tname || ';' || chr(13);
18  v_sql := v_sql || 'create table ' || v_tname || ' (' || chr(13);
19  
20  -- 处理列、注释和是否空值
21  for tab in (select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, NULLABLE, CHAR_LENGTH from user_tab_cols where TABLE_NAME = v_tname order by column_id) loop
22    v_sql := v_sql || v_pre_space;
23    v_sql := v_sql || tab.column_name || ' ';
24    
25    -- 字符
26    if tab.DATA_TYPE in ('VARCHAR2', 'NVARCHAR2') then
27      v_sql := v_sql || 'VARCHAR' || '(' || tab.CHAR_LENGTH || ') ';
28    end if;
29    if tab.DATA_TYPE = 'CHAR' then
30      v_sql := v_sql || 'CHAR' || '(' || tab.CHAR_LENGTH || ') ';
31    end if;
32        
33    -- 数字
34    if tab.DATA_TYPE = 'DATE' then
35      v_sql := v_sql || 'datetime' || ' ';
36    end if;
37    if tab.DATA_TYPE = 'NUMBER' then
38      if tab.DATA_PRECISION <= 2 then
39        v_int_type := 'TINYINT';
40      elsif tab.DATA_PRECISION <= 4 then
41        v_int_type := 'SMALLINT';
42      elsif tab.DATA_PRECISION <= 6 then
43        v_int_type := 'MEDIUMINT';
44      elsif tab.DATA_PRECISION <= 9 then
45        v_int_type := 'INT';
46      else
47        v_int_type := 'BIGINT';
48      end if;
49      
50      v_sql := v_sql || v_int_type || ' ';
51    end if;
52    
53    -- NULL value
54    if tab.NULLABLE = 'Y' then
55      v_sql := v_sql || 'not null ';
56    end if;
57    
58    -- comment
59    select COMMENTS into v_comment 
60      from user_col_comments 
61     where TABLE_NAME = v_tname
62       and COLUMN_NAME = tab.column_name;
63    if v_comment is not null then
64      v_sql := v_sql || 'comment ''' || v_comment || ''' ';
65    end if;
66    
67    v_sql := v_sql || ',' || chr(13);
68  end loop;
69  
70  -- 处理主键
71  select wm_concat(COLUMN_NAME) 
72    into v_pk
73    from user_cons_columns 
74   where table_name = v_tname 
75     and CONSTRAINT_NAME like 'PK%' 
76   order by POSITION;
77  v_sql := v_sql || v_pre_space;
78  v_sql := v_sql || 'PRIMARY KEY (' || v_pk || ')' || chr(13);
79  
80  -- 收尾
81  v_sql := v_sql || ');';
82  
83  dbtool.p(v_sql);
84end;