小弟愚笨，求解文本处理awk之SQL语句

mmwants

小弟真是愚笨啊，一直学不好AWK，现在着急交差，求各路大侠帮一下 解题

题目如下：
ALTER TABLE V3X_BBS_ARTICLE
RENAME TO BBS_ARTICLE,
CHANGE CLICK_NUMBER CLICK_NUMBER SMALLINT DEFAULT 0,
CHANGE ISSUE_TIME ISSUE_TIME DATETIME NOT NULL DEFAULT '2000-01-01 00:00:00',
CHANGE ELITE_FLAG ELITE_FLAG SMALLINT,
CHANGE RESOURCE_FLAG RESOURCE_FLAG SMALLINT,
CHANGE ANONYMOUS_FLAG ANONYMOUS_FLAG SMALLINT,
CHANGE ANONYMOUS_REPLY_FLAG ANONYMOUS_REPLY_FLAG SMALLINT,
CHANGE MESSAGE_NOTIFY_FLAG MESSAGE_NOTIFY_FLAG SMALLINT,
CHANGE TOP_SEQUENCE TOP_SEQUENCE SMALLINT,
CHANGE PP_STATE STATE SMALLINT,
CHANGE REPLY_NUMBER REPLY_NUMBER SMALLINT,
CHANGE MODIFY_TIME MODIFY_TIME DATETIME DEFAULT '2000-01-01 00:00:00';

ALTER TABLE BUL_DATA
CHANGE TITLE TITLE VARCHAR(1024) NOT NULL,
CHANGE KEYWORDS KEYWORDS VARCHAR(500),
CHANGE CREATE_DATE CREATE_DATE DATETIME DEFAULT '2000-01-01 00:00:00' NOT NULL,
CHANGE AUDIT_DATE AUDIT_DATE DATETIME DEFAULT '2000-01-01 00:00:00',
CHANGE PUBLISH_DATE PUBLISH_DATE DATETIME DEFAULT '2000-01-01 00:00:00',
CHANGE PIGEONHOLE_DATE PIGEONHOLE_DATE DATETIME DEFAULT '2000-01-01 00:00:00',
CHANGE UPDATE_DATE UPDATE_DATE DATETIME DEFAULT '2000-01-01 00:00:00',
CHANGE READ_COUNT READ_COUNT SMALLINT DEFAULT 0,
CHANGE TOP_ORDER TOP_ORDER SMALLINT NOT NULL DEFAULT 0,
CHANGE PP_STATE STATE SMALLINT NOT NULL,
CHANGE DELETED_FLAG DELETED_FLAG SMALLINT NOT NULL DEFAULT 0,
CHANGE ATT_FLAG ATT_FLAG SMALLINT;

以上是MYSQL SQL语句，想要转成ORACLE的ALTER 语句，因为MYSQL 与ORACLE的执行ALTER语句是不同的，而且ORACLE碰到RENAME之类的还要单独写成一条ALTER TABLE语句。

所以改完后应该如下：

ALTER TABLE V3X_BBS_ARTICLE RENAME  MODIFY CLICK_NUMBER SMALLINT DEFAULT 0
MODIFY ISSUE_TIME DATETIME NOT NULL DEFAULT '2000-01-01 00:00:00'
MODIFY ELITE_FLAG SMALLINT
MODIFY RESOURCE_FLAG SMALLINT
MODIFY ANONYMOUS_FLAG SMALLINT
MODIFY ANONYMOUS_REPLY_FLAG SMALLINT
MODIFY MESSAGE_NOTIFY_FLAG SMALLINT
MODIFY TOP_SEQUENCE SMALLINT
MODIFY PP_STATE SMALLINT
MODIFY REPLY_NUMBER SMALLINT
MODIFY MODIFY_TIME DATETIME DEFAULT '2000-01-01 00:00:00';
ALTER TABLE V3X_BBS_ARTICLE RENAME COLUMN PP_STATE TO STATE;
ALTER TABLE V3X_BBS_ARTICLE RENAME TO BBS_ARTICLE;


ALTER TABLE BUL_DATA
MODIFY TITLE TITLE VARCHAR(1024) NOT NULL
MODIFY KEYWORDS VARCHAR(500)
MODIFY CREATE_DATE DATETIME DEFAULT '2000-01-01 00:00:00' NOT NULL
MODIFY AUDIT_DATE DATETIME DEFAULT '2000-01-01 00:00:00'
MODIFY PUBLISH_DATE DATETIME DEFAULT '2000-01-01 00:00:00'
MODIFY PIGEONHOLE_DATE DATETIME DEFAULT '2000-01-01 00:00:00'
MODIFY UPDATE_DATE DATETIME DEFAULT '2000-01-01 00:00:00'
MODIFY READ_COUNT SMALLINT DEFAULT 0
MODIFY TOP_ORDER SMALLINT NOT NULL DEFAULT 0
MODIFY PP_STATE SMALLINT NOT NULL
MODIFY DELETED_FLAG SMALLINT NOT NULL DEFAULT 0
MODIFY ATT_FLAG SMALLINT;
ALTER TABLE BUL_DATA RENAME COLUMN PP_STATE TO STATE;

有1000多条这样的记录啊，手工改要命啊。

reyleon

看晕了

Shell_HAT

最后一行
ALTER TABLE BUL_DATA RENAME COLUMN PP_STATE TO STATE;
是根据什么规律转换来的？

seesea2517

没有细看结果对不对，不知道你那个有没有笔误，反正结果是有一些不一样，你修修改改吧。

1. [seesea@UC ~]$ awk -f sql.awk file.txt
   
2. ALTER TABLE V3X_BBS_ARTICLE
   
3. MODIFY CLICK_NUMBER  SMALLINT DEFAULT 0
   
4. MODIFY ISSUE_TIME  DATETIME NOT NULL DEFAULT '2000-01-01 00:00:00'
   
5. MODIFY ELITE_FLAG  SMALLINT
   
6. MODIFY RESOURCE_FLAG  SMALLINT
   
7. MODIFY ANONYMOUS_FLAG  SMALLINT
   
8. MODIFY ANONYMOUS_REPLY_FLAG  SMALLINT
   
9. MODIFY MESSAGE_NOTIFY_FLAG  SMALLINT
   
10. MODIFY TOP_SEQUENCE  SMALLINT
    
11. MODIFY PP_STATE  SMALLINT
    
12. MODIFY REPLY_NUMBER  SMALLINT
    
13. MODIFY MODIFY_TIME  DATETIME DEFAULT '2000-01-01 00:00:00';
    
14. ALTER TABLE V3X_BBS_ARTICLE RENAME COLUMN PP_STATE TO STATE;
    
15. ALTER TABLE V3X_BBS_ARTICLE RENAME TO BBS_ARTICLE;
    
16. 
    
17. ALTER TABLE BUL_DATA
    
18. MODIFY TITLE  VARCHAR(1024) NOT NULL
    
19. MODIFY KEYWORDS  VARCHAR(500)
    
20. MODIFY CREATE_DATE  DATETIME DEFAULT '2000-01-01 00:00:00' NOT NULL
    
21. MODIFY AUDIT_DATE  DATETIME DEFAULT '2000-01-01 00:00:00'
    
22. MODIFY PUBLISH_DATE  DATETIME DEFAULT '2000-01-01 00:00:00'
    
23. MODIFY PIGEONHOLE_DATE  DATETIME DEFAULT '2000-01-01 00:00:00'
    
24. MODIFY UPDATE_DATE  DATETIME DEFAULT '2000-01-01 00:00:00'
    
25. MODIFY READ_COUNT  SMALLINT DEFAULT 0
    
26. MODIFY TOP_ORDER  SMALLINT NOT NULL DEFAULT 0
    
27. MODIFY PP_STATE  SMALLINT NOT NULL
    
28. MODIFY DELETED_FLAG  SMALLINT NOT NULL DEFAULT 0
    
29. MODIFY ATT_FLAG  SMALLINT;
    
30. ALTER TABLE BUL_DATA RENAME COLUMN PP_STATE TO STATE;
    
31. 
    
32. [seesea@UC ~]$ cat sql.awk
    
33. {
    
34.     sub(",", "")
    
35. }
    
36. 
    
37. $1 == "ALTER" {
    
38.     sql_added = ""
    
39.     sql_alter = $0
    
40.     print $0
    
41. }
    
42. 
    
43. $1 == "RENAME" {
    
44.     sql_rename_table = sql_alter" "$0";"
    
45.     sql_added = sql_rename_table
    
46. }
    
47. 
    
48. $1 == "CHANGE" {
    
49.     if ($2 != $3)
    
50.     {
    
51.         sql_rename_column = sql_alter" RENAME COLUMN "$2" TO "$3";"
    
52.         sql_added = sql_rename_column"\n"sql_added
    
53.     }
    
54. 
    
55.     $1 = "MODIFY"
    
56.     $3 = ""
    
57.     #$0 = $0
    
58.     print $0
    
59. }
    
60. 
    
61. $0 ~ ";" {
    
62.     print sql_added
    
63. }
    
64. 
    
65. $0 == "" {
    
66.     print
    
67. }
    
68. [seesea@UC ~]$

复制代码

seesea2517

btw，有没有高人告诉我如何处理删除一个域后有两个空格的问题，印象里好像是说 $0=$0 就可以去掉多余的分隔符，但我这里好像行不通,gawk 也不行，是版本问题？

1. [seesea@UC ~]$ echo "a b c d" | gawk '{$2 = ""; $0=$0; print $0}'
   
2. a  c d
   
3. [seesea@UC ~]$ echo "a b c d" | awk '{$2 = ""; $0=$0; print $0}'
   
4. a  c d
   
5. [seesea@UC ~]$ awk --version
   
6. GNU Awk 3.1.5
   
7. Copyright (C) 1989, 1991-2005 Free Software Foundation.
   
8. 
   
9. This program is free software; you can redistribute it and/or modify
   
10. it under the terms of the GNU General Public License as published by
    
11. the Free Software Foundation; either version 2 of the License, or
    
12. (at your option) any later version.
    
13. 
    
14. This program is distributed in the hope that it will be useful,
    
15. but WITHOUT ANY WARRANTY; without even the implied warranty of
    
16. MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    
17. GNU General Public License for more details.
    
18. 
    
19. You should have received a copy of the GNU General Public License
    
20. along with this program; if not, write to the Free Software
    
21. Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
    
22. [seesea@UC ~]$ gawk --version
    
23. GNU Awk 3.1.5
    
24. Copyright (C) 1989, 1991-2005 Free Software Foundation.
    
25. 
    
26. This program is free software; you can redistribute it and/or modify
    
27. it under the terms of the GNU General Public License as published by
    
28. the Free Software Foundation; either version 2 of the License, or
    
29. (at your option) any later version.
    
30. 
    
31. This program is distributed in the hope that it will be useful,
    
32. but WITHOUT ANY WARRANTY; without even the implied warranty of
    
33. MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    
34. GNU General Public License for more details.
    
35. 
    
36. You should have received a copy of the GNU General Public License
    
37. along with this program; if not, write to the Free Software
    
38. Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.

复制代码

mmwants

seesea2517 发表于 2013-01-21 17:02
btw，有没有高人告诉我如何处理删除一个域后有两个空格的问题，印象里好像是说 $0=$0 就可以去掉多余的分隔 ...

谢谢您，耐心的阅读，谢谢您能给出个awk程序，我将您的程序研究一下。

再次谢谢。

mmwants

Shell_HAT 发表于 2013-01-18 22:36
最后一行
ALTER TABLE BUL_DATA RENAME COLUMN PP_STATE TO STATE;
是根据什么规律转换来的？

您好，是根据CHANGE PP_STATE STATE SMALLINT NOT NULL。 这句话，因第三列与第二列不同，需要改列的名字，但也有可能改列的类型，所以保留改列的类型，增加一个改列的名字，因ORACLE不能将 改名和该列类型 组合一条命令使用。

blackold

回复 5# seesea2517


    try:

1. echo "a b c d" | gawk '{$2 = ""; $0=$0; $1=$1;print $0}'

复制代码

huangyu_945

看的我有点晕啊

seesea2517

回复 8# blackold


    啊啊啊。