1 **************************************
2 游标的使用
3 **************************************
4 --12.1 使用游标
5
6 --要求: 打印出 80 部门的所有的员工的工资:salary: xxx
7
8 declare
9 --1. 定义游标
10 cursor salary_cursor is select salary from employees where department_id = 80;
11 v_salary employees.salary%type;
12 begin
13 --2. 打开游标
14 open salary_cursor;
15
16 --3. 提取游标
17 fetch salary_cursor into v_salary;
18
19 --4. 对游标进行循环操作: 判断游标中是否有下一条记录
20 while salary_cursor%found loop
21 dbms_output.put_line('salary: ' || v_salary);
22 fetch salary_cursor into v_salary;
23 end loop;
24
25 --5. 关闭游标
26 close salary_cursor;
27 end;
28
29 --12.2 使用游标
30
31 --要求: 打印出 80 部门的所有的员工的工资: Xxx 's salary is: xxx
32
33 declare
34 cursor sal_cursor is select salary ,last_name from employees where department_id = 80;
35 v_sal number(10);
36 v_name varchar2(20);
37 begin
38 open sal_cursor;
39
40 fetch sal_cursor into v_sal,v_name;
41
42 while sal_cursor%found loop
43 dbms_output.put_line(v_name||'`s salary is '||v_sal);
44 fetch sal_cursor into v_sal,v_name;
45 end loop;
46
47 close sal_cursor;
48
49 end;
50
51 --13. 使用游标的练习:
52 --打印出 manager_id 为 100 的员工的 last_name, email, salary 信息(使用游标, 记录类型)
53
54 declare
55 --声明游标
56 cursor emp_cursor is select last_name, email, salary from employees where manager_id = 100;
57
58 --声明记录类型
59 type emp_record is record(
60 name employees.last_name%type,
61 email employees.email%type,
62 salary employees.salary%type
63 );
64
65 -- 声明记录类型的变量
66 v_emp_record emp_record;
67 begin
68 --打开游标
69 open emp_cursor;
70
71 --提取游标
72 fetch emp_cursor into v_emp_record;
73
74 --对游标进行循环操作
75 while emp_cursor%found loop
76 dbms_output.put_line(v_emp_record.name || ', ' || v_emp_record.email || ', ' || v_emp_record.salary );
77 fetch emp_cursor into v_emp_record;
78 end loop;
79
80 --关闭游标
81 close emp_cursor;
82 end;
83 (法二：使用for循环)
84 declare
85
86 cursor emp_cursor is
87 select last_name,email,salary
88 from employees
89 where manager_id = 100;
90
91 begin
92
93
94 for v_emp_record in emp_cursor loop
95 dbms_output.put_line(v_emp_record.last_name||','||v_emp_record.email||','||v_emp_record.salary);
96 end loop;
97 end;
98
99 --14. 利用游标, 调整公司中员工的工资:
100
101 工资范围 调整基数
102 0 - 5000 5%
103 5000 - 10000 3%
104 10000 - 15000 2%
105 15000 - 1%
106
107 declare
108 --定义游标
109 cursor emp_sal_cursor is select salary, employee_id from employees;
110
111 --定义基数变量
112 temp number(4, 2);
113
114 --定义存放游标值的变量
115 v_sal employees.salary%type;
116 v_id employees.employee_id%type;
117 begin
118 --打开游标
119 open emp_sal_cursor;
120
121 --提取游标
122 fetch emp_sal_cursor into v_sal, v_id;
123
124 --处理游标的循环操作
125 while emp_sal_cursor%found loop
126 --判断员工的工资, 执行 update 操作
127 --dbms_output.put_line(v_id || ': ' || v_sal);
128
129 if v_sal <= 5000 then
130 temp := 0.05;
131 elsif v_sal<= 10000 then
132 temp := 0.03;
133 elsif v_sal <= 15000 then
134 temp := 0.02;
135 else
136 temp := 0.01;
137 end if;
138
139 --dbms_output.put_line(v_id || ': ' || v_sal || ', ' || temp);
140 update employees set salary = salary * (1 + temp) where employee_id = v_id;
141
142 fetch emp_sal_cursor into v_sal, v_id;
143 end loop;
144 --关闭游标
145 close emp_sal_cursor;
146 end;
147
148 使用SQL中的 decode 函数
149
150 update employees set salary = salary * (1 + (decode(trunc(salary/5000), 0, 0.05,
151 1, 0.03,
152 2, 0.02,
153 0.01)))
154
155 --15. 利用游标 for 循环完成 14.
156
157 declare
158 --定义游标
159 cursor emp_sal_cursor is select salary, employee_id id from employees;
160
161 --定义基数变量
162 temp number(4, 2);
163 begin
164 --处理游标的循环操作
165 for c in emp_sal_cursor loop
166 --判断员工的工资, 执行 update 操作
167 --dbms_output.put_line(v_id || ': ' || v_sal);
168
169 if c.salary <= 5000 then
170 temp := 0.05;
171 elsif c.salary <= 10000 then
172 temp := 0.03;
173 elsif c.salary <= 15000 then
174 temp := 0.02;
175 else
176 temp := 0.01;
177 end if;
178
179 --dbms_output.put_line(v_id || ': ' || v_sal || ', ' || temp);
180 update employees set salary = salary * (1 + temp) where employee_id = c.id;
181 end loop;
182 end;
183
184 --16*. 带参数的游标
185
186 declare
187 --定义游标
188 cursor emp_sal_cursor(dept_id number, sal number) is
189 select salary + 1000 sal, employee_id id
190 from employees
191 where department_id = dept_id and salary > sal;
192
193 --定义基数变量
194 temp number(4, 2);
195 begin
196 --处理游标的循环操作
197 for c in emp_sal_cursor(sal => 4000, dept_id => 80) loop
198 --判断员工的工资, 执行 update 操作
199 --dbms_output.put_line(c.id || ': ' || c.sal);
200
201 if c.sal <= 5000 then
202 temp := 0.05;
203 elsif c.sal <= 10000 then
204 temp := 0.03;
205 elsif c.sal <= 15000 then
206 temp := 0.02;
207 else
208 temp := 0.01;
209 end if;
210
211 dbms_output.put_line(c.sal || ': ' || c.id || ', ' || temp);
212 --update employees set salary = salary * (1 + temp) where employee_id = c.id;
213 end loop;
214 end;
215
216 --17. 隐式游标: 更新指定员工 salary(涨工资 10)，如果该员工没有找到，则打印”查无此人” 信息
217
218 begin
219 update employees set salary = salary + 10 where employee_id = 1005;
220
221 if sql%notfound then
222 dbms_output.put_line('查无此人!');
223 end if;
224 end;