sql之储存过程procedures（2）

向右看齐 2022-05-15 04:12 167阅读 0赞

**知识点：**

1.  定义
2.  使用  
    创建存储过程  
    调用存储过程  
    删除存储过程  
    显示储存过程
3.  变量的使用
    
    1.  全局变量
    2.  局部变量
4.  三种参数类型的使用：in输入参数、out输出参数、inout既能输入一个值又能传出来一个值（尽量使用前两种）
5.  储存过程优点与缺点
6.  深入储存过程之控制流程：可以参照事件中流程控制构造（一样），官方文档[http://tool.oschina.net/apidocs/apidoc?api=mysql-5.1-zh][http_tool.oschina.net_apidocs_apidoc_api_mysql-5.1-zh]
7.  存储过程的应用：应用存储过程验证用户登陆身份。

### 一.定义  ###

一个存储程序是可以被存储在服务器中的一套SQL语句。一旦它被存储了，客户端不需要再重新发布单独的语句，而是可以引用存储程序来替代。触发器，其他存储过程以及[Java][]，[Python][]，[PHP][]等应用程序可以调用存储过程。

自身的存储过程称为递归存储过程。大多数数据库管理[系统][Link 1]支持递归存储过程。 但是，MySQL不支持递归存储过程。 在MySQL中实现递归存储过程之前，您应该检查MySQL数据库的版本

### 二. 使用 ###

**1.创建**

DELIMITER$$
    CREATE PROCEDURE test.procedure1()
    BEGIN
        SELECT name,hp,damage FROM user;
    END$$
    DELIMITER;
    
    //test表示的数据库名，可不写

**2.调用**

call procedure();

**3.删除**

DROP PROCEDURE procedureName;

**4.显示**

SHOW PROCEDURE STATUS; #显示有哪些储存过程

### **三. 变量的使用** ###

**2.1 全局变量：**全局变量也称为外部变量，是在函数的外部定义的，它的作用域为从变量定义处开始，到本程序文件的末尾。  
全局变量的定义和赋值：set @adc=33；

set @adc=33；

使用@adc

DELIMITER$$
    CREATE PROCEDURE how2java.procedure6()
    BEGIN
        SELECT @abc;
    END$$
    DELIMITER;

**2.2 局部变量：**局部变量也称为内部变量。局部变量是在函数内作定义说明的。其作用域仅限于函数内部，离开该函数后再使用这种变量是非法的。

局部变量的定义：必须先用Declare命令定以后才可以使用，declare 变量名 数据类型；  
局部变量的赋值方法：set  变量名=表达式；

DELIMITER$$
    CREATE PROCEDURE procedure6()
    BEGIN
    	DECLARE aa INT(10);
    	SET aa=33;
    	SELECT aa;
    END$$
    DELIMITER;

调用储存过程procedure5：

CALL procedure5();

显示结果 aa=33;

### **四. 三种参数类型：in输入参数、out输出参数、inout输入参数** ###

**3.1 in输入参数的使用**

**实现查询：**

DELIMITER$$
    CREATE PROCEDURE procedure6(IN aa INT(10))
    BEGIN
    	SELECT CustomerName,Address,City FROM test1 WHERE id=aa;
    END$$
    DELIMITER;

调用储存过程procedure6

CALL procedure6(3);

**实现插入：**

DELIMITER$$
    CREATE PROCEDURE p4(IN aa INT(10),IN bb VARCHAR(10))
    BEGIN
    	INSERT INTO t1 VALUES(aa,bb);
    END$$
    DELIMITER;

public static void main(String[] args) throws Exception {
    		Class.forName("com.mysql.jdbc.Driver");
    		Connection con=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test","root","123456");
    		
    		//jdbc支持存储过程调用
    		String sql="call p4(?,?)";
    		CallableStatement statement=con.prepareCall(sql);
    		statement.setInt(1, 5);
    		statement.setString(2, "maomao");
    		int i=statement.executeUpdate();
    		System.out.println(i);
    
    		statement.close();
    		con.close();
    		
    	}

**3.2 out输出参数的使用**

第一步：1.创建储存过程；2.声明全局变量aa；3.out为输出aa；4.为aa赋值3；

DELIMITER$$
    CREATE PROCEDURE how2java.procedure3(OUT aa INT(10))
    BEGIN
    	SET aa=3;
    END$$
    DELIMITER;

第二步：定义@test用来接收aa的值

CALL procedure3(@test);

第三步：

SELECT @test;

**3.3 inout输入参数的使用**

第一步：

DELIMITER$$
    CREATE PROCEDURE procedure5(INOUT aa INT(10))
    BEGIN
    	SELECT aa;
    	SET aa=33;
    	SELECT aa;
    END$$
    DELIMITER;

第二步：

SET @dd=4;

第三步：调用储存过程procedure5

CALL procedure9(@dd);

查询第一次返回结果：aa=4；

查询第二次返回结果：aa=33；

第四步：

SELECT @dd;

返回结果为：@dd=33；

## ##

### **五.优点与缺点 ** ###

MySQL存储过程的优点

1.  存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度，效率要比T-SQL语句高。
2.  当对数据库进行复杂操作时，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
3.  一个存储过程在程序在网络中交互时可以替代大堆的T-SQL语句，所以也能降低网络的通信量，提高通信速率。
4.  存储过程可以重复使用,可减少数据库开发人员的工作量。
5.  安全性高,可设定只有某些用户才具有对指定存储过程的使用权 
6.  （只需要看前5条：通常存储过程有助于提高应用程序的性能。当创建，存储过程被编译之后，就存储在数据库中。 但是，MySQL实现的存储过程略有不同。 MySQL存储过程按需编译。 在编译存储过程之后，MySQL将其放入缓存中。 MySQL为每个连接维护自己的存储过程高速缓存。 如果应用程序在单个连接中多次使用存储过程，则使用编译版本，否则存储过程的工作方式类似于查询。
7.  存储过程有助于减少应用程序和数据库服务器之间的流量，因为应用程序不必发送多个冗长的SQL语句，而只能发送存储过程的名称和参数。
8.  存储的程序对任何应用程序都是可重用的和透明的。 存储过程将数据库接口暴露给所有应用程序，以便开发人员不必开发存储过程中已支持的功能。
9.  存储的程序是安全的。 数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。)

MySQL存储过程的缺点

1.  如果使用大量存储过程，那么使用这些存储过程的每个连接的内存使用量将会大大增加。 此外，如果您在存储过程中过度使用大量逻辑操作，则CPU使用率也会增加，因为数据库服务器的设计不当于逻辑运算。
2.  存储过程的构造使得开发具有复杂业务逻辑的存储过程变得更加困难。
3.  很难调试存储过程。只有少数数据库管理系统允许您调试存储过程。不幸的是，MySQL不提供调试存储过程的功能。
4.  开发和维护存储过程并不容易。开发和维护存储过程通常需要一个不是所有应用[程序开发][Link 2]人员拥有的专业技能。这可能会导致应用程序开发和维护阶段的问题。
5.  内存，CPU开销大，复杂逻辑困难，不便调试，开发和维护不易

MySQL存储过程有自己的优点和缺点。开发应用程序时，您应该决定是否应该或不应该根据业务需求使用存储过程。

**问题：**

1.DELIMITER $$中的$$是什么意思？
    
    在定义过程时，使用DELIMITER $$命令将语句的结束符号从分号 ; 临时改为两个$$，使得过程体中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释。既为了避免存储过程中分号(";")结束语句。
    
    （醉了，csdn编辑器对$$相当感冒。）
    
    2.SET @dd=4;如此声明变量为什么必须加@符号？
    
    规定。设置全局变量：set @adc=33；

[http_tool.oschina.net_apidocs_apidoc_api_mysql-5.1-zh]: http://tool.oschina.net/apidocs/apidoc?api=mysql-5.1-zh
[Java]: https://www.2cto.com/kf/ware/Java/
[Python]: https://www.2cto.com/kf/web/Python/
[PHP]: https://www.2cto.com/kf/web/php/
[Link 1]: https://www.2cto.com/os/
[Link 2]: https://www.2cto.com/kf