Oracle built-in functions for SQL
One of the beneficial features about Oracle’s SQL is their great use of built-in functions.
If we look at all of the nice extensions to SQL such as DECODE and string translation functions, it should be apparent that BIFs are an integral part of the Oracle SQL environment. In fact, there are so many BIFs that entire application programs can be written in vanilla, chocolate, rasberry, and butter pecan SQL.
In short, understanding and using BIFs is a very integral part of Oracle tuning.
Oracle has many built-in functions and Oracle also allows you to define your own function and make it a part of your own SQL dialect. You can see all built-in Oracle functions with this query:
package_name = 'STANDARD';
Here are my notes on common SQL built-in functions:
- convert(char_to_convert, new_char_set, old_char_set)
- greatest(expression, expression...)
- initcap(char) string with initial capitals
- last_day(date)returns the last day of month in date
- least(expression, expression...)
- lower(char) lpad(char, n[,padchar])
- max([distinct] expr)
- min([distinct] expr)
- months_between(end_date, start_date)
- nvl(expression, value_if_null)
- replace(char, search_str[, replace_str])