Question

使用显示游标,分别查询emp表的员工部门为20以及30的empno ename

Answer 1

CREATE OR REPLACE PROCEDURE XXX
AS
cursor cur_emp is
select empno,ename from emp where empno=20 or empno=30

rEmp cur_emp%rowtype;

BEGIN
BEGIN
OPEN cur_emp;
LOOP
FETCH cur_emp INTO rEmp;
EXIT WHEN cur_emp%NOTFOUND;

END LOOP;
CLOSE cur_emp;

COMMIT WORK;

EXCEPTION
WHEN OTHERS THEN
CLOSE cur_emp;
ROLLBACK WORK;
DBMS_OUTPUT.PUT_LINE('stat failed,error code:'||SQLCODE||',error message:'||SUBSTR(SQLERRM,1,100));
END;
END;