使用第二个存储过程中的值调用存储过程以产生单个结果？
我决定自学 MySQL 中的存储过程，但由于其语法与我通常使用的基础（php/javascript）有很大不同，我希望确定我对这些过程功能的理解是否正确，如果是的话，我如何实现我所追求的目标。
目前，我有两个表，“people”和“posts”，如下所示：
people
person_id INT(10)
first_name VARCHAR(32)
last_name VARCHAR(64)
posts
post_id INT(10)
author_id INT(10)
body_text TEXT
created DATETIME
由此，我创建了一个简单的通用存储过程，如下所示：
DELIMITER //
CREATE PROCEDURE getPerson(IN person INT(10))
BEGIN
SELECT CONCAT(first_name, ' ', last_name) as long_name, CONCAT(last_name, ', ', first_name) as index_name FROM people WHERE person_id = person;
END //
DELIMITER ;
到目前为止一切都很好，但现在这就是我的理解崩溃的地方..我想创建第二个名为“getPost”的过程，当传递一个 post_id 时，它将返回 posts 表中相应行的帖子数据，但也会在author_id 列上调用 getPerson 过程，以便返回单个结果将包含以下字段： post_id、long_name、index_name、body_text、已创建。
我已经尝试并犯了几个小时的错误，虽然我可能在某一时刻采用了正确的方法（对 getPerson 的结果使用联接？），但我的 sql 语法并没有超出单语句 MySQL 调用的范围来自 PHP。我的问题是：
- 这是我想做的事情的常见/合理方法还是有更好的方法？ （我不想使用脚本语言）
- 如果是这样，有人可以分享如何做到这一点吗？
- 我使用程序来处理这种事情是否正确，或者函数是否同样好/更好？
感谢您的帮助！
I have decided to educate myself on stored procedures in MySQL, but with the syntax being quite different from my usual stomping ground (php/javascript), I was hoping to determine if my understanding of the capabilities of these procedures is correct, and if so, how I achieve what I am after.
Currently, I am have two tables, 'people' and 'posts', as follows:
people
person_id INT(10)
first_name VARCHAR(32)
last_name VARCHAR(64)
posts
post_id INT(10)
author_id INT(10)
body_text TEXT
created DATETIME
From that, I created a simple all-purpose stored procedure, as follows:
DELIMITER //
CREATE PROCEDURE getPerson(IN person INT(10))
BEGIN
SELECT CONCAT(first_name, ' ', last_name) as long_name, CONCAT(last_name, ', ', first_name) as index_name FROM people WHERE person_id = person;
END //
DELIMITER ;
So far so good, but now here's where my understanding breaks down... I want to create a second procedure called 'getPost', which when passed a post_id, will return the post data from the respective row in the posts table, but also call the getPerson procedure on the author_id column, so that a single result returned will consist of the fields: post_id, long_name, index_name, body_text, created.
I've trial and errored for a couple of hours, and while I may have had the right approach at one point (use a join on the result of getPerson?), my sql syntax hasn't extended much beyond single-statment MySQL calls from PHP. My questions are:
- Is this a common/reasonable approach to what I want to do or is there something better? (I don't want to use a scripting language)
- If so, might someone share how to do this?
- Am I correct to use a procedure for this kind of thing, or is a function as good/better?
Thanks for any help!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
为什么不创建一个名为 viewPeoplePosts 的视图来组合人员及其帖子。然后您可以修改存储过程来查询视图。让视图完成所有艰苦的工作，并让存储过程简单地返回结果。
Why don't you just create a view called viewPeoplePosts that combines People and their Posts. Then you can modify your stored proc to query the view. Let the view do all the hard work and let stored procedure simply return the result.