CREATE OR REPLACE PROSEDURE Query_Manger(manager_id out number) AS
CURSOR c_manager(temp_manager_id in varchar2) IS
SELECT id,name
FROM emp
WHERE mid=temp_manager_id;
emp_info c_manager%rowtype;
emp_id varchar2;
emp_name varchar2;
BEGIN
OPEN c_manager(manager_id);
LOOP
FETCH c_manager into emp_info.id,emp_info.name;
EXIT WHEN c_manager%NOTFOUND;
dbms_output.put_line(emp_info.name);
INSERT INTO emp_result (id, name) VALUES (emp_info.id, emp_info.name);
Query_Manger(emp_info.id);
END LOOP;
CLOSE c_manager;
EXCEPTION
................
END;
以上语句是否有问题?
CURSOR c_manager(temp_manager_id in varchar2) IS
SELECT id,name
FROM emp
WHERE mid=temp_manager_id;
emp_info c_manager%rowtype;
emp_id varchar2;
emp_name varchar2;
BEGIN
OPEN c_manager(manager_id);
LOOP
FETCH c_manager into emp_info.id,emp_info.name;
EXIT WHEN c_manager%NOTFOUND;
dbms_output.put_line(emp_info.name);
INSERT INTO emp_result (id, name) VALUES (emp_info.id, emp_info.name);
Query_Manger(emp_info.id);
END LOOP;
CLOSE c_manager;
EXCEPTION
................
END;
以上语句是否有问题?
这句有问题
emp_name varchar2(10);CREATE OR REPLACE PROSEDURE Query_Manger(manager_id in number) AS 应该不支持这种写法。可以将迭代放在一个过程内部完成
create or replace PROSEDURE Query_Manger(temp_manager_id in varchar,manager_id out number) is
type cursor_type is ref cursor;
StartCursor cursor_type;
emp_id emp.id%type;
emp_name emp.name%type;
begin
open c_manager for SELECT id,name FROM emp WHERE mid=temp_manager_id;
loop
fetch c_manager into emp_id,emp_name ;
exit when c_manager%NOTFOUND;
end loop;
CLOSE c_manager;
end;
有问题,但是还是有点不明白你想干什么
create or replace PROSEDURE Query_Manger(temp_manager_id in varchar,manager_id out number) is
type cursor_type is ref cursor;
StartCursor cursor_type;type RecordType is record(
emp_id emp.id%type,
emp_name emp.name%type
);
StartRecord RecordType;
begin
open c_manager for SELECT id,name FROM emp WHERE mid=temp_manager_id;
loop
fetch c_manager into StartRecord ;
exit when c_manager%NOTFOUND;
end loop;
CLOSE c_manager;
end;
CURSOR c_manager(temp_manager_id in varchar2) IS SELECT id,name FROM emp WHERE id = temp_manager_id;
emp_info c_manager%rowtype;
emp_id varchar2(10);
emp_name varchar2(10);
BEGIN
OPEN c_manager(manager_id);
LOOP
FETCH c_manager into emp_info.id,emp_info.name;
EXIT WHEN c_manager%NOTFOUND;
dbms_output.put_line(emp_info.name);
INSERT INTO emp_result (id, name) VALUES (emp_info.id, emp_info.name);
Query_Manger(emp_info.id);
END LOOP;
CLOSE c_manager;
EXCEPTION
................
END;
as
begin
insert into emp_result
select id,name from emp
start with mid=manager_id
connect by prior id=mid;
exception
.....
end;
manager A
| | | |
lv1 employ a b c d
| | | |
lv2 employ 1 2 3 4
..... >>............14楼这样会不会只找到A-a-1呢?
你可以将里面那句
select id,name from emp
start with mid=manager_id
connect by prior id=mid;
取出
将manageer_id换成具体的值测试一下