MySQL will not create a table or fill it with values.

Question

In the following code I declare a cursor and then shortly there after create a table. It then loops through and adds values to the table:

DELIMITER //
DROP PROCEDURE IF EXISTS studentinfo//
CREATE PROCEDURE studentinfo()
BEGIN
 -- Declare local variables
 DECLARE done BOOLEAN DEFAULT 0;
 DECLARE studentNum INT(2);
 DECLARE gradepoint DECIMAL(3,2);
 DECLARE lastName VarChar(15);
 DECLARE classYear INT(1);
 -- Declare the cursor
 DECLARE studentNumber CURSOR
 FOR
 SELECT Student_number FROM student;
 -- Declare continue handler
 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
 -- Create a table to store the results
 **CREATE TABLE IF NOT EXISTS studentdata(gpa DECIMAL(3,2), LName VarChar(15),class INT(1));**
 -- Open the cursor
 OPEN studentNumber;
 -- Loop through all rows
 REPEAT
 -- Get student number
 FETCH studentNumber INTO studentNum;
 -- Get gpa
 Select student_gpa(studentNum) INTO gradepoint;
 -- Get name
 Select LName from student WHERE Student_number = studentNum INTO lastName;
 -- get grade level
 Select Class from student WHERE Student_number = studentNum INTO classYear;
 -- Insert info into table
 **INSERT INTO studentdata(gpa,Lname,class)
 VALUES(gradepoint, lastName, classYear);**
 -- End of loop 
 UNTIL done END REPEAT;
 -- Close the cursor
 CLOSE studentNumber;
 END//
 DELIMITER ; 

When I then go run Select * from studentdata, it gives me a 'table does not exist' error. I can manually create the table by just running the one line of code that creates the table, but it will not create it if I run the whole code block. And even if I create it manually it still doesn't populate it with any values. What am I doing wrong here? Thanks.

Answer 1

So far you have defined only a stored procedure. In order create that table in sp, you have to call studentinfo sp.