我们可以在SELECT查询中使用主变量作为表名吗?

I'm experimenting with Pro*C code.

我正在试验Pro*C代码。

I have 3 tables emp, mgr, all; All 3 tables contain emp_id and emp_name. I tried the below code it is giving me error. Please let me know if it is possible?

我有三个表emp, mgr，全部;所有3个表都包含emp_id和emp_name。我尝试了下面的代码它给我错误。如果可能的话，请告诉我。

const char table_name[3]={'emp','mgr','all'}
int counter = 0;
while(counter < 3)
{
 . . .
 EXEC SQL SELECT emp_name INTO :ename 
      From :table_name[counter++] 
      where emp_id=:emp_id;
}

Can we use variables for SELECT and FROM ?

我们可以使用变量来选择和从中选择吗?

2 个解决方案

#1

This is called Dynamic SQL:

这被称为动态SQL:

Use this info:

使用这个信息:

Dynamic SQL

动态SQL

While embedded SQL is fine for fixed applications, sometimes it is important for a program to dynamically create entire SQL statements.
With dynamic SQL, a statement stored in a string variable can be issued.
PREPARE turns a character string into a SQL statement, and EXECUTE executes that statement. Consider the following example.

虽然嵌入式SQL对于固定的应用程序来说很好，但是有时候动态创建整个SQL语句对程序来说很重要。使用动态SQL，可以发出存储在字符串变量中的语句。PREPARE将字符串转换为SQL语句，并执行该语句。考虑下面的例子。
   char *s = "INSERT INTO emp VALUES(1234, 'jon', 3)";
   EXEC SQL PREPARE q FROM :s;
   EXEC SQL EXECUTE q;
Alternatively, PREPARE and EXECUTE may be combined into one statement:

另外，准备和执行可以合并为一个语句:
   char *s = "INSERT INTO emp VALUES(1234, 'jon', 3)";
   EXEC SQL EXECUTE IMMEDIATE :s;

Source: http://infolab.stanford.edu/~ullman/fcdb/oracle/or-proc.html

来源:http://infolab.stanford.edu/ ~ ullman / fcdb / oracle / or-proc.html

#2

No, we can't use a host-variable to supply a table-name to a static SQL statement in Pro*C.

不，我们不能使用主变量来为Pro*C中的静态SQL语句提供表名。

To quote Oracle Pro*C Programmer's Guide for Oracle 11.2g:

引用Oracle Pro*C程序员指南11.2g:

You cannot use input host variables to supply SQL keywords or the names of database objects. [..] If you need to change database object names at runtime, use dynamic SQL. See also Chapter 13, "Oracle Dynamic SQL".

不能使用输入主机变量来提供SQL关键字或数据库对象的名称。(. .如果需要在运行时更改数据库对象名称，请使用动态SQL。参见第13章“Oracle Dynamic SQL”。

In Pro*C Oracle provides different methods to execute SQL statements. The main distinction is between static and dynamic methods. And for dynamic there are several sub-methods that allow different degrees of freedom.

在Pro*C中，Oracle提供了执行SQL语句的不同方法。主要区别是静态方法和动态方法。对于动态，有几个子方法允许不同的*度。

A static SQL statement is not 100% static - you can use input/output host-variables in where clause expressions (as operands), to supply values in insert statements, as select targets, etc. But not to specify table names. This is 'too dynamic' for static embedded SQL.

静态SQL语句不是100%静态的——您可以在where子句表达式(作为操作数)中使用输入/输出宿主变量，以在insert语句中提供值，如select target等。这对于静态嵌入式SQL来说“太动态了”。

Note that you can still use host-variables in your dynamically prepared SQL statements. This is recommended to avoid SQL injection issues and increase performance (when a statement is executed several times).

注意，您仍然可以在动态准备的SQL语句中使用主变量。建议这样做以避免SQL注入问题并提高性能(当语句执行多次时)。

Example (uses Oracle Dynamic SQL method 3):

示例(使用Oracle Dynamic SQL method 3):

const char *table_name[3] = {"emp", "mgr", "all"};
unsigned table_name_size = 0;
unsigned i = 0;
for (i = 0; i<table_name_size; ++i) {
    char stmt[128] = {0};
    snprintf(stmt, 128,
        "SELECT emp_name "
        "  FROM %s " 
        "  WHERE "
        "    emp_id = :emp_id",
      table_name[i]);

    EXEC SQL PREPARE emp_stmt FROM :stmt;
    // check sqlca.sqlcode ...
    EXEC SQL DECLARE emp_cursor CURSOR FOR emp_stmt;
    EXEC SQL OPEN emp_cursor USING :emp_id;
    // check sqlca.sqlcode ...
    EXEC SQL FETCH emp_cursor INTO :ename;
    // check sqlca.sqlcode ...
    EXEC SQL CLOSE emp_cursor;
    // check sqlca.sqlcode ...

    // ...
}

#1