Oracle数据库存储过程技术文档.docx
- 文档编号:24030083
- 上传时间:2023-05-23
- 格式:DOCX
- 页数:62
- 大小:337.60KB
Oracle数据库存储过程技术文档.docx
《Oracle数据库存储过程技术文档.docx》由会员分享,可在线阅读,更多相关《Oracle数据库存储过程技术文档.docx(62页珍藏版)》请在冰豆网上搜索。
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
/*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则退出循环*
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 数据库 存储 过程 技术 文档