数据库基础（七）：用户自定义数据类型与标量值、内嵌表值、多语句表值函数等用户定义函数的创建、使用、删除

USE jxsk
GO
EXEC sp_addtype Idnum,'CHAR(6)','NOT NULL'
GO

USE jxsk
GO
CREATE TABLE STUDENT(
SNO IDNUM,
SN CHAR(11),
SSEX CHAR(2),
SAGE TINYINT)
GO
CREATE TABLE TEACHER(
TNO IDNUM,
TN CHAR(11),
TSEX CHAR(2),
TAGE TINYINT,
TPROF CHAR(11))
GO

USE jxsk
GO
ALTER TABLE STUDENT ALTER COLUMN SN NAMEPERSON
GO
ALTER TABLE TEACHER ALTER COLUMN TN NAMEPERSON
GO

USE jxsk
GO
ALTER TABLE STUDENT ALTER COLUMN SN CHAR(10) NOT NULL
GO
ALTER TABLE TEACHER ALTER COLUMN TN CHAR(10) NOT NULL
GO
EXEC sp_droptype NAMEPERSON
GO

USE jxsk
GO
ALTER TABLE STUDENT ALTER COLUMN SNO CHAR(10) NOT NULL
GO
ALTER TABLE TEACHER ALTER COLUMN TNO CHAR(10) NOT NULL
GO

-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName> 
(
  -- Add the parameters for the function here
  <@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS <Function_Data_Type, ,int>
AS
BEGIN
  -- Declare the return variable here
  DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>
  -- Add the T-SQL statements to compute the return value here
  SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>
  -- Return the result of the function
  RETURN <@ResultVar, sysname, @Result>
END
GO

CREATE FUNCTION SCORE_FUN(@SNAME_IN CHAR(8),
@CNAME_IN CHAR(10))
RETURNS TINYINT
AS
BEGIN
DECLARE @SCORE_OUT TINYINT
SELECT @SCORE_OUT=SCORE FROM SC,S,C
WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND SN=@SNAME_IN AND CN=@CNAME_IN
RETURN(@score_out)
END

USE jxsk
GO
DECLARE @S_SCORE TINYINT
EXEC @S_SCORE=DBO.SCORE_FUN '钱尔','编译原理'
PRINT'钱尔的编译原理成绩是'+STR(@S_SCORE)
GO

USE jxsk
GO
CREATE FUNCTION S_SCORE_FUN(@SNAME_IN CHAR(8))
RETURNS TABLE
AS
RETURN (SELECT CN,SCORE FROM S,SC,C
WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND SN=@SNAME_IN)
GO

USE jxsk
GO
SELECT*FROM S_SCORE_FUN('钱尔')
GO

USE jxsk
GO
CREATE FUNCTION ALL_SCORE_FUN(@CNAME_IN CHAR(10))
RETURNS @ALL_SCORE_TAB TABLE(SNO CHAR(2) PRIMARY KEY,
SN CHAR(8) NOT NULL,SEX CHAR(2),SCORE TINYINT)
AS
BEGIN
INSERT @ALL_SCORE_TAB
SELECT S.SNO,SN,SEX,SCORE
FROM S,SC,C
WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND CN=@CNAME_IN
RETURN
END
GO

USE jxsk
GO
SELECT*FROM ALL_SCORE_FUN('微机原理')
GO

USE jxsk
GO
SELECT*FROM ALL_SCORE_FUN('数据库')
GO

USE [jxsk]
GO
/****** Object:  UserDefinedFunction [dbo].[SCORE_FUN]    Script Date: 05/21/2019 19:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[SCORE_FUN](@SNAME_IN CHAR(8),
@CNAME_IN CHAR(10))
RETURNS TINYINT
AS
BEGIN
DECLARE @SCORE_OUT TINYINT
SELECT @SCORE_OUT=SCORE FROM SC,S,C
WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND SN=@SNAME_IN AND CN=@CNAME_IN
RETURN(@score_out)
END

ALTER FUNCTION SCORE_FUN(@SNAME_IN CHAR(10),@CNAME_IN CHAR(10))
RETURNS CHAR(8)
AS
BEGIN
DECLARE @SCORE_OUT CHAR(8)
SELECT @SCORE_OUT=
CASE
WHEN SCORE IS NULL THEN '未考'
WHEN SCORE<60 THEN '不及格'
WHEN SCORE>=60 AND SCORE<70 THEN '及格'
WHEN SCORE>=70 AND SCORE<80 THEN '中'
WHEN SCORE>=80 AND SCORE<90 THEN '良好'
WHEN SCORE>=90 THEN '优秀'
END
FROM SC,S,C
WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND SN=@SNAME_IN AND CN=@CNAME_IN
RETURN(@SCORE_OUT)
END

USE jxsk
GO
DECLARE @S_SCORE CHAR(8)
EXEC @S_SCORE=DBO.SCORE_FUN '钱尔','编译原理'
PRINT'钱尔的编译原理成绩是'+@S_SCORE
GO

USE JXSK
GO
ALTER FUNCTION S_SCORE_FUN(@SNAME_IN CHAR(8))
RETURNS TABLE
AS
RETURN (SELECT CN,SCORE,
LEVER=
CASE
WHEN SCORE IS NULL THEN '未考'
WHEN SCORE<60 THEN '不及格'
WHEN SCORE>=60 AND SCORE<70 THEN '及格'
WHEN SCORE>=70 AND SCORE<80 THEN '中'
WHEN SCORE>=80 AND SCORE<90 THEN '良好'
WHEN SCORE>=90 THEN '优秀'
END
FROM S,SC,C
WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND SN=@SNAME_IN)
GO

USE jxsk
GO
SELECT*FROM S_SCORE_FUN('钱尔')
GO

USE jxsk
DROP FUNCTION S_SCORE_FUN
GO