mysql - 如何使用 MySQL Workbench 创建 MySQL 脚本？

Question

我关注了this教程，但他在视频中使用的查询似乎无法在 MySQL 中使用。

CREATE PROCEDURE spInsertIntoStudentCourses
@StudentName varchar(50), 
@CourseName varchar(50)

AS
BEGIN
  DECLARE  @StudentId int;
  DECLARE  @CourseId int;

  SELECT @StudentId = id from Students where StudentName = @StudentName;

  if(@StudentId is null);
  Begin;
    INSERT INTO Student VALUES (@StudentName);
    SELECT @StudentId = SCOPE_IDENTITY();
  END;

  SELECT @CourseId = id from Courses where CourseName = @CourseName;

  if(@CourseName is null)
  BEGIN;
    INSERT INTO Courses VALUES (@CourseName);
    SELECT @CourseId = SCOPE_IDENTITY();
  END;

  INSERT INTO StudentCourses VALUES (@StudentID, @CourseID);
END

正确的语法应该是什么？

Answer 1

经过一年的研究basics在 MySQL 中创建存储过程时，我像这样创建了上述问题的我自己的 MySQL 版本。

CREATE PROCEDURE `InsertStudentCourse`(
    _studentName VARCHAR(50),
    _courseName VARCHAR(50)
)
BEGIN
    DECLARE  _studentId INT;
    DECLARE  _courseId INT;

    SELECT _studentId = studentId FROM students WHERE studentName = _studentName;

    IF (_studentId IS NULL) THEN
        BEGIN
            INSERT INTO students VALUES (NULL, _studentName);
            SELECT _studentId = LAST_INSERT_ID();
        END;
    END IF;

    SELECT _courseId = courseId FROM courses WHERE CourseName = _courseName;

    IF (_courseId IS NULL) THEN
        BEGIN
            INSERT INTO courses VALUES (NULL, _courseName);
            SELECT _courseId = LAST_INSERT_ID();
        END;
    END IF;

    INSERT INTO student_courses VALUES (NULL, _studentName, _courseName);
END