使用USING子句立即执行会给出错误 [英] EXECUTE IMMEDIATE with USING clause giving errors

问题描述

全部

总体上来说，我对存储过程非常陌生，但是我尤其在Oracle中挣扎.我已经创建了一个非常简单的示例，说明了我要完成的工作，但是使用此简化版本仍然遇到相同的错误.

I am very new to stored procedures in general but I am struggling especially with those in Oracle. I have created a very simple example of what I am trying to accomplish and I am still getting the same error with this simplified version.

示例存储过程如下:

CREATE OR REPLACE PROCEDURE ashish_test
AUTHID CURRENT_USER IS
BEGIN
     DECLARE
          v_tab     VARCHAR2(50);
          v_strSQL  VARCHAR2(50);
     BEGIN
          v_strSQL := 'SELECT * FROM :1';
          v_tab    := 'ex.emp';
          EXECUTE IMMEDIATE v_strSQL USING v_tab;
     END;
END;

当我使用CALL ashish_test()调用上述存储过程时，我得到了:

When I call the above stored procedure using CALL ashish_test(), I get :

错误消息http://web1.twitpic.com/img /12831839-06a3ea536df5d5a0a839eb83d9e59d25.4a3936b8-scaled.jpg

基于本文(请参见示例7-1)，USING关键字应将v_strSQL中的编号占位符(:1)替换为v_tab中存储的值.但是，我不断收到无效的表错误.我猜这是因为EXECUTE IMMEDIATE由于某种原因无法用值替换占位符，但是我不确定为什么.有人知道我在这里做蠢事吗?

Based on this article (Look for Example 7-1), USING keyword should replace the numbered placeholder (:1) within v_strSQL with the value stored in v_tab. However, I keep getting invalid table error. I am guessing it's because EXECUTE IMMEDIATE is unable to replace the placeholder with the value for some reason but I am not sure why that is. Does anyone know if I am doing something stupid here?

我正在Oracle 10g数据库&使用PL/SQL Developer.

I am running this on Oracle 10g database & using PL/SQL Developer.

推荐答案

USING子句仅用于绑定变量(即，您将在select语句中使用列名的位置)，而不是表名.典型用法如下:

The USING clause is only for bind variables (i.e. where you would use column names in a select statement), not table names. Typical usage would look like this:

Select col1 from table1 where col2 = :a

如果要使用变量表名称，请使用以下内容:

If you want to use variable table names use something like this:

         v_tab    := 'ex.emp';
         v_strSQL := 'SELECT * FROM ' || v_tab;
         EXECUTE IMMEDIATE v_strSQL;

这篇关于使用USING子句立即执行会给出错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！