存储过程示例

CREATE OR REPLACE PROCEDURE PRO_GET_PLAN
IS
/******************************************************************************
   NAME:       PRO_GET_PLAN
   PURPOSE:
   NOTES:      从配网生产系统获取停电计划生成检修单
   Modified
******************************************************************************/
msg varchar2(1000);
BEGIN

insert into pd_td_outage_apply_bill
      (oid,
       code,
       APPLY_UNIT,
       APPLY_UNIT_ID,
       transformer_id,
       START_TIME,
       END_TIME,
       POWER_BOUND,
       ITEM_AFFILLIATED,
       ITEM_AFFILLIATED_ID,
       EXECUTE_MEANS,
       WRITE_NAME,
       FZRMOBILE_PHONE,
       OUTAGE_TYPE,
       OUTAGE_TYPEID,
       WORK_STAR,
       WORK_END,
       WORK_PROPERTY,
       CONSTRUCT_UNIT,
       FEW,
       WORK_CONTENT,
       TASK_STATE,
       STATUS,
       APPLY_GROUP_NAME,
       APPLY_GROUP,
       CONTENT_USERID,
       CONTENT_NAME,
       MOBILE_PHONE,
       transformer_name)
select oid,
       FC_getoutageapllycode(START_TIME) code,--调用配网生产库生成编号函数，避免数据重复
       dept_name,
       APPLY_UNIT_ID,
       transformer_id,
       START_TIME,
       END_TIME,
       POWER_BOUND,
       ITEM_AFFILLIATED,
       ITEM_AFFILLIATED_ID,
       EXECUTE_MEANS,
       WRITE_NAME,
       FZRMOBILE_PHONE,
       OUTAGE_TYPE,
       OUTAGE_TYPEID,
       WORK_STAR,
       WORK_END,
       WORK_PROPERTY,
       CONSTRUCT_UNIT,
       FEW,
       WORK_CONTENT,
       TASK_STATE,
       STATUS,
       APPLY_GROUP_NAME,
       APPLY_GROUP,
       CONTENT_USERID,
       CONTENT_NAME,
       MOBILE_PHONE,
       description  from
(
select
       distinct
       t1.oid,
       t2.dept_name,
       to_number(t1.apply_unit) APPLY_UNIT_ID,--配网生产数据库中这个字段才是所属分局编号
       t1.transformer_id,
       t1.START_TIME,
       t1.END_TIME,
       t1.POWER_BOUND,
       t1.ITEM_AFFILLIATED,
       t1.ITEM_AFFILLIATED_ID,
       t1.EXECUTE_MEANS,
       t1.WRITE_NAME,
       t1.FZRMOBILE_PHONE,
       t1.OUTAGE_TYPE,
       t1.OUTAGE_TYPEID,
       t1.WORK_STAR,
       t1.WORK_END,
       t1.WORK_PROPERTY,
       t1.CONSTRUCT_UNIT,
       t1.FEW,
       t1.WORK_CONTENT,
       t1.TASK_STATE,
       t1.STATUS,
       t1.APPLY_GROUP_NAME,
       t1.APPLY_GROUP,
       t1.CONTENT_USERID,
       t1.CONTENT_NAME,
       t1.MOBILE_PHONE,
       t3.description from PW_MONTH_PC_PLAN_CHILD_ATP@pwsc_dblink t1,pw_core_dept@pwsc_dblink t2,
v_kxmc_tree t3
where not exists(select 1 from PD_TD_OUTAGE_APPLY_BILL
where t1.oid=PD_TD_OUTAGE_APPLY_BILL.oid)
and t1.apply_unit in ('107')--已经上线的分局
and to_number(t1.apply_unit)=t2.dept_id
and  to_char(t1.start_time,'yyyyMM')>='201211'
and (case
     when instr(t1.transformer_id,',')>0
     then substr(t1.transformer_id,0,instr(t1.transformer_id,',')-1)
     else t1.transformer_id end)=t3.objectsid(+)--配网生产系统中的停电计划变电站可以为空，也可以是多个
and t1.status in ('2I','5E')--此状态表示停电计划已经处理完成  '2I'表示已经发布还未处理完成的 '5E'表示变更发布的
);

COMMIT;
EXCEPTION
    WHEN OTHERS
    THEN
    msg:='获取配网生产系统停电计划生成检修单存储过程出错：'||sqlcode||sqlerrm;
    INSERT INTO RH_SYS_LOG VALUES(SYSDATE,'1050',msg);
    COMMIT;
END PRO_GET_PLAN;