Oracle 数据库基础学习 (八) PL/SQL综合练习
1、定义游标：列出每个员工的姓名、部门名称并编程显示第10个到第20个记录。
declare cursor zemp_cursor is (select temp.ename, temp.dname from (select e.ename, d.dname, ROWNUM rn from zemp e, zdept d where e.deptno=d.deptno(+) and ROWNUM<=20) temp where temp.rn >10) ; begin for zemp_record in zemp_cursor loop --隐式打开游标 dbms_output.put_line('ename is ' || zemp_record.ename || ' dname is ' || zemp_record.dname) ; end loop; --隐式关闭游标 end; /
2、定义游标：从雇员表中显示工资大于3000的记录，只要姓名、部门编号和工资。编程显示其中的奇数记录。
declare cursor zemp_cursor is (select ROWNUM rn, ename, deptno, sal from zemp where sal > 3000) ; begin for zemp_record in zemp_cursor loop --if mod(zemp_record.rn, 2)<>0 then if mod(zemp_cursor%rowcount, 2)<>0 then dbms_output.put_line( zemp_cursor%rowcount --zemp_record.rn ||' ename is ' || zemp_record.ename ||' deptno is ' || zemp_record.deptno ||' sal is ' || zemp_record.sal) ; end if ; end loop; end; /
3、计算下面级数当末项小于0.001时的部分和。
1/(1*2)+1/(2*3)+1/(3*4)+…+1/(n*(n+1))+ ……
declare n number :=1 ; total number :=0 ; begin loop total:=total+1/(n*(n+1)) ; exit when (1/(n*(n+1)))<0.001 ; --select sum(total+1/(n*(n+1))) into total from dual ; n:=n+1; end loop ; dbms_output.put_line('The final sum is：'|| total) ; end ; /
4、计算s=1*2+2*3+…+N*(N+1),当N=50的值。
declare n number :=1 ; total number(10,3):=0 ; begin loop total := total + n*(n+1) ; exit when n=50 ; --select sum(total+(1/((n+1)*(n+2)))) into total from dual ; n:=n+1; end loop ; dbms_output.put_line('The final sum is：'|| total) ; end ; /
5、两重循环，计算S=1!+2!+…+10!。
declare a number:=0; s number:=0; begin for i in 1..10 loop a:=1; for j in 1..i loop a:=a*j; end loop; s:=s+a; dbms_output.put_line(a); end loop; dbms_output.put_line('s='||to_char(s)); end; /
6、编程序求满足不等式 1+3^2+5^2+…+N^2>2000的最小N值。
declare n number :=1 ; total number :=0 ; begin loop total := total+power(n,2) ; n:=n+2; exit when total>2000 ; end loop ; dbms_output.put_line('The min N is：'|| n) ; end ; /
7、将雇员表中的所有工资小于3000增加400，统计出增加工资的人数及增加的工资数量。
declare cursor sal_cursor is (select sal from zemp where sal<3000) for update of sal nowait ; --锁定sal列，保证sal能够正常更新 n number ; begin select count(1) into n from zemp where sal<3000 ; if n=0 then RAISE_APPLICATION_ERROR(-20017, '数据不存在！') ; else for emp_record in sal_cursor loop update zemp set sal=emp_record.sal+400 where current of sal_cursor ; end loop ; commit; --提交释放 end if ; dbms_output.put_line('增加工资的人数：'|| n ||' 增加工资的人数：'|| 400*n) ; exception when others then dbms_output.put_line('数据输入错误') ; dbms_output.put_line('SQLCODE = '|| SQLCODE) ; dbms_output.put_line('SQLERRM = '|| SQLERRM) ; end; /
8、将雇员表中的部门编号为30的所有员工删除，统计删除的人数及删除人的平均工资。
declare v_avg number ; v_count number ; begin select avg(sal),count(empno) into v_avg,v_count from zemp where deptno=30 ; delete from zemp where deptno=30 ; dbms_output.put_line('删除的人数：'|| v_count||' 删除人员的平均工资：'|| v_avg) ; end ; /
9、触发器的定义。
CREATE OR REPLACE TRIGGER TT BEFORE DELETE OR UPDATE OR INSERT ON zemp BEGIN DBMS_OUTPUT.PUT_LINE('触发器TT'); END; /
调用： UPDATE zemp SET SAL=SAL-100 WHERE SAL>1000; 触发器TT 16 rows updated
10、从雇员表中显示工资最高的前五个人的姓名，部门和工资。
declare cursor zemp_cursor is select sal,ename,deptno from zemp order by sal desc,ename ; i number :=0 ; begin dbms_output.put_line('最高工资的5个人：') ; for zemp_xixi in zemp_cursor loop exit when i=5; dbms_output.put_line('姓名：'|| zemp_xixi.ename||' 部门：'|| zemp_xixi.deptno ||' 工资：'|| zemp_xixi.sal) ; i:=i+1; end loop ; end; /
11、编写过程：当给定的部门编号存在时，显示部门的信息。如果不存在则将该部门插入其中。
create or replace procedure show_insert_proc(v_deptno zdept.deptno%type) as v_count number ; begin select count(1) into v_count from zdept where deptno=v_deptno; if v_count=0 then dbms_output.put_line('不存在该部门！！'); insert into zdept(deptno, dname, loc) values(v_deptno,'资讯','昆山'); commit ; dbms_output.put_line('插入成功！！'); else for x in (select * from zdept where deptno=v_deptno) loop dbms_output.put_line('部门编号：'||x.deptno ||' 部门名称：'||x.dname ||' 部门位置：'||x.loc) ; end loop ; end if ; end ; /
12、编写函数：对雇员表按工资从小到大排序后的第n条到第m条记录的工资总和。
create or replace function order_zemp_fun(v_start number, v_final number) return number as v_sum number := 0; begin if v_start>v_final then dbms_output.put_line('起点数据大于终点数据！！'); return -1 ; else select sum(sal) into v_sum from (select rownum rn,sal from (select sal from zemp order by sal) where rownum<=v_final) temp where temp.rn>=v_start ; return v_sum ; end if ; end ; /
测试： declare x number; begin x:=order_zemp_fun(5, 10); dbms_output.put_line( '第五到第十条记录工资总和：'|| x) ; end ; / 第五到第十条记录工资总和：17835.38 PL/SQL procedure successfully completed
13、编写给定雇员编号从雇员表删除信息的过程。
create or replace procedure delete_empno_proc(v_empno zemp.empno%type) as v_count number ; begin select count(1) into v_count from zemp where empno=v_empno ; if v_count=0 then dbms_output.put_line('此雇员不存在！'); else delete zemp where empno=v_empno; dbms_output.put_line('此雇员成功删除！'); end if; end ; /
14、定义触发器：对表雇员表建立插入、删除、修改之前分别显示：开始插入、开始删除、开始修改。
CREATE OR REPLACE TRIGGER TT BEFORE DELETE OR UPDATE OR INSERT ON zemp BEGIN IF DELETING THEN DBMS_OUTPUT.PUT_LINE('开始删除'); elsif UPDATING THEN DBMS_OUTPUT.PUT_LINE('开始更新'); elsif INSERTING THEN DBMS_OUTPUT.PUT_LINE('开始插入'); END IF; END TT; /
15、编写过程：从雇员表中删除按工作编号从小到大排序后第N条记录。
create or replace procedure delete_num_proc(v_num number) as i number :=1 ; v_count number ; v_empno zemp.empno%type ; v_ename zemp.ename%type ; begin select count(1) into v_count from zemp ; --错误检查 if (v_num>v_count or v_num<0) then RAISE_APPLICATION_ERROR(-20017, '数据输入错误！') ; RETURN ; else --查找到员工的编号 for c1 in (select empno,ename from zemp order by empno) loop if i=v_num then v_empno:=c1.empno ; v_ename:=c1.ename ; exit ; end if; i:=i+1 ; end loop; --删除员工信息 delete from zemp where empno=v_empno ; dbms_output.put_line('员工：'|| v_ename ||' 编号：'||v_empno||' 删除成功！！') ; end if ; exception when others then dbms_output.put_line('数据输入非法！！') ; dbms_output.put_line('SQLCODE = '|| SQLCODE) ; dbms_output.put_line('SQLERRM = '|| SQLERRM) ; end ; /
