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字节。

发表评论

电子邮件地址不会被公开。 必填项已用*标注