Changing Structure and Data

*  Altering the structure of a table

*  Dropping a table

*  Manipulating data

*  Transaction

*  Locking

*  Read Consistency

*  Summary

*  Exercises

 

Altering the structure of a table

It is possible to modify the structure of the table even after the table is created. ALTER TABLE command is used to alter the structure of the table.

The following are the possible alterations

*  Adding a new column

*  Adding a new table level constraint

*  Increasing and decreasing width of a column

*  Changing data type of the column

*  Dropping a column

*  Dropping a constraint

*  Disabling a constraint

*  Enabling a disabled constraint

 

The following is the syntax of ALTER TABLE command.

  

ALTER TABLE Syntax

ALTER TABLE tablename

  [ ADD    ( column  specification ) ]         

  [ MODIFY ( column specification) ]

  [ DROP    constraint-name  [CASCADE]

           | column [CASCADE CONSTRAINTS]

  [ DROP  UNUSED  COLUMN  [column] ]

  [SET UNUSED column] 

  [ ENABLE |  DISABLE  constraint-name ] 

Let us now examine various examples using ALTER TABLE command. It is important to note that some possibilities are applicable only when certain conditions are met. These if and buts are to be remembered while modifying the structure of the table.

 

Adding a new column or constraint

It is always possible to add a new column to an existing table. However, if column is to be added to a table that already contains rows, then certain options are not available.

To add a new column CAT (category) to COURSES table, enter the following:

ALTER TABLE courses

    ADD  (cat varchar2(5));

It is not possible to given any constraint that is not satisfied by existing data. For instance, it is not possible to add CAT as a NOT NULL column as Oracle initializes CAT column in all rows with NULL value.  See the following snapshot of the screen.

SQL> alter table courses

  2  add ( cat varchar2(5) not null);

alter table courses

            *

ERROR at line 1:

ORA-01758: table must be empty to add mandatory (NOT NULL) column

However, it is possible to have NOT NULL constraint in the following cases:

*  If DEFAULT option is used to specify the default value for the column

*  When table is empty.

The following example will work as DEFAULT option is used to specify the default value.

alter table courses  add ( cat varchar2(5) default 'prog' not null);

You can add a table constraint. Once table is created, it is not possible to add constraints other than NOT NULL to columns of the table. However, it is possible to add any constraint at the table level as follows:

alter table courses

     add ( constraint courses_cat_chk  check (length(cat) >= 2))

We will see more about functions such as length later in this book. The above constraint specifies that the column CAT should have at least two characters. It is added as a table constraint as it not possible to add CHECK constraint at the column level to column that already exists.

Modifying attributes of existing columns

It is also possible to modify the certain attributes of an existing column. The following are possible modifications.

*  Increasing the length of the column

*  Decrease the length of the column only when column is empty.

*  Change the datatype of the column only when column is empty.

*  Adding NOT NULL constraint. No other constraint can be added to column. However, it is possible to add constraints at table level. See the previous section.

To increase the size of the column CAT, enter the following:

alter table courses

  modify (cat varchar2(10));

You can decrease the width and even change the datatype of the column if the column is empty. That means if no row has any value for the column being altered.

Dropping a constraint

Used to drop the constraints defined on the table.

To drop a constraint, the name of the constraint is required.  You may use USER_CONSTRAINTS data dictionary view to get the list of constraints. 

alter table courses drop constraint courses_cat_chk;

CASCADE Option

You cannot drop a UNIQUE or PRIMARY KEY constraint that is part of a referential integrity constraint without also dropping the corresponding foreign key. To drop PRIMARY KEY or UNIQUE constraint along with REFERENCES constraint use CASCADE option.

To drop PRIMARY KEY constraint of STUDENTS along with related constraint, do the following.

alter table courses

     drop primary key cascade;

Note: You can get information about all the constraint using USER_CONSTRAINTS data dictionary view.

Dropping a column

For the first time Oracle8i has provided a command to drop a column from the table.  Till Oracle8, dropping a column is very lengthy task as there was no direct way to drop a column.  Oracle8i has provided a new option with ALTER TABLE command to drop a column – DROP COLUMN. 

Actual you have two options when you want to drop a column.

*  Either you can drop unwanted column straight away. All the data related to the column being dropped will be removed immediately.

*  Or you can mark the column for deletion and delete the column at a later stage. Since the column is marked for dropping, it is considered to be dropped. But the data of the column will remain until the column is physically removed.

The second options is especially useful considering the fact that dropping of a column does take a lot of time and if it is done when the load on the system is high then it will severely effect performance of the system. So you can mark a column for dropping and then drop the column when load on the system is low.

To drop column CAT of COURSES table, enter the following:

alter table courses drop column cat;

If column being dropped is either a PRIMARY KEY or UNIQUE key that is referenced by a foreign key, then it is not possible to drop the column.  But it is possible if CASCADE CONSTRAINTS option is used. CASCADE CONSTRAINTS option drops all constraints that depend on the column being dropped.

To drop column FCODE column of FACULTY table along with all depending constraints, enter:

alter table faculty drop column fcode cascade constraints;

Note: When you drop a UNIQUE or PRIMARY KEY column then Oracle automatically drops the index that it creates to enforce uniqueness.

SET UNUSED option of ALTER TABLE command is used to mark a column for dropping. But the column is not physically removed. However, the column is treated as deleted. Once a column is marked as UNUSED then it cannot be accessed.

The following example marks column CAT or COURSES table as unused.

alter table courses set unused column cat;

Columns that are marked for deletion can be physically deleted using DROP UNUSED COLUMNS option of ALTER TABLE command as follows:

alter table courses drop unused columns;      

 

Note: We can view the number of columns that are marked for deletion using USER_UNUSED_COL_TABS data dictionary view. USER_TAB_COLUMNS gives information about existing columns of a table.

Note: Until a column is physically dropped from the table it is counted as a column of the table and counted towards the absolute limit of 1000 columns per table.

Enabling and Disabling Constraints

ALTER TABLE can be used to enable and disable constraints without dropping constraints. When a constraint is disabled, Oracle does not enforce the rule defined by constraint. This may be useful when you need to insert a large number of rows and does not want Oracle to apply constraints as it takes a lot of time.

To disable PRIMARY KEY on SUBJECTS table:

ALTER TABLE courses DISABLE PRIMARY KEY;

Or you can drop any constraint by giving its name as follows:

alter table courses disable constraint courses_cat_chk;

If the constraint has depending constraints then you must use CASCADE clause to disable dependent constraints.

You can enable a disabled constraints using ENABLE clause as follows:

alter table courses disable constraint courses_cat_chk;

Note: You can find out status of a constraint by using STATUS column of USER_CONSTRAINTS data dictionary view.

1.                   

Dropping a table

To drop a table, use DDL command DROP TABLE.  It removes the data as well as structure of the table The following is the syntax of DROP TABLE command.

 

DROP TABLE  tablename  [CASCADE CONSTRAINTS];

 

CASCADE CONSTRAINTS clause is used to drop constraints that refer to primary and unique keys in the dropped table. If you do not give this clause and if referential integrity (references constraint) constraints exists then Oracle displays then Oracle displays error and doesn’t drop the table.

 

The following command will drop FACULTY table.

DROP TABLE faculty;

Note: When table is dropped, Views, and Synonyms based on the table will be made invalid, though they remain in the system.   Also note, dropping a table cannot be undone.

Manipulating data

As we have seen in the first chapter, SQL commands are divided into DML commands and DDL commands. DDL commands such as CREATE TABLE, ALTER TABLE, and DROP TABLE are dealing with definition of the table or structure of the table.

DML commands such as INSERT, DELETE and UPDATE are used to manipulate the data of the table. We have already seen how to use INSERT command to insert rows into table. Now let us see two other DML commands.

Updating rows using UPDATE command

UPDATE command is used to modify existing data in the rows.  The following is the syntax of UPDATE command.

 

UPDATE table SET column = {expression | subquery}

                [, column = {expression | subquery}] ...

[WHERE condition];

If WHERE clause is not given then all the rows of the table will be effected by the change.  In fact, it is more often the result of an error than intentional.

The following command will change course fee of ASP to 6000.

Update courses set fee = 6000

Where ccode = ‘asp’;

It is also possible to change more than one column at a time as follows:

update  courses  set  fee = 6000, duration=30

where  ccdoe = ‘asp’;

Note: We will discuss how to use subquery in UPDATE command later in this book.

Deleting rows using DELETE command

DELETE command is used to delete rows from a table.  The following is the syntax of DELETE command.

DELETE   FROM    table

     [WHERE  condition;]

If WHERE clause is not given then all rows of the table will be deleted.

The following command will delete row where CCODE is  “c”.

Delete from  courses

Where ccode = ‘c’;

It is not possible to delete a parent row while it has child rows. For example, it is not possible to delete a row from COURSES table if the row has dependent rows in BATCHES table or in COURSE_FACULTY table.

However, it is possible to delete parent row along with its child rows provided ON DELETE CASCADE option is given at the time of create foreign key constraint in child table.  Please see chapter 4 for more information.

Changes made using INSERT, UPDATE and DELETE are not made permanent until explicitly or implicitly they are committed.  See next section for more information.

 

Transaction

A transaction is a collection of statements used to perform a single task. These statements are logically related as they perform a single task. All these statements must be executed to successfully complete the task. If any of the statements fails then the all the statements that were executed prior to the statement that failed should be undone otherwise data in the database becomes invalid and inconsistent.

The following example will illustrate the process.

Assume that faculty with code kl (Kevin Loney) is leaving the institute. So all his batches are to be assigned to jc (Jason Couchman). For this the following steps are to be taken.

*  Change FCODE of all the batches that are currently being handled by kl to jc.

*  Delete rows from COURSE_FACULTY where FCODE is kl.

*  Delete row from FACULTY where FCODE is kl.

That means the following are the commands to be executed to perform the above-mentioned task.

update  batches  set  fcode = ‘jc’ where  fcode = ‘kl’;

delete from  course_faculty where  fcode = ‘kl’;

delete from  faculty where  fcode = ‘kl’;

It is important to make sure that all three statements are either successfully completed or all of them are rolled back. To ensure this Oracle provides transaction mechanism.

If UPDATE command in the above set of commands begins the transaction then only COMMIT command is given after the second DELETE is executed, the changes are committed. If ROLLBACK command is given then all the changes up to UPDATE will be rolled back.

So COMMIT and ROLLBACK command are used to ensure either everything is committed or everything is rolled back.

A transaction is a collection of statements   which is to be either completely done or  not  done at all.  In other words the process should not be half-done.  That means ALL or NOTHING.

A transaction begins when previous transaction ends or when the session begins. A transaction ends when COMMIT or ROLLBACK is issued.  See figure 1.

A new session starts when you connect to Oracle. For example, when you log on using SQL*PLUS you start a new session. When you exit SQL*PLUS the session is terminated.   

COMMIT Command

Used to commit all changes made since the beginning of the transaction.  It does the following.

*  Makes all the changes made to database during transaction permanent

*  Unlocks the rows that are locked during the transaction.

*  Ends the transaction.

*  Erases all savepoints in the current transaction(covered later).

4.                   

Changes made to database such as inserting rows, deleting rows, and updating rows are not made permanent until they are committed.

Implicit Commit

Oracle Implicitly issues commit in the following cases.

*  Before a DDL command.

*  After a DDL command.

*  At normal disconnect from the database. For example, when you exit SQL*PLUS using EXIT command.

Note: It is very important to remember not to mix DML commands with DDL command as the later commands are going to issue COMMIT, which might commit incomplete changes also.

ROLLBACK Command

Used in transaction processing to undo changes made since the beginning of the transaction.

*  Undoes the changes made to database in the current transaction

*  Releases locks held on rows duration transaction.

*  Ends the transaction.

*  Erases all savepoints in the current transaction (covered later).

ROLLBACK SEGMENT

In order to rollback changes that were made, Oracle has to store the data that was existing prior to the change so that previous data can be restored when user rolls back the changes. ROLLBACK SEGMENT is the area where Oracle stores data that will be used to roll back the changes. Every transaction is associated with a ROLLBACK SEGMENT. 

Generally a few ROLLBACK SEGMENTS are created at the time of creating database.  Database Administrator can create some more rollback segments depending upon the requirement using CREATE ROLLBACK SEGMENT command. Oracle assigns one rollback segment to each transaction. It is also possible to associate a transaction with a particular rollback segment using SET ROLLBACK SEGMENT command. It is not relevant to discuss more about the way ROLLBACK SEGMENT functions in a book like this. Please see Oracle Concepts for more information about ROLLBACK SEGEMENT.

SAVEPOINT Command

Savepoint is used to mark a location in the transaction so that we can rollback up to that mark and not to the very beginning of the transaction.

The following is the syntax of SAVEPOINT.

SAVEPOINT   savepoint_name;

A single transaction may also have multiple savepoints. The following example illustrates how to use savepoints.

SQL> update . . .

SQL> savepoint   s1;

Savepoint created.

SQL> insert  .... ;

SQL> insert  .... ;

SQL> savepoint  s2;

Savepoint created.

SQL> delete   ...;

SQL> rollback to s1;

Rollback complete.

SQL> update…

SQL> commit;

In the above example, ROLLBACK   TO  S1;  will  undo changes made from  savepoint  S1 to the point of rollback. That means it will undo INSERT, INSERT, and even DELETE given after second savepoint. It doesn’t undo UPDATE given before first savepoint. The COMMIT given after last UPDATE is going to commit first UDPATE and last UPDATE. Because all the remaining are already rolled back by ROLLBACK TO S1 command.

If we have given ROLLBACK TO S2; then it would have undone only DELETE given after the second savepoint and remaining statements (update, insert, insert, and update) would have been committed.

Locking

It is important from database management system’s point of view to ensure that two user are not modifying the same data at the time in a destructive manner.

Let us consider the following example to understand what will happen if two users are trying to update the same data at the same time.

Assume we have PRODUCTS table with details of products. Each product is having quantity on hand (QOH).  Transactions such as sales and purchases are going to modify QOH column of the table.

The following are the steps that might take place when two transactions – one sale and one purchase – are taking place.

5.    Assume QOH of product 10 is 20 units.

6.    At 10:00 USER1 has sold 5 units and updated QOH as follows but has not committed the change. After UPDATE command QOH will be 15.

update products set qoh = qoh – 5 where prodid = 10;

7.    At 10:11 USER2 has purchased 10 units and updated QOH as follows and committed. After UPDATE command QOH will be 25 as 10 it added to 15.

update products set qoh = qoh + 10 where prodid = 10;

8.    If at 10:12 USER1 has rolled back the UPDATE then data that was there before the UPDATE should be restored i.e.20.  But the actual data should be 30 as we added 10 units to it at 10:11 and committed.

As you can see in the above example, if two users are trying to update the same row at the same time the data may be corrupted. As shown in the above example at the end of the process QOH should be actually 30 but it is not only 20.

It is possible to ensure that two transactions are interfering with each other by locking the rows that are being modified so that only one transaction at a time can make the change.

Oracle ensures that only one transaction can modify a row at a time by locking the row once the row is updated. The lock will remain until the transaction is completed.

Oracle also ensures that other users will not see the changes that are not committed. That means if transaction T1 has updated a row then until the transaction is committed no other transaction in the system will see the changes made by T1. Instead other transactions will see only the data that was existing before the change was made.

The following scenario will illustrate the process in detail.

1.    Assume QOH of product 10 is 20.

2.    Transaction T1 has issued UPDATE command to update QOH of product 10. Oracle locks the row that is updated and does not allow other transactions to update the row. However, it is possible to read the data from the row.

update products set qoh = qoh + 5 where prodid = 10;

3.    If T1 has issued SELECT to retrieve data then it will get 25 in QOH of product 10.

4.    If T2 has issued SELECT command, it will see only 20 in QOH of product 10. This is because no uncommitted changes will be available to other transactions.

5.    If T2 is trying to update product 10 then Oracle will cause transaction T2 to wait until transaction T1 (that holds lock on this row) is completed. Oracle will wail for lock to be released indefinitely.

6.    If transaction T1 is committed then change is made permanent and lock will be released.  Now it is possible for other transactions to see the updated data and even update the row if required.

The following are the important points to be remembered about Oracle’s locking mechanism.

*  Locking in Oracle is automatic and transparent. That means we never need to ask Oracle to lock row that is being modified. Locking is transparent means user is not aware of the locking process. It happens automatically and in the background.

*  Oracle locks the row that is being updated. That means locking is row-level. Other levels that are in user are - page-level and table-level.

*  Oracle releases locks held by a transaction when transaction is completed either successfully – using COMMIT – or unsuccessfully – using ROLLBACK.

*  If a transaction is trying to update a row and if row is already locked then Oracle will wait for the row that is locked to be unlocked indefinitely. It is because of the fact that rows are locked for a small duration in a typical production system. So Oracle prefers to wait to cause any error.

*  It is possible to lock table manually using LOCK TABLE command.

Locking the rows that are being updated is an important part of Oracle. It ensures that no two transactions can update the same row at the same time. Locking mechanism is followed by all database management systems. But some smaller database management systems follow page-level locking where not the exact row that is being modified is locked instead the entire page in which the row exists is locked.

Read Consistency

When one user is trying to read data while other user is updating it, we might encounter a scenario where the data to be read by reader has already been modified by writer. In such cases Oracle provides read consistency to reader.

Read consistency ensures that whatever a user is reading is consistent to the point of starting the query.  The following example will illustrate this mechanism.

*  USER1 has issues a query to read data from SALES table at 10:00.  The table contains 20000 rows and it may take 4 minutes to retrieve data completely. As user is only reading the data no lock is obtained on the table.

*  USER2 has updated a row that is at 15000^th row in the table at 10:01 and committed the change.

*  USER1 has reached 15000^th row at 10:03. Now USER1 get the data that was before the change as by the time query started the change didn’t take place. That means Oracle has to provide the data to USER1 that existed at the time of starting the query – 10:00.

*  Any changes whether committed or not made after 10:00 should not be retrieved by USER1.

Oracle uses ROLLBACK SEGMENT to store the data that was before the change to provide read consistency.

 

Summary

ALTER TABLE command is used to alter the structure of the table. It is possible to add column or constraint, modify attributes such as datatype and length of existing columns, and drop column or constraint.

UPDATE and DELETE commands are used to update and delete rows of the table respectively.  Changes made using DML commands are not committed until they are explicitly (using COMMIT command) or implicitly committed.  ROLLBACK command is used to roll back the changes since the beginning of the transaction, which is a collection of statements to perform a single task.  SAVEPOINT command is used to mark a point in the transaction so that changes made after the savepoint can be rolled back.

Oracle locks the rows that are being update to ensure only one transaction can update a row at a time. Oracle also ensures that the data retrieved by query is consistent to the point of starting the query – read consistency.

Arithmetic and Date functions

*  What is a function?

*  Types of functions

*  Arithmetic functions

*  Dual table

*  Date arithmetic

*  Date functions

*  Summary

*  Exercises

What is a function?

A function is similar to an operator in operation.  A function is a name that performs a specific task.  A function may or may not take values (arguments) but it always returns a value as the result. If function takes values then these values are to be given within parentheses after the function name. The following is the general format of a function.

function [(argument-1, argument-2,...) ]

If the function doesn’t take any value then function name can be used alone and even parentheses are not required.

Types of functions

Functions are classified based on the type of data on which they perform the operation. The following are the different types of functions available in Oracle.

*  Arithmetic Functions.

*  Date & Time functions.

*  String functions.

*  Conversion functions.

*  Miscellaneous functions.

*  Group functions.

Arithmetic functions perform take numeric data; date functions take date type data and string functions take strings. Conversion functions are used to convert the given value from one type to another. Miscellaneous functions perform operations on any type of data.  Group functions are used to perform operations on the groups created by GROUP BY clause.

1.            Note: Group functions or aggregate functions perform their operation on a group (a collection of rows).  All the remaining functions are called as single-row functions as they return a result for each row.

Arithmetic Functions

Arithmetic functions take numbers and perform arithmetic operations. Table 1 lists the   arithmetic functions available in Oracle.

Function	Description
ABS(value)	Absolute value of the given value.
CEIL(value)	Smallest integer larger than or equal to value
FLOOR(value)	Largest integer smaller than or equal to value
MOD(value,divisor)	Remainder of the division between the value and divisor.
POWER(value,exponent)	Value is raised to exponent.
ROUND(value[,precision])	Rounds value to precision. Precision can be negative if rounding is to be done on the left of the decimal point.
TRUNC(value[,precision])	Truncates instead of rounding. Otherwise same as ROUND.
SQRT(value)	Square root of value.
SIGN(value)	Returns 1 if value  > 0, -1 if value <0, 0 if value = 0

Table 1: Arithmetic Functions.

The following are a few examples of arithmetic functions.

select mod(10,4) from dual;

The above command displays 2 as the result as the remainder of the division between 10 and 4 is 2.

ROUND and TRUNC functions

ROUND and TRUNC functions are used to round and truncate the given number to the given number of digits (either on the right or left of the decimal point).  ROUND takes the leftmost digit that is being lost and accordingly adds one to the rightmost digit. TRUNC doesn’t take the leftmost digit into account. It just truncates the given number to the given precision.

select round(1047.785,2), trunc(1047.785,2) from dual;

The above command will display 1047.79 and 1047.78.   This is because TRUNC doesn’t take the digits being lost into account.

The following examples illustrate the result of ROUND and TRUNC functions with positive and negative precision.

Function	Result
ROUND(1295.356,2)	1295.36
TRUNC(1295.356,2)	1295.35
ROUND(1295.356,0)	1295
ROUND(1285.356, -1)	1290
TRUNC(1285.356, -1)	1280
ROUND(1295,-2)	1300

When precision in ROUND is positive then rounding takes place to the specified number of digits on the right of decimal point. For example, if precision is 2 it means   round number to 2 digits on the right of the decimal point. When   precision is negative; it means number is to be rounded to the left of the decimal point. In both the cases if the leftmost digit of the digits being lost is  >= 5 then one is added to the rightmost digit of the digits that are retained.

In the example given above, ROUND (1285.356, -1) will result in 1290.  Because   one digit on the left of decimal point is to be set to zero, means 5 is replaced with 0. As the digit that is replaced with zero (5 in this case) is  >= 5 one is added to digit on the left of it (8) to make it 9. So the result is 1290.

ROUND (1295,-2)  results in  1300. This is because in 1295 two digits on the left of decimal point  are set to zeroes( 1200) and  as  leftmost  digit  out of  digits that are set to  zero is 9 one is added to 2, which is the rightmost  digit in the remaining portion. This makes it 1300.

2.            Note: ROUND and TRUNC functions can also be used with date data type. More on this later in this chapter.

CEIL & FLOOR functions

CEIL produces the smallest integer that is greater than or equal to the given value. Whereas FLOOR is opposite of CEIL. The following table illustrates the usage of these two related functions.

Function	Result
CEIL(1.3)	2
CEIL(2)	2
CEIL(-2.3)	-2
FLOOR(1.3)	1
FLOOR(2)	2
FLOOR(-2.3)	-3

DUAL Table

This is a table that is made available to every account in Oracle database. This table contains one row and one column.  This table can be used with SELECT when result of the expression is to be displayed only for once.

SQL> describe dual

 Name                      Null?    Type

 ------------------------- -------- ----

 DUMMY                              VARCHAR2(1)

For example, to display the current system date the following SELECT can be used:

SQL> select sysdate from dual;

SYSDATE

--------

24-AUG-01

As DUAL table contains only one row, the result is displayed only for once.

The following example displays the course fee by rounding it to thousands.

select ccode,name, round(fee,-3) “fee”

from courses;

CCODE NAME                                 fee

----- ------------------------------ ---------

ora   Oracle database                    5000

vbnet VB.NET                              6000

c     C programming                       4000

asp   ASP.NET                              5000

java  Java Language                       5000

xml   XML Programming                4000

Scientific functions

The following are the other arithmetic functions that are rarely used in business applications.  They are mainly used in scientific applications.  However as they are available in Oracle, we will get to know them.

Function	Description
ACOS(value)	Arc cosine of the given value.  The value is in the range –1 to 1 and return value is in radians.
ASIN(value)	Arc sine of the value.
ATAN(value)	Arc tangent of the value.
COS(value)	Cosine of the value.
COSH(value)	Hyperbolic cosine of the value.
EXP(value)	Return e (2.71828183) raised to value power.
LN(value)	Natural logarithm of value.
LOG(base,value)	Logarithm with base of value.
SIN(value)	Sine of the value.
SINH(value)	Hyperbolic sine of the value.
TAN(value)	Tangent of the value.
TANH(value)	Hyperbolic tangent of the value.

Table 2: Scientific Functions.

The following are few examples of these scientific functions.

select exp(2) from dual;

   EXP(2)

---------

7.3890561

select log(10,10) from dual

LOG(10,10)

----------

         1

SELECT COS(180 * 3.14159265359/180)

from dual

COS(180*3.14159265359/180)

--------------------------

                        -1

Date Arithmetic

When arithmetic operators are used with DATE datatype it is called as Date Arithmetic.

The following are the possible arithmetic operations on DATE type data.

*  Adding a number to date to get the date after the given number of days.

*  Subtracting a number from a date to get the date before the given number of days.

*   Subtracting two dates to get the number of days between these two dates.

The following example displays the name of the student and number of days between system date and date of joining.

select name, sysdate - dj from students;

NAME                             No Days

------------------------------ ---------

George Micheal                 226.29194

Micheal Douglas                225.29194

Andy Roberts                   225.29194

Malcom Marshall                220.29194

Vivan Richards                 220.29194

Chirs Evert                    222.29194

Ivan Lendal                    221.29194

George Micheal                 176.29194

Richard Marx                   140.29194

Tina Turner                    140.29194

Jody Foster                    139.29194

In Oracle DATE datatype stores date and time. At the time of storing a date If time is not given then it will be set to 0 hours, 0 minutes and 0 seconds (beginning of the day or 12:00 a.m.).

Default date format – DD-MON-YY – doesn’t include time. However time is always stored along with date and if you want to at any time you can extract the time portion a date using TO_CHAR function, which will be discussed in the next chapter.

3.            Note: All comparisons between two dates include time portion also.

4.            Note: The fraction portion in the result between two dates indicates the difference in time.

The following example shows the due date for first installment (assuming first installment is to be paid within 10 days from the date of joining).

select name,dj, dj + 10 "Due Date" from students

NAME                           DJ        Due Date

------------------------------ --------- ---------

George Micheal                 10-JAN-01 20-JAN-01

Micheal Douglas                11-JAN-01 21-JAN-01

Andy Roberts                   11-JAN-01 21-JAN-01

Malcom Marshall                16-JAN-01 26-JAN-01

Vivan Richards                 16-JAN-01 26-JAN-01

Chirs Evert                    14-JAN-01 24-JAN-01

Ivan Lendal                    15-JAN-01 25-JAN-01

George Micheal                 01-MAR-01 11-MAR-01

Richard Marx                   06-APR-01 16-APR-01

Tina Turner                    06-APR-01 16-APR-01

Jody Foster                    07-APR-01 17-APR-01

The following query displays the details of the payment that were made in the last 3 days:

select * from payments

Where  sysdate – dp <= 3;

Date Functions

Date functions operate on values of DATE datatype.  Except MONTHS_BETWEEN all date functions return DATE data type. The following is the list of DATE functions.

Function	Description
ADD_MONTHS(date, count)	Adds count number of months to date.
MONTHS_BETWEEN (date1, date2)	Returns number of months between date1 and date2.
LAST_DAY(date)	Returns the last day of the month in which date is.
NEXT_DAY(date, ’day’)	Gives the date of next day after the date, where day name of the week like ‘Monday’.
NEW_TIME(date, ’this’, ’other’)	Returns time in other time zone for time of  this time zone.
ROUND(date)	Rounds the date depending upon the time. If time is at or after 12 hours then date is incremented. Time is always set to beginning of the day (0:0:0).
TRUNC(date)	Same as ROUND (date) but doesn’t increment date.

Table 3: DATE Functions.

Adding and subtracting months

You can add or subtract months from a date using ADD_MONTHS function. If the count is positive, that many months will be added. If count is negative that many months will be subtracted.

Adding months is the process where Oracle will give the date of next specified number of months.

In the following example, Oracle will add two months to system date:

SQL> Select sysdate, add_months(sysdate,2)

  2  From dual;

SYSDATE   ADD_MONTH

--------- ---------

25-AUG-01 25-OCT-01

If the target month doesn’t have the required day then the last day of the month will be taken. In the following example, 30-SEP-2001 is returned, as 31-SEP is not available. Oracle automatically adjusts the date according to the requirement.

SQL>  select add_months('31-aug-2001',1) from dual;

ADD_MONTH

---------

30-SEP-01

The following example will show the date on which the students of completed batches will be issued certificates assuming it will take 3 months time to issue certificates.

SQL> select bcode, ccode, enddate, add_months(enddate,3) "Cert. Date"

  2  from  batches

  3  where  enddate is not null;

BCODE CCODE ENDDATE   Cert. Dat

----- ----- --------- ---------

b1    ora   20-FEB-01 20-MAY-01

b2    asp   05-MAR-01 05-JUN-01

b3    c     27-FEB-01 27-MAY-01

b4    xml   30-MAR-01 30-JUN-01

b5    java  10-MAY-01 10-AUG-01

When the second parameter – count -  is negative, date is decremented by that many months.

The following example shows the date on which the admissions for running batches have started. For a batch admissions start exactly one month before the starting date.

select bcode, ccode, stdate, add_months(stdate,-1)

from  batches where enddate is null

BCODE CCODE STDATE    ADD_MONTH

----- ----- --------- ---------

b6    vbnet 12-JUL-01 12-JUN-01

b7    ora   15-AUG-01 15-JUL-01

Getting months between two dates

You can obtain the number of months between two dates using MONTHS_BETWEEN function.  The following query returns the number of months between starting date and ending date of all completed batches.

select bcode, ccode, stdate, enddate,

       months_between(enddate, stdate) "NO. Months"

from batches where enddate is not null;

BCODE CCODE STDATE    ENDDATE   NO. Months

----- ----- --------- --------- ----------

b1    ora   12-JAN-01 20-FEB-01  1.2580645

b2    asp   15-JAN-01 05-MAR-01  1.6774194

b3    c     20-JAN-01 27-FEB-01  1.2258065

b4    xml   02-MAR-01 30-MAR-01  .90322581

b5    java  05-APR-01 10-MAY-01  1.1612903

The fraction in the result is the number of days beyond the number of months. For example, the difference between 12-JAN-01 and 20-FEB-01 is 1.258.  It means there is one month and 26% of another month, which comes to 8 days.

5.            Note: The fraction is calculated based on a 31-day month and also considers time portion.

Use TRUNC function to ignore fraction in the result of MONTHS_BETWEEN as follows:

select bcode, ccode, stdate, enddate,

       trunc(months_between( enddate, stdate)) "NO. Months"

from batches where enddate is not null;

BCODE CCODE STDATE    ENDDATE   NO. Months

----- ----- --------- --------- ----------

b1    ora   12-JAN-01 20-FEB-01           1

b2    asp   15-JAN-01 05-MAR-01         1

b3    c     20-JAN-01 27-FEB-01            1

b4    xml   02-MAR-01 30-MAR-01        0

b5    java  05-APR-01 10-MAY-01          1

6.            Note: It is possible to send the return value of a function to another function.

The following query displays the batches that we completed in the last 6 months and duration is more that 1 month.

select bcode, ccode

from batches

where  months_between(sysdate,enddate) <= 6

       and months_between(enddate,stdate) > 1;

BCODE CCODE

----- -----

b2    asp

b3    c

b5    java

LAST_DAY function

LAST_DAY function returns the date of the last day of the month of the given date. The following statement displays the last day of the current month:

Select  sysdate, last_day(sysdate)

From  dual;

SYSDATE   LAST_DAY(

--------- ---------

25-AUG-01 31-AUG-01

The following query displays the due date by which first installment of each batch is to be paid.  LAST_DAY function return the last day of the month in which batch has started and if 5 is added to that then it will be 5^th of the next month – due date of first installment.

select  bcode,ccode, stdate, last_day(stdate) + 5 "Due Date"

from batches

BCODE CCODE STDATE    Due Date

----- ----- --------- ---------

b1    ora   12-JAN-01 05-FEB-01

b2    asp   15-JAN-01 05-FEB-01

b3    c     20-JAN-01 05-FEB-01

b4    xml   02-MAR-01 05-APR-01

b5    java  05-APR-01 05-MAY-01

b6    vbnet 12-JUL-01 05-AUG-01

b7    ora   15-AUG-01 05-SEP-01

Similarly assuming the batches are scheduled in the last week of previous month for each batch, the following query displays the date of last week:

select bcode, ccode, stdate, last_day(add_months(stdate,-1)) - 7 ||

               ' to ' ||  last_day(add_months(stdate), -1)

from batches

BCODE CCODE STDATE    Schd. Week

----- ----- --------- ----------------------

b1    ora   12-JAN-01 24-DEC-00 to 31-DEC-00

b2    asp   15-JAN-01 24-DEC-00 to 31-DEC-00

b3    c     20-JAN-01 24-DEC-00 to 31-DEC-00

b4    xml   02-MAR-01 21-FEB-01 to 28-FEB-01

b5    java  05-APR-01 24-MAR-01 to 31-MAR-01

b6    vbnet 12-JUL-01 23-JUN-01 to 30-JUN-01

b7    ora   15-AUG-01 24-JUL-01 to 31-JUL-01

NEXT_DAY function

This function returns the date of given weekday that is greater than the given date.  It takes weekday – Sunday, Monday etc. – and returns the date on which the coming weekday is falling. The return value is always grater than the given date.  The following example shows when is the next Friday.

select  sysdate, next_day(sysdate,'Fri')  from  dual;

SYSDATE   NEXT_DAY(

--------- ---------

25-AUG-01 31-AUG-01

If weekday of the given date and the given weekday happen to be the same  then   the  date of  coming weekday is returned. This is because the result of this function is always greater than the given date. See the following example where though the given date - 25-AUG - is Saturday NEXT_DAY returns the next Saturday and not the same date.

select  sysdate, next_day(sysdate,'Sat')  from  dual

SYSDATE   NEXT_DAY(

--------- ---------

25-AUG-01 01-SEP-01

But what if you want to get the same date, when  day of the week of the given date is same as the one asked for? The following query will return the same date if date happens to fall on the required weekday.

select  sysdate, next_day(sysdate - 1,'Sat')  from  dual

SYSDATE   NEXT_DAY(

--------- ---------

25-AUG-01 25-AUG-01

When one is subtracted from the system date, though the date happens to be on Saturday it become Friday (24-AUG) because of the subtraction. Then NEXT_DAY returns the 25-AUG as it is grater than the given date – 24-AUG.

ROUND  & TRUNC functions with dates

DATE data type contains both date and time.  ROUND and TRUNC function can be used to round or truncate the date based on the time portion.   The following query displays the date and time portion of system date using TO_CHAR function. It is suffice to know that TO_CHAR can be used to convert given date to character type using the given format.

Select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual

TO_CHAR(SYSDATE,'DD

-------------------

25-08-2001 18:42:08

7.            Note: TO_CHAR converts a DATE type data to character type. Please see next chapter  for more information on TO_CHAR.

ROUND function adds one day to the date if time portion of the date is greater than or equal to 12 noon.

select sysdate, to_char(round(sysdate),'dd-mm-yyyy hh24:mi:ss') “Round Date" from dual

SYSDATE   Round Date

--------- -------------------

25-AUG-01 26-08-2001 00:00:00

In the above query first ROUND function is used to round SYSDATE. As we have seen the time in SYSDATE is 18 hours, the date is incremented by one – 26-AUG.  ROUND always sets the time portion to 0:0:0 (as seen in the output of the query).

TRUNC function doesn’t increment the date based on the time, but it sets time portion to 0 hours, 0 minutes and 0 seconds.  The following query shows the result of TRUNC function.

select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') "Today",

       to_char( trunc(sysdate),'dd-mm-yyyy hh24:mi:ss')  "Truncated Date"

from dual

Today               Truncated Date

------------------- -------------------

25-08-2001 18:56:53 25-08-2001 00:00:00

8.            Note: Both ROUND and TRUNC set the time portion in the DATE data type to 12 A.M. (00:00:00).

The following query is used to displays the details of the payments made today.

select * from payments

where  dp = sysdate;

The above query always returns no rows selected.  This is because when Oracle compares two dates it takes date and time portions into account.  In the above query, though PAYMENTS table contains rows where DP is containing the same date as SYSDATE, the time portions will not match. The remedy is to ignore time portions and compare only date portions of the dates.  The following is revised query where we truncate the dates to set both the times to 0:0:0 so that only dates are compared as times are equal.

select * from payments

where  trunc(dp) = trunc(sysdate);

The following is another example where TRUNC is used to ignore time portion of DP and compare date with 10-apr-2001.

select * from payments

where  trunc(dp) = '10-apr-2001'

   ROLLNO DP           AMOUNT

--------- --------- ---------

       10 10-APR-01      4500

       11 10-APR-01      3500

Getting time in different time zone

NEW_TIME is used to return the time of the specified time zone for the time of the given time zone.

The following query displays what will be the time in PST for the time in GMT.

select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss')  GMT ,

          to_char(  new_time(sysdate,'GMT','AST'),'dd-mm-yyyy hh24:mi:ss')  AST

from dual

TO_CHAR(SYSDATE,'DD TO_CHAR(NEW_TIME(SY

------------------- -------------------

25-08-2001 19:35:36 25-08-2001 15:35:36

The following are a few of the available time zones. For complete lit, please see Oracle online documentation.

Time Zone	Meaning
EST , EDT	Eastern Standard or Daylight Time
GMT	Greenwich Mean Time
HST , HDT	Alaska-Hawaii Standard Time or Daylight Time.
PST , PDT	Pacific Standard or Daylight Time
AST , ADT	Atlantic Standard or Daylight Time

Summary

A function is to perform a single operation and return a value. Functions are of different types. Arithmetic functions are used to perform arithmetic operations. Date functions perform operations on date type data. Performing arithmetic operations on dates is called as date arithmetic. DUAL table is with SELECT command to display the result of expression that do not relate to any table.