当前位置：首页 > news >正文

sqlserver函数与过程（二）

news 2025/6/30 16:27:37

过程

SQLserver 过程是具有特定功能，可多次对数据表操作的独立模块。
返回值通常用return 返回整数 0，1…。(可选）
也可通过output 参数或select 语句返回结果集。

1.过程的定义

本过程定义了一个过程，输入一个动态SQL语句，将结果行集组成一个SQL命令串，返回结果。该过程使用Function 是不能完成的。

CREATE PROCEDURE [dbo].[getCmd]@CursorStr nvarchar(max),@Str nvarchar(max)='' out
as
beginset nocount on;declare @s nvarchar(max)='';set @CursorStr=' declare Cur cursor for '+@CursorStr;exec sp_executeSql @CursorStr;open Cur;FETCH NEXT FROM Cur INTO @s;WHILE @@FETCH_STATUS = 0BEGINSET @Str=@Str+@s;FETCH NEXT FROM  Cur INTO @s; endclose Cur;--关闭标量库deallocate Cur;--释放光标空间  return 0;
end

2.过程的调用

DECLARE @IndexSQL NVARCHAR(MAX);
declare @ic nvarchar(max);
set @ic=@oldDb+'.sys.index_columns';
--declare @c nvarchar(max);
set @c=@oldDb+'.sys.columns';
declare @i nvarchar(max);
set @i=@oldDb+'.sys.indexes';
--declare @t nvarchar(max);
set @t=@oldDb+'.sys.tables';
SET @IndexSQL = '';
set @cmd='
SELECT  ''CREATE '' + CASE WHEN i.is_unique = 1 THEN ''UNIQUE '' ELSE '''' END + i.type_desc + '' INDEX '' + QUOTENAME(i.name) + '' ON '+@newDb+'.dbo.'' + QUOTENAME(t.name) + '' ('' + STUFF((SELECT '', '' + QUOTENAME(c.name)FROM '+@ic+' icJOIN '+@c+' c ON ic.column_id = c.column_id AND ic.object_id = c.object_idWHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0ORDER BY ic.key_ordinalFOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''), 1, 2, '''') + '')'' + CASE WHEN EXISTS (SELECT *FROM '+@ic+' icJOIN zwdb.sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_idWHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1) THEN '' INCLUDE ('' + STUFF((SELECT '', '' + QUOTENAME(c.name)FROM '+@ic+' icJOIN '+@c+' c ON ic.column_id = c.column_id AND ic.object_id = c.object_idWHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1ORDER BY ic.index_column_idFOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''), 1, 2, '''') + '')'' ELSE '''' END + '';'' + CHAR(13) + CHAR(10) COLLATE Chinese_PRC_CI_AS AS combined_column 
FROM '+@i+' i
JOIN '+@t+' t ON i.object_id = t.object_id
WHERE i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND t.is_ms_shipped = 0 AND i.index_id > 0;';
exec zwdb.dbo.getCmd @CursorStr=@cmd,@Str=@IndexSQL output ;
IF @IndexSQL <> ''EXEC sp_executesql @IndexSQL;

总结

1. 数据修改能力

(1)标量函数：

不允许修改数据（如 INSERT、UPDATE、DELETE）。
只能读取数据，保持函数的确定性（相同输入始终返回相同输出）。

(2)存储过程：

允许修改数据，支持事务处理（如 BEGIN TRANSACTION）。
可执行任何 T-SQL 语句，包括动态 SQL。

2. 性能与优化

(1)标量函数：

性能较低，尤其在 WHERE 子句中频繁调用时，可能导致全表扫描。
适合简单计算，避免复杂逻辑。

(2)存储过程：

性能较高，执行计划可缓存，减少编译开销。
适合复杂业务逻辑（如批量数据处理）。

3. 应用场景

(1)标量函数：

数据计算（如格式化日期、字符串处理）。
在查询中作为表达式使用（如 SELECT、JOIN 条件）。

(2)存储过程：

业务逻辑封装（如用户认证、订单处理）。
数据修改操作（如批量插入、事务处理）。
跨数据库操作或调用外部资源（如调用 API）。

4. 其他差异

特性	标量函数	存储过程
事务支持	不支持	支持（可使用 BEGIN TRANSACTION）
动态 SQL	不允许	允许
权限控制	可通过 GRANT EXECUTE 授权	同上
在视图中使用	允许	不允许（视图中不能直接调用存储过程）
结果集返回	不支持（只能返回单个值）	支持（通过 SELECT 语句）

总结

场景	推荐使用标量函数	推荐使用存储过程
简单计算（如数学公式）	✅	❌
查询中作为表达式	✅	❌
数据修改（INSERT/UPDATE）	❌	✅
复杂业务逻辑	❌	✅
事务处理	❌	✅
动态 SQL	❌	✅