create or replace
procedure P_PL_Test(day_date in char) is
cursor cur is
  select t1.tg_id,t1.tg_name,t2.d_prov_pq,nvl(t3.idx_upq,0) 
  from pl_jyx_tg t1
    left join pl_calc_prov_pq t2 on t1.tg_id = t2.prov_no
    left join (
      select t3.tg_id,sum(t4.idx_upq) idx_upq from PL_JYX_MP t1 
      inner join pl_jyx_cons t2 on t1.cons_id = t2.cons_id
      inner join pl_jyx_tg t3 on t1.tg_id = t3.tg_id
      inner join pl_cons_upq_lowvolt5 t4 on t2.cons_id = t4.cons_id
      where t3.tg_name in ('杨叶七组','白沙4组','白沙5组','白沙7组','书香小区')
      group by t3.tg_id
    ) t3 on t1.tg_id = t3.tg_id
  where t1.tg_name in ('杨叶七组','白沙4组','白沙5组','白沙7组','书香小区');
cur_row cur%rowtype;

begin 
  if(cur%isopen) then
    close cur;
  end if;
  
  open cur;
  loop
    fetch cur 
      into cur_row;
    exit when cur%notfound;
    
    begin
      dbms_output.put_line(cur_row.tg_name);-- 打印信息
    end;
    
  end loop;
  --commit;--如果update要提交
end P_PL_Test;

create or replace 
FUNCTION             "CHECK_EMP_NO" (dt_no_p varchar2,emp_no_p varchar2) RETURN varchar2 IS
 
 result varchar2(100);
 v_emp_no varchar2(100);
 v_counts varchar2(100);
/******************************************************************************
   NAME:       check_emp_no
   PURPOSE:    check the 148 factory process reporter emp_no
   确认刷卡员工数据是否存在：
   1、no_emp 代表尚未存在该员工信息
   2.more_emp 代表存在重复的员工信息
   3.返回该员工的员工编号

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2012/11/21   Gavei       1. Created this function.
******************************************************************************/
BEGIN
   result:='';
      select count(emp_no) into v_counts from pt_employee where id_card = emp_no_p or emp_no = emp_no_p;
        if(v_counts = '0')then 
        result :='no_emp';
      elsif (v_counts = '1') then
        result := 'one_emp';
      else
        result := 'more_emp';
      end if;
   RETURN result;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
      result:='no_emp';
       return result;
     WHEN OTHERS THEN
        --Consider logging the error and then re-raise
       result:='no_emp';
       return result;
END check_emp_no;

create or replace 
PROCEDURE           IMP_HY_PRODUCT IS
--terry 20120614 for import CUSTOMER
BEGIN

    UPDATE HY_PRODUCT
    SET PROD_TYPE = 'prod_type_01'
    WHERE CREATOR IS NULL
      AND LAST_UPDATE IS NULL;

    UPDATE HY_PRODUCT
    SET PROD_NO = RTRIM(PROD_NO,'.0')
    WHERE SUBSTR(PROD_NO,LENGTH(PROD_NO)-1,2) = '.0' 
      AND CREATOR IS NULL
      AND LAST_UPDATE IS NULL;
      
    UPDATE HY_PRODUCT
    SET PROD_MAIN_NAME = RTRIM(PROD_MAIN_NAME,'.0')
    WHERE SUBSTR(PROD_MAIN_NAME,LENGTH(PROD_MAIN_NAME)-1,2) = '.0' 
      AND CREATOR IS NULL
      AND LAST_UPDATE IS NULL;
      
    UPDATE HY_PRODUCT
    SET PROD_NAME = RTRIM(PROD_NAME,'.0')
    WHERE SUBSTR(PROD_NAME,LENGTH(PROD_NAME)-1,2) = '.0' 
      AND CREATOR IS NULL
      AND LAST_UPDATE IS NULL;

    UPDATE HY_PRODUCT
    SET CREATOR = 'import',
        CREATED_DATE = SYSDATE,
        LAST_UPDATE = 'import',
        LAST_UPDATE_DATE = SYSDATE
    WHERE CREATOR IS NULL
      AND LAST_UPDATE IS NULL;    

    commit ;

   EXCEPTION
     WHEN NO_DATA_FOUND THEN
     DELETE FROM HY_PRODUCT
     where creator is null
       and created_date is null
       and last_update is null
       and last_update_date is null;
     COMMIT ;
     
--       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
         DELETE FROM HY_PRODUCT
         where creator is null
           and created_date is null
           and last_update is null
           and last_update_date is null;
         COMMIT ;
       RAISE;
END IMP_HY_PRODUCT;