CODEWITHSUNDEEP
oracle-databaseselectboolean
BnJ
BnJ

Reputation: 1044

Set boolean to true if select returns something

How can I set a boolean to true if a select query returns something ?

Like that :

declare
    isExist boolean;

begin
    select count(1) into isExist from MYTABLE where ID = 1;
end;

Upvotes: 0

Views: 130

Answers (2)

neshkeev
neshkeev

Reputation: 6476

There is a lot of ways to do it:

DECLARE
  l_temp NUMBER(1);
  l_exists boolean;
BEGIN
  SELECT count(*)
    INTO l_temp
    FROM dual
   WHERE EXISTS (SELECT NULL FROM YOUR_TABLE WHERE id = 1)
  ;
  -- l_temp is 1 if there are something in your table and 0 if there is nothing in your table.
  l_exists := l_temp = 1;
END;

Also you can try to use cursor:

DECLARE
  l_cursor SYS_REFCURSOR;
  l_temp YOUR_TABLE.id%TYPE;
  l_exists boolean;
BEGIN
  open l_cursor for SELECT id FROM YOUR_TABLE WHERE id = 1;
  fetch l_cursor into l_temp;
  l_exists := l_cursor%FOUND;
  close l_cursor;
END;

Upvotes: 2

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

Oracle's SQL (still) doesn't support the boolean datatype, only PL/SQL does that - but a SQL statement embedded in PL/SQL has to conform to the rules of Oracle's SQL; this sometimes causes a bit of confusion.

Of course there are many workarounds possible, for example:

declare
    n number;
    isExist boolean;    
begin
    select count(1) into n from MYTABLE where ID = 1;

    isExist := (n>0);
end;

Upvotes: 2

Related Questions