我想返回一个在存储过程中动态创建的集合(为了简单起见,假设该集合是一个在循环中计算的偶数列表)。
我使用过的一些数据库有一个SUSPEND操作,该操作将OUT参数的当前值添加到过程的结果集,如下所示:
DELIMITER $
CREATE PROCEDURE EvenNumbers(
IN n INT, OUT NUM INT )
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET NUM = i;
SUSPEND; /* adds a row with a value 'i' for column NUM to the result set */
SET i = i+2;
END WHILE;
END$
DELIMITER ;SUSPEND是MySQL中的一个关键字,但我假设它不会做同样的事情,因为这不起作用(Error: Unexpected 'SUSPEND')。
那么,如何在MySQL中逐个返回存储过程中的一组行呢?
发布于 2017-10-12 03:54:53
Alex,您可以在存储过程中创建一个临时表(这是完全可以的),将计算结果存储在其中,并在完成计算后返回临时表的内容。因此,不使用OUT参数,而是使用该过程返回的普通结果集。
发布于 2017-10-12 03:30:26
我知道你想要一个偶数发生器?这是纯MySQL,没有使用存储过程。
首先,您需要使用下面的查询生成大量的off数字。此查询将生成1到10000条记录。
查询
SELECT
@row := @row + 1 AS ROW
FROM
(
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) row1
CROSS JOIN (
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) row2
CROSS JOIN (
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) row3
CROSS JOIN (
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) row4
CROSS JOIN (
SELECT @row := 0
) init_user_param主查询。
查询
SELECT
*
FROM (
SELECT
@row := @row + 1 AS ROW
FROM
(
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) row1
CROSS JOIN (
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) row2
CROSS JOIN (
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) row3
CROSS JOIN (
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) row4
CROSS JOIN (
SELECT @row := 0
) init_user_param
)
ROWS
WHERE
ROWS.ROW BETWEEN 1 AND 10
AND
ROWS.ROW MOD 2 = 0结果
row
--------
2
4
6
8
10发布于 2019-05-21 06:26:29
我只是生成JSON并将其作为单个结果返回
Bellow是一个示例查询,虽然很大,但它从关节中清晰可见的不同表中提取内容,但仍然返回一个JSON对象
select
JSON_OBJECT(
'application_id', yvs_job_search_application.application_id,
'progress_id', yvs_job_search_application.progress_id,
'progress_name', yvs_job_search_application_progress.progress_name,
'type_id', yvs_job_search_application.type_id,
'type_name', yvs_job_search_application_type.type_name,
'wage', yvs_job_search_application.wage,
'wage_id', yvs_job_search_application.wage_id,
'wage_name', yvs_job_search_wage_type.wage_name,
'position_name', yvs_job_search_application.position_name,
'agency_refference', yvs_job_search_application.agency_refference,
'position_location', yvs_job_search_application.position_location,
'job_description', yvs_job_search_application.job_description,
'agency_id', yvs_job_search_application.agency_id,
'agency_name', yvs_job_search_agency.agency_name ,
'default_number', yvs_job_search_agency.default_number,
'agency_site', yvs_job_search_agency.agency_site ,
'agent_id', yvs_job_search_application.agent_id,
'agent_name', yvs_job_search_agent.agent_name ,
'agent_email', yvs_job_search_agent.agent_email,
'agent_phone', yvs_job_search_agent.agent_phone ,
'agent_mobile', yvs_job_search_agent.agent_mobile )
from yvs_job_search_application
join yvs_job_search_application_progress on yvs_job_search_application.progress_id = yvs_job_search_application_progress.progress_id
join yvs_job_search_application_type onyvs_job_search_application.type_id = yvs_job_search_application_type.type_id
join yvs_job_search_wage_type on yvs_job_search_application.wage_id = yvs_job_search_wage_type.wage_id
join yvs_job_search_agency on yvs_job_search_application.agency_id = yvs_job_search_agency.agency_id
join yvs_job_search_agent on yvs_job_search_application.agent_id = yvs_job_search_agent.agent_id
;您可以在JSON Functions in MySQL这里阅读mysql中的JSON函数。
https://stackoverflow.com/questions/46695718
复制相似问题