根据PL/SQL之显示游标读取的几种方式文章，举例（emp表）：列举每年入职的雇员人数

写法1

DECLARE

TYPE rec_emp IS RECORD (hiredate VARCHAR2(4),

cnt PLS_INTEGER);

TYPE nested_emp IS TABLE OF rec_emp;

l_emp nested_emp;

BEGIN

SELECT * BULK COLLECT INTO l_emp

FROM (SELECT to_char(hiredate,'yyyy') AS hiredate ,COUNT(*) AS cnt

FROM emp

GROUP BY to_char(hiredate,'yyyy')

);

FOR i IN 1..l_emp.count LOOP

DBMS_OUTPUT.PUT_LINE('年份: '||l_emp(i).hiredate||' , '||'人数：'|| l_emp(i).cnt);

END LOOP;

END;

写法2

DECLARE

CURSOR cur_emp IS

SELECT to_char(hiredate,'yyyy') AS hiredate ,COUNT(*) AS cnt

FROM emp

GROUP BY to_char(hiredate,'yyyy');

l_emp cur_emp%ROWTYPE;

BEGIN

OPEN cur_emp;

LOOP

FETCH cur_emp INTO l_emp;

EXIT WHEN cur_emp%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('年份: '||l_emp.hiredate||' , '||'人数：'|| l_emp.cnt);

END LOOP;

CLOSE cur_emp;

END;

写法3

DECLARE

CURSOR cur_emp IS

SELECT to_char(hiredate,'yyyy') AS hiredate ,COUNT(*) AS cnt

FROM emp

GROUP BY to_char(hiredate,'yyyy');

BEGIN

FOR i IN cur_emp LOOP

DBMS_OUTPUT.PUT_LINE('年份: '||i.hiredate||' , '||'人数：'|| i.cnt);

END LOOP;

END;

写法4

BEGIN

FOR i IN (SELECT to_char(hiredate,'yyyy') AS hiredate ,COUNT(*) AS cnt

FROM emp

GROUP BY to_char(hiredate,'yyyy')) LOOP

DBMS_OUTPUT.PUT_LINE('年份: '||i.hiredate||' , '||'人数：'|| i.cnt);

END LOOP;

END;

PL/SQL写法比较灵活，大家可以尝试一下各种写法。

Tags：oracle for in select