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

2021-10-19 10:36:16

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;

码农公寓