悬赏100分,将sqlserver下的脚本翻译成mysql的脚本

易企加_天马行空 2011-11-01 06:51:43
脚本如下,就是从系统表里获取某张表的字段的一些信息,然后写到对应的自定义的元数据表Sync_MetaData,Sync_MetaItem.
create proc up_InitMeta
@tableName nvarchar(50),
@dataImporter nvarchar(1000),
@changedDataConverter nvarchar(1000)
as
begin
declare @metaDataID int,@xOrder int
select @xOrder = max(XOrder) + 1 from Sync_MetaData
if(@xOrder is null)
set @xOrder= 1
INSERT INTO [Sync_MetaData]
([TableName]
,[IgoreCase]
,[xOrder]
,[DataImporter]
,[ChangedDataConverter])
VALUES
(@tableName
,1
,@xOrder
,@dataImporter
,@changedDataConverter)
set @metaDataID = @@IDENTITY
set @xOrder = 1
INSERT INTO [Sync_MetaItem]
select @metaDataID,c.name,c.name,dbo.fc_GetTypeCode(t.name),0,c.is_nullable,null,null,null,ROW_NUMBER() OVER (ORDER BY column_id) from sys.columns c
inner join sys.systypes t on t.xusertype=c.system_type_id
where c.is_identity = 0 and c.object_id in (select object_id from sys.objects where name = @tableName)

Update k Set k.IsPrimaryKey=1 From Sync_MetaItem k Where k.MetaDataID in (select MetaDataID from Sync_MetaData where TableName=@tableName)
And Exists (Select 1 from sysindexkeys ik,syscolumns c
where ik.id=c.Id and ik.colid=c.colid and ik.id=Object_id(@tableName) and c.Name=k.ColumnName
And Exists(select 1 from sysindexes i where ik.id=i.id and ik.indid=i.indid
and i.status & 2948=2048))
end
go

create function fc_GetTypeCode
(
@typename nvarchar(50)
)
returns int
as
begin
if CHARINDEX('int',@typename) > 0
return 9
if CHARINDEX('char',@typename) > 0
return 18
if CHARINDEX('bit',@typename) > 0
return 3
if CHARINDEX('date',@typename) > 0
return 16
if CHARINDEX('decimal',@typename) > 0
return 15
if CHARINDEX('float',@typename) > 0
return 13
if CHARINDEX('text',@typename) > 0
return 18
if CHARINDEX('money',@typename) > 0
return 18
if CHARINDEX('numeric',@typename) > 0
return 14
if CHARINDEX('time',@typename) > 0
return 20
if CHARINDEX('xml',@typename) > 0
return 18

return -1
end
...全文
264 17 打赏 收藏 转发到动态 举报
写回复
用AI写文章
17 条回复
切换为时间正序
请发表友善的回复…
发表回复
wwwwb 2011-11-02
  • 打赏
  • 举报
回复
DELIMITER $$
DROP PROCEDURE IF EXISTS up_InitMeta$$
CREATE PROCEDURE up_InitMeta(databaseName VARCHAR(50),tableName VARCHAR(50),dataImporter VARCHAR(1000),changedDataConverter VARCHAR(1000))
BEGIN
DECLARE metaDataID INT;
DECLARE xOrder INT;
DECLARE a INT;
SET xOrder = (SELECT MAX(XOrder) + 1 FROM Sync_MetaData );
IF xOrder IS NULL THEN
SET xOrder= 1;
END IF;
INSERT INTO Sync_MetaData
(TableName
,IgoreCase
,xOrder
,DataImporter
,ChangedDataConverter)
VALUES
(tableName
,1
,xOrder
,dataImporter
,changedDataConverter);

SET metaDataID = lcast_insert_id();
SET xOrder = 1;
SET @a = 1;
INSERT INTO Sync_MetaItem
SELECT metaDataID,COLUMN_NAME ,COLUMN_NAME ,fc_GetTypeCode(DATA_TYPE), CASE WHEN COLUMN_KEY = 'PRI' THEN 1 ELSE 0 END,
CASE WHEN IS_NULLABLE = 'YES' THEN 1 ELSE 0 END,NULL,NULL,NULL,@a:=@a +1
FROM information_schema.COLUMNS
WHERE EXTRA <> 'auto_increment' AND TABLE_SCHEMA =databaseName AND TABLE_NAME = tableName;
END$$

DELIMITER ;
SHOW WARNINGS;
  • 打赏
  • 举报
回复
wwwwb,我按照你的提示,把存储过程也改了下,但执行不通过,麻烦你帮我看看,感谢!

DELIMITER $$
DROP PROCEDURE IF EXISTS up_InitMeta$$
CREATE PROCEDURE up_InitMeta
(
databaseName NVARCHAR(50),
tableName NVARCHAR(50),
dataImporter NVARCHAR(1000),
changedDataConverter NVARCHAR(1000)
)
BEGIN
DECLARE metaDataID INT;
DECLARE xOrder INT;
DECLARE a INT;

SET xOrder = (select MAX(XOrder) + 1 from Sync_MetaData );
IF xOrder is null THEN
SET xOrder= 1;
END IF;
INSERT INTO Sync_MetaData
(TableName
,IgoreCase
,xOrder
,DataImporter
,ChangedDataConverter)
VALUES
(tableName
,1
,xOrder
,dataImporter
,changedDataConverter);

SET metaDataID = lcast_insert_id();
SET xOrder = 1;
SET a = 1;
INSERT INTO Sync_MetaItem
SELECT metaDataID,COLUMN_NAME ,COLUMN_NAME ,fc_GetTypeCode(DATA_TYPE), CASE WHEN COLUMN_KEY = 'PRI' THEN 1 ELSE 0 END,
CASE WHEN IS_NULLABLE = 'YES' THEN 1 ELSE 0 END,null,null,null,a:=a +1
FROM information_schema.COLUMNS
WHERE EXTRA <> 'auto_increment' AND TABLE_SCHEMA =databaseName AND TABLE_NAME = tableName;
END$$
DELIMITER ;
wwwwb 2011-11-02
  • 打赏
  • 举报
回复
set @a=0;
select f1,f2,@a:=@a+1 from (select * from tt order by column_id) a ;
wwwwb 2011-11-02
  • 打赏
  • 举报
回复
or

set @a=0;
select f1,f2,@a:=@a+1 from tt;
wwwwb 2011-11-02
  • 打赏
  • 举报
回复
sql的关键字,都要大写嘛?不用大写,建议不使用保留字,如果要用,加``,如`sql`


还问个问题,在sqlserver2008里有个ROW_NUMBER() OVER (ORDER BY column_id)的函数,不知道在mysql是否有对应的函数啊。
MYSQL中没有此函数,插入有自增字段的临时表中 OR 如表中有唯一标识的字段,用查询解决
  • 打赏
  • 举报
回复
执行成功,非常感谢wwwwb

sql的关键字,都要大写嘛?一直弄sqlserver,都不知道标准的t-sql是啥样了。。。。


还问个问题,在sqlserver2008里有个ROW_NUMBER() OVER (ORDER BY column_id)的函数,不知道在mysql是否有对应的函数啊。
wwwwb 2011-11-02
  • 打赏
  • 举报
回复
ELSE
SET retV = -1;
END IF; -- 少了分号
wwwwb 2011-11-02
  • 打赏
  • 举报
回复
DELIMITER $$
DROP FUNCTION IF EXISTS fc_GetTypeCode$$
CREATE FUNCTION fc_GetTypeCode
(
typename NVARCHAR(50)
)
RETURNS INT
BEGIN
DECLARE retV INT;

IF INSTR('int',typename) > 0 THEN
SET retV = 9;
ELSEIF INSTR('char',typename) > 0 THEN
SET retV = 18;
ELSEIF INSTR('bit',typename) > 0 THEN
SET retV = 3;
ELSEIF INSTR('date',typename) > 0 THEN
SET retV = 16;
ELSEIF INSTR('decimal',typename) > 0 THEN
SET retV = 15;
ELSEIF INSTR('float',typename) > 0 THEN
SET retV = 13;
ELSEIF INSTR('text',typename) > 0 THEN
SET retV = 18;
ELSEIF INSTR('money',typename) > 0 THEN
SET retV = 18;
ELSEIF INSTR('numeric',typename) > 0 THEN
SET retV = 14;
ELSEIF INSTR('time',typename) > 0 THEN
SET retV = 20;
ELSEIF INSTR('xml',typename) > 0 THEN
SET retV = 18;
ELSE
SET retV = -1;
END IF;

RETURN retV;
END$$
DELIMITER ;
  • 打赏
  • 举报
回复
高手给个解答啊,急呢
  • 打赏
  • 举报
回复

create function fc_GetTypeCode
(
typename nvarchar(50)
)
returns int
begin
declare retV int;

if instr('int',typename) > 0 then
set retV = 9;
elseif instr('char',typename) > 0 then
set retV = 18;
elseif instr('bit',typename) > 0 then
set retV = 3;
elseif instr('date',typename) > 0 then
set retV = 16;
elseif instr('decimal',typename) > 0 then
set retV = 15;
elseif instr('float',typename) > 0 then
set retV = 13;
elseif instr('text',typename) > 0 then
set retV = 18;
elseif instr('money',typename) > 0 then
set retV = 18;
elseif instr('numeric',typename) > 0 then
set retV = 14;
elseif instr('time',typename) > 0 then
set retV = 20;
elseif instr('xml',typename) > 0 then
set retV = 18;
else
set retV = -1;
end if

return retV;
end
我已经改成这样还是报第7行有错误。。。
ACMAIN_CHM 2011-11-02
  • 打赏
  • 举报
回复
[Quote]12.8.6.1. IF Statement
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF

IF implements a basic conditional construct. If the search_condition evaluates to true, the corresponding SQL statement list is executed. If no search_condition matches, the statement list in the ELSE clause is executed. Each statement_list consists of one or more statements.

Note
There is also an IF() function, which differs from the IF statement described here. See Section 11.3, “Control Flow Functions”.

An IF ... END IF block, like all other flow-control blocks used within stored programs, must be terminated with a semicolon, as shown in this example:

DELIMITER //

CREATE FUNCTION SimpleCompare(n INT, m INT)
RETURNS VARCHAR(20)

BEGIN
DECLARE s VARCHAR(20);

IF n > m THEN SET s = '>';
ELSEIF n = m THEN SET s = '=';
ELSE SET s = '<';
END IF;

SET s = CONCAT(n, ' ', s, ' ', m);

RETURN s;
END //

DELIMITER ;


As with other flow-control constructs, IF ... END IF blocks may be nested within other flow-control constructs, including other IF statements. Each IF must be terminated by its own END IF followed by a semicolon. You can use indentation to make nested flow-control blocks more easily readable by humans (although this is not required by MySQL), as shown here:

DELIMITER //

CREATE FUNCTION VerboseCompare (n INT, m INT)
RETURNS VARCHAR(50)

BEGIN
DECLARE s VARCHAR(50);

IF n = m THEN SET s = 'equals';
ELSE
IF n > m THEN SET s = 'greater';
ELSE SET s = 'less';
END IF;

SET s = CONCAT('is ', s, ' than');
END IF;

SET s = CONCAT(n, ' ', s, ' ', m, '.');

RETURN s;
END //

DELIMITER ;

[/Quote]
wwwwb 2011-11-02
  • 打赏
  • 举报
回复
if instr('int',typename) > 0 THEN
select 9;
END IF;

其它的自行修改
  • 打赏
  • 举报
回复
create function fc_GetTypeCode
(
typename nvarchar(50)
)
returns int
begin
if instr('int',typename) > 0
select 9;
if instr('char',typename) > 0
select 18;
if instr('bit',typename) > 0
select 3;
if instr('date',typename) > 0
select 16;
if instr('decimal',typename) > 0
select 15;
if instr('float',typename) > 0
select 13;
if instr('text',typename) > 0
select 18;
if instr('money',typename) > 0
select 18;
if instr('numeric',typename) > 0
select 14;
if instr('time',typename) > 0
select 20;
if instr('xml',typename) > 0
select 18;

select -1;
end
我把这个函数改成这样了,但报第五行有语法错误
ACMAIN_CHM 2011-11-02
  • 打赏
  • 举报
回复
建议楼主先自己参考手册上的说明和例子改一下。然后如果有什么问题可以把你的代码及错误信息一同贴出大家一起分析。
wwwwb 2011-11-02
  • 打赏
  • 举报
回复
变量@去掉 charindex换成instr,语句用分号结束
看看MYSQL HELP,自己动手做一下吧,有问题再问
select @xOrder = max(XOrder) + 1 from Sync_MetaData ->
set xOrder = (select max(XOrder) + 1 from Sync_MetaData );
set @metaDataID = @@IDENTITY->set @metaDataID = lcast_insert_id()

如果是5以上,看看的系统表 information_schma
jinguanding 2011-11-01
  • 打赏
  • 举报
回复
这个好写 要么参考手册上的示例 要么来参考 网站: www.mysqlops.com 上的示例来了.....
rucypli 2011-11-01
  • 打赏
  • 举报
回复
变量@去掉 charindex换成instr return换成select

系统表进information_schma库查看

56,675

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧