1. PL/SQL概述

PL/SQL（Procedural Language/SQL）是Oracle的专用语言，是对标准SQL语言的扩展，它允许在其内部嵌套普通的SQL语句，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误。SQL语句的数据库操纵能力、数据查询能力和PL/SQL的过程处理能力结合在一起，可以实现比较复杂的业务逻辑。

1.1 PL/SQL块结构

PL/SQL程序都是以块（BLOCK）为单位，整个PL/SQL块分3部分：声明部分（DECLARE）、执行部分（BEGIN...END）和异常处理部分（EXCEPTION）。其中执行部分是必需的。

标准PL/SQL块的语法格式如下：

      [DECLARE]

      --声明部分，可选

      BEGIN

      --执行部分，必须

      [EXCEPTION]

      --异常处理部分，可选

      END

1.2 代码注释和标识符

1)．单行注释

      单行注释由连个连接字符“--”开始，后面紧跟着注释内容。

2)．多行注释

      多行注释由/*开头，由*/结尾，这个大多数编程语言是相同的。

3)．PL/SQL字符集

      所有的PL/SQL程序元素（比如，关键字、变量名、常量名等）都是由一些字符序列组合而成的，而这些字符序列中的字符都必须取自PL/SQL语言所允许使用的字符集，那么这些合法的字符集主要包括以下内容：

• 大写和小写字母：A-Z或a-z。
• 数字：0-9。
• 非显示的字符：制表符、空格和回车。
• 数学符号：+，-，*，/，>，<，=等。
• 间隔符：包括()，{}，[]，?，!，;，:，@，#，%，$，&等。

2. 数据类型与定义变量、常量

2.1 基本数据类型

1)．数值类型

数值类型主要包括NUMBER、PLS_INTEGER和BINARY_INTEGER三种基本类型

2)．字符类型

字符类型主要包括VARCHAR2、CHAR、LONG、NCHAR和NVARCHAR2等。这些类型的变量用来存储字符串或字符数据。

3)．日期类型

日期类型只有一个种——即DATE类型，用来存储日期和时间信息，DATE类型的存储空间是7个字节，分别使用一个字节存储世纪、年、月、天、小时、分钟和秒。

4)．布尔类型

布尔类型也只有一种——即BOOLEAN，主要用于程序的流程控制和业务逻辑判断，其变量值可以是TRUE、FALSE或NULL中的一种。

2.2 特殊数据类型

1)．%TYPE类型

使用%TYPE关键字可以声明一个与指定列名称相同的数据类型，它通常紧跟在指定列名的后面。示范：

var_address BBSDETAIL.ADDRESS%type;  --定义变量

2)．RECORD类型，语法如下：

type record_type is record(    var_member1 data_type [not null] [:=default_value],    …    var_membern data_type [not null] [:=default_value]));

3)．%ROWTYPE类型，语法如下：

rowVar_name table_name%rowtype;

2.3 定义变量和常量

1)．定义变量

<变量名> <数据类型> [(长度):=<初始值>];

2)．定义常量

<常量名> constant <数据类型>:=<常量值>;

本章节代码示范： 

declare--定义一个变长字符串var_title varchar2(200);--定义一个数值var_browse_count number(6);--定义一个小数，并给一个初始值var_number_of_coins number(6,2):=3.2;--定义一个日期类型的数据var_publish_time date;--定义一个布尔变量，不能为空，初始值为falsevar_isvalid boolean not null default false;--定义一个%type类型的变量var_address BBSDETAIL.ADDRESS%type;--定义一个record类型type bbsdetail_type is record(    title BBSDETAIL.TITLE%type,    browse_count BBSDETAIL.BROWSE_COUNT%type,    publish_time BBSDETAIL.PUBLISH_TIME%type);--定义一个record变量bbsdetail_record bbsdetail_type;--定义一个rowtypebbs_detail_row BBSDETAIL%rowtype;begin    select TITLE,BROWSE_COUNT,NUMBER_OF_COINS,PUBLISH_TIME,ADDRESS    into var_title,var_browse_count,var_number_of_coins,var_publish_time,var_address    from BBSDETAIL where DETAIL_ID=91486;    dbms_output.put_line('标题：'||var_title||' 浏览次数：'||var_browse_count);    dbms_output.put_line('币数：'||var_number_of_coins||' 发布时间：'||var_publish_time);    dbms_output.put_line('地址：'||var_address);    var_isvalid := var_browse_count>10;    if var_isvalid then        dbms_output.put_line('有效记录！');    end if;    select TITLE,BROWSE_COUNT,PUBLISH_TIME    into bbsdetail_record.title,bbsdetail_record.browse_count,bbsdetail_record.publish_time    from BBSDETAIL where DETAIL_ID=91486;    dbms_output.put_line('标题：'||bbsdetail_record.title);    dbms_output.put_line('浏览次数：'||bbsdetail_record.browse_count);    dbms_output.put_line('发布时间：'||bbsdetail_record.publish_time);    select * into bbs_detail_row from BBSDETAIL where DETAIL_ID=91486;    dbms_output.put_line('标题：'||bbs_detail_row.title||' 地址：'||bbs_detail_row.address);end;--------------------输出---------------标题：上海某店纪实 浏览次数：499--币数：8 发布时间：03-9月 -11--地址：罗秀路--有效记录！--标题：上海某店纪实--浏览次数：499--发布时间：03-9月 -11--标题：上海某店纪实 地址：罗秀路

3. 流程控制语句

3.1 选择语句

• if...then语句

if 
     
       then         plsql_sentenceend if;
     

• if..then..else语句

if 
     
       then     plsql_sentence1else     plsql_sentence2end if;
     

• if...then...elseif语句

if 
     
       then     plsql_sentence1elseif 
      
        then     plsql_sentence2...end if;
      
     

• case语句，执行方式与if...then...elseif语句十分相似

case 
     
          when 
      
        then plsql_sentence1;    when 
       
         then plsql_sentence2;    ...    when 
        
          then plsql_sentencen;    [else plsql_sentence;]end case;
        
       
      
     

3.2 循环语句

• loop语句

loop    plsql_sentence;    exit when end_condition_ expend loop;

• while语句

while condition_expression loop    plsql_sentence;end loop;

• for语句

for variable_ counter_name in [reverse] lower_limit..upper_limit loop    plsql_sentence;end loop;

注意，当语句中使用reverse关键字时，计数器的值会随循环递减。

本章节代码示范： 

declare    --定义变量    var_browse_count BBSDETAIL.BROWSE_COUNT%type;begin    --查询    select BROWSE_COUNT into var_browse_count from BBSDETAIL where DETAIL_ID=91486;    --判断    if var_browse_count>1000 then        dbms_output.put_line('很了不起啊！');    elsif var_browse_count>100 then        dbms_output.put_line('不错了！');    else        dbms_output.put_line('有待努力！');    end if;      --这儿的查询结果是499    dbms_output.put_line('查询出来的var_browse_count='||var_browse_count);    --WHILE循环，结束后var_browse_count=100    while var_browse_count>100 loop        insert into MYTABLE values(var_browse_count);        var_browse_count := var_browse_count-1;    end loop;    dbms_output.put_line('WHILE结束后var_browse_count='||var_browse_count);    --LOOP循环，结束后var_browse_count=10    loop        insert into MYTABLE values(var_browse_count);        var_browse_count := var_browse_count-1;        exit when var_browse_count = 10;    end loop;    dbms_output.put_line('LOOP循环结束后var_browse_count='||var_browse_count);    --FOR循环，递减，结束后var_browse_count=10（不变）    for i in reverse 1..var_browse_count loop        insert into MYTABLE values(var_browse_count);     end loop;    dbms_output.put_line('FOR递减循环结束后var_browse_count='||var_browse_count);end;--------------------输出---------------不错了！--查询出来的var_browse_count=499--WHILE结束后var_browse_count=100--LOOP循环结束后var_browse_count=10--FOR递减循环结束后var_browse_count=10

4. PL/SQL游标

4.1 显式游标

显式游标是由用户声明和操作的一种游标，通常用于操作查询结果集（即由SELECT语句返回的查询结果），它处理数据的步骤包括：声明游标、打开游标、读取游标和关闭游标4个步骤。

• 声明游标

cursor cur_name[(input_parameter1[,input_parameter2]...)][return ret_type]is select_sentence;

• 打开游标

open cur_name[(para_value1[,para_value2]...)]

• 读取游标

fetch cur_name into {variable};

• 关闭游标

close cur_name

4.2 游标的属性

无论是显式游标还是隐式游标，都具有%found、%notfound、%isopen和%rowcount这4个属性。

4.3 隐式游标

在执行一个SQL语句时，Oracle会自动创建一个隐式游标。当使用隐式游标的属性时，需要在属性前面加上隐式游标 的默认名称：SQL。

4.4 PL/SQL异常处理

根据异常产生的机制和原理，可将Oracle系统异常分为以下两大类：预定义异常、自定义异常。

系统预定义异常	说明
ZERO_DIVIDE	除数为零时引发的异常
ACCESS_INTO_NULL	企图为某个未初始化对象的属性赋值
COLLECTION_IS_NULL	企图使用未初始化的集合元素
CURSOR_ALREADY_OPEN	企图再次打开一个已经打开过的游标，但在重新打开之前，游标未关闭
INVALID_CURSOR	执行一个非法的游标操作，例如，关闭一个未打开的游标
INVALID_NUMBER	企图将一个字符串转换成一个无效的数字而失败
LOGIN_DENIED	企图使用无效的用户名或密码连接数据库
NO_DATA_FOUND	SELECT INTO语句没有返回数据
ROWTYPE_MISMATCH	主游标变量与PL/SQL游标变量的返回类型不兼容
SELF_IS_NULL	使用对象类型时，使用空对象调用其方法

 

注意：Oracle的异常封装体制类似于Java的异常封装体制：如果一个异常被捕捉并处理，那么此异常不再向外层抛出。这样可以设计程序在处理已知异常时，处理完异常继续程序的处理，而不是中止程序。

本章节代码示范一：

declare--定义一个record类型type bbsdetail_type is record(    title BBSDETAIL.TITLE%type,    address BBSDETAIL.ADDRESS%type,    browse_count BBSDETAIL.BROWSE_COUNT%type,    publish_time BBSDETAIL.PUBLISH_TIME%type);--定义一个record变量bbsdetail_record bbsdetail_type;--声明游标，顺便指定一默认值cursor cursor_bbsdetail(var_district_id in number:=1001)is select TITLE,ADDRESS,BROWSE_COUNT,PUBLISH_TIME    from BBSDETAIL    where DISTRICT_ID=var_district_id    order by PUBLISH_TIME desc;begin    --打开游标（用默认值打开）    open cursor_bbsdetail();    --遍历游标    fetch cursor_bbsdetail into bbsdetail_record;    while cursor_bbsdetail%found loop        --处理        dbms_output.put_line('标题：'||bbsdetail_record.title);        dbms_output.put_line('地址：'||bbsdetail_record.address);        dbms_output.put_line('浏览次数：'||bbsdetail_record.browse_count);        dbms_output.put_line('发布时间：'||bbsdetail_record.browse_count);        --游标指针指向下一条记录        fetch cursor_bbsdetail into bbsdetail_record;    end loop;    --关闭游标    close cursor_bbsdetail;-----------------------------    --遍历游标的另一种更为简便的方式(FOR语句循环游标，不用打开游标、读取游标、关闭游标）    for bbsdetail_record in cursor_bbsdetail(1012)    loop        dbms_output.put_line('标题：'||bbsdetail_record.title);        dbms_output.put_line('地址：'||bbsdetail_record.address);        --其它处理    end loop;------------------------------    --隐式游标属性    update BBSDETAIL set TITLE='新标题' where DETAIL_ID=123456789;    if sql%notfound then         dbms_output.put_line('更新不成功！不存在DETAIL_ID=123456789');    else         dbms_output.put_line('更新成功！');    end if;end;/

本章节代码示范二：

declarevar_title varchar2(200);begin    --下面两条SQL语句只能选择一条执行--    select TITLE into var_title from BBSDETAIL where DETAIL_ID=12345679;    select TITLE into var_title from BBSDETAIL where DISTRICT_ID=1001;exception    when no_data_found then        dbms_output.put_line('不存在该记录！');    when too_many_rows then        dbms_output.put_line('返回多条记录！');end;