Tuesday, November 18, 2008

Built-In Functions In SQL

Name



Type



Syntax



Returns



ABS

Number

ABS(n)

Absolute value of n.

ADD_MONTHS

Date

ADD_MONTHS(a,b)

Date a plus b months.

ASCII

Character

ASCII(c)

Decimal representation of c.

AVG

Group

AVG(DISTINCT|ALL n)

Average value of n. ALL is default.

CEIL

Number

CEIL(n)

Smallest integer equal to or greater than n.

CHARTOROWID

Conversion

CHARTOROWID(c)

Converts character to rowid data type.

CHR

Character

CHR(n)

Character having binary equivalent to n.

CONCAT

Character

CONCAT(1,2)

Character 1 concatenated with character 2.

CONVERT

Conversion

CONVERT(a,

Converts character



dest_c [,source_c])

string a from one character set to another. The source source_c to the destination character set dest_c.

COS

Number

COS(n)

Cosine of n.

COSH

Number

COSH(n)

Hyperbolic cosine of n.

COUNT

Group

COUNT(DISTINCT|ALL e)

Number of rows in a query. ALL is default. e can be represented as * to indicate all columns.

EXP

Number

EXP(n)

e raised to the nth power.

FLOOR

Number

FLOOR(n)

Largest integer equal to or less than n.

GREATEST

Other

GREATEST(e [,e]...)

The greatest of the list of expressions e.

HEXTORAW

Conversion

HEXTORAW(c)

Converts hexadecimal character c to raw.

INITCAP

Character

INITCAP(c)

c with the first letter of each word in uppercase.

INSTR

Character

INSTR

Searches 1 with



(1, 2 [, n [, m]])

nth character for mth occurrence of 2 and returns the position of the occurrence.

INSTRB

Character

INSTRB(1,2[,n[,m]])

Same as INSTR except numeric parameters are in terms of bytes.

LAST_DAY

Dae

LAST_DAY(a)

Last day of the month (date) containing a.

LEAST

Other

LEAST(e [,e]...)

The least of the list of expressions e.

LENGTH

Character

LENGTH(c)

Number of characters in c. If c is a fixed-length data type (char), all trailing blanks are included.

LENGTHB

Character

LENGTHB(c)

Same as LENGTH except in bytes.

LN

Number

LN(n)

Natural logarithm if n, where n > 0.

LOG

Number

LOG(b,n)

Logarithm, base b, of n.

LOWER

Character

LOWER(c)

c with all letters in lowercase.

LPAD

Character

LPAD(1,n [,2])

Character 1 left padded to length of n. If character 2 is not omitted, use as a pattern instead of blanks.

LTRIM

Character

LTRIM(c [,set])

Removed characters from the left of c. If set s defined, remove initial characters up to the first character not in set.

MAX

Other

MAX(DISTINCT|ALL e)

Maximum of expression e. ALL is default.

MIN

Other

MIN(DISTINCT|ALL e)

Minimum of expression e. ALL is default.

MOD

Number

MOD(r,n)

Remainder of r divided by n.

MONTHS_BETWEEN

Date

MONTHS_BETWEEN(a,b)

Number of days between dates a and b.

NEW_TIME

Date

NEW_TIME(a, z1, z2)

Date and time in time zone z2 when date and time in time zone z1 are a).

NEXT_DAY

Date

NEXT_DAY(a, c)

Date of first weekday identified by c that is later than date a.

NLSSORT

Character

NLSSORT((c [,parm])

String of bytes to sort c.

NLS_INITCAP

Character

NLS_INITCAP

c with the first



(c [,parm])

letter of each word in uppercase. parm has the form of NLS_SORT = s where s is a linguistic sort or binary.

NLS_LOWER

Character

NLS_LOWER(c [,parm])

c with all letters lowercase. See parm above.

NLS_UPPER

Character

NLS_UPPER(c [,parm])

c with all letters uppercase. See parm above.

NVL

Other

NVL(e1, e2)

If e1 is null, returns e2. If e1 is not null, returns e1.

POWER

Number

POWER(m,n)

m raised to the nth power.

RAWTOHEX

Conversion

RAWTOHEX(raw)

Converts raw value to its hexadecimal equivalent.

REPLACE

Character

REPLACE(c, s1 [, r2])

Replace each occurrence of string s1 in c with r2. If r2 is omitted then all occurrences of s1 are removed.

ROUND

Date

ROUND(n [,f])

Date rounded to format model f. If f is omitted, n will be rounded to nearest day.

ROUND

Number

ROUND(n[,m])

n rounded to m places right of decimal point. If m is omitted, to 0 places.

ROWIDTOCHAR

Conversion

ROWIDTOCHAR(rowid)

Converts rowid to varchar2 format with length of 18.

RPAD

Character

RPAD(1, n [, 2])

1 right-padded to length of n with 2.

RTRIM

Character

RTRIM(c [, s])

c with characters removed after last character not in set s. If s is omitted, set defaulted to ''.

SIGN

Number

SIGN(n)

-1 if n <>n = 0, 1 if n > 0.

SIN

Number

SIN(n)

Sine of n.

SINH

Number

SINH(n)

Hyperbolic sine of n.

SOUNDEX

Character

SOUNDEX(c)

A string with phonetic representation of c.

SUBSTR

Character

SUBSTR(c, m [,n])

A portion of c beginning at character number m for n characters. If m is negative, Oracle counts backward from the end of c. If n is omitted, all characters are returned to the end of c.

SUBSTRB

Character

SUBSTRB(c, m [,n])

The same as SUBSTR except m and n are number of bytes.

SQRT

Number

SQRT(n)

Square root of n.

STDDEV

Group

STDDEV(DISTINCT|ALL n)

Standard deviation of number n.

SUM

Group

SUM(DISTINCT|ALL n)

Sum of numbers n.

SYSDATE

Date

SYSDATE

Current date and time.

TAN

Number

TAN(n)

Tangent of n.

TANH

Number

TANH(n)

Hyperbolic tangent of n.

TO_CHAR

Conversion

TO_CHAR

Converts d date to



(d [,f [,parm])

varchar2 data type with format f and nls_date_language of parm.

TO_CHAR

Conversion

TO_CHAR

Converts n number



(n [,f [,parm])

data type to a varchar2 equivalent and number format element parm.

TO_DATE

Conversion

TO_DATE

Converts varchar2



(c [, f [, parm])

data type c to date data type with format f and nls date format element parm.

TO_MULTI_BYTE

Conversion

TO_MULTI_BYTE(c)

Converts c to their corresponding multibyte equivalent.

TO_NUMBER

Conversion

TO_NUMBER

Converts character



(c [,f [, parm]])

c to a number using format f and nls number format element parm.

TO_SINGLE_BYTE

Conversion

TO_SINGLE_BYTE(c)

Converts multibyte character c to its single byte equivalent.

TRANSLATE

Character

TRANSLATE(c, f, t)

c with each occurrences in f with each corresponding character in t.

TRUNC

Date

TRUNC(c [,f])

c with time portion truncated to format f.

TRUNC

Number

TRUNC(n[,m])

n truncated to m decimal places. If m is omitted, to 0 places.

UID

Other

UID

An integer that uniquely identifies the user.

USER

Other

USER

Current user as a varchar2.

UPPER

Character

UPPER(c)

c with all letters in uppercase.

VARIANCE

Group

VARIANCE

Variance of number



(DISTINCT|ALL n)

n.

VSIZE

Other

VSIZE(e)

Number of bytes from the internal representation of e.

What is RDBMS?

In recent years, database management systems (DBMS) have established themselves as the primary means of data storage for information systems ranging from large commercial transaction processing applications to PC-based desktop applications. At the heart of most of today's information systems is a relational database management system (RDBMS). RDBMSs have been the workhorse for data management operations for over a decade and continue to evolve and mature, providing sophisticated storage, retrieval, and distribution functions to enterprise-wide data processing and information management systems. Compared to the file systems, relational database management systems provide organizations with the capability to easily integrate and leverage the massive amounts of operational data into meaningful information systems. The evolution of high-powered database engines such as Oracle7 has fostered the development of advanced "enabling" technologies including client/server, data warehousing, and online analytical processing, all of which comprise the core of today's state-of-the-art information management systems.

Examine the components of the term relational database management system. First, a database is an integrated collection of related data. Given a specific data item, the structure of a database facilitates the access to data related to it, such as a student and all of his registered courses or an employee and his dependents. Next, a relational database is a type of database based in the relational model; non-relational databases commonly use a hierarchical, network, or object-oriented model as their basis. Finally, a relational database management system is the software that manages a relational database. These systems come in several varieties, ranging from single-user desktop systems to full-featured, global, enterprise-wide systems, such as Oracle7.

This blog discusses the basic elements of a relational database management system, the relational database, and the software systems that manage it. Also included is a discussion of nonprocedural data access. If you are a new user to relational database technology, you'll have to change your thinking somewhat when it comes to referencing data nonprocedurally.

The Relational Database Model

Most of the database management systems used by commercial applications today are based on one of three basic models: the hierarchical model, the network model, or the relational model. The following sections describe the various differences and similarities of the models.

Hierarchical and Network Models


The first commercially available database management systems were of the CODASYL type, and many of them are still in use with mainframe-based, COBOL applications. Both network and hierarchical databases are quite complex in that they rely on the use of permanent internal pointers to relate records to each other. For example, in an accounts payable application, a vendor record might contain a physical pointer in its record structure that points to purchase order records. Each purchase order record in turn contains pointers to purchase order line item records.

The process of inserting, updating, and deleting records using these types of databases requires synchronization of the pointers, a task that must be performed by the application. As you might imagine, this pointer maintenance requires a significant amount of application code (usually written in COBOL) that at times can be quite cumbersome.

Elements of the Relational Model


Relational databases rely on the actual attribute values as opposed to internal pointers to link records. Instead of using an internal pointer from the vendor record to purchase order records, you would link the purchase order record to the vendor record using a common attribute from each record, such as the vendor identification number.

Although the concepts of academic theory underlying the relational model are somewhat complex, you should be familiar with are some basic concepts and terminology. Essentially, there are three basic components of the relational model: relational data structures, constraints that govern the organization of the data structures, and operations that are performed on the data structures.

Relational Data Structures

The relational model supports a single, "logical" structure called a relation, a two-dimensional data structure commonly called a table in the "physical" database. Attributes represent the atomic data elements that are related by the relation. For example, the Customer relation might contain such attributes about a customer as the customer number, customer name, region, credit status, and so on.


Key Values and Referential Integrity


Attributes are grouped with other attributes based on their dependency on a primary key value. A primary key is an attribute or group of attributes that uniquely identifies a row in a table. A table has only one primary key, and as a rule, every table has one. Because primary key values are used as identifiers, they cannot be null. Using the conventional notation for relations, an attribute is underlined to indicate that it is the primary key of the relation. If a primary key consists of several attributes, each attribute is underlined.

You can have additional attributes in a relation with values that you define as unique to the relation. Unlike primary keys, unique keys can contain null values. In practice, unique keys are used to prevent duplication in the table rather than identify rows. Consider a relation that contains the attribute, United States Social Security Number (SSN). In some rows, this attribute may be null in since not every person has a SSN; however for a row that contains a non-null value for the SSN attribute, the value must be unique to the relation.

Linking one relation to another typically involves an attribute that is common to both relations. The common attributes are usually a primary key from one table and a foreign key from the other. Referential integrity rules dictate that foreign key values in one relation reference the primary key values in another relation. Foreign keys might also reference the primary key of the same relation. Figure illustrates two foreign key relationships.



Oracle and Client/Server


Oracle Corporation's reputation as a database company is firmly established in its full-featured, high-performance RDBMS server. With the database as the cornerstone of its product line, Oracle has evolved into more than just a database company, complementing its RDBMS server with a rich offering of well-integrated products that are designed specifically for distributed processing and client/server applications. As Oracle's database server has evolved to support large-scale enterprise systems for transaction processing and decision support, so too have its other products, to the extent that Oracle can provide a complete solution for client/server application development and deployment. This chapter presents an overview of client/server database systems and the Oracle product architectures that support their implementation.

An Overview of Client/Server Computing

The premise of client/server computing is to distribute the execution of a task among multiple processors in a network. Each processor is dedicated to a specific, focused set of subtasks that it performs best, and the end result is increased overall efficiency and effectiveness of the system as a whole. Splitting the execution of tasks between processors is done through a protocol of service requests; one processor, the client, requests a service from another processor, the server. The most prevalent implementation of client/server processing involves separating the user interface portion of an application from the data access portion.

On the client, or front end, of the typical client/server configuration is a user workstation operating with a Graphical User Interface (GUI) platform, usually Microsoft Windows, Macintosh, or Motif. At the back end of the configuration is a database server, often managed by a UNIX, Netware, Windows NT, or VMS operating system.

Client/server architecture also takes the form of a server-to-server configuration. In this arrangement, one server plays the role of a client, requesting database services from another server. Multiple database servers can look like a single logical database, providing transparent access to data that is spread around the network.

Designing an efficient client/server application is somewhat of a balancing act, the goal of which is to evenly distribute execution of tasks among processors while making optimal use of available resources. Given the increased complexity and processing power required to manage a graphical user interface (GUI) and the increased demands for throughput on database servers and networks, achieving the proper distribution of tasks is challenging. Client/server systems are inherently more difficult to develop and manage than traditional host-based application systems because of the following challenges:

The components of a client/server system are distributed across more varied types of processors. There are many more software components that manage client, network, and server functions, as well as an array of infrastructure layers, all of which must be in place and configured to be compatible with each other.

The complexity of GUI applications far outweighs that of their character-based predecessors. GUIs are capable of presenting much more information to the user and providing many additional navigation paths to elements of the interface.

Troubleshooting performance problems and errors is more difficult because of the increased number of components and layers in the system.

Databases in a Client/Server Architecture

Client/server technologies have changed the look and architecture of application systems in two ways. Not only has the supporting hardware architecture undergone substantial changes, but there have also been significant changes in the approach to designing the application logic of the system.

Prior to the advent of client/server technology, most Oracle applications ran on a single node. Typically, a character-based SQL*Forms application would access a database instance on the same machine with the application and the RDBMS competing for the same CPU and memory resources. Not only was the system responsible for supporting all the database processing, but it was also responsible for executing the application logic. In addition, the system was burdened with all the I/O processing for each terminal on the system; each keystroke and display attribute was controlled by the same processor that processed database requests and application logic.

Client/server systems change this architecture considerably by splitting all of the interface management and much of the application processing from the host system processor and distributing it to the client processor.

Combined with the advances in hardware infrastructure, the increased capabilities of RDBMS servers have also contributed to changes in the application architecture. Prior to the release of Oracle7, Oracle's RDBMS was less sophisticated in its capability to support the processing logic necessary to maintain the integrity of data in the database. For example, primary and foreign key checking and enforcement was performed by the application. As a result, the database was highly reliant on application code for enforcement of business rules and integrity, making application code bulkier and more complex. Figure 2.1 illustrates the differences between traditional host-based applications and client/server applications. Client/server database applications can take advantage of the Oracle7 server features for implementation of some of the application logic.