学明写的传神的初始化数据的存储过程

declare 
    cursor c_order_id is select a.row_id from te_cust_order_list a where nvl(a.del_flag,'0')='0' and nvl(a.arc_flag,'0')='0' 
           and f_o_get_customer_info(a.cust_id,'5') in ('运营商','企业客户','市场部')
           and not exists (select 1 from te_order_query q where q.order_id=a.row_id 
            and q.access_type ='1' and q.access_obj_type='1' and q.access_obj_id in 
            (select u.user_id from th_user_pos_rel u where u.pos_id=(select row_id from th_position pos where pos.pos_code='HKXSZL')))
    ;
   
    v_order_id varchar2(100);
begin
     open c_order_id;
          loop
              fetch c_order_id into v_order_id;
              exit when c_order_id%notfound;
              chushihua(v_order_id);
          end loop;
     close c_order_id;
     
end;

 

 

 

CREATE OR REPLACE PROCEDURE chushihua(
v_order_id in varchar2) AS

v_cust_id varchar2(40);
v_cust_attr varchar2(40);
v_order_line_id varchar2(200);

cursor c_order_line_ids is select line.row_id from te_cust_order_line line where line.order_id=v_order_id;

BEGIN
       select cust_id into v_cust_id from te_cust_order_list t where row_id = v_order_id;
       select f_o_get_customer_info(v_cust_id,'5') into v_cust_attr from dual;

       if v_cust_attr='运营商' then
        ---插入 运营关系部的销售助理
       open c_order_line_ids;
            loop
                 fetch c_order_line_ids into v_order_line_id;
                 exit when c_order_line_ids%notfound;
                   insert into te_order_query (row_id,order_id,order_line_id,access_type,access_obj_type,create_by,access_obj_id)
                          select f_get_globalid,order_id,order_line_id,access_type,access_obj_type,create_by,access_obj_id
                          from (
                          select v_order_id as order_id,v_order_line_id as order_line_id ,'1' as access_type,'1' as access_obj_type ,'chenxm' as create_by,a.row_id as access_obj_id from th_user a,th_user_pos_rel b
                          where a.row_id=b.user_id and b.pos_id='2011042600631231'
                          and a.user_dept_id= (select row_id from th_int_dept a where a.dept_code='HKYYGXB'));
            end loop;
       close c_order_line_ids;


    elsif v_cust_attr='企业客户' then
        ---插入 企业客户部的销售助理
       open c_order_line_ids;
            loop
                 fetch c_order_line_ids into v_order_line_id;
                 exit when c_order_line_ids%notfound;
                 insert into te_order_query (row_id,order_id,order_line_id,access_type,access_obj_type,create_by,access_obj_id)
                        select f_get_globalid,order_id,order_line_id,access_type,access_obj_type,create_by,access_obj_id
                        from (
                   
                        select v_order_id as order_id,v_order_line_id as order_line_id ,'1' as access_type,'1' as access_obj_type ,'chenxm' as create_by,a.row_id as access_obj_id from th_user a,th_user_pos_rel b
                        where a.row_id=b.user_id and b.pos_id='2011042600631231'
                        and a.user_dept_id= (select row_id from th_int_dept a where a.dept_code='HKQYKHB'));
             end loop;
       close c_order_line_ids;
   elsif v_cust_attr='市场部' then
        ---插入 企业客户部的销售助理
        open c_order_line_ids;
            loop
                 fetch c_order_line_ids into v_order_line_id;
                 exit when c_order_line_ids%notfound;
                 insert into te_order_query (row_id,order_id,order_line_id,access_type,access_obj_type,create_by,access_obj_id)
                      select f_get_globalid,order_id,order_line_id,access_type,access_obj_type,create_by,access_obj_id
                      from (
                  
                      select v_order_id as order_id,v_order_line_id as order_line_id ,'1' as access_type,'1' as access_obj_type ,'chenxm' as create_by ,a.row_id as access_obj_id from th_user a,th_user_pos_rel b
                      where a.row_id=b.user_id and b.pos_id='2011042600631231'
                      and a.user_dept_id= (select row_id from th_int_dept a where a.dept_code='HKSCB'));
                end loop;
       close c_order_line_ids;
       ---接应收入 暂时没有部门
    end if;
END chushihua;