Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Extend Your SQL With User-Defined Functions in OpenEdge 11.7

DZone's Guide to

Extend Your SQL With User-Defined Functions in OpenEdge 11.7

Using user-defined functions in OpenEdge can offer you significant benefits. Read on for some insight and a quick example.

· Database Zone
Free Resource

Learn how to create flexible schemas in a relational database using SQL for JSON.

With user-defined functions, OpenEdge database developers can extend their programming to validate business logic, use less bandwidth, and more.

OpenEdge SQL 11.7 allows you as a database developer to create your own routines. These routines, called user-defined functions (UDF), can accept parameters, perform custom actions, and return results. These are required especially when it comes to business logic, complex algorithmic calculations, and custom actions. They also help reduce network traffic.

UDFs help the user extend the SQL functionality. You can write UDFs in Java to provide functionality that is not available in SQL or SQL built-in functions by default.

UDFs can be added in an SQL statement to a SQL function or to an expression. For example, you can use the UDFs in the following cases:

  • The select list of a SELECT statement
  • The condition of a WHERE clause
  • ON CLAUSE, ORDER BY, and GROUP BY clauses
  • The VALUES clause of an INSERT statement
  • The SET clause of an UPDATE and DELETE statement

With UDFs, you can express your business logic and execute the business logic in the SQL engine, from where it can be called in your SQL queries.

Prerequisites

You must create UDFs as top-level functions before they can be used within an SQL statement. To use a user function in an SQL expression, you must own or have EXECUTE privileges on the user function.

Benefits of UDFs

  • Extends programming richness via user code. Your database can now do more than only process data. With UDFs, databases can now also validate business logic and perform custom actions.
  • Optimizes network traffic. UDFs allow data manipulation such that you do not need a lot of interaction with the database and you can optimize your results based on your business logic within the database. This reduces network bandwidth utilization.
  • Reuse code, which can be called akin to stored procedures with business logic.

Limitations of UDFs

  • When called in SQL queries, UDFs are executed only in a single thread. They follow a serial execution plan. Hence, if the business logic inside UDFs would work better in a multithreaded environment, it would be better to not implement it as a user-defined function since it would lead to overall performance degradation.
  • UDFs cannot change database state, so there might be usage constraints if you are attempting to change the database information inside your business logic.
  • The OpenEdge 11.7 release only supports scalar UDF.

When to Choose a UDF?

  • When you want a single value as the result, for example, the total cost of a business trip, total project cost in man-days, or the birth date of a person.
  • The return type can be any valid SQL type except arrays and lobs.

Example of an OpenEdge SQL User-Defined Function

Now that you know the benefits and limitations of a UDF, let’s walk through an example. We’ll create a function called days_between and see how to use it in a SQL select statement. This UDF accepts two input parameters named shipdate and orderdate, calculates the ifference between them, and returns a bigint value as output from the UDF.

days_between (date, date): This function provides the difference between two dates.
OpenEdge SQL has a similar Months_Between scalar function that gives you days_between functionality.
----
create function days_between(shipdate date, orderdate date)
return bigint
import
import java.util.Date;

begin
    try {
        long diff = shipdate.getTime() - orderdate.getTime();
        return diff / (24 * 60 * 60 * 1000);
    } catch (Exception e) {
        return -1L;
    }
end
----
select ordernum, pub.order.custnum, substr(Name,1,20), substr(Address,1,20), Phone,
days_between(shipdate, pub.order.orderdate) as DAYS_DELAYED
from pub.order inner join pub.Customer
on pub.customer.custnum=pub.order.custnum
where days_between(shipdate, pub.order.orderdate) > 10
order by days_between(shipdate, pub.order.orderdate) DESC;
Ordernum CustNum substr(Name,1,20) substr(Address,1,20) Phone DAYS_DELAYED
----
174 64 Hamahakkimies Ky Tainionkoskenkatu 20 (50) 374 3131 325
157 42 Stall-turn Flying Hurricane Lane 0702 258 133 260

Save Time and Bandwidth

With user-defined functions in OpenEdge 11.7, you can extend your SQL functionality to save time, gain capabilities, and reduce bandwidth use. You can read more about UDFs in our documentation, and don’t forget to check out everything that’s new in OpenEdge 11.7.

Create flexible schemas using dynamic columns for semi-structured data. Learn how.

Topics:
database ,udf ,sql ,openedge

Published at DZone with permission of Jyothi Sundaragiri, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}