Re: Please help on query question.
From: fredericks <free101_at_picusnet.com>
Date: Sun, 31 Jan 1999 03:50:20 -0500
Message-ID: <36b419b6.0_at_news.itribe.net>
Date: Sun, 31 Jan 1999 03:50:20 -0500
Message-ID: <36b419b6.0_at_news.itribe.net>
Ryan
As you have found out a (SELECT ...) = true is
not allowed in plsql.
One approach to using SQL queries in PL/SQL is to encapsulate
the SQL in a function.
The function returns the (boolean) result of a SQL query based
test. The boolean return value can be
tested directly in an IF construct.
Create functions for each query, and if your dba permits, put
them into the database,
where they can be potentially reused in other
forms.
(they can be tested first as
in forms functions).
Use a cursor to do the query, instead of an
select into; a cursor, open fetch is more efficient the a select
into.
Use parameters instead of hard coded
:host_variables in the function and cursor, which makes the function
reusable in other forms (and potentially in
other forms, database triggers etc.)
(Note: all selects in plsql (and proC etc.) require an into
clause. even a cursor fetch requires an into)
create or replace function find_name1
(in_name in names1.name%type,
in_age in names1.age) is
cursor find_names1_cur
(in_name in
names1.name%type,
in_age in
names1.age) is
select
x
from
names1
where name like
in_name
and age =
in_age;
find_names1_rec
find_names1_cur%rowtype;
begin
open
find_names1_cur (in_name = in_name,
in_age = in_age),
fetch find_names1_cur into
find_names_rec;
return find_names1_cur%FOUND;
end find_name1 ;
create a similar function for the names2
table.
then use the functions in a plsql if then else
structures:
if find_names1 (in_name => :MYBLOCK.NAME
in_age => :MYBLOCK.AGE )
then
then
-- check the NAMES1 checkbox
here.....
end if;
if find_names2 (in_name => :MYBLOCK.NAME
in_age => :MYBLOCK.AGE )
-- check the NAMES2 checkbox
here.....
end if;
Putting logic and Business rules into functions (and procedures and
packages)
will allow for more code reuse.
HTH
Mark
Received on Sun Jan 31 1999 - 09:50:20 CETRyan <"rrichards[NO SPAM]"_at_benham.com>I have a form that contains (for simplicity) 2 variables:> wrote in message <36B23F0D.16DD7DE3_at_benham.com>... NAME (varchar)
AGE (varchar).....
I need to do some kind of boolean check like:
if (SELECT * from NAMES1
WHERE :MYBLOCK.NAME like NAMES1.NAME
AND :MYBLOCK.AGE = NAMES1.AGE) = true
-- check the NAMES1 checkbox here.....if (SELECT * from NAMES2
WHERE :MYBLOCK.NAME like NAMES2.NAME
AND :MYBLOCK.AGE = NAMES2.AGE) = true
-- check the NAMES2 checkbox here.....Is there any way to do this kind fo thing in PLSQL or SQL? Everytime I try the SELECT * from table... I get a 'No INTO in clause'.