如何在 Oracle PL/SQL 中填充我自己创建的数据类型的变量? [英] How can fill a variable of my own created data type within Oracle PL/SQL?

问题描述

在 Oracle 中我创建了一个数据类型:

In Oracle I've created a data type:

TABLE of VARCHAR2(200)

我想在存储过程(本地定义，而不是数据库中的实际表)中有一个这种类型的变量，并用数据填充它.

I want to have a variable of this type within a Stored Procedure (defined locally, not as an actual table in the DB) and fill it with data.

一些在线示例展示了我如何使用我的类型，如果它被填充并作为参数传递给存储过程:

Some online samples show how I'd use my type if it was filled and passed as a parameter to the stored procedure:

SELECT column_value currVal FROM table(pMyPassedParameter)

但是我想要的是在 PL/SQL 代码本身期间使用 INSERT 语句填充它.

However what I want is to fill it during the PL/SQL code itself, with INSERT statements.

有人知道这个语法吗?

我应该澄清一下:我的源数据作为传递给存储过程的 VARCHAR2 参数输入:分隔符(如逗号)分隔的字符串.我已经在遍历分隔字符串以获取每个单独的值 - 我想将每个值插入到我的类型中，以便我可以将其视为其余逻辑的 TABLE.

I should have clarified: my source data is entered as a VARCHAR2 parameter passed to the stored procedure: a separator (like comma) delimited string. I'm already iterating through the delimited string to get every separate value - I would like to INSERT each one into my type so I can treat it as a TABLE for the rest of the logic.

推荐答案

"我要的是在PL/SQL期间填充它代码本身，带有 INSERT 语句"

"I want is to fill it during the PL/SQL code itself, with INSERT statements"

这就像填充任何其他 PL/SQL 变量:我们必须使用 INTO.只是因为我们要填充多行，所以我们需要使用 BULK COLLECT 语法.

It's like populating any other PL/SQL variable: we have to use INTO. Only because we're populating multiple rows we need to use the BULK COLLECT syntax.

declare
    l_array your_nested_table_type;
begin
    select col1
    bulk collect into l_array
    from t72;
end;
/

如果结果集返回大量记录，最好在循环内使用 LIMIT 子句.这是因为 PL/SQL 集合——就像所有其他 PL/SQL 变量一样——保存在会话内存中.所以我们不希望阵列变得太大，否则它可能会炸毁 PGA.找出更多.

If the result set returns a lot of records it is a good idea to use the LIMIT clause inside a loop. This is because PL/SQL collections - just like every other PL/SQL variable - are held in session memory. So we don't want the array to get too big, otherwise it might blow the PGA. Find out more.

编辑

我编辑了问题以澄清特别是我想要的"

"I edited the question to clarify specifically what I want"

sigh 对字符串进行标记是一个完全不同的问题.我之前在两个 SO 线程中发布了解决方案.如果您使用的是 9i 或更早版本，请使用这种方法.否则使用 这个正则表达式解决方案(实际上这将字符串拆分为数字标记，但很容易转换为字符).

sigh Tokenizing a string is an altogether different issue. I have previously posted solutions in two SO threads. If you're using 9i or earlier use this approach. Otherwise use this regex solution (actually this splits the string into numeric tokens, but it is easy enough to convert to characters).

编辑 2

"我只想能够使用输入内部"(在 Stored过程)通过向其添加值.是这是我可以用第一个做的链接?"

"I only want to be able to use the type "internally" (within the Stored Procedure) by adding values to it. Is this something I can do with the first link?"

当然.为什么不呢?

SQL> declare
  2      local_array tok_tbl;
  3  begin
  4      local_array := parser.my_parse('Keith Pellig,Peter Wakeman,Ted Bentley,Eleanor Stevens');
  5      local_array.extend();
  6      local_array(5) := 'Reese Verrick';
  7      for i in local_array.first()..local_array.last()
  8      loop
  9          dbms_output.put_line(local_array(i));
 10      end loop;
 11  end;
 12  /
Keith Pellig
Peter Wakeman
Ted Bentley
Eleanor Stevens
Reese Verrick

PL/SQL procedure successfully completed.

SQL>

这里我重新使用了我的 SQL 类型，但如果 TOK_TBL 在 PL/SQL 包中声明，它也能正常工作.

Here I have re-used my SQL type, but it would work just as well if TOK_TBL were declared in the PL/SQL package instead.

这篇关于如何在 Oracle PL/SQL 中填充我自己创建的数据类型的变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！