这个SQL要怎么写!!

Question

当然可以了 
v_c_value NumArray := NumArray(0,0,0,0,0,0,0,0);
 v_m_value NumArray := NumArray(0,0,0,0,0,0,0,0);
 v_row_count := 1;
   for i in 1..8 loop
       insert into report_drill(fund_code, re1, num1, num2, num3, num4, num5, num6, num7,user_code)
       values(p_fund_code, p_user_code, v_c_value(i), round(v_c_value(i)/v_fund_nav,4), round(v_c_value(i)/v_Market_Asset_Total,4), 
              v_m_value(i), round(v_m_value(i)/v_fund_nav,4), round(v_m_value(i)/v_Market_Asset_Total,4), v_row_count,p_user_code);
       v_row_count := v_row_count+1;
   end loop;

Answer 1

to wwl007(疑难杂症)
   谢谢,但问题没有完全解决,首先你的数组是这样的语法定义的么
:TYPE type_name IS VARRAY(maximum_size) OF element_type
   如果数组NumArray(),它的存贮量不确定,而是一个变量v_count,那么可以NumArray(v_count)吗?
   如果是在一个FUNCTION(函数)中,那么数组的定义放在哪个地方啊?
   看看我的原代码:
create or replace function TDcount(
p_starttime date,
p_stoptime  date,
p_tranname  tran.tranname%type,
p_uppowerpercent number,
p_downpowerpercent number,
p_upcurrpercent number,
p_downcurrpercent number,
p_upvolpercent number,
p_downvolpercent number)
return integer IS
v_gatherid gather.gatherid%type;
v_intervaltime   integer;
v_standpower tran.standpower%type;
v_standcul   tran.standcul%type;
v_standvol   tran.standvol%type;
v_intervalvalue number;
v_intervalvalue1 number;
v_upcurrcount transtate.upcurrcount%type;
v_upvolcount  transtate.upcurrcount%type;
v_uppowercount transtate.upcurrcount%type;
v_downcurrcount transtate.upcurrcount%type;
v_downvolcount  transtate.upcurrcount%type;
v_downpowercount transtate.upcurrcount%type;
a  integer:=1;
b  integer:=1;
c  integer:=1;
type v_varray IS varray(85440) of tran.standpower%type;--数组的定义放到这里可以么?begin
  select gatherid,standpower,standvol,standcul
  into   v_gatherid,v_standpower,v_standcul,v_standvol
  from   tran
  where  tranname=p_tranname;
   if sql%found then
      begin
      v_intervaltime := trunc(p_stoptime)-trunc(p_starttime); 
      v_intervalvalue := v_intervaltime*24;                 
      v_power    v_varray;
      v_voltage  v_varray;
      v_curr     v_varray;
      select power0,power1,power2,power3,power4,power5,power6,power7,power8,power9,power10,power11
            power12,power13,power14,power15,power16,power17,power18,power19,power20,power21,power22,power23       
into v_power      
 from trangatherhourdata 
       where gatherid=v_gatherid and trunc(ddate)<=trunc(p_stoptime) and trunc(ddate)>=trunc(p_starttime);
        for a in 1..v_intervalvalue loop
           if  v_power(a)>v_standpower*(1+p_uppowerpercent) then 
               v_uppowercount:=v_uppowercount+1;
               a:=a+1;           
           elsif v_power(a)<v_standpower*p_downpowerpercent then                
                v_downpowercount:=v_downpowercount+1;
                a:=a+1;
           else a:=a+1;
           end if;
        end loop;
        insert into transtate(uppowercount,downpowercount) values(v_uppowercount,v_downpowercount);
      v_intervalvalue1 := v_intervaltime*24*3;
      select avoltage0,avoltage1,avoltage2,avoltage3,avoltage4,avoltage5,avoltage6,avoltage7,avoltage8, avoltage9,avoltage10, avoltage11,avoltage12,avoltage13,avoltage14,avoltage15,avoltage16,avoltage17,avoltage18,avoltage19,avoltage20,avoltage21,avoltage22,avoltage23,bvoltage0,bvoltage1,bvoltage2,bvoltage3,bvoltage4,bvoltage5,bvoltage6,bvoltage7,bvoltage8, bvoltage9,bvoltage10, bvoltage11,bvoltage12,bvoltage13,bvoltage14,bvoltage15,bvoltage16,bvoltage17,bvoltage18,bvoltage19,bvoltage20,bvoltage21,bvoltage22,bvoltage23,cvoltage0,cvoltage1,cvoltage2,cvoltage3,cvoltage4,cvoltage5,cvoltage6,cvoltage7,cvoltage8, cvoltage9,cvoltage10, cvoltage11,cvoltage12,cvoltage13,cvoltage14,cvoltage15,cvoltage16,cvoltage17,cvoltage18,cvoltage19,cvoltage20,cvoltage21,cvoltage22,cvoltage23    
        into v_voltage--?
        from trangatherhourdata where gatherid=v_gatherid and trunc(ddate)<=trunc(p_stoptime) and trunc(ddate)>=trunc(p_starttime);
        for  b in 1..v_intervalvalue1 loop
           if v_voltage(b)>v_standvol*(1+p_upvolpercent) then
              v_upvolcount:=v_upvolcount+1;
              b:=b+1;
           elsif v_voltage(b)<v_standvol*p_downvolpercent then
              v_downvolcount:=v_downvolcount+1;
              b:=b+1;
            else b:=b+1;
            end if;
         end loop;
         insert into transtate(upvolcount,downvolcount) values(v_upvolcount,v_downvolcount);
      select acurr0,acurr1,acurr2,acurr3,acurr4,acurr5,acurr6,acurr7,acurr8,acurr9,acurr10,acurr11,acurr12,acurr13,acurr14,acurr15,acurr16,acurr17,acurr18,acurr19,acurr20,acurr21,acurr22,acurr23,bcurr0,bcurr1,bcurr2,bcurr3,bcurr4,bcurr5,bcurr6,bcurr7,bcurr8,bcurr9,bcurr10,bcurr11,bcurr12,bcurr13,bcurr14,bcurr15,bcurr16,bcurr17,bcurr18,bcurr19,bcurr20,bcurr21,bcurr22,bcurr23,ccurr0,ccurr1,ccurr2,ccurr3,ccurr4,ccurr5,ccurr6,ccurr7,ccurr8,ccurr9,ccurr10,ccurr11,ccurr12,ccurr13,ccurr14,ccurr15,ccurr16,ccurr17,ccurr18,ccurr19,ccurr20,ccurr21,ccurr22,ccurr23
        into v_curr--?
        from trangatherhourdata where gatherid=v_gatherid and trunc(ddate)<=trunc(p_stoptime)and trunc(ddate)>=trunc(p_starttime);
        for  c in i..v_intervalvalue1 loop
            if v_curr(c)>v_standcul*(1+p_upcurrpercent)then
              v_upcurrcount:=v_upcurrcount+1;
              c:=c+1;
            elsif v_curr(c)<v_standcul*p_downcurrpercent then
              v_downcurrcount:=v_downcurrcount+1;
              c:=c+1;
            else c:=c+1;
             end if;
         end loop;
         insert into transtate(upcurrcount,downcurrcount)values(v_upcurrcount,v_downcurrcount);
         end;
   end if;
   exception
     when others then
         null;
end;
  

Answer 2

数组是这样定义的:
v_power v_varray:=v_varray();--使v_power为数组型