如何从oracle中的cte将记录插入到变量中? [英] How to insert records into variables from cte in oracle?

问题描述

我有一个过程，我想将 cte 中的所有记录提取到 Names 变量中.但是这段代码并没有从 CTE 写入名称.如何将记录提取到名称中，以便以后可以遍历名称并获取 field_name 的内容?

I have a procedure in which I want to fetch all records from cte into Names variable. But this code is not writing into names from CTE. How can I fetch records into names so that I can later loop through names and get content of field_name?

CREATE OR REPLACE PROCEDURE sp_market
IS
    Names VARCHAR2(32767);
BEGIN
    WITH CTE(sqql) As 
    (
      SELECT   field_name sqql FROM   pld_medicare_config  
    )  
    SELECT sqql into Names   from CTE;      
END sp_market;

推荐答案

我认为最好的办法是创建一个关联数组并使用 BULK COLLECT 来填充表.最简单的代码如下所示:

I think your best bet is to create a associative array and use BULK COLLECT to populate the table. In its simplest form, the code would look like this:

CREATE OR REPLACE PROCEDURE sp_market IS
  TYPE lt_names IS TABLE OF VARCHAR2(32767) INDEX BY PLS_INTEGER;
  l_tNames lt_names;
BEGIN
  SELECT field_name
  BULK COLLECT INTO l_tNames
  FROM pld_medicare_config

  IF l_tNames.COUNT() > 0 THEN
    FOR i IN l_tNames.FIRST..l_tNames.LAST LOOP
      NULL; --REPLACE WITH YOUR LOGIC
    END LOOP;
  END IF;
END;
/

一些注意事项:

• 我假设您已将 MAX_STRING_SIZE 设置为 EXTENDED.否则，VARCHAR2 就会出现这么大的问题.
• 正如我所说，这是最简单的方法.如果您期待一个巨大的结果集，您将需要考虑将其分块.这篇 Oracle 博客文章非常有帮助，它为您提供了如何执行批量处理的多种选择.(博客帖子)

• I'm assuming that you've set MAX_STRING_SIZE to EXTENDED. Otherwise, you'll have an issue with VARCHAR2 that big.
• As I said, that is the simplest way to do this. If you're expecting a huge result set, you'll want to look into chunking it up. This Oracle blog post is very helpful in giving you multiple options for how to perform bulk processing. (Blog Post)

这篇关于如何从oracle中的cte将记录插入到变量中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！