PL/SQL语言以及编程

Oracle PL/SQL语言以及编程

SQL语言只是访问、操作数据库的语言，而并不是一种程序设计语言，因此不能用于程序开发。PL/SQL（Procedual

Language/SQL）是Oracle在标准SQL语言上进行过程性扩展后形成的程序设计语言，是一种Oracle数据库特有的，支持应

用开发的语言。

PL/SQL简介

PL/SQL是深入掌握和应用Oracle数据库的基础，它在Oracle数据库应用系统开发有重要作用。在准许运行Oracle的任何操作

系统平台上均可运行PL/SQL程序。

PL/SQL的基本结构

和所有过程化语言一样，PL/SQL也是一种莫块式结构的语言，其大体结构如下:

DECLARE

--声明一些变量、常量、用户定义的数据类型以及游标等

--这一部分可选，如不需要可以不写

BEGIN

--主程序体，在这里可以加入各种合法语句

EXCEPTION

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

END;

从中可以看出它包含3个基本部分：

声明部分（declarative section）

执行部分（executable section）

异常处理部分（exception section）

其中，只有执行部分是必须的，其他俩部分都是可选的，需要强调的是，该结构最后的分号是必须的。

PL/SQL注释

注释（comment）增强了程序的可读性。

1、单行注释

单行注释由俩个连字符开始，一直到行尾

DECLARE

V_Department CHAR(3) --保存3个字符的变量

……

END;

2、多行注释

多行注释有/*开头，有*/结尾。

PL/SQL字符集

合法字符集

一个PL/SQL程序是由一系列语句组成的，而每个语句又是由一行或者多行文本组成的。我们可以明确使用的字符取决于我们所使用的数据库字符集。PL/SQL中每一个关键字、操作符和记号都是由这个字符集中的若干字符组成的

类型	字符
字母	A～Z、a～z
数字	0～9
符号	~！@#$%*()_-+=\|:;”’<>,.?/^
空格	Tab、空格、换行、回车

PL/SQL分界符

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

                1）运算符   + - * / **（指数操作符）
                2）关系   =（相当于JAVA中的==） > < <> != ~= ^= <= >=
                3）赋值   := 例子 a:=2
                4）联接     || 例：'abc' || 123
                5）标号     << 需要的标记 >>
                6）注释      --（单行）   /*   */（段落）
                7）替代     <scape> <tab> <enter>

PL/SQL数据类型

PL/SQL定义的数据类型很多，在这里列出最经常使用的数据类型

数字类型

数字类型变量存储整数或者实数。包含NUMBER,PLS_INTEGER和BINARY_INTEGER 三种基本类型。

其中，NUMBER类型的变量可以存储整数或浮点数，而BINARY_INTEGER或PLS_INTEGER类型的变量值存储整数。

NUMBER(P,S)是一种格式化的数字，其中P是精度，S是刻度范围。

精度数数值中所有效数字的个数。

刻度是小数点右边数字位的个数。

“子类型”subtype是类型的一个候选名，它是可选的，可以使用它来限制子类型变量的合法取值，有多种与NUMBER等

价的子类型，实际上，它们是重名的NUMBER数据类型。处于可读性的考虑和其他数据库的数据类型兼容会使用这些侯

选名，这些等价的类型包括

DEC,DECIAML,DOUBLE，PRECISION.INTEGER,INT,NUMERIC,REAL,SMALLINT.BINARY_INTEGER,PLS_INTEGER

字符类型

字符类型变量用来存储字符串或者字符数据，其类型包括 VARCHAR2、CHAR、LONG、NCHAR和NVARCHAR2

VARCHAR2l类型和数据库类型中VARCHAR2类似，可以存储变长字符串，声明语法为

VARCHAR2(MAXLength); //必须在定义中给出，系统默认为最大值 32767字节。

//注：数据库类型VARCHAR2的最大长度是4000字节。

CHAR类型标识定长字符串。声明语法为：

CHAR(MaxLength); //MaxLength最大为32767字节，默认为1.

LONG类型变量是一个可变的字符串，最大长度是32767字节，LONG变量与VARCHAR2变量类似。数据库类型的LONG

长度最大可达2GB。

日期类型

日期类型中只有一种类型—DATE，用来存储日期和时                1）运算符   + - * / **（指数操作符）
                2）关系   =（相当于JAVA中的==） > < <> != ~= ^= <= >=
                3）赋值   := 例子 a:=2
                4）联接     || 例：'abc' || 123
                5）标号     << 需要的标记 >>
                6）注释      --（单行）   /*   */（段落）
                7）替代     <scape> <tab> <enter>纪、年、月、天、小时、分钟、和秒。

DATE变量的存储空间是7个字节，每个部分占用一个字节。

布尔类型

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

Type定义的数据类型

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的记录变量。

Ateacher teacher_record;

引用这个记录变量时要指明内部变量，如ateacher.TID等

PL/SQL还提供了％TYPE和％ROWTYPE俩种特殊的变量，用于声明与表的列相匹配的变量和用户定义数据类型，前一个

表示单属性的数据类型，后一个表示整个属性列表的结构，即元组的类型。

PL/SQL变量和常量

定义常量

定义常量的语句格式如下：

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

定义变量

定义变量的语句格式如下：

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

变量初始化

为初始化的变量包含的是个未知的随机数据，因此定义一个变量时要初始化，被赋值为NULL.

PL/SQL语句控制结构

结构控制语句是所有过程性程序语言的关键，因为只有能够进行结构控制才能灵活实现各种操作和功能。

PL/SQL控制语句列表

序号	控制语句	意义说明
01	If….then	判断if正确则执行then
02	If…..then…else	判断if正确则执行then，否则执行else
03	If…..then…elseif	嵌套式判断
04	Case	有逻辑地从数值中做出选择
05	Loop….exit…..end	循环控制，用判断语句执行exit
06	Loop….exit when…end	循环控制，用判断语句执行exit
07	While…loop….end	当while为真时循环
08	For….in…loop…end	已知循环次数的循环
09	Goto	无条件转向控制

选择结构

、IF语句

选择结构的语法和高级语言的IF….THEN…..ELSE很类似，命令格式如下：

IF {条件表达式1} THEN

{语句序列1;}

[ELSEIF(条件表达式2)] THEN

{语句序列2;}

[ELSE

{语句序列3;}]

END IF;

CASE语句

CASE语句的命令格式如下：

CASE检测表达式

WHEN 表达式1 THEN 语句序列1

WHEN 表达式2 THEN 语句序列2

…….

[ELSE

其他语句序列 ]

END;

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

错误CASE_NOT_FOUND.

循环结构

所谓循环结构，即指程序按照指定的逻辑条件循环执行一组命令的结构

、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;

、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; --循环尾

、WHILE…..LOOP……END语句

该语句也是控制循环，不过是先判断再进入循环。

例如：

Control_var:=0;

WHILE control_var <= 5 LOOP

Control_var := control_var + 1;

END LOOP;

、FOR…..IN……LOOP…..END语句

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

例如：

FOR control in 0…5 LOOP --control_var从0到5进行循环

NULL; --因为for语句自动给control_var加1，故是一个空操作。

END LOOP;

、GOTO语句

无条件转向语句

GOTO语句的语法是：

GOTO label;

例如：

….. --程序其他部分

<<>goto_mark>

….. --程序其他部分

IF no > 98050 THEN

GOTO goto_mark;

…… --程序其他部分

PL/SQL表达式

表达式不能独立构成语句，表达式的结果是一个值，如果不给这个值安排一个存放的位置，表达式本身豪无意义。通常

表达式作为赋值语句的一部分出现在赋值运算符的右边，或者作为函数的参数等。操作数是运算符的参数，根据所拥有的

参数个数，PL/SQL运算符可以分为一元元素符（一个参数）和二元运算符（俩个参数），表达式按照操作对象的不同，也

可以分为字符表达式和布尔表达式俩种。

、字符表达式

唯一的字符运算符就是并置运算符“||”，它的作用是把几个字符串连在一起，如表达式“hello”|| “world”的值等于

‘hello world’

、布尔表达式

PL/SQL控制结构都涉及布尔表达式。布尔表达式是一个判断为真还是为假的条件，他的值只有TRUE、FALSE、或NULL.

布尔表达式有3个布尔运算符：AND、OR和NOT与高级语言中的逻辑运算符一样，它们的操作对象是布尔变量或表达

式。

布尔表达式中的算术运算符

操作符	意义	操作符	意义
=	等于	!=	不等于
<	小于	>	大于
<=	小于等于	>=	大于等于

PL/SQL的游标

基本原来

在PL/SQL块中SELECT、INSERT、UPDATE和DELETE语句时，Oracle会在内存中为其分配上下文区（Context Area）即一

个缓冲区。游标是指向该区的一个指针，或是命名一个工作区（Work Area）或是一种结构化数据类型。它为应用程序提供了一

种对具有多行数据查询结果集中的每一行数据分别进行单独处理方法，是涉及嵌入式SQL语句的应用程序的常用编程方式。

游标分为显式游标和隐式游标俩种。

显式游标是由用户声明和操作的一种游标。

隐式游标是由数据库为所有数据操作语句自动声明和操作的一种游标。

显式游标

显式游标的处理包括声明游标，打开游标，提取游标，关闭游标4个步骤。

声明游标

打开游标

提取游标

空？

关闭游标

是

否

声明游标

对游标的声明定义了游标的名字并将该游标和一个SELECT语句相关联。显式游标声明部分在DECLARE中，语法为：

CURSOR <游标名> IS SELECT <语句>;

其中,<游标名>是游标的名字，SELECT<语句>是将要处理的查询。

例如：

DECLARE

Teacher_id NUMBER(5);

Teacher_name VARCHAR2(50);

Teacher_title VARCHAR2(50);

Teacher_sex CHAR(1);

CURSOR teacher_cur IS

SELECT TID,TNAME,TITLE,SEX

FROM teachers

WHERE TID<117;

打开游标

打开游标的语法是：

OPEN <游标名>;

其中，<游标名>标识了一个已经被声明的游标。打开游标就是执行定义的SELECT语句，执行完毕，查询结果装入内存，游标

在查询结果的首部，注意并不是第一行。当打开一个游标时，会完成以下几件事前。

、检查联变量的取值

、根据联编变量的取值，确定活动集

、活动集的指针指向第一行

打开一个已经被打开的游标是合法的，在第二次执行OPEN语句以前，PL/SQL将在重新打开该游标之前隐式的执行一条

CLOSE语句。

提取游标

打开游标后的工作就是取值了，取值语句是FETCH，它的用法有俩种形式，如下所示：

FETCH <游标名> INTO <变量列表>;

或

FETCH <游标名> INTO PL/SQL记录;

例如：

DECLARE

Teacher_id NUMBER(5);

Teacher_name VARCHAR2(50);

Teacher_title VARCHAR2(50);

Teacher_sex CHAR(1);

CURSOR teacher_cur IS

SELECT TID,TNAME,TITLE,SEX

FROM teachers

WHERE TID<117;

BEGIN

OPEN teacher_cur;

FETCH teacher_cur INTO teacher_id,teacher_name,teacher_title,teacher_sex;

FETCHu语句每执行一次，游标向后移动一行，直到结束（游标只能逐个向后移动，而不能跳跃移动或向前移动）

关闭游标

当所有的活动集都被检索以后，游标就应该被关闭。游标关闭有与游标相关联的资源可以被释放了。

DECLARE

Teacher_id NUMBER(5);

Teacher_name VARCHAR2(50);

Teacher_title VARCHAR2(50);

Teacher_sex CHAR(1);

CURSOR teacher_cur IS

SELECT TID,TNAME,TITLE,SEX

FROM teachers

WHERE TID<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属性检查释放返回成功，即是否还有要操作的行数。

、将游标中行取至变量组中时，对应变量个数和数据类型必须完全一致。

、使用完游标必须将其关闭，以释放对应内存资源。

2、隐式游标

如果在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语句必须只选中一行数据或只产生一行数据。

3、游标属性

无论是显式游标还是隐式游标，均有％ISOPEN,%FOUND,%NOTFOUND和%ROWCOUNT四种属性，它们描述与游标作

相关的DML语句的执行情况，游标属性只能用在PL/SQL的流程控制语句内，而不能用在SQL语句。

是否找到游标（％FOUND）

该属性表示当前游标是否指向有效一行，若是则为TRUE，否则为FALSE。检查此属性可以判断是否结束游标使用。

是否没找到游标（％NOTFOUND）

该属性与%FOUND属性相类似，但其值正好相反

游标行数（％ROWCOUNT）

该属性记录了游标抽取过的记录行数，也可以理解为当前游标所在的行号。这个属性在循环判断中也很有效，使得不

必抽取所有记录行就可以中断游标操作。

游标是否打开(％ISOPEN)

该属性表示游标是否处于打开状态，在世界应用中，使用一个游标前，第一步往往是检查它的%ISOPEN属性。看其是

否已经打开，若没有。要打开游标再向下操作。

参数化游标

在定义游标时，可以带上参数，使得在使用游标时，根据参数不同所选择的数据行也不同，达到使用的目的。

例如：

ACCEPT my_tid prompt ‘Please input the tid:’

DECLARE

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;

游标变量

如同常量和变量的区别一样，前面所讲的游标都是与一个SQL语句相关联，并且编译该块的时候此语句已经是可知的，

是静态的，而游标变量可以在运行时与不同的语句关联，是动态的。

游标变量被用于处理多行的查询结果集，在同一个PL/SQL块中，游标变量不同于特定的查询绑定，而是在打开游标时

才确定所对应的查询。

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

级语言中的指针。

声明游标变量

游标变量是一种引用类型。如果要使用引用类型，首先要声明该变量，然后相应的存储单元必须要被分配。

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;

打开游标变量

如果要将一个游标变量与一个特定的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;

关闭游标变量

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

变量是非法的。

过程

Oracle准许在数据库的内部创建并存储编译的PL/SQL程序，以便随时调出使用，该类程序包括过程，函数，包和触发器。

创建过程

过程用来完成一系列的操作，它的创建语法如下:

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) --输入参数

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;

调用过程

调用过程的命令是EXECUTE,如上述创建过程count_num来查看男女教师的数量。

EXECUTE count_num(‘M’);

EXECUTE count_num(‘F’);

删除过程

当一个过程不再需要时，要将此过程从内存中删除，以释放相应的内存空间，可以使用下面的语句：

DROP PROCEDURE count_num;

当一个过程已经过时，想重新定义时，不必先删除再创建，而只需要在CREATE语句后面加上OR REPLACE关键字

即可。如：

CREATE OR REPLACE PROCEDURE count_num

过程的参数类型及传递

过程的参数有3种类型，分别如下：

in参数类型

这是个输入类型的参数，表示这个参数值输入给过程，供过过程使用。

例如：

In_num参数实例。下面一个过程将in_num参数作为输入，out_num作为输出。

CREATE OR REPLACE PROCEDURE double(

In_num in NUMBER,

Out_num out NUMBER

)

BEGIN

Out_num:=in_num*2;

END double;

Out参数类型

这是个输出类型的参数，表示这个参数在过程中被赋值，可以传给过程以外的部分或环境。

In out参数类型

这种类型的参数其实是综合了上述俩种参数类型，既向过程传值，在过程中也被赋值而传向过程体外。

函数

函数一般用于计算和返回一个值，可以将经常需要进行的计算写成函数，函数的调用是表达式的一部分，而过程的调用是

一条PL/SQL语句。

函数与过程在创建的形式上有些相似，也是编译后放在内存中供用户使用，只不过调用时函数要用表达式，而不像过程只

需要调用过程名，另外函数必须有一个返回值，而过程则没有。

创建函数

创建函数的语法格式如下：

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

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;

函数调用

调用函数时可以用全局变量接收其返回值

如：

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;

删除函数

当一个函数不再使用时，要从系统中删除它。

DROP FUNCTION count_num;

当一个函数已经过时，想要重新定义时，也不必先删除再创建，用羊只需要在CREATE 语句后面加上OR REPLACE关键

字：

CREATE OR REPLACE FUNCTION count_num;

程序包

程序包（package）简称包，用于将逻辑相关的PL/SQL块或元素（变量，常用，定义数据类型，异常，过程，函数，游标）

等组织在一起，作为一个完整的单元存储在数据库中，用名称为来标识程序包，它具有面向对象的程序涉及语言的特点，

是对PL/SQL块或元素的封装，程序包类似于面向对象中的类，其中变量相当于类的成员变量，而过程和函数就相当于类

中的方法。

程序包有俩个独立的部分：说明部分和包体部分。这俩部分独立地存储在数据字典中。

说明部分是包与应用程序之间的接口，只是过程、函数、游标等名称或首部。

包体部分才是这些过程、函数、游标等的具体实现。包体部分在开始构建应用程序框架时不可暂不需要。

一般而言可以先独立地进行过程和函数的编写，当其比较为完善后，再逐步地将其按照逻辑相关性进行打包。

创建包

程序包由包说明和包体俩部分组成，包说明部分相当于一个包的头，它对包的所有部件进行一个简单声明，这些部件可以

被外界应用程序访问，其中的过程、函数、变量、常量和游标都是公共的，可在应用程序执行过程中调用。

包说明部分

包说明部分创建格式如下：

CREATE PACKAGE <包名>

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

游标定义头部;

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

END <包名>;

例如：

CREATE PACKAGE my_package

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;

包体部分

包体部分是包的说明部分中的游标、函数以及过程的具体定义。其创建格式如下：

CREATE PACKAGE BODY <包名>

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

END <包名>;

例如上面说明部分的包体部分如下:

CREATE PACKAGE BODY my_package

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

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)

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;

调用包

包的调用方式为：

包名.变量名（常量名）

包名.游标名

包名.函数名（过程名）

例如：

SQL>VARIABLE man_num NUMBER

SQL>EXECUTE man_num := my_package.F_count_num(‘M’)

删除包

删除包例如：

DROP PACKAGE my_package

当一个包已经过时，想重新定义时，也不必先删除再创建，同样只需要在CREATE语句后面加上OR REPLACE关键字

即可。

CREATE OR REPLACE PACKAGE my_package

触发器

触发器是大型关系数据库都会提供的一项技术，触发器通常用来完成由数据库的完整性约束难以完成的复杂业务规则的约

束，或用来监视对数据库的各种操作，实现审计的功能。

触发器的基本原理

触发器类似于过程、函数，其包括声明部分，异常处理部分，并且都有名称、都被存储在数据库中。但与普通的过程

函数不同的是，函数需要用户显式调用才执行，而触发器则是当某些事件发生时，系统自动执行，触发器的执行对用

户来说是透明的、

、触发器类型

、DML触发器：对表或视图执行DML操作时触发

、INSTEAD OF 触发器：只定义在视图上，用来替换实际的操作语句。

、系统触发器：对数据库系统进行操作（如DDL语句、启动或关闭数据库等系统事件）时触发

、相关概念

、触发事件

引起触发器被触发的事件。如DML语句、DDL语句、数据库系统事件、用户事件

、触发条件

触发条件是由WHEN子句指定的一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执

行触发器，使其执行触发操作，否则即便遇到触发事件也不会执行触发器。

、触发对象

触发对象包括表、视图、模式、数据库。

、触发操作

触发器所要执行的PL/SQL程序

、触发时机

触发时机指定触发器的触发时间，如果指定为BEFORE则标识在执行DML操作之间触发，以便防止某些错误操

作发生或实现某个业务规则；如果指定为AFTER则标识在DML操作之后触发，以便记录该操作或做某些事后

处理。

、条件谓词

当在触发器中包含了多个触发事件（INSERT,UPDATE、DELETE）的组合时，为了分别针对不同的事件进行不

同的事件进行不同的处理，需要使用条件谓词

INSERTING :当触发事件是INSERT时，取值为TRUE，否则为FALSE

UPDATING[(column_1,column_2,……….)]:当触发事件是UPDATE时，如果修改了column_x列，则取值为TRUE

否则为FALSE，其中column_x是可选的。

DELETING:当触发事件是DELETE时，取值为TRUE，否则为FALSE。

、触发子类型

触发子类型分别为行触发（row）和遇见触发（statement）,行触发即对每一行操作时都要触发，而语句触发只对

这种操作触发一次。一般进行SQL语句操作时都因该是行触发，只有对整个表做安全检查（即防止非法操作）

时才用语句触发。

此外，触发器中还有俩个相关值，分别对应被触发的行中的旧表值和新表值，用old和new来表示。

创建触发器

创建触发器的语句是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;

删除触发器

删除触发器的语句为：

DROP TRIGGER my_trigger;

当一个触发器已经过时时，想重新定义时，不必先删除再创建，同样只需在CREATE语句后面加上OR REFLACE即可。

CREATE OR REPLACE TRIGGER my_trigger;

posted @ 2012-08-18 15:27 Xiao方丈阅读(421) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Xiao方丈

PL/SQL语言以及编程

公告