IN运算符，Oracle pl/sql中的100个字符串 [英] 100 strings in IN operator, oracle pl/sql

问题描述

我在IN运算符中将100个table_names作为字符串传递，但是由于操作数过多，我遇到了数字溢出错误. 有什么方法可以使用IN以外的其他功能?

I am passing 100 table_names in the IN operator as strings, but I am getting numeric overflow error due to too many operands. Is there a way where I can use something else besides IN ?

set serveroutput on

 DECLARE
  ...
 BEGIN
   FOR r IN 
   (
     SELECT table_name, column_name
     FROM all_tab_columns 
     WHERE table_name IN (...100 strings)
    )
AND data_type = 'NUMBER'
     ORDER BY table_name, column_id
   )
   LOOP
    execute immediate 'SELECT COUNT("' || r.column_name || '")
                             ,COUNT(nvl2("' || r.column_name || '", NULL, 1))                        
                       FROM "' || r.table_name || '"'
    INTO not_null_count, null_count;

    DBMS_OUTPUT.PUT_LINE(..)

注意:对于变量，我使用的是PLS_Integer.

Note: For variables I am using PLS_Integer.

推荐答案

对于ORA-01426，建议采取的措施是减少操作数".这并不意味着减少操作数的数量.这意味着您试图将太大的数字放入变量中.因此，缩小数字或扩大变量.

The suggested action for ORA-01426 is "reduce the operands". This doesn't mean reduce the number of operands. It means you're trying to put too large a number into a variable. So shrink the number, or enlarge the variable.

您说:

对于我正在使用PLS_Integer的变量"

"for variables I am using PLS_Integer"

因此，如果您有一个大表，并且我的意思是说超过2,147,483,647行，那么您将得到一个数值溢出.因为PLS_INTEGER是 32位数据类型.

So, if you have a large table, and by large I mean more than 2,147,483,647 rows, you will get a numeric overflow. Because PLS_INTEGER is a 32-bit data type.

如果这是您的情况，那么您需要声明数据类型为INTEGER(或NUMBER(38,0))的变量.

If this is your scenario then you need to declare your variables of data type INTEGER instead (or NUMBER(38,0)).

正如@BobJarvis指出的那样，PLS_INTEGER已针对硬件算法进行了优化.因此，一般建议是将其用于计数类型操作.但是，您的情况只是需要一个变量来保存SQL count()操作的输出，因此我认为效率不会有任何差异.

As @BobJarvis points out, PLS_INTEGER is optimized for hardware arithmetic. So the general advice would be to use it for counting type operations. However, your case simply requires a variable to hold the output of a SQL count() operation, so I don't think there will be any difference in efficiency.

这篇关于IN运算符，Oracle pl/sql中的100个字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！