• Insert statement is used to insert record into the table but we have to specify each value for the column list. There are cases in which some value gets repeated for majority of the records and it is cumbersome each time filling the same value for such records. Values for some field gets changed only in exceptional cases while for majority record it will remain the same.
  • Say for example a column “physically challenged” may have majority “N/A” as value while very few cases will have value with “Yes” so inserting each time manually will waste our considerable amount of time.
  • We can specify default value for the column at the time of table creation.
  • If user left the field blank then default value will be inserted automatically.
  • Default value must be match with data type and size of the column on which default value has been set.
  • If user enters value for the default column then that value will be inserted.
  • Default key word is use to specified default value.
  • It saves lots of time as we do not need to enter value each time for the default column and oracle automatically fills value for us.

Syntax:

CREATE TABLE student (
                       roll_no number (3),
                       name varchar2 (15),
                       gen varchar2 (1) DEFAULT ‘M’,
                       city varchar2 (15)
                     );

Example:

INSERT INTO student(roll_no, name, city)
            VALUES (&roll_no, ‘&name’, ‘&city’);
  • Notice that we need not to include default value field in insert statement for the records having default value.