oracle 创建存储

create or replace procedure analytic_dynamic_prc( part_str varchar2)
is
type numtab is table of number(18,2) index by binary_integer;  -- 创建数组   index by binary_integer --这个说明使数据可以自动扩展
v_year numtab;
v_week numtab;
v_sale numtab;
v_rak numtab;
v_sql varchar2(1000);


type tst_arr is table of number(18,2);  --创建某一数据类型的数组
v_tst_arr tst_arr := tst_arr(10);


begin
  v_sql:='
  select * from(
  select t1.year,
       t1.week,
       t1.sale,
       rank()over(partition by '||part_str||' order by t1.sale desc) rak
  from sales_fact t1
  )where rak<=3
  ';
  
  execute immediate v_sql bulk collect into v_year,v_week,v_sale,v_rak;
  
  for i in 1..v_sale.count loop
    dbms_output.put_line(v_year(i)||chr(32)||v_week(i)||chr(32)||v_sale(i)||chr(32)||v_rak(i));    
  end loop;
  
  for i in 1..10 loop
    v_tst_arr(i) := i;
    v_tst_arr.extend;  --动态添加
  end loop;
  
  for i in 1..10 loop
    dbms_output.put_line(v_tst_arr(i));
  end loop;
  
end;