Re: PL-SQL table and Cursor

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 7 Dec 2006 08:11:53 -0800
Message-ID: <1165507913.256646.79140@79g2000cws.googlegroups.com>

On Dec 7, 7:36 am, "sybrandb" <sybra..._at_gmail.com> wrote:
> On Dec 7, 12:32 pm, "varsha.gade..._at_gmail.com"
>
> <varsha.gade..._at_gmail.com> wrote:
> > I have lacks of records. I want to check if the account_no is present
> > in that table or not. In my current application i have used cursor.
>
> > If i took all the account no data in pl-sql table with index as account
> > no and value as account no,
> > and then i will just try to check if the record is present at
> > particular index then, will it be the faster way than cursor? if yes
> > how?The general rule is: only do it in PL/SQL if you can't do it in SQL
> (which is obviously not true here, as you can. SELECT ... where not
> exists would work like a charm).
> Only do it in some 3 GL language if you can't do it in PL/SQL.
> If you think it will be faster, consider you need to populate the
> PL/SQL table.
> Run explain plan on the affected statement and find out what happens.
> Do not resort to procedural techniques.
>
> --
> Sybrand Bakker
> Senior Oracle DBA

If you do need to perform some procedural checking then here are some samples. The code could stand improving but should do for an example. Each example is repeated to show the row exists and not exists conditions:

UT1 > @t13
UT1 > set echo on
UT1 > set serveroutput on size 4096
UT1 >
UT1 > declare

  2 -- key is unique logic
  3 v_variable varchar2(10);
  4 begin
  5 select fld1 into v_variable from marktest where fld1 = 'one';   6 dbms_output.put_line('Found Logic');   7 exception
  8 when no_data_found then
  9 dbms_output.put_line('Not found Logic');  10 end;
 11 /
Found Logic

PL/SQL procedure successfully completed.

UT1 > declare
  2 -- key is unique logic
  3 v_variable varchar2(10);
  4 begin
  5 select fld1 into v_variable from marktest where fld1 = 'xxx';   6 dbms_output.put_line('Found Logic');   7 exception
  8 when no_data_found then
  9 dbms_output.put_line('Not found Logic');  10 end;
 11 /
Not found Logic

PL/SQL procedure successfully completed.

UT1 > declare
  2 v_ctr number := 0;
  3 begin
  4 -- key is non-unique
  5 select count(*) into v_ctr from marktest where fld1 = 'one';   6 if v_ctr = 0 then dbms_output.put_line('Not Found Logic');   7 else dbms_output.put_line('Found Logic');   8 end if;
  9 end;
 10 /
Found Logic

PL/SQL procedure successfully completed.

UT1 > declare
  2 v_ctr number := 0;
  3 begin
  4 -- key is non-unique
  5 select count(*) into v_ctr from marktest where fld1 = 'xxx';   6 if v_ctr = 0 then dbms_output.put_line('Not Found Logic');   7 else dbms_output.put_line('Found Logic');   8 end if;
  9 end;
 10 /
Not Found Logic

PL/SQL procedure successfully completed.

UT1 > -- you can improve on the count logic if you do not need to know
UT1 > -- how many rows exists, only that one does, via using exists
UT1 > --

Again as Sybrand pointed out, do not use procedural code if you can just use SQL.

HTH -- Mark D Powell -- Received on Thu Dec 07 2006 - 10:11:53 CST