遍历列表并插入表中 [英] Loop over list and insert into table

问题描述

我想从一个表中获取价值，然后扔到另一个表中.我有功能，我正在做大量收集到列表中.豆列表.

I want to take value from one table and throw into another table. I have function where Im doing a bulk collect into a list. A List of beans.

     FUNCTION get_things_info ( p_part_id IN NUMBER)
  RETURN bean_list
IS
  thing_list  bean_list;
  BEGIN

  SELECT thing_bean (id, file_name, file_type, dbms_lob.getlength(thing), auditable)
  BULK COLLECT INTO thing_list
  FROM part_things
  WHERE part_id = p_part_id;

  RETURN thing_list;

  END get_things_info_by_id;

我想获取该列表，对其进行迭代，然后将其放入具有相同数据类型的已删除表中.我有一个基于某些Java代码执行插入的过程:

I want to take that list, iterate over it and put in a deleted table with the same data types. I have a procedure that does an insert based off some java code:

PROCEDURE insert_thing(p_thing_bean IN OUT NOCOPY file_thing_bean, p_user_id IN NUMBER)
   IS
   BEGIN


  INSERT INTO deleted_part_things
    (id, part_id, file_name, file_type, thing, editable)
  VALUES ( p_thing_bean.id, 
          p_thing_bean.parent_id, 
          p_thing_bean.file_name, 
          p_thing_bean.file_type,
          p_thing_bean.attachment, 
          p_thing_bean.editable);

   END insert_thing;

它不必使用此过程.我只需要知道如何遍历从第一个函数返回的列表并将其插入deleted_part_thing表

It does not have to use this procedure. I just need to know how to loop over the list I got back from the first function and insert into the deleted_part_thing table

推荐答案

您可以使用

You could use FORALL This would then iterate through the collection supplied as a parameter inserting the records as required, I have given you a similar example you can amend to suit your needs:

PROCEDURE insert_from_list (
   p_bean_list IN bean_list
)
IS
BEGIN
   FORALL x IN INDICES OF p_bean_list
      INSERT INTO deleted_hot_part_attachments
      (id, hot_part_id, file_name, file_type, attachment, auditable)
      VALUES (
         p_bean_list(x).id,
         p_bean_list(x).parent_id,
         p_bean_list(x).file_name,
         p_bean_list(x).file_type,
         p_bean_list(x).attachment,
         p_bean_list(x).auditable
      );
      --
      COMMIT;
END insert_from_list; 

希望有帮助...

如果您使用的是10g或更早版本，则需要在将其插入之前，将记录拉入与表要插入的结构相同的集合中，然后再调用:

If you are using 10g or earlier, you'll need to pull the records into a collection that is the same structure as the table to insert into before then calling:

FORALL x IN INDICES OF <new_collection>
   INSERT INTO deleted_hot_part_attachments
   VALUES <new_collection>(x);

因此，如果deleted_hot_part_attachments的结构与集合类型bean_list完全匹配，则您的FORALL将是:

So if the structure of deleted_hot_part_attachments matches exactly the collection type bean_list then your FORALL would be:

FORALL x IN INDICES OF p_bean_list
   INSERT INTO deleted_hot_part_attachments
   VALUES p_bean_list(x);

如果没有，那么您将需要以下内容:

If it does not then you'll need something like:

PROCEDURE insert_from_list (
   p_bean_list IN bean_list
)
IS
   -- Declare collection to hold table values
   TYPE dhpa_tabtype IS TABLE OF deleted_hot_part_attachments%ROWTYPE
        INDEX BY PLS_INTEGER;
   dhpa_tab dhpa_tabtype;
BEGIN
   -- Loop through the bean list collection populating the 
   -- new dhpa_tab collection with the required values
   FOR i IN p_bean_list.FIRST .. p_bean_list.LAST
   LOOP
      dhpa_tab(i).id         := p_bean_list(i).id;
      dhpa_tab(i).parent_id  := p_bean_list(i).parent_id;
      dhpa_tab(i).file_name  := p_bean_list(i).file_name;
      dhpa_tab(i).file_type  := p_bean_list(i).file_type;
      dhpa_tab(i).attachment := p_bean_list(i).attachment;
      dhpa_tab(i).auditable  := p_bean_list(i).auditable;
   END LOOP;

   -- Populate the table using the new dhpa_tab collection values
   FORALL x IN INDICES OF dhpa_tab
      INSERT INTO deleted_hot_part_attachments
      VALUES dhpa_tab(x);

   --
   COMMIT;
END insert_from_list; 

这篇关于遍历列表并插入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！