CREATE OR REPLACE PROCEDURE SELECT_EMP(P_EMPNO IN NUMBER,
P_ENAME OUT VARCHAR2,
P_SAL OUT VARCHAR2) IS
BEGIN
FOR X IN (SELECT * FROM EMP WHERE EMPNO = P_EMPNO) LOOP
IF X.EMPNO = P_EMPNO THEN
DBMS_OUTPUT.PUT_LINE(X.ENAME || X.SAL);
ELSE
DBMS_OUTPUT.PUT_LINE('查无此人');
END IF;
END LOOP;
END;
这个是调用:
DECLARE
v_empno number:=&v_empno;
v_ename VARCHAR2(255);
v_sal NUMBER;
BEGIN
select_emp(v_empno,v_ename,v_sal);
END;
在你的这个隐式游标的定义里,必须是要满足EMPNO = P_EMPNO,才会进入循环,而你IF X.EMPNO = P_EMPNO判断是在循环内的,所以都会满足,不存在查无此人的情况。
不清楚你原题有什么要求,下面这个至少可以满足你本题中的要求
CREATE OR REPLACE PROCEDURE SELECT_EMP(P_EMPNO IN NUMBER,
P_ENAME OUT VARCHAR2,
P_SAL OUT VARCHAR2) IS
V_EXISTS NUMBER;
BEGIN
SELECT COUNT(1) INTO V_EXISTS FROM EMP WHERE EMPNO = P_EMPNO;
IF V_EXISTS > 0 THEN
FOR X IN (SELECT * FROM EMP WHERE EMPNO = P_EMPNO) LOOP
DBMS_OUTPUT.PUT_LINE(X.ENAME || X.SAL);
end loop;
ELSE
DBMS_OUTPUT.PUT_LINE('查无此人');
END IF;
END;