Guide to Oracle SQL (Computer Science 4 All Version)


Single-Row 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 created 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 required 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.

Defining 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

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

Figure 4-1: Single Row SELECT Code
REGION_ID REGION_NAME                     LENGTH(REGION_NAME)
---------- ------------------------------ -------------------
1 Europe                                                    6
2 Americas                                                  8
3 Asia                                                      4
4 Middle East and Africa                                   22

Figure 4-2: Sample Single Row SELECT Output

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.

SELECT product_name, product_status FROM product_information WHERE LOWER(product_status) = 'in';

Figure 4-3: Single Row LOWER() Code

You can also force the data to conform to the expected literal value in this case 'IN'. This is handy when your data is mised case and you want to find a givven string regardless of case.

SELECT product_name, product_status FROM product_information WHERE UPPER(product_status) = 'IN';

Figure 4-5: Single Row UPPER() Code
PRODUCT_NAME             PRODUCT_STATUS
------------------------ --------------------
Inkjet C/8/HQ            IN

Figure 4-6: Sample Single Row UPPER() Output

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;

Figure 4-7: Single Row INITCAP() Code
INITCAP(FIRST_NAME)
--------------------
James

Figure 4-8: Sample Single Row INITCAP() Output

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.

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

Figure 4-7: Single Row LENGTH() Code
LENGTH('THISISASTRINGOFCHARACTERS')
-----------------------------------
                                 30

Figure 4-8: Sample Single Row LENGTH() Output

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.

SELECT CONCAT(1+2.14, ' approximates pi') FROM dual;

Figure 4-7: Single Row CONCAT() Code
CONCAT(1+2.14,'APPRO
--------------------
3.14 approximates pi

Figure 4-8: Sample Single Row CONCAT() Output
Creative Commons License This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License. Copyright © 2020 Jim Gerland