Oracle数据库存储过程技术文档.docx
《Oracle数据库存储过程技术文档.docx》由会员分享，可在线阅读，更多相关《Oracle数据库存储过程技术文档.docx（62页珍藏版）》请在冰豆网上搜索。
Oracle数据库存储过程技术文档
Oracle数据库存储过程技术文档
前言
本文编写目的：
本文对ORACLE存储过程，存储函数，包作了一个概括性的介绍，以实例为驱动介绍了存储过程，存储函数，包的语法，数据类型以及程序开发编写的方法。
通过对本文的学习，达到使用ORACLE存储过程进行基本编程的目的。
本文主要参考：
《新编ＯRACLE7入门教程》电子工业出版社
《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
/*CREATE创建存储过程*/
/*REPLACE替换存储过程*/
/*CREATEORREPLACE如存储过程不存在则创建,否则替换*/
CREATEORREPLACEPROCEDURE
/*存储过程名为raise_sal*/
/*参数为输入NUMBER型emp_id和输入NUMBER型add_sal*/
raise_sal（emp_idINNUMBER,add_salINNUMBER）
AS
/*无局部变量声明*/
BEGIN
/*PLSQL语句块*/
UPDATEemp
SETsal=sal+add_sal
WHEREempno=emp_id;
EXCEPTION
/*例外处理NO_DATA_FOUND数据未找到时执行*/
WHENNO_DATA_FOUNDTHEN
/*raise_application_error（错误代码,’错误信息’）向调用环境返回错误信息*/
raise_application_error（-20011,'InvalidEmployee'||TO_CHAR（emp_id））;
ENDraise_sal;
存储过程简例
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
#include
EXECSQLINCLUDESQLCA;
main（）{
/*声明宿主变量*/
EXECSQLBEGINDECLARESECTION;
char*oid="scott/tiger";
inttt;
EXECSQLENDDECLARESECTION;
/*连接数据库*/
EXECSQLCONNECT:
oid;
if（sqlca.sqlcode!
=0）{printf（"connectdatabaseerror\n"）;exit（0）;}
/*调用存储过程raise_sal*/
EXECSQLEXECUTE
BEGIN
Raise_sal（100,1000）;
END;
END-EXEC;
/*调用完毕*/
if（sqlca.sqlcode!
=0）{printf（"Executeerror\n"）;exit（0）;}
/*断开数据库连接*/
EXECSQLCOMMITWORKRELEASE;
}
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员工的工资
CREATEORREPLACEFUNCTIONget_sal（v_empnoINemp.empno%TYPE）
RETURNNUMBER
IS
v_emp_salemp.sal%TYPE:
=0;
BEGIN
SELECTsalINTOv_emp_sal
FROMemp
WHEREempno=v_empno;
RETURN（v_emp_sal）;
ENDget_sal;
创建存储函数
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
CREATEORREPLACEPACKAGEemp_package
AS
/*声明函数*/
FUNCTION
hire_emp（nameVARCHAR2,jobVARCHAR2,mgrNUMBER,hiredateDATE,salNUMBER
commNUMBER,deptnoNUMBER）
RETURNNUMBER;
/*声明过程*/
PROCEDUREfire_emp（emp_idNUMBER）;
PROCEDUREsal_raise（emp_idNUMBER,sal_idNUMBER）;
ENDemp_package;
创建包定义
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语句完全一样,只是可以使用定义的变量和表达式
DECLARE
v_empnoemp.empno%TYPENOTNULL:
=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使用数据操纵语句
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,逻辑运算符
例:
将emp表中的雇员名为SMITH雇员的工资修改,如果工资大于$2000,则加$500,否则加$1000.
DECLARE
v_enameemp.ename%TYPE:
='SMITH';
v_addsalemp.sal%TYPE;
v_salemp.sal%TYPE;
BEGIN
SELECTsalINTOv_sal
FROMempWHEREename=v_ename;
IFv_sal>2000THEN
v_addsal:
=500;
ELSE
v_addsal:
=1000;
ENDIF;
UPDATEempSETsal=sal+v_addsal
WHEREename=v_ename;
END;
PL/SQL控制语句IF_THEN_ELSE例程
IF_THAN_ELSIF语句:
语法:
IF条件THEN
语句;
ELSIF条件THEN
语句;
[ELSIF条件THEN语句;]
[ELSE语句;]
ENDIF
根据emp表中的工种为SMITH修改工资,若工种为MANAGER,工资加$1000,工种为SALESMAN,工资加$500,工种为ANALYST,工资加$200,否则加$100.
DECLARE
v_jobemp.job%TYPE;
v_addsalemp.sal%TYPE;
BEGIN
SELECTjobINTOv_jobFROMemp
WHEREename='SMITH';
IFv_job='MANAGER'THEN
v_addsal:
=1000;
ELSIFv_job='SAIESMAN'THEN
v_addsal:
=500;
ELSIFv_job='ANALYST'THEN
v_addsal:
=200;
ELSE
v_addsal:
=100;
ENDIF;
UPDATEempSETsal=sal+v_addsal
WHEREename='SMITH';
COMMITWORK;
END;
条件控制语句例程
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;
例给10号部门增加新雇员,只确定雇员代号,其他信息忽略.
DECLARE
v_empnoemp.empno%TYPE:
=8000;
BEGIN
LOOP
INSERTINTOemp（deptno,empno）
VALUES（10,v_empno）;
v_empno:
=v_empno+100;
/*如果雇员代号>=9000则退出循环*
升级会员 