oracle 存储过程返回,oracle – 存储过程没有返回正确的结果

我有这样的存储过程

CREATE OR REPLACE PROCEDURE schema_name.CHECKS

IS

tbl_name VARCHAR2 (50);

constraint_nm VARCHAR2 (100);

CURSOR cur_constraint

IS

SELECT DISTINCT table_name,constraint_name

FROM all_constraints

WHERE constraint_type = 'R'

AND STATUS = 'ENABLED'

AND R_OWNER = 'owner1'

AND r_constraint_name = 'constraint1';

BEGIN

DBMS_OUTPUT.put_line ('Constraint Name');

OPEN cur_constraint;

LOOP

FETCH cur_constraint

INTO tbl_name,constraint_nm;

EXIT WHEN cur_constraint%NOTFOUND;

DBMS_OUTPUT.put_line (constraint_nm||'~~'||tbl_name);

END LOOP;

close cur_constraint;

END CHECKS;

我执行此过程

set serveroutput on

BEGIN

schema_name.CHECKS ();

END;

我得到的输出是

Procedure created.

Constraint Name

PL/sql procedure successfully completed.

它没有返回任何结果,但理想情况下它应该返回一行(用于定义游标的select查询将返回一行).

当我像这样执行上面的代码作为PL / sql块时

DECLARE

tbl_name VARCHAR2 (50);

constraint_nm VARCHAR2 (100);

CURSOR cur_constraint

IS

SELECT DISTINCT table_name,constraint_name

FROM all_constraints

WHERE constraint_type = 'R'

AND STATUS = 'ENABLED'

AND R_OWNER = 'owner1'

AND r_constraint_name = 'constraint1';

BEGIN

FOR i IN cur_constraint

LOOP

EXIT WHEN cur_constraint%NOTFOUND;

DBMS_OUTPUT.put_line (i.constraint_name||' is in '||i.table_name);

END LOOP;

END;

它按预期返回一行.

请帮助我理解为什么当逻辑相同时它会表现得很奇怪,除了我执行它的方式.