更新已填充表的过程 [英] Procedure to update a table which is already populated

问题描述

我想创建一个向现有表中添加新行的过程.但是，使用当前过程，我正在重写整个表.当前过程的代码是

I want to create a procedure that adds new rows to an already existing table.But with the current procedure I've, I'm rewriting the entire table. The code for the current procedure is

CREATE TYPE t_tf_row AS OBJECT (
id           NUMBER,
description  VARCHAR2(50));

CREATE TYPE t_tf_tab IS TABLE OF t_tf_row;

create or replace procedure add_n_rows(
n_rows in number)
is
  l_tab t_tf_tab := t_tf_tab();

begin
  for i in l_tab.count .. l_tab.count + n_rows
   loop
     l_tab.extend;
     l_tab(l_tab.last) := t_tf_row(i, 'Description for '|| i);

     end loop;
end;

在这里，每次我重写整个l_tab时.我想更新一个已经更新的.为我建议所需过程的正确方法.谢谢

Here, everytime I'm rewriting the entire l_tab. I want to update the one which is already updated. Suggest me the right method for the required procedure.Thanks

推荐答案

这是因为您正在重新创建对象.您需要将对象的实例化版本作为参数传递给过程:

This is because you're re-creating the object. You need to pass an instantiated version of the object into the procedure as a parameter:

create or replace procedure add_n_rows(
    Pn_rows in number
  , P_tab in out t_tf_tab ) is

begin
  for i in P_tab.count .. P_tab.count + Pn_rows
   loop
     P_tab.extend;
     P_tab(l_tab.last) := t_tf_row(i, 'Description for '|| i);    
   end loop;
end;

我已将P_tab声明为OUT参数，这意味着您可以对其进行更改.如果您不想这样做，请删除"out"并声明一个类型为t_tf_tab的局部变量，然后可以对其进行更改.

I've declared P_tab as an OUT parameter, this means you can alter it. If you don't want to do this then remove "out" and declare a local variable of the type t_tf_tab, which you can then alter.

然后您可以分别调用它，例如:

You can then call it separately, for instance:

declare
   l_tab t_tf_tab := t_tf_tab();
begin
   l_tab.extend;
   l_tab(l_tab.last) := t_tf_row(1. 'Hello');
   add_n_rows(3, l_tab);
end;

这篇关于更新已填充表的过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！