摘要:SQL语言只是访问、操作数据库的语言,而并不是一种程序设计语言,因此不能用于程序开发。PL/SQL 是ORACLE在标准SQL语言上进行过程扩展形成后的程序设计语言,是一种ORACLE数据库特有的、支持应用开发的语言。
一、PL/SQL的基本结构
DECLARE
--声明一些变量、常量、用户定义的数据类型以及游标等
--这一部分可选,如不需要可以不写
BRGIN
--主程序体,在这里可以加入各种合法语句
EXCEPTION
--异常处理程序,当程序中出现错误时执行这一部分
END;
--主程序体结束
二、PL/SQL数据类型
1、数字类型
NUMBER:可以存储整数或浮点数
PLS_INTEGER 只存储整数
BINARY_INTEGER 只存储整数
2、字符类型
VARCHAR2:可以存储变长字符串,声明语法VARCHAR2(Maxlength),最大长度32767字节
CHAR:表示定长字符串,声明语法CHAR(Maxlength)
LONG:表示一个可变的字符串,最长长度23760字节
NCHAR
NVARCHAR2
3、日期类型
DATE
4、布尔类型
BOOLEAN 一个布尔类型变量的值可以是TRUE、FALSE或NULL
5、type定义数据类型
定义数据类型的语句格式如下:
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_record;
引用这个记录变量时要指明内部变量,如ateacher.TID
三、PL/SQL变量和常量
1、定义常量
<常量名>constant<数据类型>:=<值>;
常量值不会改变
2、定义变量
<变量名><数据类型>[(宽度):=<初始值>];
可见,变量定义时没有关键字,但是要指定数据类型,宽度和初始值可以定义也可以不定义,根据需要灵活使用
简单的PL/SQL使用
1、case 判断
declare
vf varchar2(20):='及格';
va varchar2(50);
begin
va:= case vf
when '及格' then '成绩>60'
when '不及格' then '成绩<60'
else '输入有误'
end;
dbms_output.put_line(va);
end;
输出 成绩>60
2、for循环使用
begin
for x in reverse 1..10 loop -- reverse大到小
DBMS_OUTPUT.PUT_LINE('内:x='||x);
end loop;
DBMS_OUTPUT.PUT_LINE('endget');
end;
输出:内:x=10
内:x=9
内:x=8
内:x=7
内:x=6
内:x=5
内:x=4
内:x=3
内:x=2
内:x=1
endget
3、while 循环
declare
c integer;
begin
c:=0;
while c<=5 loop
c:=c+1;
DBMS_OUTPUT.PUT_LINE(c);
end loop;
end;
输出:
1
2
3
4
5
6
四、PL/SQL的游标
SQL是面向集合的,其结果一般是集合量(多条记录),而PL/SQL的变量一般是标量,其一组变量一次只能存放一条记录。所以仅仅使用变量并不能完全满足SQL语句向应用程序数据数据的要求。因为查询结果的记录数是不确定的,事先也就是不知道要声明几个变量。为此,在PL/SQL中引入了游标的概念,用游标来协调这两种不同的处理方式。
1、显式游标的处理包括声明游标、打开游标、提取游标、关闭游标4个步骤
2、声明游标
对游标的声明定义了游标的名字并将该游标个一个select语句相关联。显式游标声明部分在DECLARE中,语法为:
CURSOR<游标名> IS SELECT<语句>;
例1:完整的操作游标LOOP循环
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 teacher 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 --将性别为M的行放入男老师表
INSERT INTO MALE_TEACHERS(TID,TNAME,TITLE)VALUES(teacher_id,teacher_name,teacher_title);
else --将性别为不是男的行放入女老师表
INSERT INTO MALE_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; --将第n行数据放入变量中,游标后移
end loop;
CLOSE teacher_cur; --关闭游标
END;
例2:FOR 循环
DECLARE
type teacher_record is RECORD --type定义teacher_record集合
(
TID number(5) NOT NULL:=0,
TNAME VARCHAR2(50),
TITLE VARCHAR2(50),
SEX CHAR(1)
);
CURSOR teacher_cur IS select tid,tname,title,sex from teachers where tid<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;
3、使用显式游标时,需注意以下事项:
<->使用前须用%ISOPEN检查其打开状态,只有此值为TRUE的游标才能使用,否则要先将游标打开。
<->在使用游标过程中,每次都要用%FOUND或%NOTFOUND属性检查是否返回成功,即是否还有要操作的行。
<->将游标中行取至变量组中时,对应变量个数和数据类型必须完全一致
<->使用完游标必须将其关闭,以释放相应的内存资源。4、游标属性
%FOUND --是否找到游标
%NOTFOUND --是否没找到游标
%ROWCOUNT --记录了游标抽取过的记录行数,也可以理解成当前游标所在的行号
%ISOPEN --游标是否打开
5、游标错误
ORA-1001LInvalid Cursor --非法游标
ORA_1002:FETCH out OF sequence --超出索引
6、游标变量
有待完善