PL/SQL总结
PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点,所以PL/SQL把数据操作和查询语句组织在PL/SQL代码的过程性单元中,通过逻辑判断、循环等操作实现复杂的功能或者计算。PL/SQL只有 Oracle 数据库有。 MySQL 目前不支持 PL/SQL 的。
主要内容:
1、 数据类型
2、 运算
3、 分支结构
4、 循环结构
5、 异常
6、 游标
7、 函数
8、 存储过程
9、 触发器
二、PL/SQL 基本格式
declare --当不需要声明变量、常量、游标等时,可以省略
begin --相当于 main 方法
exception --有需要处理异常时写
end --PL/SQL代码块结束
三、数据类型
number
varchar2(10)
date
long
boolean
1)声明变量:declare
变量名 数据类型
动态获取列的数据类型: 表名.列名%type
动态获取列的所有数据类型:表名%rowtype
2)命名规则:必须以字母开头,可包含 _ $ ¥ 数字,不超过30个,不能是关键字
通常:变量:v_xxx,常量:c_xxx,游标:xxx_cursor,记录类型:xxx_record(组合类型)
3)赋值
A.变量名 := 值
B.用 sql 语句:select 列名1,列名2 into 变量1,变量2 from ….
Eg: declare
v_salemp.sal%type;
begin
selectsal into v_sal from emp where ename = ‘SCOTT’;
dbms_output.put_line(v_sal);
end;
4)记录类型:多个变量的集合
A.定义
type记录名称 isrecord(
变量1 数据类型,
变量2 数据类型
); //这里就相当于定义了一种数据类型
B.声明一个记录类型:对象名记录名称
C.赋值:select 列名1,列名2 into 对象名 from …
D.使用:对象名.变量名
Eg:declare
typeemp_record is record(
name emp.ename%type,
id emp.empno%type
);
v_emp_record emp_record;
begin
selectename,empno into v_emp_record from emp where empno = 7369;
dbms_output.put_line(v_emp_record.ename|| ‘-‘ || v_emp_record.empno);
end;
四、运算:+ - * / mod() qurt()
五、分支结构
if条件 then 处理语句; end if;
if-if,if-elsif-else 注意:elsif 中没有 e;
每个if 都要用 end if 结束,每个处理语句后面都有分号
case变量名 when 值 then 表达式
else表达式
end;
六、循环结构
1)loop 循环体 exit when 条件 end loop; //类似 Java 中的 do while
2)while 条件 loop 循环体 end loop;
3)for 变量名 in 范围 loop 循环体 end loop;
七、游标
相当于 Java 集合中的迭代器。当需要使用处理查询结果,且该结果是多行数据时,可以考虑用游标。
在创建PL/SQL语言的时候,数据库会为查询出来的结果集分配一个空间,游标就是指向这个空间的指针。
(1)在创建PL/SQL语言的时候,数据库会为查询出来的结果集分配一个空间,游标就是指向这个空间的指针
功能:可以采用游标将查询出来的结果集进行读取,相似于java中的Iterator迭代器
(2)游标的分类
*显示游标:完成上述读取结果集的功能(select)
*隐式游标:系统自动提供的游标,没有办法直接使用
update,insert,delete语句,我们只能通过游标的属性来查看它的状态,系统完成对它的使用。
(3)游标的属性:
*游标名%found:布尔类型的值,代表着结果集中还有没-有数据
*游标名%notfound:代表着结果集中是否没有数据
*游标名%isopen:布尔类型,是否被打开
*游标名%rownum:结果集中的数据条数据
(4)游标的格式
取出一条数据:fetch 游标名 into 变量;
a、定义(并且声明结果集的由来):
cursor 游标名 is select 子句;
b、打开(将结果集中的数据放入临时空间中,并让游标指向第一条数据):
open 游标名;
c、取数据
fetch 游标名 into 变量;(要先取一次在写循环,要不然取不出来数据)
while 游标名%foundloop;
fetch 游标名 into 变量;
对变量的操作;
endloop;
close 游标名;(关闭游标,释放空间,不能取数据了)
(5)隐式游标查看状态:sql%属性
(6)游标可以带参数
1)声明带参数的游标
2)使用的时候传入实际的值。
cursor emp_cursor(参数名 参数类型) is select …..where 列名= 参数名
begin
open emp_cursor(实际值);
for 循环的时候是在 in 后面的游标名后面传入实际的值。
(7)游标中关于事务的部分 for update
作用:锁定数据
八、异常
1、异常分类
A、预定义异常:常用的24种,系统提供编号和异常名称,可以直接使用
如:no_data_found
too_many_row //前两种异常出现在 insert … into …中
cursor_already_open
invalid_cursor //游标没有打开或已经关闭
B、非预定义异常:系统已经给定编号,但是没有名称
C、自定义异常:系统没有给定编号和名称,自己按需求自己定义的异常
2、格式
A.预定义异常:
在begin 与 end 代码中:
begin
exception
when异常名称 then 处理;
whenothers then 处理;
end;
B.非预定义异常:需先定义,连接,再使用
Declare
e_output_exceptionexception; //声明
progamexception_init(异常名称, -编号); //连接
begin
…
exception
…..//同理
end;
C.自定义异常
declare
e_tooLarge_exceptionexception;
v_tempnumber;
begin
if(v_temp> 100) raise e_tooLarge_exception; end if;
exception
when e_tooLarge_exception then
dbms_output.put_line(‘值太大’);
end;
九、函数(function)
这里的函数类似于Java中的方法,函数可以永久存在数据库中,以便使用。
使用方法:先定义一个函数,再使用
a.定义
createor replace function 函数名(参数名 参数类型)
return返回值类型
is
函数变量声明 //没有则省略,但 is 不能省略
begin
… //方法体
end;
b.使用:因为有返回值,所以应该定义一个变量来接收返回值
c. 参数修饰符:in 参数传入方法;out 类似return,可以把值传出;inout 参数既可以传进来,也可以传出去
十、存储过程(procedure)
与函数基本相同,但是函数有返回值,存储过程没有返回值。如果确实有需要传出数据,可以用 out 修饰参数。
十一、触发器(trigger)
当一个事件发生时,进行相应的操作。可以给增删改添加触发器。系统自动调用。
格式:声明—时间—范围
create or replace trigger test_trig
before/after insert on emp //在增/删/改之前/后
for each row //emp 表的每一行
begin
…. //事件触发后所执行的操作
end
触发器专有的属性:(可用于备份数据)
:old ——表示原来的值 :old.sal
:new ——表示新值 :new.sal