oracle储存过程培训资料Word格式.docx
《oracle储存过程培训资料Word格式.docx》由会员分享，可在线阅读，更多相关《oracle储存过程培训资料Word格式.docx（26页珍藏版）》请在冰豆网上搜索。
interger整型
2.2.3存储的基本结构
1.存储过程包含三部分：
声明，执行部分，异常.
2.3创建储存过程简单命令
2.3.1创建储存过程
1.从Window打开SQL*Plus并且从SQL*Plus登录到你的数据库;
打开skeleton.sql文件.
2.在SQL>
命令提示符下输入以下命令：
SQL>
@skeleton
注释:
（SQL*Plus装载skeleton.sql文件的内容到SQL*Plus缓冲区,
并且执行SQL*Plus语句;
SQL*Plus会通知你存储过程已经被成功地创建）
3.写一个存储过程,实例:
CREATEORREPLACEPROCEDUREskeleton
IS
BEGIN
DBMS_OUTPUT.PUT_LINE（'
HelloWorld!
'
）;
END;
2.3.2运行一个存储过程命令
EXECUTEskeleton;
注释（SQL*Plus输出一下信息确信存储过程成功执
即PL/SQLproceduresuccessfullycompleted）.
2.3.3查看储存打印信息
1.在SQL*Plus命令行提示符,键入:
SETSERVEROUTPUTON
2.再次敲入SQL>
EXECUTEskeleton即可.
注释:
查看存储过程中的打印语句信息,必须实行以上命令.
2.3.4删除一个存储过程
命令:
SQL>
DROPPROCEDUREskeleton;
2.4存储过程
2.4.1存储过程基本结构
CREATEORREPLACEPROCEDURE存储过程名字
（
参数1INNUMBER,
参数2INNUMBER
）IS
变量1INTEGER:
=0;
变量2DATE;
BEGIN
END存储过程名字;
2.4.2存储过程的基本语法
2.4.2.0LOOP循环
Counter:
LOOP
counter:
=counter+1;
EXITWHENcounter=5;
ENDLOOP;
2.4.2.1IF判断
IFstr1>
str2then
result:
=1;
ELSIFstr2>
str1THEN
=-1;
ELSE
=0;
ENDIF
2.4.2.2while循环
Counter:
WHILEcounter<
6LOOP
=counter+1;
2.4.2.3变量定义及赋值
declare
realsalemp.sal%type;
realnamevarchar（40）;
realjobvarchar（40）;
Pricenumber（5,2）;
Product_idinterger;
realjob:
=‘work’;
Product_id:
=100001;
realname:
='
Brunhilda'
;
Price:
=3.1415;
this_day:
=TODAY;
2.4.2.4for使用
1,方式一:
FORcur_resultREVERSE1..5LOOP
DBMS_OUTPUT.PUT_LINE（cur_result）;
ENDLOOP;
2.方式二:
IS
CURSORcurISSELECT*FROMxxx;
FORcur_resultincurLOOP
V_SUM:
=cur_result.列名1+cur_result.列名2
2.4.2.5带参数的cursor
CURSORC_USER（C_IDNUMBER）IS
SELECTNAMEFROMUSERWHERETYPEID=C_IDNUMBER;
OPENC_USER（变量值）;
LOOP
FETCHC_USERINTOV_NAME;
EXITFETCHC_USER%NOTFOUND;
…………………….
CLOSEC_USER;
与存储过程和函数相似,可以将参数传递给游标并在查询中使用.
2.4.2.6存储过程操作符
+
-
*
/
||合并如：
sp_str1=“ASD”||”ERT”,则：
sp_str1=“ASDERT”
2.4.2.7存储过程结构块
BEGIN
第一步处理；
第二步处理；
第三步处理；
2.4.2.8存储过程异常错误控制
exception
whentoo_many_rowsthen
返回值多于1行'
whenothersthen
在RUNBYPARMETERS过程中出错！
2.4.2.9存储过程游标
FORrecINcur_testLOOP
DBMS_OUTPUT.put_line（rec.p_number）;
DBMS_OUTPUT.put_line（rec.p_name）;
DBMS_OUTPUT.put_line（rec.p_manager）;
DBMS_OUTPUT.put_line（rec.p_client）;
2.4.2.10存储过程条件表达式
存储过程if条件表达式
比较符<
\>
\<
=\>
=\=\<
>
\!
=
ANDORNOT
（NOT）BETWEENAND
（NOT）IN（,,,,）
IS（NOT）NULL
（NOT）LIKE
2.4.2.11存储过程控制语句的跳出
exit
when
eixt
注释:
exit语句可立即结束循环
exitwhen语句是在指定条件下结束循环,并且可以出现在循环代码中
的任何位置.
2.4.2.12存储过程返回值out
Oracle存储过程的返回值,必须在创建一个存储过程时定义.
2.4.2.13存储过程的执行
Executespdata;
Executespdata（var1）;
Executespdata（var1=值1,var2=值,2）;
2.4.2.14隐形游标
1.dml语句是指:
insert、update、delete和locktable的操作.
2.对dml操作会产生隐式游标.
3.隐式游标只使用sql%found,sql%notfound,sql%rowcount三个属性.
4.sql%found,sql%notfound是布尔值，sql%rowcount是整数值。
5.sql%found为true,sql%notfound为false,
6.在执行任何dml语句之前,sql%rowcount的值都是null
7.%ROWCOUNT返回当前位置为止游标读取的记录行数.
2.4.2.15%type属性
1.在pl/sql中可以将变量和常量声明为内建或用户定义的数据类型,
以引用一个列名,同时继承他的数据类型和大小.
delcare
v_anumber（5）:
=10;
v_bv_a%type:
=15;
v_cv_a%type;
2.5开发一个储存过程
2.5.1不带参数的储存过程
1.如果没有orreplace语句，则仅仅是新建一个存储过程,如果系统存在该存储过程，则会报错。
Createorreplaceprocedure如果系统中没有此存储过程就新建一个，如果系统中有此存储过程则把原来删除掉，重新创建一个存储过程.
2.存储过程名定义：
包括存储过程名和参数列表,参数名和参数类型,参数名不能重复.
3.as（is）为关键字,可以理解为pl/sql的declare关键字,用于声明变量.
createorreplaceprocedurerunbyparmeters
begin
selectcount（*）intoicountfromempwheresal>
isalandjob=sjob;
ificount=1then
....
else
endif;
exception//存储过程异常
whentoo_many_rowsthen
DBMS_OUTPUT.PUT_LINE（'
whenothersthen
end;
2.5.2带参数存储过程含赋值
1.sal%type目的是为了保持与传参过来的数据宽度一致.
2.IN/OUT即可作输入参数，也可作输出参数。
3.变量声明块：
紧跟着的as（is）关键字,用于声明变量。
4.IN按值传递，并且它不允许在存储过程中被重新赋值。
如果存储过程的参数没有指定存参数传递类型，默认为IN.
5.OUT参数：
作为输出参数，需要注意，当一个参数被指定为OUT类型时，就算在调用存储过程之前对该参数进行了赋值，在存储过程中该参数的值仍然是null.
createorreplaceprocedurerunbyparmeters（isalinemp.sal%type,
snameoutvarchar,sjobinoutvarchar）
asicountnumber;
begin
在RUNBYPARMETERS过程中出错'
2.5.3参数的存储过程默认值
1.可以通过default关键字为存储过程的参数指定默认值。
在对存储过程调用时，就可以省略默认值。
2.默认值仅仅支持IN传输类型的参数。
OUT和INOUT不能指定默认值
createorreplaceprocedureprocdefault（p1varchar2,
p2varchar2default'
mark'
）
as
begin
dbms_output.put_line（p2）;
end;
execprocdefault（'
a'
或者SQL>
execprocdefault2（p2=>
aa'
例如二:
createorreplaceprocedureprocdefault2（p1varchar2default'
remark'
p2varchar2）
dbms_output.put_line（p1）;
execprocdefault2（'
//注释:
这种赋值方式是错误的.
execprocdefault2（sjob=>
p2）;
2.5.4.存储过程调用方式
2.5.4.1方式一:
realsalemp.sal%type;
realnamevarchar（40）;
realjobvarchar（40）;
realsal:
=1100;
realname:
='
realjob:
CLERK'
runbyparmeters（realsal,realname,realjob）;
－－必须按顺序
DBMS_OUTPUT.PUT_LINE（REALNAME||'
'
||REALJOB）;
（输出模式）
END;
2.5.4.2方式二:
runbyparmeters（sname=>
realname,isal=>
realsal,sjob=>
realjob）;
---指定值对应变量顺序可变
（输出模式）
2.5.5游标的使用
2.5.5.1实例一:
1.游标的定义只能用使关键字IS，它与AS不通用.
2loop循环.
createorreplaceproceduredept_procedure（ainvarchar2,v_aoutdept%rowtype）
is
--声明游标
cursorc_de（ainvarchar2）
isselect*fromdeptwheredname=a;
begin
--打开游标，对其中找到的记录进行遍历
openc_de（a）;
loop
fetchc_deintov_a;
exitwhenc_de%notfound;
//（exitwhen语句一定要紧跟在fetch之后。
必避免多余的数据处理。
）
dbms_output.put_line（'
deptno:
||v_a.deptno）;
dname:
||v_a.dname）;
loc:
||v_a.loc）;
endloop;
close
c_de;
2.5.5.2实例二:
1.for循环
2.注意for循环rec这个变量无需要在循环外进行声明，无需要为其指定数据类型,它应该是一个记录类型，具体的结构是由游标决定的。
CREATEORREPLACEPROCEDUREtest（v_numberproject.p_number%TYPE,
v_nameproject.p_name%TYPE,
v_managerproject.p_manager%TYPE,
v_clientproject.p_client%TYPE
）
CURSORcur_testIS
SELECTp_number,p_name,p_manager,p_clientFROMprojectWHEREp_nameLIKE'
S%'
;
INSERTINTOprojectVALUES（v_number,v_name,v_manager,v_client）;
COMMIT;
EXCEPTION
WHENOTHERSTHEN
ROLLBACK;
DBMS_OUTPUT.put_line（SQLERRM）;
2.5.5.3实例三:
1.while循环
createorreplaceprocedureproccycle（pvarchar2）
cursorc_postypeisselectpos_type,descriptionfrompos_type_tblwhererownum<
6;
v_postypevarchar2（20）;
v_descriptionvarchar2（50）;
openc_postype;
ifc_postype%foundthen
dbms_output.put_line（'
foundtrue'
elsifc_postype%found=falsethen
foundfalse'
foundnull'
endif;
fetchc_postypeintov_postype,v_description;
whilec_postype%foundloop
fetchc_postypeintov_postype,v_description;
closec_postype;
先取数据后循环,防止直接退出.while来循环处理游标是最复杂的方法
2.5.6储存过程嵌套
2.5.6.1实例一:
createorreplaceprocedureinnerBlock（p1varchar2）
o1varchar2（10）:
out1'
inner1varchar2（20）;
dbms_output.put_line（o1）;
inner1:
inner1'
dbms_output.put_line（inner1）;
exception
whenothersthennull;
end;
2.5.7储存过程建表
2.5.7.1实例一:
createorreplaceprocedureskeleton
executeimmediate'
createtabletable1（idnumber,namevarchar2（20））'
end;
2.5.8储存过程调试
2.5.8.1实例一:
declare
param_outvarchar2（28）;
param_inoutvarchar2（28）;
param_inout:
ff'
proce_test（'
dd'
param_out,param_inout）;
dbms_output.put_line（param_out）;
Oracle调试主要根据用户开发的逻辑来决定,这里就不详解.
2.5.9系统异常错误信息
2.5.9.1系统报错关键字
ACCESS_INTO_NULL试图给为初始化对象的属性赋值
CASE_NOT_FOUNDCASE中若未包含相应的WHEN，并且没有设置
COLLECTION_IS_NULL试图向为初始化的嵌套表和变长数组赋值时,引发异常
CURSER_ALREADY_OPEN试图打开一个已经打开的游标时产生异常
DUP_VAL_ON_INDEX唯一索引对应的列上有重复的值
INVALID_CURSOR在不合法的游标上进行操作
INVALID_NUMBER内嵌的SQL语句不能将字符转换为数字
NO_DATA_FOUND使用selectinto未返回行，或企图在表中访问为初始化的数据
TOO_MANY_ROWS执行selectinto时，结果集超过一行
ZERO_DIVIDE试图用0除某个数字
SUBSCRIPT_BEYOND_COUNT元素下标超过嵌套表或VARRAY的最大值
SUBSCRIPT_OUTSIDE_LIMIT试图使用嵌套表或VARRAY时，将下标指定为负数
VALUE_ERROR发生算术,转换,截断或大小约束错误.
LOGIN_DENIEDPL/SQL应用程序连接到oracle数据库时，提供了不正确的用户名或密码
NOT_LOGGED_ONPL/SQL应用程序在试图连接数据库之前访问数据库中的数据
PROGRAM_ERRORPL/SQL内部问题，可能需要重装数据字典＆pl./SQL系统包
ROWTYPE_MISMATCH宿主游标变量与PL/SQL游标变量的返回类型不兼容
SELF_IS_NULL使用对象类型时，在null对象上调用对象方法
STORAGE_ERROR运行PL/SQL时，内存用尽或者内存出现问题
SYS_INVALID_ID无效的ROWID字符串
TIMEOUT_ON_RESOURCE当数
升级会员 