我有这样的存储过程
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;
它按预期返回一行.
请帮助我理解为什么当逻辑相同时它会表现得很奇怪,除了我执行它的方式.