sql server 查询存储过程用到的表

存储过程

• 存储过程基础

• ？什么是存储过程
• ？优点

• 执行效率很高
• 降低网络通信量
• 代码复用
• 安全性

• ？缺点

• 移植性非常差
• 代码可读性差

• ？用途

• 造测试数据
• 数据同步
• 数据挖掘

• ？注意事项
• 可能遇到的问题：

• 使用语法（Mysql）

• 基础用法

• 创建
• 删除
• 调用
• 参数

• 游标的使用

• 基本用法
• 3中循环方式 while, repeat, loop
• DECLARE ... 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