帶參數(shù)的光標(biāo)

cursor 光標(biāo)名 [(參數(shù)名 數(shù)據(jù)類型[,參數(shù)名 數(shù)據(jù)類型]...)]

is select 語(yǔ)句;

案例

--查詢某個(gè)部門中員工的姓名
SET SERVEROUTPUT ON

DECLARE
 --定義帶參數(shù)的光標(biāo)
 cursor cemp(dno number)is select first_name,last_name from EMPLOYEES where EMPLOYEES.DEPARTMENT_ID = dno;
 pe_first_name EMPLOYEES.FIRST_NAME%TYPE;
 pe_last_name EMPLOYEES.LAST_NAME%TYPE;
 
BEGIN
 --打開(kāi)光標(biāo)
 open cemp(60);
 
 loop
  --取出每個(gè)員工的姓名
  FETCH cemp into pe_first_name,pe_last_name;
  exit when cemp%notfound;
  
  DBMS_OUTPUT.PUT_LINE(pe_first_name||' '||pe_last_name);
  
 end loop;
 
 --關(guān)閉光標(biāo)
 close cemp;

end;
/