什么是PL/SQL，他包含什么内容？

  • PL/SQL 是过程语言（Procedural Language）
  • 与结构化查询语言（SQL）组合而成的编程语言
  • 包含
    • 变量和类型
    • 控制语言
    • 子程序和函数
    • 对象类型和方法

PL/SQL 体系结构及执行流程

  1. 将 PL/SQL 块发送给 Oracle 服务器
  2. Oracle 服务器启动 PL/SQL 引擎
  3. 引擎将 过程语言 发送给 过程语言执行器
  4. 执行过程语言
  5. 引擎将 SQL 语句 发送给 SQL 语句执行器
  6. 执行 SQL 语句
  7. 将结果发送给用户

关于 PL/SQL 块

  • PL/SQL 块是构成 PL/SQL 程序的基本单元
  • 将逻辑上相关的声明和语句组合在一起
  • PL/SQL 分成三个部分：声明部分、可执行部分和异常处理部分

语法

[DECLARE 
declaratons]
BEGIN
executable statements
[EXCEPTION
handlers]
END;

例如：设置变量 v_name，利用程序包输出同学们上午好

  • ||为连接符，等价于字符串中的 +
  • := 为赋值
  • dbms_output.put_line为输出语句
[DECLARE 
declaratons]
BEGIN
executable statements
[EXCEPTION
handlers]
END;

 

变量和常量的声明

  • 语法
variable_name [CONSTANT] data_type[(size)] [:=init_value];
  • 示例：CONSTANT 常量 data_type 数值类型
DECLARE 
v_name VARCHAR2(20) := '老贲'; -- 姓名
v_sql NUMBER DEFAULT 10000; -- 一个月收入
c_rate CONSTANT NUMBER(4,3) := 0.037; -- 年利率
v_income NUMBER(10,3); -- 一年利息总收入
BEGIN
-- 执行部分
END;

常用数据类型

  • 标量类型：数字、字符、布尔值、日期时间
  • 属性类型：%TYPE、%ROWTYPE

%TYPE

  • 以表中的字段类型作为数据类型 `v_列名 表名.列名%TYPE`

%ROWTYPE

  • 用于接收单条记录，每列类型以行内数据为准，常用于循环便利是使用 `v_表名_record 表名%ROWTYPE`

赋值语句

teacher.tname%type -- 以 teacher 表中的 tname 字段类型作为数据类型
select tname ,sql into v_name,v_sql --把查询到的 tname 和 sql 分别赋值给变量 v_name,v_sql

示例

DECLARE
v_name teacher.tname%type;
v_sql teacher.sql%type;
BEGIN
select tname,sal inito v_name,v_sal from teacher where tno=1004;
dbms_output.put_line(v_name || '的月工资为：'|| v_sql);
END;

控制结构

条件控制：IF 语句、CASE 语句

IF 语法：

IF<布尔表达式> THEN
PL/SQL 和 SQL 语句 
ELSIF <布尔表达式> THEN
其他语句
ELSE
其他语句

CASE 语句

CASE 条件表达式
WHEN 结果一 THEN
语句段一
WHEN 结果二 THEN
语句段二
END CASE;

循环控制：LOOP 循环、 WHILE 循环、 FOR 循环

LOOP 语法

LOOP 
要执行的语句;
EXIT WHEN <条件语句>;
END LOOP;

WHILE 语法：

WHILE <布尔表达式> LOOP
要执行的语句;
END LOOP;

FOR 语句

FOR 循环计数器 IN [REVERSE] 下限上限 LOOP
要执行的语句
END LOOP;

顺序控制：NULL语句、GOTO 语句（知道即可）

什么是游标？

  • 游标示系统为用户开设的一个数据缓冲区
  • 用于存放 SQL 语句的执行结果（即临时结果集 ResultSet ）
  • 每个游标区都有一个名字供用户可以通过游标逐一获取记录

为什么使用游标

  • 由于 SQL 语句的结果集为数据模型，而程序设计语言为对象模型，例如 Java 只能接收单一变量或一条记录，而无法接收结果集，所以采用 变量 》 记录》 对象的形式进行转换传递
  • 步骤：执行 sql 语句 》 得到结果集放入游标志 》 循环便利游标区 》 记录一条条传给 Java 对象

游标的分类

分为：静态游标和动态游标

  • 静态游标：
    • 在编译时知道其 SELECT 语句的游标
  • 动态游标
    • 运行的时候动态决定执行何种查询

游标的使用

  • 声明游标
  • 语法：CURSOR cursor_name is query_sql;
  • 打开游标
  • 语法：OPEN cursor_name;
  • 提取游标
  • 使用 FETCH 语句实现对游标内容的读取
  • variable_list 必须与从游标提取的记过集类型相同
  • FETCH cursor_name INFO variable_list;
  • 关闭游标
  • 使用 CLOSE 语句关闭一个游标
  • 关闭游标后，所有资源都将被释放
  • CLOSE cursor_name;

游标的属性

  • %found
    • 用于检验游标是否成功，通常在 FETCH 语句前使用
    • 当游标按照条件查询出一条记录时，返回 true
  • %isopen
    • 判断游标是否处于打开状态，视图打开一个已经打开或者已经关闭的游标，将会出现错误
  • %notfound
    • 与 %found 的作用相反，当游标按照条件无法查询出记录时，返回 true
  • %rowcount
    • 循环执行游标读取数据时，返回检索出的记录数据的行数

动态和静态的 SQL

  • 什么是静态 SQL ？
    • 需要在编写 PL/SQL 程序时就确定的 SQL 语句
  • 什么是动态 SQL
    1. 动态 SQL 是指在 PL/SQL 程序执行时生成的 SQL 语句
    2. DDL 语句命令和会话控制语句不能再 PL/SQL 中直接使用，但是可以直接通过动态 SQL 来执行
    3. 编译程序对动态 SQL 不做处理，而是在程序运行时动态构造语句、对语句进行语法分析并执行

动态 SQL 实现

  • 方法一：本地动态 SQL—-EXECUTE IMMEDIATE 语句执行

语法

EXECUTE IMMEDTATE dynamic_sql_string
[INTO define_variable_list]
[USING bind_argument_list];
  1. dynamic_sql_string 是动态 SQL 语句字符串
  2. INTO 子句用于接受 SELECT 语句选择的记录值
  3. USING 子句用于绑定输入参数变量
  • 通过游标实现

语法

TYPE type_cursor IS REF CURSOR
[RETURN return_type];
cursor_name type_cursor;
OPEN cursor_name FOR dynamic_sql_string
[USING bind_argument_list];
  1. dynamic_sql_string 是动态 SQL 语句字符串
  2. USING 子句用于绑定输入参数变量
  • 方法二：DBMS_SQL 程序包
    • 通过 DBMS_SQL 程序包实现步骤
      1. 将要执行的 SQL 语句或一个语句块放到一个字符串变量中
      2. 打开光标（游标）
      3. 使用 DBMS_SQL 包的 parse 过程来分析该字符串
      4. 使用 DBMS_SQL 包的 bind_variable 过程来绑定变量
      5. 使用 DBMS_SQL 包的 execute 函数来执行语句
      6. 关闭光标（游标）

什么是 PL/SQL 异常处理

  • 在运行程序时出现的错误叫做异常
  • 发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分
  • 其中，异常分类包括：预定义异常

预定义异常

  • 语法
BEGIN
sequence_of_statements;
EXCETION
WHEN <exception_name> THEN
sequence_of_statements;
WHEN OTHERS THEN
sequence_of_statements;
END;
  • 示例
BEGIN
update teacher t set t.comm=100/0;
EXCETION
WHEN ZERO_DIVIDE THEN dbms_output.put_line('除数为0');
WHEN OTHERS THEN dbms_output.put_line('其他异常');
END;
  • 用户定义异常
    • 声明异常
DECLARE 
e_exceptionName EXCEPTION;
  • 抛出异常
BEGIN
RAISE e_exceprionName;
  • 处理异常
EXCEPTION
WHEN <exception_name> THEN
sequence_of_statements;
WHEN OTHERS THEN
sequence_of_statements;