PL/SQL
简述
通过if、loop等语句控制程序的执行流程,以及变量在语句间传递数据信息,将流程控制与sql的数据操作、查询结合，各取所长.
结构
[declare]
声明部分,可选
Begin
执行部分,必选
[exception]
异常处理,可选
End;
声明部分:用来定义变量,常量,游标等,且这些只能在当前块中使用,不能被其他块引用
执行部分:完成主要的逻辑控制和运算可以包含多个PL/SQL和sql语句;
执行结束后,要么到异常处理,或者到end结束
异常处理
和Java代码中异常处理类似
可以使用when...then分支语句来判断多种异常情况
一个例子
set SERVEROUTPUT ON; --设置在服务器端输出结果
DECLARE
a int:=100;
b int:=200; /*声明int型变量b并赋值*/
c number;
BEGIN
c:=(a+b)/0;
SYS.DBMS_OUTPUT.PUT_LINE(c); --输出变量c的值
exception
when zero_divide
then SYS.DBMS_OUTPUT.PUT_LINE('除数不能为零');
end;
合法字符集
大小写字母 A-Z a-z
数字 0-9
非显示字符 空格,回车,制表符
数学符号 +,-,*,/,>,<,=等
间隔符 (), {}, [], ?, !, ;, :, @等
特殊数据类型(3种)
1.%type(与指定列名相同类型的值)
格式:变量名 列名%type
好处:a.可以不知道某列的数据类型;b.当列数据类型发生改变,也不会受到影响
2.Record(是一种结构化数据类型)
Type 记录类型名称 is record
(
成员变量名称 数据类型 [not null] [:=默认值],
...
成员变量名称 数据类型 [not null] [:=默认值]
)
变量名 记录类型名称;(定义record类型变量)
例:
create table student
(
id int not null,
name varchar2(50),
birth date
)
DECLARE
type emp_student is record
(
sid int,
sname varchar2(50),
sbirth date
);
stuInfo emp_student;
BEGIN
select id,name,birth
into stuInfo
from student
where id=1;
SYS.DBMS_OUTPUT.PUT_LINE(stuInfo.sname||'出生日期是'||stuInfo.sbirth);
END;
3.%rowtype(结合%type和record的优点用来存储检索到的一行数据)
格式:变量名 表名%rowtype
例:
DECLARE
s student%rowtype;
begin
select *
into s
from student
where id=1;
SYS.DBMS_OUTPUT.PUT_LINE(s.name||'出生日期是'||s.birth);
end;
定义变量和常量
1.定义变量
变量名 数据类型[(长度):=默认值];
Name varchar2(50):=’超信’;
2.定义常量
变量名 constant 数据类型:=值;
Year_day constant int:=’365’;
流程控制语句
1.If ... Then语句
格式:
if 表达式 then
语句
End if;
表达式为true则执行语句否则不执行
2.If ... Then ... Else
格式:
If 表达式 then
语句1
Else
语句2
End if;
3.if ... Then elsif语句
If 表达式 then
语句
Elsif 表达式 then
语句
Else
语句
End if;
4.Case语句
Case 选择器(一个变量)
When 表达式(一般是个常量) then 语句;
...
[else 语句]
End case;
循环语句(loop,while,for)
1.Loop(和Java中的do{...}while类似先执行一遍语句再判断)
Loop
语句
Exit when 表达式
End loop;
2.While(先判断,true则执行语句,否则不执行)
While 表达式 loop
语句
End loop;
3.For
For 变量名 in [reverse] 下限值..上限值 loop
语句
End loop;
没有reverse表示递增计数,有表示递减
例:
DECLARE
i int:=3;
su int:=0;
begin
For i in 9..10 loop
su:=su+i;
End loop;
SYS.DBMS_OUTPUT.PUT_LINE(su);
end;
说明:计数从下限值开始,和i初始是多少没有关系,此处i可以不必给初始值3
游标
1.显示游标(通常用于查询结果集select语句,分四步:声明,打开,读取,关闭)
a.声明游标
Cursor 游标名[(参数列表)]
[return 返回值类型]
Is select语句;
参数列表中参数格式
参数名 [in] 数据类型 [ {:=|default} 默认值或表达式]
In表示输入方向,可以省略
b.打开游标
Open 游标名[(参数列表)]
此处参数列表和声明时代对应
c.读取游标
Fetch 游标名 into 变量
此处变量可以说一个变量列表,也可以是一个record变量(推荐)
d.关闭游标
Close 游标名
游标属性
%found 判断是否有数据,有为true
%notfound 与found相反
%rowcount 返回函数
%isopen 游标是否打开
例:
首先要创建student表,插入数据
DECLARE
s student%rowtype;
cursor cu
is select * from student;
begin
open cu;
fetch cu into s;
while cu%found loop
SYS.DBMS_OUTPUT.PUT_LINE(s.name);
SYS.DBMS_OUTPUT.PUT_LINE(cu%rowcount);
fetch cu into s;
SYS.DBMS_OUTPUT.PUT_LINE(cu%rowcount);
end loop;
close cu;
end;
执行结果:
daipeng
1
2
caoxin
2
2
2.隐式游标
常用于修改,删除数据,特殊情况可以用于处理select查询结果.
Oracle会默认创建一个隐式游标,名称是sql
例:
BEGIN
update student
set name='myq'
where id=1;
if sql%found then
SYS.DBMS_OUTPUT.PUT_LINE('没有1号学生');
else
SYS.DBMS_OUTPUT.PUT_LINE('已修改');
end if;
end;
3.For遍历游标
For 变量名 in 游标名或查询语句 loop
语句
End loop;
例:
DECLARE
cursor cu
is select * from student;
begin
for s in cu loop
SYS.DBMS_OUTPUT.PUT_LINE(s.name);
end loop;
end;
还可以写成,效果相同:
begin
for s in (select * from student) loop
SYS.DBMS_OUTPUT.PUT_LINE(s.name);
end loop;
end;
特别说明:
a.此处不要打开游标,否则报错
b.此处的s自动设为record型,并不要在声明部分声明,否则报错
异常处理
1.预定义异常
常用的
ZERO_DIVIDE 除数为0
ACCESS_INTO_NULL 给某个未初始化对象的属性赋值
COLLECTION_IS_NULL 使用未初始化集合
CURSOR_ALREADY_OPEN 多次打开同一个游标,第二次打开之前,没有关闭
INVALID_CURSOR 非法游标操作,如为打开之前关闭游标
INVALID_NUMBER 将字符串转换为数字失败
LOGIN_DENIED 连接数据库的用户名或密码错误
NO_DATA_FOUND select into语句没有返回值
ROWTYPE_MISMATCH 类型不匹配
SELF_IS_NULL 使用空对象调用对象方法
SUBSCRIPT_BEYOND_COUNT 下标越界
SUBSCRIPT_OUTSIDE_LIMIT 使用非法索引号引用表数据
SYS_INVALID_ROWID rowid值无效
TIMEOUT_ON_RESOURCE 等待数据超时
TOO_MANY_ROWS 使用select into结果集超过一行
2.自定义异常
A.错误编号异常
Declare
异常名 exception;
Pragma exception_init(异常名,错误号);--将错误号和异常绑定
Begin
....
Exception
When 异常名 then
语句
End;
说明:错误号就是执行语句时报的错ORA-XXXXX的-XXXXX部分
如:违反唯一约束会报ORA-00001,则错误号为-00001;
B.业务逻辑异常
Declare
异常名 exception;
Begin
....
Raise 异常名;
...
Exception
When 异常名 then
语句
End;
