大型数据库Oracle笔记整理（第六章）

大型数据库Oracle笔记整理（第六章）

第六章 pl/sql编译基础

6.1 pl/sql概述

• PL/SQL是过程化的结构查询语言，它可以弥补SQL语句的不足
• 在PL/SQL中可以通过if和loop语句控制程序的执行流程，并且可以定义变量，以便于用这些变量在语句之间传递信息。
• PL/SQL是Oracle的专用语言，是对标准SQL语言的扩展，SQL语句可以嵌套在PL/SQL程序代码中，将SQL的数据处理能力和PL/SQL的过程处理能力结合在一起。

PL/SQL程序块分为无名块、命名块两种。

• 无名块指未命名的程序块，它没有被储存，每次被执行后都不能被重用
• 命名块指过程、函数、触发器和程序包等，可存储。

1.PL/SQL块结构

• 声明：declare：变量、常量、游标…
• 执行：begin…end；要执行的PL/SQL语句，功能核心区
• 异常处理：exception；异常处理语句

2.输出 dbms_output.put_line(‘文本’)；

3./：编译并运行SQL plus块

4.单行注释–；多行注释<-- -->

6.2 变量与数据类型

6.2.1数据类型

• 基本数据类型：Boolean、Binary_interger、pls_interger、Number、int、char、varchar2、date、long
• 变量本质上是一种用名称进行标记的容器，它们可以包含或保存不同类型的数据。
• 可以用下面两种语法声明pl/sql变量；

6.2.2变量

1. 标量变量：只能保存一个值的变量称为“标量变量”

• %type类型 ：声明一个与指定列名称相同的数据类型 例如 declare v_name emp.ename%type便是声明一个v_name且数据类型与emp表中的ename类型一致

  2.复合类型变量：如果变量能保存多个值，则该变量称为“复合类型变量”

• 自定义记录类型

例如：type emp_record is record(
id employees.employee_id%type,
name varchar2(50),
job employees.job_id%type,
sal number
);

emp_row emp_record;

emp_row 便是一个复合类型变量

• %rowtype类型：针对表中的某一行，使用%rowtype定义的变量，可以储存表中的一行数据。

使用%rowtype类型定义的变量形式如下：variable_name table_name%rowtype;

例如，要定义dept表中的一行记录，并且数据类型和每列类型一致，可以使用%rowtype，声明如下：dept_record dept%rowtype；

6.3条件语句

6.3.1if…then和if…then…else条件语句

• if…then

语法形式：

ifthen

pl/sql_satement;

end if;

• if…then…else

语法形式:

ifthen

pl/sql_satement1;

else

pl/sql_statement2;

end if;

if…then…else if语句实现了if语句嵌套，从而实现了判定两个以上的判断条件。该语句的语法形式如下：

if<expression 1>then

pl/sql_satement1;

else if<expression 2>then

pl/sql_statement2;

…

else

pl/sql_statement3;

end if;

6.3.2CASE条件语句

从功能上来讲，case语句基本上可以实现if条件语句能实现的所有功能，而从代码结构上来讲，case语句有更好的阅读性，因此，建议读者尽量使用case语句代替if语句。

Oracle中的case语句分为两种类型：

• 简单case表达式 使用表达式确定返回值

语法如下：

case search_expression

when expression1 then result1;

…

when expressionN then resultN;

[else default_result;]

end case;

• 搜索case表达式 使用条件确定返回值

语法如下：

case

when condition1 then result1;

when condition2 then result2;

…

when conditionN then resultN;

[else default_result;]

end case;

6.4循环语句

循环语句可以控制程序多次重复地执行某一组语句。

在pl/sql中，常用的循环语句有三种类型

1.loop循环

2.while循环

3.for循环

在这几种基本循环的基础上又可以演变除许多嵌套循环控制。

6.4.1 loop循环

loop循环又叫做死循环，在这种类型的循环中如果没有指定的exit语句，循环将一直运行，即出现死循环。死循环是该尽量避免的。因此，在loop循环中必须指定exit语句，以便于循环停止执行。

loop…end loop;循环的语法格式如下：

loop

satements;

exit when condition

end loop;

6.4.2 while循环

适用于事先无法知道控制循环终止变量值的情况。与loop循环的不同之处在于while循环的顶部包含了判断条件，这样在每次执行循环时，都将判断改条件。

while循环的语法结构如下：

while condition

loop

statements；

end loop；

在while循环中，为了防止出现死循环，需要在循环内不断修改判断条件。

6.4.3 for循环

for循环则使用一个循环计数器，并通过它来控制循环执行的次数。该计数器可以从小到大进行记录，也可以相反，从大到小记录。如果不满足循环条件则终止循环。

for循环的语法结构如下

for loop_variable_name in [reverse] lower_bound…uper_bound

loop

statements;

end loop;

6.5 游标的使用

6.5.1 隐式游标

执行一条sql语句Oracle自动生成一个隐式游标。这个游标是内存中处理该语句的工作区域，在其存储了执行sql语句的结果。通过游标的属性可知sql语句的执行结果，以及游标的状态信息。

1. 游标的主要属性有：

• %found
• %notfound
• %isopen
• %rowcount

所有的隐式游标名默认为SQL

cursor for loop语句：对sql语句返回结果进行处理

语法：

for 游标变量 in （select 语句）

loop

DML操作

end loop；

6.5.2 显示游标

使用显示游标需要四个步骤

1. 定义游标：cursor 游标名 is select语句；
2. 打开游标：open 游标名
3. 提取游标数据：fetch 游标名 into 记录名；
4. 关闭游标：close 游标名；

游标for循环

• 静态游标：在编译时就已经确定了指向的结果集
• ref游标：在运行时才确定游标指向的结果集

6.6异常处理

在编写pl/sql程序时，不可避免的会出现一些错误。在Oracle系统中使用异常来处理这些错误，这些异常都可以包括在pl/sql程序的exception块中。Oracle系统提供了许多内置的异常，用户也可以根据自己的需求来定义异常。

Oracle系统的异常可以分为三类：

• 预定义异常（会用）
• 非预定义异常（了解）
• 用户定义异常（重点）

6.6.1 预定义异常

6.6.2 非预定义异常

6.6.3用户自定义异常