Mysql存储过程(http://dev.mysql.com/doc/refman/5.1/zh/index.html)

http://dev.mysql.com/doc/refman/5.1/zh/index.html

create procedure 'test'(param varchar(20))
begin
declare len int;
set len = LENGTH(param);
if(len>0) then
 select * from st where id = param;
end if;
end

调用时 call test('');正常，call test('001');就会提示：
1267 -Illengal mix of collations(gbk_chinese_ci,IMPLICIT)and(latin1_swedish_ci,IMPLICIT)for operatopn '=' 

DELIMITER $$;

DROP PROCEDURE IF EXISTS sp_test$$

CREATE PROCEDURE sp_test()
BEGIN
declare cnt int;
select 1 from account where username='123' into cnt;
if cnt != 0 then
insert into account (username,passwd) values('123','123');
end if;
END$$

DELIMITER ;$$

mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)

    -> BEGIN

    ->   SELECT COUNT(*) INTO param1 FROM t;

    -> END

    -> //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL simpleproc(@a);

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;

+------+

| @a   |

+------+

| 3    |

+------+

1 row in set (0.00 sec)

当使用delimiter命令时，你应该避免使用反斜杠(‘/’)字符，因为那是MySQL的转义字符。

下列是一个例子，一个采用参数的函数使用一个SQL函数执行一个操作，并返回结果：

mysql> delimiter //

mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)

    -> RETURN CONCAT('Hello, ',s,'!');

    -> //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> SELECT hello('world');

+----------------+

| hello('world') |

+----------------+

| Hello, world!  |

+----------------+

1 row in set (0.00 sec)

 问题已经解决。答案如下：
mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> SET @a = 3;
mysql> SET @b = 4;
mysql> EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|          5 |
+------------+
（注意例子中的pow是个函数，？代表变量。当时被pow忽悠了。）