如何指定在Pro * C查询变量前pression列表？ [英] How to specify a variable expression list in a Pro*C query?

问题描述

我有一个PRO * C查询我试图优化问题。

I have a problem with a Pro*C query I'm trying to optimise.

要解释一下，我们的应用程序搜索的一个庞大的数据库行。这些行几种语言存在和老code中选一排阵列中的每一种语言。现在，作为这些查询都是消耗我们的应用程序的一部分，大部分时间，我想让只有一个查询直接写在数组中。

To explain, our application searches for rows in a huge database. These rows exist in several languages and the old code selected a row for each language in an array. Now as these queries are the most time consuming part of our app, I wanted to make only one query which writes directly in an array.

语言codeS 2个字母的ISO-639 codeS（en代表英语，FR（法语））。

The language codes are 2 letter ISO-639 codes (en for english, fr for french).

旧的方式（这只是一个简单的code显示意向）

Old way (this is only a simplified code to show the intention)

struct ROW arr[MAX_LAN];
struct ROW_IND arr_ind[MAX_LAN];
uint_t LanIdx;
for(LanIdx=0; LanIdx<MAX_LAN; LanIdx++) {
  EXEC SQL SELECT *  /* Don't look at the *, it's for obfuscation only */
      INTO :arr[LanIdx]:arr_ind[LanIdx]
      FROM table WHERE id=:uniqid AND language=:LanCode[LanIdx];
}

我愿做这样的事情：

I would like to do something like this:

EXEC SQL SELECT *  /* Don't look at the *, it's for obfuscation only */
    INTO :arr:arr_ind
    FROM table WHERE id=:uniqid AND language IN (:LanCodes);

但不知道我应该怎么定义兰codeS。

but do not know how I should define LanCodes.

它与一个常数（编译时间）列表这样的

It works with a constant (compile time) list like this

EXEC SQL SELECT *  /* Don't look at the *, it's for obfuscation only */
    INTO :arr:arr_ind
    FROM table WHERE id=:uniqid AND language IN ('en','fr','de');

不过这是没有用的，因为语言可能会有所不同情况而定。

but this is not useful, as the languages may vary from case to case.

如果我写的东西像

char LanCodes[MAX_LANS*5];
sprintf(LanCodes, "%s", LanCode[LanIdx]);

EXEC SQL SELECT *  /* Don't look at the *, it's for obfuscation only */
    INTO :arr:arr_ind
    FROM table WHERE id=:uniqid AND language IN (:LanCodes);

它只能如果有1语言code字符串中的

it works only if there is 1 language code in the string.

我的问题是，没有任何人知道如何使这项工作？ Oracle文档这么大，我不知道在哪里看。我尝试不同的方法，但没有奏效。

So my question is, does anybody know how to make this work? The Oracle documentation is so big, I don't know where to look at. I tried different ways, but none worked.

修改
好吧，我发现的解决方案。这不是高雅，这不是先进的，但它工作得很好。我把OR子句的名单在我的查询，并将其返回什么，我需要在我需要的形式。

EDIT Ok, I found a solution that works. It's not elegant, it's not advanced but it works well. I put a list of OR clauses in my query and it returns what I need in the form that I need.

EXEC SQL SELECT *  /* Don't look at the *, it's for obfuscation only */
    INTO :arr:arr_ind
    FROM table WHERE id=:uniqid AND (
                language=:v1[ 0] OR
                language=:v1[ 1] OR
                language=:v1[ 2] OR
                language=:v1[ 3] OR
                language=:v1[ 4] OR
                language=:v1[ 5] OR
                language=:v1[ 6] OR
                language=:v1[ 7] OR
                language=:v1[ 8] OR
                language=:v1[ 9] OR
                language=:v1[10] OR
                language=:v1[11] OR
                language=:v1[12] OR
                language=:v1[13] OR
                language=:v1[14] OR
                language=:v1[15] OR
                language=:v1[16] OR
                language=:v1[17] OR
                language=:v1[18] OR
                language=:v1[19] OR
                language=:v1[20] OR
                language=:v1[21] OR
                language=:v1[22] OR
                language=:v1[23] OR
                language=:v1[24] OR
                language=:v1[25] OR
                language=:v1[26] OR
                language=:v1[27] OR
                language=:v1[28] OR
                language=:v1[29] OR
                language=:v1[30]);

它的速度更快时，有两种以上的语言，所以我称这种变体或旧取决于语言获取号码。

It's faster when there is more than 2 languages, so I call this variant or the old one depending on number of languages to fetch.

推荐答案

这大概<一个href=\"http://asktom.oracle.com/pls/asktom/f?p=100:11%3A0%3A%3A%3A%3AP11%5FQUESTION%5FID:146012348066\"相对=nofollow> AskTom文章，可以帮助你。

这篇关于如何指定在Pro * C查询变量前pression列表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！