# User-defined scalar functions (pl/SQL style)

XMLWordPrintable

#### Details

• Type: Explanation
• Status: Obsolete
• Affects Version/s: EXASolution 4.2.0, EXASolution 5.0, EXASOL 6.0.0, Exasol 6.1.0
• Fix Version/s: None
• Component/s:
• Labels:
None
• Explanation:
Hide

## Note: This solution is no longer maintained. For the most up-to-date information, please see our documentation portal: https://docs.exasol.com/sql/create_function.htm

---------------------------------------------------------------------------------------------------------------------------------------------

EXASolution does support scalar user-defined pl/SQL functions which compute a result value depending on their input parameters.
For more complex user defined functions use EXAPowerlytics

An execution of SQL statements inside those functions is not possible (contrary to scripts).

### Example for a simple function

```CREATE OR REPLACE FUNCTION mymax(a IN INT,b IN INT) RETURNS int
AS res INT;
BEGIN
IF (a>b) then
res:=a;
else
res:=b;
end IF;
RETURN res;
END;
/

SELECT mymax(123, 8892) FROM dual;
```

### Manual calculation of date-difference

```CREATE OR REPLACE FUNCTION examples.days_between(a IN DATE, b IN DATE) RETURNS INTEGER
IS
num INTEGER;
swap DATE;
BEGIN
num := 0;
-- Ensure that a<=b
IF( a>b ) then
swap := a;
a := b;
b := swap;
end IF;
while( a<b ) do
a := a+1;
num := num+1;
end while;
RETURN num;
END;
/

SELECT examples.days_between(sysdate, '2009-05-01') FROM dual;

```

Contrary to the built-in function days_between, this user-defined function always returns a positive integer.

Also note that functions that overload builtin functions need to be called using a fully-qualified identifier.

Furthermore you can also use EXASolution built-in scalar functions inside your function.

Show
Note: This solution is no longer maintained. For the most up-to-date information, please see our documentation portal:  https://docs.exasol.com/sql/create_function.htm --------------------------------------------------------------------------------------------------------------------------------------------- EXASolution does support scalar user-defined pl/SQL functions which compute a result value depending on their input parameters. For more complex user defined functions use EXAPowerlytics An execution of SQL statements inside those functions is not possible (contrary to scripts). Example for a simple function CREATE OR REPLACE FUNCTION mymax( a IN INT ,b IN INT ) RETURNS int AS res INT ; BEGIN IF ( a >b) then res:= a ; else res:=b; end IF ; RETURN res; END ; / SELECT mymax(123, 8892) FROM dual ; Manual calculation of date-difference CREATE OR REPLACE FUNCTION examples.days_between( a IN DATE , b IN DATE ) RETURNS INTEGER IS num INTEGER ; swap DATE ; BEGIN num := 0; -- Ensure that a <=b IF ( a >b ) then swap := a ; a := b; b := swap; end IF ; -- Repeatedly add days. while ( a <b ) do a := a +1; num := num+1; end while ; RETURN num; END ; / SELECT examples.days_between( sysdate , '2009-05-01' ) FROM dual ; Contrary to the built-in function days_between, this user-defined function always returns a positive integer. Also note that functions that overload builtin functions need to be called using a fully-qualified identifier. Furthermore you can also use EXASolution built-in scalar functions inside your function.
• Category 1:
SQL

#### People

• Assignee:
Captain EXASOL
Reporter:
Captain EXASOL