Lesson 10

This module explored many of Oracle's SQL extensions that are implemented as functions. The functions explored in this module were broken down by the datatype on which the function performs its tasks. You observed many character functions, such as SUBSTR and LENGTH. You learned the correct syntax of these functions. Working with numbers, you saw that there were many diverse functions from which to choose. You studied examples of the more common functions, such as ROUND and TRUNC.
Oracle dates implicitly include time. There are many diverse formats to use to display a date, even though all dates are stored in the database in a special numeric format. You examined complex combinations of date, number, and character functions in the exercises and quizzes of this module.

Oracle SQL, as part of the Oracle Database suite, boasts a myriad of extensions designed to enhance the standard SQL capabilities. These extensions, many of which are implemented as functions, offer developers an array of powerful tools to optimize, extend, and enrich database operations. Let's delve into some primary Oracle SQL extensions manifesting as functions:

1. Single-Row Functions: These functions operate on individual rows and return one result per row.
   • Character Functions: Oracle provides numerous character manipulation functions such as `INITCAP`, `SUBSTR`, `LPAD`, `RPAD`, and `TRANSLATE`.
   • Numeric Functions: Functions like `CEIL`, `FLOOR`, `POWER`, `ROUND`, and `TRUNC` offer advanced numeric operations beyond standard arithmetic.
   • Date Functions: Oracle has a rich set of date functions like `ADD_MONTHS`, `LAST_DAY`, `MONTHS_BETWEEN`, `NEXT_DAY`, and `SYSDATE`.
   • Conversion Functions: `TO_CHAR`, `TO_DATE`, `TO_NUMBER`, and `TO_TIMESTAMP` are essential for data type conversion operations.
2. Aggregate Functions: These functions operate on a set of rows, returning a single summarized result.
   • `SUM`, `AVG`, `MIN`, `MAX`: Used for basic statistical operations on specified columns.
   • `COUNT`: A fundamental function for quantifying rows based on specified criteria.
   • `LISTAGG`: A powerful function for aggregating data from multiple rows into a single string.
   • `STDDEV`, `VARIANCE`: Functions that provide insights into data dispersion and variability.
3. Analytical Functions: These are a set of functions that allow data analysis and transformation. Some notable ones include:
   • `ROW_NUMBER`, `RANK`, `DENSE_RANK`: Functions to assign a unique rank or number to rows within a result set.
   • `LAG`, `LEAD`: Allow users to retrieve the value from a previous or subsequent row without a self-join.
   • `FIRST_VALUE`, `LAST_VALUE`: Return the first or last value in a result set or window partition.
4. Model Functions:
   • `CV`, `PRESENTV`, `PREVIOUS`: These functions support the Model clause of a SELECT statement, allowing spreadsheet-like computations on database tables.
5. Object Reference Functions:
   • `REF`, `DEREF`: Utilized for manipulating REF values which are pointers to objects in an object-relational database.
6. Large Object (LOB) Functions: These cater to operations on large objects such as BLOBs, CLOBs, and BFILEs.
   • `DBMS_LOB`: A package that offers a multitude of functions and procedures to operate on LOB data types. Examples include `GETLENGTH`, `INSTR`, and `SUBSTR`.
7. XML Functions: Oracle provides a suite of functions for XML manipulation, including:
   • `XMLAGG`, `XMLCOMMENT`, `XMLCONCAT`, `XMLFOREST`, and `XMLSEQUENCE`.
8. Hierarchical Functions:
   • `CONNECT_BY_ROOT`, `SYS_CONNECT_BY_PATH`: Essential for operations on data organized hierarchically using the START WITH and CONNECT BY clauses.

Oracle SQL's extensions, particularly its functional repertoire, underscore Oracle's commitment to equip developers with comprehensive, robust, and diverse tools. Whether for data transformation, analytical operations, or niche manipulations like XML processing or LOB handling, Oracle ensures that its SQL developers are always a step ahead in the database management domain.

Returns a string that represents a substring of a source string. The returned substring is of a specified number of characters, beginning from a designated starting point, relative to either the beginning or end of the string.
SQL syntax

{SUBSTR | SUBSTRB | SUBSTR4}=(Source, m, n)

Function Parameters SUBSTR has the parameters:

Parameter	Description
Source	The string for which this function returns a substring. Value can be any supported character data types including CHAR, VARCHAR2, NCHAR, NVARCHAR2,CLOB, or NCLOB data types. Both TimesTen and Oracle Database data types are supported. If Source is a CHAR string, the result is a CHAR or VARCHAR2 string. If Source is a NCHAR string, the result is a NVARCHAR2 string. If Source is a LOB, the resultis the same LOB data type.
m	The position at which to begin the substring. If m is positive, the first character of the returned string is m characters from the beginning of thestring specified in char. Otherwise it is m characters from the end of the string. If ABS(m) is bigger than the length of the character string, a null value is returned.
n	The number of characters to be included in the substring. If n is omitted, all characters to the end of the string specified in char are returned. If n is less than 1 or if char, m or n is NULL, NULL is returned.

Description: SUBSTR calculates lengths using characters as defined by character set. SUBSTRB uses bytes instead of characters. SUBSTR4 uses UCS4 code points.
Examples:In the first five rows of employees, select the first three characters of last_name:

< Kin >
< Koc >
< De >
< Hun >
< Ern >

5 rows found.
In the first five rows of employees, select the last five characters of last_name:

SELECT FIRST 5 SUBSTR(last_name,-5,5) FROM employees;
 <NULL> 
< chhar >
< Haan >
< unold >
< Ernst >

5 rows found.

In this module, you learned how to:

1. List character (string) functions unique to Oracle or different in Oracle
2. Interpret the effect of number functions
3. Identify valid date-related date functions and Oracle's standard date format
4. Identify valid time-related date/time functions
5. Use DECODE and NVL to modify query results

In the next module you will review the concept of table joins. you will learn how Oracle implements outer joins and adds new functionality with special query formats.