Oracle: IN function within an IF block

Question

I have a cursor c2, with one column 'note', containing numbers. I want to check, whether the column contains 5 and change my local variable if so.

this:

CREATE OR REPLACE PROCEDURE proc

IS
result varchar(50);

cursor c2 is
SELECT  note
FROM    student;    

BEGIN

IF c2.note IN(5) THEN
result := 'contains 5';

DBMS_OUTPUT.PUT_LINE(result);

END;
/

doesnt work.

please help!

Define "doesn't work". You're certainly allowed to use an `IN` clause inside an `IF` statement so something else must be going on here. — Justin Cave, May 04 '13 at 20:35
Is `c2` the cursor name, or a record variable it's been fetched into? — Alex Poole, May 04 '13 at 20:37
@JustinCave unless I write this statement it compiles, seems like there is a syntax error there — IAM, May 04 '13 at 20:45
You can't get a value directly from the cursor, only from a record within it (which might be implicit, e.g. `for r2 in c2 loop`). you need to show more code. But I suspect you're wanting to know whether *any* record the cursor returns has a `5`; is that right? — Alex Poole, May 04 '13 at 20:48
If that is right, it sounds similar to [this question](http://stackoverflow.com/q/16063063/266304). Can you use the last option from my answer on that, with `case`? **Edit after your update** You have to loop over the cursor records; otherwise you don't need a cursor, you just need a `select into`. It really depends what else you intend to do... — Alex Poole, May 04 '13 at 20:53
@AlexPoole should work, last question-is "rec" the column name? so it's "for note in c2" in my case? — IAM, May 04 '13 at 20:56

score 3 · Answer 1 · answered May 04 '13 at 21:02

In your code, you're declaring a cursor but you are never opening it and never fetching data from it. You'd need, presumably, some sort of loop to iterate through the rows that the cursor returned. You'll either need to explicitly or implicitly declare a record into which each particular row is fetched. One option to do that is something like

CREATE OR REPLACE PROCEDURE proc
IS
  result varchar(50);

  cursor c2 is
    SELECT  note
      FROM  student;    
BEGIN
  FOR rec IN c2
  LOOP
    IF rec.note IN(5) THEN
      result := 'contains 5';
      DBMS_OUTPUT.PUT_LINE(result);
    END IF;

  END LOOP;
END;
/

Note that you also have to have an END IF that corresponds to your IF statement. Naming a cursor c2 is also generally a bad idea-- your variables really ought to be named meaningfully.

hol · Answer 2 · 2013-05-04T21:01:20.997

2

You are missing an END IF and need to LOOP over the cursor. The procedure name is included in the final END.

But using IN should work. Like this:

CREATE OR REPLACE PROCEDURE proc    
IS
  result varchar(50);

  cursor c2 is
  SELECT  note
  FROM    student;    

BEGIN
  FOR rec in c2
  LOOP 
    IF rec.note IN (5) THEN
      result := 'contains 5';
    END IF;   
  END LOOP;

  DBMS_OUTPUT.PUT_LINE(result);

END proc;
/

edited May 04 '13 at 21:01

answered May 04 '13 at 20:54

hol

8,255
5
33
59

Not straight from the *cursor* `c2`, it would need to loop and check each returned value...? – Alex Poole May 04 '13 at 20:57

score 2 · Accepted Answer · answered May 04 '13 at 21:00

2

You have too loop over the records/rows returned in the cursor; you can't refer to the cursor itself like that:

CREATE OR REPLACE PROCEDURE proc
IS
    result varchar(50);

    cursor c2 is
        SELECT  note
        FROM    student;    
BEGIN
    FOR r2 IN c2 LOOP
        IF r2.note = 5 THEN -- IN would also work but doesn't add anything
            result := 'contains 5';
        END IF;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE(result);
END;
/

But if you're just testing for the existence of any record with value 5 then you don't need a cursor:

CREATE OR REPLACE PROCEDURE proc
IS
    result varchar(50);
BEGIN
    SELECT max('contains 5')
    INTO result
    FROM student
    WHERE note = 5;

    DBMS_OUTPUT.PUT_LINE(result);
END;
/

If there are any rows with five you'll get the 'contains 5' string; if not you'll get null. The max() stops an exception being thrown if there are either zero or more than one matching records in the table.

answered May 04 '13 at 21:00

Alex Poole

183,384
11
179
318

1

`c2.note` should be `r2.note` – hol May 04 '13 at 21:02
@hol - yes, I'll go and give myself a good talking to. Thanks. – Alex Poole May 04 '13 at 21:05
No problem. Your second solution is quite interesting. Never thought of using `MAX` for something like this. – hol May 04 '13 at 21:07
@hol - it does rather assume that you don't intend to do anything else with the data from the cursor, and that a cursor just wasn't the right tool here in the first place. If you need other data *and* this flag the earlier question I linked to in a comment has a couple of approaches (though there are probably others). – Alex Poole May 04 '13 at 21:10

Oracle: IN function within an IF block

3 Answers3