Oracle存储过程及函数的练习题

1. --存储过程、函数练习题  
2.   
3. --（1）创建一个存储过程，以员工号为参数，输出该员工的工资  
4. create or replace procedure p_sxt1(v_empno in emp.empno%type, v_sal out emp.sal%type) is  
5. begin  
6.   select sal into v_sal from emp where empno = v_empno;  
7. end;  
8. --（1）执行  
9. declare  
10.   v_empno emp.empno%type := 7369;  
11.   v_sal emp.sal%type;  
12. begin  
13.   p_sxt1(v_empno,v_sal);  
14.   dbms_output.put_line(v_empno || ' 员工的工资为：' || v_sal);  
15. end;  
16.   
17.   
18. --（2）创建一个存储过程，以员工号为参数，修改该员工的工资。若该员工属于10号部门，  
19. --则工资增加150；若属于20号部门，则工资增加200；若属于30号部门，则工资增加250；  
20. --若属于其他部门，则增加300。  
21. create or replace procedure p_sxt2(v_empno in emp.empno%type) is  
22.   v_deptno emp.deptno%type;  
23.   v_sal emp.sal%type;  
24. begin  
25.   select deptno into v_deptno from emp where empno = v_empno;  
26.   select sal into v_sal from emp where empno = v_empno;  
27.   dbms_output.put_line(v_empno || ' 的部门是 ' || v_deptno || ' 修改前的工资是 ' || v_sal);  
28.     
29.   case v_deptno  
30.   when 10 then  
31.     update emp set sal = sal + 150 where empno = v_empno;  
32.   when 20 then  
33.     update emp set sal = sal + 200 where empno = v_empno;  
34.   when 30 then  
35.     update emp set sal = sal + 250 where empno = v_empno;  
36.   else  
37.     update emp set sal = sal + 300 where empno = v_empno;  
38.   end case;  
39.     
40.   select sal into v_sal from emp where empno = v_empno;  
41.   dbms_output.put_line(v_empno || ' 的部门是 ' || v_deptno || ' 修改后的工资是 ' || v_sal);  
42.   commit;  
43. end;  
44. --（2）执行  
45. begin  
46.   p_sxt2(7369);  
47. end;  
48.   
49.   
50. --（3）创建一个存储过程，以员工号为参数，返回该员工的工作年限（以参数形式返回）。  
51. create or replace procedure p_sxt3(v_empno in emp.empno%type, v_year out number) is  
52. begin  
53.   select round((sysdate - hiredate)/365,1) into v_year from emp where empno = v_empno;  
54. end;  
55. --（3）执行  
56. declare  
57.   v_empno emp.empno%type := 7369;  
58.   v_year number;  
59. begin  
60.   p_sxt3(v_empno,v_year);  
61.   dbms_output.put_line(v_empno || ' 工作年限为 ' || v_year || '年');  
62. end;  
63.   
64.   
65. --（4）创建一个存储过程，以部门号为参数，输出入职日期最早的10个员工信息。  
66. create or replace procedure p_sxt4(v_deptno emp.deptno%type) is  
67.   cursor c_emp is select * from emp where deptno = v_deptno order by hiredate;  
68.   v_times number := 0;  
69. begin  
70.   for v_emp in c_emp loop  
71.     v_times := v_times + 1;  
72.     dbms_output.put_line(v_emp.empno || '**' || v_emp.ename || '**' || to_char(v_emp.hiredate,'yyyy-mm-dd'));  
73.     if v_times = 10 then  
74.       exit;  
75.     end if;  
76.   end loop;  
77. end;  
78. --（4）执行  
79. begin  
80.   p_sxt4(20);  
81. end;  
82.   
83.   
84. --（5）创建一个函数，以员工号为参数，返回该员工的工资。  
85. create or replace function f_sxt5(v_empno emp.empno%type) return emp.sal%type is  
86.   vr_sal emp.sal%type;  
87. begin  
88.   select sal into vr_sal from emp where empno = v_empno;  
89.   return vr_sal;  
90. end;  
91. --(5)执行  
92. select f_sxt5(7369)||'元' 工资 from dual;  
93.   
94.   
95. --（6）创建一个函数，以部门号为参数，返回该部门的平均工资。  
96. create or replace function f_sxt6(v_deptno emp.deptno%type) return emp.sal%type is  
97.   vr_sal emp.sal%type;  
98. begin  
99.   select avg(sal) into vr_sal from emp where deptno = v_deptno;  
100.   return vr_sal;  
101. end;  
102. --（6）执行  
103. select f_sxt6(20) 部门平均工资 from dual;  
104.   
105.   
106. --（7）创建一个函数，以员工号为参数，返回该员工所在的部门的平均工资。  
107. create or replace function f_sxt7(v_empno emp.empno%type) return emp.sal%type is  
108.   vr_sal emp.sal%type;  
109. begin  
110.   select avg(sal) into vr_sal from emp where deptno = (select deptno from emp where empno = v_empno);  
111.   return vr_sal;  
112. end;  
113. --（7）执行  
114. select  f_sxt7(7369) from dual;  
115.   
116.   
117. --（8）创建一个存储过程，以员工号和部门号作为参数，修改员工所在的部门为所输入的部门号。  
118. --如果修改成功，则显示“员工由……号部门调入调入……号部门”；如果不存在该员工，则显示  
119. --“员工号不存在，请输入正确的员工号。”；如果不存在该部门，则显示  
120. --“该部门不存在，请输入正确的部门号。”。  
121. create or replace procedure p_sxt14(v_empno in emp.empno%type, v_deptno in emp.deptno%type) is  
122.   vt_empno number := 0;  
123.   vt_deptno number := 0;  
124.   vm_deptno emp.deptno%type;  
125. begin  
126.   select count(*) into vt_empno from emp where empno = v_empno;  
127.   select deptno into vm_deptno from emp where empno = v_empno;  
128.   select count(distinct deptno) into vt_deptno from emp where deptno = v_deptno;  
129.     
130.   if vt_empno = 0 then  
131.     dbms_output.put_line('员工号不存在，请输入正确的员工号。');  
132.   end if;  
133.   if vt_deptno = 0 then  
134.     dbms_output.put_line('该部门不存在，请输入正确的部门号。');  
135.   end if;  
136.   
137.   if vt_empno = 1 and vt_deptno = 1 then  
138.     dbms_output.put_line('员工由 ' || vm_deptno || ' 号部门调入调入 ' || v_deptno || ' 号部门');  
139.     update emp set deptno = v_deptno where empno = v_empno;  
140.     commit;  
141.   end if;  
142. end;  
143. --（8）执行  
144. begin  
145.   p_sxt14(7369,30);  
146. end;  
147.   
148.   
149. --（9）创建一个存储过程，以一个整数为参数，输入工资最高的前几个（参数值）员工的信息。  
150. create or replace procedure p_sxt15(v_number in number) is  
151.   cursor c_emp is select * from emp order by sal desc;  
152.   v_n number := 0;  
153. begin  
154.   for v_emp in c_emp loop  
155.     v_n := v_n + 1;  
156.     dbms_output.put_line(v_n || ' - ' || v_emp.ename || ' - ' || v_emp.sal);  
157.     if v_n = v_number then  
158.       exit;  
159.     end if;  
160.   end loop;  
161. end;  
162. --（9）执行  
163. begin  
164.   p_sxt15(5);  
165. end;  
166.   
167.   
168. --（10）创建一个存储过程，以两个整数为参数，输出工资排序在两个参数之间的员工信息。  
169. create or replace procedure p_sxt16(v_up in number,v_down in number) is  
170.   cursor c_emp is select * from emp order by sal desc;  
171.   v_n number := 0;  
172. begin  
173.   for v_emp in c_emp loop  
174.     v_n := v_n + 1;  
175.     if v_n >= v_up and v_n <= v_down then  
176.       dbms_output.put_line(v_n || ' - ' || v_emp.ename || ' - ' || v_emp.sal);  
177.     end if;  
178.   end loop;  
179. end;  
180. --（10）执行  
181. begin  
182.   p_sxt16(2,3);  
183. end;