This content originally appeared on DEV Community and was authored by Aditya Pratap Bhuyan
Oracle SQL is a cornerstone in the data management landscape, offering robust functionalities that streamline database operations. One of its most valuable features is the wide array of built-in functions that enable users to perform complex data manipulations with ease. This article delves into the various types of built-in functions available in Oracle and demonstrates how they can be effectively used to optimize your database workflows.
1. Numeric Functions
Numeric functions in Oracle facilitate precise calculations and transformations on numerical data, essential for any analytical tasks.
-
ABS: Returns the absolute value of a number.
- Example:
ABS(-15)returns15.
- Example:
-
CEIL: Rounds a number up to the nearest integer.
- Example:
CEIL(9.2)returns10.
- Example:
-
FLOOR: Rounds a number down to the nearest integer.
- Example:
FLOOR(9.8)returns9.
- Example:
-
ROUND: Rounds a number to a specified number of decimal places.
- Example:
ROUND(15.193, 2)returns15.19.
- Example:
-
POWER: Computes the power of a number raised to another.
- Example:
POWER(2, 3)returns8.
- Example:
These functions are particularly useful for data analysts who require accuracy in their numerical data processing and reporting.
2. String Functions
Oracle's string functions help manipulate textual data, an indispensable capability for formatting and processing strings.
-
CONCAT: Joins two or more strings.
- Example:
CONCAT('Hello', ' World')returns'Hello World'.
- Example:
-
LENGTH: Measures the length of a string.
- Example:
LENGTH('Hello')returns5.
- Example:
-
LOWER and UPPER: Converts strings to lower or upper case.
- Example:
UPPER('hello')returns'HELLO'.
- Example:
-
SUBSTR: Extracts a substring from a string.
- Example:
SUBSTR('Hello World', 7, 5)returns'World'.
- Example:
-
TRIM: Removes specified prefixes or suffixes from a string.
- Example:
TRIM('H' FROM 'Hello')returns'ello'.
- Example:
These functions are crucial for preparing data for reports, ensuring that textual data is in the required format.
3. Date Functions
Handling date and time data efficiently is crucial for many database tasks, and Oracle provides powerful functions to manage such data.
-
SYSDATE: Fetches the current system date and time.
- Example: Selecting
SYSDATEfrom a dual table gives the current date.
- Example: Selecting
-
ADD_MONTHS: Adds a specified number of months to a date.
- Example:
ADD_MONTHS('01-JAN-2020', 12)returns'01-JAN-2021'.
- Example:
-
LAST_DAY: Retrieves the last day of the month for a given date.
- Example:
LAST_DAY('15-FEB-2020')returns'29-FEB-2020'.
- Example:
-
MONTHS_BETWEEN: Calculates the months between two dates.
- Example:
MONTHS_BETWEEN('01-JAN-2021', '01-MAR-2020')returns10.
- Example:
-
TO_DATE: Converts a string to a date.
- Example:
TO_DATE('2020/01/01', 'YYYY/MM/DD')returns a date object for January 1, 2020.
- Example:
These functions are vital for applications where date and time calculations are frequent, such as scheduling and forecasting.
4. Conversion Functions
Oracle SQL's conversion functions ensure data from different sources and formats can be standardized into a consistent format for processing and analysis.
-
TO_CHAR: Converts a number or date to a string.
- Example:
TO_CHAR(123456, 'FM999,999')returns'123,456'.
- Example:
-
TO_NUMBER: Converts a string to a number.
- Example:
TO_NUMBER('123456')returns123456.
- Example:
- TO_DATE: Already discussed above under date functions, critical for data conversions between strings and dates.
5. Analytic Functions
Oracle's analytic functions offer advanced capabilities for statistical and advanced analysis directly within SQL queries.
-
RANK: Assigns a rank to each row within a partition of a result set.
- Example:
RANK() OVER (ORDER BY score DESC)assigns a rank to students by their scores.
- Example:
-
DENSE_RANK: Similar to RANK but assigns ranks without gaps.
- Example:
DENSE_RANK() OVER (ORDER BY score DESC)would rank scores without gaps.
- Example:
-
ROW_NUMBER: Provides a unique row number to each row, ordered by the specified column(s).
- Example:
ROW_NUMBER() OVER (ORDER BY last_name)assigns a unique number to each name in alphabetical order.
- Example:
These functions are crucial for detailed data analysis and reporting, providing insights into data trends and distributions.
Conclusion
Oracle’s built-in functions are integral for efficient data management, offering a sophisticated toolkit for data manipulation, analysis, and presentation. By leveraging these functions, database professionals can enhance their productivity, ensure data integrity, and unlock deep insights from their organizational data.
This content originally appeared on DEV Community and was authored by Aditya Pratap Bhuyan
Aditya Pratap Bhuyan | Sciencx (2024-09-18T04:06:35+00:00) Mastering Built-in Functions in Oracle SQL. Retrieved from https://www.scien.cx/2024/09/18/mastering-built-in-functions-in-oracle-sql-2/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.