plsql 开发手记 游标，动态sql ，自定义集合

 

CREATE OR REPLACE PROCEDURE applypeople (v_planid IN  NUMBER,v_p_cur out types.cursorType) is
        
    xm split_type;
    v_count NUMBER;
    v_app_apply app_apply%ROWTYPE;
    CURSOR  my_cur IS  SELECT  person.ID AS ID, 
		person.sfz as sfz ,
		
		person.khxm  as ksxm
     
		FROM app_apply person ,app_plan p ,pla_tenant t

		WHERE person.zt=3 
		AND person.plan_id = v_planid
		AND person.plan_id = p.ID AND person.tenant_id = t.ID ORDER BY person.Id;
begin
     
      EXECUTE IMMEDIATE'CREATE  table cellBusyHour_temp as 
       SELECT person.id as pid, person.plan_id as bcid,p.name as bcname, person.NAME as xm ,
		   person.sfz as sfz ,
		   t.tenantname  as tenant,
		   person.khxm  as ksxm
     
		   FROM app_apply person ,app_plan p ,pla_tenant t

		    WHERE person.zt=3 
		    AND person.plan_id = '||v_planid||'
		    AND person.plan_id = p.ID AND person.tenant_id = t.ID order by person.id';   
     
     
       --init the xm 
     xm:=split_type();
    
    
     -- fetch the id of  the appliers
     FOR v_app_apply IN my_cur LOOP
          --dynamic  init the xm
         
          SELECT split(khxm,',') INTO xm FROM app_apply WHERE app_apply.ID = v_app_apply.ID;
         --  to deal with the v_str
          FOR i IN 1..xm.COUNT LOOP
              IF xm(i) IS NOT NULL THEN 
                       --judege the column if exists
                      select count(*) into v_count from User_Tab_Columns where table_name=upper('cellBusyHour_temp') and column_name=upper('khxm'||i);
                      IF v_count=0 THEN 
                      --add the  column 
                    EXECUTE IMMEDIATE'alter table cellBusyHour_temp add (khxm'||i||'  varchar(50))';
                    END IF;
              EXECUTE IMMEDIATE'update cellBusyHour_temp set khxm'||i||'='||''''||xm(i)||''''||'  where cellBusyHour_temp.pid='||v_app_apply.ID;
             
         
              END IF;
              END LOOP;
     
   
  
     
     END LOOP;
     
     -- open the cur
    OPEN v_p_cur FOR 'SELECT * FROM cellBusyHour_temp';
    EXECUTE IMMEDIATE 'drop TABLE cellBusyHour_temp';
    
   
		
end ;