存储过程基础

?什么是存储过程

就是写在SQL中的方法,用于完成特定功能的SQL语句集合。

?优点
  • 执行效率很高

​ 存储过程是预编译的,即创建时编译,而SQL语句是执行一次,编译一次。调用存储过程可以大大减少同数据库的交互次数。

存储过程执行的时候,只需要call存储过程,而存储过程是sql语句的集合,减少了与数据库的连接次数,降低了数据库的压力。

  • 代码复用

存储过程就是公用的sql方法。

参数化的存储过程可以防止SQL注入式的攻击,而且可以将Grant、Deny以及Revoke权限应用于存储过程。

?缺点
  • 移植性非常差

多平台不兼容(Mysql\Oracle)

  • 代码可读性差

实现一个简单的逻辑,代码会非常长。

?用途
  • 造测试数据

可以使用存储过程,往表里造几百万条数据。

  • 数据同步

两个表之间按照一定的业务逻辑进行数据同步。

  • 数据挖掘
?注意事项

数据量大的时候(10万+),一定要做压力测试,有些存储过程在大数据量的情况下才会出现问题。
如果插入或者更新的次数比较多,为了提高效率,可以执行一万次,再commit一次。
如果先插入记录,没有commit,再对这条记录进行更新,会引起死锁。如果先后对同一笔记录进行更新,又没有commit,也会引起死锁。因为后一条语句会等待前一条语句提交。如果出现这种情况,则需要一条条commit。
不要忘记在存储过程里写commit。

可能遇到的问题:
  • 同一个服务器跨库:

    • 1、设置用户权限查询
    • 2、先建立个视图,在视图里跨库连表,再在存储过程中调用视图
  • 跨服务器

    目前只能通过编程语言来实现,不能通过底层的存储过程跨服务器编程

使用语法(Mysql)

基础用法
  • 创建
    1,参数是可选的
    2,参数分为输入参数、输出参数
    3,输入参数允许有默认值
    
    CREATE DEFINER=`root`@`%` PROCEDURE `update_file_search_procedure`( )
    @参数1  数据类型 = 默认值,
        …… ,
    @参数n  数据类型 OUTPUT 出参
    AS
    SQL语句	
    BEGIN
      #Routine body goes here...
    END
    
  • 删除
    DROP PROCEDURE 存储过程名
    
  • 调用

    一种是参数按次序调用,一种是按参数名

    EXEC 过程名 参数值1,参数值2,....,@a output   
    EXEC 过程名 参数1=参数值1,参数2=参数值2....
    
    参数有默认值时:
    exec 过程名 #都用默认值 
    exec 过程名 参数值1,  #第二个用默认值
    exec 过程名 参数2=参数值2.... #第一个用默认值
    #设置变量
    declare @out int
    
    #有出参
    exec usp_pp @canshu= @out output
    #如果是按次序调用可写成
    exec usp_pp @out output
    #打印参数值
    print @out
    
    
  • 参数

    参数分为局部参数@、全局参数@@或者没有@, 可以设置默认值

    DECLARE 参数名 参数类型;
    DECLARE a INT;
    DECLARE a INT default 0;
    
游标的使用
  • 基本用法
#游标的使用 遍历表
#声明游标
DECLARE cursor_name CURSOR FOR
	#游标处理语句
	select cloumn1, cloumn2 from table_name
	
#开启游标
open cursor_name;

DECLARE @变量1 变量类型
DECLARE @变量2 变量类型

CLOSE cursor_name       --关闭游标
  • 3中循环方式 while, repeat, loop
    • while

      #开启游标
      OPEN cursor_name
        #先将游标中的数据存入到变量中
        FETCH cursor_name into 变量1,变量2;
        #当a不等于1的时候执行内容操作
        while a<>1 do  
           ... #逻辑操作
        #再在循环中将游标中的值传入到变量中
        FETCH cursor_name into 变量1,变量2;
        end while
      #释放游标
      CLOSE cursor_name;
      
    • repeat

      #打开游标
      open cursor_name; 
      	#进入循环
      	REPEAT 
      	FETCH userCur into uid;  #赋值给变量
      	IF NOT DONE THEN  #当done!=1继续循环
      	.....#搞事情
      	END IF;
      	UNTIL done 
      	END REPEAT;   #直到done=1时结束循环
      #释放游标
      CLOSE cursor_name;
      
    • loop

      #开启游标
      OPEN  cursor_name; 
      	#定义loop循环 
      	loop_name: loop
      	#将游标中的cloum1,cloum2 、给到变量1, 变量2 
      	FETCH cursor_name INTO 变量1, 变量2;
      	# update执行的条件
      	IF b <4 THEN
          	...#搞事情
          END IF;
          IF b>=7 THEN
          	#停止LOOP 循环
          	LEAVE loop_name;
          END IF;
          #停止LOOP 循环
          END LOOP loop_name;
      #释放游标
      CLOSE cursor_name; 
      
      
    • DECLARE … HANDLER
      DECLARE handler_action HANDLER
          FOR condition_value [, condition_value] ...
          statement
      
      handler_action: {
          CONTINUE
        | EXIT
        | UNDO
      }
      
      condition_value: {
          mysql_error_code
        | SQLSTATE [VALUE] sqlstate_value
        | condition_name
        | SQLWARNING
        | NOT FOUND
        | SQLEXCEPTION
      }
      

      DECLARE ... HANDLER语句指定处理一个或多个条件的处理程序。如果出现这些条件之一,则*statement*执行指定的。 *statement*可以是简单的语句,例如,也可以是使用and 编写的复合语句。 SET *var_name* = *value*``BEGIN``END

      处理程序声明必须出现在变量或条件声明之后。

      该*handler_action*值指示处理程序在执行处理程序语句后采取的操作:

      • CONTINUE: 当前程序继续执行。
      • EXIT: 对BEGIN ... END声明处理程序的复合语句执行终止 。即使条件发生在内部块中也是如此。
      • UNDO: 不支持。

      CONTINUE 与 EXIT 对比

      CONTINUE: 发送错误时继续执行后续代码
      EXIT: 发生错误时退出当前代码块(可能是子代码块或者main代码块)

      DECLARE handler_action HANDLER
          FOR condition_value [, condition_value] ...
          statement
      
      handler_action: {
          CONTINUE
        | EXIT
        | UNDO
      }
      
      condition_value: {
          mysql_error_code
        | SQLSTATE [VALUE] sqlstate_value
        | condition_name
        | SQLWARNING
        | NOT FOUND
        | SQLEXCEPTION
      }
      

      DECLARE ... HANDLER语句指定处理一个或多个条件的处理程序。如果出现这些条件之一,则*statement*执行指定的。 *statement*可以是简单的语句,例如,也可以是使用and 编写的复合语句。 SET *var_name* = *value*``BEGIN``END

      处理程序声明必须出现在变量或条件声明之后。

      该*handler_action*值指示处理程序在执行处理程序语句后采取的操作:

      • CONTINUE: 当前程序继续执行。
      • EXIT: 对BEGIN ... END声明处理程序的复合语句执行终止 。即使条件发生在内部块中也是如此。
      • UNDO: 不支持。

使用示例:

create
    definer = root@`%` procedure update_circle_recommend_procedure()
BEGIN
    #定义参数
    DECLARE args varchar(35);

    #声明游标
    DECLARE circle_info CURSOR FOR
        select cloumn1 , ... from table_name where ...;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;

    #开启游标
    open circle_info;
    #定义循环 read_loop
    read_loop : LOOP
        FETCH circle_info into args;

        IF done THEN
            LEAVE read_loop;
        END IF;

        if updateFlag >= 1 then
            update ...
        else
            insert into ...
        end if;
    END LOOP read_loop;
    #释放游标
    CLOSE circle_info;
END

常用系统存储过程

系统存储过程,由系统定义,存放在master数据库中,名称以“sp_”开头或”xp_”开头
自定义存储过程,由用户在自己的数据库中创建的存储过程,以”usp_”开头

名称作用
sp_attach_db数据库附加到服务器上
sp_attach_single_file_db将只有一个数据文件的数据库附加到当前服务器上
sp_changedbowner更改当前数据库所有者
sp_changeobjectowner更改当前数据库中对象的所有者
sp_column_privileges返回当前环境中单个表的列的特权信息
sp_help报告有关数据库对象、用户定义数据类型或SQL Server 提供的数据类型的信息
sp_helptext显示用户定义的默认值,未加密的T-SQL存储过程、用户定义的T-SQL 函数、触发器、计算列、CHECK约束、视图或系统对象
sp_rename更改用户创建对象的名称、如表、索引、列、别名数据类型
sp_renamedb更改数据库名称
sp_tables显示可以在 SELECT 查询语句的FROM 子句中的对象
sp_stored_procedures列出所有可用的存储过程
sp_password修改而且只能修改标准登录的密码
sp_who查看当前用户、会话和进程信息
sp_monitor显示有关 SQL Server 的统计信息
sp_depends查看详细信息

未完待续…

参考文章:

官方手册:https://dev.mysql.com/doc/refman/5.7/en/declare-handler.html

https://blog.csdn.net/zsx157326/article/details/52197949?utm_medium=distribute.pc_relevant.none-task-blog-2defaultbaidujs_baidulandingword~default-0.control&spm=1001.2101.3001.4242

https://blog.csdn.net/wuxinwudai/article/details/54999071

https://blog.csdn.net/hatwn/article/details/84714179

https://blog.csdn.net/duduyingya/article/details/119568713

Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐