mysql插入语句作为变量

Question

这个问题已经有答案了:

How To have Dynamic SQL in MySQL Stored Procedure (3 个回答)

已关闭 8 年前。

是否可以将变量设置为存储过程中的插入语句？

类似于:

set variable1 = insert into table(field1, field2, field3) values(val1, val2, variable2);

如果可以的话应该怎么写？

我不断抛出错误，并且文档中的内容没有结论。

我是为了简洁，但整个过程是这样的:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `new_accession`(in barcode int, in accepted  varchar(11), in wt float(11,2), in wtunit varchar(3),in draw date, in chist varchar(256), in ghist varchar(256), in meds varchar(256), in diffdiag varchar(256),in diseasesus varchar(256),in volume int, in facility int, in patient int, in employ int, in compromised int, in receiving int, in test int)
BEGIN
declare accessionId int;
declare accessionTest int;
declare tkInsert varchar(256);
declare hptInsert varchar(256);
declare calInsert varchar(256);
declare pthInsert varchar(256);
declare vitdtkInsert varchar(256);
declare cnpInsert varchar(256);

if wtunit = 'lb' then set wt = convertLbKg(wt);
end if;

INSERT INTO accession(barcode_accession,accepted_accession,weight_accession,req_weight_units,draw_date_accession,cancer_history_accession,general_history_accession,medication_accession,differential_diagnosis_accession,disease_suspect_accession,volume_accession,facility_doctor_index_id_facility_doctor_index,patient_id_patient,employee_id_employee,accession_compromised_id_accession_compromised,receiving_id,accession_typeof_id_accession_typeof)
VALUES (barcode,accepted,wt,wtunit,draw,chist,ghist,meds,diffdiag,diseasesus,volume,facility,patient,employ,compromised,receiving,1);

set accessionId = last_insert_id();

set tkInsert = insert into pending(accession_facility_index,reagent_type,`status`)values(accessionId,1,'Pending');
set hptInsert = insert into pending(accession_facility_index,reagent_type,`status`)values(accessionId,2,'Pending');
set calInsert = insert into pending(accession_facility_index,reagent_type,`status`)values(accessionId,3,'Pending');
set pthInsert = insert into pending(accession_facility_index,reagent_type,`status`)values(accessionId,4,'Pending');

if test = 1 then tkInsert,calInsert;
elseif test =2 the hptInsert,pthInsert;
else pthInsert;
end if; 


END

Answer 1

目前还不清楚您想要什么，但如果您的目标是存储 insert 的结果(受影响的行)，您可以使用 ROW_COUNT()。

ROW_COUNT() returns the number of rows changed, deleted, or inserted by the last statement if it was an UPDATE, DELETE, or INSERT.
For other statements, the value may not be meaningful.

例如:

变量1 =(选择ROW_COUNT())；