投稿
  1. 首页
  2. sql

SQL系列——存储过程

code • 2022-5-21 • sql • 阅读 36

SQL系列——存储过程,第1张

存储过程 存储过程基础？什么是存储过程？优点执行效率很高降低网络通信量代码复用安全性 ？缺点移植性非常差代码可读性差 ？用途造测试数据数据同步数据挖掘 ？注意事项可能遇到的问题： 使用语法（Mysql）基础用法创建删除调用参数 游标的使用基本用法3中循环方式 while, repeat, loopDECLARE ... HANDLER 使用示例：常用系统存储过程

存储过程基础 ？什么是存储过程

就是写在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

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/sjk/991514.html

赞 (0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
code code 管理员组
0 0
上一篇 2022-05-21
下一篇 2022-05-21

发表评论

登录后才能评论

评论列表（0条）

保存