我正在使用 WinRDBI 在 SQL 中创建存储过程进行分配，但我不明白为什么该过程不起作用

Question

我的任务是第一次创建存储过程，我一直在使用WinRDBI，因为教授需要它

CREATE PROCEDURE uspGetDeptStats(
@DEPARTMENTNAME VARCHAR(15))
AS
BEGIN
SET NOCOUNT ON
DECLARE @FIRSTNAME AS VARCHAR(25)
DECLARE @LASTNAME  AS VARCHAR(25)
DECLARE @NO_OF_EMPLOYEES AS INT
DECLARE @TOTAL_SALARY AS INT
DECLARE @TOTAL_NO_OF_DEPENDENTS AS INT
DECLARE @TOTAL_HOURS AS DECIMAL(5,1)
DECLARE @DEPTNO AS INT
SELECT DNUMBER INTO DEPTNO FROM DEPARTMENT WHERE DNAME = DEPARTMENTNAME
SELECT
FNAME, LNAME INTO FIRSTNAME, LASTNAME
FROM DEPARTMENT D
JOIN EMPLOYEE E ON D.MGRSSN = E.SSN
WHERE DNUMBER = DEPTNO;
SELECT FIRSTNAME,LASTNAME,NO_OF_EMPLOYEES,TOTAL_SALARY,TOTAL_NO_OF_DEPENDENTS, TOTAL_HOURS;
END

我不断地得到"；query_sql.：第18行"检查输入格式"；或者作为错误的行号上的一些变化。

我试着编译代码，每次都收到一个错误。我尝试更改分号的位置，删除所有分号，然后在末尾只留下一个分号，但这些尝试都没有改变任何内容。如果有人能向我解释语法有什么问题，以及为什么它不想编译，我将不胜感激。任何指针都会有所帮助。感谢您的阅读。

编辑：晚上10:37(2022年4月2日(：我把我的程序放入在线编译器中，看看我是否能更具体地找到问题所在。以下是我在对其他类似问题的建议进行编辑后放入在线编译器的全部代码：

DROP PROCEDURE IF EXISTS `uspGetDeptStats`;
DELIMITER //
-- start of procedure creation
CREATE PROCEDURE `uspGetDeptStats` (
in DEPARTMENTNAME VARCHAR(15) )
BEGIN
-- declare local variables
DECLARE FIRSTNAME VARCHAR(25);
DECLARE LASTNAME VARCHAR(25);
DECLARE NO_OF_EMPLOYEES INT;
DECLARE TOTAL_SALARY INT;
DECLARE TOTAL_NO_OF_DEPENDENTS INT;
DECLARE TOTAL_HOURS DECIMAL(5,1);
DECLARE DEPTNO INT;
-- get the department number
SELECT DNUMBER INTO DEPTNO FROM DEPARTMENT WHERE DNAME = DEPARTMENTNAME;
-- get firstname and lastname of the manager of the department
SELECT
FNAME , LNAME INTO FIRSTNAME,LASTNAME
FROM DEPARTMENT D
JOIN EMPLOYEE E ON D.MGRSSN = E.SSN
WHERE DNUMBER = DEPTNO ;
-- to get the number of employees and total salary of all employees work for this department
SELECT COUNT(*) , SUM(SALARY) INTO NO_OF_EMPLOYEES,TOTAL_SALARY FROM EMPLOYEE WHERE DNO = DEPTNO;
-- to get the total number of dependents of all employees who work for this department
SELECT
COUNT(*) INTO TOTAL_NO_OF_DEPENDENTS
FROM EMPLOYEE E
JOIN DEPENDENT D ON D.ESSN = E.SSN
WHERE DNO=DEPTNO;
-- to get the total weekly work hours of employees who belong to this department and who work on the projects that are controlled by this department
SELECT SUM(HOURS) INTO TOTAL_HOURS FROM WORKS_ON W
JOIN PROJECT P ON P.PNUMBER = W.PNO
JOIN EMPLOYEE E ON E.SSN = W.ESSN
WHERE DNO =DEPTNO AND DNUM = DEPTNO;
-- displaying all result
SELECT FIRSTNAME,LASTNAME,NO_OF_EMPLOYEES,TOTAL_SALARY,TOTAL_NO_OF_DEPENDENTS, TOTAL_HOURS;
END //
DELIMITER ;
-- end of procedure

它仍然没有编译，但在线编译器提供了更多信息。现在的错误是：

Error: near line 1: near "PROCEDURE": syntax error 
Error: near line 2: near "DELIMITER": syntax error 
Error: near line 9: near "DECLARE": syntax error 
Error: near line 10: near "DECLARE": syntax error 
Error: near line 11: near "DECLARE": syntax error 
Error: near line 12: near "DECLARE": syntax error 
Error: near line 13: near "DECLARE": syntax error 
Error: near line 14: near "DECLARE": syntax error 
Error: near line 16: near "INTO": syntax error 
Error: near line 18: near "INTO": syntax error 
Error: near line 25: near "INTO": syntax error 
Error: near line 27: near "INTO": syntax error 
Error: near line 33: near "INTO": syntax error 
Error: near line 38: no such column: FIRSTNAME 
Error: near line 39: near "/": syntax error

再次感谢任何协助。非常感谢。

编辑：下午2:50(2022年4月3日(：根据Ramesh的建议，我尝试在每次调用或声明局部变量时都在其后面加一个@。我仍然会收到几乎相同的语法错误。这是新代码：

DROP procedure IF EXISTS `uspGetDeptStats`;
-- start of procedure creation
DELIMITER $$
CREATE PROCEDURE `uspGetDeptStats` (
in DEPARTMENTNAME VARCHAR(15) )
BEGIN
-- declare local variables
DECLARE @FIRSTNAME VARCHAR(25);
DECLARE @LASTNAME VARCHAR(25);
DECLARE @NO_OF_EMPLOYEES INT;
DECLARE @TOTAL_SALARY INT;
DECLARE @TOTAL_NO_OF_DEPENDENTS INT;
DECLARE @TOTAL_HOURS DECIMAL(5,1);
DECLARE @DEPTNO INT;
-- get the department number
SELECT DNUMBER INTO @DEPTNO FROM DEPARTMENT WHERE DNAME = @DEPARTMENTNAME;
-- get firstname and lastname of the manager of the department
SELECT
FNAME , LNAME INTO @FIRSTNAME,@LASTNAME
FROM DEPARTMENT D
JOIN EMPLOYEE E ON D.MGRSSN = E.SSN
WHERE DNUMBER = @DEPTNO;
-- to get the number of employees and total salary of all employees work for this department
SELECT COUNT(*) , SUM(SALARY) INTO @NO_OF_EMPLOYEES,@TOTAL_SALARY FROM EMPLOYEE WHERE DNO = @DEPTNO;
-- to get the total number of dependents of all employees who work for this department
SELECT
COUNT(*) INTO @TOTAL_NO_OF_DEPENDENTS
FROM EMPLOYEE E
JOIN DEPENDENT D ON D.ESSN = E.SSN
WHERE DNO=@DEPTNO;
-- to get the total weekly work hours of employees who belong to this department and who work on the projects that are controlled by this department
SELECT SUM(HOURS) INTO @TOTAL_HOURS FROM WORKS_ON W
JOIN PROJECT P ON P.PNUMBER = W.PNO
JOIN EMPLOYEE E ON E.SSN = W.ESSN
WHERE DNO =DEPTNO AND DNUM = DEPTNO;
-- displaying all result
SELECT @FIRSTNAME,@LASTNAME,@NO_OF_EMPLOYEES,@TOTAL_SALARY,@TOTAL_NO_OF_DEPENDENTS, @TOTAL_HOURS;
END$$
DELIMITER ;

以下是在线编译器发现的所有错误：

Error: near line 1: near "procedure": syntax error
Error: near line 3: near "DELIMITER": syntax error
Error: near line 9: near "DECLARE": syntax error
Error: near line 10: near "DECLARE": syntax error
Error: near line 11: near "DECLARE": syntax error
Error: near line 12: near "DECLARE": syntax error
Error: near line 13: near "DECLARE": syntax error
Error: near line 14: near "DECLARE": syntax error
Error: near line 16: near "INTO": syntax error
Error: near line 18: near "INTO": syntax error
Error: near line 25: near "INTO": syntax error
Error: near line 27: near "INTO": syntax error
Error: near line 33: near "INTO": syntax error
|||||
Error: near line 39: near "END$$": syntax error

我觉得在这一点上它变得多余了，但再次感谢任何帮助，因为今天是任务的最后一天。再次感谢！

Answer 1

在SQL server中，您需要始终在变量名之前保留@，我认为您在选择查询时缺少了这一点，请尝试使用SQL server开发人员版编辑器来检查语法和查询更正

DECLARE @DEPTNO AS INT
SELECT DNUMBER INTO @DEPTNO FROM DEPARTMENT