DROP SCHEMA IF EXISTS HW_ProcedureExamples;

CREATE SCHEMA HW_ProcedureExamples;

USE HW_ProcedureExamples;

-- start snippet procedure-1
/* code/sql/HW_ProcedureExamples.sql */
CREATE TABLE STUDENT (
  Login INT PRIMARY KEY,
  NAME VARCHAR(30),
  Major VARCHAR(30),
  Email VARCHAR(30)
);

INSERT INTO STUDENT
VALUES (
  123,
  "Test A",
  "CS",
  "a@a.edu"),
(
  124,
  "Test B",
  "IT",
  "b@a.edu"),
(
  125,
  "Test C",
  "CYBR",
  "c@a.edu");

-- end snippet procedure-1
-- start snippet procedure-2
DELIMITER $$
CREATE PROCEDURE STUDENTLIST ()
BEGIN
  SELECT *
  FROM STUDENT;

-- This ";" is not the end of the procedure definition!
END;
$$
-- This is the delimiter that marks the end of the procedure
--		   definition.
DELIMITER ;

-- Now, we want ";" to be the "natural" delimiter again.
CALL STUDENTLIST ();

-- Now, we want ";" to be the "natural" delimiter again.
CALL STUDENTLIST ();

-- end snippet procedure-2
-- start snippet procedure-3
DELIMITER $$
CREATE PROCEDURE STUDENTLOGIN (
  NameP VARCHAR(30)
)
BEGIN
  SELECT LOGIN
  FROM STUDENT
  WHERE NameP = Name;

END;
$$
DELIMITER ;

SHOW CREATE PROCEDURE STUDENTLOGIN;

-- This display information about the procedure just created.
--		    We can pass quite naturally an argument
--   to
--       our
--		  procedure.
CALL STUDENTLOGIN ("Test A");

-- end snippet procedure-3