This preview has intentionally blurred sections. Sign up to view the full version.View Full Document
Unformatted text preview: Oracle Database 10g character functions Learning objective After completing this topic, you should be able to use case and character-manipulation functions in SQL statements. 1. Single-row functions Although certain aspects of the Oracle 10g Database are case and spacing insensitive, a common coding convention has been used throughout all aspects of this course. This convention uses lowercase characters for schema, role, user, and constraint names, for permissions, synonyms, and table names (with the exception of the DUAL table), and for column names shown in code. Uppercase characters are used for keywords and functions, for view, table, schema, and column names shown in text, for column aliases that are not shown in quotes, and for data dictionary views. The spacing convention requires one space after a comma and one space before and after operators that are not Oracle-specific, such as +, -, /, and <. There should be no space between an Oracle-specific keyword or operator and an opening bracket, a closing bracket and a comma, between the last part of a statement and the closing semicolon, or before a statement. String literals in single quotes are an exception to all convention rules provided. Please use this convention for all interactive parts of this course. In Oracle, functions are used to increase the effectiveness of a basic query block. They manipulate data by performing an action, sometimes using an argument, so as to return a result value. You can use functions to • change individual data items • convert column datatypes • manipulate output for groups of rows • set number and date formats for display • perform calculations on data Functions can manipulate data in a number of ways. For example, you can use a function to ensure that outputted data values appear in either upper- or lowercase. You can also use functions to join values together, or show the length of a data string as a numeric value. There are two types of functions: • single-row function • multiple-row function single-row function Single-row functions only operate on individual rows and return one result per row. multiple-row function Multiple-row functions can manipulate groups of rows, so there is one result for a group of rows. There are several types of single-row functions: • character • numeric • date • conversion • general character Character functions only accept non-numeric character input but can return both alphabetical and numeric values. numeric Numeric functions allow the input of numbers and return numeric values. date Date functions return values of the DATE datatype, such as ADD_MONTHS , NEXT_DAY , LAST_DAY , ROUND , and TRUNC . The only exception to this is the MONTHS_BETWEEN function, which returns a number value....
View Full Document
This note was uploaded on 12/22/2009 for the course DBST DBST 651 taught by Professor Yelena - ta during the Spring '09 term at MD University College.
- Spring '09
- Yelena - TA