在Oracle BIND变量中声明多个值 [英] Declaration of multiple values in Oracle BIND Variables

问题描述

我正在尝试将大约3000个值的多个值传递给Oracle SQL PLUS命令提示符中的BIND变量.

I am trying to pass multiple values about 3000 values, to a BIND variable in Oracle SQL PLUS command prompt like..

SELECT JOB
  FROM EMP 
 WHERE JOB IN :JOB -- bind variable value

我想查看我的结果，因为必须提取出EMP表中列JOB上与该变量列表匹配的所有值.

I want to see my result, as all the values in EMP table on column JOB matching to that variable list has to be fetched out.

由于它是生产环境，因此我只能在SELECT子句上具有授予权限才能创建表.

As its being production environment I can't create tables only I have grant on SELECT clause.

需要更多有关在UNIX-SQL PLUS环境中运行相同查询时如何准确执行该信息的信息.

Need more information on how exactly it get executed when I run the same query from UNIX-SQL PLUS environment.

会提示要求输入BIND变量值，还是可以引用具有以下值的文件? :JOB1:='经理' :JOB2:='秘书' :JOB3:='ACCOUNTANT'

Will it prompt asking to enter the BIND variables values or can I refer to a file which has values as... :JOB1 := 'MANAGER' :JOB2 := 'CLERK' :JOB3 := 'ACCOUNTANT'

推荐答案

Oracle绑定变量是一对一的关系，因此您需要为要包含在IN子句中的每个值定义一个:

Oracle bind variables are a one-to-one relationship, so you'd need one defined for each value you intend to include in the IN clause:

SELECT JOB
  FROM EMP 
 WHERE JOB IN (:JOB1, :JOB2, :JOB3, ..., :JOB3000)

您还需要注意，Oracle IN仅支持最多1,000个值，否则您将获得:

You need to also be aware that Oracle IN only supports a maximum of 1,000 values, or you'll get:

ORA-01795:列表中的最大表达式数为1000

ORA-01795: maximum number of expressions in a list is 1000

最好的选择是创建一个表(派生表，临时表，实际表或视图)，并将其联接以获取所需的值. IE浏览器:

The best alternative is to create a table (derived, temporary, actual, or view), and join to it to get the values you want. IE:

SELECT a.job
  FROM EMP a
  JOIN (SELECT :JOB1 AS col FROM DUAL
        UNION ALL
        SELECT :JOB2 FROM DUAL
        UNION ALL
        SELECT :JOB3 FROM DUAL
        UNION ALL 
        ...
        UNION ALL 
        SELECT :JOB3000 FROM DUAL) b ON b.col = a.job

这篇关于在Oracle BIND变量中声明多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！