开发者

Check if a column has a certain entry in a PL/SQL function

I've a table with two columns. The second column is a foreign key column. I would want to check if there is a record with foreign key X in this column. I'm using (rather learning)开发者_如何转开发 PL/SQL on Oracle 11g.

Edit: I could use SQL SELECT with * but how do I use this in PL/SQL to return a boolean in a function?


FUNCTION my_func ()
RETURN BOOLEAN
IS
  l_contains_x NUMBER;
  l_contains_x_bool BOOLEAN := false;
BEGIN
  SELECT 1
  INTO l_contains_x
  FROM dual
  WHERE EXISTS (
    SELECT 1
    FROM table
    WHERE col = X
  );

  IF l_contains_x = 1 THEN 
    l_contains_x_bool := TRUE;
  END IF;

  RETURN l_contains_x_bool;

END;

Note that the key here is the INTO keyword which returns values into variables. These are positionally matched so you could select 4 columns into 4 variables (or * into a rowtype). This expects ONE value which is why I have moved the check into an exists, so your outer SELECT only selects one value.

Note that SQL has no concept of the BOOLEAN type so your function is only usable in PL/SQL context. If you want true / false coming back in SQL context you should use VARCHAR2 'true' and 'false'.


create or replace function hasFK(i_key in someTable.fk%type) return number as
  v_cnt pls_integer := 0;
begin
  select count(1) 
  into v_cnt
  from someTable
  where fk = i_key
  and rownum = 1;

  return v_cnt;
end;

This will give 1=true and 0=false.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜