准备在java中调用 用游标返回的存储过程 这个在pl/sql中显示这个存储过程是有问题的,begin中的sql语句 拿出来是可以以用的 只不过放在存储过程里面时候是把 固定的值 换成了参数 不知道哪里错了,希望大神帮看看
CREATE OR REPLACE PACKAGE TOLL_PATH_PACKAGE IS
--动态游标
TYPE REFCURSOR IS REF CURSOR;
END TOLL_PATH_PACKAGE;
CREATE OR REPLACE PROCEDURE TOLL_PATH_LENGTH AS
(
CUR_RET OUT TOLL_PATH_PACKAGE.REFCURSOR,
IN_LINK IN NUMBER, --起始
OUT_LINK IN NUMBER --目的
)
IS
BEGIN
OPEN CUR_RET FOR
with T (LINK_PID,S_NODE_PID,E_NODE_PID,tollpath,LENGTH,lvl) as (
select LINK_PID,
S_NODE_PID,
E_NODE_PID,
'/'||LINK_PID as tollpath,
LENGTH,
1 as lvl
from rd_link123
where LINK_PID = IN_LINK --起始
union all
select r.link_pid,
r.s_node_pid,
r.e_node_pid,
t.tollpath||'/'||r.link_pid,
t.length + r.length,
t.lvl + 1
from t,rd_link123 r
where r.s_node_pid = t.e_node_pid
and IN_LINK<> r.e_node_pid --不能转回来
and t.link_pid <>OUT_LINK --目的地
and lvl <400
and instr(t.tollpath,'/'||r.link_pid)=0
)
select t.link_pid,to_clob(t.tollpath||'/') path,t.length
from T where LINK_PID=OUT_LINK;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('出错了');
END TOLL_PATH_LENGTH;