今天学习了如何在SQLServer中,通过脚本创建Function,以下是今天练习的Demo脚本
[Code]
/*
自动生成的脚本
*/
USE forums
GO
CREATE TABLE EMPLOYEE_DUPLICATE
(
ID INT IDENTITY(1,1) PRIMARY KEY,
EMP_NAME NVARCHAR(40) NOT NULL,
EMP_TEL NVARCHAR(20) NOT NULL,
EMP_PROVINCE NVARCHAR(20) NOT NULL,
EMP_COUNTRY NVARCHAR(20) NOT NULL
)
GO
//将一个表中的数据复制到刚才新建的表中
INSERT INTO
EMPLOYEE_DUPLICATE(EMP_NAME,EMP_TEL,EMP_PROVINCE,EMP_COUNTRY)
SELECT EMP_NAME,EMP_TEL,EMP_CITY,EMP_COUNTRY FROM EMPLOYEE
/**
注意:题外话,如果要跨数据库
(前提是当前用户中可以操作那个数据库)
复制数据,则以下语句似乎可以搞定
INSERT INTO DataBase_1_Name.dbo.EMPLOYEE_DUPLICATE
(EMP_NAME,EMP_TEL,EMP_PROVINCE,EMP_COUNTRY)
SELECT EMP_NAME,EMP_TEL,EMP_CITY,EMP_COUNTRY FROM
DataBase_2_Name.dbo.EMPLOYEE
**/
SELECT * FROM EMPLOYEE_DUPLICATE
/*
创建带参数&返回值的函数
*/
IF EXISTS (SELECT * FROM sysobjects WHERE NAME = 'getResultById')
DROP FUNCTION getResultById
CREATE FUNCTION getResultById (@id INT)
RETURNS TABLE
AS
RETURN
(
SELECT EMP_NAME, EMP_TEL,EMP_PROVINCE,EMP_COUNTRY
FROM EMPLOYEE_DUPLICATE
WHERE ID = @id
)
//调用该函数
SELECT * FROM getResultById(2)
/*
*Results Listing
1 Tom 13011041125 NewYork USA
2 James 13000145296 ShanXi China
3 Harry 13103452845 ShanDong China
4 Jessica 13429990000 ShanXi China
5 Larry 0421-2909090 Yuta USA
6 Jakeboo 0911-2657999 ShanDong China
7 Vencent 010-28129588 BeiJing China
*/
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = 'getIdsByProvince')
DROP FUNCTION getIdsByProvince
CREATE FUNCTION getIdsByProvince(@province NVARCHAR(50))
//注意:一定是RETURNS,不是RETURN ! !
//另外:似乎只可以将返回类型设置为TABLE
//没有什么BEGIN...END...GO之类的东东
RETURNS TABLE
AS
RETURN
(
SELECT ID FROM EMPLOYEE_DUPLICATE
WHERE EMP_PROVINCE = @province
)
SELECT * FROM EMPLOYEE
//查询在EMPLOYEE表中过滤重复之后的EMP_CITY以及重复EMP_CITY的计数
SELECT DISTINCT(EMP_PROVINCE),
COUNT(EMP_PROVINCE)
FROM EMPLOYEE_DUPLICATE
GROUP BY EMP_PROVINCE
//调用该函数,传入CITY,然后返回相应的ID,再根据ID查询出EMPLOYEE表中的记录
SELECT * FROM EMPLOYEE
WHERE
ID IN (SELECT ID FROM getIdsByProvince('ShanXi'))
[/Code]
以上就是新建FUNCTION的脚本了,创建数据库之后,就可以直接运行了!
欢迎拍砖!
[Code]
/*
自动生成的脚本
*/
USE forums
GO
CREATE TABLE EMPLOYEE_DUPLICATE
(
ID INT IDENTITY(1,1) PRIMARY KEY,
EMP_NAME NVARCHAR(40) NOT NULL,
EMP_TEL NVARCHAR(20) NOT NULL,
EMP_PROVINCE NVARCHAR(20) NOT NULL,
EMP_COUNTRY NVARCHAR(20) NOT NULL
)
GO
//将一个表中的数据复制到刚才新建的表中
INSERT INTO
EMPLOYEE_DUPLICATE(EMP_NAME,EMP_TEL,EMP_PROVINCE,EMP_COUNTRY)
SELECT EMP_NAME,EMP_TEL,EMP_CITY,EMP_COUNTRY FROM EMPLOYEE
/**
注意:题外话,如果要跨数据库
(前提是当前用户中可以操作那个数据库)
复制数据,则以下语句似乎可以搞定
INSERT INTO DataBase_1_Name.dbo.EMPLOYEE_DUPLICATE
(EMP_NAME,EMP_TEL,EMP_PROVINCE,EMP_COUNTRY)
SELECT EMP_NAME,EMP_TEL,EMP_CITY,EMP_COUNTRY FROM
DataBase_2_Name.dbo.EMPLOYEE
**/
SELECT * FROM EMPLOYEE_DUPLICATE
/*
创建带参数&返回值的函数
*/
IF EXISTS (SELECT * FROM sysobjects WHERE NAME = 'getResultById')
DROP FUNCTION getResultById
CREATE FUNCTION getResultById (@id INT)
RETURNS TABLE
AS
RETURN
(
SELECT EMP_NAME, EMP_TEL,EMP_PROVINCE,EMP_COUNTRY
FROM EMPLOYEE_DUPLICATE
WHERE ID = @id
)
//调用该函数
SELECT * FROM getResultById(2)
/*
*Results Listing
1 Tom 13011041125 NewYork USA
2 James 13000145296 ShanXi China
3 Harry 13103452845 ShanDong China
4 Jessica 13429990000 ShanXi China
5 Larry 0421-2909090 Yuta USA
6 Jakeboo 0911-2657999 ShanDong China
7 Vencent 010-28129588 BeiJing China
*/
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = 'getIdsByProvince')
DROP FUNCTION getIdsByProvince
CREATE FUNCTION getIdsByProvince(@province NVARCHAR(50))
//注意:一定是RETURNS,不是RETURN ! !
//另外:似乎只可以将返回类型设置为TABLE
//没有什么BEGIN...END...GO之类的东东
RETURNS TABLE
AS
RETURN
(
SELECT ID FROM EMPLOYEE_DUPLICATE
WHERE EMP_PROVINCE = @province
)
SELECT * FROM EMPLOYEE
//查询在EMPLOYEE表中过滤重复之后的EMP_CITY以及重复EMP_CITY的计数
SELECT DISTINCT(EMP_PROVINCE),
COUNT(EMP_PROVINCE)
FROM EMPLOYEE_DUPLICATE
GROUP BY EMP_PROVINCE
//调用该函数,传入CITY,然后返回相应的ID,再根据ID查询出EMPLOYEE表中的记录
SELECT * FROM EMPLOYEE
WHERE
ID IN (SELECT ID FROM getIdsByProvince('ShanXi'))
[/Code]
以上就是新建FUNCTION的脚本了,创建数据库之后,就可以直接运行了!
欢迎拍砖!