记录一个存储过程

根据数据库A表中的内容，自动设置数据到B表。

create or replace procedure p_sb_lhjyhdxx as

  -- 定义变量
  v_dqswjg varchar2(1000); --当前税务机关
  v_sbjbjgdm varchar2(32);--社保经办机构代码
  v_count number(20); --计数
  v_skssqq date;
  v_skssqz date;
  v_msg varchar(512); --错误信息
  --把社保经办机构当做游标
  cursor CUR_SBJBJGDM is
    SELECT b.sbjbjg_dm FROM dm_gs_sbjbjg1 b; 


begin
   v_skssqq :=to_date(to_char(trunc(sysdate,'month'),'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss');
   v_skssqz :=to_date(to_char(last_day(sysdate),'yyyy-mm-dd '),'yyyy-mm-dd hh24:mi:ss');

  --获取当地配置的税务机关
  select x.csnr
    into v_dqswjg
    from xt_xtcspz x
   where x.csdm = 'SBF_GY_REGION';


   --根据社保经办机构遍历数据
  FOR CUR_SBJBJGDM_ROW IN CUR_SBJBJGDM LOOP
    begin
  v_sbjbjgdm := CUR_SBJBJGDM_ROW.SBJBJG_DM;

   select count(1) into v_count from sb_sbf_lhjyhdxx_error_log t where t.sbjbjgdm = v_sbjbjgdm  and t.msg='反写反馈表成功' and t.skssqq >=v_skssqq and t.skssqz<=v_skssqz;
   if v_count >0 then
    CONTINUE;
--新建日志表，记录正确和错误信息
   end if;

    --获取登记数据（插入反馈表的主要内容）反写反馈表
    insert into qz_hdsj_fk
      (sbwylsh,
       xxh,
       sbjbjgdm,
       jfrlx,
       sbbm,
       zsxmdm,
       zspmdm,
       zszmdm,
       jfbl,
       jfje,
       skssqq,
       skssqz,
       hdscrq,
       jfjs,
       hdjfbl,
       hdjfje,
       sjclzt,
       clbz)
      select sys_guid(), --uuid
             1474,
             t.sbjbjg_dm,
             t.jfrlx,
             t.sbbm,
             t.zsxm_dm,
             t.zspm_dm,
             t.zszm_dm,
             t.jffl,
             (select t.jffl * b.dczbbl * (select c.tcspgz
                                   from cs_shpjgzb c
                                  where c.zsxm_dm = t.zsxm_dm
								    and c.yxbz ='Y'
									and c.xybz ='Y'
									and c.qybj ='Y'
								
                                    and c.swjg_dm = (select x.zgswjg_dm  from  dj_sbhdj x where x.sbdjxh = t.sbdjxh))
                from cs_jfdcwh b
               where b.zsxm_dm = t.zsxm_dm and b.swjg_dm = v_dqswjg and  b.jfdc_dm=t.jfdc_dm ) jfje, --缴费金额
           v_skssqq,
           v_skssqz,
            sysdate hdscrq, --日期
              (select b.dczbbl * (select c.tcspgz
                                   from cs_shpjgzb c
                                  where c.zsxm_dm = t.zsxm_dm
								    and c.yxbz ='Y'
									and c.xybz ='Y'
									and c.qybj ='Y'
								
                                    and c.swjg_dm = (select x.zgswjg_dm  from  dj_sbhdj x where x.sbdjxh = t.sbdjxh))
                from cs_jfdcwh b
               where b.zsxm_dm = t.zsxm_dm and  b.jfdc_dm=t.jfdc_dm ) jfjs,--缴费基数
             t.jffl,
              (select  t.jffl * b.dczbbl * (select c.tcspgz
                                   from cs_shpjgzb c
                                  where c.zsxm_dm = t.zsxm_dm
								    and c.yxbz ='Y'
									and c.xybz ='Y'
									and c.qybj ='Y'
									
                                    and c.swjg_dm = (select x.zgswjg_dm  from  dj_sbhdj x where x.sbdjxh = t.sbdjxh))
                from cs_jfdcwh b
               where b.zsxm_dm = t.zsxm_dm and b.swjg_dm = v_dqswjg and  b.jfdc_dm=t.jfdc_dm) hdjfje, --核定缴费金额（跟缴费金额一样）
             '0' sjclzt,
             '1' clbz
        from dj_sbfcbxzmx t
       where t.sbjbjg_dm = CUR_SBJBJGDM_ROW.SBJBJG_DM  and t.yxbz='Y' and t.ksjfrq <= v_skssqq and t.zzjfrq >= v_skssqz and t.jfrlx='1' and t.jfdc_dm is not null;

     -- 成功写入
      insert into sb_sbf_lhjyhdxx_error_log
    (sbjbjgdm, successCount, msg,skssqq,skssqz,lrrq)
     values
    (v_sbjbjgdm, '0', '反写反馈表成功', v_skssqq, v_skssqz, SYSDATE);
     commit;

     --未成功写入
    exception   --捕获异常
    when others then
       v_msg := substr(sqlerrm, 0, 300); --记录错误信息
       insert into sb_sbf_lhjyhdxx_error_log
      (sbjbjgdm, successCount, msg, skssqq,skssqz,lrrq)
       values
      (v_sbjbjgdm, '0',  '反写反馈表失败'|| v_msg,  v_skssqq, v_skssqz, SYSDATE);
       commit;
     end;

    END LOOP;

  end p_sb_lhjyhdxx;

树结构向上查找：

select bb.* from (
select a.SWJG_DM, b.tcspgz
  from dm_gy_swjg a, cs_shpjgzb b
 where a.SWJG_DM = b.swjg_dm
   and b.zsxm_dm = '10204'
 start with a.SWJG_DM = '24601000000'
connect by prior a.sjSWJG_DM = a.SWJG_DM
) bb where rownum=1;




select a.SWJG_DM
  from dm_gy_swjg a
 start with a.SWJG_DM = '24690210000'
connect by prior a.sjSWJG_DM = a.SWJG_DM;