Oracle数据库基础学习(八)PL/SQL综合练习小猩

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 ;
/