PL/SQL : Alternative way of doing if any

From: baka <mesundara_at_hotmail.com>
Date: 17 Aug 2005 19:58:06 -0700
Message-ID: <1124333886.563585.288740@g44g2000cwa.googlegroups.com>

Dear Sir/Madam & others

i have one simple procedure to get some code if a string match exists.  written procedure to achive this. but feel it is not an efficient one. can any one give altenative approch to this problem if any.

Thanks in advance for looking into this problem. You are all great people.

data for this procedure

---

create table tmaster(
  tcode varchar2(8),
  tfolder varchar2(1048);
/
insert into tmaster(tcode,tfolder) values('T0000001','oracle'); insert into tmaster(tcode,tfolder) values('T0000002','db2');

SQL> exec gcode;
pathand file=D:\Google\oracle\@searchresult of oracle\CDROM\__work\shiryou
tcode=T0000001

---

Procedure gcode

---

create or replace procedure gcode as

spthatandfile varchar2(512):='D:\Google\oracle\@searchresult of oracle\CDROM\__work\shiryou';
/*
spthatandfile varchar2(512):='D:\Google\db2\@searchresult of db2\CDROM\__work\shiryou';
 the above data comes from the outer loop records */
tcode char(8);

CURSOR get_tcode_cur IS

     SELECT TCODE
      FROM TMASTER tm
      WHERE INSTR( spthatandfile,'\'||tm.TFOLDER||'\',1)!=0 ;

begin

    OPEN get_tcode_cur;
    FETCH get_tcode_cur
      INTO tcode;
    IF (get_tcode_cur%NOTFOUND) THEN
      tcode := '99999999';
    END IF;
    CLOSE get_tcode_cur;

     DBMS_OUTPUT.PUT_LINE('pathand file='||spthatandfile);
     DBMS_OUTPUT.PUT_LINE('tcode='||tcode);

end;

---

Received on Wed Aug 17 2005 - 21:58:06 CDT