网站建设
联系我们

PL_SQL游标的高级使用示例

发布于:2014-02-11 14:23来源:未知 作者:admin 点击:
--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 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;
insert into t1 select * from t1;
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;
END LOOP;
commit;
END;
/
 
delete t1 where num<>1;
commit;
 
tag标签:
------分隔线----------------------------
------分隔线----------------------------