Oracle Database之数据表增删改

最新推荐文章于 2021-04-11 23:42:59 发布

置顶 mrdavidtian

最新推荐文章于 2021-04-11 23:42:59 发布

阅读量750

点赞数

分类专栏： ------ Oracle 文章标签： Oracle Table DML 增删改 MERGE INTO

本文链接：https://blog.csdn.net/jssg_tzw/article/details/53487822

版权

------ Oracle 专栏收录该内容

26 篇文章 4 订阅

订阅专栏

DML是Data Manipulation Language的缩写，DML语句主要用于数据库表、视图的操作。

1. 一般INSERT语句插入表数据

--语法：

INSERT INTO [schema.]table[@db_link][(column1[,column2]...)]]
[VALUES (expression1[,expression2]...)]|[subquery];

--参数说明：

TABLE: 要插入的表名；
DB_LINK: 数据库链接名；
Column1, Column2: 表的列名；
Expression: 表达式；
Subquery: 子查询语句，可以是任何合法的SELECT语句；
Values: 给出要插入的值列表；

示例1:

--创建表DWH_USER_INF
create table dwh_user_inf
(
	user_id number,
	user_num varchar2(20),
	user_name varchar2(30),
	nickname  varchar2(30),
	status  varchar2(10),
	gender  varchar2(1),
	birthday date,
	email varchar2(50),
	telephone varchar2(20),
	mobile varchar2(20),
	address varchar2(300),
	department_id number
);

--创建序列：(序列创建语法参考： Oracle Database之序列(Sequence))
create sequence DWH_USER_INF_SEQ
INCREMENT BY 1
START WITH 1
MINVALUE 1
MAXVALUE 99999999999999999999999999999
NOCYCLE
CACHE 50;

--用INSERT INTO....VALUES插入数据：

insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL, 'STK00000001','david.tian','davide','Active','M',to_date('12-SEP-84','DD-MON-YY'),'david.tian@gmail.com','+86 510 8555 5555','13912345678','O Park, Xinhu District, Wuxi, Jiangsu, China',10);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL, 'STK00000002','black.xie','black','Active','M',to_date('12-AUG-85','DD-MON-YY'),'black.xie@gmail.com','+86 510 8555 5554','13912345671','O Park, Xinhu District, Wuxi, Jiangsu, China',10);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL, 'STK00000003','josen.zhang','josen','Active','M',to_date('12-JUN-85','DD-MON-YY'),'josen.zhang@gmail.com','+86 510 8555 5553','13912345672','O Park, Xinhu District, Wuxi, Jiangsu, China',11);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL, 'STK00000004','kaishen.yang','kaishen','Active','M',to_date('12-SEP-85','DD-MON-YY'),'kaishen.yang@gmail.com','+86 510 8555 5552','13912345673','O Park, Xinhu District, Wuxi, Jiangsu, China',12);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL, 'STK00000005','fab.yin','fab','Active','M',to_date('12-SEP-88','DD-MON-YY'),'fab.yin@gmail.com','+86 510 8555 5551','13912345674','O Park, Xinhu District, Wuxi, Jiangsu, China',12);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL, 'STK00000006','klaus.he','klaus','Active','M',to_date('12-SEP-86','DD-MON-YY'),'klaus.he@gmail.com','+86 510 8555 5550','13912345675','O Park, Xinhu District, Wuxi, Jiangsu, China',14);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL, 'STK00000007','alfred.li','alfred','Active','M',to_date('11-SEP-84','DD-MON-YY'),'alfred.li@gmail.com','+86 510 8555 5556','13912345676','O Park, Xinhu District, Wuxi, Jiangsu, China',13);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL, 'STK00000008','tom.deng','tom','Active','M',to_date('12-JAN-85','DD-MON-YY'),'tom.deng@gmail.com','+86 510 8555 5557','13912345677','O Park, Xinhu District, Wuxi, Jiangsu, China',13);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL, 'STK00000009','cindy.wang','cindy','Active','F',to_date('02-FEB-86','DD-MON-YY'),'cindy.wang@gmail.com','+86 510 8555 5558','13912345679','O Park, Xinhu District, Wuxi, Jiangsu, China',13);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL, 'STK00000010','alvin.weng','alvin','Active','M',to_date('12-MAR-87','DD-MON-YY'),'alvin.weng@gmail.com','+86 510 8555 5559','13912345618','O Park, Xinhu District, Wuxi, Jiangsu, China',15);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL, 'STK00000011','charles.du','charles','Active','M',to_date('16-SEP-89','DD-MON-YY'),'charles.du@gmail.com','+86 510 8555 5515','13912345628','O Park, Xinhu District, Wuxi, Jiangsu, China',15);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL, 'STK00000012','chris.zhang','chris','Active','M',to_date('12-NOV-88','DD-MON-YY'),'chris.zhang@gmail.com','+86 510 8555 5525','13912345638','O Park, Xinhu District, Wuxi, Jiangsu, China',16);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL, 'STK00000013','ben.liu','ben','Active','M',to_date('22-SEP-85','DD-MON-YY'),'ben.liu@gmail.com','+86 510 8555 5535','13912345648','O Park, Xinhu District, Wuxi, Jiangsu, China',16);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL, 'STK00000014','simon.song','simon','Active','M',to_date('12-APR-88','DD-MON-YY'),'simon.song@gmail.com','+86 510 8555 5545','13912345658','O Park, Xinhu District, Wuxi, Jiangsu, China',12);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL, 'STK00000015','light.chen','light','Active','M',to_date('13-MAY-87','DD-MON-YY'),'light.chen@gmail.com','+86 510 8555 5565','13912345668','O Park, Xinhu District, Wuxi, Jiangsu, China',10);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL, 'STK00000016','katina.yang','katina','Inactive','F',to_date('20-JUN-97','DD-MON-YY'),'katina.yang@gmail.com','+86 510 8555 5575','13912345688','O Park, Xinhu District, Wuxi, Jiangsu, China',10);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL, 'STK00000017','luca.albricci','luca','Inactive','M',to_date('11-AUG-83','DD-MON-YY'),'luca.albricci@gmail.com','+86 510 8555 5585','13912345698','O Park, Xinhu District, Wuxi, Jiangsu, China',10);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL, 'STK00000018','bluce.zheng','bluce','Inactive','M',to_date('06-JUL-87','DD-MON-YY'),'bluce.zheng@gmail.com','+86 510 8555 5595','13912345178','O Park, Xinhu District, Wuxi, Jiangsu, China',17);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL, 'STK00000019','jackey.zhang','jackey','Inactive','M',to_date('12-JUN-87','DD-MON-YY'),'jackey.zhang@gmail.com','+86 510 8555 5155','13912342678','O Park, Xinhu District, Wuxi, Jiangsu, China',16);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL, 'STK00000020','ekrn.dong','ekrn','Inactive','M',to_date('16-APR-83','DD-MON-YY'),'ekrn.dong@gmail.com','+86 510 8555 5255','13912343678','O Park, Xinhu District, Wuxi, Jiangsu, China',16);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL, 'STK00000021','laura.pellegrini','laura','Inactive','F',to_date('23-SEP-71','DD-MON-YY'),'laura.pellegrini@gmail.com','+86 510 8555 5355','13910345678','O Park, Xinhu District, Wuxi, Jiangsu, China',10);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL, 'STK00000022','ricky.he','ricky','Inactive','M',to_date('12-JAN-82','DD-MON-YY'),'ricky.he@gmail.com','+86 510 8555 5455','13982345678','O Park, Xinhu District, Wuxi, Jiangsu, China',18);

--创建表DWH_USER_INF_ACTIVE, 结构与DWH_USER_INF一样：

--创建表DWH_USER_INF_ACTIVE，结构与DWH_USER_INF一样，不包含任何数据
CREATE TABLE DWH_USER_INF_ACTIVE
AS
SELECT * FROM DWH_USER_INF
WHERE 1=2;

--利用INSERT INTO....SUBQUERY方式插入数据：

INSERT INTO DWH_USER_INF_ACTIVE
SELECT *
FROM DWH_USER_INF
WHERE STATUS='Active';

2. 多表INSERT语句

INSERT [ALL] [condition_insert_clause]
[insert_into_clause values_clause] (subquery)
--其中，condition_insert_clause语法如下：
[ALL][FIRST]
[WHEN condition THEN] [insert_into_clause values_clause]
[ELSE] [insert_into_clause values_clause]

--创建表DM_USER_INF_A,不包含数据，用于无条件插入
CREATE TABLE DM_USER_INF_A
AS
SELECT USER_ID, USER_NUM, USER_NAME,STATUS, EMAIL, DEPARTMENT_ID
FROM DWH_USER_INF
WHERE 1=2;
--创建表DM_USER_INF_B,不包含数据，用于无条件插入
CREATE TABLE DM_USER_INF_B
AS
SELECT USER_ID, USER_NUM, USER_NAME,STATUS, GENDER,TELEPHONE, ADDRESS
FROM DWH_USER_INF
WHERE 1=2;

--创建表DM_USER_INF_ACTIVE，不包含数据，用于有条件INSERT ALL
CREATE TABLE DM_USER_INF_ACTIVE
AS
SELECT USER_ID, USER_NUM, USER_NAME,STATUS, EMAIL, DEPARTMENT_ID
FROM DWH_USER_INF
WHERE 1=2;
--创建表DM_USER_INF_INACTIVE,不包含数据，用于有条件INSERT ALL
CREATE TABLE DM_USER_INF_INACTIVE
AS
SELECT USER_ID, USER_NUM, USER_NAME,STATUS, GENDER,TELEPHONE, ADDRESS
FROM DWH_USER_INF
WHERE 1=2;

--2.1 无条件INSERT ALL

INSERT ALL
INTO DM_USER_INF_A VALUES(USER_ID, USER_NUM, USER_NAME,STATUS, EMAIL, DEPARTMENT_ID)
INTO DM_USER_INF_B VALUES(USER_ID, USER_NUM, USER_NAME,STATUS, GENDER,TELEPHONE, ADDRESS)
SELECT USER_ID,  USER_NUM,  USER_NAME,  NICKNAME,  STATUS,  GENDER,  BIRTHDAY,  EMAIL,  TELEPHONE,  MOBILE,  ADDRESS,  DEPARTMENT_ID
FROM DWH_USER_INF;

--2.2 有条件的INSERT ALL

INSERT ALL
WHEN STATUS='Active' THEN
INTO DM_USER_INF_ACTIVE VALUES(USER_ID, USER_NUM, USER_NAME,STATUS, EMAIL, DEPARTMENT_ID)
WHEN STATUS='Inactive' THEN
INTO DM_USER_INF_INACTIVE VALUES(USER_ID, USER_NUM, USER_NAME,STATUS, GENDER,TELEPHONE, ADDRESS)
SELECT USER_ID,  USER_NUM,  USER_NAME,  NICKNAME,  STATUS,  GENDER,  BIRTHDAY,  EMAIL,  TELEPHONE,  MOBILE,  ADDRESS,  DEPARTMENT_ID
FROM DWH_USER_INF ;

--2.3 有条件的FIRST INSERT

FIRST与ALL的区别在于：当遇到第一个求值为TRUE的语句之后停止对后面WHEN子句的求值，下面的例子中，如果第一个子句求值为TRUE，则其后的WHEN子句不会被执行，反之，则直到遇到第一个满足条件的子句为止：

--创建表DM_USER_INF_GENDER_M, 不包含数据
create table DM_USER_INF_GENDER_M
AS
SELECT * FROM DWH_USER_INF
WHERE 1=2;

--创建表DM_USER_INF_GENDER_F, 不包含数据
create table DM_USER_INF_GENDER_F
AS
SELECT * FROM DWH_USER_INF
WHERE 1=2;
--创建表DM_USER_INF_STATUS，不包含数据
create table DM_USER_INF_STATUS
AS
SELECT * FROM DWH_USER_INF
WHERE 1=2;
--创建表DM_USER_INF_OTHERS,不包含数据
create table DM_USER_INF_OTHERS
AS
SELECT * FROM DWH_USER_INF
WHERE 1=2;

--有条件的FIRST INSERT示例
INSERT FIRST
WHEN GENDER='M' THEN
          INTO DM_USER_INF_GENDER_M VALUES (USER_ID,  USER_NUM,  USER_NAME,  NICKNAME,  STATUS,  GENDER,  BIRTHDAY,  EMAIL,  TELEPHONE,  MOBILE,  ADDRESS,  DEPARTMENT_ID)
WHEN GENDER='F' THEN
         INTO DM_USER_INF_GENDER_F VALUES (USER_ID,  USER_NUM,  USER_NAME,  NICKNAME,  STATUS,  GENDER,  BIRTHDAY,  EMAIL,  TELEPHONE,  MOBILE,  ADDRESS,  DEPARTMENT_ID)
WHEN STATUS='Active' THEN
          INTO DM_USER_INF_STATUS VALUES (USER_ID,  USER_NUM,  USER_NAME,  NICKNAME,  STATUS,  GENDER,  BIRTHDAY,  EMAIL,  TELEPHONE,  MOBILE,  ADDRESS,  DEPARTMENT_ID)
ELSE
         INTO DM_USER_INF_OTHERS VALUES (USER_ID,  USER_NUM,  USER_NAME,  NICKNAME,  STATUS,  GENDER,  BIRTHDAY,  EMAIL,  TELEPHONE,  MOBILE,  ADDRESS,  DEPARTMENT_ID)
SELECT USER_ID,  USER_NUM,  USER_NAME,  NICKNAME,  STATUS,  GENDER,  BIRTHDAY,  EMAIL,  TELEPHONE,  MOBILE,  ADDRESS,  DEPARTMENT_ID
FROM DWH_USER_INF;

--数据验证：

SELECT 'DM_USER_INF_GENDER_M', x.*  FROM DM_USER_INF_GENDER_M x
UNION ALL
SELECT 'DM_USER_INF_GENDER_F', x.*  FROM DM_USER_INF_GENDER_F x
UNION ALL
SELECT 'DM_USER_INF_STATUS', x.*  FROM DM_USER_INF_STATUS x
UNION ALL
SELECT 'DM_USER_INF_OTHERS', x.*  FROM DM_USER_INF_OTHERS x

说明：

插入时是根据表中的每一行数据去匹配WHEN里面的条件，最先满足条件的执行插入操作，然后后面的条件不会再走，继续下一条判断与操作。

--2.4 UPDATE

给表DWH_USER_INF增加两列，salary和bonus:

alter table DWH_USER_INF  add (salary number(22,2), bonus number(22,2));

可以查看表DWH_USER_INF表，新增的两个字段值为空（NULL）：

下面通过随机数的方式为两个字段更新数据，有关生成随机数，参考教程Oracle DBMS_RANDOM包生成随机数：

--更新salary字段：
UPDATE DWH_USER_INF
SET 
salary=dbms_random.value(2500,15000)

--根据年龄更新bonus字段：
UPDATE DWH_USER_INF
SET 
bonus=(  CASE  
       WHEN FLOOR(MONTHS_BETWEEN(sysdate,birthday )/12)>=30 THEN
              salary*10
        WHEN FLOOR(MONTHS_BETWEEN(sysdate,birthday )/12)>=20 THEN
              salary*2
        ELSE
              salary*1
END) ;

--2.5 MERGE INTO

有关MERGE INTO的用法，请参考： ORACLE Database之MERGE INTO用法

--2.6 DELETE/TRUNCATE

--语法
--DELETE FROM [schema.]table_name [where_clause];

delete from DWH_USER_INF where status='Active'; --删除status='Active'的所有用户；
delete from DWH_USER_INF; --删除表中所有数据

删除表中所有数据的另一种更高效的方法是使用TRUNCATE:

TRUNCATE TABLE DWH_USER_INF; --清空表中所有数据；

引申： TRUNCATE和DELETE的区别：

TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。 DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。
TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
TRUNCATE,DELETE,DROP放在一起比较：
TRUNCATE TABLE：删除内容、释放空间但不删除定义。
DELETE TABLE:删除内容不删除定义，不释放空间。
DROP TABLE：删除内容和定义，释放空间。

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

本文系原创，转载请表明出处！

如果您在尝试过程中遇到任何问题，请给予指正！