Oracle更新及事物操作

--更新及事物操作


--跟新操作前的准备


create table myemp as select * from emp;


select * from tab;


select * from myemp;


--数据的增加操作


select * from myemp;


desc myemp;


insert into myemp(empno,job,hiredate,ename,mgr,sal,comm,deptno) 
  values (8888,'CLERK',sysdate,'陈飞鹏',7369,800,100,20);
  
insert into myemp 
values (8899,'软件','MANAGER',7369,to_date('1992-09-12','yyyy-mm-dd'),1000,100,20);


insert into myemp(empno,ename,job,hiredate,sal)
values(6612,'李楠','CLERK',to_date('1989-09-19','yyyy-mm-dd'),600);


insert into myemp
values(6616,'李楠','CLERK',null,to_date('1989-09-19','yyyy-mm-dd'),600,null,null);


select * from myemp where empno in(6612,6616);


insert into myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
select * from emp where deptno=20;


insert into myemp select * from emp where deptno=10;


--数据的跟新操作


update myemp set sal=3000,comm=500 where empno=7369;


select * from myemp where empno=7369;


update myemp set sal=sal*1.2
where sal<(select avg(sal) from myemp);


update myemp set sal=0;


rollback;


update myemp set(job,sal,hiredate)=(select job,sal,hiredate from myemp where empno=7839)where empno=7369;


--数据的删除操作


delete from myemp where empno=7566;


select * from myemp where empno=7566;


delete from myemp where deptno=30;


select * from myemp where deptno=30;


delete from myemp where empno in(7369,7566,7788);


select * from myemp;


delete from myemp where sal=(select max(sal) from myemp);


select * from myemp;


delete from myemp myemp where to_char(hiredate,'yyyy')='1987';


select * from myemp;


--事物处理


--锁


select session_id,oracle_username,process from v$locked_object;


commit;


drop table myemp;