Array, two-dimensional array, and type, stored procedure call. SQL

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
Max value 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 list
I _child_map_lst in t_child_lst_map, -- child list set. A map element corresponds to a child_lst, and its subscript is the same as that of the parent list.
O_ret out number
)
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
-- Obtain the values of each parent Column
Var_parent: = I _parent_lst (I );

-- Get parent_id;
Select seq_p_c_id.nextval into var_parent_id from dual;

-- Insert a parent table
Insert into parent (
Id,
Name,
Title
)
Values (
Var_parent_id,
Var_parent.name,
Var_parent.title
);

-- Obtain the child list corresponding to the parent
Var_child_lst: = I _child_map_lst (I );

For J in 1 .. var_child_lst.count Loop
Var_child: = var_child_lst (j );

-- Get child_id;
Select seq_p_c_id.nextval into var_child_id from dual;

-- Insert a child table
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;
/