ORACLE入门
－PL/SQL语言篇
技术支持部 汤庆锋
福州磬基电子有限公司
本课程学习内容
PL/SQL简介
PL/SQL数据类型(ORACLE的数据类型)
ORACL内置的SQL函数
PL/SQL中使用SQL
PL/SQL中游标的使用
动态PL/SQL
PL/SQL的异常处理
PL/SQL简介
PL/SQL(Procedural Language/SQL)即模块化的程序设计语言，用于从各
种环境中访问ORACLE数据库。它具备了许多SQL中所没有的过程化属性方面
的特点。主要包括：
变量和类型
控制结构（条件语句、循环语句…)
过程、函数
游标
异常处理
PL/SQL程序的用途
无名块
就是没有命名的PL/SQL块，它可以嵌入某一个应用之中.
存储过程、函数
也就是命名了的PL/SQL块，它可以接收参数，并且可以重复的被调用。
触发器
是与数据库中的表相关的PL/SQL块，可以自动的触发。
包
命名了的PL/SQL块，由一组相关的过程、函数和标识符组成。
PL/SQL的程序结构
PL/SQL的基本单位是“块”(Block)。所有的PL/SQL程序都是由一个或
多
个PL/SQL块构成的，这些块可以相互进行嵌套。通常一个块完成程序的一个
单元的工作。一个基本的块由三个部分组成：
定义部分
定义变量、常量、游标、异常处理
可执行部分
包括对数据库进行操作的SQL语句，以及
对块中的语句进行组织、控制的PL/SQL语句。
异常处理（Exception) 部分
可执行部分中的语句，在执行过程中
出错或出现非正常现象时，所做的响应
处理
DECLARE
BEGIN
EXCEPTION
END
PL/SQL块结构
PL/SQL数据类型
PL/SQL数据类型
常用的数据类型
CHAR： 存放固定长度的字符串
VARCHAR2：存放可变长度的字符串
NUMBER： 存放0、正负数、浮点数
DATE： 存放时间数据（包括日期和时间）
LONG： 存放变长字符串。一般用来存储大文本
RAW LONG 存放多媒体数据,如声音、图片
例如：创建一雇员表
CREATE TABLE emp
(
empno number(4),
ename varchar2(10),
hiredate date,
sal number(7,2),
deptno number(2)
);
ORACLE内置的SQL函数
SQL函数按照传入参数的类型，可分为字符串函数、数值函数、日期函数、
其他函数。以下分别列举较常用的部分进行说明。
字符串函数：
UPPER(s)
将字符串‘s’转换成大写的形式返回。
LOWER(s)
将字符串‘s’转换成小写的形式返回。
SUBSTR(s,a [,b])
返回从字符位置a开始有b个字符长的‘s’的一部分。
• 若a为正数:从左边向右边计算
• 若a为负数:从右边向左边计算
实例：
Select substr(‘abcdefg123’,4) from dual; 结果返回：‘defg123’
Select substr(‘abcdefg123’,4,2) from dual; 结果返回：‘de’
Select substr(‘abcdefg123’,-4,2) from dual; 结果返回：‘g1’
RTRIM(s1,s2)
返回删除从最右边算起出现在s2中的字符的s1。s2缺省为空格
实例：Select rtrim(‘aabbccdd’,’cd’) from dual; 结果返回：‘aabb’
Select rtrim(‘aabbccdd’,’dc’) from dual; 结果返回：‘aabb’
ORACL内置的SQL函数
Concat(s1,s2)
返回串接上s2之后的s1.该函数与||运算符作用相同。
实例：select concat(‘abc’,’def’) from dual; 返回结果：
‘abcdef’
select ‘abc’||’def’ from dual; 返回结果：‘abcdef’
Length(s)
以字节为单位返回字符串s的长度。
ORACL内置的SQL函数
数值函数
Ceil(n)
返回大于或等于n的整数
Select ceil(),ceil() from dual;
Floor(n)
返回小于或等于n的整数
Select floor(),floor() from dual;
Mod(x,y)
返回x除以y得余数，若y为0，则返回x。
Select mod(23,5),mod(4,) from dual; 返回结果： ,
Round(x,[,y])
返回舍入到小数点右边y为的x值。
Select round(),round(,1),round(,-1)
from dual; 返回结果： , ,120
ORACL内置的SQL函数
日期函数
Sysdate
返回当前的日期和时间
Add_months(D,x)
Last_day(D)
返回日期D的月份的最后一天的日期
Months_Between(D1,D2)
返回在D1和D2之间月的数目。
Trunc(D[,format])
返回结尾由format指定的单位的日期。
示例：
Select trunc(sysdate,’year’) from dual; 返回今年的第一天
Select trunc(sysdate,’mm’) from dual; 返回本月的第一天
Select trunc(sysdate,’D’) from dual; 返回本周的第一天
ORACL内置的SQL函数
转换函数
To_char(D，format)
将日期转换为指定格式的字符串。
示例：
Select to_char(sysdate,’yyyy/mm/dd hh:mi:ss’) from dual;
To_Date(string,format)
将字符串转换成日期格式
示例：
Select to_date(‘2000/10/01’,’yyyy/mm/dd’) from dual;
Last_day(D)
返回日期D的月份的最后一天的日期
To_Number(string[,format])
ORACL内置的SQL函数
其它函数
Nvl(a,b)
空值替换函数，若a为空，则替换成b。
示例：
Select ename,sal,sal+nvl(comm,0) from dual;
DECODE(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
该函数的含义如下：
IF 条件=值1 THEN
RETURN(翻译值1)
ELSIF 条件=值2 THEN
RETURN(翻译值2)
......
ELSIF 条件=值n THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END IF
PL/SQL的注释
注释增强了可阅读性，使得程序更易于理解。
单行注释
-- comment
多行注释
/* comment */
注意：此注释不能作用在SQL语言上。
示例：
DECLARE
v_deptno number(2); --与雇员表中部门代码字段交互的变量
v_sal number(7,2); --与雇员表中工资字段交互的变量
BEGIN
/*this is
a test!
*/
select deptno,sal into v_deptno,v_sal from emp where empno=7788;
END;
PL/SQL块的定义部分
在PL/SQL块中引用的所有标识符，都必须在定义部分中明确定义。
定义常量
格式：〈标识符〉 CONSTANT〈数据类型〉：= 〈表达式〉]
例：定义一常量PI，值为。
PI CONSTANT NUMBER(3,2) := ;
定义标量型变量
标量型数据类型，是指数据类型为个体型。
格式：<标识符> <数据类型> [NOT NULL] [:=|DEFAULT <表达式>]
例：定义一宽度为10个字符的字符串变量X。
DECLARE
X CHAR(5)；
y CHAR(5):=‘ORACLE’;
Z CHAR(5) default ‘oracle’;
代表数据库列的变量
先看一个示例：创建一PL/SQL块，根据部门号，返回部门名称.
DECLARE
v_dname %type;
BEGIN
SELECT dname INTO v_dname FROM DEPT WHERE deptno=10;
_LINE(v_dname));
EXCEPTION WHEN NO_DATA_FOUND THEN
_LINE(‘sorry:no data found!’);
END;
问题：
所引用的数据库表中的数据类型不知道？
所引用的数据库表中的数据类型将来改变改变怎么办？
PL/SQL块的定义部分
另一种定义标量型变量的方法——%TYPE
定义一个变量,其数据类型与已知变量的数据类型相同，或者与数据库
表的某个列的数据类型相同。
%TYPE的优点在于：
所引用的数据库表中的数据类型可以不必知道。
所引用的数据库表中的数据类型可以实时改变。
格式：<标识符> <已知变量或表列> [NOT NULL] [:=|DEFAULT <表达式>]
<变量名> <基表名>.<列名>%TYPE
例：定义一个变量，其数据类型基于另一个变量
DECLARE
V_1 NUMBER(7,2);
V_11 V1%TYPE := ;
例：定义一个变量，其数据类型基于数据库中表的列
DECLARE
v_ename %TYPE;
V_SAL %TYPE;
PL/SQL块的定义部分
另一种定义组合型变量的方法——%ROWTYPE
定义一个变量,其数据类型与数据库表的数据结构相同。
%ROWTYPE的优点在于：
所引用的数据库表中的数据类型可以不必知道。
所引用的数据库表中的数据类型可以实时改变。
简易格式：<变量名> <基表名>%ROWTYPE
例：
DECLARE
v_emp emp%rowtype;
BEGIN
SELECT * INTO v_emp FROM emp WHERE empno=7788;
_LINE();
_LINE();
_LINE();
_LINE();
END;
变量的引用和赋值
标量变量赋值
格式：<变量>:=<表达式>；
例：V_NAME := ‘JOAN’;
v_demptno:=10;
组合型变量赋值
格式：〈变量.域名〉〈（主键值）〉：=〈表达式〉；
例：:=8888;
:=8888;
PL/SQL中使用SQL
在PL/SQL块中，通过SQL语句对ORACLE数据库中的数据进行
存取。在PL/SQL中：
可以使用的SQL语句有：
SELECT、INSERT、DELETE、UPDATE、COMMIT、ROLLBACK
不可以直接使用的SQL语句有：
数据定义语句（DDL），如：CREATE TALBE，DROP TABLE
数据控制语句（DCL），如：GRANT、REVOKE
备注：在PL/以上版本，允许通过DBMS_SQL包来创建动态SQL语句。
PL/SQL中使用SQL－SELECT语句
SELECT语句：将数据从数据库中检索出来并放入PL/SQL变量中。
格式：SELECT <表列> INTO <变量> FROM <表>
例：查询某个雇员的姓名及工资。
DECLARE
v_empno %type:=7788;
v_ename %type;
v_sal %type;
BEGIN
select ename,sal into v_ename,v_sal from emp where empno=v_empno;
_LINE(v_empno||v_ename||v_sal);
EXCEPTION WHEN NO_DATA_FOUND THEN
_LINE(‘sorry:no data found!’);
END;
/
PL/SQL中的SELECT语句中必须包含INTO子句，而且对应的个数要相同，位置要一一对应。
查询结果只返回一条记录，否则会产生异常情况。
（1）查询结果多于一条记录 异常变量：TOO_MANY_ROWS
（2）查询结果没有返回记录 异常变量：NO_DATA_FOUND
PL/SQL中使用SQL
在PL/SQL中，对数据库进行插入(INSERT)、删除(DELETE)
、修改（UPDATE）语句，其语法形式与SQL中的是完全一样的。
例：在EMP表中删去某个雇员。
BEGIN
DELETE emp WHERE empno=7788;
COMMIT;
END;
PL/SQL的执行部分——流程控制语句
流程控制语句主要有三种：
条件控制
循环控制
跳转控制
流程控制语句——条件控制
语法格式：
IF 〈条件〉
THEN
〈语句〉；
[ELSIF 〈条件〉
THEN
〈语句〉；]
[ELSE
〈语句〉；]
END IF；
例：根据职务浮动工资
IF v_job=‘MANAGER’ THEN
v_sal := v_sal*;
ELSIF
v_job=‘SALESMAN’ THEN
v_sal := v_sal*;
ELSE
v_sal := v_sal*;
END IF；
update emp set sal=v_sal
where empno=1234;
流程控制语句——循环控制
在PL/SQL中循环控制的有以下四种：
简单循环
FOR循环
WHERE循环
用于游标的FOR循环
循环控制——简单循环
语法格式：
LOOP
〈语句1〉；
〈语句2〉；
…
EXIT WHEN 〈条件〉；
END LOOP；
例：把数值1到50顺序插入
表中。
V_counter:=1;
LOOP
INSERT INTO temp_table
VALUES (v_counter);
EXIT WHEN v_counter>50
；
V_count :=v_count+1;
END LOOP；
循环控制——FOR循环
语法格式：
FOR 〈循环变量〉IN
[REVERSE]
〈下界〉..〈上界
〉LOOP
〈语句1〉；
〈语句2〉；
…
END LOOP；
REVERSE:使计数器由上界到下
界
递减计数
例：把数值1到50顺序插入表中。
FOR v_counter IN 1..50 LOOP
INSERT INTO temp_table
VALUES (v_counter);
END LOOP；
循环控制——WHILE循环
语法格式：
WHILE 〈条件〉
LOOP
〈语句1〉；
〈语句2〉；
…
END LOOP；
例：把数值1到50顺序插入
表中。
V_counter:=1;
WHILE v_counter<=50
LOOP
INSERT INTO temp_table
VALUES (v_counter);
V_count:=v_count+1;
END LOOP；
跳转控制语句
语法格式：
<<标号>>
……
GOTO <<标号>>;
在进行PL/SQL编程时,尽量避免或不用GOTO语句,因为这种无
条件的跳转语句 打破了程序的逻辑性,有悖于自顶向下的编程
风格.
PL/SQL游标的使用
游标(CURSOR)的功能,是ORALCE系统为了将所有查询结果返回给用户程序而
提供的。一个游标,实际上是在内存中开辟一个工作区,它对应一条SELECT语
句。当打开游标时，就是执行游标所对应的SELECT语句，并将其查询结果放
入工作区，并且指针指向工作区的首部。通过光标上的操作可以把这些记录
检索到客户端的应用程序。
CURSOR
内存区
POINTER
SELECT…INTO…:只能查询数据库的单条记录，并把记录的数据赋给变量。
游标——定义和操纵游标
步骤：
1) 定义游标
2) 打开游标
3) 从游标中取值
4) 关闭游标
定义游标
定义游标，就是定义一个游标名，以及与其相对应的SELECT
语句。
语法格式：
CURSOR 〈游标名〉I S 〈SELECT子句〉；
示例：定义一个包含所有雇员记录的游标。
cursor cur_emp is
select * from emp;
打开游标
打开游标，就是执行游标所对应的SELECT语句，将其查询结
果放入工作区，并且指针指向工作区的首部。
语法格式：
OPEN 〈游标名〉；
从游标中取值
取值工作是将游标工作区中的数据取出一行，放入指定的输
出变量中。
语法格式：
FETCH 〈游标名〉INTO 〈变量1〉,〈变量2〉… ；
示例：fetch cur_emp into v_empno,v_ename,v_sal,v_comm,v_deptno
关闭游标
释放与该游标相关的资源。
语法格式：
CLOSE <游标名>;
示例：close cur_emp;
游标的属性
从游标工作区中逐一地取数据，可以在循环中完成。但循环
的开始以及结束，需以游标属性为依据。
游标属性有：
%ISOPEN： 判断游标是否被打开
%NOTFOUND：判断何时中断循环
%FOUND： 与%NOTFOUND相反
%ROWCOUNT：实际从游标工作区抽取的记录数
示例：
Open cur_emp;
Loop
fetch cur_emp into v_empno,v_ename,v_sal,v_deptno;
exit when cur_emp%NOTFOUND;
End loop;
游标——用于游标的FOR循环
游标的FOR循环，是一种简单的游标操作方法，系统隐式地
进行游标的打开、提取数据、循环、关闭。
格式：
FOR 〈记录变量〉IN 〈游标名〉LOOP
〈语句…>;
END LOOP ;
<记录变量>:由系统隐含定义的记录名
示例：
Declare
cursor cur_emp is select * from emp;
Begin
for v_emp in cur_emp loop
_LINE();
_LINE();
end loop;
End;
一个完整的示例
例：建立一存储过程，根据职务修改工资
CREATE OR REPLACE PROCEDURE
p_update_sal
AS
CURSOR cur_emp IS
SELECT * FROM emp;
v_emp cur_emp%ROWTYPE;
BEGIN
OPEN cur_emp;
LOOP
FETCH cur_emp INTO v_emp;
EXIT WHEN cur_emp%NOTFOUND;
IF =‘MANAGER’ THEN
:=*;
ELSIF =‘SALESMAN’ THEN
:=*;
ELSE
:=*;
END IF；
UPDATE emp SET sal=
WHERE empno=;
END LOOP;
CLOSE cur_emp;
COMMIT;
END;
一个完整的示例(用FOR循环)
CREATE PROCEDURE p_update_sal
AS
CURSOR cur_emp IS SELECT * FROM emp;
BEGIN
FOR v_emp IN cur_emp LOOP
IF =‘MANAGER’ THEN
:=*;
ELSIF =‘SALESMAN’ THEN
:=*;
ELSE
v_sal := v_sal*;
END IF；
UPDATE emp SET sal= WHERE empno=;
END LOOP;
COMMIT;
END;
示例
DECLARE
CURSOR c1 is
SELECT ename, empno, sal FROM emp
ORDER BY sal DESC; -- start with highest paid employee
my_ename CHAR(10);
my_empno NUMBER(4);
my_sal NUMBER(7,2);
BEGIN
OPEN c1;
FOR i IN 1..5 LOOP
FETCH c1 INTO my_ename, my_empno, my_sal;
EXIT WHEN c1%NOTFOUND;
INSERT INTO temp VALUES (my_sal, my_empno, my_ename);
COMMIT;
END LOOP;
CLOSE c1;
END;
异常处理
PL/SQL中，将程序执行过程中的一个警告或错误称为一个异
常(EXCEPTION)。异常情况的种类有三种：
1. 预定义的ORACLE错误
ORACLE预定一的异常情况大约有24个。对这种异常情况的处理，无
须在程序中定义，由ORACLE自动将其引发。
2. 非预定义的ORACLE错误
即其他标准的ORACLE错误。对这种异常情况的处理，需在定义部分定义
，然后由ORACLE自动将其引发。
3. 用户定义的错误
程序执行过程中，出现编程人员认为非正常的。对这种异常情况的处理
，需在定义部分定义，然后显式由地将其引发。
异常处理
语法格式：
EXCEPTION
WHEN <异常情况1> THEN
〈语句〉；
[ WHEN <异常情况2〉 THEN
〈语句〉；
…
[ WHEN OTHERS THEN
〈语句〉；
]
OTHERS：指没有列在异常处理部分中的
其他异常情况。
DECLARE
BEGIN
EXCEPTION
END
PL/SQL块执行过程
异常发生
异常处理
异常处理
预定义的ORACLE错误
预定义的异常名称 错误号 说明
CURSOR_ALREADY_OPEN ORA-6511 试图打开一个已打开的光标
LOGIN_DENIED ORA-1017 无效的用户名或者口令
NO_DATA_FOUND ORA-1403 查询未找到数据
NOT_LOGGED_ON ORA-1012 还未连接就试图数据库操作
DUP_VAL_ON_INDEX ORA-0001 试图破坏一个唯一性限制
TIMEOUT_ON_RESOURCE ORA-0051 发生超时
TRANSACTION_BACKED_OUT ORA-006 由于死锁提交被退回
TOO_MANY_ROWS ORA-1422 SELECT INTD命令返回的多行
异常处理
预定义异常示例：
BEGIN
insert into emp (empno,ename) values (7788,'testuser');
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
_LINE('错误：破坏了唯一性的原则!');
WHEN OTHERS THEN
_LINE('错误：未知!');
END;
对预定义异常情况的处理，无须在程序中定义，由ORACLE自动将其引
发。
异常处理
非预定义的ORACLE异常处理
对于这类的异常情况的处理，首先必须对非预定义的ORACLE错误进行定义。
其处理步骤为：
1. 在PL/SQL块的定义部分定义异常情况
语法：<异常情况名> EXCEPTION;
2. 将定义好的异常情况，与标准的ORACLE错误联系起来，使用EXCEPTION_INIT语句。
语法：PRAGMA EXCEPTION_INIT (<异常情况>,<错误代码>);
3. 在PL/SQL块的异常情况处理部分作出相应的处理。
示例：DECLARE
e_missNUll exception;
PRAGMA EXCEPTION_INIT (e_missNull,-1400);
BEGIN
insert into emp (ename) valus (‘TOM’);
EXCEPTION
when e_missNull then
_LINE(‘错误：雇员代码不能为空!’);
END；
异常处理
用户自定义的异常处理
对于用户自定义的异常情况的处理，一般都需要用户在PL/SQL块中进行
定义，然后显示地将其引发。
步骤为：
1. 在PL/SQL块的定义部分定义异常情况名
2. 在PL/SQL块的可执行部分将其引发，使用RAISE语句。
语法为:RAISE <异常情况>；
示例：
DECLARE
DEPT_CODE NUMBER(2);
INVALID_DEPT_CODE EXCEPTION;
BEGIN
DEPT_CODE = X;
IF DEPT_CODE NOT IN(10,20,30,40) THEN
RAISE INVALID_DEPT_CODE;
END IF;
EXCEPTION WHEN INVALID_DEPT_CODE THEN
_LINE('INVALID Department CODE');
END;
异常不一定必须是oracle返回的系统错误，用户可以在自己的应用程序中创
建可触发及可处理的自定义异常。
问题
？
