1.PL/SQL简介
1）PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言
2）PL/SQL 是对 SQL 的扩展
3）支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构
4）可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑
5）与 Oracle 服务器和 Oracle 工具紧密集成，具备可移植性、灵活性和安全性。
2.PL/SQL的优点
1）支持 SQL，在 PL/SQL 中可以使用：
- 数据操纵命令
- 事务控制命令
- 游标控制
- SQL 函数和 SQL 运算符
2）用户把PL/SQL块整个发送到服务器端，oracle服务器端编译、运行，再把结果返回给用户
3）可移植性，可运行在任何操作系统和平台上的Oralce 数据库
4）更佳的性能，PL/SQL 经过编译执行
5）安全性，可以通过存储过程限制用户对数据的访问
6）与 SQL 紧密集成，简化数据处理。
- 支持所有 SQL 数据类型
- 支持 NULL 值
- 支持 %TYPE 和 %ROWTYPE 属性类型
3.PL/SQL的体系结构
1）PL/SQL 引擎驻留在 Oracle 服务器中
2）该引擎接受 PL/SQL 块并对其进行编译执行
4.PL/SQL块简介
1）PL/SQL 块是构成 PL/SQL 程序的基本单元
2）将逻辑上相关的声明和语句组合在一起
3）PL/SQL 分为三个部分，声明部分、可执行部分和异常处理部分
[DECLARE declarations]
BEGIN
executable statements
[EXCEPTION handlers]
END;
5.变量和常量
- PL/SQL 块中可以使用变量和常量
- 在声明部分声明，使用前必须先声明
- 声明时必须指定数据类型，每行声明一个标识符
- 在可执行部分的 SQL 语句和过程语句中使用
- 声明变量和常量的语法：
identifier [CONSTANT] datatype [NOT NULL]
[:= | DEFAULT expr];
- 给变量赋值有两种方法：
- 使用赋值语句 :=
- 使用 SELECT INTO 语句
实战案例
-- 建表准备环境
create table student(id number,name varchar2(40),age number);
insert into student values(1,'Jack',23);
insert into student values(2,'Tom',25);
insert into student values(3,'Alice',30);
-- 查询表记录数的PL/SQL块
declare
total_row number;
begin
select count(1) into total_row from student;
dbms_output.put_line('一共有'|| total_row ||'记录');
end;
/
执行PL/SQL块后结果如下图：
6.PL/SQL数据类型
6.1 PL/SQL支持的内置的数据类型
6.1.1 数字数据类型
BINARY_INTEGER：存储有符号整数，所需存储空间少于NUMBER类型值
NUMBER:存储整数、实数和浮点数
PLS_INTEGER:存储有符号整数，可使算术计算快速而有效
Oracle11g推出了一个新的数据类型SIMPLE_INTEGER，这种数据类型的取值范围为–2147483 648～+2147483647，数据类型不为空。对于此数据类型，Oracle可以将这个数据类型的操作直接作用于硬件，从而提高性能。
6.1.2 字符数据类型
| 数据类型 | SQL类型 | PL/SQL类型 |
|---|---|---|
| CHAR | 1~2000字节 | 1~32767字节 |
| LONG | 1~2GB字节 | 1~32760字节 |
| LONG RAW | 1~2GB字节 | 1~32760字节 |
| RAW | 1~2000字节 | 1~32767字节 |
| VARCHAR2 | 1~4000字节 | 1~32767字节 |
6.1.3 日期数据类型
日期时间类型：存储日期和时间数据
常用的两种日期时间类型
- DATE
- TIMESTAMP
6.1.4 布尔数据类型
布尔数据类型：此类别只有一种类型，即BOOLEAN类型
- 用于存储逻辑值(TRUE、FALSE和NULL)
- 不能向数据库中插入BOOLEAN数据
- 不能将列值保存到BOOLEAN变量中
- 只能对BOOLEAN变量执行逻辑操作
6.1.5 LOB数据类型
用于存储大文本、图像、视频剪辑和声音剪辑等非结构化数据。
LOB 数据类型可存储最大 4GB的数据。
LOB 类型包括：
- BLOB 将大型二进制对象存储在数据库中
- CLOB 将大型字符数据存储在数据库中
- NCLOB 存储大型UNICODE字符数据
- BFILE 将大型二进制对象存储在操作系统文件中
LOB 类型的数据库列仅存储定位符，该定位符指向大型对象的存储位置
DBMS_LOB程序包用于操纵 LOB 数据。
实战演练:clob
-- 创建表
create table testclob(id number primary key,content clob);
-- 插入数据
insert into testclob values(1,'道不尽红尘奢恋 诉不完人间恩怨,世世代代都是缘,流着相同的血 喝着相同的水,这条路漫漫又长远,红花当然配绿叶 这一辈子谁来陪,渺渺茫茫来又回,往日情景再浮现 藕虽断了丝还连,,爱江山 更爱美人,哪个英雄好汉宁愿孤单,好儿郎 浑身是胆,壮志豪情四海远名扬,人生短短几个秋啊 不醉不罢休,东边儿我度的美人哪 西边儿黄河流,来呀来个酒啊 不醉不罢休,愁情烦事别放心头。');
-- 查询
select * from testclob;
查询结果：
从查询结果我们可以看到，通过普通的select只能查出部分数据。,所以下边使用PL/SQL来获取
declare
clob_var clob;
amount integer;
offset integer;
output_var varchar2(10000);
begin
select content into clob_var from testclob where id=1;
amount := 2000;
offset := 1;
dbms_lob.read(clob_var, amount, offset, output_var);
dbms_output.put_line(output_var) ;
end;
/
运行结果：
实战演练：blob
-- 首先在c盘下创建文件夹photo,并且保存一张图片到该目录，例如：a.jpg
-- 创建目录（使用system用户授权）
grant read, write on directory PHOTO to scott;
create directory PHOTO as 'c:/photo';
-- 创建表
create table testblob(id number primary key,photo blob);
-- 创建存储过程
create or replace procedure insertBlob(id varchar2, imgFile varchar2)
is
img_file bfile;
img_blob blob;
lob_length number;
begin
-- 先插入一个空值
insert into testblob values(id, empty_blob() );
select photo into img_blob from testblob where id = id;
-- 读取img_file中的内容
img_file := bfilename('PHOTO', imgFile);
dbms_lob.open(img_file);
lob_length := dbms_lob.getlength(img_file);
-- 修改表中列photo的内容
dbms_lob.loadfromfile(img_blob, img_file, lob_length);
dbms_lob.close(img_file);
end;
/
执行该存储过程
exec insertBlob(1,'a.jpg');
查询结果，发现已经保存成功
7.属性类型
用于引用数据库列的数据类型，以及表示表中一行的记录类型
属性类型有两种：
- %TYPE - 引用变量和数据库列的数据类型
- %ROWTYPE - 提供表示表中一行的记录类型
使用属性类型的优点：
- 不需要知道被引用的表列的具体类型
- 如果被引用对象的数据类型发生改变，PL/SQL 变量的数据类型也随之改变，健壮性。
8.PL/SQL块中取序列的值
-- 创建序列
create sequence seq1 start with 1 increment by 1;
-- 取值的PL/SQL块
SQL> set serveroutput on -- 设置允许显示输出类似dbms_output
declare
num int;
begin
num := seq1.nextval; -- 11g新特性
-- 11g之前：select seq1.nextval into num from dual;
dbms_output.put_line('num的值是：' || num);
end;
/
9.逻辑比较
逻辑比较用于比较变量和常量的值，这些表达式称为布尔表达式
布尔表达式由关系运算符与变量或常量组成
布尔表达式的结果为TRUE、FALSE或NULL，通常由逻辑运算符AND、OR和NOT连接
布尔表达式有三种类型：
- 数字布尔型
- 字符布尔型
- 日期布尔型
10.控制结构
PL/SQL 支持的流程控制结构：
- 条件控制
- IF 语句
- CASE 语句
- 循环控制
- LOOP 循环
- WHILE 循环
- FOR 循环
- 顺序控制
- GOTO 语句
- NULL 语句
10.1 IF语句
IF 语句根据条件执行一系列语句，有三种形式：IF-THEN、IF-THEN-ELSE 和 IF-THEN-ELSIF
案例：
-- 建表并插入数据
create table t1(id number,name varchar2(10),age number);
insert into t1 values(1,'Jack',19);
insert into t1 values(2,'Tom',22);
insert into t1 values(3,'Alice',11);
commit;
-----PL/SQ块：判断Jack的年龄是否大于18岁
SQL> set serveroutput on -- 设置允许显示输出类似dbms_output
declare
v_age t1.age%type;
begin
select age into v_age from t1 where id=1;
if v_age > 18 then
dbms_output.put_line('Jack的年龄大于18岁');
else
dbms_output.put_line('Jack的年龄小于等于18岁');
end if;
end;
/
10.2 CASE语句
CASE 语句用于根据单个变量或表达式与多个值进行比较。
执行CASE 语句前，先计算选择器的值。
案例
--&grade表示从键盘输入值赋给变量grade
declare
outgrade varchar2(20);
begin
outgrade := CASE &grade
WHEN 'A' THEN '优秀'
WHEN 'B' THEN '良好'
WHEN 'C' THEN '中等'
WHEN 'D' THEN '及格'
WHEN 'E' THEN '不及格'
ELSE '没有此成绩'
END;
dbms_output.put_line(outgrade );
end;
/
执行结果：
10.3 循环控制
循环控制用于重复执行一系列语句
循环控制语句包括：
- LOOP、EXIT 和 EXIT WHEN 、FOR 、WHILE
循环控制的三种类型：
- LOOP - 无条件循环
- WHILE - 根据条件循环
- FOR - 循环固定的次数
-- LOOP循环
LOOP
sequence_of_statements
END LOOP;
-- WHILE循环
WHILE condition LOOP
sequence_of_statements
END LOOP;
-- FOR循环
FOR counter IN [REVERSE] value1..value2
LOOP
sequence_of_statements
END LOOP;
实战演练：按要求打印1~8的数字
-- LOOP实现
declare
num number := 0;
begin
num := 1;
loop
dbms_output.put_line(num || '******');
exit when num > 7;
num := num + 1;
end loop;
dbms_output.put_line( '结束');
end;
/
-- WHILE实现
declare
num number := 0;
begin
num := 1;
while num <=8
loop
dbms_output.put_line(num || '******');
num := num + 1;
end loop;
dbms_output.put_line( '结束');
end;
/
-- FOR实现
begin
for num in 1..8
loop
dbms_output.put_line(num || '******');
end loop;
dbms_output.put_line( '结束');
end;
/
10.4 continue语句
continue表示本次循环结束，下次循序继续
实战演练
declare
num number := 0;
begin
num := 1;
loop
num := num + 1;
exit when num > 8;
continue when num > 4;
dbms_output.put_line(num || '******');
end loop;
dbms_output.put_line( '结束');
end;
/
执行结果：
2******
3******
4******
结束
10.5 goto语句
顺序控制用于按顺序执行语句
顺序控制语句包括：
- GOTO 语句 - 无条件地转到标签指定的语句
- NULL 语句 - 什么也不做的空语句
实战演练：按要求打印1~8
-- null表示什么都不做，CPU停一下
declare
num number := 0;
begin
num := 1;
<<aa>>
dbms_output.put_line(num || '******');
num := num+1;
if num <= 8 then goto aa;end if;
if num > 8 then goto bb;end if;
<<bb>> null;
dbms_output.put_line( '结束');
end;
/
11.动态SQL
动态 SQL 是指在PL/SQL程序执行时生成的 SQL 语句。
编译程序对动态 SQL 不做处理，而是在程序运行时动态构造语句、对语句进行语法分析并执行。
DDL 语句命令和会话控制语句不能在 PL/SQL 中直接使用，但是可以通过动态 SQL 来执行。
执行动态 SQL 的语法：
EXECUTE IMMEDIATE dynamic_sql_string
[INTO define_variable_list]
[USING bind_argument_list];
实战演练
begin
execute immediate 'create table t2(id number)';
end;
/
12.错误处理
在运行程序时出现的错误叫做异常
发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分
异常有两种类型：
- 预定义异常 - 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发。
- 用户定义异常 - 用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发。
12.1 预定义异常
实战演练:预定义异常
declare
v_name t1.name%type;
begin
select name into v_name from t1 where id=99;
dbms_output.put_line(v_name);
exception
when no_data_found then
dbms_output.put_line('没有这个人的信息存在！');
end;
/
12.2 用户定义异常
实战演练:用户定义异常
-- 往表t1插入记录
insert into t1 values(4,'Lucy',-12);
SQL> select * from t1;
ID NAME AGE
---------- ---------- ----------
1 Jack 19
2 Tom 22
3 Alice 11
4 Lucy -12
-- 此时Lucy的年龄是异常的，在PL/SQL块做异常处理
declare
v_age t1.age%type;
invalid_age exception;
begin
select age into v_age from t1 where id=4;
if v_age < 0 then
raise invalid_age ;
else
dbms_output.put_line('年龄正常');
end if;
exception
when invalid_age then
dbms_output.put_line('年龄错粗了，怎么可能是负数？');
end;
/
13.引发应用程序错误
RAISE_APPLICATION_ERROR 过程
- 用于创建用户定义的错误信息
- 可以在可执行部分和异常处理部分使用
- 错误编号必须介于 –20000 和 –20999 之间
- 错误消息的长度可长达 2048 个字节
引发应用程序错误的语法：
RAISE_APPLICATION_ERROR(error_number, error_message);
实战演练
declare
v_age t1.age%type;
invalid_age exception;
begin
select age into v_age from t1 where id=4;
if v_age < 0 then
raise invalid_age ;
else
dbms_output.put_line('年龄正常');
end if;
exception
when invalid_age then
dbms_output.put_line('年龄错粗了，怎么可能是负数？');
RAISE_APPLICATION_ERROR(-20996, '年龄异常');
end;
/
