昨天请高人帮忙改了个存储过程,也觉得代码已经没问题了,但跑起来总是提示这个错误,头都大了。存储过程代码如下:-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$CREATE DEFINER=`root`@`%` PROCEDURE `USP_CreateTrackerLogTable`(IN Pointer DATETIME,IN Counts INT)
begin
declare CountPointer int;
declare DateString varchar(20);
declare TableName varchar(50);
declare SQLTxt varchar(5000);
declare ExecuteSQL varchar(8000);
Set CountPointer = Counts;
Set SQLTxt = '
CREATE TABLE IF NOT EXISTS Tracker_Log(
LogID bigint PRIMARY KEY auto_increment NOT NULL, /*自增ID*/
TrackerID varchar(50) NOT NULL, /*日志ID*/
LogTime datetime NOT NULL, /*日志时间*/
SessionId varchar(50) NOT NULL, /*会话ID*/
PassportID bigint NULL, /*账户ID*/
UserID bigint NULL, /*用户ID*/
ClientIP varchar(64) NULL, /*客户端IP*/
ServerIP varchar(64) NULL, /*应用服务器IP*/
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 = 'Tracker_Log_' + DateString;
/*替换脚本字符串*/
Select ExecuteSQL = Replace(SQLTxt, 'Tracker_Log', TableName); /*执行*/
Select SQLTxt;
/*SET @asql=ExecuteSQL;
PREPARE tt FROM @asql;
EXECUTE tt;*/
/*print ExecuteSQL*/
/*准备下一次*/
Set CountPointer = CountPointer - 1;
SET Pointer = DATE_ADD(CURDATE(), INTERVAL CountPointer DAY );
END;
END WHILE;
END
另外,我可能要在SQLTxt中放入创建多个表的代码,用于定时任务,不知道是否也行?
因为现在一个表我都跑不过去,多个表一起创建就没有测试,特请教大家。
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$CREATE DEFINER=`root`@`%` PROCEDURE `USP_CreateTrackerLogTable`(IN Pointer DATETIME,IN Counts INT)
begin
declare CountPointer int;
declare DateString varchar(20);
declare TableName varchar(50);
declare SQLTxt varchar(5000);
declare ExecuteSQL varchar(8000);
Set CountPointer = Counts;
Set SQLTxt = '
CREATE TABLE IF NOT EXISTS Tracker_Log(
LogID bigint PRIMARY KEY auto_increment NOT NULL, /*自增ID*/
TrackerID varchar(50) NOT NULL, /*日志ID*/
LogTime datetime NOT NULL, /*日志时间*/
SessionId varchar(50) NOT NULL, /*会话ID*/
PassportID bigint NULL, /*账户ID*/
UserID bigint NULL, /*用户ID*/
ClientIP varchar(64) NULL, /*客户端IP*/
ServerIP varchar(64) NULL, /*应用服务器IP*/
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 = 'Tracker_Log_' + DateString;
/*替换脚本字符串*/
Select ExecuteSQL = Replace(SQLTxt, 'Tracker_Log', TableName); /*执行*/
Select SQLTxt;
/*SET @asql=ExecuteSQL;
PREPARE tt FROM @asql;
EXECUTE tt;*/
/*print ExecuteSQL*/
/*准备下一次*/
Set CountPointer = CountPointer - 1;
SET Pointer = DATE_ADD(CURDATE(), INTERVAL CountPointer DAY );
END;
END WHILE;
END
另外,我可能要在SQLTxt中放入创建多个表的代码,用于定时任务,不知道是否也行?
因为现在一个表我都跑不过去,多个表一起创建就没有测试,特请教大家。
call USP_CreateTrackerLogTable('2010-04-21',20);总提示Commands out of sync; you can't run this command now我在想如果该存储过程没错的话我需要执行一个自动建表任务还有一疑问不知道存储过程一次能否能执行多个建表指令如:
Create table t1(...);Create table t2(...);...还没测试
call USP_CreateTrackerLogTable('2010-04-21',20);
有无问题,
你是在语言中调用此SP?
在建立链接的时候要加选项CLIENT_MULTI_STATEMENTS 或 CLIENT_MULTI_RESULTS,以便可以让query执行多个语句。mysql_real_connect(mySQL,serverIP,user,password,database,serverPort,NULL,CLIENT_MULTI_STATEMENTS)
call USP_CreateTrackerLogTable('2010-04-21',20);跑不了。。
BEGIN
DECLARE CountPointer INT;
DECLARE DateString VARCHAR(20);
DECLARE TableName VARCHAR(50);
DECLARE `SQLTxt` VARCHAR(2000);
DECLARE ExecuteSQL VARCHAR(8000);
SET CountPointer = Counts;
SET `SQLTxt` = ' CREATE TABLE IF NOT EXISTS 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);';
SELECT `SQLTxt`;
WHILE (CountPointer > 0) DO
BEGIN
/*处理日期*/
SET DateString = CONCAT(RIGHT(CAST(YEAR(Pointer) AS CHAR(4)), 2),'_',
RIGHT(CONCAT('0',CAST(MONTH(Pointer) AS CHAR(2))), 2),'_',
RIGHT(CONCAT('0',CAST(DAY(Pointer) AS CHAR(2))), 2));
SELECT DateString;
SET TableName = CONCAT('Tracker_Log_' ,DateString);
SELECT TableName;
/*替换脚本字符串*/
SET ExecuteSQL = REPLACE(`SQLTxt`, 'Tracker_Log', TableName);
SELECT ExecuteSQL;
/*执行*/
SELECT `SQLTxt`;
/*SET @asql=ExecuteSQL;
PREPARE tt FROM @asql;
EXECUTE tt;*/
/*print ExecuteSQL*/
/*准备下一次*/
SET CountPointer = CountPointer - 1;
SET Pointer = DATE_ADD(CURDATE(), INTERVAL CountPointer DAY );
END;
END WHILE;
END$$DELIMITER ;
Set TableName = CONCAT('Tracker_Log_' , DateString);2、赋值语句不能使用SELECT:
SET ExecuteSQL = Replace(SQLTxt, 'Tracker_Log', TableName);但新问题又出现了,我的SQLTxt中有多个表以及索引要创建,现在只创建了一个表,请问该如何处理?
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$CREATE DEFINER=`root`@`%` PROCEDURE `USP_CreateTrackerLogTable`(IN Pointer DATETIME,IN Counts INT)
begin
declare CountPointer int;
declare DateString varchar(20);
declare TableName1 varchar(50);
declare TableName2 varchar(50);
declare SQLTxt varchar(5000);
declare ExecuteSQL varchar(8000);
Set CountPointer = Counts;
Set SQLTxt = ' CREATE TABLE IF NOT EXISTS Cloud_Login_Info(
LogID bigint auto_increment PRIMARY KEY NOT NULL,
UniqueID varchar(50) NOT NULL,
UserName varchar(50) NOT NULL,
UserID bigint NOT NULL,
PassportID bigint NOT NULL,
PreUserID bigint NOT NULL,
PrePassportID bigint NOT NULL,
SessionID varchar(50) NULL,
ProtocolServerIP varchar(64) NULL,
AppServerIP varchar(64) NULL,
ClientIP varchar(64) NULL,
UserExplorer varchar(50) NULL,
UserOpSystem varchar(50) NULL,
UserLanguage varchar(50) NULL,
UserResolution varchar(50) NULL,
LogTime datetime NULL
); Create Index IF NOT EXISTS IX_Cloud_Login_Info_UserIDPassportID On Cloud_Login_Info(UserID,PassportID); CREATE TABLE IF NOT EXISTS Tracker_Log(
LogID bigint PRIMARY KEY auto_increment NOT NULL, /*自增ID*/
TrackerID varchar(50) NOT NULL, /*日志ID*/
LogTime datetime NOT NULL, /*日志时间*/
SessionId varchar(50) NOT NULL, /*会话ID*/
PassportID bigint NULL, /*账户ID*/
UserID bigint NULL, /*用户ID*/
ClientIP varchar(64) NULL, /*客户端IP*/
ServerIP varchar(64) NULL, /*应用服务器IP*/
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),
'_',
LPAD(CAST(MONTH(Pointer) AS CHAR(2)),2,'0'),
'_',
LPAD(CAST(DAY(Pointer) AS CHAR(2)),2,'0')
);
Set TableName1 = CONCAT('Cloud_Action_Log_' , DateString);
Set TableName2 = CONCAT('Tracker_Log_' , DateString);
/*替换脚本字符串*/
SET ExecuteSQL = Replace(SQLTxt, 'Cloud_Action_Log', TableName1);
SET ExecuteSQL = Replace(ExecuteSQL, 'Tracker_Log', TableName2);
/*执行*/
SET @asql=ExecuteSQL;
PREPARE tt FROM @asql;
EXECUTE tt;
/*print ExecuteSQL*/ /*准备下一次*/
Set CountPointer = CountPointer - 1;
SET Pointer = DATE_ADD(Pointer, INTERVAL 1 DAY );
END;
END WHILE;
END
Set TableName = CONCAT('Tracker_Log_' , DateString);
MYSQL中的字符串合并是用 CONCAT() 函数实现,当然也可以设置为 || 与ORACLE兼容。2、赋值语句不能使用SELECT:
SET ExecuteSQL = Replace(SQLTxt, 'Tracker_Log', TableName);需要改一下
select @var : = 123; 这种方式或者 set @var =123, select 123 into @var ;
示例:
FF=CONCAT('CREATE TABLE IF NOT EXISTS ',变量名,...)
即可建立相同结构不同表名的表
谢谢帮忙,我把语句放在TRANSACTION中多次执行已经调试通过了,一会我总结下散分