怎么使用object与record类型返回表类型数据

怎么使用object与record类型返回表类型数据

通过函数返回一个表类型的数据
先创建一个object的类型
 

代码:

---


create or replace type varproperty IS object(
pid number,
vid number
);
/
.


---

然后创建一个表类型对应到这个object

代码:

---


CREATE OR REPLACE TYPE auc_property AS TABLE OF varproperty;
/
.


---

创建一个函数返回这个表类型

代码:

---


create or replace function GET_PROPERTY(p_string in varchar2 )
  return auc_property
as
v_str long default p_string || ';';
v_pid varchar2(100);
v_vid varchar2(100);
v_n number;
v_m number;
TYPE auc_property AS TABLE OF varproperty;
v_data auc_property := auc_property();
begin
  loop
    v_n := instr( v_str, ';' );
    v_m := instr( v_str, ':' );
    exit when (nvl(v_n,0) = 0);
    v_pid := ltrim(rtrim(substr(v_str,1,v_m-1)));
    v_vid := ltrim(rtrim(substr(v_str,v_m+1,v_n-v_m-1)));
    v_data.extend;
    v_data(v_data.count) := varproperty(v_pid,v_vid);
      v_str := substr(v_str, v_n+1);
   end loop;
   return v_data;
end;
/
.


---

通过游标返回一个表类型的数据

代码:

---


create or replace procedure update_property is
--variale
   m_aid varchar2(32);
--type  
   type v_array is record (
    aid varchar2(32),
    astatus number,
    aproperty varchar2(4000)
    );
   type t_aucid is table of v_array;
   v_aucid t_aucid := t_aucid();
--cursor
cursor cur_pro is select id,APPROVE_STATUS,PROPERTY
   from auction_property_temp
    where PROPERTY is not null;
--start
begin
   open cur_pro;
    loop
      fetch cur_pro bulk collect into v_aucid limit 1000;
      for i in 1..v_aucid.count loop
        m_aid := v_aucid(i).aid;
        --delete from auction_property
        delete from auction_property where auction_id = m_aid;
        --insert into new recode
        insert into auction_property(auction_id,property_id,prop_vid,status)
          select m_aid,t.*,decode(v_aucid(i).astatus,-1,-1,0) status
            from table(get_property(v_aucid(i).aproperty)) t;
      end loop;
      commit;
   exit when cur_pro%notfound;
   end loop;
   commit;
   close cur_pro;
end update_property;
.


---

注，以上只是实际的例子，具体的使用还要视情况而定