Oracle数据库存储过程技术文档.docx

1、Oracle数据库存储过程技术文档Oracle数据库存储过程技术文档 前言本文编写目的： 本文对ORACLE存储过程，存储函数，包作了一个概括性的介绍，以实例为驱动介绍了存储过程，存储函数，包的语法，数据类型以及程序开发编写的方法。通过对本文的学习，达到使用ORACLE存储过程进行基本编程的目的。本文主要参考： 新编RACLE7入门教程 电子工业出版社 ORACLE8I数据库高级应用开发技术人民邮电出版社 ORACLE8 PL/SQL程序设计机械工业出版社本文面向对象： 对ORACLE有一定认识和经验的开发者和系统管理者。本文中各例均使用Oracle数据库demo用户. 用户名:scott 用

2、户口令:tiger 数据结构建立: /*使用system用户及口令登录oracle数据库*/ $SQLPLUS system/passwd /*建立scott用户口令为tiger*/ $SQLcreate user scott identified by tiger; /*给scott用户授权*/ $SQLgrant create session to scott; $SQLexit; $SQLPLUS scott/tiger $SQLstart $ORACLE_HOME/sqlplus/demo/demobld.sql主要数据结构：第一章 oracle存储过程概述Oracle存储过程(sto

3、re procedure)作为PL/SQL语言的子程序,使用PL/SQL语言对数据处理逻辑,数据存储,数据操纵进行描述和封装,通过oracle其他工具(Pro*c&sqlplus等)对存储过程调用,实现相应功能.Oracle存储过程在创建时经过数据库编译,作为数据库对象存储在数据库中,使用存储过程名称和输入输出参数实现存储过程描述的功能. 存储过程是由流控制和SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，使用时只要调用即可。在ORACLE中，若干个有联系的过程可以组合在一起构成程序包。使用存储过程有以下的优点：1.存储过程的能力大大增强了SQL语言的功能和灵活性。存储过程可

4、以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算.2.可保证数据的安全性和完整性。3.通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。4.通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。5.再运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案。这种已经编译好的过程可极大地改善SQL语句的性能。由于执行SQL语句的大部分工作已经完成，所以存储过程能以极快的速度执行。6.可以降低网络的通信量。7.使体现企业规则的运算程序放入数据库服务器中，以便集中控制。 当企业规则发生变化时在服务器中改变存储过程即可，无须

5、修改任何应用程序。企业规则的特点是要经常变化，如果把体现企业规则的运算程序放入应用程序中，则当企业规则发生变化时，就需要修改应用程序工作量非常之大（修改、发行和安装应用程序）。如果把体现企业规则的运算放入存储过程中，则当企业规则发生变化时，只要修改存储过程就可以了，应用程序无须任何变化. Oracle存储函数(FUNCTION)作为特殊的存储过程,与C/C+语言函数相似,具备函数名,输入输出参数以及返回值.存储过程和存储函数都是相对独立的实体.Oracle包(Package)为了管理上的方便,把一些相关的程序结构如存储过程,存储函数,变量,游标等组织在一起,构成一个包.Oracle包具有面向对

6、象程序设计语言的特点,是对PL/SQL程序设计元素的封装.包类似于C+和JAVA语言中的类,其中变量相当于类中的成员变量,存储过程和存储函数相当于类方法.包中的元素分为共有元素和私有元素,两种元素允许访问的程序范围不同.1.1存储过程基本结构(PROCEDURE)1.1.1创建存储过程CREATEOR REPLACE PROCEDURE存储过程名（参数定义标）IS/AS变量定义BEGIN PL/SQL语句块 EXCEPTION 例外处理 END 存储过程名 定义说明:1.参数定义表:存储过程可以有三类参数IN 数据从调用环境传入存储过程OUT 数据从存储过程传入调用环境INOUT 数据可以传入

7、或传出存储过程参数使用原则: 参数类型可以为ORACLE允许的任意类型,也可为%TYPE(与其他某一变量类型一致)或%ROWTYPE(与数据库中某一对象表,游标等数据类型一致) 类型 指定参数时,不能指定长度 所有输出参数(OUT)只能出现在SELECT INTO语句或赋值语句中. 尽量减少IN参数个数.2.变量定义 变量类型可以为ORACLE允许的任意类型,也可为%TYPE(与其他某一变量类型一致)或%ROWTYPE(与数据库中某一对象表,游标等数据类型一致) 类型.3.例外处理存储过程例外处理与PL/SQL错误处理一致,可按条件执行相应的操作.例1.1 本例实现为指定雇员号(emp)的雇员

8、加工资(sal),数据源为表emp /*CREATE 创建存储过程*/*REPLACE 替换存储过程*/*CREATE OR REPLACE 如存储过程不存在则创建,否则替换*/ CREATE OR REPLACE PROCEDURE /*存储过程名为raise_sal*/*参数为输入NUMBER型 emp_id和输入NUMBER型 add_sal*/raise_sal(emp_id IN NUMBER,add_sal IN NUMBER) AS/*无局部变量声明*/BEGIN /*PLSQL语句块*/ UPDATE emp SET sal = sal+ add_salWHERE empno

9、= emp_id;EXCEPTION /*例外处理 NO_DATA_FOUND 数据未找到时执行*/WHEN NO_DATA_FOUND THEN/*raise_application_error(错误代码,错误信息) 向调用环境返回错误信息*/ raise_application_error(-20011,InvalidEmployee|TO_CHAR(emp_id);END raise_sal;存储过程简例1.1.2 存储过程删除 $SQLPLUS DROP PROCEDURE 过程名1.1.3 调用存储过程1.SQLPLUS环境语法 $SQLPLUS EXECUTE 存储过程名 参数 S

10、QLPLUS中的变量或常量例: $SQLEXECUTE raise_sal(10,1000);2.SQLDBA环境语法 $SQLPLUS EXECUTE 存储过程名 参数 SQLPLUS中的变量或常量3.SQLFORMS 语法:过程名 参数:SQLFORMS中的域或全局变量4.PLSQL或其他存储过程语法:过程名 参数:PLSQL局部变量5.Pro*C 语法:EXECSQL 过程名参数:主变量例1.2#include EXEC SQL INCLUDE SQLCA;main()/*声明宿主变量*/ EXEC SQL BEGIN DECLARE SECTION; char *oid=scott/t

11、iger; int tt;EXEC SQL END DECLARE SECTION;/*连接数据库*/ EXEC SQL CONNECT :oid; if(sqlca.sqlcode != 0) printf(connect database error n);exit(0);/*调用存储过程raise_sal*/ EXEC SQL EXECUTE BEGIN Raise_sal(100,1000); END; END-EXEC;/*调用完毕*/ if(sqlca.sqlcode != 0) printf(Execute error n);exit(0); /*断开数据库连接*/EXEC SQ

12、L COMMIT WORK RELEASE;1.2存储函数(FUNCTIONE)存储函数是一类特殊的存储过程,与一般存储过程不同的是存储函数必须返回一个值.1.2.1 创建存储函数CREATE OR REPLACE FUNCTION 存储函数名RETUNR 返回值类型IS/AS 变量声明BEGIN PLSQL语句块EXCEPTION 例外处理END 存储函数名备注: 返回值类型不带长度1.2.2 删除存储函数 $SQLdrop function 存储函数名例1.3 从员工信息表(emp)中选择部门代号为v_empno员工的工资CREATE OR REPLACE FUNCTION get_sal

13、(v_empno IN emp.empno%TYPE)RETURN NUMBERIS v_emp_sal emp.sal%TYPE:=0;BEGIN SELECT sal INTO v_emp_sal FROM emp WHERE empno=v_empno; RETURN(v_emp_sal);END get_sal;创建存储函数1.3 包(package)1.3.1 包的基本结构包中可以包含过程(procedure),函数(function),变量(variable) 游标(cursor),常量(constant),例外处理(exception). 包由两部分组成:包定义和包体 包定义:对

14、包的公共元素如过程,函数,变量,常量,游标,例外情况等进行说明,可在包外独立使用这些公共元素. 包体部分包括包中使用的私有元素和包的公共元素的定义.1.3.2 包的创建1.创建包定义CREATEOR REPLACEPACKAGE package_nameIS/AS公共元素声明END package_name;2.创建包体CREATEOR REPLACE PACKAGE BODY package_nameIS/AS私用元素定义公共元素定义BEGIN PLSQL语句END package_name;例 1.4CREATE OR REPLACE PACKAGE emp_packageAS/*声明函数

15、*/FUNCTION hire_emp(name VARCHAR2,job VARCHAR2,mgr NUMBER,hiredate DATE,sal NUMBER,comm NUMBER,deptno NUMBER)RETURN NUMBER;/*声明过程*/PROCEDURE fire_emp(emp_id NUMBER);PROCEDURE sal_raise(emp_id NUMBER,sal_id NUMBER);END emp_package;创建包定义1.3.3 调用包中元素$SQLEXECUTE 包名.元素名(参数列表);例: $SQLEXECUT emp_package.ra

16、ise_sal(7654,100);1.3.4 包的修改和删除删除包:$SQLdrop PACKAGE 包名 $SQLdrop PACKAGE BODY 包名备注:包定义和包体应该同时修改,并保持一致.第二章 oracle存储过程基础PL/SQLOracle存储过程以PL/SQL作为其流程控制语言,可以理解Oracle存储过程为具有名称和输入输出参数的PL/SQL语句块,因此,本章介绍PL/SQL的语法和使用. 2.1pl/sql基础2.1.1 PL/SQL简介一 PL/SQL优点1.过程化能力 PL/SQL称为SQL过程语言,他将高级程序设计语言中所具备的过程能力与非过程化的SQL语言有机的

17、结合在一起,形成了一个集成式的Oracle数据库事务处理应用开发工具,为应用开发者提供了增强生产力的机制. PL/SQL以块(blocks)为单位,较大的块中可以镶嵌子块,可以将复杂的问题分解成一组易于控制的,很好定义的逻辑模块. 在PL/SQL块中可以进行变量定义,例外处理,然后在SQL语句中调用.PL/SQL块中可以使用过程化语言控制结构进行程序设计,包括条件转移,循环控制,游标. 2.改进处理性能 使用PL/SQL,Oracle数据库将PL/SQL语句块作为一组,一次提交给Oracle服务进程,减少Oracle客户服务进程间的交互. 3.良好的应用移植性 由于PL/SQL是模块化结构,在

18、进行应用移植时可以将模块内部的复杂处理忽略,二只考虑模块间的数据交换. 4.与关系数据库管理系统(RDBMS)集成 使用PL/SQL,可以将许多用户都可能用到的处理编程封装过程或包,与关系数据库管理系统有效集成.这样,用户可使用Oracle工具直接调用,提高了开发效率,减少了再编译时间,提高系统性能.二 PL/SQL应用环境 SQL*PLUS;SQL*FORMS;Oracle CDE 工具;Pro*C 三 PL/SQL块的基本结构基本的PL/SQL块由定义部分,执行部分,例外处理部分组成1.定义部分: 定义在程序执行部分使用的常量,变量,游标和例外处理名称2.可执行部分 包括数据库操作语句和P

19、L/SQL块控制语句3.例外处理部分 对执行部分的所有PL/SQL语句的执行进行监控,如执行发生例外,则程序跳到该部分执行2.1.2 一个简单的PL/SQL块 例2.1 向EMP表插入一条雇员记录,雇员代号为8000,雇员名是WUCHEN,其他字段与SMITH雇员相同,然后将所有雇员工资增加$500.执行步骤: $SQLPLUS scott/tiger $SQLstart ./PLSQL块名称 $SQL. (输入符号点) $SQLr (字母r 或符号/执行程序)一 定义变量 在PLSQL中所使用的变量必须在变量定义部分明确定义.变量定义部分是包括在关键字DECLARE和BEGIN之间的部分,每

20、条语句后用(;)结束. 定义格式: 变量标示符 CONSTANT 数据类型 NOT NULL :=缺省值或PLSQL表达式; 变量标示符命名规则应遵循SQL实体命名规则 定义常量时必须加关键字 CONSTANT 必须为其赋值 如该变量不允许为空值,必须加参数NOT NULL 变量赋值时,可使用:=或使用关键字DEFAULT. 每行只能定义一个变量. 数据类型 简单数据类型(标量数据类型): NUMBER(m,n) 数字类型 m为总长度,n为小数长度 CHAR(m) 字符型 m为变量长度 VARCHAR2(m) 可变长字符型 m为最大长度 DATE 日期型 LONG 长型 BOONEAN 布尔型

21、 值为TRUE FALSE NULL 已定义变量%TYPE 定义成与已定义变量一致类型 复合数据类型 变量标示符 对象标示符%ROWTYPE; 对象标示符可为表,游标等,变量被定义成与数据库对象一致的类型结构,当数据库结构改变时,不必改变改变量的定义. 使用%ROWTYPE分为两种不同情况:1.作为查询结果存放空间时:select 字段列表 INTO%ROWTYPE型变量2.作为单个成员使用:%ROWTYPE变量名.字段名二 变量赋值变量赋值时需使用PLSQL变量赋值操作符(:=)1.常量赋值: 变量名 := 常量2.变量赋值: 变量名 := 同类型变量3.为%ROWTYPE型变量赋值a .

22、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块中使用查询语句在

23、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_empno emp.empno

24、%TYPE NOT NULL:=8000; v_ename emp.ename%TYPE:=Bill; v_job emp.job%TYPE:=MANAGER; v_sal emp.sal%TYPE:=2000; v_comm m%TYPE:=1000; v_hiredate emp.hiredate%TYPE:=SYSDATE; v_deptno emp.deptno%TYPE:=10; v_addsal emp.sal%TYPE;BEGIN INSERT INTO emp(empno,ename,job,sal,comm,hiredate,deptno) VALUES (v_empno,v

25、_ename,v_job,v_sal,v_comm,v_hiredate,v_deptno); v_addsal:=1000; UPDATE emp SET sal=sal+v_addsal WHERE empno=v_empno; DELETE FROM emp WHERE empno 8000; COMMIT WORK;END;PL/SQL使用数据操纵语句3.PL/SQL块中使用事物控制语句提交命令(COMMIT):结束当前事物,对数据库作永久性改变 语法 COMMIT WORK回退命令(ROLLBACK): 结束当前事物,并放弃对数据库所作修改 语法 ROLLBACK WORK保存点(S

26、AVEPOINT):为了避免一处失败导致全部事物回滚,可以使用SAVEPOINT和ROLLBACKTO语句 语法 SAVEPOINT 标记 ROLLBACK TO 标记2.1.3 PL/SQL流程控制PL/SQL具有与高级语言类似的流程控制语句.PL/SQL主要控制语句有: 条件控制语句 循环控制语句 跳转控制语句1.条件控制语句:IF_THEN_ELSE语句语法: IF 条件 THEN 语句; ELSE 语句; END IF;条件可为IS NULL或NOT IS NULL以及AND, OR, NOT,逻辑运算符例:将emp表中的雇员名为SMITH雇员的工资修改,如果工资大于$2000,则加$

27、500,否则加$1000.DECLARE v_ename emp.ename%TYPE:=SMITH; v_addsal emp.sal%TYPE; v_sal emp.sal%TYPE;BEGIN SELECT sal INTO v_sal FROM emp WHERE ename=v_ename; IF v_sal 2000 THEN v_addsal:=500; ELSE v_addsal:=1000; END IF; UPDATE emp SET sal=sal+v_addsal WHERE ename=v_ename;END;PL/SQL控制语句IF_THEN_ELSE例程IF_TH

28、AN_ELSIF语句: 语法:IF 条件 THEN 语句; ELSIF 条件 THEN 语句; ELSIF 条件 THEN 语句; ELSE 语句; END IF根据emp表中的工种为SMITH修改工资,若工种为MANAGER,工资加$1000,工种为SALESMAN,工资加$500,工种为ANALYST,工资加$200,否则加$100.DECLARE v_job emp.job%TYPE; v_addsal emp.sal%TYPE;BEGIN SELECT job INTO v_job FROM emp WHERE ename=SMITH; IF v_job=MANAGER THEN v_

29、addsal :=1000; ELSIF v_job=SAIESMAN THEN v_addsal :=500; ELSIF v_job=ANALYST THEN v_addsal :=200; ELSE v_addsal :=100; END IF; UPDATE emp SET sal=sal+v_addsal WHERE ename=SMITH; COMMIT WORK;END;条件控制语句例程ELSIF v_job=SALESMAN THENv_addsal=500;ELSIF v_job=ANALYST THENV_addsal=200;ELSE v_addsal=200;END IF UPDATE emp SET sal=sal+v_addsalWHERE ename=SMITH;END;2.循环控制语句:LOOP循环: 语法: LOOP 语句; EXIT WHEN 条件; END LOOP;例 给10号部门增加新雇员,只确定雇员代号,其他信息忽略.DECLARE v_empno emp.empno%TYPE:=8000;BEGIN LOOP INSERT INTO emp(deptno,empno) VALUES(10,v_empno); v_empno:=v_empno+100; /*如果雇员代号=9000则退出循环*