How to create a function similar to Oracle (or a procedure with a return value)?

Database
Enthusiast

How to create a function similar to Oracle (or a procedure with a return value)?

How to create a function similar to Oracle (or a procedure with a return value)?

Hello everyone,

Functions defined by the user in Teradatab seem to be quite different to the concept learnt in Oracle.

I would like to know how to create a funcion that receives parameters and return a value. Something like this Oracle one:

FUNCTION F_QUITA_REPES (cCadena IN varchar2, delchar IN char) RETURN varchar2 IS

cSubCad VARCHAR2(252) := cCadena;

BEGIN

IF delchar = chr(39) THEN
cSubCad := LTRIM(cSubCad, delchar);
ELSE
cSubCad := LTRIM(RTRIM(cSubCad, delchar), delchar);
END IF;

IF cSubCad IS NOT NULL THEN
WHILE cSubCad LIKE '%'||delchar||delchar||'%' LOOP
cSubCad:=REPLACE(cSubCad, delchar||delchar, delchar);
END LOOP;
END IF;

RETURN(cSubCad);

END F_QUITA_REPES;

That later can be called inside another function or procedure like:

cCadNomVia := F_QUITA_REPES(cCadNomVia, '/');

Or like:

IF F_QUITA_REPES(cCadNomVia, '/') is null and F_QUITA_REPES(cCadNomVia, ' ') is null THEN

Cos I think Stored Procedures can't have a return value, right?

Also, I would need to be able to have OUT and INOUT parameters in the functions...

Thanks in advance and best regards

Juan
12 REPLIES
Senior Apprentice

Re: How to create a function similar to Oracle (or a procedure with a return value)?

SQL functions in Teradata are very limited, no SELECT, loops, etc. For complex logic you need C or Java.

But in your case the logic can easily be rewritten using regex, now it's one code block and this can be a UDF:

REPLACE FUNCTION F_QUITA_REPES (cCadena VARCHAR(255), delchar VARCHAR(1)) 
RETURNS VARCHAR(255)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
REGEXP_REPLACE(CASE
WHEN delchar = CHR(39)
THEN LTRIM(cCadena, delchar)
ELSE LTRIM(RTRIM(cCadena, delchar), delchar)
END, delchar||'+', delchar)
;

Caution, a VARCHAR without length is a VARCHAR(1) in Teradata.

Enthusiast

Re: How to create a function similar to Oracle (or a procedure with a return value)?

Ok Dieter, thanks for the information!

Anyway, I have functions more complex than that in Oracle to translate (and also, sometimes is easier just to try a translation of code and not a big change), so I guess a good option could be to convert functions to procedures, right?

The annoying thing would be the use of the new procedures for getting results to catch on the fly, like the examples I exposed before:

variable := FUNCTION(a, b);

IF FUNCTION_01(a,b) > 3 and FUNCTION_02(b,c) > 5 THEN ...

I guess an alternative, after converting the functions to procedures, could be to create auxuliar variables and do things like:

DECLARE var1 INTEGER;
DECLARE var2 INTEGER;

CALL FUNCTION_01(a, b, var1); -- I add to the new procedure the return value of the olf function as an OUT parameter
CALL FUNCTION_02(b, c, var2);

IF var1 > 3 and var2 > 5 THEN ...

What do you think about this? ... Because procedures in Teradata can't have a return value, can they?

Regards,

Juan
Senior Apprentice

Re: How to create a function similar to Oracle (or a procedure with a return value)?

SPs only return OUT parameters (or result sets), but not a single value like a function, so you can't use it directly and your proposed alternative is the only way.

Enthusiast

Re: How to create a function similar to Oracle (or a procedure with a return value)?

Ok, thanks again Dieter!

By the way, the function you made has only one part of the code I exposed before, the first IF-THEN-ELSE, but not the second IF with the WHILE inside.

According to what I've read, functions can only seem to have one RETURN sencence (and no loops either).

So I guess there is not a way to convert the Oracle function I exposed to a Teradata function (at least, not simple and clear), appart from converting it into a procedure and apply the method I suggested...

Because... Is there a way to do an intermediat step in a Teradata function, like declaring an auxiliar variable with the value of the firts IF-THEN-ELSE and then use it for the RETURN?

Juan
Senior Apprentice

Re: How to create a function similar to Oracle (or a procedure with a return value)?

The function I posted does both parts, the REGEXP_REPLACE replaces multiple occurrences of a character with a single.

You could do the same in Oracle.

Enthusiast

Re: How to create a function similar to Oracle (or a procedure with a return value)?

Ah ok! I didn't look at it well before. Sorry!

Just to finish about this issue, in case I needed... Is there a way to do an intermediat step in a Teradata function, like declaring an auxiliar variable with the value of an operation and then use it for the RETURN? For not doing always everything in one single big return sentence...

Juan
Senior Apprentice

Re: How to create a function similar to Oracle (or a procedure with a return value)?

No declared variables, no steps.

In fact I'm glad that no complex things are allowed, otherwise people would start writing stupid UDFs with loops/Selects, etc. and then try to use them on that 10 billion row table :-)

Enthusiast

Re: How to create a function similar to Oracle (or a procedure with a return value)?

Haha, that's true... I'm included sometimes ;)

As a curiosity, in the function you made, I've found a character that I can't introduce for delchar: the question mark as a literal. It doesn't get it as '?', '\?' or even chr(63)...

Juan
Senior Apprentice

Re: How to create a function similar to Oracle (or a procedure with a return value)?

Oops, of course, this is a regex meta character, forgot to escape it:

REPLACE FUNCTION F_QUITA_REPES (cCadena VARCHAR(255), delchar VARCHAR(1)) 
RETURNS VARCHAR(255)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
REGEXP_REPLACE(CASE
WHEN delchar = CHR(39)
THEN LTRIM(cCadena, delchar)
ELSE LTRIM(RTRIM(cCadena, delchar), delchar)
END, '\'||delchar||'+', delchar)
;