MySQL操作程序二十三(修改OURREF的头文字)

返回
  1. 修改ourref的TYPE
  2. 修改msceqn1_start.pl的子程序,obtain_time
  3. 一天订单不超过99部分的修改
  4. 同一天订单的增加
元TYPE mysql> show columns from enq1; +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | time | date | YES | | NULL | | | ourref | int(11) | YES | | NULL | | | owner | int(11) | YES | | NULL | | | ownerno | varchar(100) | YES | | NULL | | | hullnoid | int(11) | YES | | NULL | | | type1id | text | YES | | NULL | | | partsid | text | YES | | NULL | | | QTY | text | YES | | NULL | | | memo | text | YES | | NULL | | | LANGUAGEid | int(11) | YES | | NULL | | | makerid | int(11) | YES | | NULL | | | enq2s | text | YES | | NULL | | | seriesid | text | YES | | NULL | | +------------+--------------+------+-----+---------+----------------+ 14 rows in set (0.02 sec) mysql> ALTER TABLE enq1 CHANGE ourref ourref CHAR; ERROR 1406 (22001): Data too long for column 'ourref' at row 1 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = 1' at line 1 mysql> ALTER TABLE enq1 CHANGE ourref ourref CHAR(11); Query OK, 4656 rows affected (0.95 sec) Records: 4656 Duplicates: 0 Warnings: 0 mysql> show columns from enq1; +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | time | date | YES | | NULL | | | ourref | char(11) | YES | | NULL | | | owner | int(11) | YES | | NULL | | | ownerno | varchar(100) | YES | | NULL | | | hullnoid | int(11) | YES | | NULL | | | type1id | text | YES | | NULL | | | partsid | text | YES | | NULL | | | QTY | text | YES | | NULL | | | memo | text | YES | | NULL | | | LANGUAGEid | int(11) | YES | | NULL | | | makerid | int(11) | YES | | NULL | | | enq2s | text | YES | | NULL | | | seriesid | text | YES | | NULL | | +------------+--------------+------+-----+---------+----------------+ 14 rows in set (0.00 sec) ---------------------------------------------- mysql> select * from enq1 where id = max(id); ERROR 1111 (HY000): Invalid use of group function mysql> select * from enq1 where id is max(id); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'max(i d)' at line 1 mysql> select max(id) from enq1; +---------+ | max(id) | +---------+ | 4657 | +---------+ 1 row in set (0.00 sec)
# 取得现在时间的子程序(旧) sub obtain_time { my($a_ref) = @_; my(%t); ($t{day},$t{month},$t{year1}) = (localtime)[3,4,5]; $t{year} = substr($t{year1},-1); $t{month} = $t{month} + 1; $t{month} = sprintf("%02d",$t{month}); $t{day} = sprintf("%02d",$t{day}); $t{year1} = $t{year1} + 1900; $t{time1} = $t{year1} . '-' . $t{month} . '-' . $t{day}; $$a_ref{time1} = $t{time1}; $$a_ref{ymd} = $t{year} . $t{month} . $t{day}; return($a_ref); } # 取得现在时间的子程序(新) sub obtain_time { my($a_ref) = @_; my(%t); ($t{day},$t{month},$t{year1}) = (localtime)[3,4,5]; $t{year} = substr($t{year1},-1); if ( $t{year} == 0 ) { $t{year} = 'A'; } elsif ($t{year} == 1) { $t{year} = 'B'; } elsif ($t{year} == 2) { $t{year} = 'C'; } elsif ($t{year} == 3) { $t{year} = 'D'; } elsif ($t{year} == 4) { $t{year} = 'E'; } elsif ($t{year} == 5) { $t{year} = 'F'; } elsif ($t{year} == 6) { $t{year} = 'G'; } elsif ($t{year} == 7) { $t{year} = 'H'; } elsif ($t{year} == 8) { $t{year} = 'I'; } $t{month} = $t{month} + 1; $t{month} = sprintf("%02d",$t{month}); $t{day} = sprintf("%02d",$t{day}); $t{year1} = $t{year1} + 1900; $t{time1} = $t{year1} . '-' . $t{month} . '-' . $t{day}; $$a_ref{time1} = $t{time1}; $$a_ref{ymd} = $t{year} . $t{month} . $t{day}; return($a_ref); }
# 读取表格enq1最大号码的ourref $t{ourref1} = $self->dbh->selectrow_array("SELECT ourref FROM enq1 WHERE id = $t{enq1_maxid}"); # 自动生成一个ourref $t{ourref2} = $$a_ref{ymd} . '01'; $t{tmp1} = substr($t{ourref1},-2); if ( $t{tmp1} eq '99' ) { # 最大は99 $t{ourref0} = $t{ourref1}; $t{ourref0_OK} = 'NG'; } elsif ( ($t{ourref1} - $t{ourref2}) >= 0 ) { $t{ourref0} = $t{ourref1} + 1; $t{ourref0_OK} = 'OK'; } else { $t{ourref0} = $t{ourref2}; $t{ourref0_OK} = 'OK'; } --------------------------------------------------------------------------- # 读取表格enq1最大号码的ourref $t{ourref1} = $self->dbh->selectrow_array("SELECT ourref FROM enq1 WHERE id = $t{enq1_maxid}"); # 自动生成一个ourref $t{ourref2} = $$a_ref{ymd} . '01'; $t{tmp1} = substr($t{ourref1},-2); $t{tmp2} = $t{tmp1} + 1; $t{tmp2} = sprintf("%02d",$t{tmp2}); substr($t{ourref2},-2) = $t{tmp2}; if ( $t{tmp1} eq '99' ) { # 最大は99 $t{ourref0} = $t{ourref1}; $t{ourref0_OK} = 'NG'; # } elsif ( ($t{ourref1} - $t{ourref2}) >= 0 ) { # $t{ourref0} = $t{ourref1} + 1; # $t{ourref0_OK} = 'OK'; } else { $t{ourref0} = $t{ourref2}; $t{ourref0_OK} = 'OK'; }
# 读取表格enq1最大号码的ourref $t{ourref1} = $self->dbh->selectrow_array("SELECT ourref FROM enq1 WHERE id = $t{enq1_maxid}"); # 自动生成一个ourref $t{ourref2} = $$a_ref{ymd} . '01'; $t{tmp1} = substr($t{ourref1},-2); $t{tmp11} = substr($t{ourref1},1,4); $t{tmp21} = substr($t{ourref2},1,4); if ( $t{tmp1} eq '99' ) { # 最大は99 $t{ourref0} = $t{ourref1}; $t{ourref0_OK} = 'NG'; } elsif ( $t{tmp11} == $t{tmp21} ) { # 同じ日付 $t{tmp2} = $t{tmp1} + 1; $t{tmp2} = sprintf("%02d",$t{tmp2}); substr($t{ourref2},-2) = $t{tmp2}; $t{ourref0} = $t{ourref2}; $t{ourref0_OK} = 'OK'; } else { $t{ourref0} = $t{ourref2}; $t{ourref0_OK} = 'OK'; }
返回