Use of default in Oracle

When creating a database table, you can specify a DEFAULT value (DEFAULT value ). Using default values for database fields helps isolate database design issues from application code.

You can use the alter table command to change the default value of a field at a later time. After the change, the application code will immediately start to use the new value.

It is important that the DEFAULT value is used only when a field is not specified in INSERT or MERGE or the DEFAULT keyword is used. If you do not explicitly declare a DEFULAT value, Oracle implicitly defines the DEFAULT value as NULL, and the DEFAULT keyword is the same. Starting from Oracle 9i, pseudo fields such as SYSDATE or CURRENT_TIMESTAMPE can be used in the DEFAULT clause. For example:

Create table t1
(
Id $ integer not null,
Charcol char default 'y ',
Datecol date default sysdate,
Strcol varchar2 (30) default user,
Intcol integer default 12
);
Insert into t1 (id $) values (1 );
Select * from t1;

ID $ C DATECOL STRCOL INTCOL
------------------------------------------------------------
1 Y 28-MAY-04 SCOTT 12

Compared with the INSERT, MERGE, or UPDATE syntax, the DEFAULT keyword does not seem necessary. However, if you want to use all the DEFAULT values when inserting a column of data, then you will not think so. Oracle does not accept insert into <table> or insert into <table> VALUES () as valid SQL statements. At least one field must be specified, but the DEFAULT keyword can be used to allow the use of the DEFAULT value, rather than the hard-coded value. Therefore, the following is a valid syntax, which uses all DEFAULT values to create a row of records.

Create table t2 (charcol char default 'y', datecol date default sysdate );
Insert into t2 (charcol) values (default );
Select * from t2;

C DATECOL
----------
Y 28-MAY-04

A common problem
A common problem is to simulate the Autonumber function of other database providers. This function uses a certain sequence of numbers to automatically fill a field. In Oracle databases, an ordered number cannot be specified as the DEFAULT value of a field. However, you can use a trigger to simulate this function. Even if a field is declared as not null, you can ignore this field in the INSERT statement and use a trigger to fill the value of this field. Note that using the DEFAULT keyword is more readable than using an explicit NULL keyword.

Create sequence t3_seq;
Create table t3 (id $ integer constraint t3_pk primary key );
Create or replace trigger t3_autonumber
Before insert on t3 for each row
Begin
If: new. id $ is null then
Select t3_seq.nextval into: new. id $ from dual;
End if;
End;
/
Show errors;

Insert into t3 (id $) values (default );
Select * from t3;

ID $
----------
1

You can use the default value in the SYS_CONTEXT value set to fill the field and collect important information about a session:

Create table t4
(
When date default SYSDATE,
Db_domain varchar2 (200) default SYS_CONTEXT ('userenv', 'db _ DOMAIN '),
Host varchar2 (256) default SYS_CONTEXT ('userenv', 'host '),
Ip_address varchar2 (256) default SYS_CONTEXT ('userenv', 'IP _ address '),
Language varchar2 (256) default SYS_CONTEXT ('userenv', 'language '),
Protocol varchar2 (200) default SYS_CONTEXT ('userenv', 'network _ Protocol '),
Terminal varchar2 (200) default SYS_CONTEXT ('userenv', 'terminal ')
);
Insert into t4 (when) values (default );
Select * from t4;

WHEN
---------
DB_DOMAIN
------------------------------------------------------------------------------
HOST
------------------------------------------------------------------------------
IP_ADDRESS
------------------------------------------------------------------------------
LANGUAGE
------------------------------------------------------------------------------
PROTOCOL
------------------------------------------------------------------------------
TERMINAL
------------------------------------------------------------------------------
28-MAY-04
Scott. bn
MSHOME \ SCOTT-LAP
AMERICAN_AMERICA.AL32UTF8
SCOTT-LAP

You can also use the pseudo field SYS_GUID to fill a field. It has the advantage of global uniqueness and does not require sequential numbers or trigger Overhead:

Create table t5 (id $ raw (16) default sys_guid ()
Constraint t5_pk primary key );
Insert into t5 (id $) values (default );
Select * from t5;

ID $
--------------------------------
643718A07DCC43F2AC95312FD43617BA