mysql 存储过程　怎么定义变量　请问

mysql 存储过程　如何定义变量　请教

MSSQL　sql server 2000写法

go

create proc loadUser

@uname varchar(16),

@upwd varchar(16),

@count int output

as

select * from users where uname=@uname and upwd=@upwd

if(@@error=0 and @@rowcount = 1)

set @count = 1;

else set @count=0;

go

在Mysql 当中怎么写呀　

如何　定义一个变量　

create procedure login(uname varchar(64),upwd varchar(64),out counts int)

[color=#00FFFF]DECLARE iCount int;

begin

SET iCount = 10;  

SELECT iCount;

end[/color]

对了请问下　IF EXISTS(SELECT * FROM sysdatabases WHERE name=’bookshop’)　这样的语句　mysql 支持吗？就是

判断　有没有查出内容来？？

如何　定义一个变量

你的方法是对的，用 DECLARE iCount INT; 来定义变量。

IF EXISTS(。。。)这种语句目前还不被MySQL 5.x支持，实现相同功能的做法是再定义一个变量，然后利用select .. into来对这个变量赋值，然后判断是否exists符合条件的记录。如果多记录，可以改为 select count(*) into ..

SQL code

mysql> delimiter //
mysql>
mysql> CREATE PROCEDURE login (uname varchar(64),upwd varchar(64),out counts int)
    ->   BEGIN
    ->     DECLARE iCount INT;
    ->     DECLARE bExist INT DEFAULT 0;
    ->
    ->     SET iCount = 10;
    ->
    ->  SELECT 1 into bExist FROM t WHERE name=uname; #use the variable to check the IF EXISTS
    ->
    ->  IF bExist>0 then
    ->          SELECT  iCount,'Record Found.';
    ->  ELSE
    ->          SELECT  iCount,'NOT EXISTS';
    ->  END IF;
    ->
    ->   END;
    ->
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql> call login('f3','pswd',@i);
+--------+---------------+
| iCount | Record Found. |
+--------+---------------+
|     10 | Record Found. |
+--------+---------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> call login('unkonw','pswd',@i);
+--------+------------+
| iCount | NOT EXISTS |
+--------+------------+
|     10 | NOT EXISTS |
+--------+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql>


好像能用吧，我的存储过程就是是这样写的，没有报错呀


DECLARE  iCount  int;
begin
SET  iCount  =  10;  
SELECT  iCount;  

MYSQ中是可以的

对了请问下　IF EXISTS(SELECT * FROM sysdatabases WHERE name='bookshop')　这样的语句　mysql 支持吗？就是
判断　有没有查出内容来？？

select count(*) into @ee FROM sysdatabases WHERE name='bookshop'
if @ee>1 then
select 'OK';
ELSE
select 'NO';
END IF