公司需要将SQLSERVER数据库转移到ORACLE,我按照SQL的存储过程试着转换成Oracle,执行编译正确,但在调试时出现以下错误::执行转换例行程序:整数溢出,请高手们帮忙看看是哪里出错了,谢谢了啊create or replace
PROCEDURE proc_AddUpdateSumFooter(
TableName in varchar2,
OrgID in nvarchar2,
TimeStamp in varchar2
)
AS Param_TargetAssessScore number;
Param_ExceedLimitAssessScore number;
Param_TotalScore number;
SqlIsExist varchar2(500);
mySql varchar2(500);
mySql2 varchar2(500);
tID number;
begin --the add
execute immediate 'select sum(Param_TargetAssessScore) into Param_TargetAssessScore
from '||TableName||' where TimeStamp='||TimeStamp||' and OrgID = '||OrgID||' and
Param_ID is not null';
if Param_TargetAssessScore is null then
Param_TargetAssessScore := 0;
elsif Param_TargetAssessScore > 100 then
Param_TargetAssessScore := 100;
elsif Param_TargetAssessScore <0 then
Param_TargetAssessScore := 0;
end if;
execute immediate 'select sum(Param_ExceedLimitAssessScore) into Param_ExceedLimitAssessScore
from '||TableName||' where TimeStamp='||TimeStamp||' and OrgID = '||OrgID||' and
Param_ID is not null';
if Param_ExceedLimitAssessScore is null then
Param_ExceedLimitAssessScore := 0;
elsif Param_ExceedLimitAssessScore > 100 then
Param_ExceedLimitAssessScore := 100;
elsif Param_ExceedLimitAssessScore <0 then
Param_ExceedLimitAssessScore := 0;
end if;
execute immediate 'select sum(Param_TotalScore) into Param_TotalScore
from '||TableName||' where TimeStamp='||TimeStamp||' and OrgID = '||OrgID||' and
Param_ID is not null';
if Param_TotalScore is null then
Param_TotalScore := 0;
elsif Param_TotalScore > 100 then
Param_TotalScore := 100;
elsif Param_TotalScore <0 then
Param_TotalScore := 0;
end if;
mySql2 :='SELECT ID into tID FROM '||TableName||' where TimeStamp='|| TimeStamp ||' and OrgID =' || OrgID ||' and Param_ID is null)'; execute immediate mySql2;if tID is not null then mySql :=' INSERT INTO '|| TableName ||' (
ID,
OrgID,
TimeStamp,
Param_TargetAssessScore,
Param_ExceedLimitAssessScore,
Param_TotalScore
) VALUES (
''f2acf9cf-a561-4e99-8c00-8e70b1293cd3'',
'||OrgID||' ,
'||TimeStamp||',
convert(float, ' || Param_TargetAssessScore|| '),
convert(float, ' || Param_ExceedLimitAssessScore ||'),
convert(float, ' || Param_TotalScore ||')
)';
else
mySql := 'UPDATE ' || TableName ||' SET
Param_TargetAssessScore = convert(float, '|| Param_TargetAssessScore ||'),
Param_ExceedLimitAssessScore =convert(float, '|| Param_ExceedLimitAssessScore||'),
Param_TotalScore = convert(float, '|| Param_TotalScore||')
WHERE
OrgID='||OrgID||' and TimeStamp = '||TimeStamp||' and Param_ID is null';
end if;execute immediate mySql;end proc_AddUpdateSumFooter;--endregion
PROCEDURE proc_AddUpdateSumFooter(
TableName in varchar2,
OrgID in nvarchar2,
TimeStamp in varchar2
)
AS Param_TargetAssessScore number;
Param_ExceedLimitAssessScore number;
Param_TotalScore number;
SqlIsExist varchar2(500);
mySql varchar2(500);
mySql2 varchar2(500);
tID number;
begin --the add
execute immediate 'select sum(Param_TargetAssessScore) into Param_TargetAssessScore
from '||TableName||' where TimeStamp='||TimeStamp||' and OrgID = '||OrgID||' and
Param_ID is not null';
if Param_TargetAssessScore is null then
Param_TargetAssessScore := 0;
elsif Param_TargetAssessScore > 100 then
Param_TargetAssessScore := 100;
elsif Param_TargetAssessScore <0 then
Param_TargetAssessScore := 0;
end if;
execute immediate 'select sum(Param_ExceedLimitAssessScore) into Param_ExceedLimitAssessScore
from '||TableName||' where TimeStamp='||TimeStamp||' and OrgID = '||OrgID||' and
Param_ID is not null';
if Param_ExceedLimitAssessScore is null then
Param_ExceedLimitAssessScore := 0;
elsif Param_ExceedLimitAssessScore > 100 then
Param_ExceedLimitAssessScore := 100;
elsif Param_ExceedLimitAssessScore <0 then
Param_ExceedLimitAssessScore := 0;
end if;
execute immediate 'select sum(Param_TotalScore) into Param_TotalScore
from '||TableName||' where TimeStamp='||TimeStamp||' and OrgID = '||OrgID||' and
Param_ID is not null';
if Param_TotalScore is null then
Param_TotalScore := 0;
elsif Param_TotalScore > 100 then
Param_TotalScore := 100;
elsif Param_TotalScore <0 then
Param_TotalScore := 0;
end if;
mySql2 :='SELECT ID into tID FROM '||TableName||' where TimeStamp='|| TimeStamp ||' and OrgID =' || OrgID ||' and Param_ID is null)'; execute immediate mySql2;if tID is not null then mySql :=' INSERT INTO '|| TableName ||' (
ID,
OrgID,
TimeStamp,
Param_TargetAssessScore,
Param_ExceedLimitAssessScore,
Param_TotalScore
) VALUES (
''f2acf9cf-a561-4e99-8c00-8e70b1293cd3'',
'||OrgID||' ,
'||TimeStamp||',
convert(float, ' || Param_TargetAssessScore|| '),
convert(float, ' || Param_ExceedLimitAssessScore ||'),
convert(float, ' || Param_TotalScore ||')
)';
else
mySql := 'UPDATE ' || TableName ||' SET
Param_TargetAssessScore = convert(float, '|| Param_TargetAssessScore ||'),
Param_ExceedLimitAssessScore =convert(float, '|| Param_ExceedLimitAssessScore||'),
Param_TotalScore = convert(float, '|| Param_TotalScore||')
WHERE
OrgID='||OrgID||' and TimeStamp = '||TimeStamp||' and Param_ID is null';
end if;execute immediate mySql;end proc_AddUpdateSumFooter;--endregion
解决方案 »
- 一个关于oracle数据库的问题,,着急!~!
- 这个问题困扰了我们组的很多人。不知道这边有没有人可以提些意见帮忙解决
- 求一个行列转换的写法(ORACLE),多谢
- 请教:客户端浏览器无法打开oracle10g
- 看我这个触发器怎么了?急等!
- 很奇怪的用户权限问题
- oracle9i参数调整问题
- Select * from user1.tb1 中u1 的问题。
- 关于在旧机器上安装Oracle 8.1.6 for Windows的问题
- 怎么修改internal的密码??
- oracle,函数如何实现,列里的字符排序
- 连接错误:ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
应该是为变量赋值超出了数据类型的设定长度
或者是插入表中的数据超出了列的数据类型的长度