http://topic.csdn.net/u/20091024/18/2c2ca7da-6005-4121-8194-6e67499325ca.html这个帖子,刚才你给楼主回复了已经,可是我有点疑问,很不幸的你还没看到我的疑问楼主就把帖子给结了……
不知道你和楼主测试的结果是怎样的,我试了一下,好像不太对.SQL> exec proc('201410');
no record foundPL/SQL procedure successfully completed.SQL> exec proc('2014');
no record foundPL/SQL procedure successfully completed.SQL> exec proc('20');
SP2-0642: SQL*Plus internal error state 2500, context 7:8:0
Unsafe to proceedPL/SQL procedure successfully completed.SQL> col fun_name format a30
SQL> col fun_node format a30
SQL> select * from sm_funcregister;FUN_NODE FUN_NAME
------------------------------ ------------------------------
20 财务会计
201410 基础设置
201410 基础设置SQL>创建proc代码就是你在那个帖子里第二次回帖的内容
create or replace procedure proc(v_input in varchar2)
is
v_i number := 2;
v_t varchar2(200);
v_r varchar2(2000);
begin
if length(v_input) < 2 then
v_r := ' ';
dbms_output.put_line('invalid input value');
else
while v_i <= length(v_input) loop
select fun_name into v_t from sm_funcregister where fun_node = substr(v_input,1,v_i);
if v_i = 2 then
v_r := v_t;
else
v_r := v_r||'-'||v_t;
end if;
v_i := v_i + 2;
end loop;
end if;
dbms_output.put_line(v_r);
exception
when no_data_found then
v_r := ' ';
dbms_output.put_line('no record found');
when others then
raise;
end proc;
/
不知道你和楼主测试的结果是怎样的,我试了一下,好像不太对.SQL> exec proc('201410');
no record foundPL/SQL procedure successfully completed.SQL> exec proc('2014');
no record foundPL/SQL procedure successfully completed.SQL> exec proc('20');
SP2-0642: SQL*Plus internal error state 2500, context 7:8:0
Unsafe to proceedPL/SQL procedure successfully completed.SQL> col fun_name format a30
SQL> col fun_node format a30
SQL> select * from sm_funcregister;FUN_NODE FUN_NAME
------------------------------ ------------------------------
20 财务会计
201410 基础设置
201410 基础设置SQL>创建proc代码就是你在那个帖子里第二次回帖的内容
create or replace procedure proc(v_input in varchar2)
is
v_i number := 2;
v_t varchar2(200);
v_r varchar2(2000);
begin
if length(v_input) < 2 then
v_r := ' ';
dbms_output.put_line('invalid input value');
else
while v_i <= length(v_input) loop
select fun_name into v_t from sm_funcregister where fun_node = substr(v_input,1,v_i);
if v_i = 2 then
v_r := v_t;
else
v_r := v_r||'-'||v_t;
end if;
v_i := v_i + 2;
end loop;
end if;
dbms_output.put_line(v_r);
exception
when no_data_found then
v_r := ' ';
dbms_output.put_line('no record found');
when others then
raise;
end proc;
/
SQL codeselect fun_name into v_t from sm_funcregister where fun_node = substr(v_input,1,v_i);
if v_i = 2 then
v_r := v_t;
else
v_r := v_r||'-'||v_t; /*这里岂不是重复了,比如说有个数据是2014,第一次循环的话v_r和v_t都为20,第二次循环的时候v_i变为4,那么在substr函数中就会提取出2014给v_t,那么这个时候v_r就会被赋值为20||2014=202014*/
end if;
v_i := v_i + 2;
FUN_NODE FUN_NAME
------------------------------ ------------------------------
20 财务会计
201410 基础设置
201410 基础设置
第二条FUN_NODE改成2014试试我没有测试过代码
还有 把表里的数据改一下过程执行就正确了
谢了哦 呵呵