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
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.
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.
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.
You can use the
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.
You can use the
INITCAP() function to upper-case the first character in a string.
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,
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.
The SYSDATE function returns the current system date.
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