Oracle数据库存储过程技术文档.docx
- 文档编号:5425567
- 上传时间:2022-12-16
- 格式:DOCX
- 页数:52
- 大小:1MB
Oracle数据库存储过程技术文档.docx
《Oracle数据库存储过程技术文档.docx》由会员分享,可在线阅读,更多相关《Oracle数据库存储过程技术文档.docx(52页珍藏版)》请在冰豆网上搜索。
Oracle数据库存储过程技术文档
Oracle数据库存储过程技术文档
前言
本文编写目的:
本文对ORACLE存储过程,存储函数,包作了一个概括性的介绍,以实例为驱动介绍了存储过程,存储函数,包的语法,数据类型以及程序开发编写的方法。
通过对本文的学习,达到使用ORACLE存储过程进行基本编程的目的。
本文主要参考:
《新编ORACLE7入门教程》电子工业出版社
《ORACLE8I数据库高级应用开发技术》人民邮电出版社
《ORACLE8PL/SQL程序设计》机械工业出版社
本文面向对象:
对ORACLE有一定认识和经验的开发者和系统管理者。
本文中各例均使用Oracle数据库demo用户.
用户名:
scott用户口令:
tiger
数据结构建立:
/*使用system用户及口令登录oracle数据库*/
$SQLPLUSsystem/passwd
/*建立scott用户口令为tiger*/
$SQL>createuserscottidentifiedbytiger;
/*给scott用户授权*/
$SQL>grantcreatesessiontoscott;
$SQL>exit;
$SQLPLUSscott/tiger
$SQL>start$ORACLE_HOME/sqlplus/demo/demobld.sql
主要数据结构:
第一章oracle存储过程概述
Oracle存储过程(storeprocedure)作为PL/SQL语言的子程序,使用PL/SQL语言对数据处理逻辑,数据存储,数据操纵进行描述和封装,通过oracle其他工具(Pro*c&sqlplus等)对存储过程调用,实现相应功能.
Oracle存储过程在创建时经过数据库编译,作为数据库对象存储在数据库中,使用存储过程名称和输入输出参数实现存储过程描述的功能.
存储过程是由流控制和SQL语句书写的过程,这个过程经编译和优化后存储在数据库服务器中,使用时只要调用即可。
在ORACLE中,若干个有联系的过程可以组合在一起构成程序包。
使用存储过程有以下的优点:
1.存储过程的能力大大增强了SQL语言的功能和灵活性。
存储过程可以用流控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算.
2.可保证数据的安全性和完整性。
3.通过存储过程可以使没有权限的用户在控制之下间接地存取数据库,从而保证数据的安全。
4.通过存储过程可以使相关的动作在一起发生,从而可以维护数据库的完整性。
5.再运行存储过程前,数据库已对其进行了语法和句法分析,并给出了优化执行方案。
这种已经编译好的过程可极大地改善SQL语句的性能。
由于执行SQL语句的大部分工作已经完成,所以存储过程能以极快的速度执行。
6.可以降低网络的通信量。
7.使体现企业规则的运算程序放入数据库服务器中,以便集中控制。
当企业规则发生变化时在服务器中改变存储过程即可,无须修改任何应用程序。
企业规则的特点是要经常变化,如果把体现企业规则的运算程序放入应用程序中,则当企业规则发生变化时,就需要修改应用程序工作量非常之大(修改、发行和安装应用程序)。
如果把体现企业规则的运算放入存储过程中,则当企业规则发生变化时,只要修改存储过程就可以了,应用程序无须任何变化.
Oracle存储函数(FUNCTION)作为特殊的存储过程,与C/C++语言函数相似,具备函数名,输入输出参数以及返回值.
存储过程和存储函数都是相对独立的实体.
Oracle包(Package)为了管理上的方便,把一些相关的程序结构如存储过程,存储函数,变量,游标等组织在一起,构成一个包.Oracle包具有面向对象程序设计语言的特点,是对PL/SQL程序设计元素的封装.包类似于C++和JAVA语言中的类,其中变量相当于类中的成员变量,存储过程和存储函数相当于类方法.包中的元素分为共有元素和私有元素,两种元素允许访问的程序范围不同.
1.1存储过程基本结构(PROCEDURE)
1.1.1创建存储过程
CREATE [ORREPLACE]PROCEDURE 存储过程名
(参数定义标)
IS/AS
变量定义
BEGIN
PL/SQL语句块
EXCEPTION
例外处理
END存储过程名
定义说明:
1.参数定义表:
存储过程可以有三类参数
IN数据从调用环境传入存储过程
OUT数据从存储过程传入调用环境
INOUT数据可以传入或传出存储过程
参数使用原则:
参数类型可以为ORACLE允许的任意类型,也可为%TYPE(与其他某一变量类型一致)或%ROWTYPE(与数据库中某一对象表,游标等数据类型一致)类型
指定参数时,不能指定长度
所有输出参数(OUT)只能出现在SELECTINTO语句或赋值语句中.
尽量减少IN参数个数.
2.变量定义
变量类型可以为ORACLE允许的任意类型,也可为%TYPE(与其他某一变量类型一致)或%ROWTYPE(与数据库中某一对象表,游标等数据类型一致)类型.
3.例外处理
存储过程例外处理与PL/SQL错误处理一致,可按条件执行相应的操作.
例1.1
本例实现为指定雇员号(emp)的雇员加工资(sal),数据源为表emp
1.1.2存储过程删除
$SQLPLUS>DROPPROCEDURE过程名
1.1.3调用存储过程
1.SQLPLUS环境
语法$SQLPLUS>EXECUTE存储过程名
参数SQLPLUS中的变量或常量
例:
$SQL>EXECUTEraise_sal(10,1000);
2.SQLDBA环境
语法$SQLPLUS>EXECUTE存储过程名
参数SQLPLUS中的变量或常量
3.SQLFORMS
语法:
过程名
参数:
SQLFORMS中的域或全局变量
4.PLSQL或其他存储过程
语法:
过程名参数:
PLSQL局部变量
5.Pro*C
语法:
EXEC SQL过程名
参数:
主变量
例1.2
1.2存储函数(FUNCTIONE)
存储函数是一类特殊的存储过程,与一般存储过程不同的是存储函数必须返回一个值.
1.2.1创建存储函数
CREATE[ORREPLACE]FUNCTION存储函数名
RETUNR返回值类型
IS/AS
变量声明
BEGIN
PLSQL语句块
EXCEPTION
例外处理
END存储函数名
备注:
返回值类型不带长度
1.2.2删除存储函数
$SQL>dropfunction存储函数名
例1.3
从员工信息表(emp)中选择部门代号为v_empno员工的工资
1.3包(package)
1.3.1包的基本结构
包中可以包含过程(procedure),函数(function),变量(variable)游标(cursor),常量(constant),例外处理(exception).
包由两部分组成:
包定义和包体
包定义:
对包的公共元素如过程,函数,变量,常量,游标,例外情况等进行说明,可在包外独立使用这些公共元素.
包体部分包括包中使用的私有元素和包的公共元素的定义.
1.3.2包的创建
1.创建包定义
CREATE〔ORREPLACE〕PACKAGEpackage_name
IS/AS
公共元素声明
ENDpackage_name;
2.创建包体
CREATE〔ORREPLACE〕PACKAGEBODYpackage_name
IS/AS
私用元素定义
公共元素定义
BEGIN
PLSQL语句
ENDpackage_name;
例1.4
1.3.3调用包中元素
$SQL>EXECUTE包名.元素名(参数列表);
例:
$SQL>EXECUTemp_package.raise_sal(7654,100);
1.3.4包的修改和删除
删除包:
$SQL>dropPACKAGE包名
$SQL>dropPACKAGEBODY包名
备注:
包定义和包体应该同时修改,并保持一致.
第二章oracle存储过程基础――PL/SQL
Oracle存储过程以PL/SQL作为其流程控制语言,可以理解Oracle存储过程为具有名称和输入输出参数的PL/SQL语句块,因此,本章介绍PL/SQL的语法和使用.
2.1pl/sql基础
2.1.1PL/SQL简介
一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.与关系数据库管理系统(RDBMS)集成
使用PL/SQL,可以将许多用户都可能用到的处理编程封装过程或包,与关系数据库管理系统有效集成.这样,用户可使用Oracle工具直接调用,提高了开发效率,减少了再编译时间,提高系统性能.
二PL/SQL应用环境
SQL*PLUS;SQL*FORMS;OracleCDE工具;Pro*C
三PL/SQL块的基本结构
基本的PL/SQL块由定义部分,执行部分,例外处理部分组成
1.定义部分:
定义在程序执行部分使用的常量,变量,游标和例外处理名称
2.可执行部分
包括数据库操作语句和PL/SQL块控制语句
3.例外处理部分
对执行部分的所有PL/SQL语句的执行进行监控,如执行发生例外,则程序跳到该部分执行
2.1.2一个简单的PL/SQL块
例2.1向EMP表插入一条雇员记录,雇员代号为8000,雇员名是WUCHEN,其他字段与SMITH雇员相同,然后将所有雇员工资增加$500.
执行步骤:
$SQLPLUSscott/tiger
$SQL>start./PLSQL块名称
$SQL>.(输入符号点)
$SQL>r(字母r或符号/执行程序)
一定义变量
在PLSQL中所使用的变量必须在变量定义部分明确定义.变量定义部分是包括在关键字DECLARE和BEGIN之间的部分,每条语句后用(;)结束.
定义格式:
变量标示符[CONSTANT]数据类型[NOTNULL]
[:
=缺省值或PLSQL表达式];
变量标示符命名规则应遵循SQL实体命名规则
定义常量时必须加关键字CONSTANT必须为其赋值
如该变量不允许为空值,必须加参数NOTNULL
变量赋值时,可使用:
=或使用关键字DEFAULT.
每行只能定义一个变量.
数据类型
简单数据类型(标量数据类型):
NUMBER(m,n)数字类型m为总长度,n为小数长度
CHAR(m)字符型m为变量长度
VARCHAR2(m)可变长字符型m为最大长度
DATE日期型
LONG长型
BOONEAN布尔型值为TRUEFALSENULL
已定义变量%TYPE定义成与已定义变量一致类型
复合数据类型
变量标示符对象标示符%ROWTYPE;
对象标示符可为表,游标等,变量被定义成与数据库对象一致的类型结构,当数据库结构改变时,不必改变改变量的定义.
使用%ROWTYPE分为两种不同情况:
1.作为查询结果存放空间时:
select字段列表INTO %ROWTYPE型变量
2.作为单个成员使用:
%ROWTYPE变量名.字段名
二变量赋值
变量赋值时需使用PLSQL变量赋值操作符(:
=)
1.常量赋值:
变量名:
=常量
2.变量赋值:
变量名:
=同类型变量
3.为%ROWTYPE型变量赋值
a.select列表into%ROWTYPE型变量from表
b.为%ROWTYPE变量每个成员单独赋值
如%ROWTYPE变量.变量成员=值
4.表达式赋值:
变量名:
=表达式或函数
三PLSQL中使用的SQL语句
在PL/SQL块中,所有对数据库的访问和操作还是要经由SQL语言进行,在PL/SQL块中可以使用数据查询语言,数据操纵语言和数据控制语言,但不能使用数据定义语言具体地说可以使用select,insert,update,delete,commit,rollback,但不能使用create,alter,drop,grant,revoke.
1.PL/SQL块中使用查询语句
在PL/SQL中使用select时必须加INTO语句.
INTO子句后的变量个数和位置必须与SELECT后的字段列表相同
SELECT语句中的WHERE条件可以包含PL/SQL块中定义的变量及表达式.
SELECT语句必须保证有且仅有一条记录返回,否则出错:
TOO_MANY_ROWS-1422记录多于一条
NO_DATA_FOUND-1403没找到记录
在SQL语句中使用的变量名应与数据库字段名区分开.
2.PL/SQL块中使用操纵语句
PL/SQL中使用INSERT,DELETE,UPDATE与SQL语句完全一样,只是可以使用定义的变量和表达式
3.PL/SQL块中使用事物控制语句
提交命令(COMMIT):
结束当前事物,对数据库作永久性改变
语法COMMIT[WORK]
回退命令(ROLLBACK):
结束当前事物,并放弃对数据库所作修改
语法ROLLBACK[WORK]
保存点(SAVEPOINT):
为了避免一处失败导致全部事物回滚,可以使用SAVEPOINT和ROLLBACKTO语句
语法SAVEPOINT标记
ROLLBACKTO标记
2.1.3PL/SQL流程控制
PL/SQL具有与高级语言类似的流程控制语句.PL/SQL主要控制语句有:
条件控制语句
循环控制语句
跳转控制语句
1.条件控制语句:
IF_THEN_ELSE语句
语法:
IF条件THEN
语句;
ELSE
语句;
ENDIF;
条件可为ISNULL或NOTISNULL以及AND,OR,NOT,逻辑运算符
IF_THAN_ELSIF语句:
语法:
IF条件THEN
语句;
ELSIF条件THEN
语句;
[ELSIF条件THEN语句;]
[ELSE语句;]
ENDIF
ELSIFv_job=’SALESMAN’THEN
v_addsal=500;
ELSIFv_job=’ANALYST’THEN
V_addsal=200;
ELSE
v_addsal=200;
ENDIF
UPDATEempSETsal=sal+v_addsal
WHEREename=’SMITH’;
END;
2.循环控制语句:
LOOP循环:
语法:
LOOP
语句;
[EXIT[WHEN条件]];
ENDLOOP;
FOR循环:
语法:
FOR计数器IN[REVERSE]下界…上界LOOP
语句;
ENDLOOP;
计数器用于控制循环次数的变量,无需在定义部分做出定义,系统隐含定义为整数,REVERSE表示计数器从上界到下界递减计数,下界定义初值,上界定义终值,下界应小于上界.对计数器不可作赋值操作.
WHILE循环
语法:
WHILE条件LOOP
语句;
ENDLOOP;
3.跳转控制语句:
语法:
《标号》
其他语句;
GOTO标号;
跳转语句可在统一块语句间跳转
跳转语句可从子块跳转倒父块中,但不能从父块跳转到子块中
跳转语句不能在IF语句体外跳到IF体内
跳转语句不能从循环体外跳到循环体内
2.2游标(CURSOR)
在PL/SQL查询语句中,有时会返回多条记录,这时如使用SQL语句则会出错.因此,在查询语句返回多条记录或不知返回结果数目时,必须使用游标.
2.2.1游标的概念
PL/SQL中游标的使用与Pro*C中游标使用相似,包括定义,打开,提取数据,关闭四个步骤.一般游标在定义,打开后使用循环语句逐条处理提取的数据。
一定义游标
语法:
CURSOR游标名称IS
SELECT语句;
定义游标应写在PL/SQL语句的DECLARE变量定义部分
定义游标时SELECT语句中不可有INTO子语句
在SELECT语句中使用的变量必须在定义游标前定义
二打开游标
语法:
OPEN游标名;
在BEGIN语句之后,可以打开游标,在打开游标之前,必须对游标所涉及到的变量赋值.
三利用游标提取数据
语法:
FETCH游标名INTO变量1,变量2,…..
游标每次只能取到一条数据,同时游标指针下移,等待取下一条数据.该条语句变量列表应与定义游标时的参数列表一致
四关闭游标
语法:
CLOSE游标名
关闭游标,释放资源,游标关闭后不能再提取数据.
2.2.2游标的属性
游标的属性标示游标的运行状况.
%ISOPEN布尔型表示游标是否打开
%NOTFOUND布尔型描述最后一次FETCH的结果
%FOUND布尔型描述最后一次FETCH的结果,与NOTFOUND相反
%ROWCOUNT数字型描述当前取值的条数
例查询10号部门所有雇员的姓名工资,并插入到临时表tmp中
tmp表结构为t1char(20),t2number(10)
以下使用三种不同的循环方法实现该例:
2.2.3游标中FOR循环的使用
游标使用FOR循环可以简化游标的操作,使用FOR循环时,系统隐含定义了一个数据类型为%ROWTYPE的变量为循环计数器,此时在PL/SQL块中不用显式的打开,关闭游标。
语法:
FOR组合变量名IN游标名LOOP
语句;
ENDLOOP;
2.2.4带参数游标的使用方法
在定义游标时,可以加入参数,参数再游标中使用。
语法:
DECLARE
CURSOR游标名(参数列表)IS
SELECT语句;
2.3动态SQL语句
动态SQL语句是指语句文本在应用程序运行时才被建立的SQL语句或PL/SQL块,动态SQL语句文本中可以包含结合参数占为符。
在使用占位符时,必须在其前面加冒号(:
)前缀。
使用动态SQL语句还能执行在PL/SQL块中不能静态执行的SQL语句,如DDL语句。
语法:
EXECUTEIMMDIATE动态语句串
[INTO{variable[,variable]….|record}]
[USING[IN|OUT|INOUT]bind_argument
[,[IN|OUT|INOUT]bind_argument]…];
其中动态语句串表示SQL语句或PL/SQL块文本,对于SQL语句不能使用语句结束符(;),对于PL/SQL块文本,必须加结束符。
INTO子句只能用于单行查询语句,将查询结果存储到指定变量variable或记录record变量中。
USING子句使用bind_argument值替换动态语句串中的占位符。
USING语句不能传递布尔型变量(TRUEFALSENULL)。
由于Oracle自动将所有未赋值的变量设置为NULL,因此在需要传递NULL变量时,使用未赋值变量即可。
USING子句中,不能使用结合参数传递对象名称。
USING语句中结合变量默认参数模式为输入参数,当结合变量为输出参数或输入输出参数时应加OUT或INOUT选项说明。
2.4例外处理
例外处理是指程序在执行过程中的警告或错误的处理。
语法:
EXCEPTION
WHEN例外情况1[OR例外情况2…]THEN
语句;
WHEN例外情况3[OR例外情况4…]THEN
语句;
[WHENOTHERSTHEN语句;]
常见系统预定义例外情况
NO_DATA_FOUNDORA_01403执行SELECT时未找到数据
TOO_MANY_ROWSORA_01427未使用游标的SELECT语句返回了多行数据
INVALID_CURSORORA_1001非法的游标操作
VALUES_ERRORORA_06502出现数字,数据转换,截字符串错误
INVALID_NUMBERORA_01722字符串向数字转换失败
ZERO_DIVIDEORA_01476分母未零
DUP_VAL_ON_INDEXORA_0001向具有唯一索引表中插入重复键值
TIMEOUT_ON_RESOURCEORA_00051等待资源超时
INVALID_CURSORORA_1001试图关闭一个未打开的游标
NOT_LOGGED_ONORA_1012数据库未联接
LOGIN_DENIEDORA_1017登录数据库失败
SYS_INVALID_ROWIDORA_1410无效字符串
STORAGE_ERRORORA_30625PL/SQL用尽内存或内存被破坏
ROWTYPE_MISMATCHORA_6504赋值时,类型不匹配
CURSOR_ALREADY_OPENORA_6511试图打开一个已达开的游标
PROGRAM_ERRORORA_6501PL/SQL块内部错误
用户可以自定义例外处理,例外处理的名称需在定义部分声明,在引发例外处理时,需使用RAISE子句:
语法:
RAISE例外处理名称
2.5一个完整的PL/SQL实例
问题:
通过DEPT表查询储所有部门号,并将每个部门工资高于$2000的雇员姓名,工资插入到highsal表中;工资低于$2000的雇员插入到lowsal表中,如果工资大于$5000,则出错,并向tmp表中插入出错信息,退出程序,最后在summary表中统计各部门人数以及工资总和。
第三章oracle存
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 数据库 存储 过程 技术 文档