数据存储过程.docx
《数据存储过程.docx》由会员分享，可在线阅读，更多相关《数据存储过程.docx（21页珍藏版）》请在冰豆网上搜索。
数据存储过程
数据存储过程
PL/SQL语言
简介3
存储过程3
创建存储过程3
定义说明：
4
例子1：
5
存储过程删除5
调用存储过程6
存储函数（function）6
创建存储函数6
删除存储函数6
例子2：
6
包（package）7
包的创建8
创建包体8
例子3：
8
调用包中元素10
删除包10
PL/SQL语言10
PL/SQL中的关系操作符10
PL/SQL块基本结构12
一个简单的PL/SQL块12
定义变量13
定义格式：
13
数据类型：
13
PLSQL中使用的SQL语句14
PLSQL块中使用查询语句14
PLSQL块中使用操纵语句15
PL/SQL使用事物控制语句16
PL/SQL流程控制16
1、条件控制语句16
2、循环控制语句18
3、跳转控制语句20
游标（cursor）20
游标的概念21
一、定义游标21
二、打开游标21
三、利用游标提取数据21
四、关闭游标21
游标的属性22
使用NOTFOUND属性22
使用ISOPEN属性23
游标使用FOUND属性23
游标中for循环的使用24
带参数游标的使用方法24
例子：
25
简介
数据存储过程是PL/SQL语言的子程序，使用PL/SQL语言对数据处理逻辑，数据存储，数据操纵进行描述和封装，通过Oracle其他工具对存储过程调用，实现相应功能。
Oracle存储过程在创建时经过数据库编译，作为数据库对象存储在数据库中，使用存储过程名称和输入输出参数实现存储过程描述的功能。
存储过程是由流控制和SQL语句书写的过程，这个过程编译和优化后存储在数据库服务器中，在Oracle中，若干个有联系的过程可以组合在一起构成程序包。
Oracle存储函数作为特殊的存储过程，具备函数名，输入输出函数以及返回值。
存储过程和存储函数都是相对独立的实体。
Oracle包为了管理上的方便，把一些相关的程序结构如存储过程、存储函数、变量、游标等组织在一起，构成一个包。
Oracle包具有面向对象程序设计语言的特点，是对PL.SQL程序设计元素的封装。
包类似于Java中的类，其中变量相当于类中的成员变量，存储过程和存储函数相当于类中方法。
包中的元素分为共有元素和私有元素，两种元素允许访问的程序范围不同。
存储过程
创建存储过程
create[orreplace]procedure（[pr'si:
d]程序、手续、步骤）存储过程名
（参数定义表）
is/as
变量定义
begin
PL/SQL语句块
exception
例外处理
end存储过程名
定义说明：
参数定义表：
存储过程可以有三类参数
in数据从调用环境传入存储过程
out数据从存储过程传入调用环境
input数据可以传入或传出存储过程
参数使用原则：
参数类型可以为Oracle允许的任意类型，也可以为%TYPE（与其他某一变量类型一致）或%ROWTYPE（与数据库中某一对象表，游标等数据类型一致）类型。
指定参数时，不能指定长度
所有输出参数（out）只能出现在selectinto语句或赋值语句中。
尽量减少in参数个数
变量定义：
变量类型可以为Oracle允许的任意类型，，也可以为%TYPE（与其他某一变量类型一致）或%ROWTYPE（与数据库中某一对象表，游标等数据类型一致）类型
例外处理：
存储过程例外处理与PL/SQL错误处理一致，可按条件执行相应的操作。
例子1：
为指定雇员号的雇员加工资，数据源为表emp
/*********************
*create创建存储过程
*replace替换存储过程
*createorreplace如存储过程不存在则创建，否则替换
/*********************
createorreplaceprocedureraise_sal（emp_idINNUMBER,add_salINNUMBER）
as
/*无局部变量声明*/
begin
/*PL/SQL*/
UPDATEempSETsal=sal+add_salWHEREempno=emp_id;
EXCEPTION
/*例外处理NO_DATA_FOUND数据未找到时执行*/
WHENNO_DATA_FOUNDTHEN
/*raise_application_error（错误代码，‘错误信息’）向调用环境返回错误信息*/
raise_application_error（-20011,'InvalidEmployee'||TO_CHAR（emp_id））;
endraise_sal
存储过程删除
dropprocedure过程名
调用存储过程
Execute存储过程名
例子：
executeraise_sal（10,1000）;
存储函数（function）
创建存储函数
create[orreplace]function存储函数名
return返回值类型
is/as
变量声明
begin
PLSQL语句块
exception
例外处理
end存储函数名
备注：
返回值类型不带长度
删除存储函数
dropfunction存储函数名
例子2：
从员工信息表中选择部门代号为v_empno员工的工资
createorreplacefunctionget_sal（v_empnoINemp.empno%TYPE）
returnNUMBER
is
v_emp_salemp.sal%TYPE:
=0;
begin
selectsalINTOv_emp_salFROMempWHEREempno=v_empno;
RETURN（v_emp_sal）;
endget_sal;
在PL/SQL中，不能用SELECT*INTO……（错误）
必须是SELECT字段1，字段2，……INTO变量1，变量2，……
FROM表
SELECTINTO语句从一个表中选取数据，然后把数据插入另一个表中。
包（package）
包的基本结构
包中可以包含过程（procedure），函数（function）、变量（variable）、游标（cursor）常量（constant），例外处理（exception）.
包由两部分组成：
包定义和包体
包定义：
对包的公共元素如过程，函数，变量，常量，游标，例外情况等进行说明，可在包外独立使用这些公共元素
包体部分包括包中使用的私有元素和包的公共元素的定义。
包的创建
创建包定义
create[orreplace]packagepackage_name
is/as
公共元素声明
endpackage_name
创建包体
create[orreplace]packagebodypackage_name
is/as
私有元素定义
共有元素定义
begin
PL/SQL语句
endpackage_name
例子3：
createorreplacepackageemp_package
as
/*声明函数*/
functionhire_emp（nameVARCHAR2,jobVARCHAR2,mgrNUMBER,hiredateDATE,salNUMBER,comnNUMBER,deptnoNUMBER）
returnNUMBER;
/*声明过程*/
procedurefire_emp（emp_idNUMBER）;
proceduresal_raise（emp_idNUMBER,sal_idNUMBER）;
endemp_package;
/*创建包定义*/
createorreplacepackagebodyemp_packageas
/*定义函数*/
functionhire_emp（namevarchar2,jobvarchar2,mgrnumber,hiredatedate,salnummber,commnumber,deptnonumber）
returnNUMBER
is
new_empnoNUMBER（10）;
begin
selectemp_sequence.NEXTVALINTOnew_empnoFROMemp;
insertintoempvalues（new_empno,name,job,mgr,hiredate,sal,comm,deptno）;
return（new_empno）;
endhire_emp;
/*定义过程*/
procedurefire_emp（emp_idnumber）is
begin
deletefromempwhereempno=emp_id;
ifsql%notfoundthen
raise_application_error（-20011,'IvalidEmployeeNumber'||TO_CHAR（emp_id））;
endif;
endfire_emp;
proceduresal_raise（emp_idNUMBER,sal_idNUMBER）as
begin
updateempsetsal=sal+sal_idwhereempno=emp_id;
ifsql%notfoundthen
raise_application_error（-21011,'IvalidEmploteeNUMBER'||TO_CHAR（emmmp_id））;
endif;
endsal_raise;
/*结束包定义*/
endemp_package;
调用包中元素
execute包名.元素名（参数列表）;
executeemp_package.raise_sal（7654,100）;
删除包
dropPACKAGE包名
dropPACKAGEBODY包名
包定义和包体应该同时修改，并保持一致。
PL/SQL语言
Oracle存储过程以PL/SQL作为其流程控制语言，可以理解Oracle存储过程为具有名称和输入参数的PL/SQL语句块，因此，本章介绍PL/SQL的语法和使用。
PL/SQL中的关系操作符
operator
operation
<
小于操作符
<=
小于或等于操作符
>
大于操作符
>=
大于或等于操作符
=
等于操作符
!
=
不等于操作符
<>
不等于操作符
:
=
赋值操作符
PL/SQL优点：
1，过程化能力
PL/SQL称为SQL过程语言，他将高级程序设计语言中所具备的过程能力与非过程化的SQL语言有机的结合在一起，形成了一个集成式的Oracle数据库事务处理应用开发工具，为应用开发者提供了增强生产力的机制。
PL/SQL以blocks（块）为单位，较大的块中可以镶嵌子块，可以将复杂的问题分解成一组易于控制的，很好定义的逻辑模块。
在PL/SQL块中可以进行变量定义，例外处理，然后在SQL语句中调用PL/SQL块中可以使用过程化语言控制结构进行程序设计，包括条件转移，循环控制，游标。
2、改进处理性能
使用PL/SQL，Oracle数据库将PL/SQL作为一组，一次提交给Oracle服务进程，减少Oracle客户服务进程之间的交互。
3、良好的应用移植性
由于PL/SQL是模块化结构，在进行应用移植时，可以将模块内部的复杂处理忽略，而只考虑模块间的数据交流。
4、与关系数据库管理系统集成
使用PL/SQL，可以将许多用户都可能用到的处理编程封装过程或包，与关系数据库管理系统有效集成，用户可以使用Oracle工具直接调用，提高了开发效率，减少了再编译时间，提高系统性能。
PL/SQL块基本结构
Declare定义部分
Begin执行部分
Exception例外处理部分
End
1、定义部分
定义在程序执行部分使用的常量、变量、游标和例外处理名称
2、可执行部分
包括数据库操作语句和PL/SQL块控制语句
3、例外处理部分
对执行部分的所有PL/SQL语句的执行进行监控，如执行发生例外，则程序跳到该部分执行。
一个简单的PL/SQL块
/*定义变量*/
Declare
/*ROWTYPE类型定义变量myrecord为一结构，与表emp各字段数据类型一致*/
Myrecordemp%ROWTYPE;
/*定义变量myempno，类型为NUMBER（4）变量非空，初始值8000*/
Myempnonumber（4）NOTNULL:
=8000;
/*TYPE类型定义变量myname类型与表emp中ename字段一致*/
Mynameemp.ename%TYPE;
/*constant关键字定义常量addsal值为500*/
Addsalconstantnumber（4）:
=500
Begin
Select*intomyrecordfromempwhereename='SMITH';
Myname:
='WUCHEN';
Insertintoemp（EMPNO,ENAME,SAL,COMM,JOB,HIREDATE,DEPTNO）values（myempno,myname,myrecord.sal,m,myrecord.job,myrecord.hiredate,myrecord.deptno）;
Updateempsetsal=sal+addsal;
End;
定义变量
在PLSQL中所使用的变量必须在变量定义部分明确定义，变量定义部分是包括在关键字declare和begin之间的部分，每条语句后用；结束。
定义格式：
变量标识符[constant]数据类型[notnull][:
=缺省值或PLSQL表达式]
变量标识符命名规则应遵循SQL实体命名规则
定义常量时必须加constant，必须为其赋值
如该变量不允许为空，必须加参数notnull
变量赋值时，可使用：
=或使用关键字default
每行只能定义一个变量
数据类型：
简单数据类型（标量数据类型）：
NUMBER（m,n）数字类型，m为总长度，n为小数长度
CHAR（m）字符型，m为变量长度
VARCHAR2（m）可变长字符型，m为最大长度
DATE日期型
LONG长型
BOOLEAN布尔型，值为true、false或者null
已定义变量%TYPE定义成与已定义变量一致类型
复合数据类型
变量标示符对象标示符%ROWTYPE；
对象标示符可为表，游标等，变量被定义成与数据库对象一致的类型结构，当数据库结构改变时，不必改变该变量的定义。
使用%ROWTYPE分为两种不同的情况：
1、作为查询结果存放空间时：
Select字段列表into%ROWTYPE型变量
2、作为单个成员使用：
%ROWTYPE变量名.字段名
1、变量赋值
使用PLSQL变量赋值操作符（:
=）
1、常量赋值：
变量名：
=常量
2、变量赋值：
变量名：
=同类型变量
3、为%ROWTYPE型变量赋值
a）Select列表into%ROWTYPE型变量from表
b）为%ROWTYPE变量每个成员单独赋值
如：
%ROWTYPE变量.变量成员=值
4、表达式赋值：
变量名：
=表达式或函数
PLSQL中使用的SQL语句
在PLSQL块中，所有对数据库的访问和操作还是要经由SQL语句执行。
在PLSQL块中，可以使用数据查询语言，数据操纵语言和数据控制语言，但不能使用数据定义语言，具体的说可以使用selectinsertupdatedeletecommitrollback，但不能使用createalterdropgrantrevoke.
PLSQL块中使用查询语句
在PLSQL中使用select时必须加into语句
Into字句后的变量个数和位置必须与select后的字段列表相同。
Select语句中的where条件可以包含PLSQL块中定义的变量及表达式
Select语句必须保证有且仅有一条记录返回，否则出错
TOO_MANY_ROWS-1422记录多于一条
NO_DATA_FOUND-1403没找到记录
在SQL中使用的变量名必须与数据库字段名区分开
Declare
Emp_recEMP%ROWTYPE;
V_enameEMP.ename%TYPE:
='SMITH';
Begin
Select*intoemp_recfromempwhereename=v_ename;
End;
PLSQL块中使用操纵语句
PLSQL使用insertdeleteupdate与SQL语句完全一样，只是可以使用定义的变量和表达式
Declare
V_empnoemp.empno%TYPE:
=8000;
V_enameemp.ename%TYPE:
='Bill';
V_jobemp.job%TYPE:
='manager';
V_salemp.sal%TYPE:
=2000;
V_commm%TYPE:
=1000;
V_hiredateemp.hiredate%TYPE:
=SYSDATE;
V_deptnoemp.deptno%TYPE:
=10;
V_addsalemp.sal%TYPE;
Begin
Insertintoemp（empno,ename,job,sal,comm,hiredate,deptno）values（v_empno,v_ename,v_job,v_sal,v_comm,v_hiredate,v_deptno）;
V_addsal:
=1000;
Updateempsetsal=sal+v_addsalwhereempno=v_empno;
Deletefromempwhereempno>8000;
Commitwork;
End;
PL/SQL使用事物控制语句
提交命令（commit）：
结束当前事物，对数据库做永久性改变。
语法：
commit[work];
回退命令（rollback）：
结束当前事物，并放弃对数据库所做改变。
语法：
rollback[work];
保存点（savepoint）：
为了避免一处失败导致全部事物回滚，可以使用savepoint和rollbackto语句
语法：
savepoint标记
Rollbackto标记
PL/SQL流程控制
PL/SQL具有与高级语言类似的流程控制语句，PL/SQL主要控制语句有：
条件控制语句
循环控制语句
跳转控制语句
1、条件控制语句
IF_THEN_ELSE语句
语法：
if条件then
语句；
Else
语句；
Endif;
条件可为isnull或者notisnull以及and,or,not逻辑运算符
例子：
将emp表中雇员名为SMITH雇员的工资修改，如果工资大于$2000，则加$500，否则加$1000
Declare
V_enameemp.ename%TYPE='SMITH';
V_addsalemp.sal%TYPE;
V_salemp.sal%TYPE;
Begin
Selectsalintov_salwhereename=v_ename;
Ifv_sal>2000then
V_addsal:
=500;
Else
V_addsal:
=1000;
Endif;
Updatesalsetsal=sal+v_addsalwhereename=v_ename;
End;
IF_THEN_ELSIF语句
语法：
if条件then
语句；
Elsif条件then
语句；
[elsif条件then语句;]
[else语句；]
Endif;
例子：
根据emp表中的工种为SMITH修改工资，若工种为MANAGER，工资加$1000，工种为SALESMAN，工资加$500,工种为ANALYSE，工资加$200，否则加$100。
Declare
V_jobemp.job%TYPE;
V_addsalemp.sal%TYPE;
Begin
Selectjobintov_jobfromempwhereename='SMITH';
Ifv_job='MANAGER'then
V_addsal:
=1000;
Elsifv_job='SALESMAN'then
V_addsal:
=500;
Elsifv_job='ANALYSE'then
V_addsal:
=200;
Else
V_addsal:
=100;
Endif;
Updatesalsetsal=sal+v_addsalwhereename='SMITH';
Commitwork;
End;
2、循环控制语句
Loop循环：
语法：
loop
语句；
[exit[when条件]];
Endloop;
例子：
给10号部门增加新雇员，只确定雇员代号，其他信息忽略
Declare
V_empnoemp.empno%TYPE:
=8000;
Begin
Loop
Insertintoemp（deptno,empno）values（10,v_empno）;
V_empno=v_empno+100;
/*如果雇员号>=9000,则退出循环*/
Exitwhenv_empno>=9000;
Endloop;
End;
For循环：
语法：
for计数器in[reverse]下界...上界loop
语句;
Endloop;
计数器用于控制循环次数的变量，无需在定义部分做出定义，系统隐含定义为整数，reverse表示从上界到下界递减计数，下界定义初值，上界定义终值，下界应小于上界，对计数器不可做赋值操作。
例子：
同上例
Declare
V_deptnoemp.deptno%TYPE:
=10;
Begin
Foriin1...10loop
Insertintoemp（deptno,empno）values（v_deptno,8000+i*100）;
Endloop;
Commitwork;
End;
While循环
语法：
while条件loop
语句;
Endloop;
例子，同上例：
Declare
Inumber
（2）:
=1;
Begin
Whilei<=10loop
Insertintoemp（de
升级会员 