Oracle学习笔记之五，Oracle 11g的PL/SQL入门

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;
--定义一个布尔变量，不能为空，初始值为false
var_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;
--定义一个rowtype
bbs_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 <condition_expression> then

         plsql_sentence

end if;

• if..then..else语句

if <condition_expression> then
     plsql_sentence1
else
     plsql_sentence2
end if;

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

if <condition_expression1> then
     plsql_sentence1
elseif <condition_expression2> then
     plsql_sentence2
...
end if;

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

case <selector>
    when <expression1> then plsql_sentence1;
    when <expression2> then plsql_sentence2;
    ...
    when <expressionn> then plsql_sentencen;
    [else plsql_sentence;]
end case;

3.2 循环语句

• loop语句

loop
    plsql_sentence;

    exit when end_condition_ exp
end 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;
/

本章节代码示范二：

declare
var_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;