Home >Database >Mysql Tutorial >mysql通过存储过程简化INSERT和UPDATE_MySQL

mysql通过存储过程简化INSERT和UPDATE_MySQL

WBOY
WBOYOriginal
2016-06-01 13:00:531055browse

处理目的,当数据表中存在目标记录时,执行UPDATE;当数据表中不存在目标记录时,执行INSERT;从而减少一次查询数据库的过程

存储过程设计如下:

CREATE PROCEDURE `pro_SaveData`(IN `sinst` varchar(500),IN `supdt` varchar(500))
BEGIN
	#直接更新记录
     set @v_updsql=supdt;   
     prepare stmt from @v_updsql;  
     EXECUTE stmt;      
     
	#记录不存在,执行INSERT
	IF ROW_COUNT() =0 THEN
			set @v_intsql=sinst;
     prepare stmt from @v_intsql; 
     EXECUTE stmt;      
	END IF;
deallocate prepare stmt;    
END;


C#调用如下:

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);



Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn