有这样一个函数CREATE FUNCTION `insertTtree`(tableName VARCHAR(32),parentCode VARCHAR(64),`manualCode` VARCHAR(64),`code` VARCHAR(64),`name` VARCHAR(64),`mapCode` VARCHAR(64),nextNodeID int) RETURNS varchar(255)
begin
DECLARE returnVarchar VARCHAR(255) default '';
DECLARE thisCode int default 0;
DECLARE thisPosition int default 0;
select getNextSEQ(tableName,tableName+'ID') into thisCode;
if(parentCode<=>null) then
set returnVarchar=thisCode;
else
set returnVarchar=concat(parentCode,':',thisCode);
end if;
if(nextNodeID>0) then
select `position` into thisPosition from `teleprovider` where teleProviderID = nextNodeID;
UPDATE teleprovider set `position`=`position`+1 where getParent(autoCode)=parentCode and `position`>thisPosition order by `position`;
end if;
insert into Teleprovider(teleProviderID,autoCode,`position`,manualCode,code,name,mapCode) VALUES(thisCode,returnVarchar,thisPosition,manualCode,code,name,mapCode);
return returnVarchar;
end;
Teleprovider为表名
getNextSEQ(tableName,tableName+'ID')返回一个唯一id需要修改成下面的存储过程create procedure insertTtree(tableName VARCHAR(32),parentCode VARCHAR(64),`code` VARCHAR(64),`manualCode` VARCHAR(64),`name` VARCHAR(64),`mapCode` VARCHAR(64),nextNodeID int,filedStr varchar(255),valueStr varchar(500))
beginend
要求实现表名的完全动态
详细加qq30130942讨论
谢谢,期待高手
begin
DECLARE returnVarchar VARCHAR(255) default '';
DECLARE thisCode int default 0;
DECLARE thisPosition int default 0;
select getNextSEQ(tableName,tableName+'ID') into thisCode;
if(parentCode<=>null) then
set returnVarchar=thisCode;
else
set returnVarchar=concat(parentCode,':',thisCode);
end if;
if(nextNodeID>0) then
select `position` into thisPosition from `teleprovider` where teleProviderID = nextNodeID;
UPDATE teleprovider set `position`=`position`+1 where getParent(autoCode)=parentCode and `position`>thisPosition order by `position`;
end if;
insert into Teleprovider(teleProviderID,autoCode,`position`,manualCode,code,name,mapCode) VALUES(thisCode,returnVarchar,thisPosition,manualCode,code,name,mapCode);
return returnVarchar;
end;
Teleprovider为表名
getNextSEQ(tableName,tableName+'ID')返回一个唯一id需要修改成下面的存储过程create procedure insertTtree(tableName VARCHAR(32),parentCode VARCHAR(64),`code` VARCHAR(64),`manualCode` VARCHAR(64),`name` VARCHAR(64),`mapCode` VARCHAR(64),nextNodeID int,filedStr varchar(255),valueStr varchar(500))
beginend
要求实现表名的完全动态
详细加qq30130942讨论
谢谢,期待高手
create procedure insertTtree(tableName VARCHAR(32),parentCode VARCHAR(64),`code` VARCHAR(64),`manualCode` VARCHAR(64),`name` VARCHAR(64),`mapCode` VARCHAR(64),nextNodeID int,filedStr varchar(255),valueStr varchar(500),out returnVarchar VARCHAR(255))
begin
DECLARE thisCode int default 0;
DECLARE thisPosition int default 0;
SET @stableName=tableName;
set @sparentCode=parentCode;
set @scode=code;
set @smanualCode=manualCode;
set @sname=name;
set @smapCode=mapCode;
set @sfiledStr=filedStr;
set @svalueStr=valueStr;
select getNextSEQ(tableName,tableName+'ID') into thisCode;
if(isnull(parentCode) or length(parentCode)=0) then
set returnVarchar=thisCode;
set parentCode='';
else
set returnVarchar=concat(parentCode,':',thisCode);
end if;
if(nextNodeID>0) then
select `position` into thisPosition from `teleprovider` where teleProviderID = nextNodeID;
set @sqlstr=concat('UPDATE ',tableName,' set `position`=`position`+1 where getParent(autoCode)=',parentCode,' and `position`>',thisPosition,' or `position`=',thisPosition,' order by `position`');
prepare stmt from @sqlstr;
EXECUTE stmt;
else
select max(`position`)+1 into thisPosition from `teleprovider` where getParent(autoCode)=parentCode;
end if;
set @sthisCode=thisCode;
set @sreturnVarchar=returnVarchar;
set @sqlstr=concat('insert into ',tableName,'(',tableName,'ID,autoCode,`position`,manualCode,code,name,mapCode?) VALUES(?,?,?,?,?,?,?,?)');
prepare stmt from @sqlstr;
EXECUTE stmt USING @sfiledStr,@sthisCode,@sreturnVarchar,@sthisPosition,@smanualCode,@scode,@sname,@smapCode,@svalueStr;
endselect `position` into thisPosition from `teleprovider` where teleProviderID = nextNodeID;
select max(`position`)+1 into thisPosition from `teleprovider` where getParent(autoCode)=parentCode;
如何实现动态?
望高手指教
prepare stmt from @sqlstr;
EXECUTE stmt;
deallocate prepare stmt;我看你都会这个了啊
还有什么问题呢?
qq群10939695
fetion群:8460606