（二十四）存储过程

/*前言：存储过程，非常类似于Java/C#语言中的方法，它可以重复调用。当存储过程执行一次后，可以将语句缓存起来，这样下次执行的时候直接用缓存中的语句。这样可以大大滴提高存储过程的性能。
1.存储过程概念：a.存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行;b.存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。
c.由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需要提供存储过程名和必要的参数信息，所以在一定程度上也可以减少网络流量、简单网络负担。
2.存储过程的特点：
A.存储过程允许标准组件式编程：存储过程创建后可以在程序中被多次调用执行，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，但对应用源程序却毫无影响，从而极大的提高了程序的可移植性。
B.存储过程能够实现较快的执行速度：如果某一操作包含大量的T-SQL语句代码，分别被多次执行，那么存储过程要比批处理的执行速度快得多；因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的存储计划。而批处理的T-SQL语句每次执行都需要预编译和优化，所以速度就要慢一些。
C.存储过程减轻网络流量：对于同一个针对数据库对象的操作，如果这一操作所涉及到的T-SQL语句被组织成一个存储过程，那么当客户机上调用该存储过程时，网络中传递的只是该调用语句，否则将会是多条SQL语句。从而减轻了网络流量，降低了网络负载。
D.存储过程可被作为一种安全机制来充分利用：系统管理员课可以对执行的某一个存储过程进行权限限制，从而能够实现对某些数据访问的限制，避免非授权用户对数据的访问，保证数据的安全。*/
--（1）系统存储过程
/*系统存储过程是系统创建的存储过程，目的在于能够方便的从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务；系统存储过程主要存储在master数据库中，以“sp”下划线开头的存储过程；尽管这些系统存储过程在master数据库中，但我们在其他数据库还是可以调用系统存储过程。有一些系统存储过程会在创建新的数据库的时候被自动创建在当前数据库中。*/
--常用的系统存储过程有：
exec sp_databases; --查看数据库信息
exec sp_tables;--查看当前表表信息
exec sp_columns StudentsA;--查看表的列信息
exec sp_helpIndex StudentsA;--查看表的索引
exec sp_helpConstraint StudentsA;--查看表的约束
exec sp_stored_procedures;--查询数据的存储过程创建、定义语句
exec sp_helptext 'sp_stored_procedures';--查询数据的存储过程创建、定义语句(text输出）
exec sp_rename StudentsA,stuInfo;--修改表(/索引、列的名称)
exec sp_rename stuInfo,StudentsA;--修改表(/索引、列的名称)
exec sp_renamedb MyDatabaseOne,myDB;--修改数据库名称
exec sp_defaultdb 'master','myDB';--更改登录名的默认数据库
exec sp_helpdb;--数据库帮助，查询数据库信息
exec sp_helpdb master;--查询某个数据库的信息
exec sp_helpdb Students;

--(2)系统存储过程示例：
--表重命名
exec sp_rename 'studentC','student';
select * from student;
--列重命名
exec sp_rename 'StudentsA.年龄','Age','column';
exec sp_help 'StudentsA';
exec sp_columns StudentsA;--查看表的列信息
--重命名索引
exec sp_rename N'StudentsA.idx_cid',N'idx_cidd',N'index';
exec sp_help 'StudentsA';
exec sp_helpIndex StudentsA;--查看表的索引
--查询所有存储过程
select * from sys.objects where type='P';
select * from sys.objects where type_desc like '%pro%' and name like 'sp%';--查询type_desc这个属性值中间带pro以及name以sp开头的存储过程

--（3）用户自定义存储过程
--A：创建语法
/*   create proc | procedure pro_name
            [{@参数数据类型} [=默认值] [output],
						 {@参数数据类型} [=默认值] [outpur],
						 ...
						 ]
      as 
			       SQL_statements 						 
*/
--B:创建不带参数的存储过程
--创建存储过程
if(exists(select * from sys.objects where name='proc_get_student'))
   drop proc proc_get_student 
go 
create proc proc_get_student 
as 
   select Snc from StudentsA;

select * from StudentsA;		--原始表
exec proc_get_student;   --调用存储过程

--C：修改存储过程
alter proc proc_get_student 
as 
   select 姓名 from StudentsA;
	 
exec proc_get_student;   --调用存储过程	 

--D:带参数的存储过程
--带参数存储过程
if(object_id('proc_find_stu','P') is not null)
   drop proc proc_find_stu 
go 
create proc proc_find_stu(@startId int,@endId int)
as  
   select * from StudentsA where Snc between @startId and @endId 
go 

select * from StudentsA where Snc between 1 and 3;--与下面存储过程结果等价
exec proc_find_stu 1,3;	 

--E:带通配符参数的存储过程
--带通配符参数的存储过程
if (object_id('proc_findStudentByName', 'P') is not null)
    drop proc proc_findStudentByName
go
create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')
as
    select * from StudentsA where Name like @name or name like @nextName;
go

exec proc_findStudentByName;	 
exec proc_findStudentByName '%葛%','刘%';
exec proc_findStudentByName '%葛%','%操';

--F:带输入参数in的存储过程(1)
if (object_id('proc_GetEmployeeInfo', 'P') is not null)
    drop proc proc_GetEmployeeInfo
go				 
create procedure proc_GetEmployeeInfo(@employeeID int)--输入参数（默认是In类型）
as 
begin 
     select * from StudentsA where Snc=@employeeID;
end;		
--调用上面存储过程
exec proc_GetEmployeeInfo 1;--查询ID为1的员工信息 

--F：带输出参数out的存储过程（2）
if(object_id('proc_GetTotal','P')is not null)
    drop proc proc_GetTotal
go 
create procedure proc_GetTotal 
    @totalCount int output --输出参数
as 
begin 
    select @totalCount=Count(*) from StudentsA;
end;
--调用上面存储过程
declare @count int;
exec proc_GetTotal @count output;
select @count as '员工总数';	

select * from StudentsA;--确认是否存在数据

--F：带输入输出参数inout的存储过程（3）		
if(object_id('proc_Adjust','P')is not null)
    drop proc proc_Adjust 
go 
create procedure proc_Adjust(
                 @accountID int,   --输入:账户ID
								 @adjust money,    --输入：调整金额（正数存款，负数取款）
								 @newBalance money output --输出：新余额（同时也是输入参数）
								 )
as 
begin 
    --先获取当前余额（使用输入参数）
		select @newBalance=Balance from StudentsA where Snc=@accountID;
		--调整金额
		set @newBalance =@newBalance+@adjust;
		--更新数据库
		update StudentsA set Balance=@newBalance where Snc=@accountID;
end;
--调用上面存储过程
declare @balance money=0;  --初始化变量（作为输入）
exec proc_Adjust 
     @accountID=1,	--输入ID
		 @adjust=1000, --存入1000元
		 @newBalance=@balance output;
select @balance as '新余额'; --显示调整后的余额			

--G；不缓存存储过程
--with recompile 不缓存
if(object_id('proc_temp','P')is not null)
     drop proc proc_temp 
go 
create proc proc_temp 
with recompile 
as 
     select * from StudentsA;
go 

--调用上面存储过程
exec proc_temp 		

--H:加密存储过程
--加密：with encryption 
if(object_id('proc_temp_encryption','P')is not null)
      drop proc proc_temp_encryption 
go 
create proc proc_temp_encryption 
with encryption 
as 
     select * from StudentsA;
go 

exec proc_temp_encryption;
exec sp_helptext 'proc_temp';
exec sp_helptext 'proc_temp_encryption';--调用存储过程的定义的时候无法查看

--I：带游标的存储过程（批量调整员工薪资）：假设我们需要给特定部门的所有员工按不同规则调薪：
if(object_id('proc_Adjust','P')is not null)
     drop proc proc_Adjust 
go 
create procedure proc_Adjust(
     @deptID int,   --输入：部门ID
		 @adjustmentType varchar(20), --输入：调整类型（'percent'百分比/'fixed'固定值)
		 @adjustmentValue decimal(10,2), --输入：调整值（百分比或固定金额）
		 @totalAffected int output, --输出：受影响员工数
		 @totalIncrease decimal(12,2) output) --输出：总薪资增加额
as 
begin 
     --声明变量
		 declare @empID int;
		 declare @currentSalary decimal(10,2);
		 declare @newSalary decimal(10,2);
		 --初始化输出参数
		set @totalAffected=0;
		set @totalIncrease=0;
		
		--声明游标：获取该部门所有员工ID和当前薪资
		declare emp_cursor cursor for 
		        select 	EmployeeID,Salary from 	StudentsA where DepartmentID=@deptID	and IsActive=1;
		--打开游标
		open emp_cursor;
	  --获取第一行数据
		fetch next from emp_cursor into @empID,@currentSalary;	
		--循环处理每一行
		while @@fetch_status=0
		begin 
		     --根据调整类型计算新薪资
					if @adjustmentType='percent'
					   set @newSalary=@currentSalary*(1+@adjustmentValue/100);
				  else if  @adjustmentType='fixed'
					   set @newSalary=@currentSalary+@adjustmentValue;
				  else 
						 set @newSalary=@currentSalary;--无效类型则不调整
				 
				 --更新员工薪资
				 update StudentsA set Salary=@newSalary where EmployeeID=@empID;
				 
				 --累加统计信息
				 set @totalAffected=@totalAffected+1;
				 set @totalIncrease=@totalIncrease+(@newSalary-@currentSalary);
				 
				 --获取下一行
				 fetch next from emp_cursor into @empID,@currentSalary;
	  end
		
		--关闭并释放游标
	  close emp_cursor;
		deallocate emp_cursor;
		
		--输出汇总信息
		print '薪资调整完成：';
		print '  部门ID：'+cast(@deptID as varchar);
		PRINT '  调整类型: ' + @adjustmentType;
    PRINT '  调整值: ' + CAST(@adjustmentValue AS VARCHAR);
    PRINT '  受影响员工数: ' + CAST(@totalAffected AS VARCHAR);
    PRINT '  总薪资增加额: ' + CAST(@totalIncrease AS VARCHAR(20));
end;		

--调用存储过程
declare @affected int, @increase decimal(12,2);
exec proc_Adjust 
    @deptID = 1, 
    @adjustmentType = 'percent', 
    @adjustmentValue = 10,
    @totalAffected = @affected output,
    @totalIncrease = @increase output;		
--查看结果
select @affected as '受影响员工数',@increase as '总薪资增加额';	
/*注意：
游标关键步骤解析：
1.声明游标：DECLARE cursor_name CURSOR FOR select_statement
2.打开游标：OPEN cursor_name
3.获取数据：FETCH NEXT FROM cursor_name INTO variables
4.处理循环：WHILE @@FETCH_STATUS = 0 (0表示成功获取行)
5.关闭游标：CLOSE cursor_name
6.释放游标：DEALLOCATE cursor_name

实际应用场景：
1.批量数据处理（如薪资调整、状态更新）
2.需要逐行复杂计算的场景
3.基于前一行结果决定下一行操作的场景
4.生成复杂报表时逐行处理数据*/