oracle存储过程 一个sql语句赋值两个变量,存储过程中将sql语句结果赋值给变量...

create  procedure procname

AS

declare @recount int

select @recount=count(id) from school

--最关键在这句

select @recount=count(id) from school

select @recount=select count(id) from school -- 错误:这里就不再需要select了

set @recount=count(id) from school -- 错误这里只能用select不能用set

如果school为一个@chart参数的话，上面方法就不适用了

应该为:

declare @getRecordSql nvarchar(100)

set @getRecordSql='select @recordcount=count(1) from '+@chart

exec sp_executesql @getRecordSql,N'@recordCount int output',@recordCount output

N'@recordCount int output',@recordCount output

做一个参数的替换,如果测试一下:print @recordCount的话就会显示行数

//示例:选择最新的5条数据从20条开始

execute A_School_getNew 5,20

--存储过程

create procedure A_School_getNew @Size int, @StartIndex intASdeclare @recount intdeclare @Sql nvarchar(120)select @recount=count(id) from schoolset @Sql='select top '+convert(nvarchar,@Size)+'id,school_name from school where('+ 'ID not in(select top '+convert(nvarchar,@recount-@startIndex+@size)+ ' id from school)) order by id desc';execute (@Sql)