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;

最新文章

  1. yuv420转rgb 及 rgb转bmp保存
  2. CloudSim样例分析
  3. Android 网络框架 volley源码剖析
  4. VBA中find的一些使用方法
  5. 163k地方门户网站系统团购定时结束限量控制
  6. python--help
  7. UML六种关系
  8. 201521123044 《Java程序设计》第8周学习总结
  9. RabbitMQ之工作队列
  10. 学习Javascript数据结构与算法（第2版）笔记（1）
  11. MFC Dialog使用
  12. Linux系统安装Mysql5.7
  13. MacBook使用笔记1 - 快捷键与命令学习
  14. Spring Maven项目集成Springboot
  15. Linux中顿号
  16. api.closeFrame
  17. 中国气象局某分院官网漏洞打包（弱口令+SQL注入+padding oracle）
  18. TRUNCATE TABLE 与 DELETE的区别
  19. 【blockly教程】第一章 Google Blockly教学应用手册
  20. C++ STL迭代器与索引相互转换

热门文章

  1. MFC 小知识总结二
  2. Missing styles. Is the correct theme chosen for this layout? Use the Theme combo box above the layou
  3. redis可视化管理工具Redis Desktop Manager
  4. iOS中ActionSheet和Alert的区别
  5. JS模板语言不错的脚本
  6. 设计模式——门面模式（Facade）
  7. Jsp之神笔记
  8. windows安装sqlite
  9. 在OpenERP8.0中如何激活及时通讯功能im
  10. eclipse tomcat路径更改后启动报错