问PL/SQL: ORA-29481:隐式结果不能返回给客户端

EN

DROP PROCEDURE IF EXISTS  `get_police_station_for_hierarchy` ;

DELIMITER $$

 CREATE  PROCEDURE `get_police_station_for_hierarchy`(officecd BIGINT,pstsaffcd VARCHAR(20))
BEGIN
DECLARE table_count INT;
DECLARE officerInCharge VARCHAR(20);
DROP TEMPORARY TABLE IF EXISTS   temp1;
DROP TEMPORARY TABLE IF EXISTS   temp2;
DROP TEMPORARY TABLE IF EXISTS  temp3;
DROP TEMPORARY TABLE IF EXISTS  temp4;
CREATE TEMPORARY TABLE temp1 (cd BIGINT,off_level INT);
CREATE TEMPORARY TABLE temp2 (cd BIGINT,off_level INT);
CREATE TEMPORARY TABLE temp3 (cd BIGINT,off_level INT);
CREATE TEMPORARY TABLE temp4 (cd BIGINT,off_level INT);

 SELECT OFFICE_INCHARGE_CD INTO officerInCharge FROM m_office_types  WHERE OFFICE_CD=officecd ;
 IF(officerInCharge=pstsaffcd) THEN
INSERT INTO temp1 
SELECT m_office_hierarchy.OFFICE_CD,m_office_types.OFFICE_TYPE_CD FROM m_office_hierarchy,m_office_types WHERE SUPERVISOR_OFFICE_CD =officecd
AND m_office_hierarchy.office_cd=m_office_types.office_cd AND m_office_hierarchy.lang_cd=m_office_types.lang_cd AND m_office_hierarchy.RECORD_STATUS <> 'D'
AND m_office_types.RECORD_STATUS <> 'D' AND m_office_types.OFFICE_TYPE_CD=7;
INSERT INTO temp2 
SELECT m_office_hierarchy.OFFICE_CD,m_office_types.OFFICE_TYPE_CD FROM m_office_hierarchy,m_office_types WHERE SUPERVISOR_OFFICE_CD=officecd
AND m_office_hierarchy.office_cd=m_office_types.office_cd AND m_office_hierarchy.lang_cd=m_office_types.lang_cd
AND m_office_hierarchy.RECORD_STATUS <> 'D' AND m_office_types.RECORD_STATUS <> 'D' AND m_office_types.OFFICE_TYPE_CD <> 7 ;
SELECT COUNT(*) INTO table_count FROM temp2;
WHILE (table_count>0)
DO 
INSERT INTO temp1 
SELECT m_office_hierarchy.OFFICE_CD,m_office_types.OFFICE_TYPE_CD FROM m_office_hierarchy,m_office_types WHERE SUPERVISOR_OFFICE_CD IN (SELECT cd FROM temp2)
AND m_office_hierarchy.office_cd=m_office_types.office_cd AND m_office_hierarchy.lang_cd=m_office_types.lang_cd
 AND m_office_hierarchy.RECORD_STATUS <> 'D' AND m_office_types.RECORD_STATUS <> 'D' AND m_office_types.OFFICE_TYPE_CD=7;
INSERT INTO temp3 
SELECT m_office_hierarchy.OFFICE_CD,m_office_types.OFFICE_TYPE_CD FROM m_office_hierarchy,m_office_types WHERE SUPERVISOR_OFFICE_CD IN (SELECT cd FROM temp2)
AND m_office_hierarchy.office_cd=m_office_types.office_cd AND m_office_hierarchy.lang_cd=m_office_types.lang_cd
AND m_office_hierarchy.RECORD_STATUS <> 'D' AND m_office_types.RECORD_STATUS <> 'D' AND m_office_types.OFFICE_TYPE_CD <> 7  AND m_office_hierarchy.OFFICE_CD NOT IN (SELECT cd FROM temp4) AND m_office_hierarchy.OFFICE_CD <> officecd ;
DELETE FROM temp2;
INSERT INTO temp2 SELECT * FROM temp3;
INSERT INTO temp4 SELECT * FROM temp3;
DELETE FROM temp3;
SELECT COUNT(*) INTO table_count FROM temp2;
END WHILE;
SELECT DISTINCT cd,M_OFFICE_TYPES.office_name FROM temp1,m_office_types WHERE temp1.cd=m_office_types.office_cd AND m_office_types.record_status <> 'D';
ELSE
SELECT '' ;
END IF ;
DROP TABLE  temp1;
DROP TABLE  temp2;
DROP TABLE  temp3;
DROP TABLE temp4;
END  $$
DELIMITER ;

  create or replace PROCEDURE get_police_statn_for_hierarchy(officecd IN NUMBER,pstsaffcd IN VARCHAR2)
       as
         v_refcur SYS_REFCURSOR;
        table_count  NUMBER(10,0);
        officerInCharge  VARCHAR2(20);
    BEGIN
    -- This procedure was converted on Mon Nov 14 14:57:57 2016 using Ispirer SQLWays 7.0 Build 3434 64bit Licensed to prabhat.gang@gmail.com - prabhat - India (Demo License, Ispirer MnMTK 2015 Mysql to Oracle Database Migration, 1 month, 20161114).

       EXECUTE IMMEDIATE ' TRUNCATE TABLE temp1 ';
       EXECUTE IMMEDIATE ' TRUNCATE TABLE temp2 ';
       EXECUTE IMMEDIATE ' TRUNCATE TABLE temp3 ';

       INSERT INTO temp1
       SELECT m_office_hierarchy.OFFICE_CD,m_office_types.OFFICE_TYPE_CD FROM m_office_hierarchy,m_office_types WHERE SUPERVISOR_OFFICE_CD = officecd
       and m_office_hierarchy.office_cd = m_office_types.office_cd and m_office_hierarchy.lang_cd = m_office_types.lang_cd and m_office_hierarchy.RECORD_STATUS <> 'D'
       and m_office_types.RECORD_STATUS <> 'D' and m_office_types.OFFICE_TYPE_CD = 7;
       INSERT INTO temp2
       SELECT m_office_hierarchy.OFFICE_CD,m_office_types.OFFICE_TYPE_CD FROM m_office_hierarchy,m_office_types WHERE SUPERVISOR_OFFICE_CD = officecd
       and m_office_hierarchy.office_cd = m_office_types.office_cd and m_office_hierarchy.lang_cd = m_office_types.lang_cd
       and m_office_hierarchy.RECORD_STATUS <> 'D' and m_office_types.RECORD_STATUS <> 'D' and m_office_types.OFFICE_TYPE_CD <> 7;
       SELECT COUNT(*) INTO table_count FROM temp2;
       WHILE (table_count > 0) loop
    INSERT INTO temp1
          SELECT m_office_hierarchy.OFFICE_CD,m_office_types.OFFICE_TYPE_CD FROM m_office_hierarchy,m_office_types WHERE SUPERVISOR_OFFICE_CD IN(SELECT cd FROM temp2)
          and m_office_hierarchy.office_cd = m_office_types.office_cd and m_office_hierarchy.lang_cd = m_office_types.lang_cd
          and m_office_hierarchy.RECORD_STATUS <> 'D' and m_office_types.RECORD_STATUS <> 'D' and m_office_types.OFFICE_TYPE_CD = 7;
          INSERT INTO temp3
          SELECT m_office_hierarchy.OFFICE_CD,m_office_types.OFFICE_TYPE_CD FROM m_office_hierarchy,m_office_types WHERE SUPERVISOR_OFFICE_CD IN(SELECT cd FROM temp2)
          and m_office_hierarchy.office_cd = m_office_types.office_cd and m_office_hierarchy.lang_cd = m_office_types.lang_cd
          and m_office_hierarchy.RECORD_STATUS <> 'D' and m_office_types.RECORD_STATUS <> 'D' and m_office_types.OFFICE_TYPE_CD <> 7;
          DELETE FROM temp2;
          INSERT INTO temp2  SELECT * FROM temp3;
          DELETE FROM temp3;
          SELECT COUNT(*) INTO table_count FROM temp2;
          table_count := 100;
       end loop;
       open v_refcur for SELECT DISTINCT cd,M_OFFICE_TYPES.office_name FROM temp1,m_office_types WHERE temp1.cd = m_office_types.office_cd and m_office_types.record_status <> 'D';
       dbms_sql.return_result(v_refcur);

       EXECUTE IMMEDIATE ' TRUNCATE TABLE temp1 ';
       EXECUTE IMMEDIATE ' TRUNCATE TABLE temp2 ';
       EXECUTE IMMEDIATE ' TRUNCATE TABLE temp3 ';

       close v_refcur; 
    END;