Oracle数据库语言-DML、TCL/TPL

目录

一、数据操作语言（DML）：

 1.1插入数据

1.1.1批量插入

1.1.2手动插入

1.1.3区分insert first和insert all

1.2更新数据

1.3删除数据

1.4合并数据

1.4.1语法：

1.4.2实例理解

二、TPL/TCL 事务处理/控制语言

2.1理解什么是事务？

2.2理解什么是会话？

2.3事务的四个特性

2.4事务语言的作用

---

一、数据操作语言（DML）：

用于操作数据库中的数据，包括INSERT、UPDATE、DELETE等关键字。

插入数据	INSERT
更新数据	UPDATE
删除数据	DELETE
合并数据	MERGE

 1.1插入数据

批量插入

手动插入

1.1.1批量插入

Ⅰ、使用INSERT INTO SELECT语句

将数据从一个表复制到另一个表中，可以同时插入多行数据。适用于从一个表中复制数据到另一个表中。

--列的数量要一致,注意列的数据类型
insert into 表名 [(列1,列2...)] select 语句;

INSERT INTO table_name (col1, col2, col3) 
SELECT val1, val2, val3 FROM source_table;

比如：

--创建一个emp1，表格式同emp，向emp1插入emp的所有数据
create table emp1 as select * from emp where 1=2;
insert into emp1 select * from emp;
commit;
select * from emp1;
--清空emp1，向emp1的sal列和deptno列插入
--10部门的平均工资和人数
alter table emp1 modify empno null;
truncate table emp1;
insert into emp1(sal,deptno) select avg(sal),count(1)
                             from emp
                             where deptno=10;

Ⅱ、使用INSERT ALL语句

可以一次性插入多行数据。适用于插入多条数据，但是数据量不是很大的情况。

INSERT ALL
INTO table_name (col1, col2, col3) VALUES (val1, val2, val3)
INTO table_name (col1, col2, col3) VALUES (val4, val5, val6)
INTO table_name (col1, col2, col3) VALUES (val7, val8, val9)
SELECT 1 FROM DUAL;

Ⅲ、使用FORALL语句

可以在PL/SQL中一次性插入多行数据。适用于插入大量数据的情况。

DECLARE
  TYPE t_data IS TABLE OF table_name%ROWTYPE;
  v_data t_data := t_data();
BEGIN
  v_data.extend(3);
  v_data(1).col1 := val1;
  v_data(1).col2 := val2;
  v_data(1).col3 := val3;
  v_data(2).col1 := val4;
  v_data(2).col2 := val5;
  v_data(2).col3 := val6;
  v_data(3).col1 := val7;
  v_data(3).col2 := val8;
  v_data(3).col3 := val9;

  FORALL i IN v_data.first..v_data.last
    INSERT INTO table_name VALUES v_data(i);
END;

总结：

三种方法都可以实现Oracle批量插入。具体使用哪种方法取决于具体的业务需求和数据量大小。如果数据量较小，可以使用INSERT ALL语句，如果数据量较大，可以使用FORALL语句。如果需要从一个表中复制数据到另一个表中，可以使用INSERT INTO SELECT语句。

1.1.2手动插入

语法：

insert into 表名 [(列名1,列名2...)] values(值1,值2...)

 比如：

--清空emp1，向emp1插入一条数据
--姓名叫 张三 员工编号 1234 工资是250
--部门是40 职位是 BOSS 入职日期 2022-1-1
truncate table emp1;
insert into emp1(ename,empno,sal,deptno,job,hiredate)
       values('张三',1234,250,40,'BOSS',date'2022-1-1');

select * from emp1;

--清空emp1，向emp1插入一条数据
--姓名叫 里斯 员工编号 1235 工资是99999
--部门是40 职位是 王 入职日期 2022-1-1 经理是1234
--提成 是 5000
truncate table emp1;
insert into emp1 values(1235,'里斯','王',1234,date'2022-1-1',99999,5000,40);

1.1.3区分insert first和insert all

在Oracle中，insert first和insert all是两种不同的插入数据的方式。

Ⅰ、 insert first

insert first是指在插入数据时，只会插入第一个符合条件的记录，如果后续有其他符合条件的记录，则会被忽略掉。这种方式适用于需要插入唯一记录的情况。

语法如下：

insert first
when 条件1 then into 表1(列1,列2...)
                     values(值1,值2...)
when 条件2 then into 表2(列1,列2...)
                     values(值1,值2...)
when 条件3 then into 表3(列1,列2...)
                     values(值1,值2...)


INSERT FIRST
WHEN condition1 THEN insert_statement1
WHEN condition2 THEN insert_statement2
...
ELSE insert_statementN
END;

示例：

INSERT FIRST
WHEN salary > 5000 THEN
   INTO high_salary_emp VALUES (emp_id, emp_name, salary);
WHEN salary > 3000 THEN
   INTO medium_salary_emp VALUES (emp_id, emp_name, salary);
ELSE
   INTO low_salary_emp VALUES (emp_id, emp_name, salary);
END;

以上语句表示，如果员工的工资大于5000，则插入到high_salary_emp表中，如果工资大于3000，则插入到medium_salary_emp表中，否则插入到low_salary_emp表中。

Ⅱ、insert all

insert all是指在插入数据时，会插入所有符合条件的记录，而不是只插入第一个符合条件的记录。这种方式适用于需要批量插入数据的情况。

语法如下：

insert all
when 条件1 then into 表1(列1,列2...)
                     values(值1,值2...)
when 条件2 then into 表2(列1,列2...)
                     values(值1,值2...)
when 条件3 then into 表3(列1,列2...)
                     values(值1,值2...)

INSERT ALL
WHEN condition1 THEN insert_statement1
WHEN condition2 THEN insert_statement2
...
ELSE insert_statementN
END;

示例：

INSERT ALL
WHEN dept_id = 10 THEN
   INTO dept_10 VALUES (emp_id, emp_name, salary);
WHEN dept_id = 20 THEN
   INTO dept_20 VALUES (emp_id, emp_name, salary);
ELSE
   INTO other_dept VALUES (emp_id, emp_name, salary);
END;

以上语句表示，如果员工所在的部门是10，则插入到dept_10表中，如果是20，则插入到dept_20表中，否则插入到other_dept表中。

 总结：

--把一个结果集插入到多张表

insert first 一条数据只会插入一次，

这条数据就算符合后面表的条件 也不会插入；

insert all 一条数据可以插多次，

前面的插入了 后面的也可以插入。

1.2更新数据

语法：

update 表名 set 列1=值1,列2=值2...[where 条件];
即：
UPDATE table_name SET column_name1 = value1, column_name2 = value2, ... WHERE condition;

 注：不加where 对全表更新

其中，table_name是要更新数据的表名，column_name1、column_name2等是要更新的列名，value1、value2等是要更新的值，WHERE后面的condition是更新数据的条件。

比如：

--创建一个emp1表数据同emp 
create table emp1 as select * from emp;

--把emp1 10部门 工资加500
update emp1 set sal=sal+500 where deptno=10;

--把emp1 KING 的入职日期更新成空
update emp1 set hiredate=null  where ename='KING';

--把emp1 20部门员工职位改小写 工资加666
update emp1 set job=lower(job),sal=sal+666 where deptno=20;

--假设有一个employees表，其中包含了员工的ID、姓名和工资信息。现在需要将工资大于5000的员工的工资增---加10%。
UPDATE employees SET salary = salary * 1.1 WHERE salary > 5000;

1.3删除数据

语法：

delete from 表 [where 条件];
即；
DELETE FROM table_name WHERE condition;

 注：不加where 对全表进行删除

table_name是要删除数据的表名，WHERE后面的condition是删除数据的条件。

比如：

--删除emp1表中工资小于3000的员工信息
delete from emp1 where sal<3000;

--假设有一个employees表，其中包含了员工的ID、姓名和工资信息。现在需要删除工资低于3000的员工的信-----息。
DELETE FROM employees WHERE salary < 3000;

1.4合并数据

MERGE操作是一种合并数据的DML操作，可以将源表中的数据合并到目标表中，同时可以根据需要进行更新或插入操作。MERGE操作通常用于数据仓库等场景中，用于将来自不同数据源的数据进行合并。--即merge into 可插入 也可更新 也可以删除

1.4.1语法：

MERGE语句的基本语法如下：

MERGE INTO target_table
USING source_table
ON (join_condition)
WHEN MATCHED THEN
  UPDATE SET target_column = source_column
WHEN NOT MATCHED THEN
  INSERT (target_column1, target_column2, ...)
  VALUES (source_column1, source_column2, ...)

其中，`target_table`是目标表，`source_table`是源表，`join_condition`是连接条件，用于将目标表和源表中的数据进行匹配。`WHEN MATCHED`子句表示当目标表和源表中的数据匹配时，执行更新操作。`WHEN NOT MATCHED`子句表示当目标表和源表中的数据不匹配时，执行插入操作。

换种方式按下面👇语法理解：

merge into 表a               --进行插入、更新的表   
using 表b                      --参考表
on (条件)                       --条件
when matched then            --匹配上了则
  update set a.列=b.列(或)值1,...    --更新
  [where 条件]                  --限制a表，哪些行可以被update
when not matched then        --没匹配上则
  insert [(a.列1,a.列2..)]          --insert 后面不写相当于插入所有列
  values (b.列1(或)值,b.列2(或)值...)     --插入  
  [where 条件]          --限制b表，规定哪些行可以insert到a表

1.4.2实例理解

下面举些例子来说明MERGE语句的使用方法。

①假设有两个表，`employees`和`employees_new`，它们的结构如下：

employees
---------
id  name  salary  department

employees_new
-------------
id  name  salary  department

现在需要将`employees_new`表中的数据合并到`employees`表中，如果`employees_new`表中的数据在`employees`表中已经存在，则更新`employees`表中对应的记录的`salary`和`department`字段，否则插入一条新记录。

下面是实现这个功能的MERGE语句：

MERGE INTO employees e
USING employees_new en
ON (e.id = en.id)
WHEN MATCHED THEN
  UPDATE SET e.salary = en.salary, e.department = en.department
WHEN NOT MATCHED THEN
  INSERT (id, name, salary, department)
  VALUES (en.id, en.name, en.salary, en.department);

解析：

将`employees_new`表中的数据合并到`employees`表中，如果`employees_new`表中的数据在`employees`表中已经存在（即两个表中的`id`字段相等），则更新`employees`表中对应的记录的`salary`和`department`字段，否则插入一条新记录。这样，就实现了将两个表中的数据合并到一起的功能。

②按照下列要求完成表数据合并更新

题目一：
--创建emp1内容和emp表的10部门20部门数据一样
create table emp1 as select * from emp where deptno in (10,20);

--创建一个emp2内容和emp表的20,30部门一样
create table emp2 as select * from emp where deptno in (20,30);

--emp2表20部门员工姓名更新成小写
update emp2 set ename=lower(ename) where deptno=20;

--参考emp2表合并表emp1
merge into emp1 a --进行插入、更新的表   
using emp2 b --参考表
on (a.empno=b.empno) --条件
when matched then--匹配上了则
  update set a.ename=b.ename --更新
when not matched then --没匹配上则
  insert --insert 后面不写相当于插入所有列
  values (b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.comm,b.deptno)--插入 

题目二：
--创建表empa empb表结构同emp                   
create table empa as select * from emp where 1=2;
create table empb as select * from emp where 1=2;

--向empa中插入emp表的不是MANAGER的员工信息
insert into empa select * from emp where job!='MANAGER';

--向empb中插入emp表的不是SALESMAN的员工信息
insert into empb select * from emp where job!='SALESMAN';

--将empb中CLERK的工资增加1000
update empb set sal=sal+1000 where job='CLERK';

--参考表empb 插入更新empa 
merge into empa a --进行插入、更新的表   
using empb b --参考表
on (a.empno=b.empno) --条件
when matched then--匹配上了则
  update set a.sal=b.sal --更新
when not matched then --没匹配上则
  insert --insert 后面不写相当于插入所有列
  values (b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.comm,b.deptno)--插入  
 

注意：

MERGE语句中的连接条件必须能够唯一地匹配目标表和源表中的数据，否则会出现数据重复或丢失的问题。此外，在使用MERGE语句时，需要保证目标表和源表的字段名和数据类型完全一致，否则会出现数据类型不匹配或字段名不存在的错误。

总结：

MERGE语句是Oracle中非常实用的一种合并数据的DML操作，它可以将来自不同数据源的数据合并到一个表中，从而方便进行数据分析和处理。在实际应用中，需要根据具体的业务需求来灵活使用MERGE语句，以实现数据的合并和更新。

二、TPL/TCL 事务处理/控制语言

用于事务处理和控制的编程语言，包括rollback,commit关键字。

提交	COMMIT
回滚	ROLLBACK

2.1理解什么是事务？

事务(TCL/TPL:事务控制语言/事务处理语言)，事务是指一组操作，这些操作要么全部执行成功，要么全部执行失败，不会出现部分执行成功的情况。在 Oracle 中，事务通常指一组对数据库进行的操作，这些操作被视为一个单独的工作单元，要么全部执行成功，要么全部回滚到操作前的状态。事务在数据库中非常重要，因为它确保了数据的一致性和完整性，同时也提高了并发性和数据的可靠性。在 Oracle 中，事务可以通过使用 BEGIN、COMMIT 和 ROLLBACK 等命令来控制和管理。同时从狭义和广义上理解为：

狭义:从TPL到下一个TPL之间所有的DML(如果执行DDL,DDL之前的DML会自动提交)；

广义:任何一次要么成功要么失败的数据库操作。

2.2理解什么是会话？

Oracle中的会话是指用户与数据库之间的连接关系。当用户登录到Oracle数据库时，就会创建一个会话。在会话中，用户可以执行各种操作，包括查询、更新、插入、删除等。会话还可以跨多个事务，允许用户在多个事务之间共享数据。

会话是一个非常重要的概念，因为它关系到数据库的并发性、安全性和性能。会话可以通过SQL语句来管理，包括创建、关闭、暂停、恢复等操作。同时，Oracle还提供了一些监控和管理工具，可以帮助管理员监控和管理会话，包括查看会话状态、终止会话等。

注意：

注意会话和进程的区别。会话是用户与数据库之间的连接关系，而进程则是会话在数据库服务器上的实际运行实例。一个会话可以对应多个进程，这些进程可以在不同的CPU上运行，以提高数据库的并发性和性能。

总结：

一个用户从登陆到退出叫做一次会话。

2.3事务的四个特性

事务具有四个特性，即ACID特性，包括原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

分别解释这四个特性及其应用场景和应用的具体情况：

①原子性 ：

指事务中的所有操作要么全部执行成功，成功必须完全应用到数据库；要么全部回滚到操作前的状态，失败则不会对数据库产生影响。在Oracle数据库中，事务原子性的实现是通过使用回滚段（Rollback Segment）来实现的。当一个事务开始执行时，Oracle会自动为该事务分配一个回滚段，用来存储该事务所做的修改。如果事务执行失败，Oracle会自动将回滚段中的修改回滚（撤销）掉，保证事务的所有操作都能够被撤销，从而保证事务的原子性。

应用场景包括转账、订单处理等需要保证数据的一致性的操作。如果转账只执行了一部分，就会出现数据不一致的情况，因此必须保证转账操作是原子性的。

②一致性：

指事务执行前后数据库的状态必须保持一致。应用场景包括数据校验、数据更新等需要保证数据的正确性的操作。如果数据更新后，数据的状态不一致，就会影响后续操作和查询的结果，因此必须保证事务的一致性。

比如：

你给家里寄了五百块钱,在你的角度少了五百,在家里多了五百,不可能你少了五百家里多了一千吧.那五百块的状态并没有改变只是从你这里跑到家里去了,再比如你修改emp表的工资列,改过后它其他的七个列是不会变的。

③隔离性

指事务之间相互隔离，互不干扰。应用场景包括并发操作、多用户操作等需要保证数据的独立性的操作。如果当多个用户并发访问数据库时,多个用户同时修改同一条数据，就会出现数据混乱的情况，因此必须保证事务的隔离性，数据库为每个用户开启的事务不被其他事务的操作所干扰,多个并发事务之间是互相隔离的。

④持久性

指事务提交后，对数据库的修改是永久性的，即使系统故障也不会丢失。应用场景包括日志记录、数据备份等需要保证数据的可靠性的操作。如果系统故障导致数据丢失，就会对业务造成严重影响，因此必须保证事务的持久性。即一个事务一旦提交,那么对数据库的改变是永久性的,即使在数据库遇到故障的情况也不会丢失事务的操作。

2.4事务语言的作用

TPL/TCL (Transaction Processing Language/Control2.3 Language) 是一种用于事务处理和控制的编程语言。它的作用包括：

① 实现事务处理：

TPL/TCL 可以帮助程序员实现事务处理，即一组操作要么全部成功，要么全部失败。这对于需要保证数据一致性和可靠性的应用程序非常重要。

②控制程序流程：

TPL/TCL 可以控制程序的执行流程，例如循环、分支、异常处理等。这使得程序可以根据不同的条件执行不同的操作，从而实现更加灵活的程序逻辑。

③提高程序可维护性：

TPL/TCL 可以将程序的逻辑与数据分离，从而使得程序更加易于维护。程序员可以通过修改 TPL/TCL 脚本来更改程序的行为，而无需修改程序的源代码。

④支持数据查询和修改：

TPL/TCL 可以通过 SQL 语句对数据库进行查询和修改。这使得程序可以方便地与数据库进行交互，从而实现更加复杂的应用程序。

总之，TPL/TCL 是一种强大的编程语言，可以帮助程序员实现事务处理、控制程序流程、提高程序可维护性和支持数据库操作等功能。