How to add data in oracle

In Oracle, you can use the "insert" statement to add data to the table. This statement can insert one or more rows of tuple data into an existing table in the database. The syntax "insert into table name (column) Name 1, column name 2, column name 3....) values ​​(value 1, value 2, value 3....);".

The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.

In Oracle, you can use the "insert" statement to add data to the table.

The insert statement can insert data into a table, and can insert one row or multiple rows of tuple data into an existing table in the database.

Oracle syntax for inserting data:

insert into 表名（列名1,列名2,列名3.....）values(值1,值2,值3.....);

Syntax analysis:

1. The column name can be omitted, when the column name is not filled in , the default is all columns in the table, and the order of the columns is arranged in the order in which the table is created.

2. The number of column names and the number of values ​​must be consistent, and the type of values ​​must correspond to the type of columns.

3. When certain constraints are set on certain fields in the table, the value must be inserted according to the constraints of the fields. For example: the student information table (STUINFO) is set with a primary key (primary key field is STUID), so this field must be unique and cannot be repeated with the original data. Fields such as age, stuname, and calassno are required fields, so they must have values.

Oracle example of inserting data

Case 1: Insert a piece of data into the student information table (stuinfo):

insert into STUDENT.STUINFO (STUID, STUNAME, SEX, AGE, CLASSNO, STUADDRESS, GRADE, ENROLDATE, IDNUMBER)
values ('SC201801005', '龙七', '1', 26, 'C201801', '福建省厦门市XXX号', '2018', to_date('01-09-2018', 'dd-mm-yyyy'),
 '3503021992XXXXXXXX');
select * from student.stuinfo t where t.stuid='SC201801005';

The results are as follows:

Case 2: Insert duplicate data into the student information table (stuinfo):

insert into STUDENT.STUINFO (STUID, STUNAME, SEX, AGE, CLASSNO, STUADDRESS, GRADE, ENROLDATE, IDNUMBER)
values ('SC201801005', '龙七', '1', 26, 'C201801', '福建省厦门市XXX号', '2018', to_date('01-09-2018', 'dd-mm-yyyy'),
 '3503021992XXXXXXXX');

The results are as follows:

Extended knowledge: insert inserts a select result set

In Oracle , an INSERT command can insert a select result set into a table at one time.

The syntax structure is as follows:

INSERT INTO 表 SELECT 子句

Case: Insert the data of the table stuinfo_2018 backed up using the Oracle query (select) statement in the previous chapter into the table stuinfo at once:

delete  from student.stuinfo t where t.stuid in (select b.stuid from student.stuinfo_2018 b );
insert into student.stuinfo select * from student.stuinfo_2018;
select * from student.stuinfo;

The results are as follows:

Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of How to add data in oracle. For more information, please follow other related articles on the PHP Chinese website!