HomeOracle Using Scalar Functions for Retrieving Data
Using Scalar Functions for Retrieving Data
In this fifth part of a nine-part series on retrieving data from tables with the SELECT statement, you'll learn how to use scalar functions, and more. This article is excerpted from chapter three of the book Murach's Oracle SQL and PL/SQL, written by Joel Murach (Murach Publishing; ISBN: 9781890774509).
Figure 3-7 introduces you to scalar functions, which operate on a single value and return a single value. These functions work differently than the aggregate functions described in chapter 5 that are used to summarize data. For now, don’t worry about the details of how these functions work, because you’ll learn more about them in chapter 8. Instead, just focus on how they’re used in column specifications.
To code a function, you begin by entering its name followed by a set of parentheses. If the function requires one or more parameters, you enter them within the parentheses and separate them with commas. When you enter a parameter, you need to be sure it has the correct data type.
The first example in this figure shows how to use the SUBSTR function to extract the first character of the vendor_contact_first_name and vendor_contact_last_name columns. The first parameter of this function specifies the column name; the second parameter specifies the starting position; and the third parameter specifies the number of characters to return. The results of the two functions are then concatenated to form initials, as shown in the result set for this statement.
The second example shows how to use the TO_CHAR function. This function converts a column with a DATE or NUMBER data type to a character string. A common use for it is in concatenation operations, where all the data being concatenated must be string data. This function has two parameters. The first parameter, which specifies the column name, is required. The second parameter, which specifies a format mask for the column, is optional. In this example, a format mask of ‘MM/DD/YYYY’ is used to convert the payment_date column from a DATE type to a CHAR type. This format mask specifies that the date should be displayed with a two-digit month, followed by a forward slash, followed by a two-digit day, followed by another forward slash, followed by a four-digit year.
In the second example, the payment_date column for Invoice # P-0608 is NULL. Note that this causes the TO_CHAR function to return an empty string for the payment date.
The third example uses the SYSDATE function to return the current date. Since this function doesn’t accept any parameters, you don’t need to code any parentheses after the name of the function. In fact, if you do code parentheses after the name of the function, you will get an error when you execute the statement. In this example, the second column uses the SYSDATE function to return the current date, and the third column uses the SYSDATE function to calculate the number of days between the two dates. Here, the third column also uses the ROUND function to round the decimal value that’s returned by the calculation to an integer.
The fourth example shows how to use the MOD function to return the remainder of a division of two integers. Here, the second column contains an expression that returns the remainder of the division operation when the invoice-id column is divided by 10. In the result set, you can see the results for IDs 9 through 11 (the remainders are 9, 0, and 1).
A SELECT statement that uses the SUBSTR function
SELECT vendor_contact_first_name, vendor_contact_last_name, SUBSTR(vendor_contact_first_name, 1, 1) || SUBSTR(vendor_contact_last_name, 1, 1) AS initials FROM vendors
A SELECT statement that uses the TO_CHAR function
SELECT 'Invoice: # ' || invoice_number || ', dated ' || TO_CHAR(payment_date, 'MM/DD/YYYY') || ' for $' || TO_CHAR(payment_total) AS "Invoice Text" FROM invoices
A SELECT statement that uses the SYSDATE and ROUND functions
SELECT invoice_date, SYSDATE AS today, ROUND(SYSDATE - invoice_date) AS invoice_age_in_days FROM invoices
A SELECT statement that uses the MOD function
SELECT invoice_id, MOD(invoice_id, 10) AS Remainder FROM invoices ORDER BY invoice_id
A SQL statement can include a function. A function performs an operation and returns a value.
For more information on using functions, see chapter 8.
--------------------------------------------Figure 3-7 How to use scalar functions