问题描述

oracle 00306 调用‘||’时个数或参数错误

create or replace procedure lable_to_lable_relation is
Cursor phases
is
select distinct lt.phase from lable_temple lt ;

phaseName varchar2(100);

sqlStr varchar2(2000);
begin
--创建标签关系表
sqlStr:='create table SYSTEM.lable_relation (id number(16) primary key, lable_id number(16),parent_id number(16),del_mark number(1),levels number(2))';
EXECUTE IMMEDIATE sqlStr;

--插入数据第一层数据(表的序列为lable_relation_seq_test)
sqlStr:='
insert into lable_relation(id,lable_id,parent_id,del_mark,levels)
select lable_relation_seq_test.nextval,
(select tl.id from test_lable tl where tl.lable_name = A.lableName) as lable_id,
0 as parent_id,
0 as delmark,
(select tl.lable_type_id
from test_lable tl
where tl.lable_name = A.lableName) as leavels
from (select distinct lt.phase as lableName from lable_temple lt) A';
EXECUTE IMMEDIATE sqlStr;
--插入第二层数据信息
for phaseName in phases
Loop
sqlStr:='
insert into lable_relation(id,lable_id,parent_id,del_mark,levels)
select (select tl.id from test_lable tl where tl.lable_name = A.lableName) as lable_id,
(select A.id
from (select lr.id, lr.parent_id, lr.del_mark, lr.levels
from lable_relation lr
where lr.lable_id in
(select tl.id
from test_lable tl
where tl.lable_name ='||phaseName||' )) A
where A.levels = 1
start with A.parent_id = 0
Connect by prior A.id = A.parent_id) as parent_id,
0 as del_mark,
(select tl.lable_type_id
from test_lable tl
where tl.lable_name = A.lableName) as leavels
from (select distinct lt.subject as lableName
from lable_temple lt
where lt.phase ='||phaseName||' ) A';
EXECUTE IMMEDIATE sqlStr;
end Loop;
--插入敌三层数据信息

end lable_to_lable_relation;