sqlserver 函数

目录

1. 函数练习
2. [判断（数据库、表、函数、存储过程等）是否存在]
3. CREATE FUNCTION
4. EXECUTE AS

• eg.
  

USE master
GO

IF EXISTS(SELECT * FROM SYSDATABASES WHERE name='LeetCode')
	BEGIN
		DROP DATABASE LeetCode
		SELECT 1 AS deleted_leetcode
	END
CREATE DATABASE LeetCode
USE LeetCode
GO

SELECT *
	FROM SYSOBJECTS
	WHERE xtype='U'

EXEC sp_help

-- 函数
-- 第2高工资
USE master
GO

IF EXISTS(SELECT * FROM sysdatabases WHERE name='LeetCode')
	DROP DATABASE LeetCode
GO
CREATE DATABASE LeetCode
GO
USE LeetCode
GO

SELECT * FROM sysobjects WHERE xtype='U' OR xtype='FN'

IF EXISTS(SELECT * FROM sys.objects WHERE name='Salary')
	DROP TABLE Salary
GO
CREATE TABLE Salary
(
	Id INT IDENTITY PRIMARY KEY,
	Salary INT
)
INSERT INTO Salary VALUES
	(100), (200), (300)
GO

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE XTYPE='FN' 
			AND name='getNthHighestSalary')
	DROP FUNCTION getNthHighestSalary
GO
CREATE FUNCTION getNthHighestSalary(@N INT)
RETURNS INT
AS
BEGIN
	DECLARE @num INT
	-- 当输入的N值<1时，默认为1  --用户输入N的极限值逻辑处理
	IF @N < 1
		SET @N=1
	SET @num = nullif((SELECT Salary
				FROM Salary
				GROUP BY Salary
				ORDER BY Salary DESC
				OFFSET @N-1 ROWS FETCH NEXT 1 ROWS ONLY),null)
	RETURN @num
END
GO

 -- dbo & '' connot ignore them.
SELECT dbo.getNthHighestSalary(2) AS 'getNthHighestSalary(2)'  
SELECT 1 AS Other  -- Company
-- if table Salary is null
DELETE FROM Salary
SELECT * FROM Salary  -- Test is right

• sqlserver（数据库、表、函数、存储过程等）判断是否存在
• 官方 CREATE FUNCTION
  eg.1

-- 最简单的用法
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE XTYPE='fn' AND name='func1')
	DROP FUNCTION func1
GO
CREATE FUNCTION func1(@date_into VARCHAR(8))
RETURNS VARCHAR(20)
AS
BEGIN
	DECLARE @result_name VARCHAR(20)
	SELECT @result_name = @date_into
	RETURN @result_name
END
GO
SELECT dbo.func1('a')  -- 注意 dbo.
-------
'a'

eg.2 摘

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE XTYPE='FN' AND name='ISOweek')
	DROP FUNCTION ISOweek
GO
CREATE FUNCTION ISOweek (@DATE datetime)  
RETURNS int  
--WITH EXECUTE AS CALLER  
AS  
BEGIN  
     DECLARE @ISOweek int;  
     SET @ISOweek= DATEPART(wk,@DATE)+1  
          -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104');  
--Special cases: Jan 1-3 may belong to the previous year  
     IF (@ISOweek=0)   
          SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1   
               AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;  
--Special case: Dec 29-31 may belong to the next year  
     IF ((DATEPART(mm,@DATE)=12) AND   
          ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))  
          SET @ISOweek=1;  
     RETURN(@ISOweek);  
END;  
GO  
-- 设置一周的第一天 SET DATEFIRST
-- SELECT @@DATEFIRST --查询当前设置的一周的第1天
-- SELECT user_name() --查询用户组
SET DATEFIRST 1;  
SELECT dbo.ISOweek(CONVERT(DATETIME,'12/26/2004',101)) AS 'ISO Week';
----------
ISO Week
 52

• 了解EXECUTE AS
  
  eg.