oracle存储过程使用手册.docx
Oracle存储过程总结1、创建存储过程createorreplaceproceduretest(var_name_lintype,VaJname_2outtype)as-声明变量(变量名变量类型)begin-存储过程的执行体endtest;打印出输入的时间信息Egcreateorreplaceproceduretest(workDateinDate)isbegindbms_output.putline(Theinputdateis:Ilto_date(WOrkDate,yyyy-mm-dd);endtest;2、变量赋值变量名：=值；E.g：createorreplaceproceduretest(workDateinDate)isxnumber(4,2);beginx:=1;endtest;3、判断语句：if比较式thenbeginend;endif;Egcreateorreplaceproceduretest(xinnumber)isbeginifX>0thenbeginx:=0-x;end;endif;ifx=0thenbeginx:=1;end;endif;endtest;4、For循环For.in.1.OOP-执行语句end1.OOP;(1)循环遍历游标createorreplaceproceduretest()asCursorcursorisselectnamefromstudent;namevarchar(20);beginfornameincursor1.OOPbegindbms_output.putline(name);end;end1.OOP;endtest;循环遍历数组createorreplaceproceduretest(varArrayinmyPackage.TestArray)as-(输入参数VarAiTay是自定义的数组类型，定义方式见标题6)inumber;begini：=1；-存储过程数组是起始位置是从1开始的，与java、C、C+等语言不同。由于在Oracle中本是没有数组的概念的，数组其实就是一张-表(Table),每个数组元素就是表中的一个记录，所以遍历数组时就相称于从表中的第一条记录开始遍历foriin1.varArray.count1.OOPdbms_output.putline(TheNo.irecordinvarArrayis:varArray(i);end1.OOP;endtest;5、While循环while条件语句1.OoPbeginend;end1.OOP;Egcreateorreplaceproceduretest(iinnumber)asbeginwhilei<101.OOPbeginend;end1.OOP;endtest;6、数组一方面明确一个概念：OraCle中本是没有数组的概念的，数组其实就是一张表(Table),每个数组元素就是表中的一个记录。使用数组时，用户可以使用OraCle已经定义好的数组类型，或可根据自己的需要定义数组类型。使用OraCIe自带的数组类型Xarray;-使用时需要需要进行初始化已g：createorreplaceproceduretest(youtarray)isxarray;beginx:=newarray();y：=;endtest;自定义的数组类型(自定义数据类型时，建议通过创建PaCkage的方式实现，以便于管理)E.g(自定义使用参见标题4.2)createorreplacepackagemyPackageisPublictypedeclarationstypeinfoisrecord(namevarchar(20),ynumber);typeTestArrayistableofinfoindexbybinary-integer;-此处声明了一个TestArray的类型数据，其实其为一张存储Info数据类型的Table而已，及TeStArray就是一张表，有两个字段，一个是name,一个是y。需要注意的是此处使用了Indexbybinaryjnteger编制该TabIe的索引项，也可以不写，直接写成：typeTestArrayistableofinfo,假如不写的话使用数组时就需要进行初始化：varArraymyPackage.TestArray;varArray:=newmyPackage.TestArray();endTestArray;7.游标的使用OraCIe中CiIrSOr是非常有用的，用于遍历临时表中的查询结果。其相关方法和属性也很多，现仅就常用的用法做一二介绍：(I)Cursor型游标(不能用于参数传递)createorreplaceproceduretest()iscusor-lCursorisselectstd-namefromstudentwhere-Cursor的使用方式1cursor_2Cursor;beginselectclass_nameintocursor_2fromclasswhere-Cursor的使用方式2可使用Forxincursor1.OOP.end1.OOP;来实现对CUrSOr的遍历endtest;(2)SYS.REFCURSOR型游标，该游标是Orade以预先定义的游标，可作出参数进行传递createorreplaceproceduretest(rsCursoroutSYS_REFCURSOR)iscursorSYS_REFCURSOR;namevarhcar(20);beginOPENcursorFORselectnamefromstudentwhere.SYS_REFCURSOR只能通过OPEN方法来打开和赋值1.OOPfetchcursorintonameSYS_REFCURSOR只能通过fetchinto来打开和遍历exitwhencursor%NOTFOUND;-SYS.REFCURSOR中可使用三个状态属性：-%NoTFOUND(未找到记录信息)%FOUND(找到记录信息)-%R0WC0UNT(然后当前游标所指向的行位置)dbms_output.putline(name);end1.OOP;rsCursor:=cursor;endtest;卜.面写一个简朴的例子来对以上所说的存储过程的用法做一个应用:现假设存在两张表，一张是学生成绩表(StUdnet),字段为：Stdld,math,article,Ianguage,music,sport,total,average,step一张是学生课外成绩表(OUt_school),字段为:StdId,parctice,commenl通过存储过程自动计算出每位学生的总成绩和平均成绩，同时，假如学生在课外课程中获得的评价为A,就在总成绩上加20分。createorreplaceprocedureautocomputer(stepinnumber)isrsCursorSYS_REFCURSOR;CommentArraymyPackage.myArray;mathnumber;articlenumber;languagenumber;musicnumber;sportnumber;totalnumber;averagenumber;StdIdvarchar(30);recordmyPackage.stdlnfo;inumber;begini:=1;get_comment(COmmentArray);-调用名为get_comment()的存储过程获取学生课外评分信息OPENrsCursorforselectstdld,math,article,language,music,sportfromstudenttwheret.step=step;1.OOPfetchrsCursorintostdld,math,article,language,music,sport;exitwhenrsCursor%NOTFOUND;total:=math+article+language+music+sport;mentArray.count1.OOPrecord:=commentArray(i);ifstdld=record.stdldthenbeginment='A'thenbegintotal:=total+20;gotonext;-使用goto跳出for循环end;endif;end;endif;end1.OOP;<<continue>>average:=total/5;updatestudenttsett.total=totaland(.average=averagewheret.stdld=stdld;end1.OOP;end;endautocomputer;一取得学生评论信息的存储过程createorreplaceprocedureget_comment(commentArrayoutmyPackage.myArray)isrsSYS_REFCURSOR；recordmyPackage.stdlnfo;stdldvarchar(30);commentvarchar(l);inumber;beginopenrsforselectstdld,commentfromout_schooli:=1;1.OOPfetchrsintostdld,comment;exitwhenrs%NOTFOUND;record.stdld:=stdld;ment:=comment;recommentArray(i):=record;i:=i+1;end1.OOP;endget_comment;一定义数组类型myArraycreateorreplacepackagemyPackageisbegintypeStdInfoisrecord(stdldvarchar(30),commentvarchar(l);typemyArrayistableofStdInfoindexbybinary_integer;endmyPackage;项目中有涉及存储过程对字符串的解决，所以就将在网上查找到的资料汇总，做一个信息拼接式的总结。以下信息均来自互联网，贴出来一则自己保存以待以后使用，一则供大家分享。字符函数返回字符值这些函数全都接受的是字符族类型的参数(CHR除外)并且返回字符值.除r特别说明的之外,这些函数大部分返回VARCHAR2类型的数值.字符函数的返回类型所受的限制和基本数据库类型所受的限制是相同的。字符型变量存储的最大值：VARCHAR2数值被限制为2023字符(ORAe1.E8中为4000字符)CHAR数值被限制为255字符(在ORAC1.E8中是2023)long类型为2GBCIOb类型为4GB1、CHR语法：chr(x)功能：返回在数据库字符集中与X拥有等价数值的字符。CHR和ASCH是一对反函数。通过CHR转换后的字符再通过ASCI
