PLSQL编程基础，控制语句，异常详解

PL/SQL编程基础

一、PL/SQL概述

1. PL/SQL简介

PL/SQL (Procedural Language)是过程语言，与结构化查询语言(SQL)结合而成的编程语言，是对SQL的扩展。它支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构，可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑，与 Oracle 服务器和 Oracle 工具紧密集成，具备可移植性、灵活性和安全性。

简单的说：PL/SQL是PL+SQL的组合（即过程语言+结构化查询语言），是SQL的扩充，SQL能做的，PL/SQL绝大多数都能做。。

2. PL/SQL的优点

l 支持SQL：数据操纵命令，事务控制命令，游标控制，SQL函数和SQL运算符；

l 支持面向对象编程；

l 可移植性，可运行在任何操作系统上；

l 经过编译执行，性能佳；

l 与SQL紧密集成，简化数据处理，支持SQL数据类型，支持NULL值，支持%type和%rowtype属性类型（oracle中最有意思的）；

l 安全性

3. PL/SQL体系结构

二、PL/SQL编程结构

PL/SQL 块是构成 PL/SQL 程序的基本单元，它将逻辑上相关的声明和语句组合在一起。

PL/SQL 块分为三个部分：声明部分、可执行部分和异常处理部分。

[DECLARE

declarations] --声明部分：定义变量、游标和自定义异常

BEGIN

executable statements --可执行部分：包含 SQL 和 PL/SQL 语句

[EXCEPTION

handlers] --异常处理部分：指定出现错误时需要执行的操作

END;

示例：

/* 声明部分 */

DECLARE

qty_on_hand NUMBER(5);

BEGIN

/* 可执行部分 */

SELECT quantity INTO qty_on_hand FROM Products

WHERE product = '芭比娃娃'

FOR UPDATE OF quantity;

IF qty_on_hand > 0 THEN

UPDATE Products SET quantity = quantity + 1

WHERE product = '芭比娃娃';

INSERT INTO purchase_record

VALUES ('已购买芭比娃娃', SYSDATE);

END IF;

COMMIT;

/* 异常处理语句 */

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('出错:'|| SQLERRM);

END;

提示：在BEGIN……END里面用SELECT语句，必须要用SELECT…INTO。

三、PL/SQL编程基础

1. 变量和常量

声明变量：

变量名 类型;

icode VARCHAR2(6);

示例：

DECLARE

/* 声明变量，注意以；号结束 */

icode VARCHAR2(6);

p_catg VARCHAR2(20);

p_rate NUMBER;

c_rate CONSTANT NUMBER := 0.10;

BEGIN

...

icode := 'i205'; --为变量赋值

SELECT p_category, itemrate * c_rate

INTO p_catg, p_rate FROM itemfile

WHERE itemcode = icode;

...

END;

2. 赋值

(1) 使用 := 赋值

c_rate NUMBER := 0.10;

(2) 使用SELECT INTO 语句赋值

SELECT 列1, 列2…… INTO 变量1，变量2…… FROM 表 WHERE 条件

3. 输入与输出

输入：

l &（一般做测试用，使用少）

DECLARE

sid NUMBER(5);

BEGIN

sid:=&请输入;

DBMS_OUTPUT.PUT_LINE(sid);

END;

提示：

sid:=&请输入;--表示录入的是NUMBER整型

sid:='&请输入'; --表示录入的是VARCHAR2字符串类型。

输出：

l SELECT输出

SELECT ‘好好学习’ FROM dual;

l DBMS_OUTPUT.PUT_LINE输出语句

DBMS_OUTPUT.PUT_LINE(‘好好学习，天天向上’);

4. 拼接

拼接符号：||

DBMS_OUTPUT.PUT_LINE(‘好好学习’||’ ’||’天天向上’);

5. 注释

l -- 行注释

l /* */多行注释

四、数据类型

1. 常用标量类型

字符	CHAR、VARCHAR2、NVARCHAR2、LONG
数字	NUMBER、DECIAML、FLOAT、INTEGER
日期时间	DATE、TIMESTAMP
布尔型	BOOLEAN（用于存TRUE、FLASE、NULL）

2. LOB类型

用于存储大文本、图片、视频、声音等非结构化数据。最大可存储4GB数据。

BLOB	存大型二进制对象
CLOB	存大型字符数据
NCLOB	存大型UNICODE字符
BFILE	存大型二进制对象文件

3. 属性类型

l %TYPE 列类型

提供某个变量或某个列的数据类型。

示例1：

在emp表中求7369的入职日期（不知道入职日期这列是什么数据类型）

DECLARE

sid NUMBER := 7369;

shiredate emp.hiredate%TYPE; --声明个变量，它的类型与emp表中hiredate的列同一种类型

BEGIN

SELECT hiredate INTO shiredate FROM emp WHERE empno=sid;

DBMS_OUTPUT.PUT_LINE(shiredate);

END;

示例2：

求7369的姓名、工作信息（在未知列类型的情况下）

DECLARE

sid NUMBER;

sname emp.ename%TYPE;

sjob emp.job%TYPE;

BEGIN

sid:=7369;

SELECT ename, job INTO sname, sjob FROM emp WHERE empno=sid;

DBMS_OUTPUT.PUT_LINE(sname||' '||sjob);

END;

l %ROWTYPE 行类型

若表中不知类型的列太多，使用%TYPE必定会写很多代码。这里为简便，我们使用%ROWTYPE.

即某变量的类型是某表行的类型（包括多列），即称对象名。

语法：

对象变量 表名%ROWTYPE;

示例：

查询7369的所有信息（在未知所有列类型的情况下）

DECLARE

sid number;

er emp%ROWTYPE; --er可以看成是对象名

BEGIN

sid:=7369;

SELECT * INTO er FROM emp WHERE empno=sid;

DBMS_OUTPUT.PUT_LINE(er.ename||' '||er.job); --输出er对象中的ename

END;

五、PL/SQL控制语句

PL/SQL支持的流程控制语句：

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

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

顺序控制：GOTO语句、NULL语句（什么也不做的语句）

1. IF语句

l IF - THEN …… ENF IF

l IF - THEN - ELSE …… ENF IF

l IF - THEN -ELSIF …… ENF IF

示例：

输入工资，如果大于3500交税，=3500刚好，<3500努力

DECLARE

sal NUMBER;

BEGIN

sal := &请输入工资;

IF sal>3500 THEN

DBMS_OUTPUT.PUT_LINE('交税');

ELSIF sal=3500 THEN

DBMS_OUTPUT.PUT_LINE('刚好');

ELSE

DBMS_OUTPUT.PUT_LINE('努力');

END IF;

END;

2. CASE语句

CASE 语句用于根据单个变量或表达式与多个值进行比较。

示例：

输入成绩，根据成绩。

BEGIN

CASE ’&请输入’

WHEN ’A’ THEN DBMS_OUTPUT.PUT_LINE(‘优异’);

WHEN ’B’ THEN DBMS_OUTPUT.PUT_LINE (‘优秀’);

WHEN ’C’ THEN DBMS_OUTPUT.PUT_LINE (‘良好’);

WHEN ’D’ THEN DBMS_OUTPUT.PUT_LINE (‘一般’);

WHEN ’F’ THEN DBMS_OUTPUT.PUT_LINE (‘较差’);

ELSE DBMS_OUTPUT.PUT_LINE (’没有此成绩’);

END CASE;

END;

3. LOOP语句

LOOP是无条件循环（即无条件），与EXIT或EXIT WHEN搭配使用。

EXIT：表示退出LOOP循环

EXIT WHEN：表示当某条件满足时退出LOOP循环。

示例1：

打印输出1~100。

DECLARE

i NUMBER(3);

BEGIN

i := 1;

LOOP

DBMS_OUTPUT.PUT_LINE(i);

i := i + 1;

EXIT WHEN i=100; --当i=100时退出LOOP循环

END LOOP;

END;

示例2：

控制循环跳转。

DECLARE

i NUMBER(3);

BEGIN

i := 1;

LOOP

LOOP

DBMS_OUTPUT.PUT_LINE(i);

i:=i+1;

EXIT a_loop WHEN i=100; --当i=100时退到<>位置

END LOOP;

EXIT b_loop WHEN i=100;

END LOOP;

<> --<<标名>>

DBMS_OUTPUT.PUT_LINE(‘第一位置’);

<> --loop循环的名字

DBMS_OUTPUT.PUT_LINE(‘第二位置’);

END;

4. WHILE语句

WHILE是有条件循环语句，与LOOP搭配使用。

示例：

求1~100的和。

DECLARE

i NUMBER(3);

mySum NUMBER(5);

BEGIN

i := 0;

mySum := 0;

WHILE i<=100 LOOP

mySum := mySum + i;

i := i + 1;

END LOOP;

DBMS_OUTPUT.PUT_LINE('总和：'||mySum);

END;

5. FOR语句

FOR是有条件循环语句，也称FOR-IN循环。与LOOP搭配使用。

示例1：

求1~100的和。

DECLARE

mysum NUMBER(5);

BEGIN

mysum := 0;

FOR i IN 1..100 LOOP --1..100表示从1开始到100结束

mysum:=mysum+i;

END LOOP;

DBMS_OUTPUT.PUT_LINE(mysum);

END;

示例2：

求1~100偶数之和.

DECLARE

mysum NUMBER(5);

BEGIN

mysum := 0;

FOR i IN 1..100 LOOP --1..100表示从1开始到100结束

IF mod(i, 2) THEN

mysum:=mysum+i;

END IF;

END LOOP;

DBMS_OUTPUT.PUT_LINE(mysum);

END;

六、PL/SQL异常处理

发生异常后，语句将停止执行，控制权转移到PL/SQL块的异常处理部分。

异常处理有两种类型：

1. 预定义异常

当PL/SQL程序违反ORACLE规则或超越系统限制时隐式引发。

示例：

DECLARE

ordernum VARCHAR2(5);

BEGIN

SELECT empno INTO ordernum FROM emp;

EXCEPTION

WHEN TOO_MANY_ROWS THEN--预定义异常，即当出现行太多异常时

DBMS_OUTPUT.PUT_LINE ('返回多行'); --执行输出语句

END;

PL/SQL提供的预定义异常汇总：

ACCESS_INTO_NULL	在未初化对象时出现
CASE_NOT_FOUND	在CASE语句中的选项与用户输入的数据不匹配时出现
COLLECTION_IS_NULL	在给尚未初始化的表或数组赋值时出现
DUP_VAL_ON_INDEX	在用户试图将重复的值存在使用唯一索引的数据库列中时出现
INVALID_CURSOR	在执行非法游标运算（如打开一个尚未打开的游标）时出现
INVALID_NUMBER	在将字符串转换为数字时出现
LOGIN_DENIED	在输入的用户名或密码无效时出现
NO_DATA_FOUND	在表中不存在的请求的行时出现，此外，当程序引用已经删除的元素时
STORAGE_ERROR	在内存损坏或PL/SQL耗尽内存时出现
TOO_MANY_ROWS	在执行SELECT INTO语句后返回多行时出现
VALUE_ERROR	在产生大小限制错误时出现
ZERO_DIVIDE	以零作除数时出现
OTHERS	针对所有异常
CURSOR_ALREADY_OPEN	在用户试图打开已经打开的游标时出现

2. 自定义异常

用户可以在PL/SQL块的声明部分定义异常，自定义的异常通过RAISE语句显式引发。

示例：

用户自定义输入异常。

DECLARE

invalidCATEGORY EXCEPTION; --声明异常对象

category VARCHAR2(10);

BEGIN

category := '&Category';

IF category NOT IN ('附件','顶盖','备件') THEN --如输入的不是附件或顶盖或备件

RAISE invalidCATEGORY; --抛出invalidCATEGORY异常

ELSE

DBMS_OUTPUT.PUT_LINE('您输入的类别是'|| category);

END IF;

EXCEPTION

WHEN invalidCATEGORY THEN --当抛出invalidCATEGORY异常时，执行输出

DBMS_OUTPUT.PUT_LINE('无法识别该类别');

END;

带多个异常体

EXCEPTION

WHEN invalidCATEGORY1 THEN

DBMS_OUTPUT.PUT_LINE('错误信息1');

WHEN too_many_rows THEN

DBMS_OUTPUT.PUT_LINE('行太多');

……

3. RAISE_APPLICATION_ERROR()函数

作用：把异常信息返回给调用的客户端。

基本语法：

RAISE_APPLICATION_ERROR( error_number_in IN NUMBER, error_msg_in IN VARCHAR2);

示例：

RAISE_APPLICATION_ERROR(-20001,'错误提示信息');

解释：

-20001是错误代码，分配给用户自定义的错误代码范围在-20000~-20999，共一千个。

‘错误提示信息’是VARCHAR2类型，最长为2000字节。