DB schema
CREATE TABLE Users(
UserId int auto_increment NOT NULL Primary key,
Username varchar(20) NOT NULL,
Password varchar(20) NOT NULL,
Email nvarchar(30) NOT NULL,
CreatedDate datetime NOT NULL,
LastLoginDate datetime NULL
);
and the stored procedure that I am trying to write is
我想写的存储过程是
CREATE procedure Insert_User(
in uname NVARCHAR(20),
in Pass NVARCHAR(20),
in Em NVARCHAR(30)),
out result int)
BEGIN
IF EXISTS(SELECT UserId FROM Users WHERE Username = uname)
BEGIN
set result=-1 -- Username exists.
END
elif EXISTS(SELECT UserId FROM Users WHERE Email = Em)
BEGIN
set result=-2 -- Email exists.
END
ELSE
BEGIN
INSERT INTO Users
(Username
,Password
,Email
,CreatedDate)
VALUES
(@Username
,@Password
,@Email
,GETDATE())
set result=SCOPE_IDENTITY() -- UserId
END
END
What am I am doing wrong?
我做错了什么?
1 个解决方案
#1
0
You have plenty of issues in the procedure here is the one in mysql.
你在这里的程序中有很多问题是mysql中的问题。
delimiter //
CREATE procedure Insert_User(
in uname VARCHAR(20),
in Pass VARCHAR(20),
in Em VARCHAR(30),
out result int
)
BEGIN
IF (select count(*) from Users WHERE Username = uname ) > 0 then
begin
set result=-1 ;
end ;
elseif (select count(*) from Users WHERE Email = Em ) > 0 then
begin
set result=-2 ;
end ;
else
begin
INSERT INTO Users
(Username
,Password
,Email
,CreatedDate)
VALUES
(uname
,Pass
,Em
,curdate()) ;
set result=LAST_INSERT_ID();
end ;
end if ;
END ;//
delimiter ;
Here is the test case in mysql cli
这是mysql cli中的测试用例
mysql> CREATE TABLE Users(
-> UserId int auto_increment NOT NULL Primary key,
-> Username varchar(20) NOT NULL,
-> Password varchar(20) NOT NULL,
-> Email nvarchar(30) NOT NULL,
-> CreatedDate datetime NOT NULL,
-> LastLoginDate datetime NULL
->
-> );
Query OK, 0 rows affected (0.16 sec)
mysql> delimiter //
mysql> CREATE procedure Insert_User(
-> in uname VARCHAR(20),
-> in Pass VARCHAR(20),
-> in Em VARCHAR(30),
-> out result int
-> )
-> BEGIN
-> IF (select count(*) from Users WHERE Username = uname ) > 0 then
-> begin
-> set result=-1 ;
-> end ;
-> elseif (select count(*) from Users WHERE Email = Em ) > 0 then
-> begin
-> set result=-2 ;
-> end ;
-> else
-> begin
-> INSERT INTO Users
-> (Username
-> ,Password
-> ,Email
-> ,CreatedDate)
-> VALUES
-> (uname
-> ,Pass
-> ,Em
-> ,curdate()) ;
->
-> set result=LAST_INSERT_ID();
-> end ;
-> end if ;
-> END ;//
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql> call Insert_User('Abhik','abhik','abhik@aa.com',@res);
Query OK, 1 row affected (0.05 sec)
mysql> select @res;
+------+
| @res |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> select * from Users;
+--------+----------+----------+--------------+---------------------+---------------+
| UserId | Username | Password | Email | CreatedDate | LastLoginDate |
+--------+----------+----------+--------------+---------------------+---------------+
| 1 | Abhik | abhik | abhik@aa.com | 2015-03-14 00:00:00 | NULL |
+--------+----------+----------+--------------+---------------------+---------------+
1 row in set (0.00 sec)
mysql> call Insert_User('Abhik','abhik','abhik@aa.com',@res);
Query OK, 0 rows affected (0.00 sec)
mysql> select @res;
+------+
| @res |
+------+
| -1 |
+------+
1 row in set (0.00 sec)
#1
0
You have plenty of issues in the procedure here is the one in mysql.
你在这里的程序中有很多问题是mysql中的问题。
delimiter //
CREATE procedure Insert_User(
in uname VARCHAR(20),
in Pass VARCHAR(20),
in Em VARCHAR(30),
out result int
)
BEGIN
IF (select count(*) from Users WHERE Username = uname ) > 0 then
begin
set result=-1 ;
end ;
elseif (select count(*) from Users WHERE Email = Em ) > 0 then
begin
set result=-2 ;
end ;
else
begin
INSERT INTO Users
(Username
,Password
,Email
,CreatedDate)
VALUES
(uname
,Pass
,Em
,curdate()) ;
set result=LAST_INSERT_ID();
end ;
end if ;
END ;//
delimiter ;
Here is the test case in mysql cli
这是mysql cli中的测试用例
mysql> CREATE TABLE Users(
-> UserId int auto_increment NOT NULL Primary key,
-> Username varchar(20) NOT NULL,
-> Password varchar(20) NOT NULL,
-> Email nvarchar(30) NOT NULL,
-> CreatedDate datetime NOT NULL,
-> LastLoginDate datetime NULL
->
-> );
Query OK, 0 rows affected (0.16 sec)
mysql> delimiter //
mysql> CREATE procedure Insert_User(
-> in uname VARCHAR(20),
-> in Pass VARCHAR(20),
-> in Em VARCHAR(30),
-> out result int
-> )
-> BEGIN
-> IF (select count(*) from Users WHERE Username = uname ) > 0 then
-> begin
-> set result=-1 ;
-> end ;
-> elseif (select count(*) from Users WHERE Email = Em ) > 0 then
-> begin
-> set result=-2 ;
-> end ;
-> else
-> begin
-> INSERT INTO Users
-> (Username
-> ,Password
-> ,Email
-> ,CreatedDate)
-> VALUES
-> (uname
-> ,Pass
-> ,Em
-> ,curdate()) ;
->
-> set result=LAST_INSERT_ID();
-> end ;
-> end if ;
-> END ;//
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql> call Insert_User('Abhik','abhik','abhik@aa.com',@res);
Query OK, 1 row affected (0.05 sec)
mysql> select @res;
+------+
| @res |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> select * from Users;
+--------+----------+----------+--------------+---------------------+---------------+
| UserId | Username | Password | Email | CreatedDate | LastLoginDate |
+--------+----------+----------+--------------+---------------------+---------------+
| 1 | Abhik | abhik | abhik@aa.com | 2015-03-14 00:00:00 | NULL |
+--------+----------+----------+--------------+---------------------+---------------+
1 row in set (0.00 sec)
mysql> call Insert_User('Abhik','abhik','abhik@aa.com',@res);
Query OK, 0 rows affected (0.00 sec)
mysql> select @res;
+------+
| @res |
+------+
| -1 |
+------+
1 row in set (0.00 sec)