• <legend id='4g7X0'><style id='4g7X0'><dir id='4g7X0'><q id='4g7X0'></q></dir></style></legend>

    1. <tfoot id='4g7X0'></tfoot>
        <bdo id='4g7X0'></bdo><ul id='4g7X0'></ul>

      <small id='4g7X0'></small><noframes id='4g7X0'>

      1. <i id='4g7X0'><tr id='4g7X0'><dt id='4g7X0'><q id='4g7X0'><span id='4g7X0'><b id='4g7X0'><form id='4g7X0'><ins id='4g7X0'></ins><ul id='4g7X0'></ul><sub id='4g7X0'></sub></form><legend id='4g7X0'></legend><bdo id='4g7X0'><pre id='4g7X0'><center id='4g7X0'></center></pre></bdo></b><th id='4g7X0'></th></span></q></dt></tr></i><div id='4g7X0'><tfoot id='4g7X0'></tfoot><dl id='4g7X0'><fieldset id='4g7X0'></fieldset></dl></div>

        在 IN 子句中为 NUMBER 列使用逗号分隔值

        using comma separated values inside IN clause for NUMBER column(在 IN 子句中为 NUMBER 列使用逗号分隔值)
          <bdo id='OGUrs'></bdo><ul id='OGUrs'></ul>

          <i id='OGUrs'><tr id='OGUrs'><dt id='OGUrs'><q id='OGUrs'><span id='OGUrs'><b id='OGUrs'><form id='OGUrs'><ins id='OGUrs'></ins><ul id='OGUrs'></ul><sub id='OGUrs'></sub></form><legend id='OGUrs'></legend><bdo id='OGUrs'><pre id='OGUrs'><center id='OGUrs'></center></pre></bdo></b><th id='OGUrs'></th></span></q></dt></tr></i><div id='OGUrs'><tfoot id='OGUrs'></tfoot><dl id='OGUrs'><fieldset id='OGUrs'></fieldset></dl></div>

                  <tbody id='OGUrs'></tbody>
                  <legend id='OGUrs'><style id='OGUrs'><dir id='OGUrs'><q id='OGUrs'></q></dir></style></legend>
                  <tfoot id='OGUrs'></tfoot>

                  <small id='OGUrs'></small><noframes id='OGUrs'>

                  本文介绍了在 IN 子句中为 NUMBER 列使用逗号分隔值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  我在一个包中有 2 个程序.我正在调用一个程序来获取用户 ID 的逗号分隔列表.

                  I have 2 procedures inside a package. I am calling one procedure to get a comma separated list of user ids.

                  我将结果存储在 VARCHAR 变量中.现在,当我使用此逗号分隔列表放入 IN 子句中时,它会抛出ORA-01722:INVALID NUMBER" 异常.

                  I am storing the result in a VARCHAR variable. Now when I am using this comma separated list to put inside an IN clause in it is throwing "ORA-01722:INVALID NUMBER" exception.

                  这就是我的变量的样子

                  l_userIds VARCHAR2(4000) := null;
                  

                  这是我赋值的地方

                  l_userIds := getUserIds(deptId);  -- this returns a comma separated list
                  

                  我的第二个查询就像 -

                  And my second query is like -

                  select * from users_Table where user_id in (l_userIds);
                  

                  如果我运行这个查询,我会收到 INVALID NUMBER 错误.

                  If I run this query I get INVALID NUMBER error.

                  有人可以帮忙吗.

                  推荐答案

                  你真的需要返回一个逗号分隔的列表吗?声明一个集合类型通常会好得多

                  Do you really need to return a comma-separated list? It would generally be much better to declare a collection type

                  CREATE TYPE num_table
                      AS TABLE OF NUMBER;
                  

                  声明一个返回此集合实例的函数

                  Declare a function that returns an instance of this collection

                  CREATE OR REPLACE FUNCTION get_nums
                    RETURN num_table
                  IS
                    l_nums num_table := num_table();
                  BEGIN
                    for i in 1 .. 10
                    loop
                      l_nums.extend;
                      l_nums(i) := i*2;
                    end loop;
                  END;
                  

                  然后在您的查询中使用该集合

                  and then use that collection in your query

                  SELECT *
                    FROM users_table
                   WHERE user_id IN (SELECT * FROM TABLE( l_nums ));
                  

                  也可以使用动态 SQL(@Sebas 演示了这一点).然而,这样做的缺点是每次调用该过程都会生成一个新的 SQL 语句,在执行之前需要再次解析该语句.它还会给库缓存带来压力,这会导致 Oracle 清除许多其他可重用的 SQL 语句,从而导致许多其他性能问题.

                  It is possible to use dynamic SQL as well (which @Sebas demonstrates). The downside to that, however, is that every call to the procedure will generate a new SQL statement that needs to be parsed again before it is executed. It also puts pressure on the library cache which can cause Oracle to purge lots of other reusable SQL statements which can create lots of other performance problems.

                  这篇关于在 IN 子句中为 NUMBER 列使用逗号分隔值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                  相关文档推荐

                  How to redirect the output of DBMS_OUTPUT.PUT_LINE to a file?(如何将 DBMS_OUTPUT.PUT_LINE 的输出重定向到文件?)
                  How do I get column datatype in Oracle with PL-SQL with low privileges?(如何使用低权限的 PL-SQL 在 Oracle 中获取列数据类型?)
                  Get a list of all functions and procedures in an Oracle database(获取 Oracle 数据库中所有函数和过程的列表)
                  Why cannot I create triggers on objects owned by SYS?(为什么我不能在 SYS 拥有的对象上创建触发器?)
                  Returning result even for elements in IN list that don#39;t exist in table(即使对于表中不存在的 IN 列表中的元素也返回结果)
                  Reset Sequence in oracle 11g(oracle 11g 中的重置序列)

                  <small id='FUFGS'></small><noframes id='FUFGS'>

                    <tfoot id='FUFGS'></tfoot>

                            <tbody id='FUFGS'></tbody>
                          <legend id='FUFGS'><style id='FUFGS'><dir id='FUFGS'><q id='FUFGS'></q></dir></style></legend>

                        1. <i id='FUFGS'><tr id='FUFGS'><dt id='FUFGS'><q id='FUFGS'><span id='FUFGS'><b id='FUFGS'><form id='FUFGS'><ins id='FUFGS'></ins><ul id='FUFGS'></ul><sub id='FUFGS'></sub></form><legend id='FUFGS'></legend><bdo id='FUFGS'><pre id='FUFGS'><center id='FUFGS'></center></pre></bdo></b><th id='FUFGS'></th></span></q></dt></tr></i><div id='FUFGS'><tfoot id='FUFGS'></tfoot><dl id='FUFGS'><fieldset id='FUFGS'></fieldset></dl></div>
                          • <bdo id='FUFGS'></bdo><ul id='FUFGS'></ul>