本文详细介绍了MySQL数据库中过程和函数的使用方法，包括环境设置、变量声明与查询、过程与函数的创建、参数类型、返回值设定及如何查看和删除。同时，还提供了在JDBC中调用过程和函数的示例。
过程和函数 ( MySQL )
1、环境
1.1、delimiter
在MySQL数据库环境下，修改 delimiter 可以参照以下方式:
mysq > delimiter $
mysql > select now() from dual ;
-> select current_timestamp from dual ;
-> $
+---------------------+
| now() |
+---------------------+
| 2018-11-29 14:23:16 |
+---------------------+
1 row in set (0.00 sec)
+---------------------+
| current_timestamp |
+---------------------+
| 2018-11-29 14:23:16 |
+---------------------+
1 row in set (0.00 sec)
1.2、变量
- 用户自定义变量
使用 set 指令显式声明变量并赋值
mysql> set @变量名 = 取值 ;
比如:
mysql> set @name = '穆桂英' ;
查询变量的值
mysql> select @变量名 from dual ;
比如
mysql> select @name from dual ;
在 MySQL 中 ， @ 开头的是用户自定义变量，首次在函数或过程中使用时即声明
调用函数或过程时即声明变量:
mysql> call count_human_male( @male_count ) ;
通过 select 可以查询该变量的值:
mysql> select @male_count from dual ;
- MySQL数据库系统变量
比如 事务隔离级别: @@tx_isolation
查询系统变量，获取当前事务的隔离级别
mysql> select @@tx_isolation from dual ;
修改当前会话的事务隔离级别 ( 读未已提交 )
mysql> set session tx_isolation = 'read-uncommitted';
修改当前会话的事务隔离级别 ( 读已提交 )
mysql> set session tx_isolation = 'read-committed';
修改当前会话的事务隔离级别 ( 可重复读 )
mysql> set session tx_isolation = 'repeatable-read';
修改当前会话的事务隔离级别 ( 序列化 )
mysql> set session tx_isolation = 'serializable';
比如 事务提交方式: @@autocommit
查询系统变量，获取当前事务提交方式
mysql> select @@autocommit from dual ;
1 表示 自动提交 ( 每执行一条 DML 语句就提交一次事务 )
0 表示 手动提交 ( 需要通过 commit 来提交事务 或 通过 rollback 回滚事务 )
设置事务提交方式
mysql> set autocommit = false ; -- 不要再自动提交
mysql> set autocommit = true ; -- 自动提交 (默认值)
2、过程 ( procedure )
数据库中的过程( procedure ) ，就是存储过程( stored procedure )。
2.1、创建过程
创建过程的语法:
CREATE PROCEDURE 过程名称 ( 参数类型 名称 数据类型 [ , 参数类型 名称 数据类型 ] )
BEGIN
-- 在 BEGIN 和 END 之间书写 过程要完成的操作
END
其中:
- 参数名称之前的 参数类型分为三种类型: IN 、OUT 、INOUT
- 参数名称应该尽量避免与数据库中的列名相同
- 参数的数据类型建议指定数据类型和长度限制，比如 INT(10)
2.2、 参数类型
- IN : 传入参数
CREATE PROCEDURE remove_human( IN hid INT(10) )
BEGIN
DELETE FROM t_humans WHERE id = hid ;
END;
调用过程
mysql> call remove_human( 12 ) ;
- OUT : 传出参数
定义带有传出参数的过程:
CREATE PROCEDURE count_human_male ( OUT mcount INT )
BEGIN
SELECT count(*) INTO mcount FROM t_humans WHERE gender = '男' ;
END ;
调用带有传出参数的 过程:
mysql> CALL 过程名称( @用户变量 )
比如调用 count_human_male 过程，并向其传递参数( 这里的 @male_count 为用户自定义变量 )
mysql> CALL count_human_male( @male_count ) ;
通过查询 @male_count 变量来获取 过程 传出的数值
mysql> SELECT @male_count FROM dual ;
- INOUT : 传入/传出参数
CREATE PROCEDURE get_name( IN hid INT(5) , INOUT x VARCHAR(50) )
BEGIN
SELECT name INTO x FROM t_humans WHERE id = hid AND married = x ;
END;
调用过程:
mysql> set @suibian = 'N' ;
mysql> call get_name( 1 , @suibian );
mysql> select @suibian from dual ;
2.3、查看过程
- 列出所有"过程"
查询方式为:
SELECT name FROM mysql.proc WHERE db = '数据库名' AND type = 'procedure' ;
其中:
mysql 为 数据库名称 ( 这个数据库由 MySQL 来维护 )
proc 为 mysql 数据库 中的表
name 、db 、 type 都是 proc 表中的列
比如查询 ycpower 数据库中的所有过程，可以使用:
mysql> select name from mysql.proc where type='procedure' and db='ycpower' ;
- 查看过程的创建代码
mysql> show create procedure 过程名称 ;
比如:
mysql> show create procedure count_human_male ;
- 查看过程的状态
mysql> show procedure status ;
2.4、删除过程
mysql> drop procedure 过程名称 ;
3、函数( function )
数据库中的函数( function ) ，也被某些人称作 存储函数( stored function )。
3.1、创建函数
创建函数的语法:
CREATE FUNCTION 函数名称 ( 参数类型 名称 数据类型 [ , 参数类型 名称 数据类型 ] )
RETURNS 返回类型
BEGIN
-- 在 BEGIN 和 END 之间书写 函数要完成的操作
-- 函数内部通过 RETURN 返回数据
END
create function salary(hid int)
RETURNS double(7,2)
begin
return(select sal from emp where empno=hid);
end;
$
mysql> select salary(9527) ;
+--------------+
| salary(9527) |
+--------------+
| 1300 |
+--------------+
3.2、 参数类型
-
IN 传入参数
-
OUT 传出参数
-
INOUT 传入/传出参数
3.3、 返回值
创建函数时，使用 RETURNS 来声明返回类型
在函数内部，通过 RETURN 来返回数据
3.4、 查看函数
- 列出所有函数(自定义函数)
SELECT name FROM mysql.proc WHERE type = 'function' AND db = '数据库名' ;
- 查看过程的创建代码
mysql> show create function 函数名称 ;
- 查看过程的状态
mysql> show function status ;
3.5、 删除函数
mysql> drop function 函数名称 ;
在JDBC中测试
调用过程CallableStatementTest1
package vip.ycpower.jdbc.test;
import java.sql.*;
public class CallableStatementTest1 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/ycpower?userUnicode=true&characterEncoding=utf8&useSSL=false";
Connection connection= DriverManager.getConnection(url,"root","");
System.out.println("connection=>"+connection);
//1、创建用于调用指定过程或函数CallableStatement
CallableStatement callableStatement=connection.prepareCall("{CALL hcount(?,?)}");
System.out.println("callableStatement=>"+callableStatement);
//2、注册传出参数
callableStatement.registerOutParameter(2, Types.VARCHAR);
//3、设置参数占位符的值
callableStatement.setInt(1,2);//设置第一个传入参数的值
callableStatement.setString(2,"Y");//设置第二个传入参数的值
//4、执行
callableStatement.execute();
//5、获取传出参数的值（根据注册传出参数的索引来获取值）
String name=callableStatement.getString(2);
System.out.println(name);
//6、释放资源
callableStatement.close();
connection.close();
}
}
调用函数CallableStatementTest2
package vip.ycpower.jdbc.test;
import java.sql.*;
public class CallableStatementTest2 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/ycpower?userUnicode=true&characterEncoding=utf8&useSSL=false";
Connection connection= DriverManager.getConnection(url,"root","");
System.out.println("connection=>"+connection);
//1、创建用于调用指定过程或函数CallableStatement
CallableStatement callableStatement=connection.prepareCall("{?=CALL salary(?)}");
System.out.println("callableStatement=>"+callableStatement);
//2、注册传出参数
callableStatement.registerOutParameter(1, JDBCType.DOUBLE);
//3、设置参数占位符的值
int id=9527;
callableStatement.setInt(2,id);
//4、执行
callableStatement.execute();
//5、获取传出参数的值（根据注册传出参数的索引来获取值）
Double salary=callableStatement.getDouble(1);
System.out.println(salary);
//6、释放资源
callableStatement.close();
connection.close();
}
}
扫一扫
1373
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
