PL-SQL经典试题

10. 综合使用 if, while 语句, 打印 1 - 100 之间的所有素数

(素数: 有且仅用两个正约数的整数, 2, 3, 5, 7, 11, 13, ...).

declare

  v_flag number(1):=1;

  v_i number(3):=2;

  v_j number(2):=2;

begin

 

  while (v_i<=100) loop

        while v_j <= sqrt(v_i) loop

              if (mod(v_i,v_j)=0) then v_flag:= 0;end if;

              v_j :=v_j +1;

        end loop;

        if(v_flag=1) then dbms_output.put_line(v_i);end if;

 

        v_flag :=1;

        v_j := 2;

        v_i :=v_i +1;

   end loop;

 

end;

 

 

(法二)使用for循环实现1-100之间的素数的输出

declare

  --标记值, 若为 1 则是素数, 否则不是

  v_flag number(1) := 0;

begin

   for i in 2 .. 100 loop

 

       v_flag := 1;     

         

       for j in 2 .. sqrt(i) loop

           if i mod j = 0 then

              v_flag := 0;

           end if;        

       end loop;

       

       if v_flag = 1 then

           dbms_output.put_line(i);

       end if;

       

   end loop;

end;

 

11. 使用 goto

declare

  --标记值, 若为 1 则是素数, 否则不是

  v_flag number(1) := 0;

begin

   for i in 2 .. 100 loop

       v_flag := 1;     

         

       for j in 2 .. sqrt(i) loop

           if i mod j = 0 then

              v_flag := 0;

              goto label;

           end if;        

       end loop;

       

       <<label>>

       if v_flag = 1 then

           dbms_output.put_line(i);

       end if;

       

   end loop;

end;

 

11+.打印1——100的自然数，当打印到50时，跳出循环，输出“打印结束”

(方法一)

begin

  for i in 1..100 loop

      dbms_output.put_line(i);

      if(i = 50) then

      goto label;

      end if;

  end loop;

      

      <<label>>

      dbms_output.put_line('打印结束');

 

end;

(方法二)

begin

  for i in 1..100 loop

      dbms_output.put_line(i);

      if(i mod 50 = 0) then dbms_output.put_line('打印结束');

      exit;

      end if;

  end loop;

end;

******************************************************************************游标的使用

*******************************************************************************12.1 使用游标

要求: 打印出 80 部门的所有的员工的工资:salary: xxx

 

declare

  --1. 定义游标

  cursor salary_cursor is select salary from employees where department_id = 80;

  v_salary employees.salary%type;

begin

 --2. 打开游标

 open salary_cursor;

 

 --3. 提取游标

 fetch salary_cursor into v_salary;

 

 --4. 对游标进行循环操作: 判断游标中是否有下一条记录

while salary_cursor%found loop

      dbms_output.put_line('salary: ' || v_salary);

      fetch salary_cursor into v_salary;

end loop;  

 

 --5. 关闭游标

 close  salary_cursor;

end;

 

12.2 使用游标

要求: 打印出 80 部门的所有的员工的工资: Xxx 's salary is: xxx

 

declare

  cursor sal_cursor is select salary ,last_name from employees where department_id = 80;

  v_sal number(10);

  v_name varchar2(20);

begin

  open sal_cursor;

  

  fetch sal_cursor into v_sal,v_name;

  

  while sal_cursor%found loop

        dbms_output.put_line(v_name||'`s salary is '||v_sal);

        fetch sal_cursor into v_sal,v_name;

  end loop;

  

  close sal_cursor;

  

end;

 

13. 使用游标的练习:

打印出 manager_id 为 100 的员工的 last_name, email, salary 信息(使用游标, 记录类型)

 

declare  

           --声明游标    

           cursor emp_cursor is select last_name, email, salary from employees where manager_id = 100;

           

           --声明记录类型

           type emp_record is record(

                name employees.last_name%type,

                email employees.email%type,

                salary employees.salary%type

           );

           

           -- 声明记录类型的变量

           v_emp_record emp_record;

begin

           --打开游标

           open emp_cursor;

           

           --提取游标

           fetch emp_cursor into v_emp_record;

           

           --对游标进行循环操作

           while emp_cursor%found loop

                  dbms_output.put_line(v_emp_record.name || ', ' || v_emp_record.email || ', ' || v_emp_record.salary );                

                  fetch emp_cursor into v_emp_record;

           end loop;

           

           --关闭游标

           close emp_cursor;

end;

(法二：使用for循环)

declare

   

      cursor emp_cursor is

      select last_name,email,salary

      from employees

      where manager_id = 100;

 

begin

 

 

      for v_emp_record in emp_cursor loop

          dbms_output.put_line(v_emp_record.last_name||','||v_emp_record.email||','||v_emp_record.salary);

      end loop;

end;

 

14. 利用游标, 调整公司中员工的工资:

    

    工资范围       调整基数

    0 - 5000       5%

    5000 - 10000   3%

    10000 - 15000  2%

    15000 -        1%

 

declare

    --定义游标

    cursor emp_sal_cursor is select salary, employee_id from employees;

    

    --定义基数变量

    temp number(4, 2);

    

    --定义存放游标值的变量

    v_sal employees.salary%type;

    v_id employees.employee_id%type;

begin

    --打开游标

    open emp_sal_cursor;

    

    --提取游标

    fetch emp_sal_cursor into v_sal, v_id;

    

    --处理游标的循环操作

    while emp_sal_cursor%found loop

          --判断员工的工资, 执行 update 操作

          --dbms_output.put_line(v_id || ': ' || v_sal);

            

          if v_sal <= 5000 then

             temp := 0.05;

          elsif v_sal<= 10000 then

             temp := 0.03;   

          elsif v_sal <= 15000 then

             temp := 0.02;

          else

             temp := 0.01;

          end if;

          

          --dbms_output.put_line(v_id || ': ' || v_sal || ', ' || temp);

          update employees set salary = salary * (1 + temp) where employee_id = v_id;

                  

          fetch emp_sal_cursor into v_sal, v_id;

    end loop;

    --关闭游标

    close emp_sal_cursor;

end;

 

使用SQL中的 decode 函数

 

update employees set salary = salary * (1 + (decode(trunc(salary/5000), 0, 0.05,

                                                          1, 0.03,

                                                          2, 0.02,

                              

                            0.01)))

 

 

本教程由尚硅谷教育大数据研究院出品，如需转载请注明来源。