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

男娘i 2022-05-30 06:09 218阅读 0赞

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;