oracle存储过程详细的使用手册.docx
《oracle存储过程详细的使用手册.docx》由会员分享，可在线阅读，更多相关《oracle存储过程详细的使用手册.docx（53页珍藏版）》请在冰点文库上搜索。
oracle存储过程详细的使用手册
Oracle存储过程总结
1、创建存储过程
createorreplaceproceduretest（var_name_1intype,var_name_2outtype）as
--声明变量（变量名变量类型）
begin
--存储过程的执行体
endtest;
打印出输入的时间信息
E.g:
createorreplaceproceduretest（workDateinDate）is
begin
dbms_output.putline（Theinputdateis:
||to_date（workDate,yyyy-mm-dd））;
endtest;
2、变量赋值
变量名:
=值;
E.g：
createorreplaceproceduretest（workDateinDate）is
xnumber（4,2）;
begin
x:
=1;
endtest;
3、判断语句:
if比较式thenbeginend;endif;
E.g
createorreplaceproceduretest（xinnumber）is
begin
ifx>0then
begin
x:
=0-x;
end;
endif;
ifx=0then
begin
x:
=1;
end;
endif;
endtest;
4、For循环
For...in...LOOP
--执行语句
endLOOP;
（1）循环遍历游标
createorreplaceproceduretest（）as
Cursor1cursorisselectnamefromstudent;
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;
5、While循环
while条件语句LOOP
begin
end;
endLOOP;
E.g
createorreplaceproceduretest（iinnumber）as
begin
whilei<10LOOP
begin
i:
=i+1;
end;
endLOOP;
endtest;
6、数组
首先明确一个概念：
Oracle中本是没有数组的概念的，数组其实就是一张表（Table）,每个数组元素就是表中的一个记录。
使用数组时，用户可以使用Oracle已经定义好的数组类型，或可根据自己的需要定义数组类型。
（1）使用Oracle自带的数组类型
xarray;--使用时需要需要进行初始化
e.g:
createorreplaceproceduretest（youtarray）is
xarray;
begin
x:
=newarray（）;
y:
=x;
endtest;
（2）自定义的数组类型（自定义数据类型时，建议通过创建Package的方式实现，以便于管理）
E.g（自定义使用参见标题4.2）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;
7.游标的使用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;
项目中有涉及存储过程对字符串的处理，所以就将在网上查找到的资料汇总，做一个信息拼接式的总结。
以下信息均来自互联网，贴出来一则自己保存以待以后使用，一则供大家分享。
字符函数——返回字符值
这些函数全都接收的是字符族类型的参数（CHR除外）并且返回字符值.
除了特别说明的之外,这些函数大部分返回VARCHAR2类型的数值.
字符函数的返回类型所受的限制和基本数据库类型所受的限制是相同的。
字符型变量存储的最大值：
VARCHAR2数值被限制为2000字符（ORACLE8中为4000字符）
CHAR数值被限制为255字符（在ORACLE8中是2000）
long类型为2GB
Clob类型为4GB
1、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的行为方式与RTRIM很相似。
使用位置：
过程性语句和SQL语句。
5、NLS_INITCAP
语法：
NLS_INITCAP（string[,nlsparams]）
功能：
返回字符串每个单词第一个字母大写而单词中的其他字母小写的string，nlsparams
指定了不同于该会话缺省值的不同排序序列。
如果不指定参数，则功能和INITCAP相同。
Nlsparams可以使用的形式是：
‘NLS_SORT=sort’
这里sort制订了一个语言排序序列。
使用位置：
过程性语句和SQL语句。
6、NLS_LOWER
语法：
NLS_LOWER（string[,nlsparams]）
功能：
返回字符串中的所有字母都是小写形式的string。
不是字母的字符不变。
Nlsparams参数的形式与用途和NLS_INITCAP中的nlsparams参数是相同的。
如果nlsparams没有被包含，那么NLS_LOWER所作的处理和
LOWER相同。
使用位置；过程性语句和SQL语句。
7、NLS_UPPER
语法：
nls_upper（string[,nlsparams]）
功能：
返回字符串中的所有字母都是大写的形式的string。
不是字母的字符不变。
nlsparams参数的形式与用途和NLS_INITCAP中的相同。
如果
没有设定参数，则NLS_UPPER功能和UPPER相同。
使用位置：
过程性语句和SQL语句。
8、REPLACE
语法：
REPLACE（string，search_str[,replace_str]）
功能：
把string中的所有的子字符串search_str用可选的replace_str替换，如果没有指定replace_str，所有的string中的子字符串
search_str都将被删除。
REPLACE是TRANSLATE所提供的功能的一个子集。
使用位置：
过程性语句和SQL语句。
9、RPAD
语法：
RPAD（string1,x[,string2]）
功能：
返回在X字符长度的位置上插入一个string2中的字符的string1。
如果string2的长度要比X字符少，就按照需要进行复制。
如果string2
多于X字符，则仅string1前面的X各字符被使用。
如果没有指定string2，那么使用空格进行填充。
X是使用显示长度可以比字符串的实际长度
要长。
RPAD的行为方式与LPAD很相似，除了它是在右边而不是在左边进行填充。
使用位置：
过程性语句和SQL语句。
10、RTRIM
语法:
RTRIM（string1,[,string2]）
功能:
返回删除从右边算起出现在string1中出现的字符string2.string2被缺省设置为单个的空格.数据库将扫描string1,从右边开始.当遇
到不在string2中的第一个字符,结果就被返回了RTRIM的行为方式与LTRIM很相似.
使用位置：
过程性语句和SQL语句。
11、SOUNDEX
语法:
SOUNDEX（string）
功能:
返回string的声音表示形式.这对于比较两个拼写不同但是发音类似的单词而言很有帮助.
使用位置：
过程性语句和SQL语句。
12、SUBSTR
语法:
SUBSTR（string,a[,b]）
功能:
返回从字母为值a开始b个字符长的string的一个子字符串.如果a是0,那么它就被认为从第一个字符开始.如果是正数,返回字符是从左
边向右边进行计算的.如果b是负数,那么返回的字符是从string的末尾开始从右向左进行计算的.如果b不存在,那么它将缺省的设置为整个字符
串.如果b小于1,那么将返回NULL.如果a或b使用了浮点数,那么该数值将在处理进行以前首先被却为一个整数.
使用位置：
过程性语句和SQL语句。
13、TRANSLATE
语法:
TRANSLATE（string,from_str,to_str）
功能:
返回将所出现的from_str中的每个字符替换为to_str中的相应字符以后的string.TRANSLATE是REPLACE所提供的功能的一个超集.
如果from_str比to_str长,那么在from_str中而不在to_str中而外的字符将从string中被删除,因为它们没有相应的替换字符.to_str不能为空
.Oracle把空字符串认为是NULL,并且如果TRANSLATE中的任何参数为NULL,那么结果也是NULL.
使用位置：
过程性语句和SQL语句。
14、UPPER
语法:
UPPER（string）
功能:
返回大写的string.不是字母的字符不变.如果string是CHAR数据类型的,那么结果也是CHAR类型的.如果string是VARCHAR2类型的,那么
结果也是VARCHAR2类型的.
使用位置:
过程性语句和SQL语句。
字符函数——返回数字
这些函数接受字符参数回数字结果.参数可以是CHAR或者是VARCHAR2类型的.尽管实际下许多结果都是整数值,但是返回结果都是简单的NUMBER
类型的,没有定义任何的精度或刻度范围.
16、ASCII
语法:
ASCII（string）
功能:
数据库字符集返回string的第一个字节的十进制表示.请注意该函数仍然称作为ASCII.尽管许多字符集不是7位ASCII.CHR和ASCII是互为
相反的函数.CHR得到给定字符编码的响应字符.ASCII得到给定字符的字符编码.
使用位置:
过程性语句和SQL语句。
17、INSTR
语法:
INSTR（string1,string2[a,b]）
功能:
得到在string1中包含string2的位置.string1时从左边开始检查的,开始的位置为a,如果a是一个负数,那么string1是从右边开始进行
扫描的.第b次出现的位置将被返回.a和b都缺省设置为1,这将会返回在string1中第一次出现string2的位置.如果string2在a和b的规定下没有
找到,那么返回0.位置的计算是相对于string1的开始位置的,不管a和b的取值是多少.
使用位置:
过程性语句和SQL语句。
18、INSTRB
语法:
INSTRB（string1,string2[a,[b]]）
功能:
和INSTR相同,只是操作的对参数字符使用的位置的是字节.
使用位置:
过程性语句和SQL语句。
19、LENGTH
语法:
LENGTH（string）
功能:
返回string的字节单位的长度.CHAR数值是填充空格类型的,如果string由数据类型CHAR,它的结尾的空格都被计算到字符串长度中间.
如果string是NULL,返回结果是NULL,而不是0.
使用位置:
过程性语句和SQL语句。
20、LENGTHB
语法:
LENGTHB（string）
功能:
返回以字节为单位的string的长度.对于单字节字符集LENGTHB和LENGTH是一样的.
使用位置:
过程性语句和SQL语句。
21、NLSSORT
语法:
NLSSORT（string[,nlsparams]）
功能:
得到用于排序string的字符串字节.所有的数值都被转换为字节字符串,这样在不同数据库之间就保持了一致性.Nlsparams的作用和
NLS_INITCAP中的相同.如果忽略参数,会话使用缺省排序.
使用位置:
过程性语句和SQL语句。
oracle存储过程的基本语法
1.基本结构
CREATEORREPLACEPROCEDURE存储过程名字
（
参数1INNUMBER,
参数2INNUMBER
）IS
变量1INTEGER:
=0;
变量2DATE;
BEGIN
END存储过程名字
2.SELECTINTOSTATEMENT
将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条
记录，否则抛出异常（如果没有记录抛出NO_DATA_FOUND）
例子：
BEGIN
SELECTcol1,col2into变量1,变量2FROMtypestructwherexxx;
EXCEPTION
WHENNO_DATA_FOUNDTHEN
xxxx;
END;
...
3.IF判断
IFV_TEST=1THEN
BEGIN
dosomething
END;
ENDIF;
4.while循环
WHILEV_TEST=1LOOP
BEGIN
XXXX
END;
ENDLOOP;
5.变量赋值
V_TEST:
=123;
6.用forin使用cursor
...
IS
CURSORcurISSELECT*FROM
升级会员 