这是一个Sql Server的存储过程:
请帮我改成相同的Oracle过程;CREATE PROCEDURE dbo.GetProjectInfo
(
@Condition nvarchar(1000)
)
AS
declare @sqlString nvarchar(1000)
if ltrim(rtrim(@Condition))=''
select top 1 * from project
else
if substring(@Condition,1,1)='{' and substring(@Condition,len(@Condition),1)='}'
begin
set @sqlString='select * from project where '+ substring(@Condition,2,len(@Condition)-2)
exec sp_executesql @sqlString
end
else
select * from project where project_code like @Condition
/* SET NOCOUNT ON */
RETURN
GO
请帮我改成相同的Oracle过程;CREATE PROCEDURE dbo.GetProjectInfo
(
@Condition nvarchar(1000)
)
AS
declare @sqlString nvarchar(1000)
if ltrim(rtrim(@Condition))=''
select top 1 * from project
else
if substring(@Condition,1,1)='{' and substring(@Condition,len(@Condition),1)='}'
begin
set @sqlString='select * from project where '+ substring(@Condition,2,len(@Condition)-2)
exec sp_executesql @sqlString
end
else
select * from project where project_code like @Condition
/* SET NOCOUNT ON */
RETURN
GO
exec sp_executesql @sqlString改成
exec sqlString
要改的地方很多。很多语句也要改:
1:if then else
2:oracle没有select top 1 * from project表达方式,
改为select * from project where rownum=1
但在过程中必须select * into 变量 from project where rownum=1
3:
(
@Condition nvarchar(1000)
)
输入参数不能带长度限制。
4:AS
declare @sqlString nvarchar(1000)
as 后面的变量定义要去掉declare,句子最后用“;”号等等很多,都不知道怎么跟你说。。
CREATE PROCEDURE dbo.GetProjectInfo
(
Condition varchar2
)
IS
sqlString varchar2(1000);
begin
if ltrim(rtrim(Condition))='' then
select * from project where rownum < 2;
elsif substring(Condition,1,1)='{' and substring(Condition,len(Condition),1)='}' then
set sqlString='select * from project where '+ substring(Condition,2,len(Condition)-2);
exec sqlString;
else
select * from project where project_code like Condition;
end if;
end dbo.GetProjectInfo;