create table parent( id number(10), name varchar2(100), title varchar2(10) );create table child( id number(10), parent_id number(10), child_name varchar2(100), child_title varchar2(10), child_content varchar2(200), child_time timestamp );create sequence seq_p_c_id minvalue 1 maxvalue 9999999999 start with 1 increment by 1 nocache;drop type t_child_lst_map; drop type t_child_lst; drop type t_parent_lst;create or replace type t_parent as object ( name varchar2(100), title varchar2(10) ); /create or replace type t_child as object ( child_name varchar2(100), child_title varchar2(10), child_content varchar2(200) ); /create or replace type t_parent_lst as table of t_parent; /create or replace type t_child_lst as table of t_child; /create or replace type t_child_lst_map as table of t_child_lst; /create or replace procedure proc_ins_parent_child( i_parent_lst in t_parent_lst, --parent列表 i_child_map_lst in t_child_lst_map, --child列表集合,一个map元素对应一个child_lst,其下标与 parent列表的下标相同。 o_ret out number ) as var_parent t_parent; var_child_lst t_child_lst; var_child t_child; var_parent_id number; var_child_id number; begin for i in 1..i_parent_lst.count loop --取得parent各列的值 var_parent := i_parent_lst(i); --取得parent_id; select seq_p_c_id.nextVal into var_parent_id from dual;
--插入parent表 insert into parent( id, name, title ) values( var_parent_id, var_parent.name, var_parent.title );
--取得该parent对应的child列表 var_child_lst := i_child_map_lst(i); for j in 1..var_child_lst.count loop var_child := var_child_lst(j);
--取得child_id; select seq_p_c_id.nextVal into var_child_id from dual; --插入child表 insert into child( id, parent_id, child_name, child_title, child_content, child_time ) values( var_child_id, var_parent_id, var_child.child_name, var_child.child_title, var_child.child_content, systimestamp ); end loop;
end loop; o_ret := 0; exception when others then begin o_ret := -1; raise; end; end proc_ins_parent_child; /
不要数组的话,直接用type做参数即可。
返回一个集合? drop type t_sms_message_lst; drop type t_sms_message;--此类型中,添加了pk_id create or replace type t_sms_message as object ( task_id number(32), sender_num varchar2(21), mobile_no varchar2(21), param_value varchar2(900), pk_id number(32) ); /create or replace type t_sms_message_lst as table of t_sms_message; /drop procedure proc_sel_sms_message;create or replace procedure proc_sel_sms_message( in_task_id IN number, in_count IN number, out_sms_message_lst OUT t_sms_message_lst, out_ret OUT number )as var_cr_sms_message SYS_REFCURSOR;var_m_row_id varchar2(1000); var_m_task_id sms_message.task_id%TYPE; var_m_sender_num sms_message.sender_num%TYPE; var_m_mobile_no sms_message.mobile_no%TYPE; var_m_param_value sms_message.param_value%TYPE; var_m_last_send_time sms_message.last_send_time%TYPE;var_t_sms_message t_sms_message; var_total_send_amount integer;begin --查询语句增加了pk_id搜索项 open var_cr_sms_message for select rowid as row_id,task_id,sender_num,mobile_no,param_value,pk_id from sms_message where task_id = in_task_id and (sms_message.last_send_time is null or sms_message.last_send_time <> (select last_send_time from sms_task where task_id = in_task_id)) and rownum <= in_count for update; out_sms_message_lst := t_sms_message_lst(); select last_send_time into var_m_last_send_time from sms_task where task_id = in_task_id; loop --fetch语句增加了pk_id项 fetch var_cr_sms_message into var_m_row_id,var_m_task_id,var_m_sender_num,var_m_mobile_no,var_m_param_value,var_m_pk_id; exit when var_cr_sms_message%NOTFOUND; --输出对象类型中增加了pk_id项 var_t_sms_message := t_sms_message(var_m_task_id,var_m_sender_num,var_m_mobile_no,var_m_param_value,var_m_pk_id); out_sms_message_lst.extend; out_sms_message_lst(out_sms_message_lst.count) := var_t_sms_message; update sms_message set last_send_time = var_m_last_send_time where rowid = var_m_row_id;
end loop; close var_cr_sms_message; out_ret := 0; exception when others then begin out_ret := -1; raise; end; end proc_sel_sms_message; /
create table parent(
id number(10),
name varchar2(100),
title varchar2(10)
);create table child(
id number(10),
parent_id number(10),
child_name varchar2(100),
child_title varchar2(10),
child_content varchar2(200),
child_time timestamp
);create sequence seq_p_c_id
minvalue 1
maxvalue 9999999999
start with 1
increment by 1
nocache;drop type t_child_lst_map;
drop type t_child_lst;
drop type t_parent_lst;create or replace type t_parent as object (
name varchar2(100),
title varchar2(10)
);
/create or replace type t_child as object (
child_name varchar2(100),
child_title varchar2(10),
child_content varchar2(200)
);
/create or replace type t_parent_lst as table of t_parent;
/create or replace type t_child_lst as table of t_child;
/create or replace type t_child_lst_map as table of t_child_lst;
/create or replace procedure proc_ins_parent_child(
i_parent_lst in t_parent_lst, --parent列表
i_child_map_lst in t_child_lst_map, --child列表集合,一个map元素对应一个child_lst,其下标与 parent列表的下标相同。
o_ret out number
) as
var_parent t_parent;
var_child_lst t_child_lst;
var_child t_child;
var_parent_id number;
var_child_id number;
begin
for i in 1..i_parent_lst.count loop
--取得parent各列的值
var_parent := i_parent_lst(i); --取得parent_id;
select seq_p_c_id.nextVal into var_parent_id from dual;
--插入parent表
insert into parent(
id,
name,
title
)
values(
var_parent_id,
var_parent.name,
var_parent.title
);
--取得该parent对应的child列表
var_child_lst := i_child_map_lst(i); for j in 1..var_child_lst.count loop
var_child := var_child_lst(j);
--取得child_id;
select seq_p_c_id.nextVal into var_child_id from dual; --插入child表
insert into child(
id,
parent_id,
child_name,
child_title,
child_content,
child_time
)
values(
var_child_id,
var_parent_id,
var_child.child_name,
var_child.child_title,
var_child.child_content,
systimestamp
);
end loop;
end loop;
o_ret := 0; exception when others then
begin
o_ret := -1;
raise;
end;
end proc_ins_parent_child;
/
drop type t_sms_message_lst;
drop type t_sms_message;--此类型中,添加了pk_id
create or replace type t_sms_message as object (
task_id number(32),
sender_num varchar2(21),
mobile_no varchar2(21),
param_value varchar2(900),
pk_id number(32)
);
/create or replace type t_sms_message_lst as table of t_sms_message;
/drop procedure proc_sel_sms_message;create or replace procedure proc_sel_sms_message(
in_task_id IN number,
in_count IN number,
out_sms_message_lst OUT t_sms_message_lst,
out_ret OUT number
)as
var_cr_sms_message SYS_REFCURSOR;var_m_row_id varchar2(1000);
var_m_task_id sms_message.task_id%TYPE;
var_m_sender_num sms_message.sender_num%TYPE;
var_m_mobile_no sms_message.mobile_no%TYPE;
var_m_param_value sms_message.param_value%TYPE;
var_m_last_send_time sms_message.last_send_time%TYPE;var_t_sms_message t_sms_message;
var_total_send_amount integer;begin --查询语句增加了pk_id搜索项
open var_cr_sms_message for
select rowid as row_id,task_id,sender_num,mobile_no,param_value,pk_id
from sms_message
where task_id = in_task_id
and (sms_message.last_send_time is null or sms_message.last_send_time <> (select last_send_time from sms_task where task_id = in_task_id))
and rownum <= in_count
for update; out_sms_message_lst := t_sms_message_lst(); select last_send_time into var_m_last_send_time from sms_task where task_id = in_task_id; loop
--fetch语句增加了pk_id项
fetch var_cr_sms_message into var_m_row_id,var_m_task_id,var_m_sender_num,var_m_mobile_no,var_m_param_value,var_m_pk_id;
exit when var_cr_sms_message%NOTFOUND; --输出对象类型中增加了pk_id项
var_t_sms_message := t_sms_message(var_m_task_id,var_m_sender_num,var_m_mobile_no,var_m_param_value,var_m_pk_id);
out_sms_message_lst.extend;
out_sms_message_lst(out_sms_message_lst.count) := var_t_sms_message; update sms_message set last_send_time = var_m_last_send_time
where rowid = var_m_row_id;
end loop; close var_cr_sms_message; out_ret := 0; exception when others then
begin
out_ret := -1;
raise;
end;
end proc_sel_sms_message;
/
兄弟,你的太复杂了,每次都copy这么多出来,不易阅读啊,不过还是谢谢你,你有没有简单一些的例子啊,就是一个样例而已啊。你不用欧冠每次把你项目中的存储过程贴出来吧!
真的一点都不复杂,你关键的几句看看就明白啊,比如loop里面那个给自定义类型赋值,给数组加长度,给数组的最后一个成员赋值为自定义类型
其他都相当于drop type,create type啊