MySQL不支持动态表名

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/gua___gua/article/details/49996001

今天写一个数据迁移的存储过程,用到了动态表名,发现MYSQL不支持直接使用变量做表名。然后就用下面的方式来做了。

DROP PROCEDURE IF EXISTS p_transfer_data;
delimiter //  

CREATE PROCEDURE p_transfer_data()
begin 
  declare v_dbname varchar(100) default database();
  declare v_tabname varchar(100);
  declare flag int default 0;  /*是否达到记录的末尾控制变量*/
  
  DECLARE v_cur CURSOR FOR SELECT table_name FROM  information_schema.TABLES WHERE  TABLE_SCHEMA=v_dbname AND table_name like 't_2%' order by table_name desc;
  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
  open v_cur;
  FETCH v_cur INTO v_tabname;/*获取第一条记录*/
  
  while flag <> 1 do
    set @preparable_stmt1=CONCAT("insert into t_x(ID_,NAME)  select ID_,NAME ",v_tabname);
    set @preparable_stmt2=CONCAT("insert into t_y(ID_,AREA) select ID_,AREA from ",v_tabname);
	prepare preparable_stmt1 from @preparable_stmt1;
    prepare preparable_stmt2 from @preparable_stmt2;
    EXECUTE preparable_stmt1;
	EXECUTE preparable_stmt2;
	FETCH v_cur INTO v_tabname;/*取下一条记录*/
  end while;
  close v_cur;

end;
//

delimiter ;

-- call p_transfer_data();





展开阅读全文

动态表名的存储过程

06-30

想用存储过程来向SQL 2008数据库中插入数据,但是表名不定,所以把表名作为参数传入,由于初次写存储过程,搞了一上午也没弄成功,老是报IDispatch error #3092错误,有会的帮忙看看什么问题,多谢。rn存储过程:rn[code=c]rnCREATE PROCEDURE insert_defectrn@table_name nvarchar(255),rn@coil_id int, rn@class int, rn@suface int, rn@xpos int,rn@ypos int,rn@area int,rn@defltime nvarchar(255)rn ASrn declare @strSQL nvarchar(1000)rn declare @param1 nvarchar(64)rn declare @param2 nvarchar(64)rn declare @param3 nvarchar(64)rn declare @param4 nvarchar(64)rn declare @param5 nvarchar(64)rn declare @param6 nvarchar(64)rn set @param1 = @coil_idrn set @param2 = @classrn set @param3 = @sufacern set @param4 = @xposrn set @param5 = @yposrn set @param6 = @arearn select @strSQL = N'insert into '+@table_name+N'(COIL_ID ,CLASS ,SURFACE ,XPOS ,YPOS ,AREA ,DEFECT_TIME) values('+@param1+N','+@param2+N','+@param3+N','+@param4+N','+@param5+N','+@param6+N','+@defltime+N'); select SCOPE_IDENTITY()'rn exec(@strSQL)rnGOrn[/code]rnrn我在T-SQL里写入execute insert_defect @table_name='defect_table0',@coil_id=1,@class=0,@suface=1,@xpos=2,@ypos=3,@area=100,@defltime='2013-02-19'是可以成功的,但是用ADO调用老是不行:rn[code=c]rn tryrn rn _CommandPtr _pCmd; rn _pCmd.CreateInstance("ADODB.Command");//__uuidof(Command)); rn _pCmd->ActiveConnection=pCnn; rn _pCmd->CommandType=adCmdStoredProc; rn _pCmd->CommandText=_bstr_t("insert_defect"); rnrnrn _ParameterPtr pParam;rn pParam.CreateInstance("ADODB.Parameter");rnrn pParam=_pCmd->CreateParameter("tab_name",adVarChar,adParamInput,255,_variant_t(_bstr_t(strTable))); rn _pCmd->Parameters->Append(pParam); rnrn pParam=_pCmd->CreateParameter("coil_id",adInteger,adParamInput,sizeof(int),(_variant_t)(int)m_dwCurCoilID); rn _pCmd->Parameters->Append(pParam); rnrn pParam=_pCmd->CreateParameter("class",adInteger,adParamInput,sizeof(int),(_variant_t)lpInfo->uType); rn _pCmd->Parameters->Append(pParam); rnrn pParam=_pCmd->CreateParameter("suface",adInteger,adParamInput,sizeof(int),(_variant_t)lpInfo->uiFace); rn _pCmd->Parameters->Append(pParam); rnrn pParam=_pCmd->CreateParameter("xpos",adInteger,adParamInput,sizeof(int),(_variant_t)lpInfo->uPositionFromL); rn _pCmd->Parameters->Append(pParam); rnrnrn pParam=_pCmd->CreateParameter("ypos",adInteger,adParamInput,sizeof(int),(_variant_t)lpInfo->uPositionFromH); rn _pCmd->Parameters->Append(pParam); rnrn pParam=_pCmd->CreateParameter("area",adInteger,adParamInput,sizeof(int),(_variant_t)lpInfo->uiErea); rn _pCmd->Parameters->Append(pParam); rnrn pParam=_pCmd->CreateParameter("deftime",adVarChar,adParamInput,255,_bstr_t(_T("1989-02-19 02:03:04"))); rn _pCmd->Parameters->Append(pParam); rn rn _variant_t vNull; rn vNull.vt=VT_ERROR; rn vNull.scode=DISP_E_PARAMNOTFOUND; rn _pCmd->Execute(&vNull,&vNull,adCmdStoredProc); //这里报错误rn pParam.Release();rn catch (_com_error e)rn rn TRACE("InsertNewDefect error:%S\n",e.ErrorMessage());rn rn[/code] 论坛

没有更多推荐了,返回首页