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
SELECT
CodeSingle 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
LOWER()
CodeSELECT product_name, product_status FROM product_information WHERE UPPER(product_status) = 'IN'; PRODUCT_NAME PRODUCT_STATUS ------------------------ -------------------- Inkjet C/8/HQ IN
UPPER()
CodeThe 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
INITCAP()
CodeThe 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
LENGTH()
CodeThe 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
CONCAT()
The SYSDATE()
Function
The SYSDATE function returns the current system date.
SELECT (SYSDATE) FROM dual; SYSDATE --------- 21-JAN-21
SYSDATE
FunctionUser 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.
fullName
FunctionCREATE 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.
formatPhone
Function