在Oracle存储过程中使用字符串

本文介绍了在Oracle存储过程中使用字符串的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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)));