oracle存储过程语法.docx

oracle存储过程语法.docx

《oracle存储过程语法.docx》由会员分享，可在线阅读，更多相关《oracle存储过程语法.docx（12页珍藏版）》请在冰豆网上搜索。

oracle存储过程语法

存储过程

　　1CREATEORREPLACEPROCEDURE存储过程名

　　2IS

　　3BEGIN

　　4NULL;

　　5END;

行1:

　　CREATEORREPLACEPROCEDURE是一个SQL语句通知Oracle数据库去创建一个叫做skeleton存储过程,如果存在就覆盖它;

行2:

　　IS关键词表明后面将跟随一个PL/SQL体。

行3:

　　BEGIN关键词表明PL/SQL体的开始。

行4:

　　NULLPL/SQL语句表明什么事都不做，这句不能删去，因为PL/SQL体中至少需要有一句;

行5:

　　END关键词表明PL/SQL体的结束

存储过程创建语法：

 createorreplaceprocedure存储过程名（param1intype，param2outtype）

as

变量1类型（值范围）;--vs_msg   VARCHAR2（4000）;

变量2类型（值范围）;

Begin

Selectcount（*）into变量1from表Awhere列名=param1；

   If（判断条件）then

      Select列名into变量2from表Awhere列名=param1；

      Dbms_output。

Put_line（‘打印信息’）;

   Elsif（判断条件）then

      Dbms_output。

Put_line（‘打印信息’）;

   Else

      Raise异常名（NO_DATA_FOUND）;

   Endif;

Exception

   Whenothersthen

      Rollback;

End;

注意事项：

1，存储过程参数不带取值范围，in表示传入，out表示输出

类型可以使用任意Oracle中的合法类型。

2， 变量带取值范围，后面接分号

3， 在判断语句前最好先用count（*）函数判断是否存在该条操作记录

4， 用select。

。

。

into。

。

。

给变量赋值

5， 在代码中抛异常用raise+异常名

CREATEORREPLACEPROCEDURE存储过程名

（

--定义参数

 is_ym INCHAR（6）,

the_countOUTNUMBER,

）

AS

--定义变量

vs_msg   VARCHAR2（4000）;   --错误信息变量

vs_ym_beg  CHAR（6）;      --起始月份

vs_ym_end  CHAR（6）;      --终止月份

vs_ym_sn_beg CHAR（6）;     --同期起始月份

vs_ym_sn_end CHAR（6）;     --同期终止月份

--定义游标（简单的说就是一个可以遍历的结果集）

CURSORcur_1IS

 SELECT。

。

。

 FROM。

。

。

   WHERE。

。

。

  GROUPBY。

。

。

;

BEGIN

--用输入参数给变量赋初值，用到了Oralce的SUBSTRTO_CHARADD_MONTHS

TO_DATE等很常用的函数。

vs_ym_beg:

=SUBSTR（is_ym,1,6）;

vs_ym_end:

=SUBSTR（is_ym,7,6）;

vs_ym_sn_beg:

=TO_CHAR（ADD_MONTHS（TO_DATE（vs_ym_beg,'yyyymm'）,-12）,'yyyymm'）;

vs_ym_sn_end:

=TO_CHAR（ADD_MONTHS（TO_DATE（vs_ym_end,'yyyymm'）,-12）,'yyyymm'）;

--先删除表中特定条件的数据。

DELETEFROM表名WHEREym=is_ym;

 --然后用内置的DBMS_OUTPUT对象的put_line方法打印出影响的记录行数，其中用到一个系统变量SQL%rowcount

DBMS_OUTPUT.put_line（'del上月记录='||SQL%rowcount||'条'）;

INSERTINTO表名（area_code,ym,CMCODE,rmb_amt,usd_amt）

SELECTarea_code,is_ym,CMCODE,SUM（rmb_amt）/10000,SUM（usd_amt）/10000

FROMBGD_AREA_CM_M_BASE_T

 WHEREym>=vs_ym_beg

 ANDym<=vs_ym_end

GROUPBYarea_code,CMCODE;

DBMS_OUTPUT.put_line（'ins当月记录='||SQL%rowcount||'条'）;

--遍历游标处理后更新到表。

遍历游标有几种方法，用for语句是其中比较直观的一种。

FORrecINcur_1LOOP

 UPDATE表名

 SETrmb_amt_sn=rec.rmb_amt_sn,usd_amt_sn=rec.usd_amt_sn

  WHEREarea_code=rec.area_code

  ANDCMCODE=rec.CMCODE

  ANDym=is_ym;

ENDLOOP;

COMMIT;

--错误处理部分。

OTHERS表示除了声明外的任意错误。

SQLERRM是系统内置变量保存了当前错误的详细信息。

EXCEPTION

  WHENOTHERSTHEN

     vs_msg:

='ERRORINxxxxxxxxxxx_p（'||is_ym||'）:

'||SUBSTR（SQLERRM,1,500）;

  ROLLBACK;

  --把当前错误记录进日志表。

  INSERTINTOLOG_INFO（proc_name,error_info,op_date）

  VALUES（'xxxxxxxxxxx_p',vs_msg,SYSDATE）;

  COMMIT;

  RETURN;

END;

举例

1.存储过程包含三部分：

声明，执行部分，异常。

2.可以有无参数程序和带参数存储过程。

3.无参程序语法

4.1createorreplaceprocedureNoParPro

5.2as;

6.3begin

7.4;

8.5exception

9.6;

10.7end;

11.8

12.

13.带参存储过程实例

14.1createorreplaceprocedurequeryempname（sfindnoemp.empno%type）as

15.2sNameemp.ename%type;

16.3sjobemp.job%type;

17.4begin

18.5....

19.7exception

20.....

21.14end;

22.15

23.

24.带参数存储过程含赋值方式

25.1createorreplaceprocedurerunbyparmeters（isalinemp.sal%type,

26.snameoutvarchar,sjobinoutvarchar）

27.2asicountnumber;

28.3begin

29.4selectcount（*）intoicountfromempwheresal>isalandjob=sjob;

30.5ificount=1then

31.6....

32.9else

33.10....

34.12endif;

35.13exception

36.14whentoo_many_rowsthen

37.15DBMS_OUTPUT.PUT_LINE（'返回值多于1行'）;

38.16whenothersthen

39.17DBMS_OUTPUT.PUT_LINE（'在RUNBYPARMETERS过程中出错！

'）;

40.18end;

41.19

42.

43.过程调用

44.方式一

45.1declare

46.2realsalemp.sal%type;

47.3realnamevarchar（40）;

48.4realjobvarchar（40）;

49.5begin

50.6realsal:

=1100;

51.7realname:

='';

52.8realjob:

='CLERK';

53.9runbyparmeters（realsal,realname,realjob）;－－必须按顺序

54.10DBMS_OUTPUT.PUT_LINE（REALNAME||''||REALJOB）;

55.11END;

56.12

57.

58.方式二

59.1declare

60.2realsalemp.sal%type;

61.3realnamevarchar（40）;

62.4realjobvarchar（40）;

63.5begin

64.6realsal:

=1100;

65.7realname:

='';

66.8realjob:

='CLERK';

67.9runbyparmeters（sname=>realname,isal=>realsal,sjob=>realjob）;－－指定值对应变量顺序可变

68.10DBMS_OUTPUT.PUT_LINE（REALNAME||''||REALJOB）;

69.11END;

oracle存储过程语法

1、判断语句:

if比较式thenbeginend;endif;

createorreplaceproceduretest（xinnumber）is

begin

       ifx>0then

        begin

       x:

=0-x;

       end;

   endif;

   ifx=0then

      begin

       x:

=1;

   end;

   endif;

endtest;

2、For循环

For...in...LOOP

--执行语句

endLOOP;

（1）循环遍历游标

createorreplaceproceduretest（）as

Cursorcursorisselectnamefromstudent;namevarchar（20）;

begin

fornameincursorLOOP

begin

 dbms_output.putline（name）; 

end;

endLOOP;

endtest;

（2）循环遍历数组

 createorreplaceproceduretest（varArrayinmyPackage.TestArray）as

--（输入参数varArray是自定义的数组类型，定义方式见标题6）

inumber;

begin

i:

=1; --存储过程数组是起始位置是从1开始的，与java、C、C++等语言不同。

因为在Oracle中本是没有数组的概念的，数组其实就是一张

--表（Table）,每个数组元素就是表中的一个记录，所以遍历数组时就相当于从表中的第一条记录开始遍历

foriin1..varArray.countLOOP     

dbms_output.putline（'TheNo.'||i||'recordinvarArrayis:

'||varArray（i））;   

 endLOOP;

endtest;

3、While循环

while条件语句LOOP

begin

end;

endLOOP;

E.g

createorreplaceproceduretest（iinnumber）as

begin

whilei<10LOOP

begin    

 i:

=i+1;

end;

endLOOP;

 endtest;

4、数组

首先明确一个概念：

Oracle中本是没有数组的概念的，数组其实就是一张表（Table）,每个数组元素就是表中的一个记录。

使用数组时，用户可以使用Oracle已经定义好的数组类型，或可根据自己的需要定义数组类型。

（1）使用Oracle自带的数组类型

xarray;--使用时需要需要进行初始化

e.g:

createorreplaceproceduretest（youtarray）is

 xarray;  

 begin

x:

=newarray（）;

y:

=x;

endtest;

（2）自定义的数组类型（自定义数据类型时，建议通过创建Package的方式实现，以便于管理）

createorreplacepackagemyPackageis

  Publictypedeclarations  typeinfoisrecord（    namevarchar（20）,    ynumber）;

 typeTestArrayistableofinfoindexbybinary_integer;  

--此处声明了一个TestArray的类型数据，其实其为一张存储Info数据类型的Table而已，及TestArray就是一张表，有两个字段，一个是name，一个是y。

需要注意的是此处使用了Indexbybinary_integer编制该Table的索引项，也可以不写，直接写成：

typeTestArrayis

tableofinfo，如果不写的话使用数组时就需要进行初始化：

varArraymyPackage.TestArray;varArray:

=newmyPackage.TestArray（）;

endTestArray;

5.游标的使用Oracle中Cursor是非常有用的，用于遍历临时表中的查询结果。

其相关方法和属性也很多，现仅就常用的用法做一二介绍：

（1）Cursor型游标（不能用于参数传递）

createorreplaceproceduretest（）is  

cusor_1Cursorisselectstd_namefromstudentwhere ...; --Cursor的使用方式1  cursor_2Cursor;

begin

selectclass_nameintocursor_2fromclasswhere...; --Cursor的使用方式2

可使用ForxincursorLOOP....endLOOP;来实现对Cursor的遍历

endtest;

（2）SYS_REFCURSOR型游标，该游标是Oracle以预先定义的游标，可作出参数进行传递

createorreplaceproceduretest（rsCursoroutSYS_REFCURSOR）is

cursorSYS_REFCURSOR;

namevarhcar（20）;

begin

OPENcursorFORselectnamefromstudentwhere...--SYS_REFCURSOR只能通过OPEN方法来打开和赋值

LOOP

 fetchcursorintoname  --SYS_REFCURSOR只能通过fetchinto来打开和遍历exitwhencursor%NOTFOUND;             --SYS_REFCURSOR中可使用三个状态属性：

                                        ---%NOTFOUND（未找到记录信息）%FOUND（找到记录信息）                                        ---%ROWCOUNT（然后当前游标所指向的行位置）

 dbms_output.putline（name）;

endLOOP;

rsCursor:

=cursor;

endtest;

实例

下面写一个简单的例子来对以上所说的存储过程的用法做一个应用：

现假设存在两张表，一张是学生成绩表（studnet），字段为：

stdId,math,article,language,music,sport,total,average,step              

一张是学生课外成绩表（out_school）,字段为:

stdId,parctice,comment

通过存储过程自动计算出每位学生的总成绩和平均成绩，同时，如果学生在课外课程中获得的评价为A，就在总成绩上加20分。

createorreplaceprocedureautocomputer（stepinnumber）is

rsCursorSYS_REFCURSOR;

commentArraymyPackage.myArray;

mathnumber;

articlenumber;

languagenumber;

musicnumber;

sportnumber;

totalnumber;

averagenumber;

stdIdvarchar（30）;

recordmyPackage.stdInfo;

inumber;

begin

i:

=1;

get_comment（commentArray）;--调用名为get_comment（）的存储过程获取学生课外评分信息

OPENrsCursorforselectstdId,math,article,language,music,sportfromstudenttwheret.step=step;

LOOP

fetchrsCursorintostdId,math,article,language,music,sport;exitwhenrsCursor%NOTFOUND;

total:

=math+article+language+music+sport;

foriinmentArray.countLOOP 

 record:

=commentArray（i）;    

ifstdId=record.stdIdthen  

 begin     

 ifment='A'then     

 begin         

 total:

=total+20;   

  gotonext;--使用goto跳出for循环       

 end;    

endif;  

end;  

endif;

endLOOP;

<> average:

=total/5;

 updatestudenttsett.total=totalandt.average=averagewheret.stdId=stdId;

endLOOP;

end;

endautocomputer;

--取得学生评论信息的存储过程

createorreplaceprocedureget_comment（commentArrayoutmyPackage.myArray）is

rsSYS_REFCURSOR；

recordmyPackage.stdInfo;

stdIdvarchar（30）;

commentvarchar

（1）;

inumber;

begin

openrsforselectstdId,commentfromout_school

i:

=1;

LOOP

 fetchrsintostdId,comment;exitwhenrs%NOTFOUND;

record.stdId:

=stdId;

 ment:

=comment;

recommentArray（i）:

=record;

i:

=i+1;

endLOOP;

endget_comment;

--定义数组类型myArray

createorreplacepackagemyPackageisbegin

typestdInfoisrecord（stdIdvarchar（30）,commentvarchar

（1））;

typemyArrayistableofstdInfoindexbybinary_integer;

endmyPackage;

byebye