PLSQL--清华大学计算中心ORACLE培训资料.ppt

Oracle9i PL/SQL程序设计 Procedural Language 1PL/SQL程序设计 PL/SQL 概述 PL/SQL是Oracle对关系数据库语言SQL的过程化扩充,它将数据库技术和 过程化程序设计语言联系起来,是一种应用开发语言,可使用循环,分支处理 数据,将SQL的数据操纵功能与过程化语言数据处理功能结合起来. PL/SQLPL/SQL的使用的使用 , , 使使SQLSQL成为一种高级程序设计语言成为一种高级程序设计语言 , , 支持高支持高 级语言的块操作级语言的块操作 , , 条件判断条件判断 , , 循环语句循环语句 , , 嵌套等嵌套等 , , 与数据库核心的与数据库核心的 数据类型集成数据类型集成 , , 使使SQL SQL 的程序设计效率更高的程序设计效率更高 . . PL/SQLPL/SQL是一种块结构语言是一种块结构语言, ,构成构成PL/SQLPL/SQL的基本单位是逻辑的基本单位是逻辑 块块, ,块中可以嵌套子块块中可以嵌套子块, ,以块为单位向以块为单位向Oracle ServerOracle Server传递数据传递数据 执行执行, ,其块结构如下其块结构如下: : DECLAREDECLARE BEGIN BEGIN EXCEPTION EXCEPTION END; END; 说明部分:说明变量及常量 执行部分:使用SQL的DML语言事务处理 例外处理:处理错误及警告信息 逻逻 辑辑 块块 结结 束束 2PL/SQL程序设计 PL/SQL语言的特点 PL/SQL中可以定义变量,变量有其作用范围 PL/SQL是以块的方式设计,块中可以嵌套子块,子块可以位 于块中任何部分 子块可以是PL/SQL中的一条命令 PL/SQL中可以使用条件判断,循环,游标等高级语言程序设 计方法 模块式的过程化语言模块式的过程化语言 , , 以块为单位执行以块为单位执行 使用使用PL/SQLPL/SQL可以优化程序设计可以优化程序设计 , , 得到更好的性能得到更好的性能 ( ( 例如例如 , , 执行执行 10 10个个SQLSQL语句语句 , , 需要访问需要访问1010次次 OracleOracle核心核心 , , 如果如果 1010个个SQLSQL组成一个组成一个PL/SQLPL/SQL程序程序 , , 则只需访问一次则只需访问一次Oracle Oracle 核核 心心 , , 将结果一次返回给用户将结果一次返回给用户 , , 则程序执行效率高则程序执行效率高 , , 节省时间节省时间 , , 降降 低通信量低通信量 ) ) 3PL/SQL程序设计 减少对于Oracle核心的访问,降低网络负责 数据库数据类型集成 PL/SQL支持全部的SQL数据类型,这些共享的数据类型与SQL所提供的直接存取相结合,使PL/SQL与Oracle数 据字典结合成一体. PL/SQL与Oracle RDBMS集成在一起,可以使用PL/SQL 的集成数据类型,使变量的数据类型在数据库的操作中实 时确定. PL/SQL语言的特点 4PL/SQL程序设计 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的执行结果. 5PL/SQL程序设计 PL/SQL基础 变量的使用 和其他高级语言一样,在PL/SQL中可以使用变 量 注释 单行注释: - 多行注释: /* */ 数据类型 日期型 Date 字符型 CHAR,VARCHAR2 数字型 NUMBER 布尔型 BOOLEAN(TRUE,FALSE,NULL) 数据类型 Float Int Real Smallint子类 6PL/SQL程序设计 PL/SQL数据类型扩展 标示符 数据类型 := PL/SQL表达式; 例如, v_salary number(7,2); v_ename varchar2(20) :=SMITH; 11PL/SQL程序设计 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; 变量定义 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/SQLPL/SQL程序设计程序设计 13PL/SQL程序设计 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; 14PL/SQL程序设计 在PL/SQL中使用SQL %TYPE :=1234; v_ v_ename ename EMP.EMP.enameename%TYPE :=SCOTT;%TYPE :=SCOTT; v_job VARCHAR2(15) :=MANAGER; v_job VARCHAR2(15) :=MANAGER; v_ v_deptno deptno EMP.EMP.deptnodeptno%TYPE :=20;%TYPE :=20; v_ v_sal sal NUMBER(7,2) :=890.50;NUMBER(7,2) :=890.50; Begin Begin INSERT INSERT INTO INTO empemp( (empnoempno, ,enameename,job,job,hiredatehiredate, ,salsal, ,deptnodeptno) ) VALUES(v_ VALUES(v_empnoempno,v_,v_enameename,v_job,SYSDATE,v_,v_job,SYSDATE,v_salsal,v_,v_deptnodeptno); ); END; END; / / 注意注意 : : 非空非空( (NOT NULL) NOT NULL) 必须有值必须有值 NOT NULLNOT NULL 15PL/SQL程序设计 PL/SQL中使用SQL %TYPE :=1234; Begin Begin DELETEDELETE FROM FROM emp emp WHERE WHERE empnoempno=v_=v_empnoempno; ; End; End; %TYPE :=1234; BeginBegin DELETE FROM DELETE FROM emp emp WHERE WHERE empnoempno=v_=v_empnoempno; ; COMMIT;COMMIT; End; End; / / PL/SQLPL/SQL程序设计程序设计 16PL/SQL程序设计 条件控制语句 IF-THEN语句 IF 条件成立 THEN 可执行语句; END IF; 17PL/SQL程序设计 条件控制语句 IF-THEN-ELSE语句 IF 条件成立 THEN 执行语句1; ELSE 执行语句2； END IF; 18PL/SQL程序设计 条件控制语句 IF-THEN-ELSIF语句 IF 条件1成立 THEN 执行语句1; ELSIF 条件2成立 THEN 执行语句2； ELSIF 条件3成立 THEN 执行语句3; . END IF; 19PL/SQL程序设计 循环语句 基本循环 LOOP 执行语句； EXIT WHEN 条件成立； END LOOP; 20PL/SQL程序设计 循环语句 WHILE循环 WHILE 条件成立 LOOP 执行语句； END LOOP; 21PL/SQL程序设计 循环语句 FOR循环 FOR 计数器 IN 低界高界 LOOP 执行语句； END LOOP; 22PL/SQL程序设计 光标(CURSOR)设计 什么是光标 光标使用 光标说明 ：在Declare 段中命名光标，将光标与一查询语句联系起来. 语法： CURSOR 光标名(参数) IS SELECT 字句； 打开光标 获取活动集中的行 FETCH语句检索活动集中的行，每次一行，每执行一次FECTCH，光标前进到活动集中的下一行。 光标下移 关闭光标 23PL/SQL程序设计 光标属性 光标属性 每一个光标有四种属性 %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; 属性使用 24PL/SQL程序设计 隐式光标处理 隐式光标处理 隐式光标是指在处理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; 数据库中没有编码为数据库中没有编码为12341234的职工的职工 25PL/SQL程序设计 例外处理(EXCEPTION) 在在PL/SQLPL/SQL中中 , , 警告信息、出错信息、或返回信息统称为例外警告信息、出错信息、或返回信息统称为例外( (Exception)Exception)。有两中类型的例外。有两中类型的例外。 Oracle预定义的例外: 是由PL/SQL运行过程中，系统自动产生的信息。 用户定义例外: 是用户根据需要，自己定义使用的例外，执行时 由用户自己引起。 预定义的例外 CURSOR_ALREADY_OPEN VALUE_ERRORCURSOR_ALREADY_OPEN VALUE_ERROR NO_DATA_FOUND INVALID_NUMBERNO_DATA_FOUND INVALID_NUMBER TOO_MANY_ROWS ZERO_DIVIDETOO_MANY_ROWS ZERO_DIVIDE INVALID_CURSORINVALID_CURSOR 用户自定义例外 用户定义的例外必须在DECLARE段中说明，在 Begin段中用RAISE引起，在EXCEPTION段中使用。 26PL/SQL程序设计 例外处理(EXCEPTION) 用户定义例外的说明：定义名为 out_of_range 的例外 Declare out_of_range EXCEPTION; 例外的使用 Declare Begin Exception When NO_DATA_FOUNDNO_DATA_FOUND THEN When CURSOR_ALREADY_OPENCURSOR_ALREADY_OPEN THEN When TOO_MANY_ROWSTOO_MANY_ROWS THEN . When OTHERS OTHERS THEN . END; 例例 外外 段段 在在ExceptionException段中段中 对返回信息一一作出响应对返回信息一一作出响应 27PL/SQL程序设计 例外处理(EXCEPTION) 用户自定义例外的使用 Declare out_of_range EXCEPTION; Begin if v_sal MAX_SAL then RAISE