《oracle存储过程使用手册.docx》由会员分享，可在线阅读，更多相关《oracle存储过程使用手册.docx（65页珍藏版）》请在课桌文档上搜索。
1、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：createorreplaceproceduret
2、est(workDateinDate)isxnumber(4,2);beginx:=1;endtest;3、判断语句：if比较式thenbeginend;endif;Egcreateorreplaceproceduretest(xinnumber)isbeginifX0thenbeginx:=0-x;end;endif;ifx=0thenbeginx:=1;end;endif;endtest;4、For循环For.in.1.OOP-执行语句end1.OOP;(1)循环遍历游标createorreplaceproceduretest()asCursorcursorisselectnamefrom
3、student;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),每个数组元素就
4、是表中的一个记录，所以遍历数组时就相称于从表中的第一条记录开始遍历foriin1.varArray.count1.OOPdbms_output.putline(TheNo.irecordinvarArrayis:varArray(i);end1.OOP;endtest;5、While循环while条件语句1.OoPbeginend;end1.OOP;Egcreateorreplaceproceduretest(iinnumber)asbeginwhilei101.OOPbeginend;end1.OOP;endtest;6、数组一方面明确一个概念：OraCle中本是没有数组的概念的，数组其实就
5、是一张表(Table),每个数组元素就是表中的一个记录。使用数组时，用户可以使用OraCle已经定义好的数组类型，或可根据自己的需要定义数组类型。使用OraCIe自带的数组类型Xarray;-使用时需要需要进行初始化已g：createorreplaceproceduretest(youtarray)isxarray;beginx:=newarray();y：=;endtest;自定义的数组类型(自定义数据类型时，建议通过创建PaCkage的方式实现，以便于管理)E.g(自定义使用参见标题4.2)createorreplacepackagemyPackageisPublictypedeclara
6、tionstypeinfoisrecord(namevarchar(20),ynumber);typeTestArrayistableofinfoindexbybinary-integer;-此处声明了一个TestArray的类型数据，其实其为一张存储Info数据类型的Table而已，及TeStArray就是一张表，有两个字段，一个是name,一个是y。需要注意的是此处使用了Indexbybinaryjnteger编制该TabIe的索引项，也可以不写，直接写成：typeTestArrayistableofinfo,假如不写的话使用数组时就需要进行初始化：varArraymyPackage.Te
7、stArray;varArray:=newmyPackage.TestArray();endTestArray;7.游标的使用OraCIe中CiIrSOr是非常有用的，用于遍历临时表中的查询结果。其相关方法和属性也很多，现仅就常用的用法做一二介绍：(I)Cursor型游标(不能用于参数传递)createorreplaceproceduretest()iscusor-lCursorisselectstd-namefromstudentwhere-Cursor的使用方式1cursor_2Cursor;beginselectclass_nameintocursor_2fromclasswhere-C
8、ursor的使用方式2可使用Forxincursor1.OOP.end1.OOP;来实现对CUrSOr的遍历endtest;(2)SYS.REFCURSOR型游标，该游标是Orade以预先定义的游标，可作出参数进行传递createorreplaceproceduretest(rsCursoroutSYS_REFCURSOR)iscursorSYS_REFCURSOR;namevarhcar(20);beginOPENcursorFORselectnamefromstudentwhere.SYS_REFCURSOR只能通过OPEN方法来打开和赋值1.OOPfetchcursorintonameS
9、YS_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,
10、total,average,step一张是学生课外成绩表(OUt_school),字段为:StdId,parctice,commenl通过存储过程自动计算出每位学生的总成绩和平均成绩，同时，假如学生在课外课程中获得的评价为A,就在总成绩上加20分。createorreplaceprocedureautocomputer(stepinnumber)isrsCursorSYS_REFCURSOR;CommentArraymyPackage.myArray;mathnumber;articlenumber;languagenumber;musicnumber;sportnumber;totalnum
11、ber;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;exitwhenrsCur
12、sor%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;average:=total/5;updatestudenttsett.total=totaland(.average=averagewheret.stdl
13、d=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%NO
14、TFOUND;record.stdld:=stdld;ment:=comment;recommentArray(i):=record;i:=i+1;end1.OOP;endget_comment;一定义数组类型myArraycreateorreplacepackagemyPackageisbegintypeStdInfoisrecord(stdldvarchar(30),commentvarchar(l);typemyArrayistableofStdInfoindexbybinary_integer;endmyPackage;项目中有涉及存储过程对字符串的解决，所以就将在网上查找到的资料汇总
15、，做一个信息拼接式的总结。以下信息均来自互联网，贴出来一则自己保存以待以后使用，一则供大家分享。字符函数返回字符值这些函数全都接受的是字符族类型的参数(CHR除外)并且返回字符值.除r特别说明的之外,这些函数大部分返回VARCHAR2类型的数值.字符函数的返回类型所受的限制和基本数据库类型所受的限制是相同的。字符型变量存储的最大值：VARCHAR2数值被限制为2023字符(ORAe1.E8中为4000字符)CHAR数值被限制为255字符(在ORAC1.E8中是2023)long类型为2GBCIOb类型为4GB1、CHR语法：chr(x)功能：返回在数据库字符集中与X拥有等价数值的字符。CHR和
16、ASCH是一对反函数。通过CHR转换后的字符再通过ASCII转换又得到了本来的字符。使用位置：过程性语句和SQ1.语句。2、CONCAT语法：CONCAT(Stringl,string2)功能：返回String1,并且在后面连接String2。使用位置：过程性语句和SQ1.语句。3、INITCAP语法：INITCAP(string)功能：返回字符串的每个单词的第一个字母大写而单词中的其他字母小写的Slring。单词是用.空格或给字母数字字符进行分隔。不是字母的字符不变动。使用位置：过程性语句和SQ1.语句。4、1.TRIM语法：1.TRIM(stringl,string2)功能：返回删除从左边
17、算起出现在SIring2中的字符的String1。String2被缺省设立为单个的空格。数据库将扫描String1,从最左边开始。当碰到不在string2中的第一个字符，结果就被返回了。1.TRIM的行为方式与RTRIM很相似。使用位置：过程性语句和SQ1.语句。5、Nlsjnitcap语法：N1.SNITCAP(String1.nlsparams)功能：返回字符串每个单词第一个字母大写而单词中的其他字母小写的String,nlsparams指定了不同于该会话缺省值的不同排序序列。假如不指定参数，则功能和INlTCAP相同。NISParamS可以使用的形式是：N1.S-SORT=SOrf这里s
18、ort制订了一个语言排序序列。使用位置：过程性语句和SQ1.语句。6、N1.SJOWER语法：N1.S_1.OWER(string,nlsparams)功能：返回字符串中的所有字母都是小写形式的Slring。不是字母的字符不变。Nlsparams参数的形式与用途和N1.SNrrCAP中的nlsparams参数是相同的。假如nlsparams没有被包含，那么N1.S_1.OWER所作的解决和1.oWER相同。7、N1.S.UPPER语法：nls_upper(string,nlsparams)功能：返回字符串中的所有字母都是大写的形式的String。不是字母的字符不变。nlsparams参数的形式
19、与用途和Nlsjnitcap中的相同。假如没有设定参数，则N1.S.UPPER功能和UPPER相同。使用位置：过程性语句和SQ1.语句。8、REP1.ACE语法：REP1.ACE(string,search_str,replace_str)功能：把string中的所有的子字符串SearCh_str用可选的replace-str替换，假如没有指定replace_str,所有的String中的子字符串SearCh_str都将被删除。REP1.ACE是TRANS1.ATE所提供的功能的一个子集。使用位置：过程性语句和SQ1.语句。9、RPAD语法:RPAD(string1.x1.string2)功能
20、：返回在X字符长度的位置上插入一个string2中的字符的Stringlo假如string2的长度要比X字符少，就按照需要进行复制。假如Slring2多于X字符，则仅Slringl前面的X各字符被使用。假如没有指定Slring2,那么使用空格进行填充。X是使用显示长度可以比字符串的实际长度要长。RPAD的行为方式与1.PAD很相似，除了它是在右边而不是在左边进行填充。10、RTRIM语法:RTRlM(stringl,string2)功能：返回删除从右边算起出现在Slringl中出现的字符siring2.Slring2被缺省设立为单个的空格.数据库将扫描SlringI,从右边开始.当遇到不在st
21、ring2中的第一个字符,结果就被返回了RTRIM的行为方式与1.TRIM很相似.使用位置：过程性语句和SQ1.语句。11、SOUNDEX语法：SOUNDEX(string)功能：返回string的声音表达形式.这对于比较两个拼写不同但是发音类似的单词而言很有帮助.使用位置：过程性语句和SQ1.语句。12、SUBSTR语法：SUBSTR(string,a,b)功能：返回从字母为值a开始b个字符长的string的一个子字符串.假如a是0,那么它就被认为从第一个字符开始.假如是正数,返回字符是从左边向右边进行计算的.假如b是负数,那么返回的字符是从string的末尾开始从右向左进行计算的.假如b不
22、存在,那么它将缺省的设立为整个字符串.假如b小于1,那么将返回NU1.1.假如a或b使用了浮点数,那么该数值将在解决进行以前一方面被却为一个整数.13、TRANS1.ATE语法：TRANS1.ATE(string,from_str,to_str)功能：返回将所出现的from_str中的每个字符替换为to_str中的相应字符以后的String.TRanslate是replace所提供的功能的一个超集.假如from_str比to_str长,那么在from_str中而不在to_str中而外的字符将从string中被删除,由于它们没有相应的替换字符.SIr不能为空QraCIe把空字符串认为是NU1.1
23、.,并且假如TRANS1.ATE中的任何参数为NU1.1.,那么结果也是NU1.1.使用位置：过程性语句和SQ1.语句。14、UPPER语法：UPPER(string)功能：返回大写的String.不是字母的字符不变.假如string是CHAR数据类型的,那么结果也是CHAR类型的.假如string是VARCHAR2类型的,那么结果也是VARCHAR2类型的.使用位置：过程性语句和SQ1.语句。字符函数返回数字这些函数接受字符参数回数字结果.参数可以是CHAR或者是VARCHAR2类型的.尽管实际下许多结果都是整数值,但是返回结果都是简朴的NUMBER类型的,没有定义任何的精度或刻度范围.16
24、、ASCII语法：ASCII(string)功能：数据库字符集返回String的第一个字节的十进制表达.请注意该函数仍然称作为ASCH.尽管许多字符集不是7位Ascilchr和ascii是互为相反的函数CHR得到给定字符编码的响应字符.ASCII得到给定字符的字符编码.使用位置：过程性语句和SQ1.语句。17、INSTR语法：INSTR(string1,string2a,b)功能：得到在Stringl中包含String2的位置.Stringl时从左边开始检查的,开始的位置为a,假如a是一个负数,那么Stringl是从右边开始进行扫描的.第b次出现的位置将被返回.a和b都缺省设立为1,这将会返回
25、在Stringl中第一次出现string2的位置.假如string2在a和b的规定下没有找到,那么返回0.位置的计算是相对于Stringl的开始位置的,不管a和b的取值是多少.使用位置：过程性语句和SQ1.语句。18、INSTRB语法:INSTRB(Stringl,string2a,b)功能：和INSTR相同,只是操作的对参数字符使用的位置的是字节.19、1.ENGTH语法：1.ENGTH(string)功能：返回String的字节单位的长度.CHAR数值是填充空格类型的,假如String由数据类型CHAR,它的结尾的空格都被计算到字符串长度中间.假如string是NU1.1.,返回结果是NU
26、1.1.,而不是0.使用位置：过程性语句和SQ1.语句。20、1.ENGTHB语法：1.ENGTHB(string)功能：返回以字节为单位的string的长度.对于单字节字符集1.ENGTHB和1.ENGTH是同样的.使用位置：过程性语句和SQ1.语句。21、N1.SSORT语法：N1.SSORT(string,nlsparams)功能：得到用于排序string的字符串字节.所有的数值都被转换为字节字符串,这样在不同数据库之间就保持了,致性.Nlsparams的作用和N1.SNlTCAP中的相同.假如忽略参数,会话使用缺省排序.使用位置：过程性语句和SQ1.语句。oracle存储过程的基本语法
27、1.基本结构CREATEORREP1.ACEPROCEDURE存储过程名字（参数1INNUMBER,参数2INNUMBER）IS变量IINTEGER:=0;变量2DATE;BEGINEND存储过程名字2.SE1.ECTINTOSTATEMENT将SeIeCt查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常（假如没有记录抛出NO_DATA_FOUND）例子：SE1.ECTcol1,co12into变量1,变量2FROMtypestructwherexxx;EXCEPTIONWHENNO_DATA_FOUNDTHENxxxx;END;3.IF判断IFV_TEST
28、=1THENBEGINdosomethingEND;ENDIF;4 .while循环WHI1.EV_TEST=11.OOPXXXXEND;END1.OOP;5 .变量赋值V_TEST:=123;6 .用forin使用cursorISCURSORcurISSE1.ECT*FROMxxx;BEGINFORcur_resultincur1.OOPBEGINV_SUM:=CUJreSUlt.列名l+cujresult.列名2END;END1.OOP;END;7 .带参数的cursorCURSORC_USER(C_IDNUMBER)ISSE1.ECTNAMEFROMUSERWHERETYPEID=C_I
29、D;OPENJUSER(变量值)；1.OOPFETCHC_USERINTOV_NAME;EXITFETCHC.USER%NOTFOUND;dosomethingEND1.OOP;C1.OSECJJSER;8 .用pl/sqldeveloperdebug连接数据库后建立一个TestWINDOW在窗口输入调用SP的代码,F9开始debug,CTR1.+N单步调试关于oracle存储过程的若干问题备忘1.在oracle中，数据表别名不能加as,如:selecta.appnamefromappinfoa;-对的selecta.appnamefromappinfoasa;-错误也许，是怕和oracle中
30、的存储过程中的关键字as冲突的问题吧2 .在存储过程中，select某一字段时，后面必须紧跟into,假如SeIeCt整个记录，运用游标的话就另当别论了。selectaf.keynodeintoknfromAPPFOUNDATIONafwhereaf.appidaidandaf.foundalionid-fid;-有into,对的编译selectaf.keynodefromAPPFOUNDATIONafwhereaf.appidaidandaf.foundalionid-fid;-没有into,编译报错，提醒：CompilationError:P1.S00428:anINTOclauseise
31、xpectedinthisSE1.ECTstatement3 .在运用SeleCtinto语法时，必须先保证数据库中有该条记录，否则会报出n。datafOUlKr异常。可以在该语法之前，先运用SeIeCICOUm(*)from查看数据库中是否存在该记录，假如存在，再运用select.into.4 .在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运营阶段会报错selectkeynodeintoknfromAPPFOUNDATIONwhereappid-aid：foundationidfid;-对的运营selectaf.keynodeintoknfromAPPFOUNDATION
32、afwhereppi-uppidandal.lohlaiioil-ondaiionid;-运营阶段报错，提醒ORA01422:exactfetchreturnsmorethanrequestednumberofrows5 .在存储过程中，关于出现null的问题假设有一个表A,定义如下:createtableA(idvarchar2(50)primarykeynotnull,vcountnumber(8)notnull,bidvarchar2(50)notnull-外键)；假如在存储过程中，使用如下语句：selectsum(vcount)intofcountfromAwherebid=xxxxx
33、x;假如A表中不存在bid=xxxxxx的记录，则fcount=null(即使fcount定义时设立了默认值，如：fcountnumber(8):=0仍然无效，fcount还是会变成null),这样以后使用fcount时就也许有问题,所以在这里最佳先判断一下：iffcountisnullthenIcounti=O;endif;这样就一切Ok了。6 .Hibernate调用OraCIe存储过程this.pnumberManager.getHibernateTemplate().execute(newHibernateCallbackOpublicObjectdoInHibernate(Sessi
34、onsession)throwsHibemateException,SQ1.ExceptionCallableStatementcs=session.connection().prepareCall(callmodifyapppnumber_remain(?),);cs.setString(l,Foundationid);cs.execute();Ireturnnull;卜)1.);oracle存储过程语法总结及练习- 1.存储过程之ifclear;createorreplaceproceduremydel(in_aininteger)asbeginifin_a100thendbms_oUtP
35、Ut.put_Jine(小于100.);elsifin_a301;endloop;end;setserveroutputon;beginmydel(2);end;-1.存储过程之loop2clear;createorreplaceproceduremydel(in_aininteger)asainteger;begina:=0;whilea300loopdbms_output.put_line（八）;a:=a+l;endloop;end;setsen,eroutputon;beginmydel(2);end;.存储过程之loop3clear;createorreplaceproceduremy
36、del(in_aininteger)asainteger;beginforain0.300loopdbms_output.put_line（八）;endloop;end;/setsen,eroutputon;beginmydel(2);end;clear;selectename,cc:=(casewhencomm=nullthensal*12;else(sal+comm)*12;endcasefromemporderbysalpersal;clear;createorreplaceproceduregetstudentcomments(1.studentidinint,o_commentsou
37、tvarchar)asexams_satint;avg_markint;tmp_commentsvarchar(100);beginselectcount(examid)intoexams_satfromstudentexamwhereSludentid=1.studentid;ifexams_sat=0thentmp_comments:=n/a-thisstudentdidnotattendtheexam!;elseselectavg(mark)intoavg_markfromstudentexamwhereStudentid=1.studentid;casewhenavg_mark50th
38、entmp_comments:=verybad;whenavg_mark60thentmp-comments=bad,;whenavg_mark70thentmp-comments=good,;endcase;endif;o_comments:=tmp_comments;end;/setserveroutputon;declarements%type;begingetstudentcomments(8,pp);dbms_output.put_line(pp);end;deletefromempwhereempno6000;clear;createorreplaceprocedureinsert
39、data(in_numininteger)asmyNumintdefault0;emp_noemp.empno%type:=1000;beginwhilemyNumin_numloopinsertintoempvalues(emp_no,hui|myNum,coder,7555,current_date,8000,6258,30);emp_no:=emp_no+1;myNum:=myNum+1;endloop;end;/setserveroutputon;begininserldata(10);end;/select*fromemp;clear;selectstudentname,avcrageMark,casewhenaverageMark60then不及格whenaverageMark7Othen考得好，whenaverageMark(selectsalfromempwhereename-SMITH);3selecta.ename,(selectenamefromempbwhereb.empno=a.mgr)asbossnamefromempa;4selecta.enamefromempawherea.hiredatel500;8selectenamefromempwheredeptno=(select
