SQL语言只是访问、操作数据库的语言，而并不是一种程序设计语言，因此不能用于程序开发。PL/SQL（Procedual Language/SQL）是Oracle在标准SQL语言上进行过程性扩展后形成的程序设计语言，是一种Oracle数据库特有的，支持应用开发的语言。
3.1 PL/SQL简介
1. PL/SQL的基本结构
和所有过程化语言一样，PL/SQL也是一种模块式结构的语言，其大体结构如下:
DECLARE
--声明一些变量、常量、用户定义的数据类型以及游标等
--这一部分可选，如不需要可以不写
BEGIN
--主程序体，在这里可以加入各种合法语句
EXCEPTION
--异常处理程序，当程序中出现错误时执行这一部分
END;
从中可以看出它包含3个基本部分：
声明部分（declarative section）
执行部分（executable section）
异常处理部分（exception section）
其中，只有执行部分是必须的，其他两部分都是可选的，该结构最后的分号是必须的。
2. PL/SQL注释
注释（comment）增强了程序的可读性。
1、单行注释
单行注释由两个连字符开始，一直到行尾
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字符集
1. 合法字符集
| 类型 | 字符 |
| 字母 | A～Z、a～z |
| 数字 | 0～9 |
| 符号 | ~！@#$%*()_-+=|:;”’<>,.?/^ |
| 空格 | Tab、空格、换行、回车 |
2. 分界符
分界符（delimiter）是对PL/SQL有特殊意义的符号（单字符或者字符序列）。它们用来将标识符相互分割分开。
- 运算符 + - * / **（指数操作符）
- 关系 =（相当于JAVA中的==） > < <> != ~= ^= <= >=
- 赋值 := 例子 a:=2
- 联接 || 例：'abc' || 123
- 标号 起始标签<< 终结标签>>
- 注释 --（单行） /* */（段落）
- 替代 <scape> <tab>
4. PL/SQL数据类型
1、数字类型
数字类型变量存储整数或者实数。包含NUMBER, PLS_INTEGER 和 BINARY_INTEGER 三种基本类型。其中，NUMBER类型的变量可以存储整数或浮点数，而BINARY_INTEGER或PLS_INTEGER类型的变量值存储整数。
NUMBER(P,S)是一种格式化的数字，其中P是精度，S是刻度范围。精度是数值中所有效数字的个数。刻度是小数点右边数字位的个数。
2、字符类型
字符类型变量用来存储字符串或者字符数据，其类型包括 VARCHAR2、CHAR、LONG、NCHAR和NVARCHAR2。
VARCHAR2类型和数据库类型中VARCHAR2类似，可以存储变长字符串，声明语法为
VARCHAR2(MAXLength); //必须在定义中给出，系统默认为最大值 32767字节。
//注：数据库类型VARCHAR2的最大长度是4000字节。
CHAR类型标识定长字符串。声明语法为：
CHAR(MaxLength); //MaxLength最大为32767字节，默认为1
LONG类型变量是一个可变的字符串，最大长度是32767字节，LONG变量与VARCHAR2变量类似。数据库类型的LONG长度最大可达2GB。
3、日期类型
日期类型中只有一种类型——DATE，用来存储日期和时间信息，包括世纪、年、月、天、小时、分钟、和秒。DATE变量的存储空间是7个字节，每个部分占用一个字节。
4、布尔类型
布尔类型中的唯一类型是BOOLEAN，主要用于控制程序流程，一个布尔类型变量的值可以是TRUE、FALSE、或NULL
5、Type定义的数据类型
type用来定义数据类型，它类似C语言中的结构类型，定于数据类型的语句格式如下：
type<数据类型>is<数据类型>;
在Oracle中准许用户定义两种数据类型，它们是RECORD(记录类型)和TABLE(表类型)
例如：使用type定义teacher_record记录变量
type teacher_record is RECORD
(
tud number(5) NOT NULL:=0,
name varchar2(50),
title varchar2(50),
sex char(1)
);
该RECORD定义后，在以后的使用中就可以定义基于teacher_record的记录变量。
ateacher teacher_record; --定义一个teacher_record类型的记录变量ateacher
-- 引用这个记录变量时要指明内部变量，如ateacher.tid等
PL/SQL还提供了％TYPE和％ROWTYPE两种特殊的变量，用于声明与表的列相匹配的变量和用户定义数据类型，前一个表示单属性的数据类型，后一个表示整个属性列表的结构，即元组的类型，（表中的每行(即数据库中的每条记录)就是一个元组，每列就是一个属性。 在二维表里，元组也称为行）。
使用%type关键字可以声明一个与指定类型相同的数据类型，他通常紧跟在指定列名的后面
使用RECORD的该类型的变量可以存储由多个列值组成的一组数据
%rowtype结合了%type和record变量的优点，可以根据数据表中的结构定义一种特殊的数据类型，用来存储从数据表中检索到的一行数据
--将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;
5. PL/SQL变量和常量
1、定义常量
定义常量的语句格式如下：
<常量名> constant <数据类型>:=<值>;
Pass_Score constant INTEGER:=60;
2、定义变量
定义变量的语句格式如下：
<变量名><数据类型>[(宽度):=<初始值>];
address VARCHAR(30);
3、变量初始化
为初始化的变量包含的是个未知的随机数据，因此定义一个变量时要初始化，被赋值为NULL
6. PL/SQL语句控制结构
| 控制语句 | 意义说明 |
| If….then | 判断if正确则执行then |
| If…..then…else | 判断if正确则执行then，否则执行else |
| If…..then…elsif | 嵌套式判断 |
| case | 有逻辑地从数值中做出选择 |
| loop….exit…..end | 循环控制，用判断语句执行exit |
| loop….exit when…end | 循环控制，用判断语句执行exit |
| while…loop….end | 当while为真时循环 |
| for….in…loop…end | 已知循环次数的循环 |
| goto | 无条件转向控制 |
1、选择结构
IF语句
选择结构的语法和高级语言的IF….THEN…..ELSE很类似，命令格式如下：
IF {条件表达式1} THEN
{语句序列1;}
[ELSEIF(条件表达式2) THEN
{语句序列2;}]
[ELSE
{语句序列3;}]
END IF;
CASE语句
CASE检测表达式
WHEN 表达式1 THEN 语句序列1
WHEN 表达式2 THEN 语句序列2
…….
[ELSE 其他语句序列 ]
END;
其中CASE语句中的ELSE子句是可选的，如果检测表达式的值与下面任何一个表达式值都不匹配时，PL/SQL会产生预定义错误CASE_NOT_FOUND
2、循环结构
所谓循环结构，即指程序按照指定的逻辑条件循环执行一组命令的结构
1. 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. 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; --循环尾
3. WHILE…..LOOP……END语句
该语句也是控制循环，不过是先判断再进入循环。
Control_var:=0;
WHILE control_var <= 5 LOOP --如果变量小于或等于5则循环
Control_var := control_var + 1;
END LOOP;
4. FOR…..IN……LOOP…..END语句
这是个预知循环次数的循环控制语句。
FOR control in 0…5 LOOP --control_var从0到5进行循环
NULL; --因为for语句自动给control_var加1，故是一个空操作。
END LOOP;
5. GOTO语句 //几乎所有使用GOTO的语句都可以使用其他的PL/SQL控制结构进行编写
无条件转向语句
GOTO label; --GOTO语句的语法
….. --程序其他部分
<<goto_mark>> --定义了一个转向标签goto_mark
….. --程序其他部分
IF no > 98050 THEN
GOTO goto_mark; --如果条件成立则转向goto_mark继续执行
…… --程序其他部分
7. PL/SQL表达式
表达式不能独立构成语句，通常表达式作为赋值语句的一部分出现在赋值运算符的右边，或者作为函数的参数等。操作数是运算符的参数，根据所拥有的参数个数，PL/SQL运算符可以分为一元运算符（一个参数）和二元运算符（两个参数），表达式按照操作对象的不同，也可以分为字符表达式和布尔表达式两种。
1、字符表达式
唯一的字符运算符就是并置运算符“||”，它的作用是把几个字符串连在一起，如表达式'hello'|| 'world'||'!'的值等于‘hello world!’
2、布尔表达式
PL/SQL控制结构都涉及布尔表达式。布尔表达式是一个判断为真还是为假的条件，他的值只有TRUE、FALSE、或NULL.
布尔表达式有3个布尔运算符：AND、OR和NOT与高级语言中的逻辑运算符一样，它们的操作对象是布尔变量或表达式。
3.2 PL/SQL的游标
游标的作用就相当于指针，通过游标PL/SQL程序可以一次处理查询结果集中的一行，并可以对该行数据进行特定操作。SQL是面向集合，结果一般是处理多条数据；PL/SQL的变量一般是标量，其一组变量一次只能存放一条记录。
1. 基本原理
在PL/SQL块中执行SELECT、INSERT、UPDATE和DELETE语句时，Oracle会在内存中为其分配上下文区（Context Area）即一个缓冲区。游标是指向该区的一个指针，或是命名一个工作区（Work Area）或是一种结构化数据类型。它为应用程序提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理方法，是涉及嵌入式SQL语句的应用程序的常用编程方式。
游标分为显式游标和隐式游标两种。显式游标是由用户声明和操作的一种游标。隐式游标是由数据库为所有数据操作语句自动声明和操作的一种游标。
2. 显式游标
显式游标的处理包括声明游标，打开游标，提取游标，关闭游标4个步骤。声明是在声明部分进行，其他三个步骤在执行部分或异常处理中进行。
1、声明游标
对游标的声明定义了游标的名字并将该游标和一个SELECT语句相关联。显式游标声明部分在DECLARE中，语法为：
CURSOR <游标名> --<游标名>是游标的名字
IS SELECT <语句>; select<语句>是将要处理的查询
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的老师
在游标定义中的select<语句>不包含INTO子句，INTO子句是FETCH语句(提取游标)的一部分
2、打开游标
OPEN <游标名>; --打开游标的语法
其中，<游标名>标识了一个已经被声明的游标。打开游标就是执行定义的SELECT语句，执行完毕，查询结果装入内存，游标在查询结果的首部，注意并不是第一行。当打开一个游标时，会完成以下几件事情
- 检查联变量的取值
- 根据联编变量的取值，确定活动集
- 活动集的指针指向第一行
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语句。一次也可以同时打开多个游标。
3、提取游标
FETCH <游标名> INTO <变量列表>; 或
FETCH <游标名> INTO PL/SQL 记录;
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; --将第一行数据放入变量中，游标后移
FETCH语句每执行一次，游标向后移动一行，直到结束（游标只能逐个向后移动，而不能跳跃移动或向前移动）
4、关闭游标
当所有的活动集都被检索以后，游标就应该被关闭。游标关闭有与游标相关联的资源可以被释放了。
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
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;
使用显式又表示，需注意以下事项
- 使用前必须用%ISOPEN检查其打开状态，只有此值为TRUE的游标才可使用，否则要先将游标打开。
- 在使用游标过程中，每次都使用%FOUND或%NOTFOUND属性检查是否返回成功，即是否还有要操作的行。
- 将游标中行取至变量组中时，对应变量个数和数据类型必须完全一致。
- 使用完游标必须将其关闭，以释放对应内存资源。
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语句必须只选中一行数据或只产生一行数据。
4. 游标属性
无论是显式游标还是隐式游标，均有％ISOPEN,%FOUND,%NOTFOUND和%ROWCOUNT四种属性，它们描述与游标操作相关的DML语句的执行情况，游标属性只能用在PL/SQL的流程控制语句内，而不能用在SQL语句内。
1、是否找到游标（％FOUND）
该属性表示当前游标是否指向有效一行，若是则为TRUE，否则为FALSE。检查此属性可以判断是否结束游标使用。
OPEN teacher_cur; --打开游标
FETCH teacher_cur INTO teacher_id,teacher_name,teacher_title,techer_sex
--将第一行数据放入变量中，游标后移
LOOP
EXIT WHEN NOT teacher_cur%FOUND; --使用了%FOUND属性
END LOOP;
/*在隐式游标中此属性的引用方法是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、是否没找到游标（％NOTFOUND）
该属性与%FOUND属性相类似，但其值正好相反。隐式游标中此属性的引用方法是SQL%NOTFOUND
3、游标行数（％ROWCOUNT）
该属性记录了游标抽取过的记录行数，也可以理解为当前游标所在的行号。这个属性在循环判断中也很有效，使得不必抽取所有记录行就可以中断游标操作。在隐式游标此属性的引用方法是SQL%ROWCOUNT，表示最新处理过的SQL语句影响的记录数。
4、游标是否打开(％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;
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;
CLOES teacher_cur;
END;
5. 游标变量
如同常量和变量的区别一样，前面所讲的游标都是与一个SQL语句相关联，并且编译该块的时候此语句已经是可知的，是静态的，而游标变量可以在运行时与不同的语句关联，是动态的。游标变量被用于处理多行的查询结果集，在同一个PL/SQL块中，游标变量不同于特定的查询绑定，而是在打开游标时才确定所对应的查询。使用游标变量之前，必须先声明，然后在运行时必须为其分配存储空间，因为游标变量是REF类型的变量，类似于高级语言中的指针。
1、声明游标变量
游标变量是一种引用类型。如果要使用引用类型，首先要声明该变量，然后相应的存储单元必须要被分配。PL/SQL中的引用类型通过下述的语法进行声明：
REF type
定义一个游标变量类型的完整语法如下：
TYPE <类型名> IS REF CURSOR
RETURN <返回类型>;
其中，<类型名> 是新的引用类型的名字，而<返回类型>是一个记录类型，它指明了最终由游标变量返回的选择列表的类型。
游标变量的返回类型必须是一个记录类型，它可以被显式声明为一个用户定义的记录，或者隐式使用％ROWTYPE进行声明，在定义了引用类型以后，就可以声明该变量了。
例如:
DECLARE
TYPE t_StudentsRef IS REF CURSOR --定义使用％ROWTYPE
RETURN STUDENTS%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;
定义游标变量：
DECLARE
TYPE t_FlexibleRef IS REF CURSOR; --定义非受限游标变量
V_CURSORVar t_FlexibleRef; --游标变量
2、打开游标变量
如果要将一个游标变量与一个特定的SELECT语句相关联，需要使用OPEN FOR语句，其语法是：
OPEN <游标变量> FOR <SELECT 语句>;
如果游标变量是受限的，则SELECT语句的返回类型必须与游标所限的记录类型匹配，如果不匹配，Oracle会返回错误ORA_6504。例如：
DECLARE
TYPE t_SdudentsRef IS REF CURSOR --定义使用%ROWTYPE
RETURN STUDENTS%ROWTYPE;
V_StudentSCV t_SdudentSRef;
BEGIN
OPEN v_StudentSCV FOR
SELECT * FROM STUDENTS;
END;
3、关闭游标变量
游标变量的关闭和静态游标关闭类似，都是使用CLOSE语句，这会释放查询所使用的空间。关闭已经关闭的游标变量是非法的。
3.3 过程
Oracle准许在数据库的内部创建并存储编译的PL/SQL程序，以便随时调出使用，该类程序包括过程，函数，包和触发器。
1. 创建过程
过程用来完成一系列的操作，它的创建语法如下:
CREATE [OR REPLACE] PROCEDURE <过程名>
(<参数1>,[方式1] <数据类型1>,
<参数2>,[方式2] <数据类型2>
…）
IS|AS
PL/SQL 过程体;
例如：过程创建实例，如果要动态观察TEACHERS表中不同性别的人数，可以建立一个过程count_num来统计同一性别的人的数据。
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. 调用过程
调用过程的命令是EXECUTE,如上述创建过程count_num来查看男女教师的数量。
EXECUTE count_num(‘M’);
EXECUTE count_num(‘F’);
3. 删除过程
当一个过程不再需要时，要将此过程从内存中删除，以释放相应的内存空间，可以使用下面的语句：
DROP PROCEDURE count_num;
当一个过程已经过时，想重新定义时，不必先删除再创建，而只需要在CREATE语句后面加上OR REPLACE关键字即可。如：
CREATE OR REPLACE PROCEDURE count_num
4. 过程的参数类型及传递
过程的参数有3种类型，分别如下：
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;
2、Out参数类型
这是个输出类型的参数，表示这个参数在过程中被赋值，可以传给过程以外的部分或环境。
3、In out参数类型
这种类型的参数其实是综合了上述两种参数类型，既向过程传值，在过程中也被赋值而传向过程体外。
3.4 函数
函数一般用于计算和返回一个值，可以将经常需要进行的计算写成函数，函数的调用是表达式的一部分，而过程的调用是一条PL/SQL语句。函数与过程在创建的形式上有些相似，也是编译后放在内存中供用户使用，只不过调用时函数要用表达式，而不像过程只需要调用过程名，另外函数必须有一个返回值，而过程则没有。
1. 创建函数
创建函数的语法格式如下：
CREATE [OR REPLACE ] FUNCTION <>
(<参数1>,[方式1]<数据类型1>,<参数2>,[方式2]<数据类型2>….)
RETURN <表达式>
IS|AS
PL/SQL程序体 --其中必须要有个return子句
例如：使用函数完成返回给定性别的教师数量。
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. 函数调用
调用函数时可以用全局变量接收其返回值 如：
SQL>VARIABLE man_num NUMBER
SQL>VARIABLE woman_num NUMBER
SQL>EXECUTE man_num:=count_num(‘m’)
SQL>EXECUYE woman_num:=count_num(‘f’)
程序中调用函数：
DECLARE
m_num NUMBER;
f_num NUMBER;
BEGIN
m_num := count_num(‘M’);
f_num := count_num(‘F’);
END;
3. 删除函数
当一个函数不再使用时，要从系统中删除它。
DROP FUNCTION count_num;
当一个函数已经过时，想要重新定义时，也不必先删除再创建，同样只需要在CREATE 语句后面加上OR REPLACE关键字：
CREATE OR REPLACE FUNCTION count_num;
3.5 程序包
程序包（package）简称包，用于将逻辑相关的PL/SQL块或元素（变量，常用，定义数据类型，异常，过程，函数，游标）等组织在一起，作为一个完整的单元存储在数据库中，用名称为来标识程序包，它具有面向对象的程序涉及语言的特点，是对PL/SQL块或元素的封装，程序包类似于面向对象中的类，其中变量相当于类的成员变量，而过程和函数就相当于类中的方法。程序包有两个独立的部分：说明部分和包体部分。这两部分独立地存储在数据字典中。说明部分是包与应用程序之间的接口，只是过程、函数、游标等名称或首部。包体部分才是这些过程、函数、游标等的具体实现。
1. 创建包
程序包由包说明和包体两部分组成，包说明部分相当于一个包的头，它对包的所有部件进行一个简单声明，这些部件可以被外界应用程序访问，其中的过程、函数、变量、常量和游标都是公共的，可在应用程序执行过程中调用。
1、包说明部分
--包说明部分创建格式如下：
CREATE PACKAGE <包名>
IS
变量、常量及数据类型定义;
游标定义头部;
函数、过程的定义和参数列表以及返回类型;
END <包名>;
--创建一个包说明部分
CREATE PACKAGE my_package
IS
man_num NUMBER; --定义了两个全局变量
woman_num NUMBER;
CURSOR teacher_cur; --定义了一个游标
CREATE FUNCTION F_count_num(in_sex in TEACHERS.SEX%TYPE)
RETURN NUMBER; --定义了一个函数
CREATE PROCEDURE P_count_num
(in_sex in TEACHERS.SEX%TYPE,out_num out NUMBER); --定义了一个过程
END my_package;
2、包体部分
包体部分是包的说明部分中的游标、函数以及过程的具体定义。其创建格式如下：
CREATE PACKAGE BODY <包名>
AS
游标、函数、过程的具体定义;
END <包名>;
例如上面说明部分的包体部分如下:
CREATE PACKAGE BODY my_package
AS
CURSOR teacher_cur IS --游标具体定义
SELECT TID,TNAME,TITLE,SEX
FROM TEACHERS
WHERE TID < 117;
FUNCTION F_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 F_count_num;
PROCEDURE P_count_num --过程具体定义
(in_sex in TEACHERS.SEX%TYPE,out_num out NUMBER)
AS
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. 调用包
包的调用方式为：
包名.变量名（常量名）
包名.游标名
包名.函数名（过程名）
例如：
SQL>VARIABLE man_num NUMBER
SQL>EXECUTE man_num := my_package.F_count_num(‘M’)
3. 删除包
删除包例如：
DROP PACKAGE my_package
当一个包已经过时，想重新定义时，也不必先删除再创建，同样只需要在CREATE语句后面加上OR REPLACE关键字即可。
CREATE OR REPLACE PACKAGE my_package
3.5 触发器
触发器是大型关系数据库都会提供的一项技术，触发器通常用来完成由数据库的完整性约束难以完成的复杂业务规则的约束，或用来监视对数据库的各种操作，实现审计的功能。
1. 触发器的基本原理
触发器类似于过程、函数，其包括声明部分，异常处理部分，并且都有名称、都被存储在数据库中。但与普通的过程函数不同的是，函数需要用户显式调用才执行，而触发器则是当某些事件发生时，系统自动执行，触发器的执行对用户来说是透明的。
1、触发器类型
DML触发器：对表或视图执行DML操作时触发
INSTEAD OF 触发器：只定义在视图上，用来替换实际的操作语句。
系统触发器：对数据库系统进行操作（如DDL语句、启动或关闭数据库等系统事件）时触发
2、相关概念
触发事件：引起触发器被触发的事件。如DML语句、DDL语句、数据库系统事件、用户事件
触发条件：触发条件是由WHEN子句指定的一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发器，使其执行触发操作，否则即便遇到触发事件也不会执行触发器
触发对象：触发对象包括表、视图、模式、数据库
触发操作：触发器所要执行的PL/SQL程序
触发时机：触发时机指定触发器的触发时间，如果指定为BEFORE则标识在执行DML操作之间触发，以便防止某些错误操作发生或实现某个业务规则；如果指定为AFTER则标识在DML操作之后触发，以便记录该操作或做某些事后处理
条件谓词：当在触发器中包含了多个触发事件（INSERT,UPDATE、DELETE）的组合时，为了分别针对不同的事件进行不同的事件进行不同的处理，需要使用条件谓词
INSERTING :当触发事件是INSERT时，取值为TRUE，否则为FALSE
UPDATING[(column_1,column_2,…,column_n)]:当触发事件是UPDATE时，如果修改了column_x列，则取值为TRUE，否则为FALSE，其中column_x是可选的。
DELETING:当触发事件是DELETE时，取值为TRUE，否则为FALSE。
触发子类型：触发子类型分别为行触发（row）和遇见触发（statement）,行触发即对每一行操作时都要触发，而语句触发只对这种操作触发一次。一般进行SQL语句操作时都因该是行触发，只有对整个表做安全检查（即防止非法操作）时才用语句触发。此外，触发器中还有俩个相关值，分别对应被触发的行中的旧表值和新表值，用old和new来表示。
2. 创建触发器
创建触发器的语句是CREATE TRIGGER，其语法格式如下：
CREATE OR REPLACE TRIGGER <触发器名>
触发条件
触发体
例如：
CREATE TRIGGER my_trigger --定义触发器
BEFORE INSERT or UPDATE of TID,TNAME on TEACHERS
FOR each row
WHERE(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 alerdy exists!’);
END my_trigger;
3. 删除触发器
删除触发器的语句为：
DROP TRIGGER my_trigger;
当一个触发器已经过时时，想重新定义时，不必先删除再创建，同样只需在CREATE语句后面加上OR REFLACE即可。
CREATE OR REPLACE TRIGGER my_trigger;
3977
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
