hi,
can anyone help me in giving this sql server stored procedure into oracle
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'GetMedian' AND type = 'P')
DROP PROCEDURE GetMedian
GO
Create Procedure GetMedian (@SQLStmt Varchar(8000)) AS
Declare
@String Varchar(8000),
@Value int,
@Row1 int,
@Row2 int,
@Value1 Decimal(20,3),
@Value2 Decimal(20,3)
Begin
SET @String = 'Declare CurMedian CURSOR SCROLL FOR ' + @SQLStmt
EXEC (@String)
OPEN CurMedian
SET @Value = @@Cursor_Rows
IF (@@Cursor_Rows%2) = 1
Begin
SET @Row1 = (@Value/2)+1
SET @Row2 = 0
Fetch Absolute @Row1 From CurMedian INTO @Value1
SET @Value = @Value1
End
Else
Begin
SET @Row1 = (@Value/2)
SET @Row2 = (@Value/2)+1
Fetch Absolute @Row1 From CurMedian INTO @Value1
Fetch First From CurMedian Into @Value2
SET @Value2 = 0
Fetch Absolute @Row2 From CurMedian INTO @Value2
SET @Value = (@Value1 + @Value2)/2
End
Close CurMedian
Deallocate CurMedian
Select @Value
End
GO