sql - 立即执行 Oracle

Question

我正在尝试执行 EXECUTE IMMEDIATE 语句。但我收到以下错误。我正在尝试这个新的，我阅读了现有的帖子。

在看到以下示例后，我正在尝试这样做。 http://docs.oracle.com/cd/B13789_01/appdev.101/b10807/13_elems017.htm

它说无效的表名，但该表存在

Connecting to the database Local
ORA-00903: invalid table name
ORA-06512: at "MMM.Maxtable", line 26
ORA-06512: at line 9
CNTRYCNTRYID
SELECT MAX(:1) FROM  :2  WHERE :1  <= 99999
Process exited.
Disconnecting from the database oraclesrv.local.

我将 CNTRY 作为表名传递，将 CNTRYID 作为列名传递

Create OR REPLACE PROCEDURE Maxtable
    (ITableName          VarChar2,
    IColumnName  VarChar2 )
AS
 Limit1 int;
 RESULT1 INT;
 Query1  varChar(255);
 TableName          VarChar(50);
    ColumnName VarChar(50);
BEGIN
Limit1 := 99999;
MaxTableId := 0;
Result1 := 0;

TableName := ltrim(rtrim(ITableName));
ColumnName := ltrim(rtrim(iColumnName));
DBMS_OUTPUT.PUT_LINE(TableName || ColumnName );

IF (TableName is not null and  ColumnName is not null) then
Query1 := 'SELECT MAX(:1) FROM  :2  WHERE :1  <= 99999' ;
DBMS_OUTPUT.PUT_LINE(Query1);

EXECUTE IMMEDIATE Query1 INTO Result1 USING ColumnName, TableName;                               
END IF;

DBMS_OUTPUT.PUT_LINE(Result1);

 MaxTableId := Result1;

 IF (MaxTableId = Limit1) THEN
    MaxTableId := -1;
 ELSE
    MaxTableId := MaxTableId + 1  ;
 END IF;

END adm_getMaxTableIdLimited;

我也试过这个选项，它不起作用..

连接到数据库。ORA-00905: 缺少关键字ORA-06512: 在“mmm.Maxtable”，第 19 行ORA-06512: 在第 9 行从 CNTRY 中选择 max(CNTRYID) INTO Result1，其中 CNTRYID <= 99999进程退出。正在断开与数据库 oraclesrv.local 的连接。

Create OR REPLACE PROCEDURE Maxtable
    (TableName          VarChar2,
    ColumnName  VarChar2,
    MaxTableId OUT  Int )
AS
 Limit1 int;
 RESULT1 INT;
 Query1  varChar(255);
BEGIN
Limit1 := 99999;
MaxTableId := 0;
Result1 := 0;

IF (TableName is not null and  ColumnName is not null) then
Query1 := 'SELECT max( ' || ColumnName || ')  INTO  Result1'  || ' FROM ' || TableName || ' WHERE ' || ColumnName || ' <= ' || 99999 ;

DBMS_OUTPUT.PUT_LINE(Query1);

EXECUTE IMMEDIATE Query1;
END IF;

DBMS_OUTPUT.PUT_LINE(Result1);

 MaxTableId := Result1;

 IF (MaxTableId = Limit1) THEN
    MaxTableId := -1;
 ELSE
    MaxTableId := MaxTableId + 1  ;
 END IF;

END Maxtable;

Answer 1

您添加的链接内容如下:

You cannot use bind arguments to pass the names of schema objects to a dynamic SQL statement.

很遗憾，您不能为表名和列名使用绑定(bind)变量。您必须将它们连接起来:

Query1 := 'SELECT MAX(' || ColumnName || ') FROM  ' || TableName || '  WHERE ' || ColumnName || ' <= 99999' ;

并按以下方式使用 EXECUTE IMMEDIATE:

EXECUTE IMMEDIATE Query1 INTO Result1;