在oracle中参数错误数无效

Please let me know whether there is anything wrong in the insert statement of the below query. It is giving me 'invalid number of arguments' errors

请让我知道以下查询的insert语句是否有问题。它给了我“无效的参数数目”的错误

    create or replace PROCEDURE adm_getMaxTableIdLimited
    (
    v_TableName IN VARCHAR2 DEFAULT NULL ,
    v_TableIDColumnName IN VARCHAR2 DEFAULT NULL ,
    v_MaxTableId OUT NUMBER
    )
    AS
    v_Limit NUMBER(10,0);
    v_SQLStatement VARCHAR2(255);

    BEGIN
   v_Limit:= 99999 ;
   v_MaxTableId:= 0 ;
    EXECUTE IMMEDIATE 'DROP TABLE  TempResult';
   EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE TempResult
   (
     Result NUMBER(10,0) 
   )';

    v_SQLStatement:= 'Insert INTO TempResult(Result) SELECT max(' || v_TableIDColumnName || ')    
    FROM ' || v_TableName || ' WHERE ' || v_TableIDColumnName || ' <= ' || CAST(v_limit AS 
    VARCHAR2) || ';';
     EXECUTE IMMEDIATE v_SQLStatement;
     EXECUTE IMMEDIATE 'SELECT Result INTO v_MaxTableId FROM TempResult' ;
     IF ( v_MaxTableId = v_Limit ) THEN
        v_MaxTableId := -1 ;
    ELSE
       v_MaxTableId := v_MaxTableId + 1 ;
    END IF;  
    EXECUTE IMMEDIATE ' TRUNCATE TABLE TempResult ';
   END;

2 个解决方案

#1

You don't need a temporary table at all. And your code can be considerably simplified:

你根本不需要一张临时表。你的代码可以被大大简化:

create or replace PROCEDURE adm_getMaxTableIdLimited
(
    v_TableName IN VARCHAR2 DEFAULT NULL,
    v_TableIDColumnName IN VARCHAR2 DEFAULT NULL,
    v_MaxTableId OUT NUMBER
)
AS
    v_Limit NUMBER;

BEGIN
    v_Limit:= 99999 ;

    EXECUTE IMMEDIATE
        'SELECT MAX(' || v_TableIDColumnName || ') FROM '
        || v_TableName || ' WHERE ' || v_TableIDColumnName || ' <= :limit'
    INTO v_MaxTableId
    USING v_Limit;

    IF v_MaxTableId = v_Limit THEN
        v_MaxTableId := -1;
    ELSE
       v_MaxTableId := v_MaxTableId + 1;
    END IF;  
END;

BTW: The current problem in your code is:

在你的代码中，当前的问题是:

EXECUTE IMMEDIATE 'SELECT Result INTO v_MaxTableId FROM TempResult';

The correct line would be:

正确的路线是:

EXECUTE IMMEDIATE 'SELECT Result FROM TempResult' INTO v_MaxTableId;

#2

First off, it seems very, very unlikely that you want to dynamically drop and create your temporary table. A global temporary table is just that-- global. The table definition is visible to other sessions unlike what you might have grown accustomed to in other databases. Just like permanent tables, you would create the temporary table once when you're installing the application. Dropping and recreating the table won't work correctly in a multi-user environment.

首先，似乎不太可能动态地删除并创建临时表。全局临时表就是这样——全局的。表定义对于其他会话是可见的，这与您在其他数据库中可能已经习惯的不同。就像永久表一样，在安装应用程序时，您将创建一个临时表。删除和重新创建表在多用户环境中不能正常工作。

Second, there does not appear to be any reason to use a temporary table here. You can just dynamically generate the SELECT statement and store the result in a local variable.

其次，这里似乎没有任何理由使用临时表。您可以动态地生成SELECT语句并将结果存储在一个局部变量中。

Third, your INSERT statement should not have a semicolon at the end. That's what is generating the ORA-00911: invalid character error.

第三，插入语句末尾不应该有分号。这就是生成ORA-00911:无效字符错误的原因。

Fourth, your dynamic SELECT statement should not have an INTO. That INTO clause needs to be part of the EXECUTE IMMEDIATE, not the SQL statement.

第四，您的动态选择语句不应该有一个INTO。INTO子句需要是EXECUTE IMMEDIATE的一部分，而不是SQL语句。

My guess, therefore, is that you want something like

因此，我的猜测是，你想要类似的东西

create or replace PROCEDURE adm_getMaxTableIdLimited
(
  v_TableName IN VARCHAR2 DEFAULT NULL ,
  v_TableIDColumnName IN VARCHAR2 DEFAULT NULL ,
  v_MaxTableId OUT NUMBER
)
AS
  v_Limit NUMBER(10,0) := 99999;
  v_SQLStatement VARCHAR2(255);
BEGIN
  v_SQLStatement := 
   'SELECT max(' || v_TableIDColumnName || ')  ' ||
   '  FROM ' || v_TableName || 
   ' WHERE ' || v_TableIDColumnName || ' <= :1';
 EXECUTE IMMEDIATE v_SQLStatement 
    INTO v_MaxTableId
   USING v_Limit;
 IF ( v_MaxTableId = v_Limit ) THEN
    v_MaxTableId := -1 ;
ELSE
   v_MaxTableId := v_MaxTableId + 1 ;
END IF;

END;

结束;

Why are you creating this procedure in the first place, though? It appears to be used to return the next value of a key to insert into the table. If that's what you're doing, you need to reconsider your approach. Generating dynamic SQL that scans the primary key index for the maximum value is much less efficient than using a sequence. It also doesn't work correctly outside of a toy system where you can guarantee that there is only one user. In a real system, multiple sessions will get the same value and try to insert rows with the same key. One of the two will block on the INSERT and eventually get a duplicate key error.

但是，为什么要首先创建这个过程呢?它似乎被用来返回键的下一个值，以插入到表中。如果你正在这样做，你需要重新考虑你的方法。生成动态SQL以扫描主键索引以获得最大值，这要比使用序列效率低得多。它在玩具系统之外也不能正常工作，你可以保证只有一个用户。在实际系统中，多个会话将获得相同的值，并尝试使用相同的键插入行。其中一个将阻塞INSERT，并最终获得一个重复的键错误。

#1