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.
精彩评论