VB6调用mysql 存储过程_Mysql数据库，带输出值

一、Vb6 通过ADO 连接MYSQL数据库

Dim cn As New ADODB.Connection

'hostserver = "127.0.0.1"

'user = "root"

'pwd = "123456"

'dbserver = "backup"

'If cn.State = 1 Then

' cn.Close

strcn = " DRIVER={MySQL ODBC 5.2 unicode Driver};" & "SERVER=" & hostserver & "; DATABASE=" & dbserver & ";UID=" & user & ";PWD=" & pwd & "; OPTION=3"

cn.Open strcn

endif

二、Vb6 调用Mysql存储过程

sqlstr = "call Stor_InOutMat('" & selck & "'," & Val(Label20.Caption) & "," & Val(Text10) & ",'" & czymc & "','" & Trim(Text11) & "'," & czyid & ")"

cn.Execute sqlstr

三、带参数的Mysql存储过程

CREATE DEFINER=`root`@`localhost` PROCEDURE `Stor_InOutMat`(IN indeportCode varchar(30), IN inmateId int,IN Num int,IN InName varchar(20) charset utf8,IN InRemark varchar(50) charset utf8,IN InUseId int)

BEGIN

set @ServerDT=sysdate();

select count(mateid) into @aa from materialios where trim(Depotcode)=trim(indeportCode) and mateid=inmateId;

if @aa=0 then /* 无库存记录，直接加一条入仓或出仓记录 */

begin

if num>0 then insert into materialios(mateid,depotcode,IOSDateTime,AddNum,SubNum,OverNum,UserName,Remark,UserId) values(inmateId,indeportCode,@ServerDT,Num,0,Num,InName,InRemark,InUseId); /* 入仓 */

else

insert into materialios(mateid,depotcode,IOSDateTime,AddNum,SubNum,OverNum,UserName,Remark,UserId) values(inmateId,indeportCode,@ServerDT,0,0-Num,Num,InName,InRemark,InUseId); -- 出仓--

end if;

end;

else /* 以前有库存记录，查询库存量后再出入仓及更改库存量 */

begin

select max(ID) into @lasttime from materialios where trim(Depotcode)=trim(indeportCode) and mateid=inmateId;

select OverNum into @OverNum from materialios where ID=@lasttime;

/*select max(IOSDateTime) into @lasttime from materialios where trim(Depotcode)=trim(indeportCode) and mateid=inmateId; /* 最后的出入仓时间*/

/*select OverNum into @OverNum from materialios where Depotcode=indeportCode and mateid=inmateId and IOSDateTime=@lasttime; /* 现有库存量*/

if num>0 then insert into materialios(mateid,depotcode,IOSDateTime,AddNum,SubNum,OverNum,UserName,Remark,UserId) values(inmateId,indeportCode,@ServerDT,Num,0,@OverNum+Num,InName,InRemark,InUseId); /* 入仓 */

else

insert into materialios(mateid,depotcode,IOSDateTime,AddNum,SubNum,OverNum,UserName,Remark,UserId) values(inmateId,indeportCode,@ServerDT,0,0-Num,@OverNum+Num,InName,InRemark,InUseId); -- 出仓--

end if;

end;

end if;

DROP TEMPORARY TABLE IF EXISTS lstjb;

CREATE TEMPORARY TABLE lstjb SELECT mateid,depotcode,max(iosdatetime) as maxti from materialios group by mateid,depotcode;

select sum(overnum) into @LastNum from lstjb,materialios where materialios.mateid=lstjb.mateid and materialios.depotcode=lstjb.depotcode and materialios.iosdatetime=lstjb.maxti and materialios.mateid=inmateId;

update mate_inf set numbers=@LastNum where Id= inmateId;

END;

四、Mysql

游标的使用

DECLARE rs_cursor CURSOR FOR SELECT MateInfId,Numbers from View_slip_mater where trim(OddNumber)=trim(OutSlip) and SumMater=0;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

set done=0;

open rs_cursor;

cursor_loop:loop

FETCH rs_cursor into _MateInfId, _Numbers;

if done=1 then

leave cursor_loop;

else

set _Numbers=0-_Numbers;

call stor_inoutMat(indeportCode,_MateInfId,_Numbers,InName,OutSlip,InUseId);

end if;

end loop cursor_loop;

close rs_cursor;