Single-Row Functions in Oracle SQL
Single-row functions in Oracle SQL operate on a single row and return one result per row. These functions can be used to manipulate data, perform calculations, and format results. Understanding these functions is essential for effectively querying and transforming data in Oracle databases.
Key Concepts
1. Character Functions
Character functions manipulate character data. They can perform operations such as converting case, trimming spaces, and concatenating strings.
Example: Using the UPPER function to convert a string to uppercase:
SELECT UPPER('hello world') FROM dual;
This query returns 'HELLO WORLD'.
2. Numeric Functions
Numeric functions perform mathematical operations on numeric data. They can be used to round numbers, find absolute values, and perform trigonometric calculations.
Example: Using the ROUND function to round a number to two decimal places:
SELECT ROUND(123.456, 2) FROM dual;
This query returns 123.46.
3. Date Functions
Date functions manipulate date and time data. They can be used to extract parts of a date, add or subtract time intervals, and format dates.
Example: Using the SYSDATE function to get the current date and time:
SELECT SYSDATE FROM dual;
This query returns the current date and time.
4. Conversion Functions
Conversion functions convert data from one type to another. They are essential for ensuring that data is in the correct format for operations or display.
Example: Using the TO_CHAR function to convert a date to a string:
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;
This query returns the current date as a string in 'YYYY-MM-DD' format.
5. General Functions
General functions perform various operations that do not fit into the other categories. They can be used for null handling, conditional evaluation, and more.
Example: Using the NVL function to replace null values:
SELECT NVL(commission, 0) FROM employees;
This query replaces null commission values with 0.
6. Conditional Functions
Conditional functions allow you to perform conditional logic within SQL queries. They can be used to return different values based on conditions.
Example: Using the DECODE function to perform conditional logic:
SELECT DECODE(department_id, 10, 'HR', 20, 'Finance', 'Other') FROM employees;
This query returns 'HR' for department 10, 'Finance' for department 20, and 'Other' for other departments.
7. Analytical Functions
Analytical functions perform calculations across a set of table rows. They are similar to aggregate functions but maintain the individual row details.
Example: Using the RANK function to rank employees by salary:
SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees;
This query ranks employees by their salary in descending order.
By mastering these single-row functions, you can effectively manipulate and analyze data in Oracle SQL, making your queries more powerful and flexible.