Oracle PL(SQL)语言及编程

---

• PL/SQL语言简介

       SQL语言只是访问，操作数据库的语言，而并不是一种程序设计语言，因此不能用于程序开发，PL/SQL(Procedual Language/SQL)是Oracle在标准SQL语言上进行过程性扩展后形成的程序设计语言，是一种Oracle数据库特有的、支持应用开发的语言。

       PL/SQL是深入掌握和应用Oracle数据库的基础，它在Oracle数据库应用系统开发中具有重要作用。在允许运行Oracle的任何操作系统平台上均可运行PL/SQL程序。

 

• PL/SQL的基本结构

DECLARE

    --声明变量、常量、用户定义的数据类型以及游标等（可选）

BEGIN

    --主程序体，可以加入各种合法的PL/SQL语句

EXCEPTION

    --程序异常处理，当程序中出现错误时执行这一部分

END; --主程序体结束

       可以看到，PL/SQL的基本结构包含三部分：声明部分(declarative section)、执行部分(executable section)和异常处理部分(exception section)。其中，只有执行部分是必须的，其他两个部分都是可选的。注意：该结构最后的分号是必需的。

 

• PL/SQL注释

       注释(comment)增强了程序的可读性， 使得程序更易于理解。这些注释在进行编译时被PL/SQL编译器忽略。注释有单行注释和多行注释两种

1.单行注释：单行注释由两个连字符(--)开始。一直到行尾（回车符标志着注释的结束）；

2.多行注释：多行注释由/*开头，由*/结尾，这和C语言是一样的。

 

• PL/SQL字符集

1.合法字符集

（1）大写和小写字母：A-Z和a-z；

（2）数字0-9；

（3）非显示的字符、制表符、空格和回车；

（4）数学符号＋， 一，*, /, <, >, =；

（5）间隔符。包括()，{}.[], ?，!，;，:，", @, #，％，＄，^，&等

       字符集中的所有符号并且只有这些符号可以在PL/SQL程序中使用。类似于SQL。除了由引号引起来的字符串以外， PL/SQL不区分字母的大小写。标准PL/SQL字符集是ASCII字符集的一部分，ASCII是一个单字节字符集，每个字符可以表示为一个字节的数据，该性质将字符总数限制在最多256个。

 

2.分节符

       分界符(delimiter)是对PL/SQL有特殊意义的符号（单字符或者字符序列)。它们用来将标识符相互分割开。

 

 

---

 

• PL/SQL数据类型

1.数字类型

       数字类型变量存储整数或者实数。它包含NUMBER、PLS_INTEGER和BINARY _INTEGER 3种基本类型。其中，NUMBER类型的变量可以存储整数或浮点数，而BINARY_INTEGER或PLS_INTEGER类型的变景只存储整数。

       NUMBER(P,S)是一种格式化的数字。其中P是精度，S是刻度范围。精度是数值中所有有效数字的个数，而刻度范围是小数点右边数字位的个数。精度和刻度范围都是可选的，但如果指定了刻度范围，那么也必须指定精度。

注意：如果刻度范围是个负数，那么就由小数点开始向左边计算数字位的个数。

       “子类型" (subtype) 是类型的一个候选名，它是可选的，可以使用它来限制子类型变量的合法取值。有多种与NUMBER等价的子类型，实际上，它们是重命名的NUMBER数据类型。有时候可能出于可读性的考虑或者为了与来自其他数据库的数据类型相兼容会使用候选名。这些等价的类型包括DEC、DECIMAL、 DOUBLE PRECISION、INTEGER、INT、NUMERIC、REAL、SMALLINT、BINARY_INTEGER、PLS_INTEGER。

 

2.字符类型

       字符类型变量用来存储字符串或者字符数据。其类型包括VARCHAR2、 CHAR、LONG、NCHAR和NVARCHAR2(后两种类型在PUSQL8.0以后才可以使用）。

 

（1）VARCHAR2类型和数据库类型中的VARCHAR2类似，可以存储变长字符串

声明语法：

VARCHAR2(MaxLength);

MaxLength是字符出的最大长度，必须在定义中给出，因为系统没有默认的最大长度。MaxLength最大可以是32767字节，这一点与数据库类型的VARCHAR2有所不同，数据库类型的VARCHAR2的最大长度是4000字节，所以一个长度大 于4000字节的PUSQL类型VARCHAR2变员不可以赋值给数据库中的一个VARCHAR2变量，而只能赋给LONG类型的数据库变量。

注意：数据库变量和PL/SQL语言的变量是两个不问的概念。在创建表时的变量都是数据库变量。如：CREATE TABLE afei(name VARCHAR2(30)), 这里的name就是数据库变量，VARCHA应訧是数据库变量类型。

 

（2）CHAR类型表示定长字符串

声明语法：

CHAR(MaxLength);

MaxLength也是最大长度， 以字节为单位， 最大为32767字节． 与VARCHAR2 不同，MaxLenglh可以不指定，默认为1，如果赋给CHAR类型的值不足MaxLength, 则在其后面用空格补全，这也是不同于VARCHAR2的地方。注意：数据库类型中的CHAR只有2000字节，所以如果PL/SQL中CHAR类型的变量长度大于2000个字节，则不能赋给数据库中的CHAR。

       LONG类型变量是个可变的字符串，最大长度是32760字节。LONG变量与VARCHAR2变量类似。数据库类型的LONG长度最大可达2GB, 所以几乎任何字符串变量都可以赋值给它。

 

3.日期类型

       日期类型中只有一种类型—DATE，用来存储日期和时间信息，包括世纪、 年、月、天、小时、分钟和秒。DATE变量的存储空间是7个字节，每个部分占用一个字节。

 

4.布尔类型

       布尔类型中的唯一类型是BOOLEAN, 主要用于控制程序流程。一个布尔类型变量的值可以是：TRUE、FALSE或者NULL。

 

5.type定义的数据类型

       数据类型的定义，有点类似与C语言中的结构类型。

数据类型定义格式：

type <数据类型名> is <数据类型>

定义数据类型之后，就可以使用该数据类型进行定义变量。

---

 

• PL/SQL 变量和常量

1.定义常量

<常量名> constant <数据类型> := <值>;

关键字constant表示是在定义常量。常量一旦定义，在以后的使用中其值将不在改变。

 

2.定义变量

<变量名> <数据类型> [(宽度):=<初始值>];

变量定义时没有关键字，但要指定数据类型，宽度和初始值可以定义也可以不定义。

 

3.变量初始化

       许多语言没有规定未经过初始化的变量中应该存放什么内容。因此在运行时刻，未初始化的变量就可能包含随机的或者未知的取值。一般而言，如果变量的取值可以被确定，那么最好为其初始化一个数值。

       但是，PL/SQL定义了一个未初始化变量应该存放的内容，被赋值为NULL。NULL意味着“未定义或未知的取值”。换句话讲：NULL可以被默认地赋值给任何未经过初始化的变量这是PL/SQL的一个独到之处。许多其他程序设计语言没有定义未初始化变量的取值。

 

• PL/SQL 语句控制结构

 

 

1.选择结构

（1）IF语句

       选择结构的语法和高级语言的IF...THEN...ELSE很类似。

IF {条件表达式1} THEN

    {执行语句1;}

[ELSIF {条件表达式2} THEN

    {执行语句2;}]

[ELSE

    {执行语句3;}

END IF;

注意：ELSIF中只有一个E，不是ELSEIF，并且没有空格。

 

（2）CASE语句

       CASE结构是Oracle 9i后新增加的结构，它使得逻辑控制结构变得简单。类似C语言中的SWITCH语句。

CASE 检测表达式

WHEN 表达式1 THEN 执行语句1

WHEN 表达式2 THEN 执行语句2

...

WHEN 表达式n THEN 执行语句n

[ELSE 执行语句n+1］

END;

       CASE语句中的ELSE子句是可选的。如果检测表达式的值与下面任何一个表达式的值都不匹配时，PL/SQL会产生预定义错误CASE_NOT_FOUND。

实例：

DECLARE
	-- 定义两个变量：grade和score
	grade VARCHAR2(20) := '及格';
	score VARCHAR2(50);
BEGIN
	-- 将grade赋值给score
	score := CASE grade
	WHEN '不及格' THEN '成绩 < 60'
	WHEN '及格' THEN '60 < 成绩 < 70'
	WHEN '中等' THEN '70 < 成绩 < 80'
	WHEN '良好' THEN '80 < 成绩 < 90'
	WHEN '优秀' THEN '90 < 成绩 < 100'
	ELSE '输入有误！'
END;
	dbms_output.put_line(score);
END;

 

2.NULL结构

       在IF结构中，只有相关的条件为真时，相应的语句才执行，如果条件为FALSE或者NULL时，语句都不会执行。特别是当条件为NULL时，常常会对程序的流程和输出有比较大的影响。

 

3.循环结构

（1）LOOP...EXIT...END 语句

       这是一个循环控制语句，关键字LOOP和END表示循环执行的语句范围，EXIT关键字表示退出循环，它常常在一个if判断语句中。

实例：

DECLARE
	--初始化定义control_var为0
	control_var INTEGER := 0;    
BEGIN
	-- 开始循环
	LOOP     
	-- 如果control_var的值大于5则退出循环
	IF control_var > 5 THEN
		EXIT;
	END if;
	-- 改变control_var的值
	control_var := control_var +1;
END LOOP;
dbms_output.put_line(control_var);
end;

 

（2）LOOP...EXIT WHEN...END语句

       该语句表示当WHEN后面判断为真时退出循环。

实例：

DECLARE
	-- 定义var1变量为0
	var1 NUMBER(2) := 0;
BEGIN
	-- 开始循环
	LOOP
		-- 如果var1的值大于5则退出循环
		EXIT WHEN var1 > 5;
		-- 改变var1的值
		var1 := var1 + 1;
END LOOP;
dbms_output.put_line(var1);
END;

 

（3）WHILE...LOOP...END语句

       该语句是先判断再进入循环。

实例：

DECLARE
	-- 定义变量var2
	var2 NUMBER(2) := 0;
BEGIN
  -- 如果变量小于等于5则退出循环
	WHILE var2 <= 5 LOOP
	var2 := var2 + 1;
END LOOP;
END;

 

（4）FOR...IN...LOOP...END语句

       这是一个可以预知循环次数的循环控制语句。

实例：

DECLARE
	-- 定义变量var3
	var3 NUMBER(3) := 0;
BEGIN
  -- var3从0-5进行循环
	FOR var3 IN 0..5 LOOP
	-- for循环自动给变量var3加1，所以NULL是一个空操作
	NULL;
END LOOP;
END;

 

（5）GOTO语句

语法格式：

GOTO label

这是个无条件转向语句。当执行GOTO语句时，控制程序会立即转到由标签标识的语句。其中，label是在PUSQL中定义的标号。标签是用双箭头括号（<<，>>）括起来的。

---

 

• PL/SQL 游标

1.基本原理

       在PL/SQL块中执行SELECT、INSERT、UPDATE和DELETE语句时，Oracle会在内存中为其分配上下文区(Context Area), 即一个缓冲区。游标是指向该区的一个指针，或是命名一个工作区(Work Area), 或是一种结构化数据类型。它为应用程序提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法，是设计嵌入式SQL语句的应用程序的常用编程方式。

       游标分为显式游标和隐式游标两种。显式游标是由用户声明和操作的一种游标；隐式游标是Oracle为所有数据操纵语句（包括只返回单行数据的查询语句）自动声明和操作的一种游标。在每个用户会话中，可以同时打开多个游标，其数量由数据库初始化参数文件中的OPEN CURSORS参数定义。

 

2.显式游标

显式游标的处理步骤：声明游标—>打开游标—>提取游标—>关闭游标

（1）声明游标语法：

curcor <游标名> is select <SQL语句>

 

（2）打开游标语法：

open <游标名>

打开游标就是执行定义的SELECT语句。执行完毕，查询结果装入内存，游标停在查询结果的首部。当打开一个游标，会完成以下几个事情：

a.检查联编变量的取值;

b.根据联编变贵的取值，确定活动集;

c.活动集的指针指向第一行。

 

（3）提取游标语法：

fetch <游标名> into <变量列表>

或

fetch <游标名> into PL/SQL 记录

注意：FETCH语句每执行一次，游标向后移动一行，值到结束（游标只能逐个向后移动，不能跳跃移动或者向前移动）

 

（4）关闭游标语法：

close <游标名>

       PL/SQL程序将被告知对于游标的处理已经结束，与游标相关联的资源可以被释放了。这些资源包括用来存储活动集的存储空间，以及用来存储活动集的临时空间。

实例：

DECLARE
  -- 定义三个变量：id，name和sex_
	id NUMBER(5);
	name VARCHAR2(50);
	sex_ CHAR(1);
	-- 定义游标
CURSOR teach_cur is
	SELECT tid, tname, sex
	from teacher
	-- 选出tid号小于2的tercher
	where tid < 2;
BEGIN
OPEN teach_cur;
  -- 将第一行数据放入变量中
	FETCH teach_cur INTO id, name, sex_;
	LOOP
	  -- 游标到尾就结束
		EXIT WHEN NOT teach_cur%found;
		-- 将sex为M的行放入man_teacher表中
		if sex = 'M' THEN
			INSERT into man_teacher(tid, tname, sex)
			 VALUES(id, name, sex_);
	  else
		  -- 否则放入woman_tercher表中
			insert into woman_teacher(tid, tnam, sex)
		   VALUES(id, name, sex_);
	  END IF;
		FETCH teach_cur into id, name, sex;
		end loop;
-- 关闭游标
close terch_cur;
end;

 

3.隐式游标

       在PL/SQL程序中用SELECT语句进行操作，则隐式地使用了游标，也就是隐式游标，这种游标无需定义，也不需打开和关闭。对每个隐式游标来说，必须要有一个INTO子句，因此使用隐式游标的SELECT语句必须只选中一行数据或只产生一行数据。

 

4.游标属性

       无论是显式游标还是隐式游标，均有如SOPEN, %FOUND, %NOTFOUND和％ ROWCOUNT四种属性。它们描述与游标操作相关的DML语句的执行情况。游标属性 只能用在PUSQL的流程控制语句内，而不能用在SQL语句内。

 

5.游标变量

       游标都是与一个SQL语句相关联，并且在编译该块的时候此语句已经是可知的，是静态的，而游标变量可以在运行时与不同的语句关联，是动态的。游标变量被用于处理多行的查询结果集。在同一个PL/SQL块中，游标变量不同于特定的查询绑定，而是在打开游标时才确定所对应的查询。因此， 游标变量可以依次对应多个查询。

       使用游标变量之前，必须先声明，然后在运行时必须为其分配存储空间， 因为游标变量是REF类型的变量，类似于高级语言中的指针。

 

（1）声明游标变量语法：

REF type

type是已经被定义的类型，REF关键字指明新的类型必须是一个指向经过定义的类型的指针。

定义游标变量完整语法：

TYPE <类型名> IS REF CURSOR

RETURN <返回类型>

 

（2）打开游标变量语法：

OPEN <游标变量> FOR <select 语句>

 

（3）关闭游标变量

       游标变量的关闭和静态游标的关闭类似，都是使用CLOSE语句，这会释放查询所使用的空间。关闭已经关闭的游标变量是非法的。

---

 

• 程序包

       程序包（Package)简称包。用于将逻辑相关的PL/SQL块或元素（变量、常量、自定义数据类型、异常、过程、函数、游标）等组织在一起，作为一个完整的单元存储在数据库中，用名称来标识程序包。它具有面向对象的程序设计语言的特点，是对PL/SQL块或元素的封装。程序包类似于面向对象中的类，其中变量相当于类的成员变量，而过程和函数就相当于类中的方法。

 

1.基本原理

       程序包有两个独立的部分：说明部分和包体部分。这两部分独立地存储在数据字典中。说明部分是包与应用程序之间的接口，只是过程、函数、游标等的名称或首部。包体部分才是这些过程、函数、游标等的具体实现。包体部分在开始构建应用程序框架时可暂不需要。一般而言，可以先独立地进行过程和函数的编写，当其较为完善后，再逐步地将其按照逻辑相关性进行打包。

 

2.包说明部分创建格式

create package <包名＞

IS

变量、 常量及数据类型定义;

游标定义头部;

函数、 过程的定义和参数列表以及返回类型;

END <包名>;

 

3.包体部分创建格式

create package body <包名＞

AS

游标、 函数、 过程的具体定义;

END <包名＞;

 

4.调用包的方式

包名.变量名(常量名)

包名.游标名

包名.函数名(过程名)

 

5.删除包语法

drop package 包名

 

参考：《Oracle 11G从入门到精通》