Oracle+11g+笔记(2)-Oracle PL/SQL语言及编程
2、Oracle PL/SQL语言及编程
PL/SQL(Procedual Language/SQL)是Oracle在标准SQL语言上进行过程性扩展后形成的程序设计语言，是一
种 Oracle 数据库特有的、支持应用开发的语言。
2.1 PL/SQL的基本结构
和所有过程化语言一样，PL/SQL也是一种模块式结构的语言，其大体结构如下：
DECLARE
-- 声明一些变量、常量、用户定义的数据类型以及游标等
-- 这一部分可选,如不需要可以不写
BEGIN
--主程序体,在这里可以加入各种合法语句
EXCEPTION
-- 异常处理程序,当程序中出现错误时执行这一部分
END;
-- 主程序体结束
从上面这个结构可以看出，它包含3个基本部分：声明部分(declarative section)、执行部分(executable
section)和异常处理部分(exception section)。其中，只有执行部分是必须的，其他两个部分都是可选的。需
要强调的是，该结构最后的分号是必需的。
如果没有声明部分，结构就以BEGIN关键字开头，如果没有异常处理部分，关键字EXCEPTION 将被省略，END关
键字后面紧跟着一个分号结束该块的定义，这样，仅包含执行部分的结构定义如下所示：
BEGIN
/*执行部分*/
END;
如果一个块带有声明和执行部分，但是没有异常处理部分，其定义如下：
DECLARE
/*声明部分*/
BEGIN
/*执行部分*/
END;
2.2 PL/SQL注释
2.2.1 单行注释
单行注释：--。
DECLARE
V_Department CHAR(3); -- 保存3个字符的变量
-- 系代码
V_Course NUMBER; -- 保存课程号的代码
BEGIN
-- 插入一条记录
INSERT INTO classes(department,course) VALUES(V_Department,V_Course);
END;
提示：如果注释超过一行，就必须在每一行的开头使用双连字符(--)。
2.2.2 多行注释
多行注释由/*开头，由*/结尾，这和C语言是一样的。
DECLARE
V_Department CHAR(3); /* 保存3个字符的变量,系代码 */
V_Course NUMBER; /* 保存课程号的代码 */
BEGIN
/* 插入一条记录 */
INSERT INTO classes(department,course) VALUES(V_Department,V_Course);
END;
2.3 PL/SQL字符集
2.3.1 合法字符集
所有的PL/SQL程序都是由一些字符序列编写而成的，这些字符序列中的字符取自PL/SQL语言所允许使用的字符
集。该字符集包括：
-
大写和小写字母，
A~Z和a~z。 -
数字
0~9。 -
非显示的字符、制表符、空格和回车。
-
数学符号
+，-，*，/，<，>，=。 -
间隔符，包括
()，{}，[]，?，!，;，:，’，”，@，#，%，$，^，&等。
除了引号引起来的字符，PL/SQL不区分字符的大小写。
2.3.2 分界符
分界符(delimiter)是对 PL/SQL 有特殊意义的符号(单字符或者字符序列)。它们用来将标识符相互分割开。下表
列出了在PL/SQL 中可以使用的分界符。
2.4 PL/SQL数据类型
2.4.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.4.2 字符类型
字符类型变量用来存储字符串或者字符数据。其类型包括VARCHAR2、CHAR、LONG、NCHAR 和NVARCHAR2(后两
种类型在PL/SQL8.0以后才可以使用)。
VARCHAR2 类型和数据库类型中的VARCHAR2 类似，可以存储变长字符串，声明语法为：
VARCHAR2(MaxLength);
MaxLength的最大长度是32767字节，数据库类型的VARCHAR2的最大长度是4000字节。
VARCHAR2没有默认的长度。
CHAR类型表示定长字符串。声明语法为：
CHAR(MaxLength);
MaxLength 的最大长度为32767字节，默认长度为1。
如果赋给CHAR类型的值不足MaxLength，则在其后面用空格补全，这也是不同于VARCHAR2的地方。
数据库类型的CHAR的最大长度是2000字节。
LONG类型变量是一可变的字符串，最大长度是 32760 字节。LONG 变量与VARCHAR2 变量类似。数据库类型的
LONG长度最大可达2GB，所以几乎任何字符串变量都可以赋值给它。
提示：NCHAR和NVARCHAR2类型是PL/SQL8.0以后才加入的类型，它们的长度指定根据各国字符集的不同而不
同。
2.4.3 日期类型
日期类型中只有一种类型DATE，用来存储日期和时间信息，包括世纪、年、月、天、小时、分钟和秒。DATE变量
的存储空间是7个字节，每个部分占用一个字节。
2.4.4 布尔类型
布尔类型中的唯一类型是BOOLEAN，主要用于控制程序流程。一个布尔类型变量的值可以是TRUE、FALSE 或
NULL。
2.4.5 type定义的数据类型
它类似C语言中的结构类型，定义数据类型的语句格式如下：
type<数据类型名> is <数据类型>;
在Oracle 中允许用户定义两种数据类型，它们是RECORD(记录类型)和TABLE(表类型)。
【使用type定义teacher_record记录变量】。
type teacher_record is RECORD
(
TID NUMBER(5) NOT NULL:=0,
NAME VARCHAR2(50),
TITLE VARCHAR2(50),
SEX CHAR(1)
);
该RECORD定义后，在以后的使用中就可以定义基于teacher_record的记录变量。
【定义一个 teacher_record 类型的记录变量 ateacher】。
teacher teacher_record;
引用这个记录变量时要指明内部变量，如ateacher.tid 或ateacher.name。
另外，PL/SQL 还提供了%TYPE 和%ROWTYPE 两种特殊的变量，用于声明与表的列相匹配的变量和用户定义数据
类型，前一个表示单属性的数据类型，后一个表示整个属性列表的结构，即元组的类型。
【将上述例中的 teacher_record 定义成】
type teacher_record is RECORD
(
TID TEACHERS.TID%TYPE NOT NULL:=0,
NAME TEACHERS.NAME%TYPE,
TITLE TEACHERS.TITLE%TYPE,
SEX TEACHERS.SEX%TYPE
);
也可以定义一个与表TEACHERS的结构类型一致的记录变量，如下所示：
teacher_record TEACHERS%ROWTYPE;
2.5 常量和变量
2.5.1 定义常量
<常量名> constant <数据类型>:=<值>;
Pass_Score constant INTERGE:=60;
常量一旦定义将不可改变。
2.5.2 定义变量
<变量名><数据类型>[(宽度):=<初始值>];
address VARCHAR2(30);
2.5.3 变量初始化
一般而言，如果变量的取值可以被确定，那么最好为其初始化一个数值。
但是，PL/SQL定义了一个未初始化变量应该存放的内容，被赋值为 NULL。 NULL意味着"未定义或未知的取值"。
换句话讲，NULL可以被默认地赋值给任何未经过初始化的变量。
2.6 语句控制结构
选择结构
2.6.1 IF语句
IF(条件表达式1) THEN
{语句序列1;}
[ELSIF(条件表达式2) THEN
{语句序列2;)]
[ELSE
{语句序列 3;)]
END IF;
# 第一种情况:IF...THEN语句
IF NO=98020 THEN -- 此处NO值通过游标得到,有关游标后面将讲到
INSERT INTO temp_table values(NAME,BIRTHDAY);
END IF;
# 第二种情况:IF...THEN...ELSE语句
IF NO=98020 THEN -- 如果NO值为98020则执行下面语句
INSERT INTO found_table values(NAME,BIRTHDAY);
ELSE -- 否则执行下面语句
INSERT INTO notfound_table values(NAME,BIRTHDAY);
END IF;
# 第三种情况:IF...THEN...ELSEIF语句
IF score>90 THEN -- 如果score大于90则执行下面语句
Score:=score-5;
ELSIF score<60 THEN -- 否则,如果 score小于60则执行下面语句
Score:=score+5;
END IF;
2.6.2 CASE语句
Oracle 9i之后新增的结构。
CASE 检测表达式
WHEN 表达式1 THEN 语句序列1
WHEN 表达式2 THEN 语句序列2
...
WHEN 表达式n THEN 语句序列n
[ELSE 其他语句序列]
END;
ELSE语句是可选的，如果检测表达式的值与下面任何一个表达式的值都不匹配时，PL/SQL会产生预定义错误
CASE NOT FOUND。注意：CASE语句中表达式1到表达式n的类型必须同检测表达式的类型相符。一旦选定的语句
序列被执行，控制就会立即转到CASE语句之后的语句。
DECLARE
V_grade VARCHAR2(20):='及格';
v_score VARCHAR2(50);
BEGIN
v_score:=CASE v_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(v_score);
END;
# 执行结果
60<=成绩<70
NULL结构
在IF 结构中，只有相关的条件为真时，相应的语句才执行，如果条件为FALSE 或者NULL时，语句都不会执行。特
别是当条件为NULL时，常常会对程序的流程和输出有比较大的影响。请对比以下两个例子。
DECLARE
V_NUMBER1 NUMBER;
V_NUMBER2 NUMBER;
V_Result VARCHAR2(7);
BEGIN
IF V_NUMBER1 < V_NUMBER2 THEN
V_Result:='Yes';
ELSE
V_Result:='No';
END IF;
-- No
dbms_output.put_line(V_Result);
END;
DECLARE
V_NUMBER1 NUMBER;
V_NUMBER2 NUMBER;
V_Result VARCHAR2(7);
BEGIN
IF V_NUMBER1 > V_NUMBER2 THEN
V_Result:='No';
ELSE
V_Result:='Yes';
END IF;
-- Yes
dbms_output.put_line(V_Result);
END;
DECLARE
V_NUMBER1 NUMBER;
V_NUMBER2 NUMBER;
V_Result VARCHAR2(7);
BEGIN
IF V_NUMBER1 IS NULL OR V_NUMBER2 IS NULL THEN
V_Result:='Unknown';
ELSIF V_NUMBER1<V_NUMBER2 THEN
V_Result:='YES';
ELSE
V_Result:='NO';
END IF;
-- Unknown
dbms_output.put_line(V_Result);
END;
循环结构
2.6.3 LOOP…EXIT…END
这是一个循环控制语句，关键字LOOP和END表示循环执行的语句范围，EXIT关键字表示退出循环，它常常在一
个if判断语句中。
control_var:=0; -- 初始化control_var为0
LOOP --开始循环
IF control_var>5 THEN --如果 control_var的值大于5则退出循环
EXIT;
END if;
control_var:=control_var+1; -- 改变control_var的值
END LOOP;
2.6.4 LOOP…EXIT WHEN…END
该语句表示当when后面判断为真时退出循环。
control_var:=0; -- 初始化control_var变量为0
LOOP --开始循环
EXIT WHEN control_var>5 -- 如果control_var值大于5则退出循环
control_var:=control_var+1; --改变 control_var值
END LOOP; --循环尾
2.6.5 WHILE…LOOP…END
该语句也是控制循环，不过是先判断再进入循环。
control_var:=0;
WHILE control_var<=5 LOOP --如果变量小于或等于5则循环
control_var:=control_var+1;
END LOOP;
2.6.6 FOR…IN…LOOP…END
FOR control_val in 0...5 LOOP -- control_val从0到5进行循环
null; -- 因为for语句自动给control_val加1，故这里是一个空操作
END LOOP;
2.6.7 GOTO语句
GOTO label;
... -- 程序其他部分
<<goto_mark>> -- 定义了一个转向标签goto_mark
... -- 程序其他部分
IF no>98050 THEN
GOTO goto_mark; -- 如果条件成立则转向goto_mark继续执行
... -- 程序其他部分
2.7 PL/SQL表达式
2.7.1 字符表达式
唯一的字符运算符就是并置运算符||，它的作用是把几个字符串连在一起，如表达式：
'Hello'||'World'||'!'的值等Hello World!。
2.7.2 布尔表达式
布尔表达式的值有：true、false、NULL。
(x>y)
NULL;
(4>5)OR(-1<0)
布尔表达式有3个运算符：AND、OR、NOT。
A AND B OR 1 NOT C
其中，A、B、C都是布尔变量或者表达式。表达式TRUE AND NULL的值为NULL，因为不知道第二个操作数是否
为TRUE。
布尔表达式中的算术运算符：
此外，BETWEEN操作符划定一个范围，在范围内则为真，否则为假。IN操作符判断某一元素是否属于某个集合。
2.8 PL/SQL的游标
SQL 是面向集合的，其结果一般是集合量(多条记录)，而PL/SQL 的变量一般是标量，其一组变量一次只能存放一
条记录。所以仅仅使用变量并不能完全满足SQL 语句向应用程序输出数据的要求。因为查询结果的记录数是不确
定的，事先也就不知道要声明几个变量。为此，在PL/SQL 中引入了游标(cursor)的概念，用游标来协调这两种不
同的处理方式。
2.8.1 基本原理
在PL/SQL块中执行SELECT、INSERT、UPDATE和DELETE语句时，Oracle 会在内存中为其分配上下文区
(Context Area)，即一个缓冲区。游标是指向该区的一个指针，或是命名一个工作区(Work Area)，或是一种结构
化数据类型。它为应用程序提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法，是设
计嵌入式SQL 语句的应用程序的常用编程方式。
游标分为显式游标和隐式游标两种。显式游标是由用户声明和操作的一种游标；隐式游标是 Oracle 为所有数据操
纵语句(包括只返回单行数据的查询语句)自动声明和操作的一种游标。在每个用户会话中，可以同时打开多个游
标，其数量由数据库初始化参数文件中的OPEN CURSORS参数定义。
提示：游标在PL/SQL中作为对数据库操作的必备部分应该熟练掌，灵活地使用游标才能深刻地领会程序控制数据
库操作的内涵。
2.8.2 显示游标
显示游标的处理包括声明游标、打开游标、提取游标、关闭游标4个步骤。
2.8.2.1 声明游标
对游标的声明定义了游标的名字并将该游标和一个SELECT语句相关联。
CURSOR <游标名> IS SELECT<语句>;
游标声明可以在WHERE子句中引用PL/SQL变量。这些变量被认为是联编变量bindVARIABLE，即已经被分配空间
并映射到绝对地址的变量。由于可以使用通常的作用域法则，因此这些变量必须在声明游标的位置处是可见的。
DECLARE
teacher_id NUMBER(5); -- 定义4个变量来存放TEACHERS表中的内容
teacher_name VARCHAR2(50);
teacher_title VARCHAR2(50);
teacher_sex char(1);
CURSOR teacher_cur IS --定义游标teacher_cur
SELECT TID,TNAME,TITLE,SEX FROM TEACHERS WHERE TID<117; -- 选出号码小于117的老师
2.8.2.2 打开游标
OPEN <游标名>;
打开游标就是执行定义的SELECT语句。执行完毕，查询结果装入内存，游标停在查询结果的首部，注意并不是第
一行。当打开一个游标时，会完成以下几件事情。
1)、检查联编变量的取值。
2)、根据联编变量的取值,确定活动集。
3)、活动集的指针指向第一行。
DECLARE
teacher_id NUMBER(5); --定义4个变量来存放 TEACHERS表中的内容
teacher_name VARCHAR2(50);
teacher_title VARCHAR2(50);
teacher_sex char(1);
CURSOR teacher_cur IS --定义游标 teacher_cur
SELECT TID,TNAME,TITLE,SEX FROM TEACHERS WHERE TID<117; --选出号码小于117的老师
BEGIN
OPEN teacher_cur; --打开游标
注意：打开一个已经被打开的游标是合法的。在第二次执行OPEN语句以前，PL/SQL将在重新打开该游标之前隐式
地执行一条 CLOSE 语句。一次也可以同时打开多个游标。
2.8.2.3 提取游标
# 两种形式
FETCH <游标名> INTO <变量列表>;
或 FETCH <游标名> INTO PL/SQL记录;
DECLARE
teacher_id NUMBER(5); --定义4个变量来存放 TEACHERS表中的内容
teacher_name VARCHAR2(50);
teacher_title VARCHAR2(50);
teacher_title char(1);
CURSOR teacher_cur IS --定义游标 teacher_cur
SELECT TID,TNAME,TITLE,SEX FROM TEACHERS WHERE TID<117; --选出号码小于117的老师
BEGIN
OPEN teacher_cur; --打开游标
-- 将第一行数据放入变量中，游标后移
FETCH teacher_cur INTO teacher_id,teacher_name,teacher_title,teacher_sex;
FETCH语句每执行一次，游标向后移动一行，直到结束(游标只能逐个向后移动，而不能跳跃移动或是向前移动)。
2.8.2.4 关闭游标
CLOSE <游标名>;
一旦关闭了游标，也就关闭了SELECT操作，释放了占用的内存区。如果再从游标提取数据就是非法的。这样做会
产生下面的Oracle错误：
ORA-1001:Invalid CURSOR --非法游标
或
ORA-1002:FETCH out Of sequence --超出界限
类似地，关闭一个已经被关闭的游标也是非法的，这也会触发ORA-1001错误。
【完整实例】
DECLARE
teacher_id NUMBER(5); --定义4个变量来存放TEACHERS表中的内容
teacher_name VARCHAR2(50);
teacher_title VARCHAR2(50);
teacher_sex char(1);
CURSOR teacher_cur IS -- 定义游标 teacher_cur
SELECT TID,TNAME,TITLE,SEX FROM TEACHERS WHERE TID<117; --选出号码小于117的老师
BEGIN
OPEN teacher_cur; --打开游标
FETCH teacher_cur INTO teacher_id,teacher_name,teacher_title,teacher_sex;
-- 将第一行数据放入变量中,游标后移
LOOP
EXIT WHEN NOT teacher_cur%FOUND; --如果游标到尾则结束
IF teacher_sex='M' THEN
--将性别为男的行放入男老师表MALE TEACHERS中
INSERT INTO MALE_TEACHERS(TID,TNAME,TITLE) VALUES(teacher_id,teacher_name,teacher_title);
ELSE
--将性别为女的行放入女老师表FEMALE TEACHERS 中
INSERT INTO FEMALE_TEACHERS(TID,TNAME,TITLE) VALUES(teacher_id,teacher_name,teacher_title);
END IF;
FETCH teacher_cur INTO teacher_id,teacher_name,teacher_title,teacher_Sex;
END LOOP;
close teacher_cur; -- 关闭游标
END;
SELECT * FROM MALE_TEACHERS;
SELECT * FROM FEMALE_TEACHERS;
使用显式游标时，需注意以下事项：
-
使用前须用
%ISOPEN检查其打开状态，只有此值为TRUE的游标才可使用，否则要先将游标打开。 -
在使用游标过程中，每次都要用
%FOUND或%NOTFOUND属性检查是否返回成功，即是否还有要操作的行。 -
将游标中行取至变量组中时，对应变量个数和数据类型必须完全一致。
-
使用完游标必须将其关闭，以释放相应内存资源。
用游标也能实现修改和删除操作，但必须在游标定义时指定FOR子句后面的编辑类，如DELETE或UPDATE。
【下面的过程把编号为113的老师的职称修改为Professor】
DECLARE
type teacher_record is RECORD
(
TID NUMBER(5) NOT NULL:=0,
NAME VARCHAR2(50),
TITLE VARCHAR2(50),
SEX CHAR(1)
);
CURSOR teacher_cur IS
SELECT TID,TNAME,TITLE,SEX FROM TEACHERS WHERE TID<117; --选出号码小于117的老师
BEGIN
FOR teacher_record in teacher_cur LOOP
IF teacher_record.TID=113 THEN
UPDATE TEACHERS SET TITLE='Professor';
END IF;
END LOOP;
END;
2.8.3 隐式游标
如果在 PL/SQL程序中用SELECT语句进行操作，则隐式地使用了游标，也就是隐式游标，这种游标无需定义，也
不需打开和关闭。
BEGIN
SELECT TID,TNAME,TITLE,SEX INTO teacher_id,teacher_name, teacher_title,teacher_sex FROM TEACHERS
WHERE TID=113;
END;
对每个隐式游标来说，必须要有一个INTO子句，因此使用隐式游标的SELECT语句必须只选中一行数据或只产生
一行数据。
2.8.4 游标属性
无论是显式游标还是隐式游标，均有%ISOPEN，%FOUND，%NOTFOUND和%ROWCOUNT四种属性。它们描述与游标
操作相关的DML语句的执行情况。游标属性只能用在PL/SQL的流程控制语句内，而不能用在SQL 语句内。下面将
对游标的属性进行介绍。
2.8.4.1 是否找到游标(%FOUND)
该属性表示当前游标是否指向有效一行，若是则为TRUE，否则为FALSE。检查此属性可以判断是否结束游标使
用。
【%FOUND示例】
OPEN teacher_cur; -- 打开游标;
-- 将第一行数据放入变量中,游标后移
FETCH teacher_cur INTO teacher_id,teacher_name,teacher_title,teacher Sex;
LOOP
EXIT WHEN NOT teacher_cur%FOUND; --使用了%FOUND属性
END LOOP;
在隐式游标中此属性的引用方法是SQL%FOUND。
【SQL%FOUND示例】
DELETE FROM TEACHERS WHERE TID=teacher_id; --teacher_id为一个有值变量
IF SQL%FOUND THEN --如果删除成功则写入SUCCESS表中该行号码
INSERT INTO SUCCESS VALUES(TID);
ELSE
--不成功则写入FAIL表中该行号码
INSERT INTO FAIL VALUES(TID);
END IF;
2.8.4.2 是否没找到游标(%NOTFOUND)
该属性与%FOUND属性相类似,但其值正好相反。
【%NOTFOUND 示例】
OPEN teacher_cur; --打开游标
--将第一行数据放入变量中,游标后移
FETCH teacher_cur INTO teacher_id,teacher_name,teacher_title,teacher_sex;
LOOP
EXIT WHEN teacher_cur%NOTFOUND;--使用了%NOTFOUND 属性
END LOOP;
在隐式游标中此属性的引用方法是SQL%NOTFOUND。
【SQL%NOTFOUND示例]
DELETE FROM TEACHERS WHERE TID=teacher_id; --teacher_id为一个有值变量
IF SQL%NOTFOUND THEN
--删除不成功则写入FAIL表中该行号码
INSERT INTO FAIL VALUES(TID);
ELSE
--删除成功则写入SUCCESS表中该行号码
INSERT INTO SUCCESS VALUES(TID);
END IF;
2.8.4.3 游标行数(%ROWCOUNT)
该属性记录了游标抽取过的记录行数，也可以理解为当前游标所在的行号。这个属性在循环判断中也很有效，使得
不必抽取所有记录行就可以中断游标操作。
【%ROWCOUNT示例】
LOOP
FETCH teacher_cur INTO teacher_id,teacher_name,teacher_title,teacher_sex;
EXIT WHEN teacher_cur%ROWCOUNT=10;--只抽取10条记录
...
END LOOP;
还可以用 FOR 语句控制游标的循环，系统隐含地定义了一个数据类型为%ROWCOUNT的记录，作为循环计数器，并
将隐式地打开和关闭游标。
【FOR 语句中%ROWCOUNT示例】
FOR teacher_record in teacher_cur LOOP --teacher_record为记录名,它隐含地打开游标teacher_cur
INSERT INTO TEMP_TEACHERS(TID,TNAME,TITLE,SEX) VALUES(teacher_record.TID,teacher_record.TNAME, teacher_record.TITLE,teacher_record.SEX);
END LOOP;
在隐式游标中此属性的引用方法是SQL%ROWCOUNT，表示最新处理过的 SQL语句影响的记录数。
2.8.4.4 游标是否打开(%ISOPEN)
该属性表示游标是否处于打开状态。在实际应用中，使用一个游标前，第一步往往是先检查它的%ISOPEN 属性，
看其是否已打开，若没有，要打开游标再向下操作。这也是防止运行过程中出错的必备一步。
【%ISOPEN示例】
IF teacher_cur%ISOPEN THEN
FETCH teacher_cur INTO teacher_id,teacher_name,teacher_title,teacher_sex;
ELSE
OPEN teacher_cur;
END IF;
在隐式游标中此属性的引用方式是SQL%ISOPEN。隐式游标中SQL%ISOPEN属性总为TRUE，因此在隐式游标使用
中不用打开和关闭游标，也不用检查其打开状态。
2.8.4.5 参数化游标
在定义游标时，可以带上参数，使得在使用游标时，根据参数不同所选中的数据行也不同，达到动态使用的目的。
【参数化游标示例】
ACCEPT my_tid prompt 'Please input the tid:'
DECLARE
-- 定义游标时带上参数CURSOR_id
CURSOR teacher_cur(CURSOR_id NUMBER) IS
SELECT TNAME,TITLE,SEX FROM TEACHERS WHERE TID=CURSOR_id; -- 使用参数
BEGIN
OPEN teacher_cur(my_tid); --带上实参量
LOOP
FETCH teacher_cur INTO teacher_name,teacher_title,teacher_sex;
EXIT WHEN teacher_cur%NOTFOUND;
...
END LOOP;
CLOSE teacher_cur;
END;
2.8.5 游标变量
2.8.5.1 声明游标变量
游标变量是一种引用类型。当程序运行时，它们可以指向不同的存储单元。
# 声明引用类型
REF type
其中，type是已经被定义的类型。REF关键字指明新的类型必须是一个指向经过定义的类型的指针。因此，游标
可以使用的类型就是REF CURSOR。
# 声明游标类型
TYPE <类型名> IS REF CURSOR
RETURN <返回类型>;
其中，<类型名>是新的引用类型的名字，而<返回类型>是一个记录类型，它指明了最终由游标变量返回的选择列
表的类型。
游标变量的返回类型必须是一个记录类型。它可以被显式声明为一个用户定义的记录，或者隐式使用%ROWTYPE进
行声明。在定义了引用类型以后，就可以声明该变量了。
DECLARE
TYPE t_StudentsRef IS REF CURSOR
RETURN STUDENTS%ROWTYPE; -- 定义使用%ROWTYPE
TYPE t_AbstractstudentsRecord IS RECORD( -- 定义新的记录类型
sname STUDENTS.sname%TYPE,
sex STUDENTS.sex%TYPE);
V_AbstractStudentsRecord t_AbstractStudentsRecord;
TYPE t_AbstractStudentsRef IS REF CURSOR -- 使用记录类型的游标变量
RETURN t_AbstractStudentsRecord;
TYPE t_NamesRef2 IS REF CURSOR -- 另一类型定义
RETURN v_AbstractStudentsRecord%TYPE;
v_StudentCV t_StudentsRef; -- 声明上述类型的游标变量
v_AbstractStudentCV t_AbstractStudentsRef;
上例中介绍的游标变量是受限的，它的返回类型只能是特定类型。而在PL/SQL语言中，还有一种非受限游标变
量，它在声明的时候没有RETURN子句。一个非受限游标变量可以为任何查询打开。
【定义游标变量】
DECLARE
-- 定义非受限游标变量
TYPE t_FlexibleRef IS REF CURSOR;
-- 游标变量
V_CURSORVar t_FlexibleRef;
2.8.5.2 打开游标变量
如果要将一个游标变量与一个特定的SELECT语句相关联，需要使用OPEN FOR语句，其语法是：
OPEN <游标变量> FOR <SELECT语句>;
如果游标变量是受限的，则SELECT语句的返回类型必须与游标所限的记录类型匹配，如果不匹配，Oracle会返回
错误ORA 6504。
【游标变量的打开示例】
DECLARE
TYPE t_SdudentsRef IS REF CURSOR -- 定义使用%ROWTYPE
RETURN scott.STUDENTS%ROWTYPE;
V_StudentSCV t_SdudentSRef;
BEGIN
OPEN v_StudentSCV FOR SELECT * FROM scott.STUDENTS;
END;
2.8.5.3 关闭游标变量
游标变量的关闭和静态游标的关闭类似，都是使用CLOSE语句，这会释放查询所使用的空间。关闭已经关闭的游
标变量是非法的。
2.9 存储过程
2.9.1 创建存储过程
CREATE [OR REPLACE] PROCEDURE<过程名>
(<参数1>,[方式1]<数据类型1>,
<参数2>,[方式2]<数据类型2>
...)
IS|AS
PL/SQL 过程体;
【例】过程创建示例。如果要动态观察TEACHERS表中不同性别的人数，可以建立一个过程count_num来统计同一
性别的人的数目。
create table TEACHERS(
name VARCHAR2(20) not null,
sex char(1) not null
);
insert INTO TEACHERS values('tom1','M');
insert INTO TEACHERS values('tom2','F');
insert INTO TEACHERS values('tom3','M');
insert INTO TEACHERS values('tom4','F');
insert INTO TEACHERS values('tom5','M');
insert INTO TEACHERS values('tom6','M');
insert INTO TEACHERS values('tom7','M');
insert INTO TEACHERS values('tom8','M');
-- SET SERVEROUTPUT ON FORMAT WRAPPED;
CREATE OR REPLACE PROCEDURE count_num
(in_sex in TEACHERS.SEX%TYPE) -- 输入参数
AS
out_num NUMBER;
BEGIN
IF in_sex='M' THEN
SELECT count(SEX) INTO out_num FROM TEACHERS WHERE SEX='M';
dbms_output.put_line('NUMBER of Male Teachers:'||out_num);
ELSE
SELECT count(SEX)INTO out_num FROM TEACHERS WHERE SEX='F';
dbms_output.put_line('NUMBER of Female Teachers:'||out_num);
END IF;
END count_num;
2.9.2 调用存储过程
调用过程的命令是call或者是EXECUTE|exec。
call count_num('M');
call count_num('F');
execute count_num('M');
execute count_num('F');
exec是sqlplus的命令，只能在sqlplus中使用。
call是sql命令，任何工具都可以使用，call必须有括号，即存储过程没有参数。
2.9.3 删除存储过程
DROP PROCEDURE count_num;
当一个过程已经过时，想重新定义时，不必先删除再创建，而只需在CREATE 语句后面加上OR REPLACE关键字即
可。如：CREATE OR REPLACE PROCEDURE count_num。
2.9.4 过程的参数类型及传递
过程的参数有3种类型，分别如下：
2.9.4.1 in参数类型
这是个输入类型的参数，表示这个参数值输入给过程，供过程使用。
【例 】in _num 参数示例。下面一个过程将 in_num 参数作为输入，out_num作为输出。
CREATE OR REPLACE PROCEDURE double -- 完成将一个数加倍
(in_num in NUMBER, -- 输入型参数
out_num out NUMBER
)
AS
BEGIN
out_num:=in_num*2;
END double;
declare count1 number:=10;
count2 number;
begin
double(count1, count2);
dbms_output.put_line('result is ' || count1);
end;
2.9.4.2 out 参数类型
这是个输出类型的参数，表示这个参数在过程中被赋值，可以传给过程体以外的部分或环境。
【例】out_ num参数示例。下面这一过程是将out_num参数作为输出。
CREATE OR REPLACE PROCEDURE double -- 完成将一个数加倍
(
in_num in NUMBER,
out_num out NUMBER --输出型参数
)
AS
BEGIN
out_num:=in_num*2;
END double;
2.9.4.3 in out参数类型
这种类型的参数其实是综合了上述两种参数类型，既向过程体传值，在过程体中也被赋值而传向过程体外。
【例】in_out_num 参数示例。在下面过程中 in_out_num参数既是输入又是输出。
CREATE OR REPLACE PROCEDURE double -- 完成将一个数加倍
(
in_out_num in out NUMBER -- in out 类型参数
)
AS
BEGIN
in_out_num=in_out_num*2
END double;
2.10 函数
函数一般用于计算和返回一个值，可以将经常需要进行的计算写成函数。函数的调用是表达式的一部分，而过程的
调用是一条 PL/SQL 语句。函数与过程在创建的形式上有些相似，也是编译后放在内存中供用户使用，只不过调用
时函数要用表达式，而不像过程只需调用过程名。 另外，函数必须有一个返回值，而过程则没有。
2.10.1 创建函数
CREATE [OR REPLACE] FUNCTION<>
(<参数1>,[方式1]<数据类型1>,<参数2>,[方式2]<数据类型2>...)
RETURN<表达式>
IS|AS
PL/SQL 程序体 --其中必须要有一个RETURN子句
其中，RETURN在声明部分需要定义一个返回参数的类型，而在函数体中必须有一个RETURN语句。而其中<表达
式>就是要函数返回的值。当该语句执行时，如果表达式的类型与定义不符，该表达式将被转换为函数定义子句
RETURN中指定的类型。同时，控制将立即返回到调用环境。但是，函数中可以有一个以上的返回语句。如果函
数结束时还没有遇到返回语句，就会发生错误。通常，函数只有in类型的参数。
【例】使用函数完成返回给定性别的教师数量。
CREATE OR REPLACE FUNCTION count_num
(in_sex in TEACHERS.SEX%TYPE)
return NUMBER
AS
out_num NUMBER;
BEGIN
IF in_sex='M' THEN
SELECT count(SEX) INTO out_num FROM TEACHERS WHERE SEX='M';
ELSE
SELECT count(SEX) INTO out_num FROM TEACHERS WHERE SEX='F';
END IF;
RETURN(out_num);
END count_num;
2.10.2 调用函数
调用函数时可以用全局变量接收其返回值。如：
SQL>VARIABLE man_num NUMBER
SQL>VARIABLE woman_num NUMBER
SQL>EXECUTE man_num:=count_num('M')
SQL>EXECUTE woman_num:=count_num('F')
同样，我们可以在程序块中调用它。
DECLARE
m_num NUMBER;
f_num NUMBER;
BEGIN
m_num:=count_num('M');
f_num:=count_num('F');
dbms_output.put_line('M number is ' || m_num);
dbms_output.put_line('F number is ' || f_num);
END;
M number is 6
F number is 2
2.10.3 删除函数
DROP FUNCTION count_num;
当一个函数已经过时，想重新定义时，也不先删除再创建，同样只需在CREATE语句后面加上OR REPLACE关键字
即可，如下所示：CREATE OR REPLACE FUNCTION count _num;。
2.11 程序包
程序包(package)简称包，用于将逻辑相关的 PL/SQL 块或元素(变量、常量、自定义数据类型、异常、过程、函
数、游标)等组织在一起,作为一个完整的单元存储在数据库中，用名称来标识程序包。它具有面向对象的程序设计
语言的特点，是对PL/SQL 块或元素的封装。程序包类似于面向对象中的类，其中变量相当于类的成员变量，而过
程和函数就相当于类中的方法。
2.11.1 基本原理
程序包有两个独立的部分：说明部分和包体部分。这两部分独立地存储在数据字典中。说明部分是包与应用程序之
间的接口，只是过程、函数、游标等的名称或首部。包体部分才是这些过程、函数、游标等的具体实现。包体部分
在开始构建应用程序框架时可暂不需要。一般而言，可以先独立地进行过程和函数的编写，当其较为完善后，再逐
步地将其按照逻辑相关性进行打包。
在编写程序包时，应该将公用的、通用的过程和函数编写进去，以便再次共享使用，Oracle 也提供了许多程序包
可供使用。为了减少重新编译调用包的应用程序的可能性，应该尽可能地减少包说明部分的内容，因为对包体的更
新不会导致重新编译包的应用程序，而对说明部分的更新则需要重新编译每一个调用包的应用程序。
2.11.2 创建包
程序包由包说明和包体两部分组成，包说明部分相当于一个包的头，它对包的所有部件进行一个简单声明，这些部
件可以被外界应用程序访问，其中的过程、函数、变量、常量和游标都是公共的，可在应用程序执行过程中调用。
2.11.2.1 包说明部分
包说明部分创建格式如下：
CREATE PACKAGE<包名>
IS
变量、常量及数据类型定义;
游标定义头部;
函数、过程的定义和参数列表以及返回类型;
END<包名>;
create or replace package my_package
IS
man_num NUMBER; -- 定义了两个全局变量
woman_num NUMBER;
FUNCTION F_count_num(in_sex in TEACHERS.SEX%TYPE)
RETURN NUMBER; -- 定义了一个函数
PROCEDURE P_count_num
(in_sex in TEACHERS.SEX%TYPE,out_num out NUMBER); --定义了一个过程
END my_package;
2.11.2.2 包体部分
包体部分是包的说明部分中的游标、函数及过程的具体定义。其创建格式如下：
CREATE PACKAGE BODY<包名>
AS
游标、函数、过程的具体定义;
END<包名>;
create or replace package BODY my_package
AS
-- 函数具体定义
FUNCTION F_count_num
(in_sex in TEACHERS.SEX%TYPE)
return NUMBER
is
out_num NUMBER;
BEGIN
IF in_sex='M' THEN
SELECT count(SEX) INTO out_num FROM TEACHERS WHERE SEX='M';
ELSE
SELECT count(SEX) INTO out_num FROM TEACHERS WHERE SEX='F';
END IF;
RETURN(out_num);
END F_count_num;
-- 过程具体定义
PROCEDURE P_count_num(in_sex in TEACHERS.SEX%TYPE,out_num out NUMBER)
is
BEGIN
IF in_sex='M' THEN
SELECT count(SEX) INTO out_num FROM TEACHERS WHERE SEX='M';
ELSE
SELECT count(SEX) INTO out_num FROM TEACHERS WHERE SEX='F';
END IF;
END P_count_num;
-- 包体定义结束
END my_package;
注意：如果在包体的过程或函数定义中有变量声明，则包外不能使用这些私有变量。
2.11.3 包调用
包的调用方式为：
包名.变量名(常量名)
包名.游标名
包名.函数名(过程名)
一旦包创建之后，便可以随时调用其中的内容。
SQL>VARIABLE man_num NUMBER
SQL>EXECUTE man_num:=my_package.F_count_num('M')
declare
v number;
begin
v:=my_package.F_count_num('M');
dbms_output.put_line('result is :'||v);
end;
2.11.4 删除包
与函数和过程一样，当一个包不再使用时，要从内存中删除它。例如：
DROP PACKAGE my_package
当一个包已经过时，想重新定义时，也不必先删除再创建，同样只需在CREATE语句后面加上OR REPLACE关键字
即可，如：CREATE OR REPLACE PACKAGE my_package。
2.11.5 综合举例
-- 定义包 计算圆的面积
create or replace package pac_area is
-- 定义pi常量
v_pi constant number(5,2):=3.14;
--定义计算圆的面积的过程，打印圆的面积
procedure pro_area(v_r number);
--定义一个获取圆的面积的函数
function fun_area return number;
end;
-- 定义包体，用来实现包
create or replace package body pac_area is
--把面积参数定义成包体的成员变量，这样函数也可以使用这个变量
v_area number(5,2);
-- 实现过程
procedure pro_area(v_r number) is
begin
v_area:=v_pi*v_r*v_r;
dbms_output.put_line('圆的面积是:'||v_area);
end;
--实现函数,注意，调用该函数前，一定要先调用过程
function fun_area return number is
begin
return v_area;
end;
end;
--使用匿名块调用包中的过程和函数
declare
v_area number(5,2);
begin
-- 调用打印圆的面积的过程
pac_area.pro_area(2);
-- 调用获取圆的面积的函数
v_area :=pac_area.fun_area();
dbms_output.put_line('函数计算的圆的面积是：'||v_area);
end;
2.12 触发器
2.12.1 触发器的基本原理
触发器类似于过程、函数，其包括声明部分、异常处理部分，并且都有名称、都被存储在数据库中。但与普通的过
程、函数不同的是，函数需要用户显式地调用才执行，而触发器则是当某些事件发生时，由Oracle自动执行，触
发器的执行对用户来说是透明的。
2.12.2 触发器类型
触发器的类型包括如下三种：
DML 触发器：对表或视图执行DML操作时触发。
INSTEAD OF触发器：只定义在视图上，用来替换实际的操作语句。
系统触发器：对数据库系统进行操作(如DDL语句、启动或关闭数据库等系统事件)时触发。
2.12.3 相关概念
2.12.3.1 触发事件
引起触发器被触发的事件。如DML语句(如INSERT、UPDATE、DELETE 语句对表或视图执行数据处理操作)、
DDL 语句(如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象)、数据库系统事件(如系统启动
或退出、异常错误)、用户事件(如登录或退出数据库)。
2.12.3.2 触发条件
触发条件是由WHEN子句指定的一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触
发器，使其执行触发操作，否则即便遇到触发事件也不会执行触发器。
2.12.3.3 触发对象
触发对象包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，才会执行触发操作。
2.12.3.4 触发操作
触发器所要执行的PL/SQL程序，即执行部分。
2.12.3.5 触发时机
触发时机指定触发器的触发时间。如果指定为BEFORE，则表示在执行DML操作之前触发，以便防止某些错误操作
发生或实现某些业务规则；如果指定为AFTER，则表示在DML操作之后触发，以便记录该操作或做某些事后处
理。
2.12.3.6 条件谓词
当在触发器中包含了多个触发事件(INSERT、UPDATE、DELETE)的组合时，为了分别针对不同的事件进行不同的
处理，需要使用Oracle提供的如下条件谓词。
INSERTING：当触发事件是INSERT时，取值为TRUE，否则为FALSE。
UPDATING[(column_1,column_2,...,column_n)]：当触发事件是UPDATE时，如果修改了column_x列，则取
值为TRUE，否则为FALSE，其中column_x是可选的。
DELETING：当触发事件是DELETE时，取值为TRUE，否则为FALSE。
2.12.3.7 触发子类型
触发子类型分别为行触发(row)和语句触发(statement)，行触发即对每一行操作时都要触发，而语句触发只对这种
操作触发一次。一般进行 SQL 语句操作时都应是行触发，只有对整个表作安全检查(即防止非法操作)时才用语句触
发。如果省略此项，默认为语句触发。
此外，触发器中还有两个相关值，分别对应被触发的行中的旧表值和新表值，用old和new来表示。
2.12.4 创建触发器
创建触发器的语句是CREATE TRIGGER，其语法格式如下：
CREATE OR REPLACE TRIGGER<触发器名>
触发条件
触发体
CREATE TRIGGER my_trigger -- 定义一个触发器my_trigger
BEFORE INSERT or UPDATE of TID,TNAME on TEACHERS
FOR each row
WHEN(new.TNAME='David') -- 这一部分是触发条件
DECLARE --下面这一部分是触发体
teacher_id TEACHERS.TID%TYPE;
INSERT_EXIST_TEACHER EXCEPTION;
BEGIN
SELECT TID INTO teacher_id FROM TEACHERS WHERE TNAME=new.TNAME;
RAISE INSERT_EXIST_TEACHER;
EXCEPTION -- 异常处理也可用在这里
WHEN INSERT_EXIST_TEACHER THEN
INSERT INTO ERROR(TID,ERR) VALUES(teacher_id,'the teacher already exists!');
END my_triqqer;
2.12.5 执行触发器
当某些事件发生时，由Oracle自动执行触发器。对一张表上的触发器最好加以限制，否则会因为触发器过多而加
重负载，影响性能。另外，最好将一张表的触发事件编写在一个触发体中，这也可以大大改善性能。
【例】把与表TEACHERS有关的所有触发事件都放在触发器my_trigger1中。
CREATE TRIGGER my_trigger1
AFTER INSERT or UPDATE or DELETE on TEACHERS
FOR each row:
DECLARE
info CHAR(10);
BEGIN
IF inserting THEN -- 如果进行插入操作
info:='INSERT';
ELSIF updating THEN --如果进行修改操作
info:='Update';
ELSE --如果进行删除操作
info:='Delete';
END IF;
INSERT INTO SQL_INFO VALUES(info); -- 记录这次操作信息
END my_trigger1;
2.12.6 删除触发器
当一个触发器不再使用时，要从内存中删除它。例如：
DROP TRIGGER my_trigger;
当一个触发器已经过时，想重新定义时，不必先删除再创建，同样只需在CREATE语句后面加上OR REPLACE关键
字即可。
CREATE OR REPLACE TRIGGER my_trigger;
扫一扫
2155
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
