目前的报错信息如下:
begin Get_TreeDepth(21); end;ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 'GET_TREEDEPTH' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored存储过程代码如下,因为要交论文,这几天已经快被逼疯了,非常感谢各位朋友的帮助:create or replace procedure Get_TreeDepth
(Node in NUMBER,
Depth out number
)
as
N number;
sql_exec varchar2(2000);
cursor C1 is
select levelno from frank.msot_nodetest where id = Node;
begin
--建立临时表
sql_exec := 'create global temporary table NodeCal
(id number(10),
levelno number(10),
QTY number(10),
ParentID number(10))
on commit preserve rows';execute immediate sql_exec;sql_exec := 'INSERT INTO NodeCal
select * from frank.msot_nodetest where id = :NODE';execute immediate sql_exec using Node;open c1;
fetch C1 into Depth;
close C1;--开始逐层遍历
sql_exec := 'select count(*) into :n from NodeCalno commit preserve rows';
execute immediate sql_exec;
while n>0 LOOP
execute immediate 'delete * from NodeCal on commit';
execute immediate 'insert into NodeCal (id,levelno,qty,parentid) select id,levelno,qty,parentid from msot_nodetest where parentid in (select id from NodeCal) on commit';
Depth := Depth + 1;
end loop;
DBMS_OUTPUT.PUT_LINE(Depth-1);
exception
when no_data_found then
sql_exec := 'drop table NodeCal';
execute immediate sql_exec;
when value_error then
sql_exec := 'drop table NodeCal';
execute immediate sql_exec;
end Get_TreeDepth;
begin Get_TreeDepth(21); end;ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 'GET_TREEDEPTH' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored存储过程代码如下,因为要交论文,这几天已经快被逼疯了,非常感谢各位朋友的帮助:create or replace procedure Get_TreeDepth
(Node in NUMBER,
Depth out number
)
as
N number;
sql_exec varchar2(2000);
cursor C1 is
select levelno from frank.msot_nodetest where id = Node;
begin
--建立临时表
sql_exec := 'create global temporary table NodeCal
(id number(10),
levelno number(10),
QTY number(10),
ParentID number(10))
on commit preserve rows';execute immediate sql_exec;sql_exec := 'INSERT INTO NodeCal
select * from frank.msot_nodetest where id = :NODE';execute immediate sql_exec using Node;open c1;
fetch C1 into Depth;
close C1;--开始逐层遍历
sql_exec := 'select count(*) into :n from NodeCalno commit preserve rows';
execute immediate sql_exec;
while n>0 LOOP
execute immediate 'delete * from NodeCal on commit';
execute immediate 'insert into NodeCal (id,levelno,qty,parentid) select id,levelno,qty,parentid from msot_nodetest where parentid in (select id from NodeCal) on commit';
Depth := Depth + 1;
end loop;
DBMS_OUTPUT.PUT_LINE(Depth-1);
exception
when no_data_found then
sql_exec := 'drop table NodeCal';
execute immediate sql_exec;
when value_error then
sql_exec := 'drop table NodeCal';
execute immediate sql_exec;
end Get_TreeDepth;
解决方案 »
- 求这条sql该怎么写。
- 这该如何做?
- oracle存储过程如何在运行中让其报错
- oracle如何能够快速的读取大量数据
- SQL 语句的拼写
- oracle+resin+jsp出现的驱动问题?
- 有多个字段以上相同的SQL怎么写?
- 才开始学Oracle问个弱智问题:在oracle中每添加一条记录,记录是不是一定都添加到表的最后一条的后面呀
- 【★】 一份 UNIX 下的 SQL 作业 【★】 --------求助------【现金报酬】【英文】
- oracle启动不了!急!!!!(高手请帮忙,在线等待)
- 向高手寻求解答,,,,,这个SQL该如何优化?????????????????????
- oracle 建表如何提高检索速度
v_Depth number
begin Get_TreeDepth(21, v_Depth); end;
sql_exec := 'create global temporary table NodeCal
(id number(10),
levelno number(10),
QTY number(10),
ParentID number(10))
on commit preserve rows';execute immediate sql_exec;
上面这段应该报错,不能用动态SQL创建表;
--把过程里面的参数改下
create or replace procedure Get_TreeDepth
(Node in frank.msot_nodetest.id%type,
Depth out frank.msot_nodetest.levelno%type
)
(Node in NUMBER,
Depth out number
)begin Get_TreeDepth(21);
参数不对参考1楼
execute immediate 'insert into NodeCal (id,levelno,qty,parentid) select id,levelno,qty,parentid from msot_nodetest where parentid in (select id from NodeCal) on commit';红色部分,错误的
( Node in NUMBER,
Depth out number
)
as
N number;
sql_exec varchar2(2000);
cursor C1 is
select levelno from frank.msot_nodetest where id = Node;
begin
--建立临时表
sql_exec := 'create global temporary table NodeCal
(id number(10),
levelno number(10),
QTY number(10),
ParentID number(10))
on commit preserve rows'; execute immediate sql_exec; sql_exec := 'INSERT INTO NodeCal
select * from frank.msot_nodetest where id = :NODE'; execute immediate sql_exec using Node; -- 下面的“逐层遍历”应该放在游标循环体中吧?
open c1;
fetch C1 into Depth;
close C1; --开始逐层遍历
sql_exec := 'select count(*) into :n from NodeCalno commit preserve rows'; -- 存储过程里面定义的变量,不能用于绑定!(只有存储过程传入的参数才能用于绑定!)
execute immediate sql_exec;
while n > 0 LOOP
execute immediate 'delete * from NodeCal on commit'; -- delete 不需要 *,不需要 on commit,要不就用purge代替on commit
execute immediate 'insert into NodeCal (id,levelno,qty,parentid) select id,levelno,qty,parentid from msot_nodetest where parentid in (select id from NodeCal) on commit'; -- on commit 不需要!
Depth := Depth + 1;
end loop; DBMS_OUTPUT.PUT_LINE(Depth-1);
exception
when no_data_found then
sql_exec := 'drop table NodeCal';
execute immediate sql_exec;
when value_error then
sql_exec := 'drop table NodeCal';
execute immediate sql_exec;
end Get_TreeDepth;
( Node in NUMBER,
Depth out number
)
as
N number;
sql_exec varchar2(2000);
cursor C1 is
select levelno from frank.msot_nodetest where id = Node;
begin
--建立临时表
sql_exec := 'create global temporary table NodeCal
(id number(10),
levelno number(10),
QTY number(10),
ParentID number(10))
on commit preserve rows'; execute immediate sql_exec; sql_exec := 'INSERT INTO NodeCal
select * from frank.msot_nodetest where id = :NODE'; execute immediate sql_exec using Node; -- 下面的“逐层遍历”应该放在游标循环体中吧?
open c1;
fetch C1 into Depth;
close C1; --开始逐层遍历
sql_exec := 'select count(*) into :n from NodeCalno commit preserve rows'; -- 存储过程里面定义的变量,不能用于绑定!(只有存储过程传入的参数才能用于绑定!)
execute immediate sql_exec;
while n > 0 LOOP
execute immediate 'delete * from NodeCal on commit'; -- delete 不需要 *,不需要 on commit,要不就用purge代替on commit
execute immediate 'insert into NodeCal (id,levelno,qty,parentid) select id,levelno,qty,parentid from msot_nodetest where parentid in (select id from NodeCal) on commit'; -- on commit 不需要!
Depth := Depth + 1;
end loop; DBMS_OUTPUT.PUT_LINE(Depth-1);
exception
when no_data_found then
sql_exec := 'drop table NodeCal';
execute immediate sql_exec;
when value_error then
sql_exec := 'drop table NodeCal';
execute immediate sql_exec;
end Get_TreeDepth;