Stored procedure MySQL với SELECT trả về toàn bộ bảng

Tạo bảng mẫu:

mysql> create table DemoTable1971 ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentName varchar(20), StudentPassword int ); Query OK, 0 rows affected (0.00 sec)

Thêm dữ liệu:

mysql> insert into DemoTable1971(StudentName,StudentPassword) values('John','123456'); Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1971(StudentName,StudentPassword) values('Chris','123456'); Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1971(StudentName,StudentPassword) values('David','123456'); Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1971(StudentName,StudentPassword) values('Mike','123456'); Query OK, 1 row affected (0.00 sec)

Hiển thị dữ liệu:

mysql> select * from DemoTable1971;

+-----------+-------------+-----------------+
| StudentId | StudentName | StudentPassword |
+-----------+-------------+-----------------+
| 1         | John        | 123456          |
| 2         | Chris       | 123456          |
| 3         | David       | 123456          |
| 4         | Mike        | 123456          |
+-----------+-------------+-----------------+
4 rows in set (0.00 sec)

Tạo stored procedure nhận tham số mật khẩu và trả về các hàng khớp:

mysql> delimiter //
mysql> create procedure returnAll(pass varchar(30))
    begin
        select * from DemoTable1971 where StudentPassword=pass;
    end
//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;

Gọi procedure:

mysql> call returnAll('123456');

Kết quả trả về toàn bộ bảng:

+-----------+-------------+-----------------+
| StudentId | StudentName | StudentPassword |
+-----------+-------------+-----------------+
| 1         | John        | 123456          |
| 2         | Chris       | 123456          |
| 3         | David       | 123456          |
| 4         | Mike        | 123456          |
+-----------+-------------+-----------------+
4 rows in set (0.00 sec)