Oracle BIND 变量中多个值的声明
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6632741/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Declaration of multiple values in Oracle BIND Variables
提问by dilipece2001
I am trying to pass multiple values about 3000 values, to a BIND variable in Oracle SQL PLUS command prompt like..
我试图将大约 3000 个值的多个值传递给 Oracle SQL PLUS 命令提示符中的 BIND 变量，例如..
SELECT JOB
FROM EMP
WHERE JOB IN :JOB -- bind variable value
I want to see my result, as all the values in EMPtable on column JOBmatching to that variable list has to be fetched out.
我想查看我的结果，因为必须取出与该变量列表匹配的EMP列上的表中的所有值JOB。
As its being production environment I can't create tables only I have grant on SELECT clause.
由于它是生产环境，我无法创建表，只有我对 SELECT 子句有授权。
Need more information on how exactly it get executed when I run the same query from UNIX-SQL PLUS environment.
当我从 UNIX-SQL PLUS 环境运行相同的查询时，需要更多关于它如何准确执行的信息。
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'
它会提示要求输入 BIND 变量值，或者我可以参考一个具有以下值的文件... :JOB1 := 'MANAGER' :JOB2 := 'CLERK' :JOB3 := 'ACCOUNTANT'
回答by OMG Ponies
Oracle bind variables are a one-to-one relationship, so you'd need one defined for each value you intend to include in the INclause:
Oracle 绑定变量是一对一的关系，因此您需要为要包含在IN子句中的每个值定义一个：
SELECT JOB
FROM EMP
WHERE JOB IN (:JOB1, :JOB2, :JOB3, ..., :JOB3000)
You need to also be aware that Oracle INonly supports a maximum of 1,000 values, or you'll get:
您还需要注意 OracleIN最多只支持 1,000 个值，否则您会得到：
ORA-01795: maximum number of expressions in a list is 1000
ORA-01795: 列表中的最大表达式数为 1000
The best alternative is to create a table (derived, temporary, actual, or view), and join to it to get the values you want. IE:
最好的替代方法是创建一个表（派生的、临时的、实际的或视图），并加入它以获得您想要的值。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
回答by Frank Bergstrom
Our team just ran into this issue and this query is very clean to pass multiple state values. Each value is separated by comma only. I can pass all 52 states if required:
我们的团队刚刚遇到了这个问题，这个查询非常干净，可以传递多个状态值。每个值仅由逗号分隔。如果需要，我可以通过所有 52 个州：
SELECT county_code,state_code FROM WMS__ASSET_COUNTY_STATE
WHERE STATE_CODE IN
(SELECT regexp_substr(:bindstateocde, '[^,]+', 1, LEVEL) token
FROM dual
CONNECT BY LEVEL <= length(:bindstateocde) - length(REPLACE(:bindstateocde, ',', '')) + 1) ;
回答by Adam Musch
One way to do it in 10g and up is with subquery factoring.
在 10g 及更高版本中执行此操作的一种方法是使用子查询分解。
Assume :JOBis a comma-separated list of values. The following would work:
假设:JOB是一个逗号分隔的值列表。以下将起作用：
with job_list as
(select trim(substr(job_list,
instr(job_list, ',', 1, level) + 1,
instr(job_list, ',', 1, level + 1)
- instr (job_list, ',', 1, level) - 1
)
) as job
from (select
-- this is so it parses right
','|| :JOB ||',' job_list
from dual)
connect by level <= length(:JOB)
- length (replace (:JOB, ',', '') ) + 1
)
select * from emp
where job in (select * from job_list);
It's a bit ugly to read, yes, but it works, and Oracle's clever enough to do the parsing of the list of values once, not once per row, which is what you end up with otherwise. What it does under the covers is build a temporary table of the parsed values, which it then can join to the base table.
读起来有点难看，是的，但它确实有效，而且 Oracle 足够聪明，可以对值列表进行一次解析，而不是每行解析一次，否则您最终会得到这样的结果。它在幕后所做的是构建一个解析值的临时表，然后它可以连接到基表。
(I didn't come up with this on my own - original credit goes to an asktom question.)
（我不是自己想出来的 - 最初的功劳归于一个提问的问题。）
:JOBis a bind variable which must be declared and populated before it can be used. The statements below demonstrate how to do that with SQL*Plus.
:JOB是一个绑定变量，必须在使用前声明和填充。下面的语句演示了如何使用 SQL*Plus 做到这一点。
SQL> variable JOB varchar2(4000);
SQL> exec :JOB := '10, 20';
回答by Witold Sosnowski
While facing similar problem, I came up with this dirty solution:
在面临类似问题时，我想出了这个肮脏的解决方案：
select * from my_table where ',param_1,param_2,param_3,param_4,' LIKE '%,'||my_column||',%'
回答by Markus Winand
Have a look at the Ugly-Delimited-String-Approach(tm).
看看Ugly-Delimited-String-Approach(tm)。
That is, bind a string and convert it to a list in SQL. Ugly, that is.
即绑定一个字符串，并在SQL中将其转换为一个列表。丑，就是。
回答by Luke Woodward
The first question I have to ask is this: where is this list of about 3000 values coming from? If it's coming from another table, then you can write something like the following:
我要问的第一个问题是：这个大约 3000 个值的列表来自哪里？如果它来自另一个表，那么您可以编写如下内容：
SELECT JOB
FROM EMP
WHERE JOB IN (SELECT something FROM some_other_table WHERE ... )
For the rest of this answer, I'll assume it's not in the database anywhere.
对于这个答案的其余部分，我假设它不在数据库中的任何地方。
In theory it's possible to do what you want. There are various ways to devise a query with a lot of bind variables in it. As an example, I'll write a script to query the all_objectsdata dictionary view using 3000 bind variables. I'm not going to write a SQL*Plus script with 3000 bind variables in it, so instead I wrote a Python script to generate this SQL*Plus script. Here it is:
理论上可以做你想做的。有多种方法可以设计包含大量绑定变量的查询。例如，我将编写一个脚本来all_objects使用 3000 个绑定变量查询数据字典视图。我不打算编写包含 3000 个绑定变量的 SQL*Plus 脚本，因此我编写了一个 Python 脚本来生成此 SQL*Plus 脚本。这里是：
ns = range(1, 9001, 3) # = 1, 4, 7, ..., 8998
# This gets rid of a lot of lines saying 'PL/SQL procedure successfully completed'.
print "SET FEEDBACK OFF;"
print
# Declare the bind variables and give them values.
for i, n in enumerate(ns):
print "VARIABLE X%04d NUMBER;" % i
print "EXEC :X%04d := %d;" % (i, n)
print
query = "SELECT object_name FROM all_objects WHERE"
# Break up the query into lines to avoid SQL*Plus' limit of 2500 characters per line.
chunk_size = 100
for i in range(0, len(ns), chunk_size):
query += "OR object_id IN (" + ",".join( ":X%04d" % j for j in range(i, i + chunk_size) ) + ")\n"
query = query.replace("WHEREOR", "WHERE") + ";\n"
print query
I was then able to run this script, redirect its output to a .sqlfile, and then run that .sqlfile in SQL*Plus.
然后我能够运行这个脚本，将它的输出重定向到一个.sql文件，然后.sql在 SQL*Plus 中运行该文件。
You may notice above that I wrote 'In theoryit's possible...'. I put the in theoryclause there for a good reason. The query appears to be valid, and I don't know of a reason why it shouldn't execute. However, when I ran it on my Oracle instance (XE 11g Beta), I got the following output:
您可能会注意到我在上面写了“理论上这是可能的......”。我把理论条款放在那里是有充分理由的。该查询似乎是有效的，我不知道它不应该执行的原因。但是，当我在我的 Oracle 实例（XE 11g Beta）上运行它时，我得到了以下输出：
SQL> @genquery.sql SELECT object_name FROM all_objects WHERE object_id IN (:X0000,:X0001,:X0002,:X0 003,:X0004,:X0005,:X0006,:X0007,:X0008,:X0009,:X0010,:X0011,:X0012,:X0013,:X0014 ,:X0015,:X0016,:X0017,:X0018,:X0019,:X0020,:X0021,:X0022,:X0023,:X0024,:X0025,:X 0026,:X0027,:X0028,:X0029,:X0030,:X0031,:X0032,:X0033,:X0034,:X0035,:X0036,:X003 7,:X0038,:X0039,:X0040,:X0041,:X0042,:X0043,:X0044,:X0045,:X0046,:X0047,:X0048,: X0049,:X0050,:X0051,:X0052,:X0053,:X0054,:X0055,:X0056,:X0057,:X0058,:X0059,:X00 60,:X0061,:X0062,:X0063,:X0064,:X0065,:X0066,:X0067,:X0068,:X0069,:X0070,:X0071, :X0072,:X0073,:X0074,:X0075,:X0076,:X0077,:X0078,:X0079,:X0080,:X0081,:X0082,:X0 083,:X0084,:X0085,:X0086,:X0087,:X0088,:X0089,:X0090,:X0091,:X0092,:X0093,:X0094 ,:X0095,:X0096,:X0097,:X0098,:X0099) * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 556 Session ID: 137 Serial number: 29
The ORA-03113error indicates that the server process crashed.
该ORA-03113错误表明服务器进程崩溃。
I tried several variations on this:
我对此尝试了几种变体：
- not using bind variables at all (i.e. putting the values in directly)
- not using
INlists, i.e. writingSELECT ... FROM all_objects WHERE object_id=:X0000 OR object_id=:X0001 OR ..., - using OMG Ponies' approach,
- using OMG Ponies' approach without using bind variables,
- copying the data out of
all_objectsinto a table, and querying that instead.
- 根本不使用绑定变量（即直接将值放入）
- 不使用
IN列表，即写作SELECT ... FROM all_objects WHERE object_id=:X0000 OR object_id=:X0001 OR ...， - 使用 OMG 小马的方法，
- 使用 OMG Ponies 的方法而不使用绑定变量，
- 将数据复制
all_objects到表中，然后进行查询。
All of the above approaches caused an ORA-03113error.
以上所有方法都导致了ORA-03113错误。
Of course, I don't know whether other editions of Oracle will suffer from these crashes (I don't have access to any other editions), but it doesn't bode well.
当然，我不知道其他版本的 Oracle 是否会遭受这些崩溃（我无法访问任何其他版本），但这并不是好兆头。
EDIT:You ask if you can achieve something like SELECT JOB FROM EMP WHERE JOB IN (:JOB). The short answer to that is no. SQL*Plus's usage message for the VARIABLEcommand is as follows:
编辑：您问是否可以实现类似SELECT JOB FROM EMP WHERE JOB IN (:JOB). 对此的简短回答是否定的。SQL*Plus 对VARIABLE命令的使用信息如下：
Usage: VAR[IABLE] [ [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]
All of the above types are single data values, with the exception of REFCURSOR, but SQL*Plus still seems to treat that as a single value. I can't find a way to query data returned in a REFCURSORthis way.
上述所有类型都是单个数据值，除了REFCURSOR，但 SQL*Plus 似乎仍将其视为单个值。我找不到查询以REFCURSOR这种方式返回的数据的方法。
So in summary, what you're attempting to achieve is almost certainly impossible. I don't know what your ultimate aim is here, but I don't think you'll be able to do it using a single query in SQL*Plus.
所以总而言之，您试图实现的目标几乎肯定是不可能的。我不知道您在这里的最终目标是什么，但我认为您无法在 SQL*Plus 中使用单个查询来实现。
