数据存储过程.doc

数据存储过程PL/SQL语言简介3存储过程3创建存储过程3定义说明：4例子1：5存储过程删除5调用存储过程6存储函数(function)6创建存储函数6删除存储函数6例子2：6包（package）7包的创建8创建包体8例子3：8调用包中元素10删除包10PL/SQL语言10PL/SQL中的关系操作符10PL/SQL块基本结构12一个简单的PL/SQL块12定义变量13定义格式：13数据类型：13PLSQL中使用的SQL语句14PLSQL块中使用查询语句14PLSQL块中使用操纵语句15PL/SQL使用事物控制语句16PL/SQL流程控制161、条件控制语句162、循环控制语句183、跳转控制语句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 or replaceprocedure(prsi:d程序、手续、步骤) 存储过程名(参数定义表)is/as变量定义beginPL/SQL 语句块exception例外处理end存储过程名定义说明：参数定义表：存储过程可以有三类参数in 数据从调用环境传入存储过程out 数据从存储过程传入调用环境input 数据可以传入或传出存储过程参数使用原则：参数类型可以为Oracle允许的任意类型，也可以为%TYPE（与其他某一变量类型一致）或%ROWTYPE（与数据库中某一对象表，游标等数据类型一致）类型。指定参数时，不能指定长度所有输出参数（out）只能出现在select into 语句或赋值语句中。尽量减少in参数个数变量定义：变量类型可以为Oracle允许的任意类型，也可以为%TYPE（与其他某一变量类型一致）或%ROWTYPE（与数据库中某一对象表，游标等数据类型一致）类型例外处理：存储过程例外处理与PL/SQL错误处理一致，可按条件执行相应的操作。例子1：为指定雇员号的雇员加工资，数据源为表emp/* *create 创建存储过程 *replace 替换存储过程 *create or replace 如存储过程不存在则创建，否则替换/*create or replace procedure raise_sal(emp_id IN NUMBER, add_sal IN NUMBER)as/*无局部变量声明*/begin/*PL/SQL*/UPDATE emp SET sal = sal + add_sal WHERE empno = 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存储过程删除drop procedure 过程名调用存储过程Execute 存储过程名例子：execute raise_sal(10,1000);存储函数(function)创建存储函数create or replace function 存储函数名return 返回值类型is/as变量声明beginPLSQL 语句块exception例外处理end 存储函数名备注：返回值类型不带长度删除存储函数drop function 存储函数名例子2：从员工信息表中选择部门代号为v_empno员工的工资create or replace function get_sal(v_empno IN emp.empno%TYPE)return NUMBERisv_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;在PL/SQL中，不能用SELECT * INTO (错误)必须是SELECT 字段1，字段2， INTO 变量1，变量2， FROM 表SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。包（package）包的基本结构包中可以包含过程(procedure)，函数(function)、变量(variable)、游标(cursor)常量(constant)，例外处理(exception).包由两部分组成：包定义和包体包定义：对包的公共元素如过程，函数，变量，常量，游标，例外情况等进行说明，可在包外独立使用这些公共元素包体部分包括包中使用的私有元素和包的公共元素的定义。包的创建创建包定义create or replace package package_nameis/as公共元素声明end package_name创建包体create or replace package body package_nameis/as私有元素定义共有元素定义beginPL/SQL语句end package_name例子3：create or replace package emp_packageas/*声明函数*/function hire_emp(name VARCHAR2,job VARCHAR2,mgr NUMBER,hiredate DATE,sal NUMBER,comn NUMBER,deptno NUMBER)return NUMBER;/*声明过程*/procedure fire_emp(emp_id NUMBER);procedure sal_raise(emp_id NUMBER,sal_id NUMBER);end emp_package;/*创建包定义*/create or replace package body emp_package as/*定义函数*/function hire_emp(name varchar2,job varchar2,mgr number,hiredate date,sal nummber,comm number,deptno number)return NUMBERis new_empno NUMBER(10);beginselect emp_sequence.NEXTVAL INTO new_empno FROM emp;insert into emp values(new_empno,name,job,mgr,hiredate,sal,comm,deptno);return(new_empno);end hire_emp;/*定义过程*/procedure fire_emp(emp_id number) isbegindelete from emp where empno=emp_id;if sql%notfound thenraise_application_error(-20011,Ivalid EmployeeNumber|TO_CHAR(emp_id);end if;end fire_emp;procedure sal_raise(emp_id NUMBER,sal_id NUMBER) asbeginupdate emp set sal = sal + sal_id where empno = emp_id;if sql%notfound thenraise_application_error(-21011,Ivalid EmploteeNUMBER|TO_CHAR(emmmp_id);end if;end sal_raise;/*结束包定义*/end emp_package;调用包中元素execute 包名.元素名(参数列表);execute emp_package.raise_sal(7654,100);删除包drop PACKAGE 包名drop PACKAGE BODY 包名包定义和包体应该同时修改，并保持一致。PL/SQL语言Oracle存储过程以PL/SQL作为其流程控制语言，可以理解Oracle存储过程为具有名称和输入参数的PL/SQL语句块，因此，本章介绍PL/SQL的语法和使用。PL/SQL中的关系操作符operatoroperation小于操作符大于操作符=大于或等于操作符=等于操作符!=不等于操作符不等于操作符:=赋值操作符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各字段数据类型一致*/Myrecord emp%ROWTYPE;/*定义变量myempno，类型为NUMBER（4）变量非空，初始值8000*/Myempno number(4) NOT NULL := 8000;/*TYPE类型 定义变量myname 类型与表emp中ename字段一致*/Myname emp.ename%TYPE;/*constant 关键字 定义常量 addsal值为500*/Addsal constant number(4) := 500BeginSelect * into myrecord from emp where ename = SMITH;Myname := WUCHEN;Insert into emp(EMPNO,ENAME,SAL,COMM,JOB,HIREDATE,DEPTNO) values(myempno,myname,myrecord.sal,m,myrecord.job,myrecord.hiredate,myrecord.deptno);Update emp set sal = sal + addsal;End;定义变量在PLSQL中所使用的变量必须在变量定义部分明确定义，变量定义部分是包括在关键字declare 和begin 之间的部分，每条语句后用；结束。定义格式：变量标识符constant数据类型not null:=缺省值或PLSQL表达式变量标识符命名规则应遵循SQL实体命名规则定义常量时必须加constant，必须为其赋值如该变量不允许为空，必须加参数not null变量赋值时，可使用：=或使用关键字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块中，可以使用数据查询语言，数据操纵语言和数据控制语言，但不能使用数据定义语言，具体的说可以使用select insert update delete commit rollback，但不能使用create alter drop grant revoke.PLSQL块中使用查询语句在PLSQL中使用select 时必须加into语句Into字句后的变量个数和位置必须与select后的字段列表相同。Select语句中的where条件可以包含PLSQL块中定义的变量及表达式Select语句必须保证有且仅有一条记录返回，否则出错TOO_MANY_ROWS -1422记录多于一条NO_DATA_FOUND-1403没找到记录在SQL中使用的变量名必须与数据库字段名区分开Declare Emp_rec EMP%ROWTYPE;V_ename EMP.ename%TYPE:=SMITH;BeginSelect * into emp_rec from emp where ename = v_ename;End;PLSQL块中使用操纵语句PLSQL使用insert delete update 与 SQL语句完全一样，只是可以使用定义的变量和表达式Declare V_empno emp.empno%TYPE := 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;BeginInsert into emp(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;Update emp set sal = sal + v_addsal where empno = v_empno;Delete from emp where empno 8000;Commit work;End;PL/SQL使用事物控制语句提交命令（commit）：结束当前事物，对数据库做永久性改变。语法：commit work;回退命令（rollback）：结束当前事物，并放弃对数据库所做改变。语法：rollback work;保存点（savepoint）：为了避免一处失败导致全部事物回滚，可以使用savepoint和rollbackto语句语法：savepoint 标记 Rollback to 标记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，则加$500，否则加$1000DeclareV_ename emp.ename%TYPE = SMITH;V_addsal emp.sal%TYPE;V_sal emp.sal%TYPE;BeginSelect sal into v_sal where ename = v_ename;If v_sal 2000 thenV_addsal := 500;ElseV_addsal := 1000;End if;Update sal set sal = sal + v_addsal where ename = v_ename;End;IF_THEN_ELSIF语句语法：if 条件 then语句； Elsif 条件 then 语句； elsif 条件 then 语句;else 语句；End if;例子：根据emp表中的工种为SMITH修改工资，若工种为MANAGER，工资加$1000，工种为SALESMAN，工资加$500,工种为ANALYSE，工资加$200，否则加$100。DeclareV_job emp.job%TYPE;V_addsal emp.sal%TYPE;BeginSelect job into v_job from emp where ename = SMITH;If v_job =MANAGER thenV_addsal := 1000;Elsif v_job=SALESMAN thenV_addsal := 500;Elsif v_job=ANALYSE thenV_addsal := 200;ElseV_addsal := 100;End if;Update sal set sal = sal + v_addsal where ename = SMITH;Commit work;End;2、循环控制语句Loop 循环：语法：loop 语句；exit when 条件;End loop;例子：给10号部门增加新雇员，只确定雇员代号，其他信息忽略Declare V_empno emp.empno%TYPE:=8000;BeginLoop Insert into emp(deptno,empno) values(10,v_empno);V_empno = v_empno + 100;/*如果雇员号=9000,则退出循环*/Exit when v_empno = 9000;End loop;End;For循环：语法：for 计数器 in reverse 下界.上界 loop语句;End loop;计数器用于控制循环次数的变量，无需在定义部分做出定义，系统隐含定义为整数，reverse表示从上界到下界递减计数，下界定义初值，上界定义终值，下界应小于上界，对计数器不可做赋值操作。例子：同上例Declare V_deptno emp.deptno%TYPE := 10;BeginFor i in 1.10 loopInsert into emp(deptno,empno) values(v_deptno,8000+i*100);End loop;Commit work;End;While 循环语法：while 条件 loop语句;End loop;例子，同上例：Declare I number(2):=1;BeginWhile i=10 loopInsert into emp(deptno,empno) values(10,8000+i*100);I := i+1;End loop;Commit work;End;3、跳转控制语句语法：标号其他语句；Goto 标号；跳转语句可在同一块语句中跳转跳转语句可从子块跳转到父块中，但不能从父块跳转到子块中跳转语句不能在if语句体外跳到if语句体内跳转语句不能从循环体外跳到循环体内还是那个例子：DeclareV_empno emp.empno%TYPE:=8000;BeginrepeatInsert into emp(deptno,empno) values(10,v_empno);V_empno := v_empno + 100;If v_empno 9000 thenGoto repeat;End if;End;游标(cursor)在PL/SQL中，有的时候会返回多条记录，这时如使用SQL语句会出错，因此，在查询语句返回多条记录时或者不知返回结果数目时，必须使用游标。游标的概念PL/SQL中游标的使用与Pro C中游标的使用相似，包括定义、打开、提取数据、关闭四个步骤。一般游标在定义，打开后使用循环语句逐条处理提取的数据。一、定义游标语法：cursor 游标名称 is Select 语句;定义游标应写在PLSQL语句的declare变量定义部分定义游标时select语句中不可有into子语句在select语句中使用的变量必须在定义游标前定义二、打开游标语法：open 游标名;在begin语句之后，可以打开游标，在打开游标之前，必须对游标所涉及的变量赋值。三、利用游标提取数据语法：fetch 游标名 into 变量1，变量2游标每次只能取出一条数据，同时游标指针下移，等待取下一条数据，该条语句变量列表应与定义游标时的参数列表一致。四、关闭游标语法：close 游标名关闭游标，释放资源，游标关闭后，不能再提取数据。游标的属性游标的属性标示游标的运行状况。%ISOPEN布尔型表示游标是否打开%NOTFOUND布尔型描述最后一次fetch的结果%FOUND布尔型描述最后一次fetch的结果，与NOTFOUND相反%ROWCOUNT数字型描述当前取值的条数例子：查询10号部门所有雇员的姓名工资，并插到临时表tmp中使用NOTFOUND属性DeclareV_deptno emp.deptno%TYPE:=10;/*定义游标*/Cursor C1 isSelect ename,sal from emp where deptno = v_deptno;/*定义ROWTYPE型变量，emp_rec变量为与游标C1结构一致的结构型，即ename，sal结构*/Emp_rec C1%ROWTYPE;Begin/*打开游标*/Open C1;/*循环开始*/Loop/*提取数据到变量emp_rec结构中*/Fetch C1 into emp_rec;/*如果数据提取完毕，退出*/Exit when C1%NOTFOUND;Insert into tmp values(emp_rec.ename,emp_rec.sal);/*结束循环*/End loop;/*关闭游标*/Close C1；Commit work;End;使用ISOPEN属性/*如果游标已经打开*/If C1%ISOPEN thenFetch C1 into emp_rec;/*如果游标未打开，先打开游标再提取数据*/ElseOpen C1;Fetch C1 i