欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-04-08 10:56:01
PL/SQL编程基础 一、PL/SQL概述 1. PL/SQL简介 PL/SQL (Procedural Language)是过程语言,与结构化查询语言(SQL)结合而成的编...

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