oracle数据库--PL/SQL

1、PL/SQL介绍

PL/SQL：oracle对SQL的扩展，是一种模块化、过程化语言，属于第三代语言（3G）。
PL/SQL引擎：PL/SQL编译和运行系统，处于PL/SQL单元（块、函数、包、过程、触发器等）。过程化执行器处理过程化语句;SQL执行器处理SQL语句。

1）PL/SQL块（基本单元）

[delare] --声明部分
--declaration statements
begin --开始
--executable statements
[exception] --异常处理
--exception statements
end --结束

注：块可以嵌套，分为匿名块（未命名的PL/SQL单元，不可被调用）和命名块（被编译好并存储于数据库中，包括存储过程、函数、包等），PL/SQL子程序就是命名的PL/SQL块，一般特质函数和存储过程。

2）变量和常量

变量声明：varuable_name datatype [not null] [:=|default expression] （变量的作用范围：在一个块中定义的变量，在该块的子块中也有效）
常量声明：varuable_name constant datatype [not null] [:=|default expression]

3）绑定变量：在SQL*Plus中创建和使用的变量，目的是重复利用SQL语句，可以被SQL*Plus识别，可以用在PL/SQL和SQL中使用。

创建绑定变量：variable bd varchar2(100);
引用绑定变量：:bd:=‘hello!‘ --赋值，引用格式是冒号后面紧跟绑定变量的名称

4）PL/SQL的数据类型与数据库的数据类型有差异。PL/SQL数据类型分为：

4.1）预定义PL/SQL数据类型：

• 标量类型（Scalar）：标准的数据类型，没有内部组件 --char、varchar2、number、raw、boolean、date等
• 集合类型（Composite）：包含能够被独立操作的内部组件 --嵌套表nested table、可变数组varray、表table、记录record
• 指针类型（Reference）：类似于3G语言中的指针 --游标类型
• LOB类型（Large Object）：LOB类型的值就是LOB定位器 --bfile、blob、clob、nclob

4.2）用户自定义PL/SQL子类型：其他数据类型的子集

定义格式：subtype 子类型名 is 基本数据类型[constraint][not null];

5）NULL：既可以表示空值也可以表示空语句

v2:=null;
null;

6）数据类型转换

6.1）隐式转换：有PL/SQL自动完成，作为赋值，oracle可以自动转换如下：

6.2）显示转换：利用函数来转换，如：to_char(sysdate,‘YYYY-MM-DD:HH24:MI:SS‘)

7）在PL/SQL中可以使用如下DML：

declare
x varchar2(20) := ‘xxxx‘;
y varchar2(20) := ‘yyyyyyyy‘;
begin
insert into testtable values (x,y) --往表中插入数据
update testtable set name = x where address = y; --更新
delete from testtable where name = x; --删除数据
commit; --也可以使用rollback
end;

    在PL/SQL中使用查询，必须把查询结果赋值给变量，使用into子句将结果赋值给变量。

8）控制结构

8.1）条件结构

if 条件1 then
语句
...
elsif 条件2
语句
...
else
语句
...
end if

8.2）循环结构

--基本循环：
loop
语句
...
end loop;

--for循环：
for counter in [reverse] 开始值...结束值 loop --counter每次加1，默认从开始到结束，reverse表示从结束到开始
语句
...
end loop

--while循环：
while "条件" loop
语句
...
end loop

循环退出：

无条件退出：exit
有条件退出：exit when y > 3;

循环继续：

无条件继续：continue
有条件继续：continue when y > 3;

9）goto

begin
<<pb2>> --标签
...
goto pb2;
end;

10）异常

oralce预定义的异常：
      

块中异常处理的定义：

begin
...
exception
when 异常1 then
对异常1进行处理
when 异常2 then
对异常2进行处理
...
when others then
...
end;

捕捉异常：

begin
...
exception
when others then
dbms_output.put_line(‘error code:‘||sqlcode||‘ ‘||sqlerrm); --sqlcode返回错误代码，sqlerrm返回错误信息，都是oracle预定义函数
end;

自定义异常：

"异常的名字" exception;

给异常指定错误编号：

pragma exception_init(exception_name,-oracle_error_number); --20000~20999

异常的触发方式：

• 由oracle自动触发
• raise语句手工触发：raise myecp;
• 调用存储过程raise_application_error手工触发：raise_application_error(-20009,‘data is out of list‘);

异常的传递：

    当一个子块的异常发生时，如果子块中没有异常处理，该异常将传递到父块中，并满足条件时一致往上传递。

2、游标