oracle PL/SQL习题

--1，写匿名块，输入三角形三条边的长度。在控制台打印三角形的面积。
declare
  v_a number(8,2):=&输入第一条边;
  v_b number(8,2):=&输入第二条边;
  v_c number(8,2):=&输入第三条边;
  v_p number(8,2);
  v_if number(20,2);
  v_area number(8,2);


begin
  v_p:=(v_a+v_b+v_c)/2;
  v_if:=v_p*((v_p-v_a)*(v_p-v_b)*(v_p-v_c));
 if v_if>0 then
   v_area:=sqrt(v_if);
   dbms_output.put_line('三角形的面积是：'||v_area);
  else
     dbms_output.put_line('你输入的三角形不存在');
   end if;
end;


--2，输入部门编号，在控制台打印这个部门的名称，总人数，平均工资（基本工资+奖金）
declare
   v_deptno dept.deptno%type:=&请输入部门编号;
   v_dname dept.dname%type;
   v_total dept.deptno%type;
   v_avg emp.sal%type;
begin
  select dname,count(e.empno),avg(sal+nvl(comm,0))into v_dname,v_total,v_avg from emp e 
  inner join dept d on e.deptno=d.deptno where
  e.deptno=v_deptno  group by d.dname;
  dbms_output.put_line('部门名称为'||v_dname||'部门总人数为'||v_total||'人，平均工资为'||v_avg||'元');
exception
  when no_data_found then
     dbms_output.put_line('您输入的部门编号不存在');

end;


--3，编写一个PL/SQL块，输出所有员工的员工姓名、员工号、工资和部门号
declare
  cursor c is select * from emp;
  v_emp emp%rowtype;
  v_total number(8,2);
  pnum number:=1;
begin
  open c;
  
  select count(*) into v_total from emp;
  loop
    fetch c  into v_emp;
    exit when pnum>v_total;
  
  dbms_output.put_line('员工姓名:'||v_emp.ename||'员工号：'||v_emp.empno||'工资'||v_emp.sal||'部门号'||v_emp.deptno);

         pnum:=pnum+1;
     end loop;
     close c;
end;
--网上写法
declare
cursor c is select * from emp;
begin
for v_emp in c loop
dbms_output.put_line(v_emp.empno ||' '||v_emp.ename ||' '||v_emp.sal ||' '||v_emp.deptno);
end loop ;
end ;

--4，编写一个PL/SQL块,输出所有比本部门平均工资高的员工信息。

declare 
v_emp emp%rowtype; 
cursor a is select * from emp a where sal >(select avg(sal) from emp b inner join dept d on d.deptno=b.deptno );
begin 
      open a;
     loop 
     fetch a into v_emp;
     exit when a%notfound;
     dbms_output.put_line('名字'||v_emp.ename||'员工编号'||v_emp.empno||'员工薪资'||v_emp.sal||'员工部门编号'||v_emp.deptno); 
 end loop; 
 close a;
end;
 

--5，编写一个PL/SQL块,输出所有员工及其部门领导的姓名、员工号及部门号。
declare 
type emp_record_type is record(
     ename emp.ename%type,
     mname emp.ename%type,
     empno emp.empno%type,
     deptno emp.deptno%type
);
type emp_table_type is table of emp_record_type
index by binary_integer;
 
v_emp emp_table_type;
begin
  select ename,nvl((select e2.ename from emp e2 where e1.mgr=e2.empno),'无'),empno,deptno 
  bulk collect into v_emp from emp e1;
  
  for v_i in 1..v_emp.count
    loop
      dbms_output.put_line('员工姓名：'||v_emp(v_i).ename||',部门领导姓名：'||v_emp(v_i).mname
      ||',员工号：'||v_emp(v_i).empno||',部门编号：'||v_emp(v_i).deptno);
    end loop;
end;
 

--6，查询姓为“SMITH”的员工信息，并输出其员工号、姓名、工资、部门号。如果该员工不存在，则插入一条新记录，员工号为2012，员工姓名为“Smith”,工资为7500元，入职日期为“2002年3月5日”，部门号为40.如果存在多个名“Smith”的员工，则输出所有名为“Smith”的员工号、姓名、工资、入职日期、部门号L。
declare
 v_emp emp%rowtype;
begin
      select * into v_emp from emp where ename='SMITH';
      dbms_output.put_line('员工号'||v_emp.empno||'姓名'||v_emp.ename||'工资'||v_emp.sal||'部门号'||v_emp.deptno);
exception
  when no_data_found then
  insert into emp(empno,ename,sal,hiredate,deptno)
  values(2012,'Smith',7500,'5-3月-2012',40);
  when too_many_rows then
  for v_emp in (select * from emp where ename='SMITH')

   loop 
    dbms_output.put_line('员工号'||v_emp.empno||'姓名'||v_emp.ename||'工资'||v_emp.sal||'入职日期'||v_emp.hiredate||'部门号'||v_emp.deptno);
    end loop;
    end;
    
    
--7，输入员工编号，根据员工的入职时间修改发放奖金类，大于等于6年的奖金为2000，小于6年的奖金是1500
declare
v_now varchar2(100);
v_hire varchar2(100);
v_year varchar2(100);
begin
  select sysdate into v_now from dual;
  select hiredate into v_hire from emp where empno=&no;
  
  select months_between(v_now,v_hire) into v_year from dual;
  v_year:=v_year/12;
  if v_year>=6 then
    update emp set comm=1500 where empno=v_empno;
  else
    update emp set comm=2000 where empno=v_empno;
  end if;
end;


--8，分别用loop ，while，for循环在控制台输出1到100的自然数

declare
v_i number(8):=1;
begin
  loop
      if v_i>100 then
      exit;
      end if;
    dbms_output.put_line(v_i);
    v_i:=v_i+1;
  end loop;
end;


declare
v_i number(8):=1;
begin
  while v_i<100
  loop 
    dbms_output.put_line(v_i);
    v_i:=v_i+1;
  end loop;
end;


declare

begin
  for v_i in 1..100
  loop
    dbms_output.put_line(v_i);
  end loop;
end;

--9，在控制台输出1到100以内的素数。
declare
 v_number number;
 v_temp number:=1;
begin 
 for v_number in 1..100
   loop
     v_temp:=1;
     while v_temp<v_number
       loop
         v_temp:= v_temp+1;
           if v_temp=v_number then
            dbms_output.put_line(v_number);
            end if;
               if mod(v_number,v_temp)=0 then 
               exit;
               end if;
        end loop;
    end loop;
end;