User-Defined Functions 0Oracle server software - Oracle DBA

Even though many functions come prewritten and packaged with the default installation of the Oracle software, sometimes you need some functionality that cannot be provided by those built-in functions. Oracle's programming language, PL/SQL, which stands for Programming Language SQL, can come to the rescue.

The advanced techniques on how functions, procedures, and packages are constructed and used are beyond the scope of this book. Here, you'll get an introduction to user-defined functions, including a look at how you could write a custom function that's available to all database users.

User-Defined Function A function that is written by an analyst, user, or database administrator and does not come as part of the default installation of the Oracle server software.

Using PL/SQL, a database analyst, database user, or database administrator can construct a user-defined function. A user-defined function has the same characteristics as a built-in function. It will take zero, one, or more values and return a single value as its result. Functions in Oracle, whether they are built-in or written by a developer or DBA, are often known as stored functions, since the source code and the compiled code are both stored in the database.

Stored Function A sequence of PL/SQL variable declarations and statements that can be called as a unit, passing zero or more arguments and returning a single value of a specified datatype. Built-in stored functions are created when the database software is installed. Customized or user-defined functions are defined by application developers or DBAs.

As an example, let's once again consider Scott's burgeoning widget company. Since the company is still small, Scott must perform the duties of both an application developer and a DBA. The HR department appears to frequently run queries that combine the employee name, job, and department into a formatted string for display on both web pages and corporate documents. To standardize the format of this string throughout the organization, Scott wrote a function called FORMAT_EMP that can be used by any department to display the employee name, job, and department, as follows:

Scott creates his stored function like this:

The first line of this command will create the function if it doesn't exist or replace it if it already exists. The next three lines define what kinds of values are going to be provided as input to the function, as well as what kind of value will be returned. In this example, Scott will provide the FORMAT_EMP function with a number and two strings, and he expects a string to be returned. He needs to create the function only once. By default, only the user who created the function can use it.

Line 6 declares a local variable called concat_rslt, which will temporarily hold the formatted string result. In a stored procedure or function, all of the actual processing occurs between the begin and the end keywords. In lines 8 to 11, the variable concat_rslt is assigned the formatted value using some of the Oracle built-in functions. Finally, in line 12, the function returns the result to the calling program, which, in this case, is a SQL statement similar to the following:

Note that the names you give for the parameters in the function need not be the same as the names of the columns in the table you're using. In fact, you could use this function just as well with some values that aren't even in a table:

Notice how you can use objects such as stored functions for standardization within an organization. An Accounting department employee does not need to remember how to format the employee information, because the formatting is kept in a common location via the stored function.

Scott can grant rights for other departments to use this function also. As an added bonus for the DBA, only a single copy of this function is stored in the shared pool for use by an unlimited number of users. This reduces the overall memory requirements for the database and can improve the response time for a query.

Shared Pool An area of memory within the total amount of memory allocated for the Oracle database that can hold recently executed SQL statements, PL/SQL procedures and packages, as well as cached information from the system tables.

Note It's important for the DBA to keep track of how many stored procedures and functions are running during the course of a business day, because there are memory and performance implications for the objects that share space in the database's shared pool. If there are too many other SQL statements and frequent accesses to database control structures, then the stored functions and procedures may be temporarily removed from the shared pool, thus affecting the response time the next time the user calls the stored function or procedure because it must be reread from disk.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle DBA Topics