如何修复这个SQL程序？

Question

我在网上发现了这个SQL，但是它给了我一些错误，我不知道如何修复它。

请帮我处理一下这个SQL好吗？

Create Table Veterinarians
(vetName varchar2(20),
vid Number(5) primary key);


Create Table Dogs
(dogName varchar2(20),
did Number(5) primary key);


Create Table Location
(lid Number(5) primary key,
locName varchar2(20),);


Create Table Examine
(vid int foreign key references Veterinarians(vid),
did int foreign key references Dogs(did),
lid int foreign key references Location(lid),
fee Number(5));

INSERT INTO Veterinarians VALUES ('Alice',112);
INSERT INTO Veterinarians VALUES ('Mary',211);
INSERT INTO Veterinarians VALUES ('Jim',111);

INSERT INTO Dogs VALUES ('Spot',324);
INSERT INTO Dogs VALUES ('Fido',582);
INSERT INTO Dogs VALUES ('Tiger',731);

INSERT INTO Location VALUES (1001,'St.Cloud');
INSERT INTO Location VALUES (1002,'Minneapolis');
INSERT INTO Location VALUES (1003,'Duluth');

INSERT INTO Examine VALUES (111,324,1001,10);
INSERT INTO Examine VALUES (111,731,1003,20);
INSERT INTO Examine VALUES (112,324,1001,30);
INSERT INTO Examine VALUES (112,582,1001,50);
INSERT INTO Examine VALUES (112,731,1002,35);
INSERT INTO Examine VALUES (211,324,1001,25);
INSERT INTO Examine VALUES (211,582,1002,35);
INSERT INTO Examine VALUES (211,731,1001,20);
INSERT INTO Examine VALUES (211,582,1001,25);
INSERT INTO Examine VALUES (211,582,1003,65);

--Creating a stored procedure
Create PROCEDURE display_Avg(vid int) AS
BEGIN
select v.vid,v.vetName,Avg(e.fee) as AverageFee
from Veterinarians v
INNER JOIN Examine e
ON v.vid=e.vid
Where v.vid=display_Avg.vid
Group By v.vid,v.vetName;
END;

--Executing stored procedure
Execute display_Avg(112);

CREATE PROCEDURE display_DogNames 
AS BEGIN
select dogName
from Dogs
INNER JOIN Examine
ON Examine.did=Dogs.did
INNER JOIN Location
On Examine.lid=Location.lid
Where Location.lid=1001
AND Location.lid=1002
AND Location.lid=1003
END;

这是它给我带来的错误

Msg 102，级别15，状态1，过程display_Avg，第46行批处理开始行0附近的'vid‘不正确的语法。 Msg 102，15级，状态1，过程display_Avg，第57行批处理开始行0附近的'112‘不正确的语法。 Msg 111、级别15、状态1、过程display_Avg、第60行批处理开始行0‘创建/更改过程’必须是查询批处理中的第一个语句。

Answer 1

您可以使用以下代码：

    CREATE TABLE Veterinarians (
      vetName varchar(20),
      vid int NOT NULL PRIMARY KEY
    );
    
    
    CREATE TABLE Dogs (
      dogName varchar(20),
      did int NOT NULL PRIMARY KEY
    );
    
    
    CREATE TABLE tblLocation (
      lid int NOT NULL PRIMARY KEY,
      locName varchar(20)
    );
    
    
    CREATE TABLE Examine (
      vid int NOT NULL FOREIGN KEY REFERENCES Veterinarians (vid),
      did int NOT NULL FOREIGN KEY REFERENCES Dogs (did),
      lid int NOT NULL FOREIGN KEY REFERENCES tblLocation (lid),
      fee int
    );
    
    INSERT INTO Veterinarians
      VALUES ('Alice', 112);
    INSERT INTO Veterinarians
      VALUES ('Mary', 211);
    INSERT INTO Veterinarians
      VALUES ('Jim', 111);
    
    INSERT INTO Dogs
      VALUES ('Spot', 324);
    INSERT INTO Dogs
      VALUES ('Fido', 582);
    INSERT INTO Dogs
      VALUES ('Tiger', 731);
    
    INSERT INTO tblLocation
      VALUES (1001, 'St.Cloud');
    INSERT INTO tblLocation
      VALUES (1002, 'Minneapolis');
    INSERT INTO tblLocation
      VALUES (1003, 'Duluth');
    
    INSERT INTO Examine
      VALUES (111, 324, 1001, 10);
    INSERT INTO Examine
      VALUES (111, 731, 1003, 20);
    INSERT INTO Examine
      VALUES (112, 324, 1001, 30);
    INSERT INTO Examine
      VALUES (112, 582, 1001, 50);
    INSERT INTO Examine
      VALUES (112, 731, 1002, 35);
    INSERT INTO Examine
      VALUES (211, 324, 1001, 25);
    INSERT INTO Examine
      VALUES (211, 582, 1002, 35);
    INSERT INTO Examine
      VALUES (211, 731, 1001, 20);
    INSERT INTO Examine
      VALUES (211, 582, 1001, 25);
    INSERT INTO Examine
      VALUES (211, 582, 1003, 65);
    
    IF OBJECT_ID('dbo.display_Avg') > 0
      DROP PROCEDURE dbo.display_Avg
    GO
    
    --Creating a stored procedure
    CREATE PROCEDURE display_Avg (@vid int)
    AS
    BEGIN
      SELECT
        v.vid,
        v.vetName,
        AVG(e.fee) AS AverageFee
      FROM Veterinarians v
      INNER JOIN Examine e
        ON v.vid = e.vid
      WHERE v.vid = @vid
      GROUP BY v.vid,
               v.vetName;
    END;
    
    --Executing stored procedure
    EXECUTE display_Avg 112;
    
    IF OBJECT_ID('dbo.display_DogNames') > 0
        DROP PROCEDURE dbo.display_DogNames
    GO
    CREATE PROCEDURE display_DogNames
    AS
    BEGIN
      SELECT
        dogName
      FROM Dogs
      INNER JOIN Examine
        ON Examine.did = Dogs.did
      INNER JOIN tblLocation
        ON Examine.lid = tblLocation.lid
      WHERE tblLocation.lid = 1001
      AND tblLocation.lid = 1002
      AND tblLocation.lid = 1003
    END;

    EXEC display_DogNames

修改如下：

1.-类型是VARCHAR而不是varchar2

2.-对于主键它的整数，不需要长的类似"int (5)“。

3.-在不接受空的主键中添加not null

4.-将位置的名称更改为tblLocation，以防" Location“是SQL中的一个保留词

5.-在创建之前销毁该物体：

    IF OBJECT_ID ('dbo.display_DogNames')> 0
      DROP PROCEDURE dbo.display_DogNames
    GO

6.-程序中的参数在param名称之前有一个@：@vid

7.-最后，为了消除一个不需要"()“符号的过程:执行display_Avg 112；

如果要删除表，请记住要记住外键：

1-下降表检查

2滴表tblLocation

3降桌兽医

四滴桌狗

Answer 2

为什么从无法运行和无法调试的脚本开始。找到一个定义良好的示例数据库，并使用它学习编写tsql查询。这种方法还将避免试图在没有关系知识基础的情况下设计数据库时出现的问题。MS已经发布了示例数据库这里。

但是这可能不会阻止你，所以解决方案是把你的脚本分解成几个批次。很简单，在每个"create“语句之前添加一个包含"GO”的行，并在创建第一个过程之后立即添加一个行，因为后面是同一个过程的execute语句。

GO  -- **HERE**

--Creating a stored procedure
Create PROCEDURE display_Avg(vid int) AS
BEGIN
select v.vid,v.vetName,Avg(e.fee) as AverageFee
from Veterinarians v
INNER JOIN Examine e
ON v.vid=e.vid
Where v.vid=display_Avg.vid
Group By v.vid,v.vetName;
END;

GO  -- **HERE**

--Executing stored procedure
Execute display_Avg(112);

GO  -- **HERE**

CREATE PROCEDURE display_DogNames 
AS BEGIN
select dogName
from Dogs
INNER JOIN Examine
ON Examine.did=Dogs.did
INNER JOIN Location
On Examine.lid=Location.lid
Where Location.lid=1001
AND Location.lid=1002
AND Location.lid=1003
END;