【基于SSH框架的企业级应用开发】PL/SQL编程

PL/SQL编程

什么是PL/SOL

RL/SOL( Procedural Language./SL.过程语言/SOL)是结合了ole过程语言和结构化查 言(SL)的一种扩展语言,使用PL/S0L可以编写具有很多高级功能的程序,虽然通过多个 语句也能实现同样的动能。但是相比而言.PL/SaL具有更为明显的一些优点 
(1)PL/SOL具有编程语言的特点,它能把一组S0L语句放到一个模块中,使其更具模块化程序 的特点 
(2)PL/sL可以采用过程性语言控制程序的结构.也就是说。我们可以在L/SOL中增加逻 结构,如判断,循环等程序结构 
(3)同其他的编程语言一样,PL/SOL可以对程序中的错误进行自动处理.使程序能够在遇到错 误时不会中断,即它的异常处理机制 
(4)PL/SOL程序块具有更好的可移植性,可以移植到另一个 Oracle t数据库中 (5)PL/SOL程序减少了网络的交互,有助于提高程序性能

PL/SOL体系结构

PL/SL引用来编译和执行PL/SOL块或子程序该引驻留在 Oracle服务器中。PL/SOL引 摩仅执行过程语句,而将OL语句发送给ae服务器上的SL语句执行器,由SL语句执行器执 行这些SOL语句PL/SL体系结构

PL/SOL块简介

 PL/SOL是一种块结构的语言.它将一组语句放在一个块中。PL/SOL块将逻辑上相关的声明和 语句组合在一起,匿名块是一个未在数据库中命名的R/50L块,在运行时被传速到PA/SDL引以 便执行,在P/SL块中可以使用 SELECT、 INSERT、 UPDATE.DEE等DML语句,事务控制语句及 SL函数等。PL/SOL块中不允许直接使用 CREATE.DROP或 ALTER等DOL语句.但可以通过动态 L来执行它们

一个PL/SQL块由三部分组成，即声明部分、执行部分、异常处理部分。

PL/SQL块的结构：

[DECLARE]

--声明部分：在此声明PL/SQL用到的变量，类型、及游标，以及局部的存储过程和函数

BEGIN

--执行部分：过程及SQL语句，即程序的主要部分

[EXCEPTION]

--异常处理部分:错误处理

其中:执行部分不能省略

运算符和表达式

PL/SOL语言支持的操作符包含关系运算符,一般运算符和逻辑运算符等,与SOL语言的 照表

关系运算符

运算符	意义
=	等于
<>/!=/~=/^=	不等于
<	小于
>	大于
<=	小于或等于
>=	大于或等于

一般运算符

运算符	意义
+	加号
-	减号
*	乘号
/	除号
:=	赋值号
=>	关系号
..	范围运算符
||	字符连接符

逻辑运算符

运算符	意义
IS NULL	是空值
BETWEEN AND	介于两者之间
IN	在一列值中间
AND	逻辑与
OR	逻辑或
NOT	取反

常量和变量的声明

在PLS이L块的可执行部分引用变量和常量前,必须先对其进行声明。变量和常量在P/SL块 的部分声明,在P/SOL块的可执行部分被使用

声明变量语法如下 

variable name data type[ (size)] [:= init_valuel ];

在语法中

• variable_name表示变量名称
• data_type表示变量的SL或PL/SQL数据类型
• SIZE指定变量的范围 
• Init_value指变量的初始值。

 声明常量语法

variable name CONSTANT data type :value

P/SOL程序设计中的标识符定义与SL的标识符定义的要求相同.

要求和限制如下

• 标识符名不能超过30个字符
• 第一个字符必须为字母 
• 不区分大小写.
• 不能用 (减号)
• 不能是SQL保留字 变量的命名 

 

编码规则

1. 利用缩进排列展現逻辑结构,保留字后出现的列开始缩进三个空格。如 DECLARE下面声明 变量行
2. 利用大小写増强可读性。保留宇统一大写。应用程序专用名称或者标识符统一小写,以增 强程序可读性
3. 格式化单独语句每行至多写一条语句,声明语句中尽量保持声明的数据类型靠近变量名 而不是与数据类型对齐
4. 格式化5L语句。右对弃DML语句的子句中的保留字

SELECT	INSERT	UPDATE	DELETE
SELECT FROM WHERE AND OR GROUP BY HAVING AND’OR ORDER BY	INSERT INTO VALUES INSERT INTO SELECT FROM WHERE	UPDATE SET WHERE	DELETE FROM WHERE

 注释

在PL/SQL里,可以使用如下两种符号来写注释

• 使用双”-”(减号)加注释 

v_sa1 NUMBER(12,2);--人员的工资变量

• 使用/**/来加一行或多行注释,建议使用如下方法进行多行注释

/*

||我在注释开始的首行只放斜线星号,标志注释开始

||然后注释块的每一行以双垂直线开头,突出注释后面

||的内容是注释部分,最后,将星号斜线单置于一行

*/

其中||可以不写

PL/SQL数据类型

标量数据类型

包含单个值。没有内部组件,标量数据类型包括数字.字符、布尔值和日期时间值四

Oracle使用的变量类型

类型	子类	说明	范围
Char	Character Nchar	定长字符串 民族语言字符集	0~32767 可选,默认为1
Varchar2	Varchar string Nvarchar2	可变字符串 民族语言字符集	0~32767
BINARY_INTEGER		带符号整数,为整数计算优化性能
NUMBER(p,s)	DEC Double Precision Integer Int Numeric Real Small int	小数,NUMBER的子类型 高精度实数 整数,NUMBER的子类型 整数,NUMBER的子类型 与NUMBER等价 与NUMBER等价 整数,比integer小
LONG		变长字符串	0~2147483647
DATE		日期型	公元去4712年1月1日至宫你那后4712年12/31
BOOLEAN		布尔型	TRUE/FALSE/NULL

与ORACLE教据库的数据类型的区别:

①同样美型的大小不同;②増加了布尔类型 

LOB数据类型

Oracle提供了LOB类型,用于存储大的数据对象的类型.Oracle目前主要支持了BFILE、BLOB、CLO B、NCLOB类型

属性类型

属性用于引用变量或数据库列的数据类型，以及表示表中行的记录类型。PL/SQL支持以下属性类型

1. %TYPE定义一个变量，其数据类型与已经定义的某个数据变量（尤其是表的某一列）的数据类型相一致，这时可以使用%TYPE，优点有：

• 可以不必知道所引用的数据库列的数据类型
• 引用的数据库的数据类型可以实时改变，容易保持一致，不用修改PL/SQL程序

1. %ROWTYPE

返回一个记录类型，其数据类型和数据库表结构相一致，这时可以使用%ROWTYPE，优点：

• 可以不必知道所引用的数据库中列的个数和数据类型
• 所引用的数据库中列的个数和数据类型可以实时改变，容易保持一致，不用修改PL/SQL程序

PL/SL控制语句

PL/sL程序可通过控制结构来控制命令执行的流程。标准的SOL没有流程控制的概念而 RSOL提供了丰富的流程控制语句

控制结构共有三种类型.具体包括条件控制、循环控制和顺序控制

1. 条件控制

条件控制用于根据条件执行一系列语句。条件控制包括语句和CASE语句

(1)F语句语法如下

IF<布尔表达式>THEN

PL/SQL和sQL语句 END TE IF

If<布尔表达式>THEN

PL/SQL和SQL其他语句

ELSE

其他语句

END IF;

IF <布尔表达式 >THEN

  PL/SQL和SQL语句

ELSIF<其他布尔表达式>THEN

其他语句

ELSE

其他语句

END IF;

(2)CASE语句语法如下

-----------格式--------------

Case条件表达式

When 条件表达式结果 then

语句段1

When条件表达式结果2then

语句段2

When 条件表达式结果n then

语句段N

[ELSE 语句段]

END CASE;

1. 循环控制

循环控制用于重复执行一系列语句。循环控制包括LOOP和EXT语句,使用EXT语句可 退出循环,使用 EXIT WHEN语句可以根据条件结束循环。

循环共有三种类型,包括LOOP循环、 WHILE循环和FOR循环

(1)LO0P循环语法如下。

LOOP

执行的语句: 

WXIT WHEN<条件语句>一一条件满足,退出语环语句 

(2)WHILE循环语法如下 

WHEN<布尔表达式>LOOP 
要执行的语句

END LOOP;

1. FOR语环语法如下 语法 
   FOR循环计数器IN [REVERSE]下限…上限LOOP 

END LOOP;

顾序控制

顺序控制用于按顺序执行语句、序控制包括null语句和GOTO语句,GOTO语句不推春使用 下面介绍NULL语句 

NULL语:NUL语句是一个可执行语句,相当于ー个占位符或不执行任何操作的空语句,它可以使某些语句变得有意义,提高程序的可读性,保证其他语句结构的完整性和正确性 

异常

在运行程序时出现的错误叫异常.发生异常后,语句将停止执行,PL/SQL引擎立即将控制器转到PL/SQL块的异常处理部分,值得注意的是.PL/SQL编译错误发生在PL/SQL程序执行之前 因此不能由PL/SQL异常处理部分来处理

异常情况处理( XCEPTION)用来处理正常执行过程中未预料的事件,这里介绍两种比较典型的即预定义异常和用户自定义异常

预定义异常

Oracle预定义的异常情况大约有24个对于这种异常情况的处理,无须在程序中定义,可由Oracle自动引发

处理用户自定义异常

程序在执行过程中,出现编程人员认为的非正常情况。对于这种异常情况的处理:需要用户在 程序中定义,然后显式地在程序中将其引发,用户定义的异常错误通过显式使用RAS语句来触发 当引发一个异常错误时,控制就转到 EXCEPTION块异常错误部分。执行错误处理代码

对于这类异常情况的处理。步骤如下

(1)在PL/SQL块的定义部分定义异常情况

<异常情况> EXCEPTION

(2)抛出异常情况

RAISE<异常情况>

(3)在PL/SQL块的异常情况处理部分对异常情况做出相应的处理

除了以上异常情况处理外, RAISE_APPLICATION_ ERROR存储过程还可以重新定义异常 为应用程序提供一种与 Oracle交互的方法

RASE_ APPLICATION EROR(error_number,erroe_message);

游标

游标的基本原理

在oracle中、在执行一个有 SELECT.insert、 UPDATE和 DELETE语句的PL/SQL,Oracle会在内存中为其分配一个缓冲区,将执行结果放在这个缓冲区中,而游标是指向该区的一个指针,游标为应用程序供了一种对多行数据查询结果集中的每行数据进行单独处理的方法,是设计嵌入 式SQL语句的应用程序的常用编方式

游标分类

在 Oracle中提供了两种标类型,即静态游标和动态游标。静态游标是在编译时知道明确的 SEEECT语句的游标。静态游标又分为两种类型.即隐式游标和显式源标。

显式游标

显式游标的使用步骤

1. 声明游标

语法:

CURSOR cursor_name【游标名称】[(parameter【指定输入参数，在指定时，不饿能使用长度约束】 [,]parameter]...)]

[RETURN return_type【用于定义游标提取的行的类型】] IS select_statement【游标定义的查询语句】;

1. 打开游标

Open currsor——name[(parameters)]

1. 提取游标

Fetch 游标的名称 into 变量名

4)关闭游标

语法：

CLOSE cursor_name;

显式游标属性

• %FOUND:只有DML影响一行或多行时，此属性才返回TRUE；
• %NOTFOUND:与%FOUND属性相反，没有影响时，返回TRUE；
• %ROWCOUNT:返回受影响行数
• ISOPEN：返回游标是否已开启

使用显式游标删除或更新

使用游标时，如果处理过程中需要删除或更新行，在定义游标时必须使用SELECT……FOR UPDATE语句，而在执行DELETE和UPDATE时使用WHERE CURRENT OF子句指定游标的当前行，声明更新游标语法：

CURSOR cursor_name IS

select_statements FOR UPDATE [OF columns];--更新查询，锁定选择的行，

1）选择单表更新查询时，可以省略OF子句

2）当选择多个表更新查询时，被锁定的行来源于ＯＦ子句后声明的列所在的表中的行。

在使用FOR UPDATE声明游标之后，可以使用以下语法更新：

UPDATE table_name

SET column_name =column_value

WHERE CURRENT OF cursor_name;

在语法中：

多表查询时，更新表为锁定行所在的表

使用循环游标简化游标的读取

语法:

FOR record_index IN cursor_name

LOOP

executable_statements

END LOOP;

在语法中：

• record_index:声明的记录变量值。此变量的属性声明为％ROWTYPE类型，作用域在for循环之内

存储过程

子程序的组成

子程序是已命名的P/SOL块.它们存储在数据库中,可以为它们指定参数。也可以从任何数 据库客户端和应用程序中调用它们.子程序包括存储过程和函数。使用存储过程执行操作.使用函 数执行操作并返回值,就像应用程序中的方法一样 与匿名的PA/SL块一样,子程序具有声明部分.可执行部分,异常处理部分(可选)

1. 声明部分

声明部分包括类型、游标,常量、变量、异常和嵌套子程序的声明。这些项是局部的.退出子 程序后将不复存在

1. 可执行部分

可执行部分包括值,控制执行过程及操纵 Oracle数据的语句 3.异常处理部分 异常处理部分包括异常处理程序,负责处理执行存储过程中出现的异常 子程序的优点如下

• 模块化:通过子程序,可以将程序分解为可管理的.明确的逻辑模块
• 可重用性:子程序在创建并执行后,就可以在任意数目的应用程序中使用
• 可理护性,子程序可以前化护作。如果一个子程序受到影响。则只需要修改该子程序的定义
• 安全性用户可以没置权限。使访问数的唯一方式就是通过用户提供的存儲过程和函数,这不仅可以让数据更加安全。而且可以保证它的正确性 

存储过程用法

存过程是执行某些作的子程序是执行持定任务的模块,从报本上讲,存储过程就是命名的程序、它可以赋予参数并存储在款据库中,然后由一个应用程序或其他PL/SQL程序调用

介绍存健过程的基本用法

1. 建存储过程 (2)调用存储过程

本章总结

• PL/SQL是一种可移植的高性能事务处理语言。
• PL/SQL引擎驻留在 Oracle服务器中。
• PL/SQL块由声明部分、可执行部分和错误处理部分组成。
• PL/SQL数据类型包括标量数据类型、LOB数据类型和属性类型。
• PL/SQL程序可通过控制结构控制命令执行的流程。
• PL/SQL使用异常增强程序的健壮性,包含预定义异常和用户自定义异常。
• 游标是在PL/SQL中构建的用来查询数据、获取记录集合的指针。
• 游标类型包括隐式游标、显式游标和REF游标。
• 显式游标用于可得到多行结果的查询中.可以声明为带参数或不带参数两种类型。
• 循环游标用于简化处理游标中所有行的查询
• 存储过程用于执行特定的任务。