Guide to Oracle SQL (Buffalo State University Version)

Single-Row and User Defined Functions

Most programming languages, such as C++, Java, or PHP, have functions or methods. There are two (2) general types of functions: User created, which you yourself create and then called FROM your program, and system defined where you added an include or import or require statement to your program to make your functions available to your program and then referenced the function that you wanted. This requires that you know the name of the function file you need to include so you could use the functions. In SQL there are no header files and the function names and what they are used for are straightforward.

Oracle SQL 12c provides a great deal of SQL functions that help developers. Oracle also has a programming language extension, PL/SQL, that uses SQL to create functions, procedures and packages.

There is an important distinction between the system functions that come with SQL packages and the ones created by third-party SQL developers. User Defined SQL functions are created using PL/SQL or T-SQL (depending on which RDBMS you are using Oracle or MS SQL) and are "owned" by different schemas and you may have to GRANT permissions to use them.

Functions

A function is a program written to optionally accept input parameters, perform an operation, or return a single value. A function returns only one value per execution. Functions take zeero (0) or more parameters and return a value of a certain data type. The parameters sent to the function can be from different data types.

Depending on the function, some of the parameters are mandatory and some are optional. What you want to accomplish will often determine what function you use and how you use it. Functions may be stand-alone or nested inside other functions to achieve the desired result. SQL functions are broadly divided into those that calculate and return a value for every row in a data set and those that return a single aggregated value for all rows.

Single Row Functions

Single row functions return one value for each row as a result set.

SELECT region_id, region_name, length(region_name)
FROM regions;

REGION_ID REGION_NAME                     LENGTH(REGION_NAME)
---------- ------------------------------ -------------------
1 Europe                                                    6
2 Americas                                                  8
3 Asia                                                      4
4 Middle East and Africa                                   22

Figure 4-1: Single Row SELECT Code

Single Row Character Functions

Character data defined as VARCHAR2 can hold data such as letters, numbers and other characters. Manipulating this data in SQL is often similar to other languages such as C/C++ and Java, and is many times easier to do since you don't have to worry about data structures.

The `LOWER()` and `UPPER()` Functions

You can use the LOWER() and UPPER() functions to make all the characters of a string lowercase or uppercase. This is handy when your data is mixed case and you want to find a given string regardless of case.

SELECT product_name, product_status
FROM product_information
WHERE LOWER(product_status) = 'in';
  
PRODUCT_NAME             PRODUCT_STATUS
------------------------ --------------------
Inkjet C/8/HQ            IN

Figure 4-2: Single Row LOWER() Code

SELECT product_name, product_status
FROM product_information
WHERE UPPER(product_status) = 'IN';
  
PRODUCT_NAME             PRODUCT_STATUS
------------------------ --------------------
Inkjet C/8/HQ            IN

Figure 4-3: Single Row UPPER() Code

The `INITCAP()` Function

You can use the INITCAP() function to upper-case the first character in a string.

SELECT INITCAP(first_name)
FROM employees
WHERE employee_id = 206;
  
INITCAP(FIRST_NAME)
--------------------
James

Figure 4-4: Single Row INITCAP() Code

The `LENGTH()` Function

Often it is necessary to know the length of a string before performing an update or insert on a table. What if the string is too long for the destination column? You can use the LENGTH() function to determine the length of the character string. The DUAL table is a system table that contains one (1) record wit one (1) field, DUMMY(VARCHAR2(1)).

SELECT LENGTH('this is a string of characters')
FROM dual;
  
LENGTH('THISISASTRINGOFCHARACTERS')
-----------------------------------
                                 30

Figure 4-5: Single Row LENGTH() Code

The `CONCAT()` Function

Earlier we looked at the concatenation operator '||'. You can use the CONCAT() function to do the same thing. Whether you use the operator or the function will depend on what you want to accomplish. The CONCAT() function takes two parameters representing the string literals, character column values, or expressions resulting in character values. Two (2) vertical bars (||) can also be used to concatenate data.

SELECT CONCAT(1+2.14, ' approximates pi') AS PI
FROM dual;
  
PI
--------------------
3.14 approximates pi

Figure 4-6: Sample Single Row CONCAT()

The `SYSDATE()` Function

The SYSDATE function returns the current system date.

SELECT (SYSDATE)
FROM dual;

SYSDATE
---------
21-JAN-21

Figure 4-7: The SYSDATE Function

User Defined Functions

Oracle SQL also allows developers to create their own functions using the CREATE OR REPLACE statement. Quite often there is a need to produce the same output for multiple reports. Such as a formatted employee name or a phone number. A User Defined function is a handy means of writing the code once and being able to use it in multiple SELECT statements.

CREATE OR REPLACE FUNCTION fullName (firstName IN VARCHAR2, lastName IN VARCHAR2)
  RETURN VARCHAR2
  IS formattedName VARCHAR(50);
  BEGIN     
     SELECT lastName || ', ' || firstName INTO formattedName FROM dual;       
  RETURN formattedName;
  END fullName;
  
SELECT fullName(first_name, last_name) AS "Full Name" FROM employees;

Full Name        
--------------
Oates, John
Hall, Darryl
Hall, Sarah
Felix, Allison
Jones, LoLo
Lindgren, Gerry
Scott, Steve
Wertz, Charlie
Taylor, James
Taylor, Livingston

10 rows selected.

Figure 4-8: A User Defined fullName Function

CREATE OR REPLACE FUNCTION formatPhone (in_phone IN VARCHAR2)
  RETURN VARCHAR2
  IS formattedPhone VARCHAR(50);
  BEGIN    
    SELECT '(' || SUBSTR(in_phone, 0, 3) || ') ' || SUBSTR(in_phone, 5) INTO formattedPhone FROM dual;
  RETURN formattedPhone;
  END formatPhone;
  
COLUMN fn FORMAT A50
COLUMN fn HEADING "Faculty Name"
COLUMN p FORMAT A25
COLUMN p HEADING "Office Phone"
SELECT fullName(firstname, lastname) AS fn, formatPhone(officephone) AS p FROM faculty;

Faculty Name                                       Office Phone             
-------------------------------------------------- -------------------------
Teddon, Don                                        (516) 689-1245           
SMITH, Steve                                       (516) 825-7913           
Thomas, Becky                                      (516) 826-6501           
Baryshinokov, Mikhail                              (516) 802-3212           
Mitchell, Arthur                                   (516) 798-0023           
Alonzo, Alicia                                     (516) 895-3201           
Fonteyn, Dame Margot                               (516) 826-6502           
Tudor, Antony                                      (516) 802-3213           
Balanchine, George                                 (516) 798-0022           
Graham, Martha                                     (516) 895-3201           
Gerland, James                                     (716) 123-4567           

11 rows selected.

Figure 4-9: A User Defined formatPhone Function

Guide to Oracle SQL (Buffalo State University Version)

Single-Row and User Defined Functions

Functions

Single Row Functions

Single Row Character Functions

The LOWER() and UPPER() Functions

The INITCAP() Function

The LENGTH() Function

The CONCAT() Function

The SYSDATE() Function