数据存储过程.docx
- 文档编号:6503875
- 上传时间:2023-01-07
- 格式:DOCX
- 页数:21
- 大小:24.27KB
数据存储过程.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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据 存储 过程