1 --创建一个函数,用来根据部门编号返回调薪幅度
2 create or replace function get_ratio_by_dept(deptno varchar2)
3 return number is
4 n_salaryratio number(10,2); --调薪比率返回值变量
5 begin
6 case deptno
7 when 10 then
8 n_salaryratio:=1.09;
9 when 20 then
10 n_salaryratio:=1.11;
11 when 30 then
12 n_salaryratio:=1.18;
13 else
14 n_salaryratio:=0;
15 end case;
16 return n_salaryratio;
17 end;
18
19 begin
20 dbms_output.put_line(get_ratio_by_dept(20));
21 end;
22
23 --创建一个存储过程,用来实现加薪,它将调用get_ratio_by_dept来获取加薪幅度
24
25 create or replace procedure raise_salary(p_empno number)
26 as
27 v_deptno number(2);
28 v_ratio number(10,2); --存储调薪幅度变量
29 begin
30 select deptno into v_deptno from emp where empno=p_empno;
31 v_ratio:=get_ratio_by_dept(v_deptno);
32 if v_ratio>0
33 then
34 update scott.emp
35 set sal =sal* (1+v_ratio)
36 where empno = p_empno;
37 end if;
38 dbms_output.put_line('加薪成功!');
39 exception
40 when no_data_found then
41 dbms_output.put_line('没有找到该员工的任何信息!');
42 when others then
43 dbms_output.put_line('调整薪资时出现了错误!');
44 end;
45
46 set serveroutput on;
47 exec raise_salary(7369);
48
49 --创建包头
50 create or replace package emp_sal_pkg as
51 function get_ratio_by_dept(deptno varchar2) return number;
52 procedure raise_salary(p_empno number);
53 end emp_sal_pkg;
54
55 --创建包体
56 create or replace package body emp_sal_pkg as
57 function get_ratio_by_dept(deptno varchar2)
58 return number
59 is
60 n_salaryratio number(10,2);
61 begin
62 case deptno
63 when 10 then
64 n_salaryratio:=1.09;
65 when 20 then
66 n_salaryratio:=1.11;
67 when 30 then
68 n_salaryratio:=1.18;
69 else
70 n_salaryratio:=1;
71 end case;
72 return n_salaryratio;
73 end get_ratio_by_dept;
74
75 procedure raise_salary (p_empno number)
76 as
77 v_deptno number(2);
78 v_ratio number(10,2);
79 begin
80 select deptno into v_deptno from emp where empno=p_empno;
81 v_ratio:=get_ratio_by_dept(v_deptno);
82 if v_ratio>0
83 then
84 update scott.emp
85 set sal =sal* (1+v_ratio)
86 where empno = p_empno;
87 end if;
88 dbms_output.put_line('加薪成功!');
89 exception
90 when no_data_found then
91 dbms_output.put_line('没有找到该员工的任何信息!');
92 when others then
93 dbms_output.put_line('调整薪资时出现了错误!');
94 end raise_salary;
95 end emp_sal_pkg;
96 --创建过程添加新员工
97 create or replace procedure AddNewEmp(p_empno emp.empno%type,
98 p_ename emp.ename%type,
99 p_job emp.job%type,
100 p_sal emp.sal%type,
101 p_deptno emp.deptno%type:=20)
102 as
103 begin
104 if p_empno<0 then
105 raise_application_error(-20001,'员工编号必须大于0');
106 end if;
107 insert into emp
108 (empno,ename,job,sal,deptno)
109 values (p_empno,p_ename,p_job,p_sal,p_deptno);
110 end AddNewEmp;
111
112 begin
113 AddNewEmp(8236,'诸葛亮','策划人员',25000,40);
114 end;
115
116
117
118 子程序创建高度可维护,重用的代码
119 (1)模块化代码(2)简化应用程序(3)提升管理性:需求变更,只要改子程序
120 (4)可重用性,提高性能
121 程序调试
122 C+R 直接运行 C+N单击进入 C+O单步逃过 C+T单步退出
123 --在过程中使用return语句
124 create or replace procedure RaiseSalary(
125 p_empno emp.empno%type) as
126 v_job emp.job%type;
127 v_sal emp.sal%type;
128 begin
129 select job,sal into v_job,v_sal from emp
130 where empno=p_empno;
131 if v_job<>'CLERK' then
132 return; --如果不是职员则退出
133 elsif v_sal>3000 then
134 return ;
135 else
136 --否则更新薪资记录
137 update emp set sal=Round(sal*1.12,2) where
138 empno=p_empno;
139 end if;
140 exception
141 when no_data_found then
142 dbms_output.put_line('没有找到员工记录');
143 end RaiseSalary;
144 begin
145 RaiseSalary(7369);
146 end;
147 select * from emp where empno=7369;
148 --查询当前scott方案下的过程和函数列表
149 select object_name,object_type,created,
150 last_ddl_time,status,temporary
151 from user_objects
152 where object_type in ('PROCEDURE','FUNCTION','TABLE');
153
154
155 --in out 模式参数
156 create or replace procedure calcRaiseSalary(
157 p_job in varchar2,
158 p_salary in out number
159 ) as
160 v_sal number(10,2);
161 v_job varchar2(10);
162 begin
163 if p_job='CLERK' then
164 v_sal:=p_salary*1.12;
165
166 elsif p_job='销售人员' then
167 v_sal:=p_salary*1.18;
168 elsif p_job='经理' then
169 v_sal:=p_salary*1.19;
170 else
171 v_sal:=p_salary;
172
173 end if;
174 p_salary:=v_sal; ---值传递
175
176 end calcRaiseSalary;
177
178 declare
179 v_sal number(10,2);
180 v_job varchar2(10);
181 begin
182 select sal,job into v_sal,v_job from emp where
183 empno=7369;
184 calcRaiseSalary(v_job,v_sal);
185 dbms_output.put_line('计算后的调整薪水为:'||v_sal); --||'JOB'||v_jo
186 end;
187
188 select * from emp where empno=7369;
189
190 引用传递:将实际参数的指针(内存地址)传递给形式参数,
191 值传递:将实际参数的值赋给形式参数,值拷贝,指向不同的内存地址
192
193 如果参数是大型数据结构,比如集合,记录和对象实例, 参数复制会大大降低执行速度,
194 消耗内存
195 nocopy 使得out和in out模式的参数按引用进行传递
196
197
198
199
200
201
202 --编译包规范
203 alter package emp_pkg_overloading compile specification;
204 --编译包体
205 alter package emp_pkg_overloading compile body;
206 --同时编译包规范和包体
207 alter package emp_pkg_overloading compile package;
208
209 --查询包规范和包体信息
210 select object_name,object_type,created,last_ddl_time from user_objects
211 where object_type in ('PACKAGE','PACKAGE BODY');
212
213 --查看包的源代码
214 select line,text from user_source where name='EMP_MGMT_PKG_OVERLOADING'
215 and type='PACKAGE';
216
217
218 --使用returning into 为记录变量赋值
219 declare
220 type t_emp is record(
221 empno emp.empno%type,
222 ename emp.ename%type,
223 sal emp.sal%type
224 );
225 emp_info t_emp;
226 old_sal emp.sal%type;
227 begin
228 select sal into old_sal
229 from emp
230 where empno=7369;
231 update emp
232 set sal=sal*1.1
233 where empno=7369
234 returning empno,ename,sal into emp_info;
235 dbms_output.put_line(
236 emp_info.empno||' '||emp_info.ename ||' '||
237 old_sal||' '|| emp_info.sal);
238
239 end;
240 select sal,ename from emp where empno =7369
241
242 --在insert语句中使用记录类型
243 declare
244 type t_dept_rec is record(
245 rec_deptno number,
246 rec_dname varchar2(14),
247 rec_loc varchar2(13)
248 );
249 rec_dept_1 t_dept_rec;
250 rec_dept_2 dept%rowtype;
251 begin
252 rec_dept_1.rec_deptno:=71;
253 rec_dept_1.rec_dname:='系统部';
254 rec_dept_1.rec_loc:='上海';
255 insert into dept values rec_dept_1;
256 rec_dept_2.deptno:=72;
257 rec_dept_2.dname:='开发部';
258 rec_dept_2.loc:='重庆';
259 insert into dept values rec_dept_2;
260 end;
261
262 select * from dept
263
264
265 --在update语句中使用记录类型
266 declare
267 rec_dept_2 dept%rowtype;
268 begin
269 rec_dept_2.deptno:=20;
270 rec_dept_2.dname:='系统部';
271 rec_dept_2.loc:='上海';
272 update dept set row=rec_dept_2 where deptno=rec_dept_2.deptno;
273 end;
274
275 使用记录类型的限制
276 (1)记录类型不能出现在select语句的选择列表, where子句,group by 子句
277 或order by子句中
278 (2)update语句中Row关键字只能出现在set语句之后,并且不能和子查询连用
279 (3)不能包含其他的变量和值,不能具有嵌套的记录类型,不支持在
280 execute immediate 语句中使用记录类型.
