TOAD显示存储过程返回的游标记录集 [英] TOAD displaying cursor recordset returned by stored procedure

问题描述

请问如何从返回的光标中打印重新设置的结果. 下方执行正常，但我需要查看结果.

How I could print recorset result from the returning cursor, please. Down below executes fine but I need to see result.

这是TOAD中的一个块，调用程序包sp AMD_NEEDMSG:

This is block in TOAD, calling package sp AMD_NEEDMSG:

DECLARE 
     RETURN_RECORDSET CTI_MATRIX.AMD.REF_CURSOR;
    BEGIN 
     CTI_MATRIX.AMD.AMD_NEEDMSG ( '88888888885', RETURN_RECORDSET );
END;

此包装规格:

    CREATE OR REPLACE PACKAGE CTI_MATRIX.AMD AS
      TYPE REF_CURSOR IS REF CURSOR;
      PROCEDURE AMD_NEEDMSG (v_CRN IN VARCHAR2, return_recordset OUT REF_CURSOR);
    END AMD;

这是程序包主体:

CREATE OR REPLACE PACKAGE BODY CTI_MATRIX.AMD AS

PROCEDURE AMD_NEEDMSG (v_CRN IN VARCHAR2, return_recordset OUT REF_CURSOR) IS


return_flag INTEGER;
row_cnt INTEGER;
number_of_days INTEGER;
var_DATE DATE;

CURSOR ACCNTSEARCH (P_CRN IN VARCHAR2) IS
    SELECT DISTINCT COUNT(*) , AMD.MSG_DATE    
       FROM TBL_AMD_NEEDMSG AMD
      WHERE AMD.PHONE_NUMBER = P_CRN AND ROWNUM = 1;

BEGIN 
 OPEN ACCNTSEARCH(v_CRN);
 FETCH ACCNTSEARCH INTO row_cnt, var_DATE;
 CLOSE ACCNTSEARCH;

 IF (row_cnt = 0)
 THEN
        INSERT INTO TBL_AMD_NEEDMSG (PHONE_NUMBER, MSG_DATE) VALUES (v_CRN , SYSDATE); 
        return_flag := 1;
 ELSE
    SELECT SYSDATE-var_DATE INTO number_of_days FROM dual;

     IF (number_of_days>7)
     THEN 
        UPDATE TBL_AMD_NEEDMSG SET MSG_DATE = SYSDATE WHERE PHONE_NUMBER = v_CRN;
        return_flag := 1;
    ELSE
         return_flag := 0;
    END IF;

 END IF;    

 COMMIT;

 OPEN return_recordset FOR 
 SELECT return_flag AS ReturnFLag FROM DUAL;

EXCEPTION
 WHEN OTHERS THEN
    ROLLBACK;

END AMD_NEEDMSG;

END AMD;
/

最重要的是，以记录集的形式向客户返回return_flag的值.

Bottom line is to return to client a value of return_flag in the form of record set.

推荐答案

要返回return_flag(整数= 1或0)，您需要返回单个值而不是记录集的函数.下面是常规记录集示例-希望这会有所帮助:

To return the return_flag, which is int=1 or 0 you need function that returns a single value, not recordset. Below are general recordset examples - hope this helps:

DECLARE
  TYPE empcurtyp IS REF CURSOR;
  emp_cv  empcurtyp;
--
  TYPE namelist IS TABLE OF scott.emp.ename%TYPE;
  TYPE sallist  IS TABLE OF scott.emp.sal%TYPE;
  names   namelist;
  sals    sallist;
BEGIN
  OPEN emp_cv FOR
    SELECT ename, sal FROM scott.emp
      WHERE job = 'MANAGER' ORDER BY sal DESC;
--
  FETCH emp_cv BULK COLLECT INTO names, sals;
  CLOSE emp_cv;

-- loop through the names and sals collections
 FOR i IN names.FIRST .. names.LAST LOOP
   DBMS_OUTPUT.PUT_LINE
     ('Name = ' || names(i) || ', salary = ' || sals(i));
   END LOOP;
END;
/

-- SYS_REFCURSOR example --
DECLARE
  p_rc_type  SYS_REFCURSOR;
  emp_rec    scott.emp%ROWTYPE;
 --
 PROCEDURE p_Emp_Info (p_cur_var OUT SYS_REFCURSOR)
 IS 
 BEGIN
    OPEN p_cur_var FOR 
     SELECT ename, job FROM scott.emp WHERE job = 'MANAGER';
   LOOP
    FETCH p_cur_var INTO emp_rec.ename, emp_rec.job;
    EXIT WHEN p_cur_var%NOTFOUND;
     dbms_output.put_line(emp_rec.ename ||' '|| emp_rec.job);
   END LOOP;
  CLOSE p_cur_var;
 END p_Emp_Info;
 --
 BEGIN
  p_Emp_Info(p_rc_type);
END;
/

这篇关于TOAD显示存储过程返回的游标记录集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！