mysql 存储过程 创建

vitou 发布于 2012/06/12 15:02
阅读 1K+
收藏 1

我想知道是不是可以按我下面的这种做法直接创建存储过程。如不行,还有什么比较简便的方法?

这段语句是在Navicat for Mysql 中存储过程中的ddl中 拿到的,创建的存储过程是没有问题的。 现在就是碰到一个问题是,我直接在mysql的命令行中输入:

CREATE DEFINER=`root`@`%` PROCEDURE `sp_faq`(IN `faq_number` varchar(255),IN `faq_name` varchar(255),IN `faq_type` varchar(50),IN `faq_topic` varchar(50),IN `faq_answer` varchar(255),IN `first_result` int,IN `page_size` int)
BEGIN
 SET @SELECT ='select * from faq f inner join faq_node fn ,faq_node fn1 
 where f.faq_node_id = fn.id and fn1.id =fn.parent_id';
 SET @CONDITION =' and 1=1';
 
 IF faq_number IS NOT NULL THEN
  SET @v_faq_number = CONCAT('''%',faq_number,'%''');
  SET @CONDITION = CONCAT(@CONDITION ,' and f.faq_number like ',@v_faq_number);
 END IF;
 
  IF faq_topic IS NOT NULL THEN
  SET @CONDITION = CONCAT(@CONDITION ,' and f.faq_node_id in (',faq_topic,')');
 END IF;

 IF faq_type IS NOT NULL THEN
  SET @CONDITION = CONCAT(@condition ,' and fn1.id in (',faq_type,')');
 END IF;

 IF faq_name IS NOT NULL THEN
  SET @v_faq_name = CONCAT('''%',faq_name,'%''');
  SET @CONDITION = CONCAT(@CONDITION ,' and f.question like',@v_faq_name);
 END IF;
 
 IF faq_answer IS NOT NULL THEN
  SET @v_faq_answer = CONCAT('''%',faq_answer,'%''');
  SET @CONDITION = CONCAT(@CONDITION ,' and f.answer like ',@v_faq_answer);
 END IF;
 
 IF page_size != -1 THEN
  SET @LIMIT =CONCAT(' limit ',CAST(first_result AS CHAR),',',CAST(page_size AS CHAR));
 ELSE 
  SET @LIMIT ='';
 END IF;
 
 SET @ORDER =' order by f.create_time';
 SET @s =CONCAT(@SELECT ,@CONDITION ,@LIMIT);
 PREPARE stmt FROM @s ;
 EXECUTE stmt;
 
END

时报错说, SET @SELECT ='select * from faq f inner join faq_node fn ,faq_node fn1
 where f.faq_node_id = fn.id and fn1.id =fn.parent_id';
有错误, 1064 - 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 '' at line 3。

加载中
0
囧小约
囧小约
定义存储过程前要先用delimter,把默认的分号结束符换成别的符号,定义完后用那个符号结束,再用delimter ;换回来。
返回顶部
顶部