在Oracle存储过程中使用字符串 [英] Using string in Oracle stored procedure

问题描述

当我简单地编写一个查询，其中包含类似的代码

When I simply write a query in which it has code like

Select * from ..
where ...
AND    gfcid in ( select regexp_substr('1005771621,1001035181'||',','\d+',1,level)
        from dual
      connect by level <= (select max(length('1005771621,1001035181')-length(replace('1005771621,1001035181',',')))+1
                             from dual) )

有效.

但是我想在oracle存储过程中使其动态查询.我确实是这样的:

But I want to make it dynamic query in oracle stored procedure. I did like this:

GDFCID_STRING := ' select regexp_substr('
           || '1005771621,1001035181'
           || ','
           || ','
           || '\d+'
           || ',1,level) from dual connect by level <= (select max(length('
           || '1005771621,1001035181'
           || ')-length(replace('
           || '1005771621,1001035181'
           || ','
           || ','
           || ')))+1 from dual)';

Select * from ..  
where ...
AND    gfcid in (GDFCID_STRING)

但是它现在可以工作了.

But it does now work.

推荐答案

据我所知，您需要一种方法来接受以逗号分隔的字符串作为输入，将其分解为整数集合，然后进行比较一个数字(读取为整数)，带有此集合中的值.

As far as I understand your problem, you need a method to accept a comma-delimited string as an input, break it into a collection of integers and then compare a number (read: integer) with the values in this collection.

Oracle主要提供三种类型的集合- varrays ，关联数组.我将解释如何将逗号分隔的字符串转换为嵌套表，并使用它来查询或比较.

Oracle offers mainly three types of collections- varrays, nested tables and associative arrays. I would explain how to convert a comma-delimited string into a nested table and use it to query or compare.

首先，您需要在架构中定义对象类型.只有在模式级别定义此类型时，您才能使用此类型编写查询.

First, you need to define an object type in the schema. You can write queries using this type only if you define it at schema level.

CREATE OR REPLACE TYPE entity_id AS OBJECT (id_val NUMBER(28));
/

CREATE OR REPLACE TYPE entity_id_set IS TABLE OF entity_id;
/

接下来，定义一个这样的函数:

Next, define a function like this:

FUNCTION comma_to_nt_integer (p_comma_delimited_str IN VARCHAR)
    RETURN entity_id_set IS
    v_table     entity_id_set;
BEGIN
    WITH temp AS (SELECT TRIM(BOTH ',' FROM p_comma_delimited_str) AS str FROM DUAL)
        SELECT ENTITY_ID(TRIM (REGEXP_SUBSTR (t.str,
                                    '[^,]+',
                                    1,
                                    LEVEL)))
                   str
          BULK COLLECT INTO v_table
          FROM temp t
    CONNECT BY INSTR (str,
                      ',',
                      1,
                      LEVEL - 1) > 0;

    RETURN v_table;
END comma_to_nt_integer;

您已完成此任务所需的DDL.现在，您只需将查询写为:

You are done with the DDL required for this task. Now, you can simply write your query as:

SELECT *
  FROM ..  
 WHERE ...
       AND gfcid in (table(comma_to_nt_integer(GDFCID_STRING)));

这篇关于在Oracle存储过程中使用字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！