mysql - MySQL存储过程出现游标错误

我正在尝试编写一个MySQL存储过程，该存储过程是（1）将值插入到Employee表中，（2）获取新员工的部门编号，扫描Project表中该部门所处理的任何项目，以及（3）插入具有员工的SSN进入Works_On表。

我正在尝试使用游标执行此操作，但一直遇到无法弄清的语法错误。当前错误是在我声明游标的时候，但是我不知道如何解决它。我已经尝试了很多事情，希望有人能够看到错误。

    Use Company
DELIMITER //

Create Procedure SP_Insert_NewEmployee
(
    IN fname varchar(30),
    IN minit char(1),
    IN lname varchar(30),
    IN ssn char(9),
    IN bdate date,
    IN address varchar(50),
    IN sex char(1),
    IN salary decimal(10,1),
    IN super_ssn char(9),
    IN dno int
)
Begin
Declare projectNumber Integer;
Declare myCursor2 = CURSOR FOR
    SELECT Pnumber
    FROM PROJECT
    Where Dnum = @dno;

#Insert into Employee
Insert into EMPLOYEE
    (
        Fname,
        Minit ,
        Lname ,
        Ssn ,
        Bdate ,
        Address ,
        Sex,
        Salary ,
        Super_ssn,
        Dno
    )
Values
    (
        $fname ,
        $minit ,
        $lname,
        $ssn ,
        $bdate ,
        $address ,
        $sex ,
        $salary ,
        $super_ssn ,
        $dno
    );
    END

#Find projects by new employee's dept




OPEN myCursor2;

FETCH NEXT
    FROM myCursor2
    INTO
        projectNumber


WHILE @@FETCH_STATUS = 0

BEGIN
 Insert Into WORKS_ON
 Values
    (
        ssn,
        projectNumber,
        0
    )

 FETCH NEXT
 FROM myCursor2
    INTO
        projectNumber
END

CLOSE myCursor2;
DEALLOCATE myCursor2;
END
END
//
DELIMITER ;

最佳答案

我们已根据我的要求删除了您的问题，因此您不会感到困惑。您的代码有几个语法错误。有些只是用sql编写的，有些则缺少逗号和错误的美元符号。希望这会有所帮助。

USE Company;
DROP PROCEDURE IF EXISTS SP_Insert_NewEmployee;
DELIMITER //
CREATE PROCEDURE SP_Insert_NewEmployee
(
    IN fname varchar(30),
    IN minit char(1),
    IN lname varchar(30),
    IN ssn char(9),
    IN bdate date,
    IN address varchar(50),
    IN sex char(1),
    IN salary decimal(10,1),
    IN super_ssn char(9),
    IN dno int
)
Begin
DECLARE done INT DEFAULT FALSE;
Declare projectNumber Integer;
Declare myCursor2 CURSOR FOR SELECT Pnumber FROM PROJECT Where Dnum = dno;

#Insert into Employee
Insert into EMPLOYEE
    (   Fname,
        Minit ,
        Lname ,
        Ssn ,
        Bdate ,
        Address ,
        Sex,
        Salary ,
        Super_ssn,
        Dno
    )
Values
    (   fname ,
        minit ,
        lname,
        ssn ,
        bdate ,
        address ,
        sex ,
        salary ,
        super_ssn ,
        dno
    );

#Find projects by new employee's dept

OPEN myCursor2;

do_something: LOOP
    FETCH myCursor2 INTO projectNumber;
    IF done THEN
      LEAVE do_something;
    END IF;
    Insert Into WORKS_ON Values (ssn,projectNumber,0);
END LOOP;

CLOSE myCursor2;

END //
DELIMITER ;

在此处紧密遵循手册页：CURSORS。