麻烦看个存储过程

Question

create or replace procedure sp_Department(mycode Pub_Department.Deptcode%Type, curPubx out SYS_REFCURSOR) 
as
  sqlstr varchar2(200) := 'select DEPTCODE,DEPTNAME,STOPFLAG from Pub_Department where DEPTCODE=:p_id and XSFLAG=1';
  dept_rec pub_department%ROWTYPE;
begin
  open curPubx for sqlstr using mycode;
  LOOP
    FETCH curPubx INTO dept_rec;
    EXIT WHEN curPubx%NOTFOUND;
  END LOOP;
   
   if dept_rec.deptcode is null THEN
     raise_application_error(-20016, '此部门不存在');
   elsif dept_rec.stopflag = 0 THEN
       raise_application_error(-20016, '此部门已经停用');
   end if;
end;编译通过，但在调用时，提示
ORA-01007: 变量不在选择列表中
ORA-06512: 在 "SCOTT.SP_DEPARTMENT", line 8，即这行 FETCH curPubx INTO dept_rec;
是不是不能对out 游标进行遍历，那怎么才能达到对游标的遍历，进而进行下面的if语句呢。
还有这个游标返回的要么是一行数据要么就没有数据，能不能优化下。

Answer 1

pub_department只有DEPTCODE,DEPTNAME,STOPFLAG三个字段?

Answer 2

create or replace procedure sp_Department(mycode Pub_Department.Deptcode%Type, curPubx out SYS_REFCURSOR) 
as
  sqlstr varchar2(200) := 'select DEPTCODE,DEPTNAME,STOPFLAG from Pub_Department where DEPTCODE=:p_id and XSFLAG=1';
  dept_rec pub_department%ROWTYPE;
begin
  open curPubx for sqlstr using mycode;
  LOOP
    FETCH curPubx INTO dept_rec;
    EXIT WHEN curPubx%NOTFOUND;   
    if dept_rec.deptcode is null THEN
        raise_application_error(-20016, '此部门不存在');
    elsif dept_rec.stopflag = 0 THEN
        raise_application_error(-20016, '此部门已经停用');
   end if;
  END LOOP; --end loop 应该放在这里吧！ 不然dept_rec.deptcode就读取不到这个变量end;

Answer 3

CREATE OR REPLACE PROCEDURE sp_department(mycode  pub_department.deptcode%TYPE,
                                          curpubx OUT SYS_REFCURSOR) AS
  sqlstr VARCHAR2(200) := 'select DEPTCODE,DEPTNAME,STOPFLAG from Pub_Department where DEPTCODE=:p_id and XSFLAG=1';
  v_num  NUMBER;
  v_cnt  NUMBER;
BEGIN
  SELECT COUNT(*), SUM(decode(stopflag, 0, 1, 0))
    INTO v_num, v_cnt
    FROM pub_department
   WHERE deptcode = mycode
     AND xsflag = 1;
  IF v_num = 0 THEN
    raise_application_error(-20016, '此部门不存在');
  ELSIF dept_rec.stopflag = 0 THEN
    raise_application_error(-20016, '此部门已经停用');
  END IF;
  OPEN curpubx FOR sqlstr
    USING mycode;
END;

Answer 4

太谢谢  minitoy  我一直想着用这种流程，但是就是规划不好，现在好了。再次谢谢下

Answer 5

不客气,不过有个地方写错了
CREATE OR REPLACE PROCEDURE sp_department(mycode  pub_department.deptcode%TYPE,
                                          curpubx OUT SYS_REFCURSOR) AS
  sqlstr VARCHAR2(200) := 'select DEPTCODE,DEPTNAME,STOPFLAG from Pub_Department where DEPTCODE=:p_id and XSFLAG=1';
  v_num  NUMBER;
  v_cnt  NUMBER;
BEGIN
  SELECT COUNT(*), SUM(decode(stopflag, 0, 1, 0))
    INTO v_num, v_cnt
    FROM pub_department
   WHERE deptcode = mycode
     AND xsflag = 1;
  IF v_num = 0 THEN
    raise_application_error(-20016, '此部门不存在');
  ELSIF v_cnt = 0 THEN
    raise_application_error(-20016, '此部门已经停用');
  END IF;
  OPEN curpubx FOR sqlstr
    USING mycode;
END;