当前位置： 文江博客话题详情

Oracle BIND 变量中的多个值的声明

发布于 2024-11-19 11:23:19 字数 429 浏览 6 评论 0原文

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

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

我想查看我的结果，如 EMP 表中 列上的所有值必须取出与该变量列表匹配的作业。

由于它是生产环境，我无法创建表，只能在 SELECT 子句上授予权限。

需要更多有关当我从 UNIX-SQL PLUS 环境运行相同查询时它是如何执行的信息。

它会提示要求输入 BIND 变量值还是我可以引用一个具有以下值的文件... :JOB1 := '经理' :JOB2 := '职员' :JOB3 := '会计师'

原文

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

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.

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

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

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'

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（6）

风情万种。 2024-11-26 11:23:19

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

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

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

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

最好的替代方法是创建一个表（派生表、临时表、实际表或视图），然后连接到该表以获取所需的值。 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 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)

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

ORA-01795: maximum number of expressions in a list is 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:

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
回复 原文
三生殊途 2024-11-26 11:23:19

我们的团队刚刚遇到了这个问题，这个查询非常干净，可以传递多个状态值。每个值仅用逗号分隔。如果需要的话我可以通过所有 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) ;

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:

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) ;
回复 原文
拒绝两难 2024-11-26 11:23:19

看看 丑陋的分隔字符串方法（tm）。

也就是绑定一个字符串，然后在SQL中将其转换为列表。丑陋，就是这样。

Have a look at the Ugly-Delimited-String-Approach(tm).

That is, bind a string and convert it to a list in SQL. Ugly, that is.

回复 原文
绅士风度i 2024-11-26 11:23:19

在 10g 及更高版本中实现此目的的一种方法是使用子查询分解。

假设 :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);

是的，读起来有点难看，但它确实有效，而且 Oracle 足够聪明，可以对值列表进行一次解析，而不是每行解析一次，否则就会得到这样的结果。它在幕后的作用是构建一个解析值的临时表，然后可以将其连接到基表。

（我自己没有想出这个 - 最初的功劳归于一个 Asktom 问题。）

:JOB 是一个绑定变量，必须在使用它之前对其进行声明和填充。下面的语句演示了如何使用 SQL*Plus 来做到这一点。

SQL> variable JOB varchar2(4000);

SQL> exec :JOB := '10, 20';

One way to do it in 10g and up is with subquery factoring.

Assume :JOB is a comma-separated list of values. The following would work:

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.

(I didn't come up with this on my own - original credit goes to an asktom question.)

:JOB is 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.

SQL> variable JOB varchar2(4000);

SQL> exec :JOB := '10, 20';
回复 原文
没有心的人 2024-11-26 11:23:19

我要问的第一个问题是：这个大约 3000 个值的列表来自哪里？如果它来自另一个表，那么您可以编写如下内容：

SELECT JOB
  FROM EMP
 WHERE JOB IN (SELECT something FROM some_other_table WHERE ... )

对于此答案的其余部分，我假设它不在数据库中的任何位置。

理论上可以做你想做的事。有多种方法可以设计包含大量绑定变量的查询。例如，我将编写一个脚本来使用 3000 个绑定变量查询 all_objects 数据字典视图。我不会编写包含 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

然后我能够运行此脚本，将其输出重定向到 .sql 文件，然后在 SQL*Plus 中运行该 .sql 文件。

您可能会注意到上面我写了“理论上这是可能的......”。我将理论上子句放在那里是有充分理由的。该查询似乎有效，但我不知道它不应该执行的原因。但是，当我在 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

ORA-03113 错误表明服务器进程崩溃了。

我尝试了几种变体：

  • 根本不使用绑定变量（即直接将值放入）
  • 不使用 IN 列表，即编写 SELECT ... FROM all_objects WHERE object_id=:X0000 OR object_id=:X0001 OR ...，
  • 使用 OMG Ponies 的方法，
  • 使用 OMG Ponies 的方法而不使用绑定变量，
  • 将数据从 all_objects 复制到表，然后查询该表。

上述所有方法都会导致 ORA-03113 错误。

当然，我不知道其他版本的 Oracle 是否会遭受这些崩溃的困扰（我无法访问任何其他版本），但这不是一个好兆头。

编辑：您询问是否可以实现类似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 ] ]

除了 REFCURSOR 之外，上述所有类型都是单个数据值，但 SQL*Plus 似乎仍然将其视为单个值。我找不到以这种方式查询 REFCURSOR 返回的数据的方法。

总而言之，您试图实现的目标几乎肯定是不可能的。我不知道您的最终目标是什么，但我认为您无法使用 SQL*Plus 中的单个查询来完成此任务。

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:

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_objects data 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:

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 .sql file, and then run that .sql file in SQL*Plus.

You may notice above that I wrote 'In theory it's possible...'. I put the in theory clause 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:

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-03113 error indicates that the server process crashed.

I tried several variations on this:

  • not using bind variables at all (i.e. putting the values in directly)
  • not using IN lists, i.e. writing SELECT ... 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_objects into a table, and querying that instead.

All of the above approaches caused an ORA-03113 error.

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.

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 VARIABLE command is as follows:

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 REFCURSOR this way.

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.

回复 原文
人事已非 2024-11-26 11:23:19

在面临类似的问题时，我想出了这个肮脏的解决方案：

select * from my_table where ',param_1,param_2,param_3,param_4,' LIKE '%,'||my_column||',%'

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||',%'
回复 原文
~没有更多了~

关于作者

洒一地阳光

暂无简介

0 文章
0 评论
25 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

lixs

文章 0 评论 0

敷衍　

文章 0 评论 0

盗梦空间

文章 0 评论 0

tian

文章 0 评论 0

13375331123

文章 0 评论 0

你对谁都笑

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文