一、存储过程
1.定义:
一般在大型的数据库系统中,一组为了完成特定功能的sql语句集,存储在数据库中,经过第一次编译后,再次调用时就不需要再次编译了,用户只需要指定存储过程名称并给定参数就可以执行完成任务。
在开发程序中,为了一个特定的业务功能,会向数据库进行多次连接关闭(连接和关闭是很耗费资源的),需要对数据库进行多次I/O读写,性能比较低。如果把这些放到存储过程中,就可以实现连接关闭一次数据库来实现业务,大大提高了效率。
2.语法:
(1)创建存储过程
create procedure 存储过程名(参数)
begin
sql语句…
sql语句…
end;
例:
create procedure pro_b()
begin
select * from t_man;
end;
注意:sql语句是以“;”结尾的,这样“end;”就不会执行,造成存储过程不完整,需要重新定义结束符,一般
使用delimiter定义,如:delimiter #,表示遇到#算结束。
create procedure pro_b()
begin
select * from t_man;
end#
(2)调用存储过程
call pro_b() #
(3)删除存储过程
drop procedure (if exists) 存储过程名;
3.带参数的存储过程:in out inout
(1)in类型:in可以省略不写,默认是in类型,即入参
例:给存储过程一个学号,存储过程显示学生信息
create procedure pro_c(in id int)
begin
select * from t_student where sid=id;
end#
调用:call pro_c(12)#
例:给存储过程一个班级编号和英文名,查询这些学生信息(or)(多参数)
create procedure pro_d(in class int,in ename varchar(20))
begin
select * from t_student where sclass=class or sename=ename;
end;
注意:存储过程中limit后面不能使用参数,应写明具体值,下面写法错误:
create procedure pro_e(in n int,in m int)
begin
select * from t_student limit m,n;
end;
(2)out类型
例:写一个存储过程,返回3班的最高分
create procedure pro_r1(out x int)
begin
select max(sscore) into x from t_student where sclass=3;
end;
注意:执行后最高分放到变量x里,调用out存储过程前,需要先定义一个变量:set @变量名=值;
call 存储过程名(@变量名);
例:给存储过程一个系编号,获取对应的系名称;
create procedure pro_r2(in x int,out name varchar(20))
begin
select dname into name from t_dept where dclass=x;
end#
执行过程:
①执行存储过程;
②定义输出变量:set @n=‘abc’#
③调用存储过程:call pro_r2(3,@n)#
④显示@n结果:select @n #
(3)inout类型:入参和出参使用同一个参数
例:传入一个班级编号,返回这个班级的最高分
create procedure pro_r3(inout x int)
begin
select max(sscore) into x from t_student where sclass=x;
end#
执行过程:
①执行存储过程;
②定义输出变量:set @a=1 #
③调用存储过程:call pro_r2(3,@a) #
④显示@n结果:select @a #
二、PLSQL编程:
1.定义:PLSQL(Procedure Language SQL),是Oracle对sql语句的过程化扩展,在SQL语言中添加过程处理语句(如分支、循环等),使SQL语言具有过程处理能力。
PLSQL语言的基本结构:
2.变量:常见变量分为两大类
①普通数据类型:char、varchar2、date、number、boolean、long;
②特殊变量类型:引用型变量、记录型变量
声明变量的方式:
变量名 变量类型(变量长度)
name varchar2(20);
(1)普通变量:赋值的方式有两种
①直接赋值语句:使用:= 赋值
②语句赋值:使用select…into…赋值,即select 值 into 变量
例:
(2)引用型变量:变量的类型和长度取决于表中字段的类型和长度。通过
表名.列名%TYPE指定变量的类型和长度,例如:name emp.ename%TYPE
例:
(3)记录型变量:接受表中的一整行记录,相当于Java中的一个对象
语法:变量名称 表名%ROWTYPE,例如:v_emp emp%ROWTYPE
例:
3.流程控制:
(1)条件分支:
语法:
BEGIN
IF 条件1 THEN 执行1
ELSIF 条件2 THEN 执行2
ELSE 执行3
END IF
END;
例:
(2)循环:在Oracle中有3种循环方式,这里介绍:loop循环
语法:
BEGIN
LOOP
EXIT WHEN 退出循环条件
END LOOP;
END;
例:
4.游标:
(1)定义:用于临时存储一个查询返回的多行数据(结果集,类似于Java的 JDBC连接返回的ResultSet集合),通过遍历游标逐行访问处理该结果集的数据 。
(2)使用方式:声明—>打开—>读取—>关闭
(3)语法:
●游标声明:
CURSOR 游标名[(参数列表)] IS 查询语句;
●游标打开:
OPEN 游标名;
●游标取值:
FETCH 游标名 INTO 变量列表;
●游标关闭:
CLOSE 游标名;
(4)游标的属性:
(5)无参游标:
例:
(6)有参游标:
存储过程:
三、在Java程序中调用存储过程(JDBC连接中):