存储过程简介
SQL语句需要先编译然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。
存储过程是可编程的函数,在数据库中创建并保存,可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数,或者封装特定功能时,存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟,它允许控制数据的访问方式。
存储过程的优点
- 增强SQL语言的功能和灵活性:存储过程可以用控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。
- 标准组件式编程:存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响。
- 较快的执行速度:如果某一操作包含大量的Transaction-SQL代码或分别被多次执行,那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询,优化器对其进行分析优化,并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化,速度相对要慢一些。
- 减少网络流量:针对同一个数据库对象的操作(如查询、修改),如果这一操作所涉及的Transaction-SQL语句被组织进存储过程,那么当在客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而大大减少网络流量并降低了网络负载。
- 作为一种安全机制来充分利用:通过对执行某一存储过程的权限进行限制,能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。
MySQL的存储过程
存储过程是数据库的一个重要的功能,MySQL 5.0以前并不支持存储过程,这使得MySQL在应用上大打折扣。好在MySQL 5.0开始支持存储过程,这样即可以大大提高数据库的处理速度,同时也可以提高数据库编程的灵活性。
存储过程相关命令汇总
1. 创建存储过程
DELIMITER //
CREATE PROCEDURE 存储过程名(参数种类1 参数1 数据类型1,[...])
BEGIN
具体的procedure(处理)
END
- **DELIMITER //**表示给变分隔符,默认分隔符是;,否则存储过程中含有;,MySQL监视器无法分辨。
- 存储过程中具体的处理类容放在BEGIN和ENDBEGIN和END 之间;
- 存储过程需要制定参数,包括种类(IN,OUT,INOUTIN,OUT,INOUT,分别代表输入参数,输出参数和即是输入也是输出的参数),参数名和数据类型。【和函数不同,函数指定输入参数即可】
2 查看存储过程
- 查看数据库中是否存在存储过程
SHOW PROCEDURE STATUS\G
- 查看存储过程的具体信息
SHOW CREATE PROCEDURE 存储过程名\G
- 执行存储过程 CALL
CALL 存储过程名(参数,...)
3. 删除存储过程
DROP PROCEDURE 存储过程名;
存储过程优化
- 使用if条件语句创建存储过程
- CASE命令的多重分支
- 定义本地变量
存储函数
存储函数的其实和存储过程差不多。
语法:
create[or replace] function 存储函数的名字(参数名 in|out 参数类型...) return 参数类型
is|as
begin
end;
调用存储函数:
declare
vsal number;
begin
vsal :=fun_test1(7369);
dbms_output.put_line(vsal);
end;
JDBC调用存储过程:
//1、导入驱动包
//2、注册驱动
Class.forName("oracle.jdbc.OracleDriver");
//3、获取连接
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@localhost:1521:ORCL", "wsh", "wsh");
//4、获取执行sql的statement对象,封装参数,执行sql
String sql="{call proc_gettotalsal(?,?)}";
CallableStatement statement = conn.prepareCall(sql);
//设置输入参数
statement.setInt(1, 7788);
//注册输出参数
statement.registerOutParameter(2, OracleTypes.NUMBER);
//执行statement对象
statement.execute();
//5、获取结果,输出参数是几,获取的就是几
int totalsal = statement.getInt(2);
//输出结果:工资前和工资后
System.out.println("工资"+totalsal);
//6、释放资源
statement.close();
conn.close();
```
**JDBC调用存储函数**:
```java
//1、导入驱动包
//2、注册驱动
Class.forName("oracle.jdbc.OracleDriver");
//3、获取连接
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@localhost:1521:ORCL", "wsh", "wsh");
//4、获取执行sql的statement对象,封装参数,执行sql
String sql="{?= call func_getsal(?)}";
CallableStatement statement = conn.prepareCall(sql);
//5、获取结果
//设置输出参数
statement.registerOutParameter(1, OracleTypes.NUMBER);
//设置输入参数
statement.setInt(2, 7788);
//执行sql
statement.execute();
int totalsal = statement.getInt(1);
System.out.println("年薪工资:"+totalsal);
//6、关闭资源
statement.close();
conn.close();