ORACLE PL/SQL编程--PL/SQL块结构和组成元素-优快云博客

标识符	命名规则	例子
程序变量	V_name	V_name
程序常量	C_Name	C_company_name
游标变量	Cursor_Name	Cursor_Emp
异常标识	E_name	E_too_many
表类型	Name_table_type	Emp_record_type
表	Name_table	Emp
记录类型	Name_record	Emp_record
SQL*Plus替代变量	P_name	P_sal
绑定变量	G_name	G_year_sal

2.4PL/SQL变量类型

在前面的介绍中，有系统的数据类型，也可以自定义数据类型。下表给出ORACLE类型和PL/SQL中的变量类型的合法使用列表：

2.4.1变量类型

在ORACLE8i中可以使用的变量类型有：

类型	子类	说明	范围	ORACLE限制
CHAR	Character String Rowid Nchar	定长字符串民族语言字符集	0à32767 可选,确省=1	2000
VARCHAR2	Varchar, String NVARCHAR2	可变字符串民族语言字符集	0à32767 4000	4000
BINARY_INTEGER		带符号整数,为整数计算优化性能
NUMBER(p,s)	Dec Double precision Integer Int Numeric Real Small int	小数, NUMBER的子类型高精度实数整数, NUMBER的子类型整数, NUMBER的子类型与NUMBER等价与NUMBER等价整数,比integer小
LONG		变长字符串	0->2147483647	32,767字节
DATE		日期型	公元前4712年1月1日至公元后4712年12月31日
BOOLEAN		布尔型	TRUE, FALSE,NULL	不使用
ROWID		存放数据库行号
UROWID		通用行标识符，字符类型

例1.插入一条记录并显示；

DECLARE
Row_idROWID;
info VARCHAR2 ( 40 );
BEGIN
INSERT INTO scott.dept VALUES ( 90 , ' 财务室 ' , ' 海口 ' )
RETURNINGrowid,dname || ' : ' || to_char(deptno) || ' : ' || loc
INTO row_id,info;
DBMS_OUTPUT.PUT_LINE( ' ROWID: ' || row_id);
DBMS_OUTPUT.PUT_LINE(info);
END ;

其中：

RETURNING子句用于检索INSERT语句中所影响的数据行数，当INSERT语句使用VALUES子句插入数据时，RETURNING字句还可将列表达式、ROWID和REF值返回到输出变量中。在使用RETURNING子句是应注意以下几点限制：

1．不能与DML语句和远程对象一起使用；

2．不能检索LONG类型信息；

3．当通过视图向基表中插入数据时，只能与单基表视图一起使用。

例2.修改一条记录并显示

DECLARE
Row_idROWID;
info VARCHAR2 ( 40 );
BEGIN
UPDATE dept SET deptno = 100 WHERE DNAME = ' 财务室 '
RETURNINGrowid,dname || ' : ' || to_char(deptno) || ' : ' || loc
INTO row_id,info;
DBMS_OUTPUT.PUT_LINE( ' ROWID: ' || row_id);
DBMS_OUTPUT.PUT_LINE(info);
END ;

其中：

RETURNING子句用于检索被修改行的信息。当UPDATE语句修改单行数据时，RETURNING子句可以检索被修改行的ROWID和REF值，以及行中被修改列的列表达式，并可将他们存储到PL/SQL变量或复合变量中；当UPDATE语句修改多行数据时，RETURNING子句可以将被修改行的ROWID和REF值，以及列表达式值返回到复合变量数组中。在UPDATE中使用RETURNING子句的限制与INSERT语句中对RETURNING子句的限制相同。

例3.删除一条记录并显示

DECLARE
Row_idROWID;
info VARCHAR2 ( 40 );
BEGIN
DELETE dept WHERE DNAME = ' 办公室 '
RETURNINGrowid,dname || ' : ' || to_char(deptno) || ' : ' || loc
INTO row_id,info;
DBMS_OUTPUT.PUT_LINE( ' ROWID: ' || row_id);
DBMS_OUTPUT.PUT_LINE(info);
END ;

其中：

RETURNING子句用于检索被删除行的信息：当DELETE语句删除单行数据时，RETURNING子句可以检索被删除行的ROWID和REF值，以及被删除列的列表达式，并可将他们存储到PL/SQL变量或复合变量中；当DELETE语句删除多行数据时，RETURNING子句可以将被删除行的ROWID和REF值，以及列表达式值返回到复合变量数组中。在DELETE中使用RETURNING子句的限制与INSERT语句中对RETURNING子句的限制相同。

2.4.2复合类型

ORACLE在PL/SQL中除了提供象前面介绍的各种类型外,还提供一种称为复合类型的类型---记录和表.

2.4.2.1记录类型

记录类型类似于C语言中的结构数据类型，它把逻辑相关的、分离的、基本数据类型的变量组成一个整体存储起来，它必须包括至少一个标量型或RECORD数据类型的成员，称作PL/SQL RECORD的域(FIELD)，其作用是存放互不相同但逻辑相关的信息。在使用记录数据类型变量时，需要先在声明部分先定义记录的组成、记录的变量，然后在执行部分引用该记录变量本身或其中的成员。

定义记录类型语法如下:

TYPErecord_name IS RECORD(
v1data_type1 [ NOTNULL ] [ :=default_value ] ,
v2data_type2 [ NOTNULL ] [ :=default_value ] ,
......
vndata_typen [ NOTNULL ] [ :=default_value ] );

例4：

DECLARE
TYPEtest_rec IS RECORD(
Name VARCHAR2 ( 30 ) NOT NULL : = ' 胡勇 ' ,
Info VARCHAR2 ( 100 ));
rec_booktest_rec;
BEGIN
rec_book.Name: = ' 胡勇 ' ;
rec_book.Info: = ' 谈PL/SQL编程; ' ;
DBMS_OUTPUT.PUT_LINE(rec_book.Name || ' ' || rec_book.Info);
END ;

可以用SELECT语句对记录变量进行赋值,只要保证记录字段与查询结果列表中的字段相配即可。

例5：

DECLARE
-- 定义与hr.employees表中的这几个列相同的记录数据类型
TYPERECORD_TYPE_EMPLOYEES IS RECORD(
f_namehr.employees.first_name % TYPE,
h_datehr.employees.hire_date % TYPE,
j_idhr.employees.job_id % TYPE);
-- 声明一个该记录数据类型的记录变量
v_emp_recordRECORD_TYPE_EMPLOYEES;

BEGIN
SELECT first_name,hire_date,job_id INTO v_emp_record
FROM employees
WHERE employee_id = & emp_id;

DBMS_OUTPUT.PUT_LINE( ' 雇员名称： ' || v_emp_record.f_name
|| ' 雇佣日期： ' || v_emp_record.h_date
|| ' 岗位： ' || v_emp_record.j_id);
END ;

一个记录类型的变量只能保存从数据库中查询出的一行记录，若查询出了多行记录，就会出现错误。

2.4.2.2数组类型

数据是具有相同数据类型的一组成员的集合。每个成员都有一个唯一的下标，它取决于成员在数组中的位置。在PL/SQL中，数组数据类型是VARRAY。

定义VARRY数据类型语法如下：

TYPEvarray_name IS VARRAY(size) OF element_type [ NOTNULL ] ;

varray_name是VARRAY数据类型的名称，size是下整数，表示可容纳的成员的最大数量，每个成员的数据类型是element_type。默认成员可以取空值，否则需要使用NOT NULL加以限制。对于VARRAY数据类型来说，必须经过三个步骤，分别是：定义、声明、初始化。

例6：

DECLARE
-- 定义一个最多保存5个VARCHAR(25)数据类型成员的VARRAY数据类型
TYPEreg_varray_type IS VARRAY( 5 ) OF VARCHAR ( 25 );
-- 声明一个该VARRAY数据类型的变量
v_reg_varrayREG_VARRAY_TYPE;

BEGIN
-- 用构造函数语法赋予初值
v_reg_varray: = reg_varray_type
( ' 中国 ' , ' 美国 ' , ' 英国 ' , ' 日本 ' , ' 法国 ' );

DBMS_OUTPUT.PUT_LINE( ' 地区名称： ' || v_reg_varray( 1 ) || ' 、 '
|| v_reg_varray( 2 ) || ' 、 '
|| v_reg_varray( 3 ) || ' 、 '
|| v_reg_varray( 4 ));
DBMS_OUTPUT.PUT_LINE( ' 赋予初值NULL的第5个成员的值： ' || v_reg_varray( 5 ));
-- 用构造函数语法赋予初值后就可以这样对成员赋值
v_reg_varray( 5 ): = ' 法国 ' ;
DBMS_OUTPUT.PUT_LINE( ' 第5个成员的值： ' || v_reg_varray( 5 ));
END ;

2.4.2.3使用%TYPE

定义一个变量，其数据类型与已经定义的某个数据变量(尤其是表的某一列)的数据类型相一致，这时可以使用%TYPE。

使用%TYPE特性的优点在于：

l所引用的数据库列的数据类型可以不必知道；

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

例7：

DECLARE
-- 用%TYPE类型定义与表相配的字段
TYPET_Record IS RECORD(
T_noemp.empno % TYPE,
T_nameemp.ename % TYPE,
T_salemp.sal % TYPE);
-- 声明接收数据的变量
v_empT_Record;
BEGIN
SELECT empno,ename,sal INTO v_emp FROM emp WHERE empno = 7788 ;
DBMS_OUTPUT.PUT_LINE
(TO_CHAR(v_emp.t_no) || ' ' || v_emp.t_name || ' ' || TO_CHAR(v_emp.t_sal));
END ;

例8：

DECLARE
v_empnoemp.empno % TYPE: =& no;
Typet_record is record(
v_nameemp.ename % TYPE,
v_salemp.sal % TYPE,
v_dateemp.hiredate % TYPE);
Rect_record;
BEGIN
SELECT ename,sal,hiredate INTO Rec FROM emp WHERE empno = v_empno;
DBMS_OUTPUT.PUT_LINE(Rec.v_name || ' --- ' || Rec.v_sal || ' -- ' || Rec.v_date);
END ;

2.4.3使用%ROWTYPE

PL/SQL提供%ROWTYPE操作符,返回一个记录类型,其数据类型和数据库表的数据结构相一致。

使用%ROWTYPE特性的优点在于：

l所引用的数据库中列的个数和数据类型可以不必知道；

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

例9：

DECLARE
v_empnoemp.empno % TYPE: =& no;
recemp % ROWTYPE;
BEGIN
SELECT * INTO rec FROM emp WHERE empno = v_empno;
DBMS_OUTPUT.PUT_LINE( ' 姓名: ' || rec.ename || ' 工资: ' || rec.sal || ' 工作时间: ' || rec.hiredate);
END ;

2.4.4LOB类型

ORACLE提供了LOB (Large OBject)类型，用于存储大的数据对象的类型。ORACLE目前主要支持BFILE, BLOB, CLOB及NCLOB类型。

BFILE (Movie)

存放大的二进制数据对象，这些数据文件不放在数据库里，而是放在操作系统的某个目录里，数据库的表里只存放文件的目录。

BLOB(Photo)

存储大的二进制数据类型。变量存储大的二进制对象的位置。大二进制对象的大小<=4GB。

CLOB(Book)

存储大的字符数据类型。每个变量存储大字符对象的位置，该位置指到大字符数据块。大字符对象的大小<=4GB。

NCLOB

存储大的NCHAR字符数据类型。每个变量存储大字符对象的位置，该位置指到大字符数据块。大字符对象的大小<=4GB。

2.4.5BIND变量

绑定变量是在主机环境中定义的变量。在PL/SQL程序中可以使用绑定变量作为他们将要使用的其它变量。为了在PL/SQL环境中声明绑定变量，使用命令VARIABLE。例如：

VARIABLEreturn_code NUMBER
VARIABLEreturn_msg VARCHAR2 ( 20 )

可以通过SQL*Plus命令中的PRINT显示绑定变量的值。例如：

PRINT return_code
PRINT return_msg

例10：

VARIABLEresult NUMBER ;
BEGIN
SELECT (sal * 10 ) + nvl(comm, 0 ) INTO :result FROM emp
WHERE empno = 7844 ;
END ;
-- 然后再执行
PRINT result

2.4.6PL/SQL表(TABLE)

定义记录表（或索引表）数据类型。它与记录类型相似，但它是对记录类型的扩展。它可以处理多行记录，类似于高级中的二维数组，使得可以在PL/SQL中模仿数据库中的表。

定义记录表类型的语法如下：

TYPEtable_name IS TABLE OF element_type [ NOTNULL ]
INDEX BY [ BINARY_INTEGER|PLS_INTEGER|VARRAY2 ] ;

关键字INDEX BY表示创建一个主键索引，以便引用记录表变量中的特定行。

方法	描述
EXISTS(n)	如果集合的第n个成员存在，则返回true
COUNT	返回已经分配了存储空间即赋值了的成员数量
FIRST LAST	FIRST：返回成员的最低下标值 LAST：返回成员的最高下标值
PRIOR(n)	返回下标为n的成员的前一个成员的下标。如果没有则返回NULL
NEXT(N)	返回下标为n的成员的后一个成员的下标。如果没有则返回NULL
TRIM	TRIM：删除末尾一个成员 TRIM(n)：删除末尾n个成员
DELETE	DELETE：删除所有成员 DELETE(n)：删除第n个成员 DELETE(m, n)：删除从n到m的成员
EXTEND	EXTEND：添加一个null成员 EXTEND(n)：添加n个null成员 EXTEND(n,i)：添加n个成员，其值与第i个成员相同
LIMIT	返回在varray类型变量中出现的最高下标值

例11：

DECLARE
TYPEdept_table_type IS TABLE OF
dept % ROWTYPE INDEX BY BINARY_INTEGER;
my_dname_tabledept_table_type;
v_count number ( 2 ): = 4 ;
BEGIN
FOR int IN 1 ..v_countLOOP
SELECT * INTO my_dname_table( int ) FROM dept WHERE deptno = int * 10 ;
END LOOP;
FOR int IN my_dname_table.FIRST..my_dname_table.LASTLOOP
DBMS_OUTPUT.PUT_LINE( ' Departmentnumber: ' || my_dname_table( int ).deptno);
DBMS_OUTPUT.PUT_LINE( ' Departmentname: ' || my_dname_table( int ).dname);
END LOOP;
END ;

例12：按一维数组使用记录表

DECLARE
-- 定义记录表数据类型
TYPEreg_table_type IS TABLE OF varchar2 ( 25 )
INDEX BY BINARY_INTEGER;
-- 声明记录表数据类型的变量
v_reg_tableREG_TABLE_TYPE;

BEGIN
v_reg_table( 1 ): = ' Europe ' ;
v_reg_table( 2 ): = ' Americas ' ;
v_reg_table( 3 ): = ' Asia ' ;
v_reg_table( 4 ): = ' MiddleEastandAfrica ' ;
v_reg_table( 5 ): = ' NULL ' ;

DBMS_OUTPUT.PUT_LINE( ' 地区名称： ' || v_reg_table( 1 ) || ' 、 '
|| v_reg_table( 2 ) || ' 、 '
|| v_reg_table( 3 ) || ' 、 '
|| v_reg_table( 4 ));
DBMS_OUTPUT.PUT_LINE( ' 第5个成员的值： ' || v_reg_table( 5 ));
END ;

例13：按二维数组使用记录表

DECLARE
-- 定义记录表数据类型
TYPEemp_table_type IS TABLE OF employees % ROWTYPE
INDEX BY BINARY_INTEGER;
-- 声明记录表数据类型的变量
v_emp_tableEMP_TABLE_TYPE;
BEGIN
SELECT first_name,hire_date,job_id INTO
v_emp_table( 1 ).first_name,v_emp_table( 1 ).hire_date,v_emp_table( 1 ).job_id
FROM employees WHERE employee_id = 177 ;
SELECT first_name,hire_date,job_id INTO
v_emp_table( 2 ).first_name,v_emp_table( 2 ).hire_date,v_emp_table( 2 ).job_id
FROM employees WHERE employee_id = 178 ;

DBMS_OUTPUT.PUT_LINE( ' 177雇员名称： ' || v_emp_table( 1 ).first_name
|| ' 雇佣日期： ' || v_emp_table( 1 ).hire_date
|| ' 岗位： ' || v_emp_table( 1 ).job_id);
DBMS_OUTPUT.PUT_LINE( ' 178雇员名称： ' || v_emp_table( 2 ).first_name
|| ' 雇佣日期： ' || v_emp_table( 2 ).hire_date
|| ' 岗位： ' || v_emp_table( 2 ).job_id);
END ;

2.5运算符和表达式(数据定义)

2.5.1关系运算符

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

2.5.2一般运算符

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

2.5.3逻辑运算符

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

2.6变量赋值

在PL/SQL编程中，变量赋值是一个值得注意的地方，它的语法如下：

variable:= expression ;

variable是一个PL/SQL变量, expression是一个PL/SQL表达式.

2.6.1字符及数字运算特点

空值加数字仍是空值：NULL + <数字> = NULL

空值加（连接）字符，结果为字符：NULL || <字符串> = <字符串>

2.6.2BOOLEAN赋值

布尔值只有TRUE, FALSE及NULL三个值。如：

DECLARE
bDoneBOOLEAN;
BEGIN
bDone: = FALSE;
WHILE NOT bDoneLOOP
Null ;
END LOOP;
END ;

2.6.3数据库赋值

数据库赋值是通过SELECT语句来完成的，每次执行SELECT语句就赋值一次，一般要求被赋值的变量与SELECT中的列名要一一对应。如：

例14：

DECLARE
emp_idemp.empno % TYPE: = 7788 ;
emp_nameemp.ename % TYPE;
wagesemp.sal % TYPE;
BEGIN
SELECT ename,NVL(sal, 0 ) + NVL(comm, 0 ) INTO emp_name,wages
FROM emp WHERE empno = emp_id;
DBMS_OUTPUT.PUT_LINE(emp_name || ' ---- ' || to_char(wages));
END ;

提示：不能将SELECT语句中的列赋值给布尔变量。

2.6.4可转换的类型赋值

lCHAR转换为NUMBER：

使用TO_NUMBER函数来完成字符到数字的转换，如：

v_total := TO_NUMBER('100.0') + sal;

lNUMBER转换为CHAR：

使用TO_CHAR函数可以实现数字到字符的转换，如：

v_comm := TO_CHAR('123.45') ||'元';

l字符转换为日期：

使用TO_DATE函数可以实现字符到日期的转换，如：

v_date := TO_DATE('2001.07.03','yyyy.mm.dd');

l日期转换为字符

使用TO_CHAR函数可以实现日期到字符的转换，如：

v_to_day := TO_CHAR(SYSDATE,'yyyy.mm.dd hh24:mi:ss') ;

2.7变量作用范围及可见性

在PL/SQL编程中，如果在变量的定义上没有做到统一的话，可能会隐藏一些危险的错误，这样的原因主要是变量的作用范围所致。变量的作用域是指变量的有效作用范围，与其它高级语言类似，PL/SQL的变量作用范围特点是：

l变量的作用范围是在你所引用的程序单元（块、子程序、包）内。即从声明变量开始到该块的结束。

l一个变量（标识）只能在你所引用的块内是可见的。

l当一个变量超出了作用范围，PL/SQL引擎就释放用来存放该变量的空间（因为它可能不用了）。

l在子块中重新定义该变量后，它的作用仅在该块内。

例15：

DECLARE
Emess char ( 80 );
BEGIN

DECLARE
V1 NUMBER ( 4 );
BEGIN
SELECT empno INTO v1 FROM emp WHERE LOWER (job) = ' president ' ;
DBMS_OUTPUT.PUT_LINE(V1);
EXCEPTION
When TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE( ' Morethanonepresident ' );
END ;

DECLARE
V1 NUMBER ( 4 );
BEGIN
SELECT empno INTO v1 FROM emp WHERE LOWER (job) = ' manager ' ;
EXCEPTION
When TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE( ' Morethanonemanager ' );
END ;

EXCEPTION
When others THEN
Emess: = substr(SQLERRM, 1 , 80 );
DBMS_OUTPUT.PUT_LINE(emess);
END ;

2.8注释

在PL/SQL里，可以使用两种符号来写注释，即：

l使用双‘-‘ (减号)加注释

PL/SQL允许用–来写注释，它的作用范围是只能在一行有效。如：

V_SalNUMBER(12,2);--人员的工资变量。

l使用/**/来加一行或多行注释，如：

/***********************************************/

/*文件名：department_salary.sql*/

/*作者：EricHu*/

/*时间：2011-5-9*/

/***********************************************/

提示：被解释后存放在数据库中的PL/SQL程序，一般系统自动将程序头部的注释去掉。只有在PROCEDURE之后的注释才被保留；另外程序中的空行也自动被去掉。

2.9简单例子

2.9.1简单数据插入例子

例16：

/* ********************************************* */
/* 文件名：test.sql */
/* 说明:
一个简单的插入测试，无实际应用。 */
/* 作者：EricHu */
/* 时间：2011-5-9 */
/* ********************************************* */
DECLARE
v_ename VARCHAR2 ( 20 ): = ' Bill ' ;
v_sal NUMBER ( 7 , 2 ): = 1234.56 ;
v_deptno NUMBER ( 2 ): = 10 ;
v_empno NUMBER ( 4 ): = 8888 ;
BEGIN
INSERT INTO emp(empno,ename,JOB,sal,deptno,hiredate)
VALUES (v_empno,v_ename, ' Manager ' ,v_sal,v_deptno,
TO_DATE( ' 1954.06.09 ' , ' yyyy.mm.dd ' ));
COMMIT ;
END ;

2.9.2简单数据删除例子

例17：

/* ********************************************* */
/* 文件名：test_deletedata.sql */
/* 说明:
简单的删除例子，不是实际应用。 */
/* 作者：EricHu */
/* 时间：2011-5-9 */
/* ********************************************* */
DECLARE
v_ename VARCHAR2 ( 20 ): = ' Bill ' ;
v_sal NUMBER ( 7 , 2 ): = 1234.56 ;
v_deptno NUMBER ( 2 ): = 10 ;
v_empno NUMBER ( 4 ): = 8888 ;
BEGIN
INSERT INTO emp(empno,ename,JOB,sal,deptno,hiredate)
VALUES (v_empno,v_ename,‘Manager’,v_sal,v_deptno,
TO_DATE(’ 1954.06 . 09 ’,’yyyy.mm.dd’));
COMMIT ;
END ;
DECLARE
v_empno number ( 4 ): = 8888 ;
BEGIN
DELETE FROM emp WHERE empno = v_empno;
COMMIT ;
END ;