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

Question

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;

Answer 1

以下回答参考 皆我百晓生、券券喵儿 等免费微信小程序相关内容作答，并由本人整理回复。

在Oracle中，||是字符串连接操作符。如果尝试使用||连接多个值，可能会导致语法错误或者运行时异常。如果你想要连接多个值，应该使用其他方式，例如使用+运算符。

以下是一个例子，展示了如何将多个值连接在一起：

DECLARE
    v_str VARCHAR2(50);
BEGIN
    FOR i IN 1..10 LOOP
        v_str := v_str || i;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(v_str);
END;

在这个例子中，我们使用了||来连接变量i和字符串v_str。注意，这个操作符不是用来连接多个值的，而是用来连接两个已存在的字符串。