MySQL 用存储过程更新员工工作时间表

mysql更新表中数据的存储过程，代码如下：

mysql>CREATE TABLE Employee( //创建表

->idint,

->first_nameVARCHAR(15),

->last_name VARCHAR(15),

->start_dateDATE,

->end_date DATE,

->salaryFLOAT(8,2),

->city VARCHAR(10),

->description VARCHAR(15)

->)

Query OK, 0 rows affected (0.01 sec)

--//导入数据

mysql>insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

-> values (1,'Jason','Martin', '19960725', '20060725', 1234.56, 'Toronto', 'Programmer')

Query OK, 1 row affected (0.00 sec)

CREATE DEFINER = CURRENT_USER FUNCTION `NewProc`(`url` varchar,`id` int,`cataLogType` int,`parentID` int,`parentType` int,`caption` varchar)

RETURNS int(32)

BEGIN

DECLARE iCount int DEFAULT 0

iCount = SELECT COUNT(*) FROM catalog WHERE url = url

IF iCount == 0 THEN

INSERT INTO catalog VALUES(url,id,cataLogType,parentID,parentType,caption)

RETURN 1

ELSE

UPDATE catalog SET id=id,cataLogType=cataLogType,parentID=parentID,parentType=parentType,caption=caption WHERE url=url

RETURN 0

END IF

END

//问题是保存上面的存储过处出错。

提示：1064-You have an error in your SQL syntaxcheck the manual that corresponds to your MySQL server version for the right syntax to use near 'id' int ......

可能是ID是关键字，不让用，请问是不是这个回事呢？

上面是我的存储过程，catalog是一个表名。有字段url，id,cataLogType,parentID,parentType,caption

我的参数是

MySqlParameter[] pramsCatalog ={new MySqlParameter(@"?url", MySqlDbType.VarChar,256),

new MySqlParameter(@"?id",MySqlDbType.Int32),

new MySqlParameter(@"?cataLogType",MySqlDbType.Int32),

new MySqlParameter(@"?parentID",MySqlDbType.Int32),

new MySqlParameter(@"?parentType", MySqlDbType.Int32),

new MySqlParameter(@"?caption",MySqlDbType.VarChar,256)}

create procedure batchUpdate(in n int)  

begin  

declare inParam int default n  

declare i int default 1  

declare id int  

declare username varchar(255)  

declare integral varchar(255)  

declare ranking int  

declare _resultSet cursor for select a.id, a.username,a.integral,a.ranking from user a order by a.integral desc limit inParam  

open _resultSet  

while i <= inParam do  

fetch _resultSet into id, username, integral,ranking  

update user a set a.ranking = i where a.id = id  

set i = i +1  

end while  

close _resultSet  

end  

  

mysql> select * from user  

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

| id | username | integral | ranking |  

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

|  1 | abc      |        1 |       0 |  

|  2 | abc      |        2 |       0 |  

|  3 | abc      |        3 |       0 |  

|  4 | abc      |        4 |       0 |  

|  5 | abc      |        5 |       0 |  

|  6 | abc      |        6 |       0 |  

|  7 | abc      |        7 |       0 |  

|  8 | abc      |        8 |       0 |  

|  9 | abc      |        9 |       0 |  

| 10 | abc      |       10 |       0 |  

| 11 | abc      |       11 |       0 |  

| 12 | abc      |       12 |       0 |  

| 13 | abc      |       13 |       0 |  

| 14 | abc      |       14 |       0 |  

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

14 rows in set  

  

mysql> call batchUpdate(14)  

Query OK, 1 row affected  

  

mysql> select * from user  

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

| id | username | integral | ranking |  

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

|  1 | abc      |        1 |      14 |  

|  2 | abc      |        2 |      13 |  

|  3 | abc      |        3 |      12 |  

|  4 | abc      |        4 |      11 |  

|  5 | abc      |        5 |      10 |  

|  6 | abc      |        6 |       9 |  

|  7 | abc      |        7 |       8 |  

|  8 | abc      |        8 |       7 |  

|  9 | abc      |        9 |       6 |  

| 10 | abc      |       10 |       5 |  

| 11 | abc      |       11 |       4 |  

| 12 | abc      |       12 |       3 |  

| 13 | abc      |       13 |       2 |  

| 14 | abc      |       14 |       1 |  

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

14 rows in set

---