存在于满足条件的游标中 [英] Exists in cursor where condition is met

问题描述

我创建了一个游标，返回一些数据。然后我希望在返回的数据中有一个if语句，它检查字段'test_field'是否存在于test_field2 ='A'的光标中。我努力得到我的语法正确的if语句虽然，并且不能在线找到任何指针。

I have created a cursor, that returns some data. I then wish to have an if statement in the returned data that checks if the field 'test_field' exists in the cursor where test_field2='A'. I am struggling to get my syntax correct on the if statement though, and can't find any pointers online.

CURSOR test_cur IS
      SELECT *
      FROM  test
      where name=pk_name;

BEGIN

FOR trec IN test_cur LOOP
    --The following line where my syntax is incorrect
    IF trec.test_field EXISTS IN test_cur WHERE trec.test_field2 = 'A' THEN
        {logic code to go here}
    END IF;
...

示例数据：

name    | test_field    | test_field2
--------------------------------------
x       |  101          | ROL
x       |  101          | A
x       |  102          | ROL
x       |  103          | ROL

在游标的第一个循环中，我们有以下内容：

On the first loop of the cursor we have the following:

trec.name=x
trec.field=101
trec.field2=ROL 

我想要它做的是，意识到有另一行（y）其中test_field = 101和test_field2 ='A'到if语句。

What I want it to do though, is realise that there is another row (y) where where test_field=101 and test_field2='A' and therefor go into the if statement.

推荐答案

我宁愿建议检查这个条件在查询你使用的光标， ：

I'd rather suggest checking this condition within the query you use for he cursor, smth like this:

CURSOR test_cur IS
    SELECT TEST.*,
           CASE
              WHEN EXISTS
                      (SELECT 1
                         FROM TEST T
                        WHERE     T.TEST_FIELD = TEST.TEST_FIELD
                              AND T.TEST_FIELD2 = 'A')
              THEN
                 1
              ELSE
                 0
           END
              HAS_A
      FROM TEST
          WHERE NAME=PK_NAME;

BEGIN

FOR trec IN test_cur LOOP
    --The following line where my syntax is incorrect
    IF trec.has_a = 1 THEN
        {logic code to go here}
    END IF;
...

这篇关于存在于满足条件的游标中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！