int attr1;
int attr2;
int attr3;
int attr4;
int attr5;
int attr6;
int attr7;
int attr8;
int attr9;
int attr10;
int attr11;
int attr12;
int attr13;
int attr14;
int attr15;
CREATE TABLE tableA
(tableid int not null,
col1 int not null,
col2 int not null,
col3 int not null,
col4 int not null,
col5 int not null,
col6 int not null,
col7 int not null,
col8 int not null,
col9 int not null,
col10 int not null,
col11 int not null,
col12 int not null,
col13 int not null,
col14 int not null,
col15 int not null
)
CREATE TABLE tableB
(tableBid int not null,
parentid int not null,
coll1 int not null,
coll2 int not null,
coll3 int not null,
coll4 int not null,
coll5 int not null,
coll6 int not null,
coll7 int not null,
coll8 int not null,
coll9 int not null,
coll10 int not null,
coll11 int not null,
coll12 int not null,
coll13 int not null,
coll14 int not null,
coll15 int not null
)
假設我們的存儲過程大緻是下面的樣子,很長時間都沒有寫存儲過程,基本上都不知道怎麼寫了。
for cur in (
select a.*,b.* from tableA a, tableB b where a.tableid = b.parentid
) loop
p_nt_results.extend;
p_nt_results(i) = new ob_result;
...
end loop;
省略号的部分就是我們要給集合指派的部分,這部分可以使用正規表達式來做。
使用該語句
select a.*,b.* from tableA a, tableB b where a.tableid = b.parentid having 1 <> 1;