最新下载
热门教程
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
mssql server 数据库存储过程转换成mysql数据库
时间:2010-06-07 编辑:简简单单 来源:一聚教程网
mssql server 数据库教程存储过程转换成mysql教程数据库
DELIMITER $$
DROP PROCEDURE IF EXISTS ChangeSequence$$
CREATE PROCEDURE ChangeSequence
(
_sign INT ,-- 0: 上移 1:下移
TableName VARCHAR(50) ,-- 表名
ItemName VARCHAR(50) ,-- 主键字段名
ItemID INT , -- 主键ID
SortName VARCHAR(50) , -- 排序ID
TypeName VARCHAR(50) , -- 分类字段名
TypeValue VARCHAR(50) -- 分类值
)
BEGIN
DECLARE _SQL VARCHAR(4000);
DECLARE ThisSort INT;
DECLARE PREVID INT;
DECLARE NextID INT;
DECLARE _Count INT;CREATE TEMPORARY TABLE _Tab
(
_ItemID INT,
_Sort INT
);
SET _SQL=CONCAT('INSERT INTO _Tab (_ItemID,_Sort) SELECT ' ,ItemName,SortName,' FROM ',TableName) ;IF (TypeName<>'' AND TypeValue<>'') THEN
SET _SQL=CONCAT(_SQL,' where ',TypeName,'=',TypeValue);
END IF;
SET _SQL = CONCAT(_SQL,' ORDER BY _Sort ASC ') ;
SET @v_sql=_SQL;
PREPARE stmt FROM @v_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET _SQL = '';
SELECT COUNT(*) INTO _Count FROM _Tab ;
SELECT Sort INTO ThisSort FROM _Tab WHERE _ItemID = ItemID;
IF(ThisSort>1) THEN
SELECT _ItemID INTO PREVID FROM _Tab WHERE _Sort=(ThisSort-1);
END IF;
IF(ThisSort<_Count) THEN SELECT _ItemID INTO NextID FROM _Tab WHERE _Sort=(ThisSort+1);END IF;
IF(_Sign=0) THEN
IF(ThisSort>1) THEN
SET _SQL = CONCAT('UPDATE ',TableName,' SET ',SortName,'=','CONVERT((',ThisSort,'-1),varchar(100))',' WHERE ',ItemName,' = ','CONVERT(',ItemID,',varchar(100))' , ';' ,
'UPDATE ',TableName,' SET ',SortName,'=','CONVERT(',ThisSort,',varchar(100))',' WHERE ',ItemName,' = ','CONVERT(',PREVID,',varchar(100))');
END IF;
ELSE
IF(ThisSort<_Count) THEN
SET _SQL =CONCAT( 'UPDATE ',TableName,' SET ',SortName,'=','CONVERT((',ThisSort,'+1),varchar(100))',' WHERE ',ItemName,' = ','CONVERT(',ItemID,',varchar(100))',';',
'UPDATE ',TableName,' SET ',SortName,'=','CONVERT(',ThisSort,',varchar(100))',' WHERE ',ItemName,' = ','CONVERT(',NextID,',varchar(100))');
END IF;
END IF;
SET @v_sql1=_SQL;
PREPARE stmt1 FROM @v_sql1;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END$$
相关文章
- MySQL登录、访问及退出操作解析 10-18
- sql语句 update字段null不能用is null问题解析 09-28
- SQL Server ISNULL 不生效原因及解决分析 09-28
- 关于if exists的用法及说明分析 09-28
- Access数据库日常维护方法 09-28
- Oracle 删除大量表记录操作介绍 09-27