无法将字符串值 1,2 作为输入传递给 oracle 查询 [英] Unable to pass string value 1,2 as input to an oracle query

问题描述

下面是我的查询，我将字符串值 1,2 作为绑定值传递，但它显示错误，因为它不是有效数字.我知道 IN 只接受数字，但在这里我需要传递字符串值

SELECT e.*FROM 员工详细信息 eWHERE e.emp_id IN (:emp_id)

解决方案

In 与 list of values 或 subqueries 一起使用.

您可以使用以下 hack 将逗号分隔的 string 转换为 subquery:

 SELECT TRIM(REGEXP_SUBSTR(temp, '[^,]+', 1, level))FROM (SELECT :emp_id temp FROM DUAL)按级别连接 <= REGEXP_COUNT(temp, '[^,]+')

这里 1,2,3 字符串将被转换为 subquery 返回 3 行.

因此，对于您的情况，最终结果可能是这样的:

SELECT e.*FROM 员工详细信息 eWHERE e.emp_id 在 (SELECT decode(:emp_id,null, (select e.emp_id from dual),TRIM(REGEXP_SUBSTR(temp, '[^,]+', 1, level)) )FROM (SELECT :emp_id temp FROM DUAL)按级别连接 <= REGEXP_COUNT(temp, '[^,]+'))

请注意，在这种情况下，如果 :emp_id 为 null 并且这是故意实现的，则 In 将返回 true通过使用 decode 功能.

Below is my query and i am passing a string value 1,2 as bind value but it is showing an error as it is not a valid number. I know IN accepts only number but here i need to pass the string value

SELECT  e.*
FROM    employee_detail e
WHERE   e.emp_id IN (:emp_id)

解决方案

In is used with list of values or subqueries.

You can convert a comma separeted string into a subquery, by using the following hack:

 SELECT TRIM(REGEXP_SUBSTR(temp, '[^,]+', 1, level))   
    FROM (SELECT  :emp_id temp FROM DUAL)
    CONNECT BY level <= REGEXP_COUNT(temp, '[^,]+')

Here the 1,2,3 sting will be converted into subquery that returns 3 rows.

So, the end result, for your case, could be something like this:

SELECT  e.*
FROM    employee_detail e
WHERE   e.emp_id in (
SELECT decode(:emp_id,null,  (select  e.emp_id from dual) 
,TRIM(REGEXP_SUBSTR(temp, '[^,]+', 1, level)) )  
    FROM (SELECT  :emp_id temp FROM DUAL)
    CONNECT BY level <= REGEXP_COUNT(temp, '[^,]+'))

Note that in this case In will return true if :emp_id is null and this was deliberatly achieved by using decode function.

这篇关于无法将字符串值 1,2 作为输入传递给 oracle 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！