在PHPMYADMIN里建的,来个最简单的测试语句,语句为
CREATE PROCEDURE tesdddt()
BEGIN
select * from mmnews;
END;
错误
SQL 查询: CREATE PROCEDURE tesdddt( ) BEGIN SELECT *
FROM mmnews;
MySQL 返回:#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
CREATE PROCEDURE tesdddt()
BEGIN
select * from mmnews;
END;
错误
SQL 查询: CREATE PROCEDURE tesdddt( ) BEGIN SELECT *
FROM mmnews;
MySQL 返回:#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
BEGIN<br>
select * from mmnews; <br>
END; <br>csdn没换行,手工加下
DELIMITER $$
CREATE PROCEDURE tesdddt()
BEGIN
select * from mmnews;
END$$
DELIMITER ; set @sql=concat('select count(id) from aaaa where bbbb = ',asb);
->
set @sql=concat('select count(id) into @aid from aaaa where bbbb = ',asb);
asb是字符串,但为什么查询的时候,它是以INT返回的?
你执行的结果是count(id),当然是INT
mysql> CREATE PROCEDURE proc1(OUT s int)
-> BEGIN
-> SELECT COUNT(*) INTO s FROM user;
-> END
-> //
mysql> DELIMITER ;
这里需要注意的是DELIMITER //和DELIMITER ;两句,DELIMITER是分割符的意思,因为MySQL默认以";"为分隔符,如果我们没有声明分割符,那么编译器会把存储过程当成SQL语句进行处理,则存储过程的编译过程会报错,所以要事先用DELIMITER关键字申明当前段分隔符,这样MySQL才会将";"当做存储过程中的代码,不会执行这些代码,用完了之后要把分隔符还原。
--------------------------------------------------------------------
asb是个字符串的参数,这个存储过程的目的是,输出这个参数后,求出它的count(id)值
读取方式:
call tesdddt('王继山')
然后出来叫王继山的总条数/count(id)
----------
谢谢
DELIMITER $$
DROP PROCEDURE IF EXISTS tesdddt$$
CREATE PROCEDURE tesdddt(asb VARCHAR(10),OUT aa INT)
BEGIN
SET @asql=CONCAT('select count(id) into @aa from aaaa where bbbb=\'',asb,'\'');
PREPARE stml FROM @asql;
EXECUTE stml;
SET aa=@aa;
END$$
DELIMITER ; CALL tesdddt('王继山',@ss);
SELECT @ss