oracle-3

1.编写程序计算并输出1-100的和

Declare

   v_sum  integer:=0;

begin

   for i in 1..100 loop

      v_sum := v_sum+i;

   end loop;

   dbms_output.put_line(‘1~100的和为：’|| v_sum);

end;

2.分别使用显示游标和隐式游标逐行输出sott.emp表中的员工姓名个工资

1）显示游标

DECLARE

   CURSOR emp_cursor01 IS SELECT ename,sal FROM scott.emp;

   emp_record emp_cursor01%ROWTYPE;

BEGIN

   OPEN emp_cursor01 ;

   LOOP

      FETCH emp_cursor01 INTO emp_record;

      EXIT WHEN emp_cursor01%NOTFOUND;

     dbms_output.put_line('ename:'||emp_record.ename||',sal:'||emp_record.sal);

   END LOOP;

   dbms_output.put_line('row count:'||emp_cursor01%rowcount);

    CLOSE emp_cursor01;

END;

2）隐式游标

BEGIN

   FOR emp_record IN (SELECT ename,sal  FROM scott.emp)

   LOOP

     dbms_output.put_line('ename:'||emp_record.ename||',sal:'||emp_record.sal);

   END LOOP;

END;

3.创建一个表top_dogs，包含两列：name varchar2(25) 和 salary NUMBER(11,2)，用来存放员工姓名和工资

create table top_dogs

 (name varchar2(25),

  salary NUMBER(11,2));

创建一个PL/SQL块，查询出工资水平前n名的员工。具体要求如下：

（1）通过替代变量读取n的值。

（2）通过循环从emp表中获取工资在前n名的员工的姓名和工资

（3）将得到的员工姓名和工资写入top_dogs表。

（4）如果有多名员工的工资相同，则每人都占n名中的一个名额。

（5）测试特殊情况，例如n=0或者n大于员工总数。

（6）每次向top_dogs 表中写入数据前，先清空该表中的数据。

一．DECLARE

   CURSOR emp_cursor(n number) IS SELECT ename,sal FROM scott.emp

   ORDER BY sal DESC;

   v_n integer := &n;

BEGIN

   delete from top_dogs;

   FOR rec IN emp_cursor(v_n)  LOOP

      IF emp_cursor%ROWCOUNT <=v_n THEN

         insert into top_dogs  values(rec.ename,rec.sal);     

         dbms_output.put_line('ename: '||rec.ename||'  sal:'||rec.sal);

      ELSIF v_n<=0 or v_n>emp_cursor%ROWCOUNT  then

         dbms_output.put_line('error!');

      ELSE EXIT;    --退出循环

      END IF;

   END LOOP;

END;

二．

DECLARE

   CURSOR emp_cursor IS SELECT ename,sal FROM scott.emp

   ORDER BY sal DESC;

   v_n integer := &n;

BEGIN

   delete from top_dogs;

   FOR rec IN emp_cursor 

LOOP

        Exit when emp_cursor%notfound;

        Exit when emp_cursor%ROWCOUNT>v_n;

        insert into top_dogs values(rec.ename,rec.sal);     

        -- dbms_output.put_line('ename: '||rec.ename||'  sal:'||rec.sal);

      END LOOP;

END;

3、在上题的基础上，如果员工工资相同（并列第几名），需要将前n名的员工全部输出

Declare

   Cursor dept_cur is select deptno,dname from scott.dept;

   Cursor emp_cur (c_deptno scott.emp.deptno%type) is select ename,job,hiredate,sal from scott.emp where deptno=c_deptno;

   Emp_rec emp_cur%rowtype;

Begin

   For dept_rec in dept_cur

   Loop

     Open emp_cur(dept_rec.deptno);

     Loop

       Fetch emp_cur into Emp_rec;

       Exit when emp_cur%notfound;

       Dbms_output.put_line('ename:'|| Emp_rec.ename||' job:'|| Emp_rec.job||' hiredate:'|| Emp_rec.hiredate||' sal:'|| Emp_rec.sal);

     End loop;

     Close emp_cur;

   End loop;

End;

5、为emp表增加一个列stars，类型为VARCHAR2(100)。使用游标更新stars列：创建一个PL/SQL块，根据员工的工资计算他能获得的星号“*”数量，每100美元奖励一个星号，按四舍五入处理。并根据员工所获得的星号数量n，形成由n个星号组成的字符串，写入emp表的stars列。

（1）为emp表增加一个列stars，类型为VARCHAR2(100)。

Alter table scott.emp add star varchar2(100);

（2）使用游标更新stars列：

Declare

    v_stars scott.emp.stars%type;

    v_num number:=0;

    Cursor c1 is select empno,sal,stars from scott.emp;

 Begin

    For emp_rec in c1

    Loop

      v_stars:='';

      exit when c1%notfound;

      v_num:=round(emp_rec.sal/100);

    for i in 1..v_num

    loop

      v_stars:= v_stars||'*';

    end loop;

    update scott.emp set stars= v_stars where empno=emp_rec.empno;

    end loop;

    end;