疑问如题,下面是过程结构,请问这是咋回事呢?
mysql> SHOW CREATE PROCEDURE getmessage;
+------------+----------+-------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------
--------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedureharacter_set_client | collation_connection | Database Collation |
+------------+----------+-------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------
--------------------+----------------------+--------------------+
| GetMessage | | CREATE DEFINER=`root`@`localhost` PROCEDURE `GetMessage`(
msgname varchar(45)
)
BEGIN
declare msg int;
set msg = ( select count( `idMessages` ) from `messages` where ( `isDeleted` is null or `isDeleted` <> 1 ) and `MsgName` = msgname );
if msg < 1 then
begin
INSERT INTO `chinatelecom`.`messages` (`MsgID`, `MsgName`, `MsgResult`, `AddUser`)
VALUES ( LEFT( CONCAT( 'NO_MSG-000',RAND() ), 20 ) , msgname , 'this message haven\'t defined', '-2');
call GetMessage( 'MESSAGE_NOT_DEFINED' );
end;
else
begin
select ( 0 - `idMessages` ) as returned,
`MsgID` as MessageID,
`MsgName` as MessageName,
`MsgResult` as MessageResult
from `messages`
where `MsgName` like msgname; -- ( `isDeleted` is null or `isDeleted` <> 1 )
-- and
end;
end if;
END | utf8 | utf8_general_ci | latin1_swedish_ci |
+------------+----------+-------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------
--------------------+----------------------+--------------------+
1 row in set (0.00 sec)mysql> call addtreebytypename('test','test',-1);
+----------+------------------+------------------------------------+------------------------------+
| returned | MessageID | MessageName | MessageResult |
+----------+------------------+------------------------------------+------------------------------+
| -1 | MSG-0001 | TREES_TYPE_HAS_DEFINED_BUT_DELETED | A message haven't defined |
| -2 | NO-MSG-0000.6085 | TREES_TYPE_HAS_DEFINED_BUT_DELETED | this message haven't defined |
+----------+------------------+------------------------------------+------------------------------+
2 rows in set (0.01 sec)Query OK, 0 rows affected (0.11 sec)mysql> select ( 0 - `idMessages` ) as returned,
-> `MsgID` as MessageID,
-> `MsgName` as MessageName,
-> `MsgResult` as MessageResult
-> from `messages`
-> where `MsgName` like 'TREES_TYPE_HAS_DEFINED_BUT_DELETED';
+----------+------------------+------------------------------------+------------------------------+
| returned | MessageID | MessageName | MessageResult |
+----------+------------------+------------------------------------+------------------------------+
| -2 | NO-MSG-0000.6085 | TREES_TYPE_HAS_DEFINED_BUT_DELETED | this message haven't defined |
+----------+------------------+------------------------------------+------------------------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE PROCEDURE getmessage;
+------------+----------+-------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------
--------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedureharacter_set_client | collation_connection | Database Collation |
+------------+----------+-------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------
--------------------+----------------------+--------------------+
| GetMessage | | CREATE DEFINER=`root`@`localhost` PROCEDURE `GetMessage`(
msgname varchar(45)
)
BEGIN
declare msg int;
set msg = ( select count( `idMessages` ) from `messages` where ( `isDeleted` is null or `isDeleted` <> 1 ) and `MsgName` = msgname );
if msg < 1 then
begin
INSERT INTO `chinatelecom`.`messages` (`MsgID`, `MsgName`, `MsgResult`, `AddUser`)
VALUES ( LEFT( CONCAT( 'NO_MSG-000',RAND() ), 20 ) , msgname , 'this message haven\'t defined', '-2');
call GetMessage( 'MESSAGE_NOT_DEFINED' );
end;
else
begin
select ( 0 - `idMessages` ) as returned,
`MsgID` as MessageID,
`MsgName` as MessageName,
`MsgResult` as MessageResult
from `messages`
where `MsgName` like msgname; -- ( `isDeleted` is null or `isDeleted` <> 1 )
-- and
end;
end if;
END | utf8 | utf8_general_ci | latin1_swedish_ci |
+------------+----------+-------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------
--------------------+----------------------+--------------------+
1 row in set (0.00 sec)mysql> call addtreebytypename('test','test',-1);
+----------+------------------+------------------------------------+------------------------------+
| returned | MessageID | MessageName | MessageResult |
+----------+------------------+------------------------------------+------------------------------+
| -1 | MSG-0001 | TREES_TYPE_HAS_DEFINED_BUT_DELETED | A message haven't defined |
| -2 | NO-MSG-0000.6085 | TREES_TYPE_HAS_DEFINED_BUT_DELETED | this message haven't defined |
+----------+------------------+------------------------------------+------------------------------+
2 rows in set (0.01 sec)Query OK, 0 rows affected (0.11 sec)mysql> select ( 0 - `idMessages` ) as returned,
-> `MsgID` as MessageID,
-> `MsgName` as MessageName,
-> `MsgResult` as MessageResult
-> from `messages`
-> where `MsgName` like 'TREES_TYPE_HAS_DEFINED_BUT_DELETED';
+----------+------------------+------------------------------------+------------------------------+
| returned | MessageID | MessageName | MessageResult |
+----------+------------------+------------------------------------+------------------------------+
| -2 | NO-MSG-0000.6085 | TREES_TYPE_HAS_DEFINED_BUT_DELETED | this message haven't defined |
+----------+------------------+------------------------------------+------------------------------+
1 row in set (0.00 sec)
(不要高估你的汉语表达能力或者我的汉语理解能力)
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。