【安博培训技术】Oracle5 使用 PL/SQL20130911

Oracle5 使用 PL/SQL


回顾
同义词是现有数据库对象的别名
序列用于生成唯一、连续的序号
视图是基于一个或多个表的虚拟表
索引是与表相关的一个可选结构，用于提高 SQL 语句执行的性能
索引类型有标准索引、唯一索引、反向键索引、位图索引和基于函数的索引


目标
理解 PL/SQL 功能和特点
了解数据类型及其用法
理解逻辑比较
理解控制结构
掌握错误处理


PL/SQL 简介
PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言
PL/SQL 是对 SQL 的扩展
可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑
与 Oracle 服务器和 Oracle 工具紧密集成，具备可移植性、灵活性和安全性
简单一句话，PL/SQL是一段具有编程逻辑的sql代码，这段代码存在oracle数据库中，是能够被我们调用的


PL/SQL 的优点 2-1
支持 SQL，在 PL/SQL 中可以使用：
数据操纵命令
事务控制命令
游标控制
SQL 函数和 SQL 运算符
支持面向对象编程 (OOP) 
可移植性，可运行在任何操作系统和平台上的Oralce 数据库
更佳的性能，PL/SQL 经过编译执行
Procedure
Begin
  ProcedureCall
  SQL Command
  …
End
[说明]
并非所有的SQL都可以在PL/SQL中使用，仅可对数据进行操作（还支持动态SQL：稍后讲述）
OOP：减少程序的复杂性和编写成本（在以后的编程中仔细体会）
PL/SQL与在SQLPlus中写多条语句的不同，编译过程


PL/SQL 的优点 2-2
与 SQL 紧密集成，简化数据处理。
支持所有 SQL 数据类型
支持 NULL 值
支持 %TYPE 和 %ROWTYPE 属性类型
安全性，可以通过存储过程限制用户对数据的访问
[说明]
两种属性类型： %TYPE 和 %ROWTYPE （稍后介绍）
安全性：数据对象的细节被隐藏在语句块中（存储过程）


可以降低网络开销、提高应用程序性能。对于其他数据库（例如
sqlserver、Sybase、DB2等），当应用程序访问数据库时，每次只
能发送单条sql语句，执行4条sql语句需要在网络上发送四次。
而对于oracle数据库来说，通过使用Pl/sql块，可以将多条sql语句组织到同一
个pl/sql块中，从而降低了网络开销，提高了应用程序的性能


PL/SQL 的体系结构
PL/SQL 引擎驻留在 Oracle 服务器中
该引擎接受 PL/SQL 块并对其进行编译执行


PL/SQL 块简介
PL/SQL 块是构成 PL/SQL 程序的基本单元
将逻辑上相关的声明和语句组合在一起
PL/SQL 分为三个部分，声明部分、可执行部分和异常处理部分
   [DECLARE 
    declarations]
    BEGIN
    executable statements
    [EXCEPTION 
    handlers]
    END;
SQLServer中没有如此的规定，灵活VS规范
Declare：变量、游标、自定义异常（作用域）
注：大小写不敏感，但为团队开发的统一，需选择确定的编码规范（其他数据库或开发工具亦然）。
[说明]通过阅读这个例子初步认识PL/SQL块
declare
    a number :=10;
    b varchar2(20):='hello world';
begin
    dbms_output.put_line(a);
    dbms_output.put_line(b);
end;


变量和常量 1-2 
PL/SQL 块中可以使用变量和常量
在声明部分声明，使用前必须先声明
声明时必须指定数据类型，每行声明一个标识符
在可执行部分的 SQL 语句和过程语句中使用
声明变量和常量的语法：
identifier [CONSTANT] datatype [NOT NULL]   
[:= | DEFAULT expr];
给变量赋值有两种方法：
使用赋值语句 :=
使用 SELECT INTO 语句
[说明]与SQLServer对比，掌握基本的变量、常量的声明和赋值方法


变量和常量 2-2
declare
  v_name varchar2(10);
  c_name constant varchar2(10):='员工姓名:';
begin
  select ename into v_name from emp where empno=73619;
  dbms_output.put_line(c_name||v_name);
exception
  when no_data_found then
    dbms_output.put_line('没有找到员工');
end;/
[例]显示学号为001的学生姓名
[说明]此处说明什么是内置包及dbms_output的用法，引导学员查阅内置包参考文档


数据类型
PL/SQL 支持的内置数据类型
数据类型
标量类型 数字 字符 布尔型 日期时间
LOB类型 存储非结构化数据块 BFILE BLOB CLOB NCLOB 
属性类型 %TYPE 提供某个变量或数据库表列的数据类型
%ROWTYPE 提供表示表中一行的记录类型 


PL/SQL内置数据类型VSOracle数据类型（以前已经提到过）
回忆已学过的Oracle数据类型
标量（只包含单个值，没有内部组件）VS矢量、张量
[说明] PL/SQL数据类型只讲常用的数据类型，对于不常用的数据类型了解即可，不需记忆


数字数据类型
指定数值的存储格式
BINARY_INTEGER 存储有符号整数，所需存储空间少于NUMBER类型值
natural naturalln positive poitiven signtype
NUMBER 存储整数、实数和浮点数
decimal float integer real
PLS_INTEGER 存储有符号整数，可使算术计算快速而有效


编程时，根据实际情况选择使用父类型或子类型
解释binary_integer（-2<31>-1~2<31>-1）中各种子类型，注意如何记忆
number(p,s)（1E-130~10E125）：溢出则出错；number(p)：整数； p,s不能用变量或常量制定；小数位（正、负）
decimal：38位十进制定点数；float：126位二进制（38位十进制）；integer：38位十进制整数；real：63位二进制浮点数（18位十进制）
pls_integer：同binary_integer，但执行运算更快，以及其算术运算为基础，而非库算数运算，与number比需要存储空间更小；建议使用


字符数据类型
字符数据类型包括：
CHAR
VARCHAR2
LONG
RAW
LONG RAW
PL/SQL 的数据类型与 SQL数据类型的比较
数据类型 SQL类型 PL/SQL类型
CHAR 1..2000 1..32767
LONG 1..2GB 1..32760
LONG RAW 1..2GB 1..32760
RAW 1..2000 1..32767
VARCHAR2 1..4000 1..32767


char[(maximum_size [char|byte])]：默认长度为1；Oracle中char数据列最大2000字节，可放入long数据列，但不能将超过范围的long类数据取入char变量中
varchar2：string、varchar（不建议使用）
long：可变长字符串（类似于varchar2 ）
raw：存储固定长度的二进制数据
long raw：可变长二进制数据（类似于raw）
每个版本的数据类型稍有不同，掌握最基本的数据类型用法，了解Oracle提供的数据类型


日期时间和布尔数据类型
日期时间类型
存储日期和时间数据
常用的两种日期时间类型
DATE
TIMESTAMP
布尔数据类型
此类别只有一种类型，即BOOLEAN类型
用于存储逻辑值(TRUE、FALSE和NULL)
不能向数据库中插入BOOLEAN数据
不能将列值保存到BOOLEAN变量中
只能对BOOLEAN变量执行逻辑操作


date：(B.C.E)4712-1-1~ (C.E)9999-12-31；利用nls_date_format设置默认日期格式
timestamp[(p)]：nls_timestamp_format


LOB  数据类型 2-1
用于存储大文本、图像、视频剪辑和声音剪辑等非结构化数据。
LOB 数据类型可存储最大 4GB的数据。
LOB 类型包括：
BLOB   将大型二进制对象存储在数据库中
CLOB   将大型字符数据存储在数据库中
NCLOB   存储大型UNICODE字符数据
BFILE     将大型二进制对象存储在操作系统文件中


LOB 数据类型 2-2
LOB 类型的数据库列仅存储定位符，该定位符指向大型对象的存储位置
DBMS_LOB程序包用于操纵 LOB 数据
set serveroutput on
declare
  content CLOB;
  beginPosition integer;
  amount integer;
  outputString varchar(100);
begin
  select f_content into content from t_booktext where f_chapterid='001'; --从表中选择 CLOB 定位符到 contentr变量中--
  beginPosition := 1;
  amount := 22;
  dbms_lob.read(content,amount,beginPosition,outputString);--从CLOB数据中读取22个字符存储到 outputString 变量中--
  dbms_output.put_line(outputString);--显示读到的信息--
end;
/


举个使用%type、%rowtype的例子：
declare temp t_student.f_id %type
declare temp t_student %rowtype
[例]显示学号为001的学生姓名
declare
  v_name t_student.f_name%type;
begin
  select f_name into v_name from martin.t_student where f_id='001';
  dbms_output.put_line(v_name);
end;
/
[例]显示编号为001的学生信息(姓名、生日、系别)
declare
  rec_stu t_student%rowtype;
begin
  select * into rec_stu from martin.t_student where f_id='001';
  dbms_output.put_line(rec_stu.f_name||'#'||rec_stu.f_birth||'#'||rec_stu.f_department);
end;
/


逻辑比较
逻辑比较用于比较变量和常量的值，这些表达式称为布尔表达式
布尔表达式由关系运算符与变量或常量组成
关系运算符 说明
= 比较两个变量是否相等，如果值相当，则返回 True
<>, != 比较两个变量，如果不相等，则返回 True
< 比较两个变量，检查值 1 是否小于值 2
> 比较两个变量，检查值 1 是否大于 值 2
<= 比较两个变量，检查变量 1 是否小于等于变量 2
>= 比较两个变量，检查变量 1 是否大于等于变量 2


布尔表达式的结果为TRUE、FALSE或NULL，通常由逻辑运算符AND、OR和NOT连接
布尔表达式有三种类型：
数字布尔型
字符布尔型
日期布尔型


控制结构
PL/SQL 支持的流程控制结构：
条件控制
IF 语句
CASE 语句
循环控制
LOOP 循环
WHILE 循环
FOR 循环
顺序控制
GOTO 语句
NULL 语句


条件控制 2-1
IF 语句根据条件执行一系列语句，有三种形式：IF-THEN、IF-THEN-ELSE 和 IF-THEN-ELSIF
declare
     sal number;
begin
     select sal into sal from emp where empno=7369;
     if sal<2000 then
        dbms_output.put_line('less than 2000');
     elsif sal>2000 and sal<4000 then
        dbms_output.put_line('between 2000 and 4000');
     else 
        dbms_output.put_line('more than 4000');
     end if;   
end;


条件控制 2-2
CASE 语句用于根据单个变量或表达式与多个值进行比较
执行 CASE 语句前，先计算选择器的值
declare
     a number:=0;
begin
     a:=&a;
     case a 
       when 10 then dbms_output.put_line('aaaaaaaaaaaaaaaa');
       when 20 then dbms_output.put_line('bbbbbbbbbbbbbbbb');
       when 30 then dbms_output.put_line('cccccccccccccccc');
       else dbms_output.put_line('dddddddddddddd');
     end case;
end;


循环控制
循环控制用于重复执行一系列语句
循环控制语句包括：
LOOP、EXIT 和 EXIT WHEN
循环控制的三种类型：
LOOP   -   无条件循环
WHILE  -  根据条件循环
FOR  -  循环固定的次数


LOOP 
  sequence_of_statements
END LOOP;


WHILE condition LOOP 
  sequence_of_statements
END LOOP;


FOR counter IN [REVERSE] value1..value2
LOOP 
  sequence_of_statements
END LOOP;


顺序控制
顺序控制用于按顺序执行语句
顺序控制语句包括：
GOTO 语句 -  无条件地转到标签指定的语句
NULL 语句 -  什么也不做的空语句
declare
      sal number;
begin
      select sal into sal from emp where empno=7369;
      if sal<2000 then
         goto a;
      else goto b;
      end if;
      <<a>>
          dbms_output.put_line(sal);
      <<b>>
          null;
end;


错误处理 2-1
在运行程序时出现的错误叫做异常
发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分
异常有两种类型：
预定义异常 -  当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发
用户定义异常  -  用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发


错误处理 2-2
处理预定义异常
处理用户定义异常
DECLARE 
  invalidDep EXCEPTION;
  department VARCHAR2(10);
BEGIN
  department := '&Dep';
  IF department NOT IN ('CS','BIO','Maths','PHY') THEN
    RAISE invalidDep;
  ELSE
    DBMS_OUTPUT.PUT_LINE('您输入系别的是'||department);
  END IF;
EXCEPTION
  WHEN invalidDep THEN
    DBMS_OUTPUT.PUT_LINE('无法识别该系别');
END;
/


引发应用程序错误
RAISE_APPLICATION_ERROR 过程
用于创建用户定义的错误信息
可以在可执行部分和异常处理部分使用
错误编号必须介于 –20000 和 –20999 之间
错误消息的长度可长达 2048 个字节
引发应用程序错误的语法：
   RAISE_APPLICATION_ERROR(error_number, error_message);


declare
  v_comm emp.comm%type;
  raise_exception exception;
begin
  select nvl(comm,0) into v_comm from emp where empno=&empno;
  if v_comm=0 then
     raise raise_exception;
    else dbms_output.put_line(v_comm);
  end if;
exception
  when raise_exception then 
    raise_application_error(-20002,'该员工没有提成');
end;
当需要对错误信息详加说明，需要用到；
错误消息以Oracle消息的形式显示


总结
PL/SQL 是一种可移植的高性能事务处理语言 
PL/SQL 引擎驻留在 Oracle 服务器中
PL/SQL 块由声明部分、可执行部分和异常处理部分组成
PL/SQL 数据类型包括标量数据类型、LOB 数据类型和属性类型
控制结构包括条件控制、循环控制和顺序控制 
运行时出现的错误叫做异常
异常可以分为预定义异常和用户定义的异常