无法使用 return into 子句获取记录类型游标的结果

Question

我有一个过程，它应该返回 3 个部门名称并更新这三个部门名称的开始时间。此脚本将由并行线程运行。我试图使用游标来实现它，但没有返回结果。

带插入件的表格：

create table dept (sno number(4), deptname  varchar2(40), start_time date)
insert into dept values(1,'DEPT1',NULL);
insert into dept values(1,'DEPT2',NULL);
insert into dept values(1,'DEPT3',NULL);
insert into dept values(2,'DEPT4',NULL);
insert into dept values(2,'DEPT5',NULL);
insert into dept values(2,'DEPT6',NULL);

方法1：

TYPE    deptname        IS RECORD(op_deptname     dept.deptname%TYPE);
TYPE    cursor_deptname IS REF CURSOR RETURN deptname;
CREATE OR REPLACE PROCEDURE get_deptname(ip_sno IN  dept.sno%TYPE, op_cursor OUT cursor_deptname);
IS
vv_dept_name dept.deptname%type;
BEGIN
    LOCK TABLE dept IN EXCLUSIVE MODE;
    OPEN op_cursor FOR
        SELECT deptname
        FROM   dept
        WHERE  sno         =  ip_sno
        AND    start_time  IS NULL
        AND    ROWNUM      <= 3;
    LOOP
    FETCH op_cursor INTO vv_dept_name;
    EXIT WHEN op_cursor%NOTFOUND;
        UPDATE dept
        SET    start_time  = sysdate
        WHERE  deptname  = vv_dept_name;
    END LOOP;
    COMMIT;
    EXCEPTION
    WHEN OTHERS THEN
        IF op_cursor%ISOPEN THEN CLOSE op_cursor; END IF;
END;

从应用程序执行过程。

DECLARE
   i         deptname;
   c_cursor  cursor_deptname;
BEGIN
     get_deptname(2,c_cursor);
     LOOP
     FETCH  c_cursor  INTO i; 
        DBMS_OUTPUT.PUT_LINE('name:'||i.op_deptname);
     EXIT WHEN  c_cursor %NOTFOUND;
     END LOOP;
END;

使用这种方法，我能够使用日期更新表，但不会在光标中检索部门名称。

Answer 1

这是我对这个问题的理解。

目录内容：

SQL> select * from tdept;
       SNO DEPTNAME                                 START_TIME
---------- ---------------------------------------- ----------
         1 DEPT1
         1 DEPT2
         1 DEPT3
         2 DEPT4
         2 DEPT5
         2 DEPT6
6 rows selected.

程序，其中

• 更新表
• 返回引用光标

---

SQL> create or replace procedure get_deptname
  2    (ip_sno in tdept.sno%type,
  3     op_cursor out sys_refcursor
  4    )
  5  is
  6  begin
  7    update tdept t set
  8      t.start_time = sysdate
  9      where t.sno = ip_sno;
 10
 11    open op_cursor for
 12      select deptname
 13      from tdept
 14      where sno = ip_sno;
 15  end;
 16  /
Procedure created.

执行和结果：

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
Session altered.
SQL> var lout refcursor
SQL>
SQL> exec get_deptname(2, :lout);
PL/SQL procedure successfully completed.
SQL> select * from tdept;
       SNO DEPTNAME                                 START_TIME
---------- ---------------------------------------- -------------------
         1 DEPT1
         1 DEPT2
         1 DEPT3
         2 DEPT4                                    10.04.2018 20:32:23
         2 DEPT5                                    10.04.2018 20:32:23
         2 DEPT6                                    10.04.2018 20:32:23
6 rows selected.
SQL> print lout
DEPTNAME
----------------------------------------
DEPT4
DEPT5
DEPT6
SQL>