这里只给一个例子，具体操作请根据实际情况改写。
通过函数返回一个表类型的数据
先创建一个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;
- 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;
- /
有了以上的函数，我们就可以通过如下的方法来调用了，如，我们输入的是一个特定格式的字符串，返回的却是一个表，说白了，就是把列转换成行，甚至是列转换成表：
- SQL> select * from table(get_property('123:234;1000:2000;9876:6789'));
- PID VID
- ---------- ----------
- 123 234
- 1000 2000
- 9876 6789
当然，我们可以采用另外的方法，如在一个存储过程中，调用这个函数，如我们在游标中，把行转换成列，插入到另外一个表中:
- 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;
- /
注，以上只是实际的例子，具体的使用还要视情况而定
评论 (3)
