你的位置:首页 > 数据库

[数据库]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 TTBEFORE DELETE OR UPDATE OR INSERT ON zempBEGIN   DBMS_OUTPUT.PUT_LINE('触发器TT');END;/
调用:UPDATE zemp SET SAL=SAL-100 WHERE SAL>1000;触发器TT16 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.38PL/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 TTBEFORE DELETE OR UPDATE OR INSERTON zempBEGIN    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 ;/