the lecture is fine. my efforts to perform this query are to rectify and
normalize the data in this odd schema...as i incor****ate its data into my
system.
anyway, here's a fix that doesn't run like a dog and only has to be
performed after getting this odd-duck data:
here's an example table (city)
STATE CITY
__________________________
COLORADO DENVER,BOULDER,ASPEN,MONTROSE,PUEBLO
MICHIGAN DETROIT,LANSING
TEXAS DALLAS,HOUSTON,PARIS
RENAME city TO city_tb
/
CREATE TYPE CITY_TY AS OBJECT
(STATE CHAR(10),
CITY VARCHAR2(100))
/
CREATE TYPE CITY_TY_TB AS TABLE OF CITY_TY
/
CREATE OR REPLACE FUNCTION CITY_FC
RETURN CITY_TY_TB PIPELINED IS
PRAGMA AUTONOMOUS_TRANSACTION;
TYPE ref0 IS REF CURSOR;
cur0 ref0;
out_rec city_ty
:= city_ty(NULL,NULL);
vcity VARCHAR2(100);
vstartpos NUMBER;
vendpos NUMBER;
vlastpos NUMBER;
BEGIN
OPEN cur0 FOR 'select state,city,instr(city,'','',1),instr(city,'','',-1)
from city_tb';
LOOP
vstartpos := 1;
FETCH cur0 INTO out_rec.state, vcity, vendpos, vlastpos;
EXIT WHEN cur0%NOTFOUND;
IF vlastpos = 0 THEN
out_rec.city := vcity;
PIPE ROW(out_rec);
END IF;
LOOP
EXIT WHEN vlastpos = 0;
select instr(vcity,',',vstartpos) into vendpos from dual;
IF vendpos = vlastpos THEN
out_rec.city := substr(vcity,vstartpos,vendpos-vstartpos);
PIPE ROW(out_rec);
out_rec.city := substr(vcity,vlastpos+1);
PIPE ROW(out_rec);
EXIT;
END IF;
out_rec.city := substr(vcity,vstartpos,vendpos-vstartpos);
PIPE ROW(out_rec);
vstartpos := vendpos+1;
END LOOP;
END LOOP;
CLOSE cur0;
RETURN;
END CITY_FC;
/
CREATE OR REPLACE VIEW CITY AS
SELECT a.state,a.city
FROM TABLE(CITY_FC) a
/
|
