Create PROCEDURE USP_CreateLogTables(in Pointer datetime,in Counts int)
begin
declare CountPointer int
declare DateString varchar(20)
declare TableName varchar(50)
declare ExecuteSQL varchar(50)
declare SQLTxt varchar(max)
Set CountPointer = Counts
Set SQLTxt=' CREATE TABLE dbo.Tracker_Log(
LogID bigint PRIMARY KEY auto_increment NOT NULL,
TrackerID varchar(50) NOT NULL,
LogTime datetime NOT NULL,
SessionId varchar(50) NOT NULL,
PassportID bigint NULL,
UserID bigint NULL,
ClientIP varchar(64) NULL,
ServerIP varchar(64) NULL,
CurrentUrl varchar(255) NULL,
ReferrerUrl varchar(255) NULL,
ClickLinkUrl varchar(255) NULL,
ClickLinkTitle nvarchar(200) NULL,
CurrentClientTime datetime NULL,
CurrentServerTime datetime NULL,
ClientServerTimeDif bigint Null,
ServerExecuteTime bigint NULL,
NetTransTime bigint NULL,
ServerResponseTime datetime NULL,
ClientLoadTime bigint NULL,
UserDwellTime bigint NULL
)' While(CountPointer > 0)
Begin
Set DateString = Right(Cast(DateName(yyyy, Pointer) as varchar(4)), 2) + '_' +
Right('0' + Cast(Month(Pointer) as varchar(2)), 2) + '_' +
Right('0' + Cast(DatePart(day, Pointer) as varchar(2)), 2) Set TableName = 'Tracker_Log_' + DateString
Select ExecuteSQL = Replace(SQLTxt, 'Tracker_Log', TableName) execute(ExecuteSQL) Set CountPointer = CountPointer - 1
Set Pointer = DateAdd(dd, CountPointer, getDate())
EndEND
无论我怎么改,IDE都提示存在错误
我的MYSQL版本为5.0.67
之前是MSSQL的存储过程,今天刚接触MYSQL,死活调不通,还请大家指点。
execute(ExecuteSQL)->
set @asql=ExecuteSQL;
prepre tt from @asql;
execute tt;
Select ExecuteSQL
看看语句是否正确
......
END WHILE;
BEGIN
DECLARE CountPointer INT;
DECLARE DateString VARCHAR(20);
DECLARE TableName VARCHAR(50);
DECLARE ExecuteSQL VARCHAR(50);
DECLARE SQLTxt VARCHAR(5000);
SET CountPointer = Counts;
SET SQLTxt=' CREATE TABLE dbo.Tracker_Log(
LogID bigint PRIMARY KEY auto_increment NOT NULL,
TrackerID varchar(50) NOT NULL,
LogTime datetime NOT NULL,
SessionId varchar(50) NOT NULL,
PassportID bigint NULL,
UserID bigint NULL,
ClientIP varchar(64) NULL,
ServerIP varchar(64) NULL,
CurrentUrl varchar(255) NULL,
ReferrerUrl varchar(255) NULL,
ClickLinkUrl varchar(255) NULL,
ClickLinkTitle nvarchar(200) NULL,
CurrentClientTime datetime NULL,
CurrentServerTime datetime NULL,
ClientServerTimeDif bigint Null,
ServerExecuteTime bigint NULL,
NetTransTime bigint NULL,
ServerResponseTime datetime NULL,
ClientLoadTime bigint NULL,
UserDwellTime bigint NULL
)'; WHILE (CountPointer > 0) DO
BEGIN
SET DateString = CONCAT(RIGHT(CAST(YEAR(Pointer) AS CHAR(4)), 2),'_',
RIGHT('0' + CAST(MONTH(Pointer) AS CHAR(2)), 2),'_',
RIGHT('0' + CAST(DAY(Pointer) AS CHAR(2)), 2)); SET TableName = CONCAT('Tracker_Log_' ,DateString);
SELECT ExecuteSQL = REPLACE(SQLTxt, 'Tracker_Log', TableName); SET @asql=ExecuteSQL;
PREPARE tt FROM @asql;
EXECUTE tt; SET CountPointer=CountPointer - 1;
SET Pointer = DATE_ADD(CURDATE(), INTERVAL CountPointer DAY );
END;
END WHILE;END
$$DELIMITER ;