Mysql的存储过程:代码如下:
CREATE DEFINER=`root`@`localhost` PROCEDURE `Sp_SetMatable`(
IN pID TINYINT ,
IN pname VARCHAR(64),
IN pheigt TINYINT ,
IN pPhone VARCHAR(64),
IN pcrea_time VARCHAR(64),
IN pmov_time VARCHAR(64),
IN pOpType TINYINT, -- 操作类型【1:新增、更新;2:删除】
OUT retError INT -- 错误代码【-1:异常;0:正常;】
)
BEGIN
-- 步骤跟踪变量的定义
DECLARE _Step TINYINT DEFAULT 0;
-- 异常声明
DECLARE EXIT HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND SET retError = -1;
-- 业务操作类型的选择
IF pOpType = 1 THEN
-- 步骤跟踪
SET _Step = 1;
-- 单位信息不存在,则新增,否则更新
IF NOT EXISTS (SELECT 1 FROM matable1 WHERE ID = pID LIMIT 1) THEN
-- 生成单位信息
INSERT INTO matable1
(
name,
heigt,
Phone,
crea_time,
mov_time
)
VALUES
(
pname,
pheigt,
pPhone,
pcrea_time,
pmov_time
);
ELSE
-- 更新单位信息
UPDATE matable1 tea
SET
tea.name = pname, -- 行业代码
tea.heigt = pheigt, -- 单位名称
tea.Phone = pPhone, -- 单位昵称
tea.crea_time =pcrea_time,
tea.mov_time = pmov_time -- 单位签名
-- 更新日期
WHERE tea.ID = pID;
END IF;
ELSEIF pOpType = 2 THEN
-- 步骤跟踪
SET _Step = 2;
-- 删除信息
DELETE
FROM matable1
WHERE ID = pID;
END IF;
-- 无异常出现,提交数据
SET retError = 0;
COMMIT;
END;
///
C++BuilderX8e Mysql存过程储过程的增、删、改.代码如下:
void __fastcall TForm1::Button3Click(TObject *Sender)
{
ADOStoredProc1->Close();
ADOStoredProc1->ProcedureName ="Sp_SetMatable";
ADOStoredProc1->Parameters->CreateParameter("pOpType", ftString,pdInput, 10, '1');
ADOStoredProc1->Parameters->CreateParameter("pname",ftString,pdInput , 64, "ZHANGJIANHUA");
ADOStoredProc1->Parameters->CreateParameter("pheigt", ftString, pdInput , 100, '90');
ADOStoredProc1->Parameters->CreateParameter("pOpType", ftInteger,pdInput, 10, 1);
ADOStoredProc1->Parameters->CreateParameter("@retError",ftInteger,pdOutput,10,1);
ADOStoredProc1->Refresh();
ADOStoredProc1->ExecProc();
void __fastcall TForm1::Button4Click(TObject *Sender)
{
ADOStoredProc1->Close();
ADOStoredProc1->ProcedureName ="Sp_SetMatable";
ADOStoredProc1->Parameters->CreateParameter("pOpType", ftString,pdInput, 10, '2');
ADOStoredProc1->Parameters->CreateParameter("pID", ftString,pdInput, 10, '1008');
ADOStoredProc1->Parameters->CreateParameter("pOpType", ftInteger,pdInput, 10, 1);
ADOStoredProc1->Parameters->CreateParameter("@retError",ftInteger,pdOutput,10,1);
ADOStoredProc1->Refresh();
ADOStoredProc1->ExecProc();
}
void __fastcall TForm1::Button1Click(TObject *Sender)
{
//对应存储过程的参数,
ADOStoredProc1->Close();
ADOStoredProc1->ProcedureName ="Sp_SetMatable";
AnsiString st; //入删
ADOStoredProc1->Parameters->CreateParameter("pID", ftInteger,pdInput, 10, 0);
ADOStoredProc1->Parameters->CreateParameter("pname",ftString,pdInput , 64, "ZHANGJIANHUA");
ADOStoredProc1->Parameters->CreateParameter("pheigt", ftInteger, pdInput , 100, 90);
ADOStoredProc1->Parameters->CreateParameter("pPhone", ftString, pdInput ,100, '1232');
ADOStoredProc1->Parameters->CreateParameter("pcrea_time",ftString, pdInput , 64, "2016-10--23");
ADOStoredProc1->Parameters->CreateParameter("pmov_time", ftString, pdInput , 64, "2016-10--27");
ADOStoredProc1->Parameters->CreateParameter("pOpType", ftInteger,pdInput, 10, 1);
// 出删
ADOStoredProc1->Parameters->CreateParameter("@retError",ftInteger,pdOutput,10,1);
ADOStoredProc1->ExecProc();
}