存储过程有执行,但是其中的一个更新语句没有执行,所以想打更新语句输出
DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`UpdateOldrec` $$
CREATE DEFINER=`test`@`%` PROCEDURE `UpdateOldrec`(IN 1_cid int,IN 1_url char(250),IN 1_keywords char(250),IN 1_Title char(250),IN1_Contents char(250),IN 1_Other char(250),IN 1_Image char(250),IN 1_Category int)
BEGIN
DECLARE pid INT DEFAULT (select `Pid` From data_index where `Url`=1_url and `Cid`=1_cid);
DECLARE Str1 varchar(300) DEFAULT '';
IF pid>0 then
IF LENGTH(1_Title)> 0 THEN
UPDATE `data_contents` SET `Title` = 1_Title,`Contents` = 1_Contents ,`Other` = 1_Other ,`images` = 1_Image WHERE `Pid`= pid LIMIT 1 ;
END IF; SET @SqlCmd ="update data_index SET Edit_date = UNIX_TIMESTAMP()";
IF LENGTH(1_keywords) > 0 THEN SET @SqlCmd = CONCAT(@SqlCmd , ',Keywords = ?');
SET @a = 1_keywords;
SET @SqlCmd = CONCAT(@SqlCmd , ' WHERE Pid=?');
SET @b = pid;
PREPARE stmt from @SqlCmd;
EXECUTE stmt USING @a,@b;
ELSE
SET @SqlCmd = CONCAT(@SqlCmd , ' WHERE Pid=?');
SET @b = pid;
PREPARE stmt from @SqlCmd;
EXECUTE stmt USING @b;
END IF; END IF;END $$DELIMITER ;
DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`UpdateOldrec` $$
CREATE DEFINER=`test`@`%` PROCEDURE `UpdateOldrec`(IN 1_cid int,IN 1_url char(250),IN 1_keywords char(250),IN 1_Title char(250),IN1_Contents char(250),IN 1_Other char(250),IN 1_Image char(250),IN 1_Category int)
BEGIN
DECLARE pid INT DEFAULT (select `Pid` From data_index where `Url`=1_url and `Cid`=1_cid);
DECLARE Str1 varchar(300) DEFAULT '';
IF pid>0 then
IF LENGTH(1_Title)> 0 THEN
UPDATE `data_contents` SET `Title` = 1_Title,`Contents` = 1_Contents ,`Other` = 1_Other ,`images` = 1_Image WHERE `Pid`= pid LIMIT 1 ;
END IF; SET @SqlCmd ="update data_index SET Edit_date = UNIX_TIMESTAMP()";
IF LENGTH(1_keywords) > 0 THEN SET @SqlCmd = CONCAT(@SqlCmd , ',Keywords = ?');
SET @a = 1_keywords;
SET @SqlCmd = CONCAT(@SqlCmd , ' WHERE Pid=?');
SET @b = pid;
PREPARE stmt from @SqlCmd;
EXECUTE stmt USING @a,@b;
ELSE
SET @SqlCmd = CONCAT(@SqlCmd , ' WHERE Pid=?');
SET @b = pid;
PREPARE stmt from @SqlCmd;
EXECUTE stmt USING @b;
END IF; END IF;END $$DELIMITER ;
Query OK, 0 rows affected (0.00 sec)mysql> select @sql;
+--------------------+
| @sql |
+--------------------+
| select * from tbl1 |
+--------------------+
1 row in set (0.00 sec)mysql>
mysql> CREATE PROCEDURE `UpdateOldrec`(
-> BEGIN
-> declare pid int;
-> set pid=100;
-> select pid;
-> set pid=pid+10;
->
-> end;
-> //
Query OK, 0 rows affected (0.22 sec)mysql> delimiter ;
mysql> call UpdateOldrec();
+------+
| pid |
+------+
| 100 |
+------+
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql>