Oracle Function With - IF EXISTS -

Question

How I do if exists in Oracle? In SQL Server the following works.

create or replace FUNCTION FExemplo(p_processoId INT) RETURN varchar2
AS
  v_Result varchar2(255);
  v_TemIsso INT;
BEGIN
  v_TemIsso := 0;
  IF EXISTS (SELECT EXEMPLO.EXEMPLOID FROM EXEMPLO WHERE EXEMPLO.EXEMPLOID = p_processoId)
  THEN
    v_TemIsso := 1;
  END IF;
  RETURN '';
END FExemplo;

Unfortunately you cannot simply copy code from _SQL Server_ to _Oracle_. You need to learn the _Oracle_ way of doing things. If you haven't already done so, I suggest reading the _PL/SQL Language Reference_ which is part of the _Oracle_ database documentation. — Abra, Nov 29 '19 at 19:46

score 0 · Answer 1 · edited Nov 29 '19 at 19:58

0

The IF EXISTS syntax is not allowed in PL/SQL. You could rewrite your code so it uses EXISTS within a query instead, like so:

BEGIN
SELECT 
    CASE WHEN EXISTS (
        SELECT 1
        FROM EXEMPLO 
        WHERE EXEMPLO.EXEMPLOID = p_processoId
    ) 
    THEN 1 ELSE 0 END
    INTO v_TemIsso
FROM DUAL;
-- rest of your code follows
END

edited Nov 29 '19 at 19:58

Abra

19,142
7
29
41

answered Nov 29 '19 at 19:48

GMB

216,147
25
84
135

score 0 · Accepted Answer · edited Nov 29 '19 at 19:56

You can use a variable for counting elements like this:

SELECT count(EXEMPLO.EXEMPLOID) into v_count FROM EXEMPLO WHERE EXEMPLO.EXEMPLOID = 
p_processoId

 IF v_count > 0 THEN 
    --do something
 ELSE 
    --do something
 END IF;

Of course, you have to define the variable v_count as a number before you use it.

If you do not use the v_count variable you can get a exception like NO_DATA_FOUND.

Oracle Function With - IF EXISTS -

2 Answers2