第八章：Oracle数据操作与事务控制以及锁

最新推荐文章于 2024-06-13 09:07:38 发布

Alan伟

最新推荐文章于 2024-06-13 09:07:38 发布

阅读量196

点赞数

分类专栏： Oracle 文章标签： oracle

本文链接：https://blog.csdn.net/u013914878/article/details/120857755

版权

Oracle 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

新增 insert into 表名（列名1,[列名2….]） values(值1,[值2…..])
注意：
–列名列表与值列表个数、数据类型保持一致
–字符类型与日期类型用”单引号括起来
–列名列表可省略，默认值列表为所有列赋值

插入空值NULL 隐含法: 在列名列表中忽略该列
insert into dept(deptno,dname) values(50,’软件开发事业部’);

插入空值NULL 显示法: 指定 NULL关键字或者”
insert into dept(deptno,dname,loc) values(60,’测试部’,null);
insert into dept values(70,’运维部’,”);

插入日期值 SYSDATE 函数记录当前日期和时间
insert into emp(empno,ename,hiredate) values(1111,’张三’,sysdate);

插入日期值用户指定日期，需用to_date()函数将字符串转换为日期类型
insert into emp(empno,ename,hiredate) values(2222,’李四’,to_date(‘2018-07-30’,’YYYY-MM-DD’));

插入特字符&
insert into dept(deptno,dname,loc) values(80,’#@$%实施部&’,’广东湛江’);
insert into dept(deptno,dname,loc) values(90,’&’ || ‘实施部’,’广东湛江’);

插入多行数据
创建表manager,且结构与emp表一致
–create table manager as select * from emp; –复制表结构及所有表数据
create table manager as select * from emp where 1=0; –仅复制表结构

将emp表中职位为MANAGER的员工信息插入到manager表中
insert into manager select * from emp where job=’MANAGER’;

——————————————————-
修改数据
语法： update 表名 set 列名=新值,[列名1=新值1…] where 限制条件

使用 WHERE 子句指定要修改的记录
把员工编号为7782的部门编号修改为20;
update emp set deptno=20 where empno=7782;

如果要修改所有记录，WHERE子句可以忽略
把所有员工的部门编号修改为20
update emp set deptno = 20;

一次修改多列
把部门编号为10的员工，部门编号调整为20，工资增加100
update emp set deptno=20,sal = sal+100 where deptno=10;

嵌入子查询修改
把部门编号为10的员工，部门编号调整为20，工资在原有的基础上，增加所有人的平均工资
update emp set deptno=20,sal=sal+(select avg(sal) from emp) where deptno=10;

修改记录时的完整性约束错误
把部门编号为10的员工，部门编号调整为55
update emp set deptno=55 where deptno=10; –错误未找到父项关键字外键（emp.deptno）对应的值不在主键(dept.deptno)范围之内

相关修改
修改工作地点在NEW YORK或CHICAGO的员工工资，工资增加500
update emp set sal = sal+500 where (select loc from dept where deptno=emp.deptno) in(‘NEW YORK’,’CHICAGO’);
select * from emp where (select loc from dept where deptno=emp.deptno) in(‘NEW YORK’,’CHICAGO’);

在emp表中增加一个列dname, 来存储部门名称.
alter table emp add(dname varchar2(14));

使用相关子查询更新dname列为正确的部门名称
update emp set dname= (select dname from dept where deptno = emp.deptno);

——————————————————-
删除数据
语法： delete [from] 表名 where 限制条件

删除选中记录
删除职位是CLERK的员工记录
delete from emp where job=’CLERK’;

删除全部记录
删除所有员工记录
delete from emp;

基于另外一张表进行删除操作
删除部门SALES的员工记录
delete from emp where deptno=(select deptno from dept where dname=’SALES’);

删除部门编号为10的部门信息
delete from dept where deptno = 10;
–错误已找到子记录（留意错误：未找到父项关键字）

相关删除
删除曾经做过入职的员工的记录
delete from emp where exists (select * from emp_jobhistory where empno = emp.empno);

删除没有员工的部门记录
delete from dept where not exists (select * from emp where deptno=dept.deptno)

——————————————————-

合并:如果匹配则修改,如果不匹配则插入
语法:
MERGE INTO mydept m --目标表
USING dept d --原表
ON ( m.deptno=d.deptno ) --条件
WHEN MATCHED THEN
UPDATE SET
dname='zzz',loc='zzz' WHERE deptno=11
WHEN NOT MATCHED THEN
INSERT(m.deptno,m.dname,m.loc)
VALUES(d.deptno,d.dname,d.loc);

rowid:伪列,是表中的虚拟的列,是系统自动产生的,效率最快
表示一行的物理地址,可以唯一标识该行
前6位:数据对象编号,接下3位:相关文件编号,接下6位:块编号,末3位:行号

事务管理（TPL）:一组SQL语句的管理单元（针对增、删、改语句做事务管理）

四个特性：
原子性：所有sql语句执行成功都成功，任意一个失败，全部必须执行失败
一致性：不论失败或成功，数据库数据保持一致性
隔离性：事务之间相互独立，互不影响
持久性：事务一旦被提交，永久保存在数据库中，无法撤回

事务开始：当上一个事务结束，第一个新的DML（增删改）语句执行开始，事务开始

事务结束：
显示结束：显示提交：commit
显示回滚：rollback

隐式结束：
隐式提交：执行DDL 或 DCL 或从SQL*PLUS正常退出
隐式回滚：从SQL*Plus中强行退出
客户端连接到服务器端异常中断
系统崩溃

–显示结束：事务提交commit
insert into dept(deptno,dname,loc) values(50,’开发部’,’东软睿道’);
commit;–提交事务（增删改操作均需要提交事务）

–显示结束：事务回滚rollback
insert into dept(deptno,dname,loc) values(60,’运维部’,’东软睿道’);
insert into dept(deptno,dname,loc) values(70,’IT部’,’东软睿道’);
rollback;–回滚事务

设置保存点
insert into dept(deptno,dname) values(80,’实施部’);
savepoint point1;–设置保存点1
insert into dept(deptno,dname) values(90,’测试部’);
insert into dept(deptno,dname) values(91,’需求部’);
savepoint point2;–设置保存点2
insert into dept(deptno,dname) values(92,’产品部’);
savepoint point3;–设置保存点3

回滚到保存点1的位置
rollback to point1;

Oracle加锁方式:
1)自动加锁:用户在执行insert,update,delete,DCl,DDL语句时,Oracle会自动加锁
2)手动加锁
2-1)表级加锁:
LOCK TABLE 表名 IN 锁类型 MODE [NOWAIT]
2-2)行级加锁:
SELECT 查询列 FROM 表名 FOR UPDATE [NOWAIT]

锁的类型:
排它锁(Exclusive):不能在其上再加其他锁
共享锁(share):可以在其上再加共享锁

提交或回滚前数据状态
1)数据变化前的状态可以被恢复
2)当前会话可以使用select语句来验证DML操作后的结果
3)其他会话不能查看当前用户的DML操作结果
4)受影响纪录被锁定,也就是其他用户不能改变受影响记录中的数据
提交后数据状态
1)在数据库中数据变化为永久性的,先前的数据状态永久性消失
2)所有用户/会话都可以查询到提交后的结果
3)锁定的记录被释放,可以有效地被其他用户操作
4)所有的存储节点被清除
回滚后数据状态
1)先前的数据状态被恢复
2)锁定的记录被释放
3)所有的存储节点被清除