DROP PROCEDURE IF EXISTS TABLE_INSERT; DELIMITER | CREATE PROCEDURE TABLE_INSERT (in username VARCHAR(16),in operatetime VARCHAR(16),in operatetype VARCHAR(16)) begin declare IDD int; declare sqlstr varchar(100); select IDD = count(*) FROM tab_userlog; set IDD = IDD+1; if IDD<=8 then prepare sqlstr from "INSERT INTO tab_userlog SELECT ?,?,?,?"; EXECUTE sqlstr USING @IDD,@username,@operatetime,@operatetype; ELSE set IDD = IDD-8; prepare sqlstr from "UPDATE tab_userlog SET ID=?,username=?,operatetime=?,operatetype=? WHERE ID=?"; EXECUTE sqlstr USING @IDD,@username,@operatetime,@operatetype; end if; end; | DELIMITER ; 这是我设计的存储空间,能执行成功,但是在调用的时候出错 SET @username = 'DWDD'; SET @operatetime = 'DWDD'; SET @operatetype = 'operatetype'; CALL TABLE_INSERT (@username,@operatetime,@operatetype);14:37:57 CALL TABLE_INSERT (@username,@operatetime,@operatetype) Error Code: 1210 Incorrect arguments to EXECUTE
EXECUTE sqlstr USING @IDD,@username,@operatetime,@operatetype;改成EXECUTE sqlstr USING IDD,username,operatetime,operatetype;
说错了 要set @idd = IDD 每个变量都需要这么做一下
我改成了DROP PROCEDURE IF EXISTS TABLE_INSERT; DELIMITER | CREATE PROCEDURE TABLE_INSERT (in username VARCHAR(16),in operatetime VARCHAR(16),in operatetype VARCHAR(16)) begin declare IDD int; declare sqlstr varchar(100); select count(*) into IDD FROM tab_userlog; select IDD; set IDD = IDD+1; select IDD; if IDD<=8 then prepare sqlstr from "INSERT INTO tab_userlog SELECT ?,?,?,?"; EXECUTE sqlstr USING @IDD,@username,@operatetime,@operatetype; ELSE set IDD = IDD-8; select IDD;prepare sqlstr from "UPDATE tab_userlog SET ID=?,username=?,operatetime=?,operatetype=? WHERE ID=?"; select sqlstr; EXECUTE sqlstr USING @IDD,@username,@operatetime,@operatetype,@IDD; end if; end; | DELIMITER ; 现在if条件满足的情况下没问题,但是ELSE后面分支执行后数据表里面始终没效果,也没报错
DROP PROCEDURE IF EXISTS TABLE_INSERT;
DELIMITER |
CREATE PROCEDURE TABLE_INSERT (in username VARCHAR(16),in operatetime VARCHAR(16),in operatetype VARCHAR(16))
begin
declare IDD int;
declare sqlstr varchar(100);
select IDD = count(*) FROM tab_userlog;
set IDD = IDD+1;
if IDD<=8 then
prepare sqlstr from "INSERT INTO tab_userlog SELECT ?,?,?,?";
EXECUTE sqlstr USING @IDD,@username,@operatetime,@operatetype;
ELSE
set IDD = IDD-8;
prepare sqlstr from "UPDATE tab_userlog SET ID=?,username=?,operatetime=?,operatetype=? WHERE ID=?";
EXECUTE sqlstr USING @IDD,@username,@operatetime,@operatetype;
end if;
end;
|
DELIMITER ; 这是我设计的存储空间,能执行成功,但是在调用的时候出错
SET @username = 'DWDD';
SET @operatetime = 'DWDD';
SET @operatetype = 'operatetype';
CALL TABLE_INSERT (@username,@operatetime,@operatetype);14:37:57 CALL TABLE_INSERT (@username,@operatetime,@operatetype) Error Code: 1210 Incorrect arguments to EXECUTE
每个变量都需要这么做一下
DELIMITER |
CREATE PROCEDURE TABLE_INSERT (in username VARCHAR(16),in operatetime VARCHAR(16),in operatetype VARCHAR(16))
begin
declare IDD int;
declare sqlstr varchar(100);
select count(*) into IDD FROM tab_userlog;
select IDD;
set IDD = IDD+1;
select IDD;
if IDD<=8 then
prepare sqlstr from "INSERT INTO tab_userlog SELECT ?,?,?,?";
EXECUTE sqlstr USING @IDD,@username,@operatetime,@operatetype;
ELSE
set IDD = IDD-8;
select IDD;prepare sqlstr from "UPDATE tab_userlog SET ID=?,username=?,operatetime=?,operatetype=? WHERE ID=?";
select sqlstr;
EXECUTE sqlstr USING @IDD,@username,@operatetime,@operatetype,@IDD;
end if;
end;
|
DELIMITER ; 现在if条件满足的情况下没问题,但是ELSE后面分支执行后数据表里面始终没效果,也没报错