Oracle 02 事务

事务 作为单个逻辑工作单元执行的一系列操作  四大特性 ACID

文章目录

---

前言

Atomicity 原子性:要么都成功，要么都失败  || 两个人转账要么都成功要么都失败                Consistency 一致性：事务执行前后总量保持一致 ||两个人转账 钱的总额不变                    Isolation隔离性 各个事务并发执行独立                                                                                Durability 持久性    持久化操作，不可变 数据库重启不改变事务结果

 事务的生命周期：MySQL是自动提交（自动执行commit），oracle是手工提交

再oracle中，事务的开始的标识 就是第一条DML（增删改）语句，

结束的标识 ：正常结束（commit显示提交；执行 exit;DDL自动执行commit，即 隐式提交）                                  回滚（显示rollback ； 隐式：宕机或断电）

保存点 savepoint：

     可以有多个savepoint ，保存点可以防止rollback后不会回滚所有的结果，语法为  savepoint  保存点的名字(保存点的定义)    rollback to savepoint  保存点的名字 （保存点的应用）   

事务的隔离级别：解决并发问题（脏读 不可重复读 幻读）：隔离级别SQL99标准4种

读未提交  读已提交  可重复读  序列化  ；oracle只支持两种

 切换隔离级别使用  set transaction isolation level Serializable ;(必须在事务第一行执行)，一般使用默认的隔离级别。 对于MySQL则是4种都支持。

---

declare 定义常量 变量 游标  例外（异常）  ；代码写在begin ..... end ；  中间

一、plsql新建

  begin
         dbms_output.put_line('hello word !　');
          
       end; 

显示

二、基础使用语法

不在职员工数（status > 3  ）： 14613 ；   在职员工数（status < 3） :  19037;

考试成绩表中：

数据条数 21827 ；（一个人有多个科目要考，暂未删除离职人员考试成绩）

考试成绩表中 存在问题：

              0.已离职的人员考试成绩也存储在成绩表中

              1.录入考试信息时个别人员工号和姓名没有核对，导致h表中的workcode和c表中的工号（gh）相同，但是h表中的

               

                3. 在职人员工作调动到新部门，成绩表的 单位 仍然是之前的 部门，没有变动

1.解决 工号和姓名不匹配的问题

先将离职人员的信息筛选出来，在考试成绩表(uf_czlj1 c)中的备注（bz1）leavePerson

找到人力资源表（hrmresource  h）,根据表中的  和  字段对照考试成绩表中的姓名工号，将考试成绩表中异常的信息（- 工号（h.workcode=c.gh）校验和姓名（c.xm!））筛选出来

  select subcompanyid1 from hrmresource  where id = 28519
  
  select subcompanyid1 from hrmresource  where loginid= '20151926'
  
   
 select * from  hrmresource  where workcode= '20180823' OR workcode= '20181134'
 select *  from  hrmresource where LOGINID is null --not null 筛选 离职/在职 人员
  select count(*) from  hrmresource where LOGINID is null
 --  where workcode= '20180823' OR workcode= '20181134'
 select * from  uf_czlj1 where gh = '20181134' -- 成长路径考试
 
 
 update uf_czlj1  t1 set t1.dw = 11248    where gh = '20180950'           --  11248
  commit;
  select *  from uf_czlj1 t1 where t1.gh = hrmresource.loginid
  update uf_czlj1  t1 set t1.dw = (select subcompanyid1 from hrmresuorce where uf_czlj1.gh = hrmresource.loginid)   where uf_czlj1.gh = '20151926'  ;         --  subcompanyid1 11248
  commit;
  
  --(select subcompanyid1 from hrmresuorce where uf_czlj1.gh = hrmresource.loginid)  where  uf_czlj1.gh = hrmresource.loginid
   update uf_czlj1  
    set uf_czlj1.dw = (select subcompanyid1,gh,loginid from (select *  from uf_czlj1 c, hrmresource h ) zuhe where  zuhe.gh = '20151926' and   ) 
   --from (select *  from uf_czlj1 , hrmresource ) ch
   where uf_czlj1.gh = '20151926' ;
     commit;  
     
     --新建视图 将考试成绩表与人力资源信息表连接为一个视图，包含考试成绩表的 工号（gh）和人力资源信息表的 loginid , subcompanyid1 , 条件为subcompanyid1 = gh
     select *  from  hrmresource, uf_czlj1 where uf_czlj1.gh = hrmresource.loginid and hrmresource.loginid = '20151926'
     select * from  uf_czlj1 where uf_czlj1.gh = '20171527'  --loginid,subcompanyid1
    --根据传入的工号 自动更新  uf_czlj1.dw 单位
    update uf_czlj1  
      set  uf_czlj1.dw =
      (select  subcompanyid1  from  hrmresource, uf_czlj1 where uf_czlj1.gh = hrmresource.loginid and hrmresource.loginid = '20180948' and rownum = 1   )  
       where uf_czlj1.gh = '20180948'  ;
          commit;  
          
        declare --定义先名字 后类型  := 进行赋值
            psex  varchar2(5) := 'boy' ;
            pname hrmresource.lastname%type ; --使用引用类型使变量随表字段变化
       begin
            select lastname into pname from  hrmresource where loginid = '20180823';
             
            dbms_output.put_line(pname||'- -'||psex);
       end;   
          
       --记录型变量（相当于Java中的类） 将一整行 保存到一个 记录型变量中
      
       declare --定义先名字 后类型  
          kscj_infor uf_czlj1%rowtype ;
       begin
             select * into kscj_infor from  uf_czlj1 where uf_czlj1.id = '83812';
            dbms_output.put_line(kscj_infor.gh||'*******'||kscj_infor.xm);
       end; 
       
       --if 语法 条件直接写 不写括号 成功执行 then 否则 else(elsif) 结尾用 end if;
       declare 
         pnum number :=1 ;
        begin
          if pnum=1 then  dbms_output.put_line('00001');
          elsif pnum=2 then dbms_output.put_line('00002');
          else dbms_output.put_line('00003');
          end if ;
        end;
       
       --循环结构
       --while do...while for
       --1.while : 条件  loop ... end loop;
       --2.do...while :   loop ... end loop 
       --3.for : for i in 1..10  loop  end loop;
       begin 
         for x in 1..5
           loop
             dbms_output.put_line('0000 '||x);
           end loop;
       end;
       
       --while 计算 1 - 5 之和
       declare
        pnum number :=1;
        psum number :=0;
       
         begin 
           loop
             exit when pnum > 5 ; 
              psum:=psum+pnum ;
              pnum:=1+pnum ;
           end loop;
           dbms_output.put_line('sum is  '||psum);
       end;
       
       --游标 cursor 相当于Java中的集合  游标的4个属性 %isopen  %rowcount  %found  %notfound
       --定义 ： cursor 光标名（参数列表）// is //select ....
       --打印全部姓名，工号  select xm,gh from  uf_czlj1
        declare 
          cursor cksxx is  select xm,gh from  uf_czlj1 where xm<1000;
          pxm uf_czlj1.xm%type ;
          pgh uf_czlj1.gh%type ;
       begin
         --打开光标
         open cksxx;
         --循环取出光标元素信息
         loop
           fetch cksxx into pxm ,pgh ; --fetch获取当前行游标元素，之后移动下一行
           exit when cksxx%notfound ;
           dbms_output.put_line(pxm||'<-----姓名-工号--->'||pgh);
         end loop;
         --关闭光标
         close cksxx;
       end;
       
      --删除 考试成绩表 uf_czlj1 中 离职人员的信息 
      --筛选出hrmresource表单中在职人员   --select *  from  hrmresource where LOGINID is not null
      --将 hrmresource 表中的 工号（workcode），字段存到游标中  
      
       --   获取 2020年以来离职的人员 工号
      select  *  from  hrmresource where status > 3 and  lastlogindate > '2021-01-01'  --count(*)
       -- 查询工号20181134是否存在
       select count(*) from uf_czlj1 where gh= '20180962'
     -- 存在，则删除 uf_czlj1 数据
    delete from uf_czlj1 where gh= '20180962';
    commit;
    
    
     declare 
          cursor chrm_workcode  is   select workcode from  hrmresource where status > 3 and  lastlogindate > '2021-01-01';
          pworkcode hrmresource.workcode%type ;
          pgh uf_czlj1.gh%type ;
          pcount integer ;
       begin
         --打开光标
         open cksxx;
         --循环取出光标元素信息
         loop
           fetch cksxx into pxm ,pgh ; --fetch获取当前行游标元素，之后移动下一行
           exit when cksxx%notfound ;
           dbms_output.put_line(pxm||'<-----姓名-工号--->'||pgh);
         end loop;
         --关闭光标
         close cksxx;
       end;
 

0.由于开始时姓名是浏览类型，导致重名的人员姓名信息错误的写入到了考试成绩表中，导致点击姓名（浏览类型字段）后弹出的人员信息不是本人。现在在表中新增加一个文本类型的姓名(xmwb)字段，通过表中的 工号（gh）查询 h表中的workcode对应的姓名（lastname），写入该文本姓名字段中

1.首先将离职的人员标注出来（c.bz1= leaveOffice）

declare 
    cursor c_czljks  is select distinct gh from uf_czlj1 ; --5410
    p_c_gh uf_czlj1.gh%type ; 
    
    --创建h表中的 status 游标，当输入c表的工号则查询h的status ，status > 3 ,说明此人离职，set c.bz1 = 'leaveOffice' 更新
    cursor c_hrm(cs_code uf_czlj1.gh%type) is select status from hrmresource h where h.workcode= cs_code  ;
    p_h_status hrmresource.status%type ; 
    begin 
      --打开游标 c_czljks   1.1
      open c_czljks  ;
      --循环 c_czljks      1.2
      loop
        --游标设置 c_czljks  1.3
        fetch c_czljks  into p_c_gh ;
        exit when  c_czljks%notfound ;
          --打开游标 c_hrm  2.1
          open c_hrm(p_c_gh)  ;--将c 表中的工号输入到 游标2 中
          --循环  c_hrm  2.2
          loop
            --游标设置 c_hrm 2.3
          fetch c_hrm  into p_h_status ;
          exit when  c_hrm%notfound ;
            --开始判断  
            if p_h_status > 3 then 
              update uf_czlj1 c set c.bz1 = 'leaveOffice' where c.gh = p_c_gh ;
              commit ;
            else commit ;
            end if ;
      --关闭循环 c_hrm 2.4
          end loop ;
      --关闭光标 c_hrm  2.5
          close c_hrm ;
         
    
      --关闭循环 c_czljks  1.4
      end loop ;
      --关闭光标 c_czljks  1.5
      close c_czljks ;
    
   end;
   
   select count(*) from uf_czlj1 c where c.bz1 = 'leaveOffice' ; --3912

2.对照c表中的 xm 和 xmwb 字段，两个字段值不相同，就标注出来（bz1= erroName）

由于开始时姓名是浏览类型，导致重名的人员姓名信息错误的写入到了考试成绩表中，由于c表中的xm与h表中的id相对应 ，导致点击姓名（浏览类型字段）后弹出的人员信息不是本人。现在在表中新增加一个文本类型的姓名(xmwb)字段，通过表中的 工号（gh）查询 h表中的workcode对应的姓名（lastname），写入该文本姓名字段中

编写核心执行的SQL语句，即 如何将 工号（c.gh）对应的姓名（h.lastname）更新到c.xmwb

查找 c表中的一条数据用于测试（该工号（gh）对应的姓名为重名的其他人）

 -- 测试查询一条重名的数据 c表中的姓名对应h表的 id 为 29823

select   * 　from uf_czlj1 c where c.gh = '20171527' and c.id = '83527'  ;
 --查找这条数据在h表中对应的lastname id 实际为 26579

select *from hrmresource h where workcode = '20171527';

--更新c表中 id = '83527'  的 xmwb 将lastname(对应 gh为 '20171527' )写入该字段
  update uf_czlj1 c set c.xmwb = (select  lastname from hrmresource h where h.workcode = '20171527' ) where c.id ='83527';
  commit ;

  --更新一条c表的数据（ id = '83527'）
   update uf_czlj1 c set c.xm = (select id from hrmresource h where h.workcode = '20171527' ) where c.id = '83527' ;
   commit ;
 --处理标记该条数据（c.xm != h.id ， ）,将其备注（bz1）标记为 erroName-workcode(gh='20171527' 在c 表中有4条，一条是已处理正确的xm,3条不是)
 update uf_czlj1 c set c.bz1 = 'erroName-workcode' where  xm != (select id from hrmresource h where h.workcode = '20171527' ) ; 
   commit ;

可以看到，测试案例中工号为 20171527 的员工xm字段有异常的都在bz1的字段中有被标注为 erroName-workcode 。

有了以上的测试SQL ，现在尝试更新整张c表，j将 c 表中的工号筛选出来存储到游标中，判断如果之后再对照c表中的 xm 和 xmwb 字段，两个字段值不同，就标注出来（bz1= haveSameName）  代码：

 --将c表中的工号存入 游标 c_czljks_code 中，之后循环遍历 工号游标，当h表中的id对应workcode 与 c表的gh 相同
 --但是 c表的xm与h表中的id 不同，说明c表中的xm是重名，则 c表 bz1='erroName-workcode' 
 declare 
    cursor c_czljks  is select distinct gh,xm from uf_czlj1 ;
    p_c_gh uf_czlj1.gh%type ;
    p_c_xm uf_czlj1.xm%type ;
    begin 
      --打开游标
      open c_czljks  ;
      --循环
      loop
        --游标设置
        fetch c_czljks  into p_c_gh ,p_c_xm ;
        exit when  c_czljks%notfound ;
        -- 判断 p_gh（c.gh）对应的c.xm 与 筛选出来的h.id （select id from hrmresource h where h.workcode = p_gh）
        --如果 c.xm != h.id then c.bz1 = 'haveSameName ' 
        if p_c_xm != (select id from hrmresource h where h.workcode= p_c_gh) then
          update uf_czlj1 c set c.bz1 = 'haveSameName' where c.gh = p_c_gh; 
          commit ;
        else commit ;
        end if ;
      --关闭循环
      end loop ;
      --关闭光标
      close c_czljks ;
  
    end;

报错

使用两个游标，简化if条件

 declare 
    cursor c_czljks  is select distinct gh,xm from uf_czlj1 ;
    p_c_gh uf_czlj1.gh%type ;
    p_c_xm uf_czlj1.xm%type ;
  
    --创建h表中的id游标，当输入c表的工号则查询h的id ，与对应c表的xm对比 用于判断c中的数据 是否重名
    cursor c_hrm(cs_code uf_czlj1.gh%type) is select id from hrmresource h where h.workcode= cs_code  ;
    p_h_id hrmresource.id%type ;
    
    begin 
      --打开游标 c_czljks   1.1
      open c_czljks  ;
      --循环 c_czljks      1.2
      loop
        --游标设置 c_czljks  1.3
        fetch c_czljks  into p_c_gh ,p_c_xm ;
        exit when  c_czljks%notfound ;
          --打开游标 c_hrm  2.1
          open c_hrm(p_c_gh)  ;--将c 表中的工号输入到 游标2 中
          --循环  c_hrm  2.2
          loop
            --游标设置 c_hrm 2.3
          fetch c_hrm  into p_h_id ;
          exit when  c_hrm%notfound ;
            --开始判断 h.id != c.xm (h.workcode = c.gh 获取 c.id)
            if p_h_id != p_c_xm then 
              update uf_czlj1 c set c.bz1 = 'haveSameName' where c.gh = p_c_gh and c.xm = p_c_xm ;
              commit ;
            else commit ; 
            end if ;
           
      --关闭循环 c_hrm 2.4
          end loop ;
      --关闭光标 c_hrm  2.5
          close c_hrm ;
         
    
      --关闭循环 c_czljks  1.4
      end loop ;
      --关闭光标 c_czljks  1.5
      close c_czljks ;
  
    end;

效果如下（更新了4001条数据）

 3.以上是根据工号校验姓名（默认工号是正确的）但是出现姓名正确但是工号不对，即一个正确的姓名在c表中有好几个工号对应，相应的就需要根据姓名（xm）来校验工号（gh）。

但是我们要注意到，既然存在之前的按工号校验姓名，现在又需要根据姓名校验工号，两个校验方式不论哪个在前面，都不能保证最后的结果是准确的。如果也安装2.的方式来进行，那么就会有覆盖2.中正确的标记。 因此，需要引入一个新的参考用以圈定一个范围，确认出  一个姓名有多个工号相对应。

在2.中可知 c.xm对应的h.id ,因为 c表中有多个姓名，即使在2中校验xm是重名的，但是c.xm的值是一致的，因此，即使姓名是错误的重名，也可以筛选出C表中 一个姓名对应多个工号的数据。

---

前提： 先将c表中的xm对应在h表中的lastname（姓名）更新写入到c表的xmwb中；之后将c.bz1的值为  SameName 的数据中 xm字段按照c.gh = h.workcode 获取的h.id更新c.xm 。

---

但是由于姓名异常的也存在 因此需要进行筛选

编写核心执行的SQL语句，即 如何

3.之后寻找 xm对应的 workcode 与gh不同

4. 更新c表中的单位

2.读入数据

代码如下（示例）：

declare 
          cursor chrm_workcode  is select workcode from hrmresource h  where exists ( select 1 from uf_czlj1 c where c.gh= h.workcode and h.status > 3 and  lastlogindate > '2020-06-01');
          pworkcode hrmresource.workcode%type ;
          pxm uf_czlj1.xm%type ;
          pcount integer ;
          
          begin
         --打开光标
         open chrm_workcode;
         --循环取出光标元素信息
         loop
           fetch chrm_workcode into pworkcode ; --fetch获取当前行游标元素，之后移动下一行

           exit when chrm_workcode%notfound ;
           
           --select uf_czlj1.xm  from uf_czlj1 where uf_czlj1.gh = pworkcode  ;
           update uf_czlj1 set uf_czlj1.bz1 = 'leavePerson' where uf_czlj1.gh = pworkcode  ;
           commit;
           dbms_output.put_line( '<---工号->'||pworkcode );

         end loop;
         --关闭光标
         close chrm_workcode;
       
       end;
       
       select * from uf_czlj1 where uf_czlj1.gh = '20160059'
       select subcompanyid1 ,workcode ,lastname   from hrmresource where workcode = '20180948'
  select count(*)  from uf_czlj1 where uf_czlj1.bz1 = 'leavePerson'
 

 

---

总结