oracle游标 date类型,Oracle游标使用

1 --声明游标 CURSOR cursor_name IS select_statement

2

3 --For 循环游标

4 --(1)定义游标

5 --(2)定义游标变量

6 --(3)使用for循环来使用这个游标

7 declare

8 --类型定义

9 cursor c_sursorA

10 is

11 select empA,empB,empC,empD

12 from emp

13 where empE='MANAGER';

14 --定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型

15 c_row c_sursorA%rowtype;

16 begin

17 for c_row in c_sursorA loop

18 dbms_output.put_line(c_row.empA||'-'||c_row.empB||'-'||c_row.empC||'-'||c_row.empD);

19 end loop;

20 end;

21

22 --Fetch游标

23 --使用的时候必须要明确的打开和关闭

24

25 declare

26 --类型定义

27 cursor c_job

28 is

29 select empno,ename,job,sal

30 from emp

31 where job='MANAGER';

32 --定义一个游标变量

33 c_row c_job%rowtype;

34 begin

35 open c_job;

36 loop

37 --提取一行数据到c_row

38 fetch c_job into c_row;

39 --判读是否提取到值，没取到值就退出

40 --取到值c_job%notfound 是false

41 --取不到值c_job%notfound 是true

42 exit when c_job%notfound;

43 dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);

44 end loop;

45 --关闭游标

46 close c_job;

47 end;

48

49 --1：任意执行一个update操作，用隐式游标sql的属性%found,%notfound,%rowcount,%isopen观察update语句的执行情况。

50 begin

51 update emp set ENAME='ALEARK' WHERE EMPNO=7469;

52 if sql%isopen then

53 dbms_output.put_line('Openging');

54 else

55 dbms_output.put_line('closing');

56 end if;

57 if sql%found then

58 dbms_output.put_line('游标指向了有效行');--判断游标是否指向有效行

59 else

60 dbms_output.put_line('Sorry');

61 end if;

62 if sql%notfound then

63 dbms_output.put_line('Also Sorry');

64 else

65 dbms_output.put_line('Haha');

66 end if;

67 dbms_output.put_line(sql%rowcount);

68 exception

69 when no_data_found then

70 dbms_output.put_line('Sorry No data');

71 when too_many_rows then

72 dbms_output.put_line('Too Many rows');

73 end;

74 declare

75 empNumber emp.EMPNO%TYPE;

76 empName emp.ENAME%TYPE;

77 begin

78 if sql%isopen then

79 dbms_output.put_line('Cursor is opinging');

80 else

81 dbms_output.put_line('Cursor is Close');

82 end if;

83 if sql%notfound then

84 dbms_output.put_line('No Value');

85 else

86 dbms_output.put_line(empNumber);

87 end if;

88 dbms_output.put_line(sql%rowcount);

89 dbms_output.put_line('-------------');

90

91 select EMPNO,ENAME into empNumber,empName from emp where EMPNO=7499;

92 dbms_output.put_line(sql%rowcount);

93

94 if sql%isopen then

95 dbms_output.put_line('Cursor is opinging');

96 else

97 dbms_output.put_line('Cursor is Closing');

98 end if;

99 if sql%notfound then

100 dbms_output.put_line('No Value');

101 else

102 dbms_output.put_line(empNumber);

103 end if;

104 exception

105 when no_data_found then

106 dbms_output.put_line('No Value');

107 when too_many_rows then

108 dbms_output.put_line('too many rows');

109 end;

110

111

112

113 --2,使用游标和loop循环来显示所有部门的名称

114 --游标声明

115 declare

116 cursor csr_dept

117 is

118 --select语句

119 select DNAME

120 from Depth;

121 --指定行指针,这句话应该是指定和csr_dept行类型相同的变量

122 row_dept csr_dept%rowtype;

123 begin

124 --for循环

125 for row_dept in csr_dept loop

126 dbms_output.put_line('部门名称:'||row_dept.DNAME);

127 end loop;

128 end;

129

130

131 --3,使用游标和while循环来显示所有部门的的地理位置(用%found属性)

132 declare

133 --游标声明

134 cursor csr_TestWhile

135 is

136 --select语句

137 select LOC

138 from Depth;

139 --指定行指针

140 row_loc csr_TestWhile%rowtype;

141 begin

142 --打开游标

143 open csr_TestWhile;

144 --给第一行喂数据

145 fetch csr_TestWhile into row_loc;

146 --测试是否有数据，并执行循环

147 while csr_TestWhile%found loop

148 dbms_output.put_line('部门地点：'||row_loc.LOC);

149 --给下一行喂数据

150 fetch csr_TestWhile into row_loc;

151 end loop;

152 close csr_TestWhile;

153 end;

154 select * from emp

155

156

157

158

159 --4,接收用户输入的部门编号，用for循环和游标，打印出此部门的所有雇员的所有信息(使用循环游标)

160 --CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;

161 --定义参数的语法如下:Parameter_name [IN] data_type[{:=|DEFAULT} value]

162

163 declare

164 CURSOR

165 c_dept(p_deptNo number)

166 is

167 select * from emp where emp.depno=p_deptNo;

168 r_emp emp%rowtype;

169 begin

170 for r_emp in c_dept(20) loop

171 dbms_output.put_line('员工号：'||r_emp.EMPNO||'员工名：'||r_emp.ENAME||'工资：'||r_emp.SAL);

172 end loop;

173 end;

174 select * from emp

175 --5：向游标传递一个工种，显示此工种的所有雇员的所有信息(使用参数游标)

176 declare

177 cursor

178 c_job(p_job nvarchar2)

179 is

180 select * from emp where JOB=p_job;

181 r_job emp%rowtype;

182 begin

183 for r_job in c_job('CLERK') loop

184 dbms_output.put_line('员工号'||r_job.EMPNO||' '||'员工姓名'||r_job.ENAME);

185 end loop;

186 end;

187 SELECT * FROM EMP

188

189 --6：用更新游标来为雇员加佣金：(用if实现,创建一个与emp表一摸一样的emp1表，对emp1表进行修改操作),并将更新前后的数据输出出来

190 --http://zheng12tian.iteye.com/blog/815770

191 create table emp1 as select * from emp;

192

193 declare

194 cursor

195 csr_Update

196 is

197 select * from emp1 for update OF SAL;

198 empInfo csr_Update%rowtype;

199 saleInfo emp1.SAL%TYPE;

200 begin

201 FOR empInfo IN csr_Update LOOP

202 IF empInfo.SAL<1500 THEN

203 saleInfo:=empInfo.SAL*1.2;

204 elsif empInfo.SAL<2000 THEN

205 saleInfo:=empInfo.SAL*1.5;

206 elsif empInfo.SAL<3000 THEN

207 saleInfo:=empInfo.SAL*2;

208 END IF;

209 UPDATE emp1 SET SAL=saleInfo WHERE CURRENT OF csr_Update;

210 END LOOP;

211 END;

212

213 --7:编写一个PL/SQL程序块，对名字以‘A’或‘S’开始的所有雇员按他们的基本薪水(sal)的10%给他们加薪(对emp1表进行修改操作)

214 declare

215 cursor

216 csr_AddSal

217 is

218 select * from emp1 where ENAME LIKE 'A%' OR ENAME LIKE 'S%' for update OF SAL;

219 r_AddSal csr_AddSal%rowtype;

220 saleInfo emp1.SAL%TYPE;

221 begin

222 for r_AddSal in csr_AddSal loop

223 dbms_output.put_line(r_AddSal.ENAME||'原来的工资:'||r_AddSal.SAL);

224 saleInfo:=r_AddSal.SAL*1.1;

225 UPDATE emp1 SET SAL=saleInfo WHERE CURRENT OF csr_AddSal;

226 end loop;

227 end;

228 --8：编写一个PL/SQL程序块，对所有的salesman增加佣金(comm)500

229 declare

230 cursor

231 csr_AddComm(p_job nvarchar2)

232 is

233 select * from emp1 where JOB=p_job FOR UPDATE OF COMM;

234 r_AddComm emp1%rowtype;

235 commInfo emp1.comm%type;

236 begin

237 for r_AddComm in csr_AddComm('SALESMAN') LOOP

238 commInfo:=r_AddComm.COMM+500;

239 UPDATE EMP1 SET COMM=commInfo where CURRENT OF csr_AddComm;

240 END LOOP;

241 END;

242

243 --9：编写一个PL/SQL程序块，以提升2个资格最老的职员为MANAGER(工作时间越长，资格越老)

244 --(提示：可以定义一个变量作为计数器控制游标只提取两条数据；也可以在声明游标的时候把雇员中资格最老的两个人查出来放到游标中。)

245 declare

246 cursor crs_testComput

247 is

248 select * from emp1 order by HIREDATE asc;

249 --计数器

250 top_two number:=2;

251 r_testComput crs_testComput%rowtype;

252 begin

253 open crs_testComput;

254 FETCH crs_testComput INTO r_testComput;

255 while top_two>0 loop

256 dbms_output.put_line('员工姓名：'||r_testComput.ENAME||'工作时间：'||r_testComput.HIREDATE);

257 --计速器减一

258 top_two:=top_two-1;

259 FETCH crs_testComput INTO r_testComput;

260 end loop;

261 close crs_testComput;

262 end;

263

264

265 --10：编写一个PL/SQL程序块，对所有雇员按他们的基本薪水(sal)的20%为他们加薪，

266 --如果增加的薪水大于300就取消加薪(对emp1表进行修改操作，并将更新前后的数据输出出来)

267 declare

268 cursor

269 crs_UpadateSal

270 is

271 select * from emp1 for update of SAL;

272 r_UpdateSal crs_UpadateSal%rowtype;

273 salAdd emp1.sal%type;

274 salInfo emp1.sal%type;

275 begin

276 for r_UpdateSal in crs_UpadateSal loop

277 salAdd:= r_UpdateSal.SAL*0.2;

278 if salAdd>300 then

279 salInfo:=r_UpdateSal.SAL;

280 dbms_output.put_line(r_UpdateSal.ENAME||': 加薪失败。'||'薪水维持在：'||r_UpdateSal.SAL);

281 else

282 salInfo:=r_UpdateSal.SAL+salAdd;

283 dbms_output.put_line(r_UpdateSal.ENAME||': 加薪成功.'||'薪水变为：'||salInfo);

284 end if;

285 update emp1 set SAL=salInfo where current of crs_UpadateSal;

286 end loop;

287 end;

288

289 --11:将每位员工工作了多少年零多少月零多少天输出出来

290 --近似

291 --CEIL(n)函数：取大于等于数值n的最小整数

292 --FLOOR(n)函数：取小于等于数值n的最大整数

293 --truc的用法 http://publish.it168.com/2005/1028/20051028034101.shtml

294 declare

295 cursor

296 crs_WorkDay

297 is

298 select ENAME,HIREDATE, trunc(months_between(sysdate, hiredate) / 12) AS SPANDYEARS,

299 trunc(mod(months_between(sysdate, hiredate), 12)) AS months,

300 trunc(mod(mod(sysdate - hiredate, 365), 12)) as days

301 from emp1;

302 r_WorkDay crs_WorkDay%rowtype;

303 begin

304 for r_WorkDay in crs_WorkDay loop

305 dbms_output.put_line(r_WorkDay.ENAME||'已经工作了'||r_WorkDay.SPANDYEARS||'年,零'||r_WorkDay.months||'月,零'||r_WorkDay.days||'天');

306 end loop;

307 end;

308

309 --12:输入部门编号，按照下列加薪比例执行(用CASE实现，创建一个emp1表，修改emp1表的数据),并将更新前后的数据输出出来

310 -- deptno raise(%)

311 -- 10 5%

312 -- 20 10%

313 -- 30 15%

314 -- 40 20%

315 -- 加薪比例以现有的sal为标准

316 --CASE expr WHEN comparison_expr THEN return_expr

317 --[, WHEN comparison_expr THEN return_expr]... [ELSE else_expr] END

318 declare

319 cursor

320 crs_caseTest

321 is

322 select * from emp1 for update of SAL;

323 r_caseTest crs_caseTest%rowtype;

324 salInfo emp1.sal%type;

325 begin

326 for r_caseTest in crs_caseTest loop

327 case

328 when r_caseTest.DEPNO=10

329 THEN salInfo:=r_caseTest.SAL*1.05;

330 when r_caseTest.DEPNO=20

331 THEN salInfo:=r_caseTest.SAL*1.1;

332 when r_caseTest.DEPNO=30

333 THEN salInfo:=r_caseTest.SAL*1.15;

334 when r_caseTest.DEPNO=40

335 THEN salInfo:=r_caseTest.SAL*1.2;

336 end case;

337 update emp1 set SAL=salInfo where current of crs_caseTest;

338 end loop;

339 end;

340

341 --13:对每位员工的薪水进行判断，如果该员工薪水高于其所在部门的平均薪水，则将其薪水减50元，输出更新前后的薪水，员工姓名，所在部门编号。

342 --AVG([distinct|all] expr) over (analytic_clause)

343 ---作用：

344 --按照analytic_clause中的规则求分组平均值。

345 --分析函数语法:

346 --FUNCTION_NAME(,...)

347 --OVER

348 --()

349 --PARTITION子句

350 --按照表达式分区(就是分组),如果省略了分区子句,则全部的结果集被看作是一个单一的组

351 select * from emp1

352 DECLARE

353 CURSOR

354 crs_testAvg

355 IS

356 select EMPNO,ENAME,JOB,SAL,DEPNO,AVG(SAL) OVER (PARTITION BY DEPNO ) AS DEP_AVG

357 FROM EMP1 for update of SAL;

358 r_testAvg crs_testAvg%rowtype;

359 salInfo emp1.sal%type;

360 begin

361 for r_testAvg in crs_testAvg loop

362 if r_testAvg.SAL>r_testAvg.DEP_AVG then

363 salInfo:=r_testAvg.SAL-50;

364 end if;

365 update emp1 set SAL=salInfo where current of crs_testAvg;

366 end loop;

367 end;