【Oracle11g】18_PL/SQL基础

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);