| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL Cursor : Is this the best way?
A copy of this was sent to Altitude <rawatd_at_logica.com>
(if that email address didn't require changing)
On Wed, 16 Jun 1999 11:05:55 +0100, you wrote:
>Hi,
>
>I'm writing an integrity checker for our system at work which basically
>scans the database tables for any discrepant pieces of data that should
>not be there. Anyway, there's one rule which is as follows :
>
>(I've changed the names of tables and columns)
>
>For all names in table_a whose status is 'P', a record should exist in
>table_b. I want a list of all names and corresponding status which DO
>NOT meet this rule.
>I've come up with the following cursor in PL/SQL (Oracle 7.3.3) :
>
>CURSOR rule_cursor IS
>SELECT a.name, a.status
>FROM table_a a
>WHERE a.status = 'P'
>AND a.name NOT IN
> (SELECT b.name
> FROM table_a a,
> table_b b
> WHERE a.name = b.name);
>
>My question is this ... is there an easier, more efficient way of doing
>this? I'm fairly new to PL/SQL, and to me, it seems an awful amount of
>work to be done for something which seems straight forward. I've tried
>more "simpler" SELECTS, but all come back with incorrect results.
>
a probably faster performing query will be:
select a.name, a.status
from table_a a
where a.status = 'P'
and not exists ( select null
from table b
where b.name = a.name )
>Any help will be greatly appreciated.
>Thanks in advance.
>
See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/
Current article is "Fine Grained Access Control", added June 8'th
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA--
 |
 |