4.1 Insert new records
problem
Insert a new record into the table.
Solution
Insert a line using an insert statement with a values clause.
insert into dept(deptno,dname,loc)
values(19,'xgj','BEIJING');discuss
As a simple way, you can omit the list of fields in insert statements.
However, if the statement does not list the target fields in the row to be inserted, all columns in the table must be inserted. It should be noted that the order of the values listed in the insert value list must be exactly the same as that listed in the select* query statement.
4.2 Insert default values
problem
Defining tables defines default values for certain columns.
create table test_xgj (
c1 varchar2(10) default 'Default 1',
c2 varchar2(10) default 'Default 2',
c3 varchar2(10) default 'Default 3',
c4 date default sysdate
);Solution
SQL> insert into test_xgj(c1,c2,c3)
values (default , null ,'Hand input');
SQL> commit ;
SQL> select * from test_xgj;
C1 C2 C3 C4
---------- ---------- ---------- -----------
//default1 Hand input 2017-03-05
discuss
The default keyword in the value list inserts the default value for the corresponding column, which is defined when the table is created. This keyword can be used in all dbms.
Matters needing attention:
- If there are no columns with default values in the insert statement, default values, such as C4, are added.
- If a column contains default values, you need to use the default keyword to add default values, such as C1.
- If NULL or other values have been displayed, no default values, such as C2, C3, will be generated.
4.3 Prevent insertion of columns
problem
For example, what if we want the default value of the C4 column to be SYSDATE, which is generally used to record the time of data generation and not allow manual entry?
Solution
We can create a View that does not contain a C4 column, and add data through this VIEW.
SQL> create or replace view test_xgj_view as
select c1 , c2 ,c3 from test_xgj ;
SQL > insert into test_xgj_view (c1, c2, c3) values ('default value 2', NULL,'C3');
SQL> commit ;
- Query the test_xgj table and find that although C4 is not inserted, it also has value because default value is set.
SQL> select a.* from test_xgj a ;
C1 C2 C3 C4
---------- ---------- ---------- -----------
Default value 2 C3 2017-03-05
Default 1 hand input 2017-03-05discuss
Be careful:
By view ing new data, you can no longer use the keyword default.
SQL> insert into test_xgj_view(c1,c2,c3) values (default ,NULL ,'C3');
insert into test_xgj_view(c1,c2,c3) values (default ,NULL ,'C3')
ORA-32575: Explicit column default is not supported for modifying views
4.3 Definition and data of replication table
Solution
We can copy table EMP with one statement
create table emp2 as select * from emp ;You can also copy the definition of a table before adding new data.
create table emp2 as select * from emp where 1=2;Note: Replicated tables do not contain constraint information such as default values. After replicating tables in this way, it is necessary to reconstruct default values, indexes and constraints.
Give an example:
SQL> desc test_xgj;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
C1 VARCHAR2(10) Y 'Default 1'
C2 VARCHAR2(10) Y 'Default 2'
C3 VARCHAR2(10) Y 'Default 3'
C4 DATE Y sysdate
--The first way
SQL> create table test_xgj_2 as select * from test_xgj ;
Table created
SQL> desc test_xgj_2;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
C1 VARCHAR2(10) Y
C2 VARCHAR2(10) Y
C3 VARCHAR2(10) Y
C4 DATE Y
SQL>
---The second way
SQL> create table test_xgj_copy as select * from test_xgj where 1=2 ;
Table created
SQL> desc test_xgj_copy ;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
C1 VARCHAR2(10) Y
C2 VARCHAR2(10) Y
C3 VARCHAR2(10) Y
C4 DATE Y 4.4 Restrict Data Entry with check option
If the view definition includes conditions (such as the WHERE clause) and its intention is to ensure that any INSERT or UPDATE statement that references the view applies the WHERE clause, the view must be defined using WITH CHECK OPTION. This option ensures the integrity of the data being modified in the database. If a condition is violated during an INSERT or UPDATE operation, an SQL error is returned.
Examples are given to illustrate:
We create a view and use with check option to restrict the view. Then let's look at the results contained in the view.
SQL> create or replace view xgj
2 as
3 select empno,ename from emp where ename like 'J%'
4 with check option;
View created
SQL> select * from xgj;
EMPNO ENAME
----- ----------
7566 JONES
7900 JAMES
SQL> update xgj set ename='XGJ' where empno=7566;
update xgj set ename='XGJ' where empno=7566
ORA-01402: view WITH CHECK OPTION where-clause violation
--If the update meetswith check point Yes, you can.
SQL> update xgj set ename='Jack' where empno=7566;
1 row updated
SQL> rollback;
Rollback complete
ORA-01402: View WITH CHECK OPTIDN violates where clause error, why?
This is because we specified the witch check option keyword when creating the view earlier, that is to say, every updated data still needs to satisfy the where condition specified when creating the view, so we have an error ORA-01402 here.
But when it comes to illustration, we can delete the data in the view even though we specify with check option. For example, in the example above, we can use
delete from xgj where empno = 7566;Usage of INSERT WITH CHECK OPTION
insert into (<select clause> WITH CHECK OPTION) values (...)
SQL> insert into (select object_id,object_name,object_type from xgj where object_id<1000 WITH CHECK OPTION)
2 values(999,'xxx','xxxx');This grammar looks very special. In fact, insert is inserted into the table in subquery. However, insert is not allowed unless the where condition in subquery is satisfied.
If the inserted column is not in the subquery where condition as a check, then insertion is also not allowed.
If WITH CHECK OPTION is not added, it will not be checked at insertion time.
Note here that subquery is not actually executed.
4.5 Multi-table Insert Statement
oracle can insert data into multiple tables with an insert statement from 9i
Oracle Insert all has three situations:
I. Unconditional INSERT ALL
Conditional INSERT ALL
III. Conditional INSERT FIRST
Grammar:
INSERT [ALL] [conditional_insert_clause]
[insert_into_clause values_clause](subquery)subquery: subquery statement, which can be any valid select statement
conditional_insert_clause is as follows:
[ALL][FIRST]
[WHEN condition THEN][insert_into_clause values_clause]
[ELSE] [insert_into_clause values_clause]Unconditional INSERT ALL
grammar
INSERT ALL
insert_into_clause values_clause_1
[insert_into_clause values_clause_2]
......
Subquery； Examples:
INSERT ALL
INTO sal_history(emp_id,hire_date,salary) values (empid,hiredate,sal)
INTO mgr_history(emp_id,manager_id,salary) values (empid,hiredate,sal)
SELECT employee_id empid,hire_date hiredate,salary sal,manager_id mgr
FROM employees
WHERE employee_id>200; 1. Specify that all the following multi-table insert_in_clauses perform unconditional multi-table inserts;
2. For each row returned by a subquery, the Oracle server executes each insert_in_clause once.
Conditional INSERT ALL
INSERT ALL
WHEN condition THEN insert_into_clause values_clause
[WHEN condition THEN] [insert_into_clause values_clause]
......
[ELSE] [insert_into_clause values_clause]
Subquery； Examples:
Insert All
when id>5 then into z_test1(id, name) values(id,name)
when id<>2 then into z_test2(id) values(id)
else into z_test3 values(name)
select id,name from xgj; 1. Specify conditional_insert_clause to perform a conditional multi-table insertion.
2. The Oracle server filters each insert_in_clause through the corresponding WHEN conditions to determine whether the insert_in_clause is executed or not.
3. A single multi-table insertion statement can contain up to 127 WHEN clauses.
Conditional INSERT FIRST
The difference between FIRST and ALL is that when the first clause evaluates true, it stops evaluating the WHEN clause, and ALL does not matter whether the evaluation is true or not.
grammar
INSERT FIRST
WHEN condition THEN insert_into_clause values_clause
[WHEN condition THEN] [insert_into_clause values_clause]
......
[ELSE] [insert_into_clause values_clause]
Subquery； 1. The Oracle server evaluates each WHEN clause that appears in the order of statements.
2. If the value of the first WHEN clause is true, the Oracle server executes the corresponding INTO clause for a given row and skips the following WHEN clause (the latter when clause no longer considers the record satisfying the first When clause, even if the record satisfies the conditions in the when clause).
Note: Constraints on multi-table INSERT statements
a. You can only insert multiple tables on tables instead of views.
b. You can't insert a multi-table into a remote table.
c. When performing a multi-table insert, you cannot specify a table set expression.
d. In a multi-table insert, all insert_in_clauses cannot be combined to specify more than 999 entry columns;
e. Rollback works only when all table data in insert_in_clauses is not updated.
4.6 Delete records that violate reference integrity
ORA-02298 failed to find the parent keyword
delete from emp
where not exists (
select null from dept where dept.deptno=emp.deptno
);4.7 Delete records with duplicate names
problem
The data are as follows:
SQL> create table xgj (id integer, name varchar(10));
Table created
SQL> INSERT INTO xgj VALUES (1, 'NAPOLEON');
1 row inserted
SQL> INSERT INTO xgj VALUES (2, 'DYNAMITE');
1 row inserted
SQL> INSERT INTO xgj VALUES (3, 'DYNAMITE');
1 row inserted
SQL> INSERT INTO xgj VALUES (4, 'SHE SELLS');
1 row inserted
SQL> INSERT INTO xgj VALUES (5, 'SEA SHELLS');
1 row inserted
SQL> INSERT INTO xgj VALUES (6, 'SEA SHELLS');
1 row inserted
SQL> INSERT INTO xgj VALUES (7, 'SEA SHELLS');
1 row inserted
SQL> commit ;
Commit complete
SQL> DYNAMITE and SEA SHELLS duplicate. Now the duplicate name in the table is required to reserve only one line. What should we do instead?
Solution
Processing data needs to be careful, and the results of the changes should be confirmed before they are submitted.
Here are three approaches
Judging by the same name and different id
First query the data and confirm it is correct
select *
from xgj a
where exists (select null
from xgj b
where a.name = b.name
and b.id > a.id);
ID NAME
-------- ----------
2 DYNAMITE
5 SEA SHELLS
6 SEA SHELLSdelete from xgj a where exists (select null from xgj b where a.name = b.name and b.id > a.id );When deleting data in this way, the composite index needs to be reconstructed.
create index idx_name_id on xgj(name,id)；Use rowid instead of id
select *
from xgj a
where exists (select null
from xgj b
where a.name = b.name
and b.rowid > a.rowid);
delete from xgj a
where exists (select null
from xgj b
where a.name = b.name
and b.rowid > a.rowid);Because we don't need to associate id columns, we just need to create a single column index.
create index idx_name on xgj(name);The analysis function generates the ordinal number according to the name grouping, and then deletes the data whose ordinal number is greater than 1.
View the data to be deleted
select rowid as rid ,
name,
row_number() over(partition by name order by id) as seq
from xgj;
RID NAME SEQ
------------------ ---------- ----------
AAAzBkAAIAAOet8AAB DYNAMITE 1
AAAzBkAAIAAOet8AAC DYNAMITE 2
AAAzBkAAIAAOet8AAA NAPOLEON 1
AAAzBkAAIAAOet8AAE SEA SHELLS 1
AAAzBkAAIAAOet8AAF SEA SHELLS 2
AAAzBkAAIAAOet8AAG SEA SHELLS 3
AAAzBkAAIAAOet8AAD SHE SELLS 1
7 rows selectedDelete data: Delete data with serial number > 1 after extracting duplicate data with analysis function
delete from xgj
where rowid in (select rid
from (select rowid as rid,
name,
row_number() over(partition by name order by id) as seq
from xgj)
where seq > 1);Of course, there are other ways.