oracle条件表示匿名列,Oracle 第5章 施用PL/SQL

Oracle 第5章 使用PL/SQL

Oracle 第4章 同义词、序列、视图、索引

Oracle 第6章 游标

Oracle 第5章 使用PL/SQL

1、技术目标

理解 PL/SQL 功能和特点

了解数据类型及其用法

理解逻辑比较

理解控制结构

掌握错误处理

2、什么是PL/SQL？

PL/SQL是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言

PL/SQL是对SQL的扩展

支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构

可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑

与 Oracle服务器和Oracle 工具紧密集成，具备可移植性、灵活性和安全性

3、PL/SQL特点

支持 SQL，在 PL/SQL 中可以使用：

数据操纵命令

事务控制命令

游标控制

支持动态SQL，可以在程序运行过程中动态构造和运行各种SQL命令

SQL函数和SQL运算符

支持面向对象编程 (OOP)

可移植性，可运行在任何操作系统和平台上的Oralce 数据库

更佳的性能，PL/SQL 经过编译执行

与 SQL 紧密集成，简化数据处理，如下：

支持所有SQL数据类型

支持NULL值

支持%TYPE和%ROWTYPE属性类型

安全性，可以通过存储过程限制用户对数据的访问

4、PL/SQL体系结构

PL/SQL引擎用来编译和执行PL/SQL块或子程序，该引擎驻留在Oracle

服务器中，引擎仅执行过程语句，对于SQL语句则发送给Oracle服务器

上的SQL语句执行器执行，如图：

5、什么是PL/SQL块？

PL/SQL是一种块结构语言，将一组语句放在一个块中

构成PL/SQL程序的基本单元是逻辑块(过程、函数或匿名块)，逻辑块可以包含任何数量的嵌套子块，每个逻辑块对应要解决的问题或子问题，匿名块是未在数据库中命名的PL/SQL块，运行时传递到PL/SQL引擎执行

(本文只涉及匿名块，命名块见后续文章)

将逻辑上相关的声明和语句组合在一起

PL/SQL分为三个部分：

声明部分，声明块中使用的变量、游标和自定义异常，其作用域仅限所在的块，另外，局部子程序也可在声明部分中定义

可执行部分，执行命令并操作声明部分的变量、游标，可嵌套子块，该部分是必选

异常处理部分，处理执行块时引发的异常，该部分也可嵌套子块

定义PL/SQL块，语法如下：

[DECLARE

declarations]

BEGIN

executable statements

[EXCEPTION

handlers]

END;

语法说明：

declarations，为声明部分(可不要)

executable statements，为可执行部分

exception handlers，为错误处理代码块(可不要)

使用：

定义PL/SQL语句块

--声明部分

DECLARE

--定义变量

qty_on_hand number(5);

--可执行部分

BEGIN

--PL/SQL语句

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;

/

注意：默认情况下，SQL*Plus工具不会显示DBMS_OUTPUT程序包

的输出，要启用DBMS_OUTPUT，须执行命令set serveroutput on

，

语句结束后的"/"，表示执行程序块

6、PL/SQL语言特性

PL/SQL对大小写不敏感

PL/SQL中的复合符号：

:=         赋值操作符

||         连接操作符

--        单行注释

/* */        多行注释

<>        标签分隔符

..        范围操作符

**        求冥操作符

可执行部分所使用的变量和常量，必须在声明部分先声明

声明变量时必须指定变量的数据类型，可以在声明变量时初始化，如下：

语法：

变量名 数据类型[(范围)] [:= 初始值];

使用：

DECLARE

vcode varchar2(5);

vname varchar2(25) := 'rose'

BEGIN

...

END;

注意：一条语句只能声明一个变量

变量赋值时可使用select ... into语句将查询出的数据赋值给变量，

使用：

DECLARE

icode varchar2(6);

pCatg varchar2(20);

pRate number;

--定义常量

cRate CONSTANT number := 0.10;

BEGIN

...

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

select pCategory, itemRate * cRate

into  pCatg, pRate --给变量pCatg、pRate赋值

from ItemFile where itemCode = icode;

...

END;

声明常量的语法为：

常量名 CONSTANT 数据类型 := 常量值

注意：初始化变量和常量时，可用保留字"DEFAULT"替换赋值操作符":="，例如，

flag boolean DEFAULT true; --声明布尔型变量flag，初始值为true

score number DEFAULT 50; --声明数值型常量score，值为50

7、PL/SQL 数据类型

PL/SQL所使用的数据类型如下图：

数值类型：

字符类型：

日期时间类型

：

DATE

TIMESTAMP

布尔数据类型，BOOLEAN：

用于存储逻辑值(TRUE、FALSE和NULL)

不能向数据库中插入BOOLEAN数据

不能将列值保存到BOOLEAN变量中

只能对BOOLEAN变量执行逻辑操作

LOB数据类型：

用于存储大文本、图像、视频剪辑和声音剪辑等非结构化数据。

LOB 数据类型可存储最大 4GB的数据。

LOB 类型包括：

BLOB   将大型二进制对象存储在数据库中

CLOB   将大型字符数据存储在数据库中，使用：

SET SERVEROUTPUT ON

DECLARE

clob_var   CLOB;

amount     INTEGER;

offset     INTEGER;

output_var VARCHAR2(100);

BEGIN

--从表中选择CLOB字段值到clob_var变量中

SELECT chapter_text INTO clob_var

FROM my_book_text

WHERE chapter_id=5;

amount := 24;  -- 要读取的字符数

offset := 1;   -- 起始位置

--从clob数据中读取24个字符存到output_var变量中

DBMS_LOB.READ(clob_var, amount, offset, output_var);

--显示读取的信息

DBMS_OUTPUT.PUT_LINE(output_var);

END;

/

NCLOB 存储大型UNICODE字符数据

BFILE   将大型二进制对象存储在操作系统文件中，

使用：

insert into myBook values ('Oracle第5章',

BFILENAME('oracle_book', 'oracle05.txt'));

oracle_book为目录别名(目录可以使用create directory语句创建)，

oracle05.txt为目录下的文件名

属性类型：

用于引用数据库表中列的数据类型，以及表示表中一行的记录

类型，有两种：

%TYPE，引用变量和数据库列的数据类型

%ROWTYPE，提供表示表中一行的记录类型

使用1：

声明变量icode，其类型为表ItemFile中的列itemCode的类型，

icode ItemFile.itemCode%TYPE;

使用2：

声明变量empRec，可存储表Emp表中的一行记录，

empRec Emp%ROWTYPE;

属性类型的优点：

不需要知道被引用的表列的具体类型

如果被引用对象的数据类型发生改变，PL/SQL 变量的数据类型也随之改变

8、关系运算符

关系运算符    说明

-------------------------------

=                 比较两个变量是否相等，如果值相当，则返回 True

<>, !=        比较两个变量，如果不相等，则返回 True

>                比较两个变量，检查值 1 是否大于 值 2

<=              比较两个变量，检查变量 1 是否小于等于变量 2

>=              比较两个变量，检查变量 1 是否大于等于变量 2

9、布尔表达式

布尔表达式的结果为TRUE、FALSE或NULL，通常由逻辑运算符

AND、OR和NOT连接，有3中类型的布尔表达式：

表达式        说明                                          示例

------------------------------------------------

数字布尔型    用于比较数字，为定量比较           num1 := 25, num2 :=

68，

num1  <= num2 为 true

字符布尔型    比较字符串每个字节的二进制值     char1 := 'Mickey',

char2 :=

'Michael'，

char1 > char2 为 true

日期布尔型    按年月顺序进行比较                    date1 := '15-9月-08',

date2 := '16-10-08'，

date1 < date2 为 true

10、PL/SQL程序控制

三种程序控制方式：

分支(条件)控制

循环控制

顺序控制

11)分支(条件)控制

11.1)分支控制if语句

if语句有3种形式：if-then、if-then-else、if-then-elsif，

if-then语句

：先检测条件，条件为true时执行then部分的语句，

语法为，

IF condition THEN

语句;

END IF

if-then-else语句：不符合条件，执行else部分语句，

语法为，

IF condition THEN

语句;

ELSE

语句;

END IF;

使用：

从ItemFile表中查询编号为I188的商品价格，存变量iRate中，如果

价格大于200，将价格减少200，价格小于200，将价格减少50，

set serveroutput on

DECLARE

iCode varchar2(4);--商品编号

iRate number;--商品价格

BEGIN

--查询编号为I188的商品，将商品编号赋给iCode，

--将价格赋给变量iRate

select itemCode, itemRate into iCode ,iRate

from ItemFile

where itemCode = 'I188';

--根据价格范围进行更新操作

IF iRate > 200 THEN

update ItemFile set itemRate = itemRate - 200

where itemCode = iCode;

ELSE

update ItemFile set itemRate = itemRate - 50

where itemCode = iCode;

END IF;

--输出信息

DBMS_OUTPUT.PUT_LINE('编号：' || iCode ||  ' 价格：' || iRate);

END;

/

if-then-elsif语句：

将附加条件语句与if-then-else语句相结合的一种形式，语法为，

IF condition1 THEN

语句1;

ELSIF condition2 THEN

语句2;

ELSIF condition3 THEN

语句3;

...

ELSE

语句4;

END IF;

11.2)分支控制，case语句

将单个变量或表达式与多个值进行比较，执行 CASE 语句前先计算

选择器(selector)的值，当selector的值与WHEN中的表达式相等时，

执行THEN部分的语句，语法：

CASE selector

WHEN 表达式1 THEN 语句1;

WHEN 表达式1 THEN 语句1;

...

WHEN 表达式N THEN 语句N;

[ELSE 语句;]--当以上条件都不满足时执行ELSE中语句

END CASE;

使用：

提示用户输入成绩值，根据成绩输出相应的等级，

set serveroutput on

BEGIN

CASE '&score' --接收输入的成绩

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 'E' THEN DBMS_OUTPUT.PUT_LINE('较差');

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

END CASE;

END;

/

12、循环控制

循环控制包括：loop、exit语句，exit可退出循环，

exit when语句可按条件退出循环，共三种类型：

loop-end loop 循环

while 循环

for 循环

12.1)LOOP循环(无条件循环)

语法：

LOOP

语句;

END LOOP;

循环执行LOOP块中的语句，为了避免死循环，LOOP的语句中必须

加入EXIT或EXIT WHEN语句退出循环

使用：使用学员分数，大于60分的显示"通过"，

set serveroutput on

BEGIN

LOOP

--&score变量接收用户的输入

IF &score > 60 THEN

DBMS_OUTPUT.PUT_LINE('通过');

EXIT;

END IF;

END LOOP;

END;

/

12.2)WHILE循环(条件循环)

语法：

WHILE 条件表达式 LOOP

语句;

END LOOP;

使用：

每日销量循环累加，直至月销量大于等于4000为止，

语法：

set serveroutput on

DECLARE

monthlyValue number := 0;--声明月销量

dailyValue number := 0;--声明日销量

BEGIN

WHILE monthlyValue <= 4000 LOOP

--根据日销量计算月销量

monthlyValue := dailyValue * 31;

--日销量累加

dailyValue := dailyValue + 10;

--输出日销量信息

DBMS_OUTPUT.PUT_LINE('日销量：' || dailyValue);

END LOOP;

--输出月销量信息

DBMS_OUTPUT.PUT_LINE('月销量：' || monthlyValue);

END;

/

12.3)FOR循环(带计数器的循环)

语法：

FOR 计数器变量 IN [REVERSE] 值1...值N

LOOP

语句;

END LOOP;

说明：reverse可使值由大到小执行循环

使用：

循环25次，显示25个偶数，

set serveroutput on

BEGIN

FOR counter IN 1..25

LOOP

DBMS_OUTPUT.PUT_LINE(counter * 2);

END LOOP;

END;

/

13、顺序控制

可控制程序的执行顺序，可使用如下语句，

GOTO语句

无条件地跳到标签指定的语句，标签使用双尖括号(<>)括起来

2在语句块内必须是唯一的名称，标签后跟待执行语句或PL/SQL块，GOTO

3无法跳转到IF、CASE、LOOP语句或子快中

NULL语句

NULL是一条可执行语句，什么也不做，用在IF或其他语句的语法要求至少

需要一条可执行语句但又不需要执行任何操作的情况下

使用：

检查itemCode为'I188'的产品的库存，如现有库存小于订购量则更新

现有库存

DECLARE

inventory ItemFile.inventory%type;--库存，类型同字段inventory

relevel ItemFile.relevel%type;--订购数量

BEGIN

--查询商品编号为'I188'的商品库存和订购量并赋值给变量

select inventory, relevel into inventory, relevel

from ItemFile

where itemCode = 'I188';

--判断库存是否小于订购量

IF inventory < relevel THEN

--跳转到updation标签处

GOTO updation;

ELSE

--跳转到quit标签处

GOTO quit;

END IF;

--设置updation标签

<>

update ItemFile set inventory = inventory + relevel

where itemCode = 'I188';

--设置quit标签

<>

NULL;

END;

/

14、动态SQL

在PL/SQL程序中，可使用DML、TCL语句，但是DDL语句和会话控制

语句不能在PL/SQL中直接使用，要想使用可以通过动态SQL实现

动态SQL指在PL/SQL块编译时SQL语句不确定，比如可以根据用户输入

参数的不同而执行不同的操作，编译程序不处理动态语句部分，在程序

运行时可动态创建语句、对语句进行语法分析并执行

动态SQL执行方式：

通过本地SQL命令执行

通过DBMS_SQL程序包来执行

本地动态SQL语法：

EXECUTE IMMEDIATE dynamic_sql_string

[into define_variable_list]

[using bind_argument_list];

说明：

dynamic_sql_string，为动态SQL语句字符串

into子句，用于接受select语句查询出的值

using子句，用于绑定输入参数变量

注意：EXECUTE IMMEDIATE语句只能处理返回单行或没有返回的SQL语句，

要处理返回多行的动态SQL可使用REF游标的OPEN...FOR语句(见后续文章)

使用：

先执行创建表的动态SQL，再执行带参select语句，

DECLARE

sqlStmt varchar2(200); --保存SQL语句的字符串

empId number(4) := 9527; --员工编号

empRec emp%ROWTYPE; --保存行记录

BEGIN

--执行建表的动态SQL语句

EXECUTE IMMEDIATE

'create table TempTable (id number, amt number)';

--查询员工表的SQL语句，带一个输入参数id

sqlStmt := 'select * from Emp where empNo = :id';

--执行查询员工表的动态SQL语句，同时设置输入参数empId，

--查询结果(1条记录)保存到变量empRec

EXECUTE IMMEDIATE sqlStmt INTO empRec USING empId;

END;

/

15、异常

在运行程序时出现的错误叫做异常，发生异常后，语句将停止执行，

控制权转移到 PL/SQL 块的异常处理部分

异常有两种类型：

预定义异常，当PL/SQL程序违反 Oracle 规则或超越系统限制时隐式引发

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

16、预定义异常

Oracle把一些常见错误预先定义为异常

每个预定义异常都有一个错误号

必须通过名字来捕获异常，比如除数为零会引发"ZERO_DIVIDE"异常

PL/SQL支持的预定义异常如下：

异常名                              说明

--------------------------------------------

ACCESS_INTO_NULL          未初始化对象时出现

CASE_NOT_FOUND            在CASE语句中的选项与输入的数据不匹配时出现

COLLECTION_IS_NULL       给尚未初始化的表或数组赋值时出现

CURSOR_ALREADY_OPEN  试图重新打开已打开的游标时出现，重新打开游标

前必须先关闭

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                   除数为零时出现

注意：预定义异常在STANDARD程序包中声明

异常处理程序语法为：

BEGIN

...

EXCEPTION

WHEN 异常名 THEN

语句;

WHEN OTHERS THEN

语句;

END;

说明：

OTHERS 处理程序确保不会遗漏任何异常，PL/SQL块只能有一个

OTHERS异常处理程序，函数SQLCODE和SQLERRM可返回错误代码和错误文本信息

使用：

检测代码中的预定义错误，

set serveroutput on

DECLARE

orderNum varchar2(5); --订单号

BEGIN

--查询订单号

select orderNo into orderNum from orderMaster;

--异常处理

EXCEPTION

WHEN TOO_MANY_ROWS THEN

--输出提示信息

DBMS_OUTPUT.PUT_LINE('返回多行');

END;

/

注意：select into只允许返回一行，所以会引发TOO_MANY_ROWS预定义异常

17、用户定义异常

用户可自行定义代码中使用的异常，这种处理方式与PL/SQL引擎处理错误的方式相同，可以作为一种良好的编程习惯，获得直观而好管理的代码，用户定义异常在PL/SQL块或子程序的声明部分定义，语法如下：

DECLARE

异常名 EXCEPTION;

自定义异常可使用RAISE语句显示引发，语法为：

RAISE 异常名;

使用：

用自定义异常处理错误，如用户输入'附件', '顶盖', '备件'，

属于错误的类别，引发自定义异常，

set serveroutput on

DECLARE

--自定义异常(无效类别)

InvalidCategory EXCEPTION;

category varchar2(10);

BEGIN

--接收输入的数据

category := '&Category';

--判断输入数据

IF category NOT IN ('附件', '顶盖', '备件') THEN

--引发自定义异常

RAISE InvalidCategory;

ELSE

--输出信息

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

END IF;

--异常处理块

EXCEPTION

--捕获InvalidCategory异常

WHEN InvalidCategory THEN

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

END;

/

18、引发应用程序错误

使用过程RAISE_APPLICATION_ERROR创建用户定义的错误信息，用户

定义的错误信息可以比指定的异常描述得更详细，语法如下：

RAISE_APPLICATION_ERROR (error_number, error_message);

说明：

error_number，是为异常指定的编号，编号必须是-20000 ~ -20999之间

的负整数

error_message，为用户异常指定的消息文本，消息长度可到2048字节，

错误消息是与error_number表示关联的文本

RAISE_APPLICATION_ERROR即可在PL/SQL程序可执行部分使用，还

可在其异常处理部分使用，调用此过程可同时显示错误编号和消息

使用：

如果费率是未知的，将显示"未指定费率"，

set serveroutput on

DECLARE

rate ItemFile.itemRate%TYPE;

--自定义异常

RateException EXCEPTION;

BEGIN

--查询编号为'I188'的商品的费率，赋给变量rate，费率为NULL就赋0

select NVL(itemRate, 0) into rate from ItemFile

where itemCode = 'I188';

--判断费率

IF rate = 0 THEN

--费率为0，引发异常

RAISE RateException;

ELSE

--费率不为0，显示费率

DBMS_OUTPUT.PUT_LINE('费率为：' || rate);

END IF;

--异常处理块

EXCEPTION

WHEN RateException THEN

--显示指定的异常编号和异常信息

RAISE_APPLICATION_ERROR(-20008, '该商品未指定费率');

END;

/

19、总结

PL/SQL 是一种可移植的高性能事务处理语言

PL/SQL 引擎驻留在 Oracle 服务器中

PL/SQL 块由声明部分、可执行部分和异常处理部分组成

PL/SQL 数据类型包括标量数据类型、LOB 数据类型和属性类型

控制结构包括条件控制、循环控制和顺序控制

PL/SQL 支持动态 SQL

运行时出现的错误叫做异常

异常可以分为预定义异常和用户定义的异常

Oracle 第4章 同义词、序列、视图、索引

Oracle 第6章 游标