Data Type Conversion in SQL

PL/SQL supports both explicit and implicit data type conversions of specified values. Explicit conversions work through the use of an internal function, such as TO_NUMBER described previously. Implicit conversions happen at compile time where one data type is supplied and a different data type is expected. This PL/SQL feature enables you to rely on the compiler instead of using explicit conversion routines. Consider the following SQL statement:

SELECT SUM(grand_total)FROM order

WHERE order_date < '10-SEP-95';

In this case, the order_date column is stored as data type DATE, and it is being compared to '10-SEP-95', which is a literal CHAR value. PL/SQL does an implicit conversion on this literal to data type DATE when the procedure containing this SQL select is compiled.

Exception Handling

PL/SQL exception handling is a mechanism for dealing with run-time errors encountered during procedure execution. Use of this mechanism enables execution to continue if the error is not severe enough to cause procedure termination. The decision to enable a procedure to continue after an error condition is one you have to make in development as you consider possible errors that could arise.

You must define the exception handler within a subprogram specification. Errors cause the program to raise an exception with a transfer of control to the exception-handler block. After the exception handler executes, control returns to the block in which the handler was defined. If there are no more executable statements in the block, control returns to the caller.
User-Defined Exceptions

PL/SQL enables the user to define exception handlers in the declarations area of subprogram specifications. You accomplish this by naming an exception as in the following example:

ot_failure EXCEPTION;

In this case, the exception name is ot_failure. Code associated with this handler is written in the EXCEPTION specification area as follows:

EXCEPTION

when OT_FAILURE then

out_status_code := g_out_status_code;

out_msg := g_out_msg;

This exception is defined in the order_total example to capture status and associated data for any NO_DATA_FOUND exceptions encountered in a subprogram. The following is an example of a subprogram exception:

EXCEPTION

when NO_DATA_FOUND then

g_out_status_code := 'FAIL';

RAISE ot_failure;

Within this exception is the RAISE statement that transfers control back to the ot_failure exception handler. This technique of raising the exception is used to invoke all user-defined exceptions.

System-Defined Exceptions

PL/SQL internal exceptions.

Exception Name	Oracle Error
CURSOR_ALREADY_OPEN	ORA-06511
DUP_VAL_ON_INDEX	ORA-00001
INVALID_CURSOR	ORA-01001
INVALID_NUMBER	ORA-01722
LOGIN_DENIED	ORA-01017
NO_DATA_FOUND	ORA-01403
NOT_LOGGED_ON	ORA-01012
PROGRAM_ERROR	ORA-06501
STORAGE_ERROR	ORA-06500
TIMEOUT_ON_RESOURCE	ORA-00051
TOO_MANY_ROWS	ORA-01422
TRANSACTION_BACKED_OUT	ORA-00061
VALUE_ERROR	ORA-06502
ZERO_DIVIDE	ORA-01476

In addition to this list of exceptions, there is a catch-all exception named OTHERS that traps all errors for which specific error handling has not been established. This exception is illustrated in the following example:

when OTHERS then

out_status_code := 'FAIL';

out_msg := g_out_msg || ' ' || SUBSTR(SQLERRM, 1, 60);

This technique is used in the order_total sample procedure to trap all procedure errors other than NO_DATA_FOUND. The information passed back to the caller in out_msg is the subprogram name contained in g_out_msg concatenated with the first 60 characters returned from the SQLERRM function by the SUBSTR function.

Processing Control in SQL

Every procedural language has control structures that provide processing of information in a logical manner by controlling the flow of information. Available structures within PL/SQL include IF-THEN-ELSE, LOOP, and EXIT-WHEN. These structures provide flexibility in manipulating database information.
Loop Control

Use of the LOOP statement provides iterative processing based on logical choices. The basic construct for PL/SQL LOOPs is shown in the following example:

<>

LOOP

(repetitive processing)

END LOOP loop_name;

To break out of a loop such as this, you must issue an EXIT or GOTO statement based on some processing condition. If you raise a user-defined exception, the LOOP also terminates. Now, examine three types of PL/SQL loops that expressly define LOOP termination conditions.

WHILE Loops

The WHILE loop checks the status of any PL/SQL expression that evaluates to TRUE, FALSE, or NULL at the start of each processing cycle. The following is an example of the use of WHILE loops:

WHILE (expression) LOOP

(loop processing)

END LOOP;

As stated, the program evaluates the expression at the start of each loop cycle. The program performs the loop processing if the expression evaluates to TRUE. A FALSE or NULL evaluation terminates the loop. Iterations through the loop are exclusively determined by the evaluation of the expression.
Numeric FOR Loops

You can control loop iterations with the use of numeric FOR loops. This mechanism enables the developer to establish a range of integers for which the loop will cycle. The following example from the order_total package illustrates numeric FOR loops:

<>

FOR i in 1..g_line_counter LOOP

(loop processing)

END LOOP recip_list;

In this example, loop processing cycles over the range of integers 1 through the value of g_line_counter. The value of the loop index i is checked at the start of the loop and incremented at the end of the loop. When i is one greater than g_line_counter, the loop terminates.
Cursor FOR Loops

Cursor FOR loops combine cursor control and conditional control for manipulation of database information. The loop index, cursor OPEN, cursor FETCH, and cursor CLOSE are all implicit when using cursor FOR loops. Consider the following example:

CURSOR c_line_item IS

(sql statement)

BEGIN

FOR li_info IN c_line_item LOOP

(retrieved record processing)

END LOOP;

END;

As shown, the program explicitly declares the c_line_item cursor before its reference in the FOR loop. When the program enters the FOR loop, the code implicitly opens c_line_item and implicitly creates the li_info record as if the following declaration were made:

li_info c_line_item%ROWTYPE;

Once inside the loop, the program can reference the fields of the li_info record that are assigned values by the implicit FETCH inside the FOR loop. Fields of li_info mirror the row retrieved by the c_line_item cursor.

When data is exhausted for the FETCH, c_line_item is implicitly closed.



Iterative Control

The IF-THEN-ELSE structure provides alternative processing paths that depend on certain conditions. For example, consider merchandise orders with multiple-line items where a list of recipients is built. Using conditional and iterative control to build the recipient list, the code is as follows:

PROCEDURE

init_recip_list

IS

recipient_num NUMBER;

i BINARY_INTEGER;

j BINARY_INTEGER := 1;

k BINARY_INTEGER;

BEGIN

g_out_msg := 'init_recip_list';

<>

FOR i in 1..g_line_counter LOOP

IF i = 1 THEN

g_recip_list(j) := g_recipient_num(i);

j := j + 1;

g_recip_list(j) := 0;

ELSE

FOR k in 1..j LOOP

IF g_recipient_num(i) = g_recip_list(k) THEN

exit;

ELSIF k = j THEN

g_recip_list(j) := g_recipient_num(i);

j := j + 1;

g_recip_list(j) := 0;

end IF;

end LOOP;

end IF;

end LOOP recip_list;

END;

In the order_total example, the subprogram init_recip_list builds a list of unique recipient numbers for calculating additional shipping charges. There is a controlling FOR loop that cycles through each recipient number found on a particular order. The g_recip_list array is initialized with the first recipient number, and subsequent numbers are checked against all unique numbers in g_recip_list until a unique list of all recipients is compiled.

Also illustrated in this example is the IF-THEN-ELSE extension ELSIF. This statement provides further conditional control with additional constraint checks within the IF-THEN-ELSE structure. Use of ELSIF also requires a THEN statement in executing logic control.

Another example of iterative control is the use of the EXIT-WHEN statement that allows completion of a LOOP once certain conditions are met. Consider the example of exiting a cursor fetch loop:

open c_line_item;

loop

fetch c_line_item

into li_info;

EXIT WHEN (c_line_item%NOTFOUND) or (c_line_item%NOTFOUND is NULL);

In this example, the LOOP is terminated when no more data is found to satisfy the select statement of cursor c_line_item.

Composite Data Types

The two composite data types in PL/SQL are TABLE and RECORD. The TABLE data type enables the user to define a PL/SQL table to be used for array processing. The RECORD data type enables the user to go beyond the %ROWTYPE variable attribute; with it, you specify user-defined fields and field data types.
Array Processing

The TABLE composite data type provides the developer a mechanism for array processing. Although it's limited to one column of information per PL/SQL table, you can store any number of rows for that column. The word from Oracle is that future versions of PL/SQL will provide more flexibility in the use of tables.

In the order_total example, define a PL/SQL table named g_recip_list (the information will be used globally).
The following is an illustration of this concept:

TYPE RecipientTabTyp IS TABLE OF NUMBER(22)

INDEX BY BINARY_INTEGER;

...

g_recip_list RecipientTabTyp;

To initialize an array, you must first define an array name or TYPE, which in this example is RecipientTabTyp. This TABLE column is defined as NUMBER with a maximum of 22 digits. You can define the column as any valid PL/SQL data type; however, the primary key, or INDEX, must be of type BINARY_INTEGER. After defining the array structure, you can make reference for variable definition as shown with g_recip_list defined as an array of TYPE RecipientTabTyp.
Building Arrays

Arrays are available as information stores subsequent to initialization of the array. To store information in the array g_recip_list that was defined in the last example, you simply reference the array with a numeric value. This is shown in the following example:

g_recip_list(j) := g_recipient_num(i)

In this example, i and j are counters with values 1. . .n. Once information is stored in an array, you can access it, also with numeric values, as shown in the example. In this case, rows of g_recipient_num are referenced for storage in g_recip_list.


Record Processing

The RECORD composite data type provides the developer a mechanism for record processing as described previously. Although you cannot initialize TABLEs at the time of declaration, you can with RECORDs, as illustrated in the following example:

TYPE LineRecTyp IS RECORD

(merch_gross NUMBER := 0,

recip_num NUMBER := 0 );

...

li_info LineRecTyp;

Defining a RECORD of TYPE LineRecTyp allows declarations such as li_info of that TYPE as shown. You can use this method of RECORD declaration in place of the li_info declaration in the previous %ROWTYPE example. As with %ROWTYPE, references to RECORD information is accomplished with dot notation.

g_order_merch_total := g_order_merch_total + li_info.merch_gross;

You can use one of three methods to assign values to records. First, you can assign a value to a record field as you would assign any variable.

li_info.merch_gross := 10.50;

A second method is to assign all fields at once by using two records that are declared with the same data type. Assume a second LineRecTyp is defined as new_li_info.

new_li_info := li_info;

This statement assigns all fields of new_li_info the values from the same fields of li_info.

A third method of assigning values to fields of a record is through SQL SELECT or FETCH statements.

OPEN c_line_item;

...

FETCH c_line_item

INTO li_info;

In this case, all fields of li_info are assigned values from the information retrieved by the FETCH of cursor c_line_item.

Scalar Data Types

PL/SQL supports a wide range of scalar data types for defining variables and constants. Unlike composite data types, scalar data types have no accessible components. These data types fall into one of the following categories:

* Boolean

* Date/time

* Character

* Number

Now, take a closer look at the data types in each category.
Boolean

The BOOLEAN data type, which takes no parameters, is used to store a binary value, TRUE or FALSE. This data type can also store the non-value NULL. You cannot insert or retrieve data from an Oracle database using this data type.
Date/Time

The data type DATE, which takes no parameters, is used to store date values. These DATE values include time when stored in a database column. Dates can range from 1/1/4712 B.C. to 12/31/4712 A.D. Defaults for the DATE data type are as follows:

* Date: first day of current month

* Time: midnight

Character

Character data types include CHAR, VARCHAR2, LONG, RAW, and LONG RAW. CHAR is for fixed-length character data, and VARCHAR2 stores variable-length character data. LONG stores variable-length character strings; RAW and LONG RAW store binary data or byte strings. The CHAR, VARCHAR2, and RAW data types take an optional parameter for specifying length.

datatype(max_len)

This length parameter, max_len, must be an integer literal, not a constant or variable.

Data Type	Maximum Length	Maximum Database Column Width
CHAR	32767	255
VARCHAR2	32767	2000
LONG	32760	2147483647
RAW	32767	255
LONG RAW	32760	2147483647

From this table, you can see the constraint on inserting CHAR, VARCHAR2, and RAW data into database columns of the same type. The limit is the column width. However, you can insert LONG and LONG RAW data of any length into similar columns because the column width is much greater.
Number

There are two data types in the number data type category: BINARY_INTEGER and NUMBER. BINARY_INTEGER stores signed integers with a range of -231 to 231-1. The most common use for this data type is an index for PL/SQL tables.

Storage for fixed or floating-point numbers of any size is available using the NUMBER data type. For floating-point numbers, you can specify precision and scale in the following format:

NUMBER(10,2)

A variable declared in this manner has a maximum of ten digits, and rounding occurs to two decimal places. The precision default is the maximum integer supported by your system, and 0 is the default for scale. The range for precision is 1 to 38 whereas the scale range is -84 to 127.

What is Cursors?

PL/SQL uses cursors for all database information access statements. The language supports the use of both implicit and explicit cursors. Implicit cursors are those established for which explicit cursors are not declared. You must use explicit cursors or cursor FOR loops in all queries that return multiple rows.
Declaring Cursors

You define cursors in the variable definition area of PL/SQL subprograms using the CURSOR name IS statement, as shown in the following example:

CURSOR c_line_item IS

(sql statement)

The cursor SQL statement can be any valid query statement. Subsequent to cursor initialization, you are able to control cursor actions with the OPEN, FETCH, and CLOSE statements.
Cursor Control

To use a cursor for manipulating data, you must use the statement OPEN name to execute the query and identify all rows that meet the select criteria. Subsequent retrieval of rows is accomplished with the FETCH statement. Once all information is processed, the CLOSE statement terminates all activity associated with the opened cursor. The following is an example of cursor control:

OPEN c_line_item;

...

FETCH c_line_item

INTO li_info;

...

(retrieved row processing)

...

CLOSE c_line_item;

The code opens the cursor c_line_item and processes the fetched rows. After it retrieves and processes all the information, the cursor closes. Retrieved row processing is typically controlled by iterative loops as discussed later in the chapter.
Explicit Cursor Attributes

There are four attributes associated with PL/SQL cursors.

* %NOTFOUND

* %FOUND

* %ROWCOUNT

* %ISOPEN

All cursor attributes evaluate to TRUE, FALSE, or NULL, depending on the situation. The attribute %NOTFOUND evaluates to FALSE when a row is fetched, TRUE if the last FETCH did not return a row, and NULL if the cursor SELECT returned no data. Attribute %FOUND is the logical opposite of %NOTFOUND with respect to TRUE and FALSE but still evaluates to NULL if the cursor FETCH returns no data.

You can use %ROWCOUNT to determine how many rows have been selected at any point in the FETCH. This attribute increments upon successful selection of a row. In addition, %ROWCOUNT is at zero when the cursor first opens.

The final attribute, %ISOPEN, is either TRUE or FALSE, depending on whether the associated cursor is open. Before the cursor opens and after the cursor closes, %ISOPEN is FALSE. Otherwise, it evaluates to TRUE.
Cursor Parameters

You can specify parameters for cursors in the same way you do for subprograms. The following example illustrates the syntax for declaring parameter cursors:

CURSOR c_line_item (order_num IN NUMBER) IS

SELECT merch_gross, recipient_num

FROM line_item

WHERE order_num = g_order_num;

The parameter mode is always IN for cursor parameters, but the data type can be any valid data type. You can reference a cursor parameter, whose value is set when the cursor opens, only during the cursor's declared SQL query.

Flexibility within cursor parameters enables the developer to pass different numbers of parameters to a cursor by using the parameter default mechanism. This is illustrated in the following example:

CURSOR c_line_item

(order_num INTEGER DEFAULT 100,

line_num INTEGER DEFAULT 1) IS ...

By using the INTEGER DEFAULT declaration, you can pass all, one, or none of the parameters to this cursor depending on the logic flow of your code.
Creating Cursor Packages

A cursor package is similar to a procedure package in that you specify the cursor and its return attribute, %TYPE or %ROWTYPE, in the package specification area. You then specify the cursor "body" in the package body specification area. Packaging a cursor in this manner gives you the flexibility of changing the cursor body without having to recompile applications that reference the packaged procedure. The following is a cursor package example:

CREATE OR REPLACE PACKAGE order_total

AS

CURSOR c_line_item RETURN line_item.merch_gross%TYPE;

...

END order_total;

CREATE OR REPLACE PACKAGE BODY order_total

AS

CURSOR c_line_item RETURN line_item.merch_gross%TYPE

SELECT merch_gross

FROM line_item

WHERE order_num = g_order_num;

...

END order_total;

In this example, the RETURN variable is the same as the line_item.item_merch_gross column. You can use the %ROWTYPE attribute to specify a RETURN record that mirrors a row in a database table.
Procedure Variables

The most important feature of any language is how to define variables. Once you've defined the variables, PL/SQL enables you to use them in SQL statements as well as language statements. Definition of constants within PL/SQL follow the same rules. Also, you can define variables and constants as local to one subprogram or global to the entire package you are creating.

Function Definition

Function definition is much the same as procedure definition as illustrated by the following example:

FUNCTION

calc_ship_charges (

in_merch_total IN NUMBER

) RETURN NUMBER;

The FUNCTION statement begins the definition of the package function calc_ship_charges. Enclosed in parentheses are the parameters to be passed to the function for calculating shipping charges. The RETURN statement identifies the data type of the calculated value to be returned. The semicolon marks the end of the function definition.
Subprogram Parameter Modes

You can define parameters as IN (the default parameter mode), IN OUT, or OUT, depending on the nature of the information to be passed. The first parameter, in_order_num, is defined as IN, which designates it as a value being passed to the subprogram. Defining a parameter as IN prevents it from being assigned a value in the routine.

Parameters out_status_code, out_msg, out_merch_total, and out_grand_total from the procedure definition example are defined as OUT—values being returned to the caller. These parameters are uninitialized upon entry to the routine and are available for assignment of a value within the routine. Designating a parameter as OUT prevents it from being used in a subprogram expression.

Parameters out_shipping and out_taxes are defined as IN OUT, the last parameter mode. Parameters designated as IN OUT are initialized variables that are available for reassignment within the subprogram.
Subprogram Specifications

After defining a subprogram and its parameters, you develop code for the packaged procedure subprogram. The following example illustrates a few basic constructs to be aware of while coding a subprogram:

PROCEDURE

init_line_items

IS

(local variables)

BEGIN

(subprogram logic)

EXCEPTION

(exception handling)

END init_line_items;

In this example, the PROCEDURE name is init_line_items with the local variables specified after the IS statement. The BEGIN statement is the actual start of the procedure (or function) where subprogram code is developed along with any subprogram exception handling. The procedure is finished with the END name statement.

Creating Package Subprograms

Creating subprograms within a package is the next step in developing a packaged procedure. You must decide which routines will be application-interface routines and which routines will be available only within the package. This determines where the subprogram specification will reside—in the package or in the package body. There are two types of subprograms in PL/SQL, procedures and functions.
Procedure Definition

To define a procedure, you must specify a routine name and the parameters to be passed in and out of the routine. In the order_total example, the following code defines the application-interface routine and resides in the package specification area:

PROCEDURE

get_order_total (

in_order_num IN NUMBER,

out status_code OUT VARCHAR2,

out_msg OUT VARCHAR2,

out_merch_total OUT NUMBER,

out_shipping IN OUT NUMBER,

out_taxes IN OUT NUMBER,

out_grand_total OUT NUMBER

);

The PROCEDURE statement begins the definition of the package application-interface routine get_order_total. Enclosed in parentheses are the parameters to be passed between the application and the order_total package. The semicolon marks the end of the procedure definition.

Package Creation

Before reading about the various aspects of the PL/SQL language, examine the syntax for creating a procedure and building a script for ease of maintenance as changes occur. This code is the first step in developing a sample package for calculating dollar totals in a merchandise order—an example that is completed within this chapter. The following example illustrates some commands for creating this PL/SQL packaged procedure and script.

set echo on

spool order_total

CREATE OR REPLACE PACKAGE order_total

AS

(package specifications)

END order_total

CREATE OR REPLACE PACKAGE BODY order_total

AS

(package body specifications)

END order_total;

DROP PUBLIC SYNONYM order_total;

CREATE PUBLIC SYNONYM order_total for order_total;

GRANT EXECUTE ON order_total TO PUBLIC;

spool off

SELECT

*

FROM

user_errors

WHERE

name='ORDER_TOTAL'

;

The first command in this script, SET ECHO ON, displays a listing of the package to the screen as it is being compiled. ECHO combined with the SPOOL name command creates a list file (order_total.lst) for debug purposes. This file will contain the compilation of the procedure, including errors, complete with line numbers.

CREATE OR REPLACE PACKAGE name is the command that starts the procedure build in the database. Declarations of objects and subroutines within the package area are visible to your applications. Think of this area as the application interface to your PL/SQL code; at the very least, you must define the procedure entry routine here. Modifications to any specifications in this area require rebuilding your applications. The END statement signifies the end of the package specification area.

Master/Detail or Parent/Child SQL

me a SQL statement is constructed with multiple tables, a parent/child relationship is usually in effect.

The user must be familiar with the database schema in use and the corresponding constraints in order to properly join tables. Writing poorly constructed SELECT statements will not harm the database but might decrease the system performance and possibly give a false relationship representation to the users. If there are poorly constructed INSERT, UPDATE, or DELETE statements, the effect could be disastrous.

Before you see any examples, certain assumptions need to be made.

1. An employee cannot be entered without a department number. This indicates that the emp table is a child of the dept table.
   
   
2. Addresses do not have to be entered when creating a new employee or department. Therefore, the addresses table is optional and is a child of the emp table and a child of the dept table.
   
   

If these constraints are enforced in the database, protection would be provided when a parent row is deleted but does not delete the corresponding children.

SELECT d.name                          dept_name,

      d.dept_no                       dept_number,

      e.first_name || e.last_name     emp_name,

      e.job_title                     title,

      e.hire_date                     start_date

FROM dept    d,

    emp     e

WHERE d.dept_no   =  e.dept_no

ORDER BY d.name, e.last_name;

In this example, all the department names and numbers will be displayed (the parent) with all of the corresponding employees (the children) in the departments.

SELECT d.name                                              dept_name,

      d.dept_no                                           dept_number,

      e.first_name || e.last_name                         emp_name,

      e.job_title                                         title,

      e.hire_date                                         start_date,

      DECODE(a.box_number, NULL, a.adrs_1, a.box_number)  address,

      DECODE(a.adrs_2, NULL, NULL, a.adrs_2)              address_2,

      a.city || ', '||a.state ||'   '||a.zip              city_stat_zip

FROM dept       d,

    emp        e,

    addresses  a

WHERE d.dept_no   =  e.dept_no

 AND e.adrs_id   =  a.adrs_id (+)

ORDER BY d.name, e.last_name;

This example shows the addition of the optional child table, called addresses. An outer join, (+) is used so that the employee row will still be retrieved even if there is no address information available yet. The DECODEs will retrieve the box number or adrs 1 depending upon the existence of box number.

INSERTs, UPDATEs, and DELETEs

The INSERT statement is used to put new rows into the database. This can be done one row at a time using the VALUES expression or a whole set of records at a time using a subquery. The following is the syntax for an INSERT statement:

INSERT INTO schema.table column(s) VALUES subquery

where schema is an optional parameter to identify which database schema to use for the insert. The default is your own.

table is mandatory and is the name of the table.

column is a list of columns that will receive the inserted values.

VALUES is used when one row of data will be inserted. Values are represented as constants.

subquery is used when the VALUES option is not used. The columns in the subquery must match the sequence and data types of the columns in the insert list.

INSERT INTO dept (dept_no,

name,

adrs_id)

VALUES (dept_seq.NEXTVAL,

'CUSTOMER SERVICE',

adrs_seq.NEXTVAL);

This example inserts one row into the table dept. Sequences dept_seq and adrs_seq are used to retrieve the next numeric values for dept_no and adrs_id.

If multiple rows need to be inserted, the INSERT EXAMPLE 1 statement would have to executed for each individual row. If a subquery can be used, multiple rows would be inserted for each row returned by the subquery.

INSERT INTO emp (emp_id,

first_name,

last_name,

dept_no,

hire_date,

job_title,

salary,

manager_id)

SELECT emp_seq.NEXTVAL,

new.first_name,

new.last_name,

30,

SYSDATE,

'CUSTOMER REPRESENTATIVE',

new.salary,

220

FROM candidates new

WHERE new.accept = 'YES'

AND new.dept_no = 30;

This example will insert all rows form the candidates table that have been assigned to department number 30 and have been accepted. Because the department number and manager ID are known, they are used as constants in the subquery. The UPDATE statement is used to change existing rows in the database. The syntax for the UPDATE statement is

UPDATE schema.table SET column(s) = expr sub query WHERE condition

where

schema is an optional parameter to identify which database schema to use for the update. The default is your own.

table is mandatory and is the name of the table.

SET is a mandatory reserved word.

column is a list of columns that will receive the updated values.

expr is the new value to be assigned.

sub query is a select statement that will retrieve the new data values.

WHERE is optional and is used to restrict which rows are to be updated.

UPDATE emp

SET dept_no = 30

WHERE last_name = 'DOE'

AND first_name = 'JOHN';

This example will transfer an employee named JOHN DOE to department 30. If there is more than one JOHN DOE, further restrictions will have to be made in the WHERE clause.

UPDATE emp

SET salary = salary + (salary * .05);

This update example will give everyone in table emp a 5 percent increase in salary.

UPDATE emp a

SET a.salary = (SELECT a.salary

+ (a.salary * DECODE(d.name, 'SALES', .1,

'ADMIN', .07,

.06))

FROM dept d

WHERE d.dept_no = a.dept_no)

WHERE a.dept_no = (SELECT dept_no

FROM dept y, addresses z

WHERE y.adrs_id = z.adrs_id

AND z.city = 'ROCHESTER');

This example will give raises to employees located in Rochester. The amount of the raise is handled by the DECODE statement evaluating the department name. Employees in the Sales department will receive a 10 percent raise, the Admin department receives a seven percent raise, and everyone else receives a 6 percent raise.

The DELETE statement is used to remove database rows. The syntax for DELETE is

DELETE FROM schema.table WHERE condition

where

SCHEMA is an optional parameter to identify which database schema to use for the delete. The default is your own.

TABLE is mandatory and is the name of the table.

WHERE restricts the delete operation.

DELETE FROM addresses

WHERE adrs_id = (SELECT e.adrs_id

FROM emp e

WHERE e.last_name = 'DOE'

AND e.first_name = 'JOHN');

DELETE FROM emp e

WHERE e.last_name = 'DOE'

AND e.first_name = 'JOHN';

If employee John Doe left the company, you probably would want to delete him from the database. One way to accomplish this is to delete the row containing his name from the addresses table and the emp table. In order to find John Doe in the addresses table, you must perform a subquery using the emp table. Therefore, the entry in the emp table has to be the last row to be deleted, or else there would be an orphan row in the addresses table.

DELETE FROM dept

WHERE adrs_id is null;

In this example, all rows in the dept table will be deleted if the corresponding adrs_id is null.

Deletes are permanent! Once the commit has taken place, it is impossible to get the row(s) back apart from issuing an INSERT statement. There is not an undo command available.

The DECODE Statement

One of the most powerful and overlooked SQL statements is the DECODE statement. The DECODE statement has the following syntax:

DECODE(val, exp1, exp2, exp3, exp4, ..., def);

DECODE will first evaluate the value or expression val and then compare expression exp1 to val. If val equals exp1, expression exp2 will be returned. If val does not equal exp1, expression exp3 will be evaluated and returns expression exp4 if val equals exp3. This process continues until all expressions have been evaluated. If there are no matches, the default def will be returned.

SELECT e.first_name,

e.last_name,

e.job_title,

DECODE(e.job_title, 'President', '******', e.salary)

FROM emp e

WHERE e.emp_id in (SELECT NVL(z.manager_id, e.emp_id)

FROM emp z);

In this example, all manager names will be retrieved with their salaries. When the row identifying the president is displayed, show '******' instead of his salary. Also notice the NVL function used to evaluate a null manager ID. Only the president will have a null manager ID, which would not have been retrieved without the NVL.

Also notice that DECODE is evaluating job_title and returning salary, which would normally be a data type mismatch since the job title and salary columns are different data types but is okay here.

SELECT e.first_name,

e.last_name,

e.job_title,

e.salary

FROM emp e

WHERE DECODE(USER,'PRES',e.emp_id,

UPPER(e.last_name),e.emp_id, 0) = e.emp_id ;

In this example, if the user is the president, all employees will be returned with their corresponding salary. For all other users, only one row will be retrieved, enabling the user to see his or her own salary only.

SELECT e.first_name,

e.last_name,

e.job_title,

DECODE(USER,'ADMIN',DECODE(e.job_title, 'PRESEDENT', '*****', e.salary),

'PRES', e.salary, '******')

FROM emp e

WHERE e.emp_id in (SELECT NVL(z.manager_id, e.emp_id)

FROM emp z);

In this example, the DECODE statement is nested with another DECODE statement. If the Oracle user is 'ADMIN', show the salaries except for the president's salary. If the Oracle user is 'PRES', show all salaries and if the user is anybody else, return '******'.

Another place the DECODE statement can be used is in the ORDER BY clause. The next example will sort the output in such a way that the president is the first row returned followed by the departments 'SALES', 'ADMIN', and then 'IS' with their corresponding employees.

SELECT d.name,

e.job_title,

e.first_name,

e.last_name

FROM emp e,

dept d

WHERE d.dept_no = e.dept_no

ORDER BY DECODE(e.job_title,'PRESIDENT', 0,

DECODE(d.name,'SALES', 1,

'ADMIN', 2, 3)), e.last_name;

This example does not ORDER BY e.job_title but uses this column to search for the title 'PRESIDENT' and returns a 0. For all other rows, another DECODE is used to evaluate the department name and returning numbers 1, 2, or 3 depending upon what the department name is. After the DECODEs are finished, the data is further sorted by employee last name e.last_name.

What is Subqueries?

Subqueries, or nested queries, are used to bring back a set of rows to be used by the parent query. Depending on how the subquery is written, it can be executed once for the parent query or it can be executed once for each row returned by the parent query. If the subquery is executed for each row of the parent, this is called a correlated subquery.

A correlated subquery can be easily identified if it contains any references to the parent subquery columns in its WHERE clause. Columns from the subquery cannot be referenced anywhere else in the parent query. The following example demonstrates a non-correlated subquery.

SELECT e.first_name,

e.last_name,

e.job_title

FROM emp e

WHERE e.dept_no in (SELECT dept_no

FROM dept

WHERE name = 'ADMIN');

In this example, all employee names and job titles will be retrieved for the department 'ADMIN'. Notice the use of the operator in when referring to the subquery. The in operator is used when one or more rows might be returned by a subquery. If the equal operator (=) is used, it is assumed that only one row will be returned. If the equal operator (=) is used and more than one row is returned, Oracle will return an error.

This statement could have been written by directly joining the dept table with the emp table in the main or parent query. Subqueries are sometimes used for performance gain. If the parent query contains a lot of tables, it might be advantageous to break up the WHERE clause into subqueries.

SELECT d.name,

e.first_name,

e.last_name,

e.job_title

FROM emp e,

dept d

WHERE e.dept_no = d.dept_no

AND d.adrs_id = (SELECT adrs_id

FROM ADDRESSES

WHERE adrs_id = d.adrs_id)

ORDER BY d.name, e.job_title, e.last_name;

In this example, all employees with their corresponding departments will be retrieved only for departments that have a valid adrs_id in the addresses table. This is a correlated subquery because the subquery references a column in the parent query.

SELECT d.name,

e.first_name,

e.last_name,

e.job_title

FROM emp e,

dept d

WHERE e.dept_no = d.dept_no

AND not exists (SELECT 'X'

FROM ADDRESSES

WHERE city in ('ROCHESTER','NEW YORK')

AND adrs_id = d.adrs_id)

ORDER BY d.name, e.job_title, e.last_name;

This example will return all departments and employees except where departments are located in 'ROCHESTER' and 'NEW YORK'. SELECT 'X' will return a true or false type answer that will be evaluated by the not exists operator. Any constant could be used here; 'X' is only one example.

Outer Joins

When the columns of a table are outer joined, this tells the database to retrieve rows even if data is not found. The plus symbol (+) is used to denote an outer join condition, as shown in the following example:

SELECT d.name,

a.city,

e.last_name,

e.first_name

FROM emp e,

dept d,

addresses a

WHERE d.dept_no(+) = e.dept_no

AND a.adrs_id = d.adrs_id

ORDER BY d.name,e.last_name,e.first_name;

If the president of the company was never assigned a department, his name would never be retrieved in previous examples because his department number would be null. The outer join would cause all rows to be retrieved even if there is not a match for dept_no.
Outer joins are effective but will make the query perform slower. You might need to rewrite the query if you need to improve performance.

Joining Tables

Tables are physically joined in the FROM clause of your query. They are logically joined in the WHERE clause. Table columns that appear in the WHERE clause must have the table name listed in the FROM clause. The WHERE clause is where the tables relate one to another.

The way in which the WHERE clause is constructed greatly affects the performance of the query. A two-table join does not necessarily perform better than a 10-table join.

If there are a lot of queries that have a large number of tables joined together (more than seven tables, for example), you might need to consider denormalizing certain data elements to reduce the number of table joins. This type of denormalization might be required when user productivity or system performance has significantly decreased.

In the following example, a query is written that will list all departments with their corresponding employees and the city in which the department resides.

SELECT d.name,

      e.last_name,

      e.first_name,

      a.city

FROM emp        e,

    dept       d,

    addresses  a

WHERE d.dept_no    = e.dept_no

 AND a.adrs_id    = d.adrs_id

ORDER BY d.name,e.last_name,e.first_name;

If the employee city needed to be retrieved as well, the query could be written like the following:

SELECT d.name,

      a.city  dept_city,

      e.last_ name,

      e.first_name,

      z.city  emp_city

FROM emp        e,

    dept       d,

    addresses  a,

    addresses  z

WHERE  d.dept_no      = e.dept_no

 AND  a.adrs_id      = d.adrs_id

 AND  z.adrs_id      = e.adrs_id

ORDER BY d.name,e.last_name,e.first_name;

In this example the addresses table was joined twice, enabling the city column to be retrieved for both the department and employee. In order to clarify the output, aliases were assigned to the different city columns in the SELECT portion of the query.

The following example adds the employee manager's name to the query.

SELECT d.name,

      a.city  dept_city,

      e.last_name,

      e.first_name,

      z.city  emp_city,

      m.first_name || m.last_name manager

FROM emp          e,

    dept       d,

    addresses  a,

    addresses  z,

    emp        m

WHERE  d.dept_no      = e.dept_no

 AND  a.adrs_id      = d.adrs_id

 AND  z.adrs_id      = e.adrs_id

 AND  m.emp_id       = e.manager_id

ORDER BY    d.name,e.last_name,e.first_name;

The output from this query will cause the manager (alias) column to appear as one column even though it is made from two columns. The symbol (||) is used to concatenate columns together.

Know Your Tables and Views In SQL?

To ensure that your data contains all of the required columns and restrictions, you must be familiar with the database schema. If a schema diagram is not available, there are numerous ways to find out what tables or views might be needed for writing queries. One way is to look at some of the data dictionary tables.

To view all of the data dictionary table names, issue the following SELECT statement:

SELECT table_name

FROM dictionary

ORDER BY table_name;

Some of the tables of interest should be all_tables, all_columns, all_views and all_constraints.

To view the column names of these tables, issue 'DESC table_name'. DESC stands for DESCribe and 'table_name' is the name of the table in question, such as 'all_tables'. Therefore, 'DESC all_tables' will return all of the columns and their data types for the table 'all_tables'.

With the help of the data dictionary tables, it is possible to determine what tables, views, and constraints are in effect for the application in question.

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.

Writing Queries in SQL

To retrieve data from the database, use the SELECT statement. Once again, proper privileges are required and are maintained by the DBA. The SELECT statement has the following format:

SELECT column(s)

FROM tables(s)

WHERE conditions are met

GROUP BY selected columns

ORDER BY column(s);

Every SQL statement ends with a semicolon (;). When you are writing scripts (disk files) that will be executed, you can also use a slash (\) to terminate the SQL statement.

When SELECT column(s) is used, it is assumed that all of the columns fitting the WHERE clause will be retrieved. It is sometimes necessary to only retrieve columns that are distinct from one another. To do this, use the reserved word DISTINCT before the column descriptions. In the following example, a SELECT statement is used to retrieve all of the cities and states from the addresses table (defined previously).

SELECT city, state

FROM addresses;

When this code run, every city and state will be retrieved from the table. If 30 people lived in Rochester, NY, the data would be displayed 30 times. To see only one occurrence for each city and state use the DISTINCT qualifier, as shown in the following example:

SELECT DISTINCT city, state

FROM addresses;

This will cause only one row to be retrieved for entries with Rochester, NY.

The FROM clause is a listing of all tables needed for the query. You can use table aliases to help simplify queries, as shown in the following example:

SELECT adrs.city, adrs.state

FROM addresses adrs;

In this example, the alias adrs has been given to the table addresses. The alias will be used to differentiate columns with the same name from different tables.

The WHERE clause is used to list the criteria necessary to restrict the output from the query or to join tables in the FROM clause. See the following example.

SELECT DISTINCT city, state

FROM addresses

WHERE state in ('CA','NY','CT')

AND city is NOT NULL;

This example will retrieve cities and states that are in the states of California, New York, and Connecticut. The check for NOT NULL cities will not bring data back if the city field was not filled in.

The GROUP BY clause tells Oracle how to group the records together when certain functions are used.

SELECT dept_no, SUM(emp_salary)

FROM emp

GROUP BY dept_no;

The GROUP BY example will list all department numbers once with the summation of the employee salaries for that particular department.

The CREATE Statement in SQL

The CREATE statement opens the world to the user. Whether a simple temporary table is to be created or a complex database schema, you will repeatedly use the CREATE statement. Only a few of the more common CREATE statements are covered here.
Tables

Every database designer will have to create a table sometime. The CREATE TABLE system privilege is needed to execute this command. The DBA is responsible for administering these privileges. The syntax to create a table is :

CREATE TABLE schema.TABLE (COLUMN DATATYPE default expression column constraint) table constraint

PCTFREE x PCTUSED x INITRANS x MAXTRANS x TABLESPACE name STORAGE clause CLUSTER cluster clause

ENABLE clause DISABLE clause AS subquery

In this syntax, SCHEMA is an optional parameter to identify which database schema to place this table in. The default is your own.

TABLE is mandatory and is the name of your table.

COLUMN DATATYPE are required to identify each column in the table. Separate the columns with commas. There is a maximum of 254 columns per table.

The DEFAULT expression is optional and is used to assign a default value to a column when a subsequent insert statement fails to assign a value.

COLUMN CONSTRAINT is optional. It is used to define an integrity constraint such as not null.

TABLE CONSTRAINT is optional and is used to define an integrity constraint as part of the table, such as the primary key.

PCTFREE is optional but has a default of 10. This indicates that 10 percent for each data block will be reserved for future updates to the table's rows. Integers from 1 to 99 are allowed.

PCTUSED is optional but has a default of 40. This indicates the minimum percentage of space used that Oracle maintains before a data block becomes a candidate for row insertion. Integers from 1 to 99 are allowed. The sum of PCTFREE and PCTUSED must be less than 100.

INITRANS is optional but has a default of 1. Integers from 1 to 255 are allowed. It is recommended that you leave this alone. This is an allocation of the number of transaction entries assigned within the data block for the table.

MAXTRANS is optional but has a default that is a function of the data block size. This is used to identify the maximum number of concurrent transactions that can update a data block for your table. It is recommended that this parameter not be changed.

TABLESPACE is optional but has a default value as the tablespace name of the owner of the schema. A different tablespace name than the default can be used. Tablespace names are usually application-dependent. The DBA will be able to give proper recommendations.

STORAGE is optional and has default characteristics defined by the DBA.

CLUSTER is optional and specifies that a table is to be part of a cluster. You must identify the columns from the table that need to be clustered. Typically, the cluster columns are columns that comprise the primary key.

ENABLE is optional and turns on an integrity constraint.

DISABLE is optional and turns off an integrity constraint.

AS SUBQUERY is optional and inserts the rows returned by the subquery into the table upon creation.

Once the table is created, you can use the ALTER TABLE command to make alterations to the table. To modify an integrity constraint, DROP the constraint first, and then re-create it.

Let's look at two examples on creating tables:

CREATE TABLE ADDRESSES (ADRS_ID NUMBER(6),

ACTIVE_DATE DATE,

BOX_NUMBER NUMBER(6),

ADDRS_1 VARCHAR2(40),

ADDRS_2 VARCHAR2(40),

CITY VARCHAR2(40),

STATE VARCHAR2(2),

ZIP VARCHAR2(10));

This is the simplest form of a table create using all of the default capabilities. The second example follows:

CREATE TABLE ADDRESSES (ADRS_ID NUMBER(6) CONSTRAINT PK_ADRS PRIMARY KEY,

ACTIVE_DATE DATE DEFAULT SYSDATE,

BOX_NUMBER NUMBER(6) DEFAULT NULL,

ADDRS_1 VARCHAR2(40) NOT NULL,

ADDRS_2 VARCHAR2(40) DEFAULT NULL,

CITY VARCHAR2(40) DEFAULT NULL,

STATE VARCHAR2(2) DEFAULT 'NY',

ZIP VARCHAR2(10))

PCTFREE 5

PCTUSED 65

TABLESPACE adrs_data

STORAGE (INITIAL 5140

NEXT 5140

MINEXTENTS 1

MAXEXTENTS 10

PCTINCREASE 10);

In this example, data constraints are being utilized and certain storage parameters will be in effect. Using PCTFREE and PCTUSED is a good idea if your data is relatively static.
Indexes

Indexes are used to increase performance of the database. An index is created on one or more columns of a table or cluster. Multiple indexes per table are allowed. The CREATE INDEX system privilege is needed to execute this command. The DBA is responsible for administering these privileges. The syntax to create an index is

CREATE INDEX schema.index ON schema.table (COLUMN ASC/DESC)

CLUSTER schema.cluster INITRANS x MAXTRANS x TABLESPACE name STORAGE clause PCTFREE x NOSORT

In this syntax, SCHEMA is an optional parameter to identify which database schema to place this table in. The default is your own.

INDEX is mandatory and is the name of the index.

ON is a mandatory reserved word.

TABLE is a mandatory table name upon which the index will be built.

COLUMN is the column name to be indexed. If there is more than one column, make sure they are in order of priority.

ASC/DESC are optional parameters. Indexes are built in ascending order by default. Use DESC for descending order.

CLUSTER is needed only if this index is for a cluster.

INITRANS is optional but has a default of 1. Integers from 1 to 255 are allowed. It is recommended that this parameter not be changed. This is an allocation of the number of transaction entries assigned within the data block for the index.

MAXTRANS is optional but has a default that is a function of the data block size. It is used to identify the maximum number of concurrent transactions that can update a data block for the index. It is recommended that this parameter not be changed.

TABLESPACE is optional but has a default value as the tablespace name of the owner of the schema. A different tablespace name than the default might be needed. The DBA will be able to give some recommendations.

STORAGE is optional and has default characteristics defined by the DBA.

PCTFREE is optional but has a default of 10. This indicates that 10 percent for each data block will be reserved for future updates to the index. Integers from 1 to 99 are allowed.

NOSORT is an optional parameter that will save time when creating the index if the table data is already stored in ascending order. This cannot be used if a clustered index is being created.

Using the addresses table defined from the create table example, two indexes will be created in the next example.

CREATE INDEX x_adrs_id ON ADDRESSES (ADRS_ID);

This will create an index on the adrs_id column only.

CREATE INDEX x_city_state ON ADDRESSES (CITY,STATE)

TABLESPACE application_indexes;

This index has two columns; CITY is the primary column. In order for queries to use an index, the column names must be part of the select statement. If a select statement included STATE but not CITY, the index would not be used. However, if the select statement contained a reference to CITY but not STATE, part of the index would be used because CITY is the first column of the index.
Sequences

Sequences are a great way to have the database automatically generate unique integer primary keys. The CREATE SEQUENCE system privilege is needed to execute this command. The DBA is responsible for administering these privileges. The syntax to create a sequence is

CREATE SEQUENCE schema.name

INCREMENT BY x

START WITH x

MAXVALUE x NOMAXVALUE

MINVALUE x NOMINVALUE

CYCLE NOCYCLE

CACHE x NOCACHE

ORDER NOORDER

In this syntax, SCHEMA is an optional parameter that identifies which database schema to place this sequence in. The default is your own.

NAME is mandatory because it is the name of the sequence.

INCREMENT BY is optional. The default is one. Zero is not allowed. If a negative integer is specified, the sequence will descend in order. A positive integer will make the sequence ascend (the default).

START WITH is an optional integer that enables the sequence to begin anywhere.

MAXVALUE is an optional integer that places a limit on the sequence.

NOMAXVALUE is optional. It causes the maximum ascending limit to be 10 27 and -1 for descending sequences. This is the default.

MINVALUE is an optional integer that determines the minimum a sequence can be.

NOMINVALUE is optional. It causes the minimum ascending limit to be 1 and -(10 26) for descending sequences. This is the default.

CYCLE is an option that enables the sequence to continue even when the maximum has been reached. If the maximum is reached, the next sequence that will be generated is whatever the minimum value is.

NOCYCLE is an option that does not enable the sequence to generate values beyond the defined maximum or minimum. This is the default.

CACHE is an option that enables sequence numbers to be preallocated that will be stored in memory for faster access. The minimum value is 2.

NOCACHE is an option that will not enable the preallocation of sequence numbers.

ORDER is an option that ensures the sequence numbers are generated in order of request.

NOORDER is an option that does not ensure that sequence numbers are generated in the order they are requested.

If you want to create a sequence for your adrs_id column in the ADDRESSES table, it could look like the following example:

CREATE SEQUENCE adrs_seq

INCREMENT BY 5

START WITH 100;

To generate a new sequence number, use the pseudocolumn NEXTVAL. This needs to be preceded with your sequence name. For example, adrs_seq.nextval would return 100 for the first access and 105 for the second. If determining the current sequence number is necessary, use CURRVAL. Therefore, adrs_seq.currval will return the current value of the sequence.
Other Objects

The purpose of this chapter is not to elaborate on every SQL statement. The ones given have been covered to give an overview of the more common create statements. Listed next is an alphabetical list of all objects that can be created with the CREATE statement.

CREATE xxx, where xxx is one of the following:

CLUSTER

CONTROLFILE

DATABASE

DATABASE LINK

DATAFILE

FUNCTION

INDEX

PACKAGE BODY

PACKAGE

PROCEDURE

PROFILE

ROLE

ROLLBACK SEGMENT

SCHEMA

SEQUENCE

SNAPSHOT

SNAPSHOT LOG

SYNONYM

TABLE

TABLESPACE

TRIGGER

USER

VIEW

SQL functions

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.