存储过程不工作某些语法错误

Question

我正在学习有关存储过程的知识，并编写了以下内容：

delimiter //

create procedure show_inv(in inv_num int)
    beign
    select * from items
    where items.invoice_num = inv_num;
    end
    //
    delimiter ;

这是一个简单的程序，其中插入了发票编号以进行查找。

我收到以下错误：

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select * from items
where items.invoice_num = inv_num;
end' at line 3

查询本身运行良好，但存储过程似乎有一个错误，我不知道它在哪里。

Answer 1

在结束后，您将缺少;和begin的拼写错误，因此该过程将如下所示

delimiter //

create procedure show_inv(in inv_num int)
begin
select * from items
where items.invoice_num = inv_num;
end ; //
delimiter ;

这是一个测试用例，select来自不同的表，但rest all是相同的

mysql> select * from student ;
+---------------+----------+
| studentnumber | name     |
+---------------+----------+
|            11 | AAAAAAAA |
|            17 | BBBBBBB  |
|             8 | CCCCCCC  |
+---------------+----------+
3 rows in set (0.00 sec)

mysql> delimiter //
mysql> create procedure show_inv(in inv_num int)
    -> begin
    -> select * from student where studentnumber = inv_num ;
    -> end ; //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call show_inv(11);
+---------------+----------+
| studentnumber | name     |
+---------------+----------+
|            11 | AAAAAAAA |
+---------------+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)