Oracle存储过程基本语法
1 存储过程 2 3 1 CREATE OR REPLACE PROCEDURE 存储过程名 4 5 2 IS 6 7 3 BEGIN 8 9 4 NULL; 10 11 5 END; 12 13 14 15 行1: 16 17 CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracle数据库去创建一个叫做skeleton存储过程, 如果存在就覆盖它; 18 19 行2: 20 21 IS关键词表明后面将跟随一个PL/SQL体。 22 23 行3: 24 25 BEGIN关键词表明PL/SQL体的开始。 26 27 行4: 28 29 NULL PL/SQL语句表明什么事都不做,这句不能删去,因为PL/SQL体中至少需要有一句; 30 31 行5: 32 33 END关键词表明PL/SQL体的结束 34 35 存储过程创建语法: 36 37 create or replace procedure 存储过程名(param1 in type,param2 out type) 38 39 as 40 41 变量1 类型(值范围); --vs_msg VARCHAR2(4000); 42 43 变量2 类型(值范围); 44 45 Begin 46 47 Select count(*) into 变量1 from 表A where列名=param1; 48 49 50 51 If (判断条件) then 52 53 Select 列名 into 变量2 from 表A where列名=param1; 54 55 Dbms_output。Put_line(‘打印信息’); 56 57 Elsif (判断条件) then 58 59 Dbms_output。Put_line(‘打印信息’); 60 61 Else 62 63 Raise 异常名(NO_DATA_FOUND); 64 65 End if; 66 67 Exception 68 69 When others then 70 71 Rollback; 72 73 End; 74 75 76 77 78 79 80 81 注意事项: 82 83 1, 存储过程参数不带取值范围,in表示传入,out表示输出 84 85 类型可以使用任意Oracle中的合法类型。 86 87 2, 变量带取值范围,后面接分号 88 89 3, 在判断语句前最好先用count(*)函数判断是否存在该条操作记录 90 91 4, 用select 。。。into。。。给变量赋值 92 93 5, 在代码中抛异常用 raise+异常名 94 95 96 97 CREATE OR REPLACE PROCEDURE存储过程名 98 ( 99 100 --定义参数 101 is_ym IN CHAR(6) , 102 103 the_count OUT NUMBER, 104 ) 105 AS 106 --定义变量 107 vs_msg VARCHAR2(4000); --错误信息变量 108 vs_ym_beg CHAR(6); --起始月份 109 vs_ym_end CHAR(6); --终止月份 110 vs_ym_sn_beg CHAR(6); --同期起始月份 111 vs_ym_sn_end CHAR(6); --同期终止月份 112 113 --定义游标(简单的说就是一个可以遍历的结果集) 114 115 116 CURSOR cur_1 IS 117 SELECT 。。。 118 FROM 。。。 119 WHERE 。。。 120 GROUP BY 。。。; 121 122 BEGIN 123 124 125 --用输入参数给变量赋初值,用到了Oralce的SUBSTR TO_CHAR ADD_MONTHS 126 127 128 129 TO_DATE 等很常用的函数。 130 vs_ym_beg := SUBSTR(is_ym,1,6); 131 vs_ym_end := SUBSTR(is_ym,7,6); 132 vs_ym_sn_beg := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_beg,'yyyymm'), -12),'yyyymm'); 133 vs_ym_sn_end := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_end,'yyyymm'), -12),'yyyymm'); 134 135 136 --先删除表中特定条件的数据。 137 138 139 DELETE FROM 表名 WHERE ym = is_ym; 140 141 142 --然后用内置的DBMS_OUTPUT对象的put_line方法打印出影响的记录行数,其中用到一个系统变量SQL%rowcount 143 144 145 DBMS_OUTPUT.put_line('del上月记录='||SQL%rowcount||'条'); 146 147 INSERT INTO表名(area_code,ym,CMCODE,rmb_amt,usd_amt) 148 SELECT area_code,is_ym,CMCODE,SUM(rmb_amt)/10000,SUM(usd_amt)/10000 149 FROM BGD_AREA_CM_M_BASE_T 150 WHERE ym >= vs_ym_beg 151 AND ym <= vs_ym_end 152 GROUP BY area_code,CMCODE; 153 154 DBMS_OUTPUT.put_line('ins当月记录='||SQL%rowcount||'条'); 155 --遍历游标处理后更新到表。遍历游标有几种方法,用for语句是其中比较直观的一种。 156 157 158 FOR rec IN cur_1 LOOP 159 UPDATE 表名 160 SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn 161 WHERE area_code = rec.area_code 162 AND CMCODE = rec.CMCODE 163 AND ym = is_ym; 164 END LOOP; 165 166 COMMIT; 167 168 169 --错误处理部分。OTHERS表示除了声明外的任意错误。SQLERRM是系统内置变量保存了当前错误的详细信息。 170 171 172 EXCEPTION 173 174 175 WHEN OTHERS THEN 176 vs_msg := 'ERROR IN xxxxxxxxxxx_p('||is_ym||'):'||SUBSTR(SQLERRM,1,500); 177 178 179 ROLLBACK; 180 181 182 --把当前错误记录进日志表。 183 184 185 INSERT INTO LOG_INFO(proc_name,error_info,op_date) 186 VALUES('xxxxxxxxxxx_p',vs_msg,SYSDATE); 187 COMMIT; 188 RETURN; 189 190 191 END; 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 oracle存储过程语法 252 253 1 、判断语句: 254 255 if 比较式 then begin end; end if; 256 257 create or replace procedure test(x in number) is 258 259 begin 260 261 if x >0 then 262 263 begin 264 265 x := 0 - x; 266 267 end; 268 269 end if; 270 271 if x = 0 then 272 273 begin 274 275 x: = 1; 276 277 end; 278 279 end if; 280 281 end test; 282 283 2 、For 循环 284 285 For ... in ... LOOP 286 287 -- 执行语句 288 289 end LOOP; 290 291 (1) 循环遍历游标 292 293 create or replace procedure test() as 294 295 Cursor cursor is select name from student; name varchar(20); 296 297 begin 298 299 for name in cursor LOOP 300 301 begin 302 303 dbms_output.putline(name); 304 305 end; 306 307 end LOOP; 308 309 end test; 310 311 (2) 循环遍历数组 312 313 create or replace procedure test(varArray in myPackage.TestArray) as 314 315 --( 输入参数varArray 是自定义的数组类型,定义方式见标题6) 316 317 i number; 318 319 begin 320 321 i := 1; -- 存储过程数组是起始位置是从1 开始的,与java 、C 、C++ 等语言不同。因为在Oracle 中本是没有数组的概念的,数组其实就是一张 322 323 -- 表(Table), 每个数组元素就是表中的一个记录,所以遍历数组时就相当于从表中的第一条记录开始遍历 324 325 for i in 1..varArray.count LOOP 326 327 dbms_output.putline('The No.'|| i || 'record in varArray is:'||varArray(i)); 328 329 end LOOP; 330 331 end test; 332 333 3 、While 循环 334 335 while 条件语句 LOOP 336 337 begin 338 339 end; 340 341 end LOOP; 342 343 E.g 344 345 create or replace procedure test(i in number) as 346 347 begin 348 349 while i < 10 LOOP 350 351 begin 352 353 i:= i + 1; 354 355 end; 356 357 end LOOP; 358 359 end test; 360 361 4 、数组 362 363 首先明确一个概念:Oracle 中本是没有数组的概念的,数组其实就是一张表(Table), 每个数组元素就是表中的一个记录。 364 365 使用数组时,用户可以使用Oracle 已经定义好的数组类型,或可根据自己的需要定义数组类型。 366 367 (1) 使用Oracle 自带的数组类型 368 369 x array; -- 使用时需要需要进行初始化 370 371 e.g: 372 373 create or replace procedure test(y out array) is 374 375 x array; 376 377 begin 378 379 x := new array(); 380 381 y := x; 382 383 end test; 384 385 (2) 自定义的数组类型 ( 自定义数据类型时,建议通过创建Package 的方式实现,以便于管理) 386 387 create or replace package myPackage is 388 389 Public type declarations type info is record( name varchar(20), y number); 390 391 type TestArray is table of info index by binary_integer; 392 393 -- 此处声明了一个TestArray 的类型数据,其实其为一张存储Info 数据类型的Table 而已,及TestArray 就是一张表,有两个字段,一个是name ,一个是y 。需要注意的是此处使用了Index by binary_integer 编制该Table 的索引项,也可以不写,直接写成:type TestArray is 394 395 table of info ,如果不写的话使用数组时就需要进行初始化:varArray myPackage.TestArray; varArray := new myPackage.TestArray(); 396 397 end TestArray; 398 399 5. 游标的使用 Oracle 中Cursor 是非常有用的,用于遍历临时表中的查询结果。其相关方法和属性也很多,现仅就常用的用法做一二介绍: 400 401 (1)Cursor 型游标( 不能用于参数传递) 402 403 create or replace procedure test() is 404 405 cusor_1 Cursor is select std_name from student where ...; --Cursor 的使用方式1 cursor_2 Cursor; 406 407 begin 408 409 select class_name into cursor_2 from class where ...; --Cursor 的使用方式2 410 411 可使用For x in cursor LOOP .... end LOOP; 来实现对Cursor 的遍历 412 413 end test; 414 415 (2)SYS_REFCURSOR 型游标,该游标是Oracle 以预先定义的游标,可作出参数进行传递 416 417 create or replace procedure test(rsCursor out SYS_REFCURSOR) is 418 419 cursor SYS_REFCURSOR; 420 421 name varhcar(20); 422 423 begin 424 425 OPEN cursor FOR select name from student where ... --SYS_REFCURSOR 只能通过OPEN 方法来打开和赋值 426 427 LOOP 428 429 fetch cursor into name --SYS_REFCURSOR 只能通过fetch into 来打开和遍历 exit when cursor%NOTFOUND; --SYS_REFCURSOR 中可使用三个状态属性: ---%NOTFOUND( 未找到记录信息) %FOUND( 找到记录信息) ---%ROWCOUNT( 然后当前游标所指向的行位置) 430 431 dbms_output.putline(name); 432 433 end LOOP; 434 435 rsCursor := cursor; 436 437 end test; 438 439 440 441 442 443 444 445 实例 446 447 下面写一个简单的例子来对以上所说的存储过程的用法做一个应用: 448 449 现假设存在两张表,一张是学生成绩表(studnet) ,字段为:stdId,math,article,language,music,sport,total,average,step 450 451 一张是学生课外成绩表(out_school), 字段为:stdId,parctice,comment 452 453 通过存储过程自动计算出每位学生的总成绩和平均成绩,同时,如果学生在课外课程中获得的评价为A ,就在总成绩上加20 分。 454 455 create or replace procedure autocomputer(step in number) is 456 457 rsCursor SYS_REFCURSOR; 458 459 commentArray myPackage.myArray; 460 461 math number; 462 463 article number; 464 465 language number; 466 467 music number; 468 469 sport number; 470 471 total number; 472 473 average number; 474 475 stdId varchar(30); 476 477 record myPackage.stdInfo; 478 479 i number; 480 481 begin 482 483 i := 1; 484 485 get_comment(commentArray); -- 调用名为get_comment() 的存储过程获取学生课外评分信息 486 487 OPEN rsCursor for select stdId,math,article,language,music,sport from student t where t.step = step; 488 489 LOOP 490 491 fetch rsCursor into stdId,math,article,language,music,sport; exit when rsCursor%NOTFOUND; 492 493 total := math + article + language + music + sport; 494 495 for i in 1..commentArray.count LOOP 496 497 record := commentArray(i); 498 499 if stdId = record.stdId then 500 501 begin 502 503 if record.comment = 'A' then 504 505 begin 506 507 total := total + 20; 508 509 go to next; -- 使用go to 跳出for 循环 510 511 end; 512 513 end if; 514 515 end; 516 517 end if; 518 519 end LOOP; 520 521 <<continue>> average := total / 5; 522 523 update student t set t.total=total and t.average = average where t.stdId = stdId; 524 525 end LOOP; 526 527 end; 528 529 end autocomputer; 530 531 -- 取得学生评论信息的存储过程 532 533 create or replace procedure get_comment(commentArray out myPackage.myArray) is 534 535 rs SYS_REFCURSOR ; 536 537 record myPackage.stdInfo; 538 539 stdId varchar(30); 540 541 comment varchar(1); 542 543 i number; 544 545 begin 546 547 open rs for select stdId,comment from out_school 548 549 i := 1; 550 551 LOOP 552 553 fetch rs into stdId,comment; exit when rs%NOTFOUND; 554 555 record.stdId := stdId; 556 557 record.comment := comment; 558 559 recommentArray(i) := record; 560 561 i:=i + 1; 562 563 end LOOP; 564 565 end get_comment; 566 567 -- 定义数组类型myArray 568 569 create or replace package myPackage is begin 570 571 type stdInfo is record(stdId varchar(30),comment varchar(1)); 572 573 type myArray is table of stdInfo index by binary_integer; 574 575 end myPackage;