Page 149
by Timothy Atwood
Day 5, "Using Functions, IF Statements, and Loops," demonstrates how to create functions, both within the code and stored. However, Oracle already comes complete with hundreds of excellent functions. With so many of these functions, it is unlikely that even the best Oracle guru will have them all memorized. Today's lesson covers the most frequently used functions. I highly recommend that you review Appendix B, "Oracle Functions Reference," and keep it near you while you work. Appendix B contains a complete listing of Oracle functions, their purpose, and their syntax.
Page 150
| TIP |
The best way to learn a programming language, whether it is a third-generation language or a database management language, is to always initially review all the commands and functions, their syntax, and their usage. Any time you need to use any of these, you can then refer to this chapter for the syntax and definition. Otherwise, you might miss some very powerful features, never knowing that they even exist. This methodology also helps you to easily understand and learn competing vendor's products and makes you a more valuable programmer. |
As with any database, you can use SQL within PL/SQL to take advantage of all the features of PL/SQL. Almost all the functions will work within PL/SQL except those functions that operate on rows such as MAX, MIN, or any other "grouping/summary" type functions, as well as special functions like DECODE and DUMP.
Tables 7.1 through 7.6 summarize Oracle's functions within the category types character functions, number functions, date functions, conversion functions, group functions, and miscellaneous functions.
Table 7.1. Character functions.
| Function Name | Function Description |
| ASCII | Returns the ASCII code of the character. |
| CHR | Returns a character when given its ASCII value. |
| CONCAT | Joins (concatenates) two strings together (same as using the || operator, which you might have noticed on Day 5 and Day 6, "Implementing Loops and GOTOs"). |
| INITCAP | Returns a string in which the first letter in each word is capitalized and all remaining characters are converted to lowercase. Does not affect any nonalphabetic characters. |
| INSTR | Returns the location of a string within another string. |
| INSTRB | Returns the location of a string within another string, but returns the value in bytes for a single-byte character system. |
Page 151
| Function Name | Function Description |
| LENGTH | Returns the length of a character string, including pads. Returns NULL if the value is NULL. |
| LENGTHB | Returns the length of a character string in bytes, except that the return value is in bytes for single-byte character sets. |
| LOWER | Returns the entire character string to lowercase. Does not affect any nonalphabetic characters. |
| LPAD | Pads a string on the left side with any string specified. |
| LTRIM | Trims character string on the left side with any character string specified. |
| NLS_INITCAP | Same as the INITCAP function except that it can use a different sort method as specified by NLSSORT. |
| NLS_LOWER | Same as the LOWER function except that it can use a different sort method as specified by NLSSORT. |
| NLS_UPPER | Same as the UPPER function except that it can use a different sort method as specified by NLSSORT. |
| NLSSORT | Changes the method of sorting the characters. Must be specified before any NLS function; otherwise, the default sort will be used. |
| REPLACE | Replaces every occurrence of one string with another string. |
| RPAD | Pads a string on the right side with any string specified. |
| RTRIM | Trims character string on the right side with any character string specified. |
| SOUNDEX | Returns the phonetic representation of a string. Useful for words that are spelled differently but sound alike. |
| SUBSTR | Returns a portion of a string from within a string. |
| SUBSTRB | Same as SUBSTR except the parameters are expressed in bytes instead of characters to handle single-byte character systems. |
| TRANSLATE | Same as REPLACE except operates at a character-level basis instead of a string-level basis. |
| UPPER | Returns the entire character string to uppercase. Does not affect any nonalphabetic characters. |
Page 152
Table 7.2. Number functions.
| Function Name | Function Description |
| ABS | Returns the absolute value of a number. |
| ACOS | Returns the arc (inverse) cosine of a number, expressed in radians. |
| ASIN | Returns the arc (inverse) sine of a number, expressed in radians. |
| ATAN | Returns the arc (inverse) tangent of a number (x), expressed in radians. |
| ATAN2 | Returns the arc (inverse) tangent of a number (y/x), expressed in radians. |
| CEIL | Returns the value representing the smallest integer, which is greater than or equal to a specified number. |
| COS | Returns the cosine of a number, expressed in radians. |
| COSH | Returns the hyperbolic cosine of a number, expressed in radians. |
| EXP | Returns the exponentiation of e raised to the power of some number, where e = 2.7182818... |
| FLOOR | Returns the value representing the largest integer, which is greater than or equal to a specified number. |
| LN | Returns the natural logarithm of some number x. |
| LOG | Returns the logarithm of some base x of some number y. |
| MOD | Returns the remainder of some number x divided by some number y. |
| POWER | Returns some number x to the power of some number y. |
| ROUND | Returns x rounded to y places. |
| SIGN | Determines if a number is negative, zero, or positive by the following rules: If x is negative, returns a value of _1. If x is zero, returns zero. If x is positive, returns a value of 1. |
| SIN | Returns the sine of some number x in radians. |
| SINH | Returns the hyperbolic sine of some number x in radians. |
| SQRT | Returns the square root of some number x. The value of x can't be an imaginary number—x must never be negative! |
| TAN | Returns the tangent of some number x in radians. |
| TANH | Returns the hyperbolic tangent of some number x in radians. |
| TRUNC | Returns some number x, truncated to y places. Does not round, just cuts off at the location specified. |