MySQL simplifies INSERT and UPDATE through stored procedures

MySQL simplifies INSERT and UPDATE through stored procedures

MySQL simplifies INSERT and UPDATE through stored procedures

Processing purpose: Execute UPDATE when the target record exists in the data table. When the target record does not exist in the data table, execute INSERT. This reduces the process of querying the database once.

The storage process is designed as follows:

Create procedure 'Pro _ savedata' (IN 'sinst' varchar (500), IN 'supdt 'varchar (500 ))
BEGIN
# Directly update records
Set @ v_updsql = supdt;
Prepare stmt from @ v_updsql;
EXECUTE stmt;

# If the record does not exist, execute INSERT
IF ROW_COUNT () = 0 THEN
Set @ v_intsql = sinst;
Prepare stmt from @ v_intsql;
EXECUTE stmt;
End if;
Deallocate prepare stmt;
END;

C # The call is as follows:

Int r = data. ExecuteNonQuery (System. Data. CommandType. StoredProcedure,
@ "CALL pro_SaveData ('insert INTO 'table' VALUES ('1', 'username ')',
'Update table SET name = 'table222 'WHERE id = '1';') ", null );

This article permanently updates the link address: