oracle存储过程超详细使用手册

Oracle存储过程总结1、创建存储过程createorreplaceproceduretest(var_name_1intype,var_name_2outtype)as--声明变量(变量名变量类型)begin--存储过程的执行体endtest;打印出输入的时间信息E.g:createorreplaceproceduretest(workDateinDate)isbegindbms_output.putline(Theinputdateis:||to_date(workDate,yyyy-mm-dd));endtest;2、变量赋值变量名:=值;E.g：createorreplaceproceduretest(workDateinDate)isxnumber(4,2);beginx:=1;endtest;3、判断语句:if比较式thenbeginend;endif;E.gcreateorreplaceproceduretest(xinnumber)isbeginifx0thenbeginx:=0-x;end;endif;ifx=0thenbeginx:=1;end;endif;endtest;4、For循环For...in...LOOP--执行语句endLOOP;(1)循环遍历游标createorreplaceproceduretest()asCursorcursorisselectnamefromstudent;namevarchar(20);beginfornameincursorLOOPbegindbms_output.putline(name);end;endLOOP;endtest;(2)循环遍历数组createorreplaceproceduretest(varArrayinmyPackage.TestArray)as--(输入参数varArray是自定义的数组类型，定义方式见标题6)inumber;begini:=1;--存储过程数组是起始位置是从1开始的，与java、C、C++等语言不同。因为在Oracle中本是没有数组的概念的，数组其实就是一张--表(Table),每个数组元素就是表中的一个记录，所以遍历数组时就相当于从表中的第一条记录开始遍历foriin1..varArray.countLOOPdbms_output.putline(TheNo.||i||recordinvarArrayis:||varArray(i));endLOOP;endtest;5、While循环while条件语句LOOPbeginend;endLOOP;E.gcreateorreplaceproceduretest(iinnumber)asbeginwhilei10LOOPbegini:=i+1;end;endLOOP;endtest;6、数组首先明确一个概念：Oracle中本是没有数组的概念的，数组其实就是一张表(Table),每个数组元素就是表中的一个记录。使用数组时，用户可以使用Oracle已经定义好的数组类型，或可根据自己的需要定义数组类型。(1)使用Oracle自带的数组类型xarray;--使用时需要需要进行初始化e.g:createorreplaceproceduretest(youtarray)isxarray;beginx:=newarray();y:=x;endtest;(2)自定义的数组类型(自定义数据类型时，建议通过创建Package的方式实现，以便于管理)E.g(自定义使用参见标题4.2)createorreplacepackagemyPackageis--Publictypedeclarationstypeinfoisrecord(namevarchar(20),ynumber);typeTestArrayistableofinfoindexbybinary_integer;--此处声明了一个TestArray的类型数据，其实其为一张存储Info数据类型的Table而已，及TestArray就是一张表，有两个字段，一个是name，一个是y。需要注意的是此处使用了Indexbybinary_integer编制该Table的索引项，也可以不写，直接写成：typeTestArrayistableofinfo，如果不写的话使用数组时就需要进行初始化：varArraymyPackage.TestArray;varArray:=newmyPackage.TestArray();endTestArray;7.游标的使用Oracle中Cursor是非常有用的，用于遍历临时表中的查询结果。其相关方法和属性也很多，现仅就常用的用法做一二介绍：(1)Cursor型游标(不能用于参数传递)createorreplaceproceduretest()iscusor_1Cursorisselectstd_namefromstudentwhere...;--Cursor的使用方式1cursor_2Cursor;beginselectclass_nameintocursor_2fromclasswhere...;--Cursor的使用方式2可使用ForxincursorLOOP....endLOOP;来实现对Cursor的遍历endtest;(2)SYS_REFCURSOR型游标，该游标是Oracle以预先定义的游标，可作出参数进行传递createorreplaceproceduretest(rsCursoroutSYS_REFCURSOR)iscursorSYS_REFCURSOR;namevarhcar(20);beginOPENcursorFORselectnamefromstudentwhere...--SYS_REFCURSOR只能通过OPEN方法来打开和赋值LOOPfetchcursorintoname--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)isrsCursorSYS_REFCURSOR;commentArraymyPackage.myArray;mathnumber;articlenumber;languagenumber;musicnumber;sportnumber;totalnumber;averagenumber;stdIdvarchar(30);recordmyPackage.stdInfo;inumber;begini:=1;get_comment(commentArray);--调用名为get_comment()的存储过程获取学生课外评分信息OPENrsCursorforselectstdId,math,article,language,music,sportfromstudenttwheret.step=step;LOOPfetchrsCursorintostdId,math,article,language,music,sport;exitwhenrsCursor%NOTFOUND;total:=math+article+language+music+sport;foriin1..commentArray.countLOOPrecord:=commentArray(i);ifstdId=record.stdIdthenbeginifrecord.comment='A'thenbegintotal:=total+20;gotonext;--使用goto跳出for循环end;endif;end;endif;endLOOP;continueaverage:=total/5;updatestudenttsett.total=totalandt.average=averagewheret.stdId=stdId;endLOOP;end;endautocomputer;--取得学生评论信息的存储过程createorreplaceprocedureget_comment(commentArrayoutmyPackage.myArray)isrsSYS_REFCURSOR；recordmyPackage.stdInfo;stdIdvarchar(30);commentvarchar(1);inumber;beginopenrsforselectstdId,commentfromout_schooli:=1;LOOPfetchrsintostdId,comment;exitwhenrs%NOTFOUND;record.stdId:=stdId;record.comment:=comment;recommentArray(i):=record;i:=i+1;endLOOP;endget_comment;--定义数组类型myArraycreateorreplacepackagemyPackageisbegintypestdInfoisrecord(stdIdvarchar(30),commentvarchar(1));typemyArrayistableofstdInfoindexbybinary_integer;endmyPackage;项目中有涉及存储过程对字符串的处理，所以就将在网上查找到的资料汇总，做一个信息拼接式的总结。以下信息均来自互联网，贴出来一则自己保存以待以后使用，一则供大家分享。字符函数——返回字符值这些函数全都接收的是字符族类型的参数(CHR除外)并且返回字符值.除了特别说明的之外,这些函数大部分返回VARCHAR2类型的数值.字符函数的返回类型所受的限制和基本数据库类型所受的限制是相同的。字符型变量存储的最大值：VARCHAR2数值被限制为2000字符(ORACLE8中为4000字符)CHAR数值被限制为255字符(在ORACLE8中是2000)long类型为2GBClob类型为4GB1、CHR语法:chr(x)功能：返回在数据库字符集中与X拥有等价数值的字符。CHR和ASCII是一对反函数。经过CHR转换后的字符再经过ASCII转换又得到了原来的字符。使用位置：过程性语句和SQL语句。2、CONCAT语法：CONCAT（string1,string2）功能：返回string1，并且在后面连接string2。使用位置：过程性语句和SQL语句。3、INITCAP语法：INITCAP（string）功能：返回字符串的每个单词的第一个字母大写而单词中的其他字母小写的string。单词是用.空格或给字母数字字符进行分隔。不是字母的字符不变动。使用位置：过程性语句和SQL语句。4、LTRIM语法：LTRIM（string1,string2）功能：返回删除从左边算起出现在string2中的字符的string1。String2被缺省设置为单个的空格。数据库将扫描string1，从最左边开始。当遇到不在string2中的第一个字符，结果就被返回了。LTRIM的行为