oracle存储过程培训资料.ppt

Oracle9i PL/SQL程序设计,PL/SQL程序设计,2,PL/SQL 概述,PL/SQL是Oracle对关系数据库语言SQL的过程化扩充,它将数据库技术和过程化程序设计语言联系起来,是一种应用开发语言,可使用循环,分支处理数据,将SQL的数据操纵功能与过程化语言数据处理功能结合起来.PL/SQL的使用,使SQL成为一种高级程序设计语言,支持高级语言的块操作,条件判断,循环语句,嵌套等,与数据库核心的数据类型集成,使SQL 的程序设计效率更高.PL/SQL是一种块结构语言,构成PL/SQL的基本单位是逻辑块,块中可以嵌套子块,以块为单位向Oracle Server传递数据执行,其块结构如下:DECLARE BEGIN EXCEPTION END;,说明部分:说明变量及常量,执行部分:使用SQL的DML语言事务处理,例外处理:处理错误及警告信息,逻辑块结束,PL/SQL程序设计,3,PL/SQL语言的特点,PL/SQL中可以定义变量,变量有其作用范围PL/SQL是以块的方式设计,块中可以嵌套子块,子块可以位于块中任何部分子块可以是PL/SQL中的一条命令PL/SQL中可以使用条件判断,循环,游标等高级语言程序设计方法模块式的过程化语言,以块为单位执行使用PL/SQL可以优化程序设计,得到更好的性能(例如,执行 10个SQL语句,需要访问10次 Oracle核心,如果10个SQL组成一个PL/SQL程序,则只需访问一次Oracle 核心,将结果一次返回给用户,则程序执行效率高,节省时间,降低通信量),PL/SQL程序设计,4,减少对于Oracle核心的访问,降低网络负责数据库数据类型集成PL/SQL支持全部的SQL数据类型,这些共享的数据类型与SQL所提供的直接存取相结合,使PL/SQL与Oracle数据字典结合成一体.PL/SQL与Oracle RDBMS集成在一起,可以使用PL/SQL的集成数据类型,使变量的数据类型在数据库的操作中实时确定.,PL/SQL语言的特点,PL/SQL程序设计,5,PL/SQL与SQL语言结合,PL/SQL语句含有SQL的DML语句,检索及事务处理PL/SQL中可以使用的SQL语句如下:INSERT UPDATE DELETE SELECT INTO COMMIT ROLLBACK SAVEPOINT支持 RDBMS 的所有数据类型 对于SQL的数据类型进行了扩充,增加了新的数据类型,使PL/SQL所支持的数据类型更灵活.Oracle RDBMS 对于PL/SQL中引用的表名,列名进行语句检查对于SQL语句的执行结果,PL/SQL提供返回信息检查,通过PL/SQL的例外处理,记录SQL的执行结果.,PL/SQL程序设计,6,PL/SQL基础,变量的使用 和其他高级语言一样,在PL/SQL中可以使用变 量注释 单行注释:-多行注释:/*/数据类型 日期型 Date 字符型 CHAR,VARCHAR2 数字型 NUMBER 布尔型 BOOLEAN(TRUE,FALSE,NULL),数据类型,PL/SQL数据类型扩展,数字型NUMBER 存储整型及实型数据，可以定义精度和取值范围BINARY_INTEGER 可存储带符号整数，为整数计算优化性能 DEC NUMBER的子类型，存储实型数据 DOUBLE PRECISION NUMBER的子类型，高精度实数 INTEGER NUMBER的子类型，整数 INT NUMBER的子类型，整数,PL/SQL数据类型扩展,数字型SMALLINT NUMBER的子类型，取值范围比INTEGER小NUMERIC NUMBER的子类型，与NUMBER等价REAL NUMBER的子类型，存储实型数据,PL/SQL数据类型扩展,字符型VARCHAR2 存放可变长字符串，有最大长度限制 CHAR 字符型，固定长度 LONG 长字符型 CLOB 大对象字符(Oracle8、Oracle8i)日期型 Date 存储日期数据,PL/SQL数据类型扩展,布尔型 BOOLEAN 结果是TRUE或FALSE 二进制数据 RAW 存储多媒体数据，如图象、声音、视频等 BLOB 大对象如图象、声音、视频等（Oracle8、Oracle8i）,PL/SQL程序设计,11,PL/SQL基础,数据定义 标示符 数据类型;标示符 数据类型:=PL/SQL表达式;例如,v_salary number(7,2);v_ename varchar2(20):=SMITH;,PL/SQL程序设计,12,PL/SQL基础,变量赋值 变量名:=值;v_salary:=890.76;v_date:=25-MAR-99;Available_now:=FALSE;%TYPE类型Credit Real(7,2);Debit Credit%TYPE;My_name Employee.ename%TYPE;%TYPE 属性+不必知道My_name的真正数据类型+当数据库中列ename列定义改变时，数 据库运行时自动修改%ROWTYPE:行类型,PL/SQL程序设计,13,PL/SQL基础,PL/SQL 的记录类型把逻辑相关的数据作为一个单元存储起来,在 Declare 段中定义record类型数据，使某一 变量使用该record型数据定义方法：TYPE r_record is RECORD(v_name emp.ename%TYPE,v_job emp.job%TYPE,v_sal emp.sal%TYPE);变量定义 r_emp r_record;变量使用SELECT ename,job,sal INTO r_emp FROM emp WHERE empno=7934;则，r_emp.v_ename,r_emp.v_job,r_emp.v_sal 已有 值；给变量赋值：r_employee r_record;r_employee.v_ename:=JACK;r_employee.v_job:=CLERK;r_employee.v_sal:=890.98;,PL/SQL程序设计,PL/SQL程序设计,14,PL/SQL基础,TABLE类型数据PL/SQL中的表(table)类型类似于语言中的结构类型数组定义方法：TYPE table_emp IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;一个PL/SQL表有两个列，(key,value),key列类型即是 BINARY_INTEGER,而value类型则是所定义的数据类型Table类型使用：定义变量 my_name为 table_emp 类型,则可以使用变量 my_name,也可以在SQL语句中使用 table类型变量my_name table_emp;my_name(0):=SCOTT;my_name(1):=SMITH;my_name(2):=SUSAN;SELECT ename INTO my_name(10)FROM emp WHERE empno=7934;,PL/SQL程序设计,15,在PL/SQL中使用SQL,INSERT语句的使用 Declare v_empno EMP.empno%TYPE:=1234;v_ename EMP.ename%TYPE:=SCOTT;v_job VARCHAR2(15):=MANAGER;v_deptno EMP.deptno%TYPE:=20;v_sal NUMBER(7,2):=890.50;Begin INSERT INTO emp(empno,ename,job,hiredate,sal,deptno)VALUES(v_empno,v_ename,v_job,SYSDATE,v_sal,v_deptno);END;/,注意:非空(NOT NULL)必须有值,NOT NULL,PL/SQL程序设计,16,PL/SQL中使用SQL,DELETE 语句的使用 Declare v_empno EMP.empno%TYPE:=1234;Begin DELETE FROM emp WHERE empno=v_empno;End;事务处理语句的使用在PL/SQL中可以使用SQL的 COMMIT,ROLLBACK及 SAVEPOINT语句Declare v_empno EMP.empno%TYPE:=1234;Begin DELETE FROM emp WHERE empno=v_empno;COMMIT;End;/,PL/SQL程序设计,条件控制语句,IF-THEN语句 IF 条件成立 THEN 可执行语句;END IF;,条件控制语句,IF-THEN-ELSE语句 IF 条件成立 THEN 执行语句1;ELSE 执行语句2；END IF;,条件控制语句,IF-THEN-ELSIF语句 IF 条件1成立 THEN 执行语句1;ELSIF 条件2成立 THEN 执行语句2；ELSIF 条件3成立 THEN 执行语句3;.END IF;,循环语句,基本循环 LOOP 执行语句；EXIT WHEN 条件成立；END LOOP;,循环语句,WHILE循环 WHILE 条件成立 LOOP 执行语句；END LOOP;,循环语句,FOR循环 FOR 计数器 IN 低界.高界 LOOP 执行语句；END LOOP;,PL/SQL程序设计,23,光标(CURSOR)设计,什么是光标光标使用光标说明：在Declare 段中命名光标，将光标与一查询语句联系起来.语法：CURSOR 光标名(参数)IS SELECT 字句；打开光标 获取活动集中的行 FETCH语句检索活动集中的行，每次一行，每执行一次FECTCH，光标前进到活动集中的下一行。光标下移关闭光标,PL/SQL程序设计,24,光标属性,光标属性 每一个光标有四种属性%FOUND 查询语句(FETCH语句)返回记录%NOTFOUND 查询语句(FETCH语句)无返回记录，用于循环退出条件%ROWCOUNT FETCH已获取的记录数%ISOPEN 光标已打开标记 if not C%ISOPEN then open C;end if;,Loop FETCH C INTO v_ename,v_sal;EXIT WHEN C%NOTFOUND;END LOOP;,属性使用,PL/SQL程序设计,25,隐式光标处理,隐式光标处理 隐式光标是指在处理SQL时,不需定义光标，所使用的SQL语句包括：INSERT,UPDATE,DELETE子句。隐式光标属性 SQL%FOUND SQL%NOTFOUND 由执行上述语句结果引起 SQL%ROWCOUNT隐式光标的使用Begin update emp set sal=1200 where empno=1234;if SQL%NOTFOUND then insert into Errors(empno,sal)values(1234,1200);end if;End;,数据库中没有编码为1234的职工,PL/SQL程序设计,26,例外处理(EXCEPTION),在PL/SQL中,警告信息、出错信息、或返回信息统称为例外(Exception)。有两中类型的例外。Oracle预定义的例外:是由PL/SQL运行过程中，系统自动产生的信息。用户定义例外:是用户根据需要，自己定义使用的例外，执行时 由用户自己引起。预定义的例外CURSOR_ALREADY_OPEN VALUE_ERRORNO_DATA_FOUND INVALID_NUMBERTOO_MANY_ROWS ZERO_DIVIDEINVALID_CURSOR用户自定义例外 用户定义的例外必须在DECLARE段中说明，在Begin段中用RAISE引起，在EXCEPTION段中使用。,PL/SQL程序设计,27,例外处理(EXCEPTION),用户定义例外的说明：定义名为 out_of_range 的例外 Declare out_of_range EXCEPTION;例外的使用DeclareBeginException When NO_DATA_FOUND THEN When CURSOR_ALREADY_OPEN THEN When TOO_MANY_ROWS THEN.When OTHERS THEN.END;,例外段,在Exception段中对返回信息一一作出响应,PL/SQL程序设计,28,例外处理(EXCEPTION),用户自定义例外的使用Declare out_of_range EXCEPTION;Begin if v_sal MAX_SAL then RAISE out_of_range;end if;Exception When NO_DATA_FOUND THEN When CURSOR_ALREADY_OPEN THEN When TOO_MANY_ROWS THEN.When out_of_range THEN.END;,例外段,在Declare段定义,在Begin段中引起,在Exception段中使用,