PL_SQL游标的高级使用示例_东方融尚（北京）科技有限公司—

--1、将表中每个部门的工资前两名找到，插入到T1表中。

--用到的带变量的游标

CONNECT TEACH/ORACLE

DROP TABLE T1;

create table t1 as select LAST_NAME,DEPARTMENT_ID,SALARY from employees where 0=9;

declare

cursor c1(v1 t1.department_id%TYPE) IS

select last_name,DEPARTMENT_ID,salary from employees where department_id=V1 order by salary desc;

cursor c2 IS SELECT DISTINCT DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID IS NOT NULL;

BEGIN

FOR I IN C2 LOOP

FOR K IN C1(I.DEPARTMENT_ID) LOOP

INSERT INTO T1 VALUES(K.last_name,K.DEPARTMENT_ID,K.salary);

EXIT WHEN C1%ROWCOUNT>=2;

END LOOP;

END;

COMMIT;

SELECT * FROM T1;

SELECT DEPTNO,ENAME,SAL FROM (

SELECT DEPTNO,ENAME,SAL,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) RN FROM EMP)

--2、将表中重复的行找到，编上号码，保留一个，其他的删除。

--用到更新当前行的属性

conn scott/tiger

drop table t1;

create table t1 as select rowid ID,ename,sal,deptno from emp;

insert into t1 select * from t1;

commit;

update t1 set id=rowid;

commit;

ALTER TABLE T1 ADD (num number(8));

select * from t1 order by 2;

--T1

DECLARE

CURSOR C1 IS SELECT * FROM T1;

CURSOR C2 (V1 VARCHAR2) IS SELECT ROWNUM N# FROM T1 WHERE ENAME=V1 FOR UPDATE NOWAIT;

BEGIN

FOR I1 IN C1 LOOP

FOR I2 IN C2(I1.ENAME) LOOP

UPDATE T1 SET NUM=I2.N# WHERE CURRENT OF C2;

END LOOP;

commit;

END;

delete t1 where num<>1;

commit;