Do you remember what the two main categories of built-in functions are in Oracle? These categories are created based on how many rows these functions operate at a time. I am talking about the built-in, Oracle delivered functions, and not the ones you would create.
The two big categories are: Single Row Functions and Multiple Row Functions. The difference between the two are really the number of rows they operate on.
Today’s post will talk about Single Row Functions.
As the name suggest, the Single Row Functions operate on exactly one row. What does that mean?
If your query returns multiple rows, the functions operates on each row individually, one at a time. Let’s look at an example: Let’s say you want to convert the last_name column of the employees table into upper case for all the rows returned, then, you would use the upper() function, and this function would convert the last_name column, for each row, one at a time into upper case. If the last_name is stored in lower case, the function will
return the last name in upper case. Just to be clear, the data in the table is NOT changed, only the results that are displayed to the users are changed.
Within the single row functions category, there are other sub-categories, based on Oracle’s documentation. Here is a break down of these categories, you personally might group them differently:
-numeric
-character (2 types: some return character values, some return number values)
-character set
-datetime
-general comparison
-conversion
-large object
-collection
-hierarchical
-data mining
-xml
-json
-encoding and decoding
-NULL-related
-environment and identifier
Were you aware of all these sub-categories, or did you group them differently?
It is good to remember that the single row functions can appear in the following clauses of a SQL statement: SELECT, WHERE, START WITH and CONNECT BY, HAVING.
Some of the functions are foreign to me, in a sense that I do not use them regularly, or maybe not at all, but the good part is, I know of their existence. If I need to know how to use them, then I will look up the documentation.
In the following weeks, I will have a mini-series of blog posts dedicated to these functions! Let me know which one you want to learn more about, and which one is your favorite! I read every email!
