一、目标

1、理解 PL/SQL 功能和特点

2、了解T/SQL（SQLServer)和PL/SQL(Oracle)在语法上的差异

3、了解PL/SQL中的数据类型及其用法

4、了解oracle11g新增加的数据类型

5、掌握oracle11g中对LOB类型进行操作的方法，即：如何把大文件保存到表BLOB列中，如何把大内容保存到表的CLOB列中。

6、理解oracle11g的PL/SQL中对序列的改进，即无dual的序列的使用

7、理解控制结构，了解oracle11g的continue语句

8、掌握oracle11g中的错误处理

二、PL/SQL简介

1、PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言

2、PL/SQL 是对 SQL 的扩展

3、支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构

4、可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑

5、与 Oracle 服务器和 Oracle 工具紧密集成，具备可移植性、灵活性和安全性

三、PL/SQL 的优点

1、支持 SQL，在 PL/SQL 中可以使用：

（1）数据操纵命令

（2）事务控制命令

（3）游标控制

（4）SQL 函数和 SQL 运算符

2、用户把PL/SQL块整个发送到服务器端，oracle服务器端编译、运行，再把结果返回给用户

3、可移植性，可运行在任何操作系统和平台上的Oralce 数据库

4、更佳的性能，PL/SQL 经过编译执行

5、安全性，可以通过存储过程限制用户对数据的访问

6、与 SQL 紧密集成，简化数据处理。

（1）支持所有 SQL 数据类型

（2）支持 NULL 值

（3）支持 %TYPE 和 %ROWTYPE 属性类型

四、PL/SQL 的体系结构

1、PL/SQL 引擎驻留在 Oracle 服务器中

2、PL/SQL引擎接受 PL/SQL 块并对其进行编译执行

五、PL/SQL块简介

1、PL/SQL 块是构成 PL/SQL 程序的基本单元将逻辑上相关的声明和语句组合在一起

2、PL/SQL 分为三个部分，声明部分、可执行部分和异常处理部分

[DECLARE     declarations]

BEGIN

executable statements

[EXCEPTION    handlers]

END;

六、变量和常量

1、TSQL和PL/SQL的区别

2、获取student多少条记录

declare
    total_row number;
begin 
    select count(*) into total_row from student;
    dbms_output.put_line('there are '||total_row||' jilu');
end;

3、常量、变量的语法

1、PL/SQL 块中可以使用变量和常量

（1）在声明部分声明，使用前必须先声明

（2）声明时必须指定数据类型，每行声明一个标识符

（3）在可执行部分的 SQL 语句和过程语句中使用

2、声明变量和常量的语法：

identifier [CONSTANT] datatype [NOT NULL]   

  [:= | DEFAULT expr];

4、给变量赋值有两种方法：

（1）使用赋值语句 :=

（2）使用 SELECT INTO 语句

DECLARE
  icode VARCHAR2(6);
  p_catg VARCHAR2(20);
  p_rate NUMBER;
  c_rate CONSTANT NUMBER := 0.10;
BEGIN
  ...
  icode := 'i205';
  SELECT p_category, itemrate * c_rate
  INTO  p_catg, p_rate
  FROM itemfile WHERE itemcode = icode;
  ...
END;

七、数据类型

1、PL/SQL 支持的内置数据类型（和数据表中列的类型不同）

2、数字数据类型

（1）指定数值的存储格式

3、oracle 11g新增的数据类型

Oracle11g推出了一个新的数据类型SIMPLE_INTEGER，这种数据类型的取值范围为–2147483 648～+2147483647，数据类型不为空。

对于此数据类型，Oracle可以将这个数据类型的操作直接作用于硬件，从而提高性能。

4、字符数据类型

5、字符数据类型

6、LOB数据类型1 （输出需要使用pl/sql 块进行）

（1）用于存储大文本、图像、视频剪辑和声音剪辑等非结构化数据。

（2）LOB 数据类型可存储最大 4GB的数据。

（3）LOB 类型包括：

a、BLOB   将大型二进制对象存储在数据库中

b、CLOB   将大型字符数据存储在数据库中

c、NCLOB   存储大型UNICODE字符数据

d、BFILE     将大型二进制对象存储在操作系统文件中

7、LOB 数据类型 2

（1）LOB 类型的数据库列仅存储定位符，该定位符指向大型对象的存储位置

（2）DBMS_LOB程序包用于操纵 LOB 数据

SET SERVEROUTPUT ON #打开输出
DECLARE
  clob_var   CLOB;
  amount     INTEGER;
  offset     INTEGER;
  output_var VARCHAR2(100);
BEGIN
  SELECT chapter_text INTO clob_var
  FROM my_book_text
  WHERE chapter_id=5;
  amount := 24;  -- 要读取的字符数
  offset := 1;   -- 起始位置
  DBMS_LOB.READ(clob_var,amount,offset,output_var);
  DBMS_OUTPUT.PUT_LINE(output_var);
END;
/

8、举例--CLOB

SQL>create table testClob
(
    tid number primary key,
    t_info clob
);

插入数据：

insert into testclob values(1,'News [1] refers to a style used by newspapers, radio stations, television stations, the Internet and other media to record and disseminate information [2] a style that reflects the times. The concept of news has a broad sense and a narrow sense. Broadly speaking: Common texts other than comments and articles published on newspapers, radio, the Internet, and TV are news, including news, newsletters, close-ups, and sketches (some include sketches in close-ups), etc. In a narrow sense: the message is to use a general narrative method, with more concise and concise text, to quickly and timely report the recent and valuable facts that happen nearby, so that a certain group of people can understand. News generally includes five parts: title, introduction, main body, background and conclusion. The first three are the main part, the latter two are the auxiliary part. The writing method is mainly based on narrative and may also include discussion, description and comment. News is a news service platform that contains massive amounts of information and truly reflects important events at all times. You can search for news events, hot topics, person dynamics, product information, etc. to quickly understand their latest progress. [4]。')

SET SERVEROUTPUT ON
declare
    clob_var clob;
    amount integer;
    offset     integer;
    output_var   varchar2(10000);
begin
    select t_info into clob_var from testclob where tid=1;
    amount :=10000;
    offset     :=1;
    dbms_lob.read(clob_var,amount,offset,output_var);
    dbms_output.put_line(output_var);
end;

9、举例---BLOB列

（1）创建表

create table person(pid varchar2(20) primary key,photo blob);

（2）创建目录逻辑(前提是有这个目录，类似于expdp中的directory创建)

用system创建，并赋予scott读写权限

SQL>create directory PHOTO as '/home/oracle/photo';

SQL>grant read,write on directory PHOTO to scott;

（3）存储过程实现

create or replace procedure insertBlob(id varchar2,imgFile varchar2)
is
    img_file bfile;
    img_blob blob;
    lob_length number;
begin
--先插入一个空值
    insert into person values(id,empty_blob());
    select photo into img_blob from person where pid=id;
--读取img_file中的内容
    img_file := bfilename('PHOTO',imgFile);
    dbms_lob.open(img_file);
    lob_length :=dbms_lob.getlength(img_file);
--装载图片文件
    dbms_lob.loadfromfile(img_blob,img_file,lob_length);
    dbms_lob.close(img_file);
    commit;
end;

（4）执行存储过程

exec insertBlob('1','wuyuedfeng_.png');

（5）后面可以通过java进行还原读取，把图片删除后，再进行还原【见后面存储过程中的java进行还原】