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;
以上语句是否有问题?
解决方案 »
- Oracle语句的解释
- rownum的疑问
- 谁有oracle oo4o 的开发资料啊,中英文都可以。
- 求一条切字符串的SQL
- 妹妹求助,外网的机子怎么通过Web服务器访问与其连接着的内网Oracle?谢谢!
- 请上星级的大侠们回答,你们都是做什么工作的?怎么会有如此多的时间助人为乐?谢谢你们!
- ***************很简单的问题,送分,快来啊*************
- 初级问题:在oracle中它的存储过程,是指得什么?
- 在oracle中在两个整型值之间执行按位逻辑与运算的运算符是什么?
- oracle设置记录寿命
- 知道一个时间,和时间差,求另一个时间
- 如何在ORACLE的存储过程里传参数,并且存储过程里面是in的结构
这句有问题
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换成具体的值测试一下