ch04 sql 创建方法

最新推荐文章于 2026-01-01 02:01:16 发布

原创最新推荐文章于 2026-01-01 02:01:16 发布 · 479 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #assembly #function #产品 #table #insert

本文介绍了使用 SQL Server 进行数据库查询的各种实用技巧，包括如何利用系统函数 fn_dblog 获取数据库日志信息，如何通过 sysprocesses 和 dm_exec_requests 视图监控当前运行的进程，如何创建自定义函数来提高查询效率，以及如何使用不同方法返回表格数据等。

--8-1 USE Northwind SELECT * FROM ::fn_dblog('', '') GO --8-2 USE Northwind SELECT * FROM ::fn_dblog('', '') WHERE [Begin Time] >= '02/01/07' GO --9-1 SELECT * FROM master.dbo.sysprocesses --9-2 SELECT * FROM sys.dm_exec_requests --9-3 DECLARE @Handle varbinary(64); SELECT @handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = @@SPID SELECT * FROM ::fn_get_sql(@Handle); GO --10-1 方法 USE Northwind GO CREATE FUNCTION dbo.CountOFProductsByCategory(@pCategoryID INT) RETURNS INT AS BEGIN RETURN ( SELECT COUNT(*) FROM dbo.Products WHERE CategoryID = @pCategoryID ) END GO --10-2 SELECT COUNT(*) FROM Products WHERE CategoryID ='1' GO PRINT '属于产品类型的产品有'+CONVERT(VARCHAR(3),dbo.CountOFProductsByCategory(1))+'种。' GO --10-3 USE Northwind GO PRINT '属于产品类型的产品有' PRINT '属于产品类型的产品有'+CONVERT(VARCHAR(3),dbo.CountOFProductsByCategory(DEFAULT))+'种。' GO --11-1 方法返回 TABLE USE Northwind GO CREATE FUNCTION dbo.ProductsByCategory(@pCategoryID INT) RETURNS TABLE AS RETURN SELECT ProductID,CategoryID,ProductName,UnitPrice FROM dbo.Products WHERE CategoryID = @pCategoryID GO --11-2 USE Northwind GO Select * FROM DBO.ProductsByCategory('1') --12-1 方法返回 TABLE2 USE Northwind GO CREATE FUNCTION dbo.CategoryInfo() RETURNS @CategoryInfo TABLE ( CategoryID INT, NumberOfProducts INT ) AS BEGIN DECLARE @lminCID INT DECLARE @lmaxCID INT DECLARE @CountsOfRec INT SELECT @lminCID =MIN(Categoryid) FROM Products SELECT @lmaxCID =MAX(Categoryid) FROM Products SELECT @CountsOfRec = Count(*) FROM Products WHERE CategoryID = @lminCID WHILE @lminCID <= @lmaxCID BEGIN INSERT INTO @CategoryInfo VALUES(@lminCID,@CountsOfRec) SET @lminCID =@lminCID+1 SELECT @CountsOfRec = Count(*) FROM Products WHERE CategoryID = @lminCID END RETURN END --12-2 SELECT * FROM dbo.CategoryInfo() --12-3 SELECT * FROM dbo.CategoryInfo() WHERE CategoryID = 6 --17-1 select * from sys.assembly_modules If exists(select * from sys.assembly_modules where assembly_class = 'ClrAggregate.AvgUnitPriceOfOrderDetails') drop AGGREGATE AvgUnitPriceOfOrderDetails GO select * from sys.assemblies --17-2 IF EXISTS (select * from sys.assemblies where name = N'ClrAggregate') DROP ASSEMBLY ClrAggregate; GO