dml数据操作和tcl事务管理

--dml 数据操作和tcl事务操作
    --一、insert
        insert into emp(empno, ename, job, sal) values(1001,'rose', 'CLERK',  1500);
        --使用子查询插入多条数据
        create table emp_20(
            empid number(4) primary key,
            name varchar(10),
            job varchar(9)
        );
        insert into emp_20 (empid, name, job) select empno, ename, job from emp where deptno=20;
        --多表insert语句 查询语句只执行一次,然后把满足条件的分别插入到表中
        --通过子句创建表,因为1=0==>false 所以里面没有数据
        create table clerk as select * from emp where 1=0;
        create table salesman as select * from emp where 1=0;
        create table manager as select * from emp where 1=0;
        create table emp10 as select * from emp where 1=0;
        create table others as select * from emp where 1=0;

        --使用all执行多表插入 相当于没有break的switch语句,满足条件的就插入到表中
        insert all 
        when job=upper('clerk') then into clerk
        when job=upper('salesman') then into salesman
        when job=upper('manager') then into manager
        when deptno='10' then into emp10 
        else into others 
        select * from emp;
        --清除表数据
        truncate table clerk;
        truncate table salesman;
        truncate table manager;
        truncate table emp10;
        truncate table others;

        -- 使用first执行多表插入,相当于带了break的switch语句,如果满足了一个条件,然后就退出判断
        insert first 
        when job='CLERK'  then into clerk
        when job='SALESMAN' then into salesman
        when job='MANAGER' then into manager
        when deptno='10' then into emp10
        else into others
        select * from emp;
    --update
        update emp set sal=3500, job=lower(job) where ename ='SCOTT';
        --使用带子查询的update
        update clerk set (mgr,sal) = 
        (select mgr,sal from clerk where ename='JAMES') 
        where ename='rose';
    --delete
        delete from clerk where ename ='rose';
        delete from clerk where mgr=(select empno from emp where ename='SCOTT');

    --meger 数据备份 这里的update和insert和前面的语法有点不同
        create table emp_bak as select * from emp;

        update emp set sal=1500 where ename='SCOTT';
        insert into emp (empno, ename, job, sal) values(1002,'YLW','CLERK', 2500);
        commit;
        --使用on制定目标表和源表之间的关系
        merge into emp_bak D 
        using emp S 
        on (D.empno = S.empNo)
        when matched then update set D.sal=S.sal
        when not matched then 
        insert (D.empno, D.ename, D.job, D.sal)  
        values(S.empno, S.ename, S.job, S.sal);
    --事务控制
        --每次连接数据库,都会打开一个session,
        --每次执行的dml操作都是放入缓存中的,直到用户提交,别的session才看得到修改的数据
        -- commit rollback

        --如果大型事务在尾部出错,全部回滚开销太大,可以设置savepoint
        --savepoint 在事务中定义一些回退的点
        insert into emp(empno,ename) values(1111,'SHAGOU');
        savepoint a;
        insert into emp(empno,ename) values(1112,'SHAGOU');
        savepoint b;
        insert into emp(empno,ename) values(1113,'SHAGOU');
        savepoint c;
        insert into emp(empno,ename) values(1114,'SHAGOU');

        select empno, ename from emp where empno in(1111,1112,1113,1114);
        rollback to c;
        select empno, ename from emp where empno in(1111,1112,1113,1114);
        rollback to a ;
        select empno, ename from emp where empno in(1111,1112,1113,1114);

        --只读事务
        --开启后,相当于对当前数据库进行一次快照,之后修改的数据,在此事务中无法获得
        set transaction read only;
        select sal form emp where ename ='SCOTT';

        --顺序事务
        --可以执行dml的只读事务
        set transaction isolation level serializable;