How to make an oracle creation function to insert data into the table?

Asked

Viewed 12 times

-1

someone could give me a hint, as it transforms this function table Valued functions, from mssql, to oracle! Parameter entry is ini month, ini year, end month, end year And returns year, month and a sequence.

SELECT ANO,MES,SEQ FROM dbo.fn_IntData('01','2021','12','2021') 

ALTER Function [dbo].[fn_IntData]
    (
        @MESINI Varchar(2),
        @ANONI  Varchar(4),
        @MESFIM Varchar(2),
        @ANOFIM Varchar(4)                                       
    )
Returns @INTDATA Table 
    (
        ANO Char(4) COLLATE SQL_Latin1_General_CP850_BIN,
        MES Char(2) COLLATE SQL_Latin1_General_CP850_BIN,
        SEQ Char(2) COLLATE SQL_Latin1_General_CP850_BIN
    ) As BEGIN 
    DECLARE @_DTINI VARCHAR(8)
    DECLARE @_DTFIM VARCHAR(8)
    DECLARE @_CONT INT 
    SET @_DTINI = ( @ANONI + @MESINI + '01' )
    SET @_DTFIM = ( @ANOFIM + @MESFIM + '01' )
    SET @_CONT = 0 

   WHILE (SELECT DATEDIFF(M,@_DTINI,@_DTFIM) + 1 ) <> 0 
   BEGIN

    INSERT INTO @INTDATA(ANO,MES,SEQ) SELECT SUBSTRING(@_DTINI,1,4) , SUBSTRING(@_DTINI,5,2) , dbo.F010_STRZERO(@_CONT,2) 
    SET @_DTINI = ( SELECT CONVERT(VARCHAR(8),DATEADD(M,1,@_DTINI),112) )
    SET @_CONT = @_CONT + 1 
   END   

RETURN
END
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.