Orace——PL/SQL基础练习题

1. 接收部门编号，显示部门名和地理位置

declare	
	sno dept.deptno%type;
	dname dept.dname%type;
	loc dept.loc%type;
begin
	sno:=&no;
	select dname,loc into dname,loc from dept where deptno=sno;
	dbms_output.put_line(dname||'     '||loc);
	exception
	when no_data_found then
	dbms_output.put_line('not find data');
end;

2. 接收雇员号，显示该雇员的工资和提成，没有提成的用 0 替代。（用%type 实现）

declare
	sno emp.empno%type;
	sal emp.sal%type;
	comm emp.comm%type;
begin
	sno:=&no;
	select sal,nvl(comm,0) into sal,comm from emp where empno=sno;
	dbms_output.put_line(sal||' '||comm);
	exception
	when no_data_found then
	dbms_output.put_line('not find data');
end;

3、接收雇员号，显示该雇员的所有信息，没有提成的用 0 替代。（用%rowtype 实现）

declare
	sno emp.empno%type;
	e1 emp%rowtype;
begin
	sno:=&no;
	select * into e1 from emp where empno=sno;
	dbms_output.put_line(e1.empno||' '||e1.ename||' '||e1.job||' '||e1.mgr||'     '||e1.hiredate||' '||e1.sal||''||nvl(e1.comm,0)||' '||e1.deptno);
	exception
	when no_data_found then
	dbms_output.put_line('not find date');
end;

4. 接收一个雇员名，判断他的 job，根据 job 不同，为他增加相应的 sal（用 if-elsif 实现）

declare
	sname emp.ename%type;
	ejob emp.job%type;
	esal emp.sal%type;
begin
	sname:='&no';
	select job,sal into ejob,esal from emp where ename=sname;
	if(ejob='ANALYST') then
	esal:=esal+esal*0.1;
	elsif(ejob='CLERK') then
	esal:=esal+esal*0.2;
	elsif(ejob='MANAGER') then
	esal:=esal+esal*0.3;
	elsif(ejob='PRESIDENT') then
	esal:=esal+esal*0.4;
	elsif(ejob='SALESMAN') then
	esal:=esal+esal*0.5;
	end if;
	update emp set sal=esal where ename= sname;
	exception
	WHEN NO_data_found then
	dbms_output.put_line('not find data');
end;

5. 用while循环结构，为 dept 表增加 50-90 这些部门

declare
	no number;
begin
	no :=50;
	while (no <= 90) 
	loop
	insert into dept(deptno) values(no);
	no := no+10;
	end loop;	
end;

6. 接收一个雇员名，显示该雇员的所有内容（用%rowtype 实现），当没有这个雇员时（no_data_found），用异常来显示错误提示

declare
	name emp.ename%type;
	e1 emp%rowtype;
begin
	name:=&name;
	select * into e1 from emp where ename=name;
	dbms_output.put_line(e1.empno||' '||e1.job||' '||e1.mgr||'  '||e1.hiredate||' '||e1.sal||' '||e1.comm||'  '||e1.deptno);
	exception
	when no_data_found then
	dbms_output.put_line('没有这个雇员');
end;
/

7. 编写一个 PL/SQL 程序块以计算某个雇员的年度薪水总额

declare
	name emp.ename%type;
	sal number;
begin
	name := &name;
	select (sal*12) into sal from emp where ename = name;
	dbms_output.put_line('薪水'||sal);
	exception
	when no_data_found then
	dbms_output.put_line('没有这个雇员');
end;
/

8. 编写一个 PL/SQL 程序块以向 emp 表添加 10个 新雇员编号（7901-7910）

declare
	eno emp.empno%type;
	begin
	eno := 7901;
	loop
	insert into emp(empno) values(eno);
	exit when eno = 7910;
	eno := eno+1;
	end loop;
end;
/

9. 接受 2 个数相除，并显示结果，如果除数为 0，则显示错误提示;

declare
	a number;
	b number;
begin
	a := &a;
	b := &b;
	dbms_output.put_line(a||'除以'||b||'='||a/b);
	exception
	when zero_divide then
	dbms_output.put_line('被除数不能为 0！');
end;
/

10. 输入一个雇员编号，根据它所在的部门涨工资，规则：

• 10 部门上涨 10%
• 20 部门上涨 20%
• 30 部门上涨 30%
所有部门的上涨工资，最不能超过 5000，如果超过 5000，则工资就为 5000。

declare
	eno emp.empno%TYPE;
	dno emp.deptno%TYPE;
	esal emp.sal%TYPE;
begin
	eno:=&no;
	select deptno,sal into dno,esal from emp where empno=eno;
	if dno=10 then
	esal := esal + esal*0.1;
	elsif dno=20 then
	esal := esal + esal*0.2;
	elsif dno=30 then
	esal := esal + esal*0.3;
	end if;
	if esal>5000 then
	esal:=5000;
	end if;
end;


update emp set sal=esal where empno=eno;

11.编写一个PL/SQL程序块，对所有雇员按他们的基本工资的20%为他们加薪，如果增加的薪水大于300就取消加薪。输出更新前后的工资。

方案一：

declare
	cursor emp_cursor is select empno,sal from emp for update;
	v_sal emp.sal% TYPE;
	v_no emp.empno% TYPE;
begin
    open emp_cursor;
    loop
    fetch emp_cursor into v_no, v_sal;
    if v_sal*1.2<300 then
    update emp set sal=sal*1.2 where current of emp_cursor;
    dbms_output.put_line('员工编号为：'||v_no||'的员工，更新前：'||v_sal||'更新后：'||v_sal*1.2);
    else
    dbms_output.put_line('员工编号为：'||v_no||'的员工取消加薪');
	end if;
    exit when emp_cursor%notfound;
    END LOOP;
    close emp_cursor;
end;

方案二：

declare
	cursor mycur is select empno,sal from emp for update;
	my mycur%rowtype;
begin
	open mycur;
	loop
	    fetch mycur into my;
	    if my.sal*1.2<=1500 then
      	update emp set sal=sal*1.2 where current of mycur;
    	dbms_output.put_line('员工编号为：' || my.empno || '的员工更新前工资为：' || my.sal || '更新后的工资为：' || my.sal*1.2);
	    end if;
	    exit when mycur%notfound;
	end loop;
	close mycur;
end;

方案三：

declare
	cursor mycur is select empno,sal from emp;
	my mycur%rowtype;
begin
	open mycur;
	loop
	    fetch mycur into my;
	    if my.sal*1.2<=300 then 
	        update emp set sal=sal*1.2 where empno=my.empno;
	        dbms_output.put_line('员工编号为：'||my.empno||'的员工，更新前：'||my.sal||'更新后：'||my.sal*1.2);
	    else	
    	        dbms_output.put_line('员工编号为：'||my.empno||'的员工取消加薪');
	    end if;
	    exit when mycur%notfound;
	end loop;
end;

12. 将每位员工工作了多少年零多少月零多少天输出来。

方案一：

declare
  cursor mycur is select ename,hiredate,to_number(to_char(sysdate,'yyyy'))-to_number(to_char(hiredate,'yyyy')) years,to_number(to_char(sysdate,'mm'))-to_number(to_char(hiredate,'mm')) months,to_number(to_char(sysdate,'dd'))-to_number(to_char(hiredate,'dd')) days from emp;
  my mycur%rowtype;
begin
  open mycur;
  loop
    fetch mycur into my;
    exit when mycur %notfound;
    dbms_output.put_line(my.years||' '||my.months||' '||my.days);
  end loop;
  close mycur;
end;

方案二：

declare 
	cursor mycur is select ename,trunc(month_between(sysdate,hiredate)/12) v_year,mod(floor(months_between(sysdate,hiredate)),12) v_month,mod(floor(sysdate-hiredate),30) v_day;
	my mycur%rowtype;
begin
	open mycur;
	loop
	    fetch mycur into my;
	    dbms_output.put_line(my.ename || '工作了' || my.v_year || '年' || my.v_month || '月' || month.v_day || '天'); 	
	    exit when mycur%notfound;
	end loop;
	close mycur;
end;

方案三：

declare 
	cursor mycur is select ename,trunc(month_between(sysdate,hiredate)/12) v_year,trunc(mod(month_between(sysdate,hiredate),12)) v_month,trunc(mod(mod(sysdate - hiredate, 365), 30)) v_day; --30，原本写的12，应该不对
	my mycur%rowtype;
begin
	open mycur;
	loop
	    fetch mycur into my;
	    dbms_output.put_line(my.ename || '工作了' || my.v_year || '年' || my.v_month || '月' || month.v_day || '天'); 	
	    exit when mycur%notfound;
	end loop;
	close mycur;
end;

方案四：

declare 
	cursor mycur is select ename,trunc(month_between(sysdate,hiredate)/12) v_year,trunc(mod(month_between(sysdate,hiredate),12)) v_month,round(mod((mod(months_between(sysdate,hiredate),12)*30),30)) v_day; 
	my mycur%rowtype;
begin
	open mycur;
	loop
	    fetch mycur into my;
	    dbms_output.put_line(my.ename || '工作了' || my.v_year || '年' || my.v_month || '月' || month.v_day || '天'); 	
	    exit when mycur%notfound;
	end loop;
	close mycur;
end;

方案五：（只提供查询的sql语句）

select ename,hiredate, trunc(months_between(sysdate,hiredate)/12) years ,
trunc(mod(months_between(sysdate,hiredate),12)) months,
trunc(sysdate-add_months(hiredate,months_between(sysdate,hiredate)))days
from emp;

13. 输入部门编号，按照下列加薪比例执行（用case实现），并将更新前后的数据输出。

deptno	raise(%)
10	5%
20	10%
30	15%
40	20%

方案一：

declare 
	dno emp.deptno%type := &dno;
	cursor mycur is select empno,ename,sal,deptno from emp where deptno=dno;
	my mycur%rowtype;
	v_sal emp.sal%type;
begin
	open mycur;
	loop
	    fetch mycur into my;
	    case
	    	when my.deptno=10 then
			v_sal := my.sal*1.05;
			when my.deptno=20 then
			v_sal := my.sal*1.1;
			when my.deptno=30 then
			v_sal := my.sal*1.15;
			when my.deptno=40 then
			v_sal := my.sal*1.2;
	    end case;
	    update emp set sal=v_sal where empno=my.empno;
	    dbms_output.put_line(my.ename || '涨前工资为：' || my.sal || '涨后工资为：' || v_sal);
	end loop;
	close mycur;
end;	 

方案二：

declare 
	dno emp.deptno%type;
	cursor mycur is select empno,ename,sal,deptno from emp where deptno=dno;
	v_sal emp.sal%type;
	--my mycursor%rowtype;
begin
	for my in mycur
	loop
		case
		when my.deptno=10 then
		v_sal := my.sal*1.05;
		when my.deptno=20 then
		v_sal := my.sal*1.1;
		when my.deptno=30 then
		v_sal := my.sal*1.15;
		when my.deptno=40 then
		v_sal := my.sal*1.2;
		end case;
	update emp set sal=v_sal where eno=my.empno;
	dbms_output.put_line(my.ename || '涨前工资为：' || my.sal || '涨后工资为：' || v_sal);
	end loop;
end;

方案三：

declare
	cursor mycur(dno emp.deptno%type) is select empno,ename,sal,deptno from emp for update;
	v_sal emp.sal%type;
begin
	for my in mycur(&dno) loop
		case
		when my.deptno=10 then
		v_sal := my.sal*1.05;
		when my.deptno=20 then
		v_sal := my.sal*1.1;
		when my.deptno=30 then
		v_sal := my.sal*1.15;
		when my.deptno=40 then
		v_sal := my.sal*1.2;
		end case;
	update emp set sal=v_sal where current of mycur;
	dbms_output.put_line(my.ename || '涨前工资为：' || my.sal || '涨后工资为：' || v_sal);
	end loop;
end;

14. 对每位员工的薪水进行判断，如果该员工薪水高于其所在部门的平均薪水，则将其薪水减50元，输出修改前后的薪水、员工姓名和所在部门编号。

declare
	cursor mycur is select empno,ename,deptno,sal from emp;
	avgsal emp.sal%type;
begin
	for my in mycur loop
	select avg(sal) into avgsal from emp where deptno=my.deptno;
	if my.sal>avgsal then
	update emp set sal=sal-50 where empno=my.empno;
	dbms_output.put_line(my.ename ||'修改前的薪水为：' || (my.sal+50) || '修改后的薪水为：' || my.sal || '所在部门编号为：' || my.deptno);
	end if;
	end loop;
end;

(my.sal+50)
注意在dbms输出语句中输出这种加法运算的时候，记得加上括号

15. 董事长涨1000元工资，经理涨800元，普通职工涨500元。

declare
	cursor mycur if select empno,job,sal from emp;
	vsal emp.sal%type;
begin
	for my in mycur loop
	if my.job='PROCIDENT' then
	vsal := my.sal+1000;
	elsif my.job='MANAGER' then
	vsal := my.sal+800;
	else 
	vsal := my.sal+500;
	end if;
	update emp set sal=vsal where empno=my.empno;
	end loop;
end;

16. 使用游标来修改emp表中雇员的工资，使得工资等于或超过1500的雇员，工资增长20%，低于1500的增长30%。要求使用for update和where current of

方案一：

declare 
	cursor mycur is select empno,sal from emp for update;
begin
	for my in mycur loop
	if my.sal>=1500 then
	update emp set sal=sal*1.2 where current of mycur;
	else
	update emp set sal=sal*1.3 where current of mycur;
	end if;
	end loop;
end;

方案二：

declare 
	cursor mycur is select empno,sal from emp;
	my mycur%rowtype;
begin
	open mycur;
	loop
		fetch mycur into my;
		if my.sal>=5000;
		update emp set sal=sal*1.2 where empno=my.empno;
		else
		update emp set sal=sal*1.3 where empno=my.empno;
		end if;
		exit when mycur%notfound;
	end loop;
	close mycur;
end;

17. 使用游标从dept表中依次取出部门的编号和名称，将这两个参数传递到另一个游标中。取出emp表中工作在该部门的雇员姓名、工作、雇佣日期和工资信息。

解答：

declare
	cursor mycur is select deptno,dname from dept;
	my mycur%rowtype;

	cursor mycur2(dno emp.deptno%type) is select ename,job,hiredate,sal from emp where deptno = dno;
	--my2 mycur2%rowtype;
begin
	open mycur;
	loop
	fetch mycur into my;
	exit when mycur%notfound;
	dbms_output.put_line('部门编号：' || my.deptno || '部门名称：' || my.dname);
	for my2 in mycur2(my.deptno) loop
	dbms_output.put_line('姓名：' || my2.ename || '工作：' || my2.job || '雇佣日期：' || my2.hiredate || '工资：' || my2.sal);
	end loop;
	end loop;
	close mycur;
end; 
	

18. 根据员工编号对emp表修改员工的奖金，当员工原本有奖金，则再增加100元，如果原来没有奖金，则抛出异常，说明奖金为空。

DECLARE
 	  v_comm emp.comm% TYPE;
	  v_no emp.empno% TYPE:=&no;
 	  e_comm_null EXCEPTION; --声明异常
BEGIN	
 	  select comm into v_comm from emp where empno=v_no;
 	  if v_comm is null THEN
  	  raise e_comm_null;
	  else
 	  update emp set comm=comm+100 where empno=v_no;
	  end if;
	  exception
      when e_comm_null THEN
	  dbms_output.put_line('该员工奖金为空');
  　  when no_data_found THEN
      dbms_output.put_line('没有这个员工');
END;

19. 查询雇员表中部门编号为50的员工姓名。如果没有员工，自定义异常，输出无员工。

set serveroutput on
declare
  	cursor c1 is select ename from emp where deptno=50;
 	v_name emp.ename%type;
  	no_data EXCEPTION;
begin
  	open c1;
  	fetch c1 into v_name;
  	if c1%notfound then --v_name is null
    	raise no_data;
  	else
    	dbms_output.put_line(v_name);
  	end if;
  	end loop;
  	close c1;
    EXCEPTION
    when no_data then
      dbms_output.put_line('没有员工');
end;

20. 使用游标 和 loop 循环来显示所有部门的名称

declare
	cursor mycur is select dname from dept;
	--my mycur%rowtype;
	vname dept.dname%type;
begin
	open mycur;
	loop
		fetch mycur into vname;
		exit when mycur%notfound;
		dbms_output.put_line(vname);
	end loop;
	close mycur;
	exception
	when no_data_found then
	dbms_output.put_line('没有部门，查询失败');
end;

21. 使用游标 和 loop 循环来显示所有部门的的地理位置（用%found 属性）

declare 
	cursor mycur is select deptno,loc from dept;
	my mycur%rowtype;
begin
	open mycur;
	loop
		fetch mycur into my;
		exit when !mycur%found;
		dbms_output.put_line(my.deptno || '部门的地理位置为：' || my.loc);
	end loop;
	close mycur;
	exception
	when no_data_found then
	dbms_output.put_line('没有记录，查询失败');	
end;

22. 接收用户输入的部门编号，用 for 循环和游标，打印出此部门的所有雇员的所有信息

declare
	cursor mycur(dno emp.deptno%type) is select * from emp where deptno=dno;
	--my mycur%rowtype;
begin
	for my in mycur loop
	dbms_output.put_line(my.empno || '   ' || my.ename || '   ' || my.deptno);	
	end loop;
	exception
	when no_data_found then
	dbms_output.put_line('没有记录，查询失败');	
end;
	

23. 向游标传递一个工种，显示此工种的所有雇员的所有信息

declare
	cursor mycur(vjob emp.job%type) is select * from emp where job=vjob;
begin
	for my in mycur loop
	dbms_output.put_line(my.empno || '   ' || my.ename || '   ' || my.job);
	end loop;
	exception
	when no_data_found then
	dbms_output.put_line('没有记录，查询失败');	
end;
	

24. 编写一个 PL/SQL 程序块，对名字以‘A’或‘S’开始的所有雇员按他们的基本薪水的10%给他们加薪

declare
	cursor mycur is select empno,ename,sal from emp where enmae like 'A%' or 'S%';
begin
	for my in mycur loop
	update emp set sal=sal*1.1 where empno=my.empno;
	end loop;
	exception
	when no_data_found then
	dbms_output.put_line('没有记录，查询失败');	
end;

25. 编写一个 PL/SQL 程序块，对所有的 salesman 增加佣金 500

declare
	cursor mycur is select empno,sal from emp where job=salesman;
begin
	for my in mycur loop
	update emp set sal=sal+500 where empno=my.empno;
	end loop;
	exception
	when no_data_found then
	dbms_output.put_line('没有记录，查询失败');	
end;

26. 编写一个 PL/SQL 程序块，对所有雇员按他们的基本薪水的 20%为他们加薪，如果增加的薪水大于 300 就取消加薪

declare 
	cursor mycur is select empno,sal from emp;
begin
	for my in mycur loop
	if my.sal*1.2>300 then
	dbms_output.put_line('薪水大于 300 就取消加薪');	
	else
	update emp set sal=sal*1.2 where empno=my.empno;
	end if;
	end loop;
	exception
	when no_data_found then
	dbms_output.put_line('没有记录，查询失败');	
end;