技術(shù) 點
- 技術(shù)
- 點
- V幣
- 點
- 積分
- 71645
|
想寫一個通用的用戶管理數(shù)據(jù)庫,因此,今天寫了如下 T-SQL 腳本,練習自己的寫腳本能力。現(xiàn)在放在這里,也許對一些網(wǎng)友有作用。呵呵......
2012-01-12
朱亦文- ---=========================================================================---
- --- 創(chuàng)建 [Person] 數(shù)據(jù)庫
- --- 日期:2011-01-12
- ---=========================================================================---
- USE [master]
- GO
- IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Person')
- DROP DATABASE [Person]
- GO
- CREATE DATABASE [Person] ON
- (
- NAME = N'Person',
- FILENAME = N'D:\DB\Person\Person.mdf',
- SIZE = 1024KB,
- MAXSIZE = UNLIMITED,
- FILEGROWTH = 1024KB
- )
- LOG ON
- (
- NAME = N'Person_log',
- FILENAME = N'D:\DB\Person\Person_log.ldf',
- SIZE = 1024KB,
- MAXSIZE = UNLIMITED,
- FILEGROWTH = 1024KB
- )
- GO
- USE [Person]
- GO
- ---=========================================================================---
- --- 創(chuàng)建表
- ---=========================================================================---
- GO
- --------------------------------------------------
- --- 表 :USysUsers
- --- 用途:存儲用戶
- --------------------------------------------------
- CREATE TABLE [dbo].[USysUsers](
- [UID] [int] IDENTITY(1000,1) NOT NULL,
- [UserName] [nvarchar](50) NOT NULL,
- [Password] [varchar](256) NOT NULL DEFAULT (''),
- [IsSystem] [bit] NOT NULL DEFAULT (0),
- [Description] [nvarchar](256) NULL,
- [Enabled] [bit] NOT NULL DEFAULT (1),
- [CreateTime] [datetime] NOT NULL DEFAULT (getdate()),
- CONSTRAINT [PK_USysUsers] PRIMARY KEY CLUSTERED
- (
- [UID] ASC
- ),
- CONSTRAINT [IX_USysUsers] UNIQUE NONCLUSTERED
- (
- [UserName] ASC
- )
- )
- GO
- --- 創(chuàng)建系統(tǒng)帳戶
- SET IDENTITY_INSERT [dbo].[USysUsers] ON
- GO
- INSERT [dbo].[USysUsers]
- ([UID],[UserName],[Password],[IsSystem],[Description],[Enabled]) VALUES
- (0, N'Guest', '', 1, N'匿名來賓用戶', 0)
- INSERT [dbo].[USysUsers]
- ([UID],[UserName],[Password],[IsSystem],[Description],[Enabled]) VALUES
- (1, N'Administrator', 'sys', 1, N'系統(tǒng)管理員', 1)
- GO
- SET IDENTITY_INSERT [dbo].[USysUsers] OFF
- GO
- --------------------------------------------------
- --- 函數(shù):fbUSysGeustIsEnabled
- --- 目的:判定是否允許匿名來賓用戶
- --- 返回:0 - 不允許,1 - 允許
- --- 依賴:dbo.USysUsers
- --------------------------------------------------
- CREATE FUNCTION [dbo].[fbUSysGeustIsEnabled]()
- RETURNS bit
- AS
- BEGIN
- DECLARE @ret bit
- -- 從[dbo].[USysUsers]表中獲取 Guest 用戶啟用
- -- 作為系統(tǒng)是否允許匿名
- SELECT @ret = [Enabled] FROM [dbo].[USysUsers] WHERE [UID] = 0
- IF @ret IS NULL
- SET @ret = 0
- RETURN @ret
- END
- GO
- --------------------------------------------------
- --- 過程:_paUSysSetUserEnabled
- --- 目的:設(shè)置用戶是否啟用
- --- 參數(shù):
- --- @UID - 用戶 ID
- --- @Enabled - 0-不允許,1-允許
- --- 返回:0 - 不成功
- --- 依賴:dbo.USysUsers
- --------------------------------------------------
- CREATE PROCEDURE [dbo].[_paUSysSetUserEnabled]
- (
- @UID int,
- @Enabled bit
- )
- AS
- BEGIN
- IF @Enabled IS NOT NULL
- UPDATE [dbo].[USysUsers]
- SET Enabled = @Enabled
- WHERE UID = @UID
- RETURN @@ROWCOUNT
- END
- GO
- --------------------------------------------------
- --- 過程:paUserDisableGuest
- --- 目的:停用匿名用戶
- --- 返回:0 - 不成功
- --- 依賴:dbo.USysUsers/dbo._paUSysSetUserEnabled
- --------------------------------------------------
- CREATE PROCEDURE [dbo].[paUserDisableGuest]
- AS
- BEGIN
- DECLARE @return_value int
- EXEC @return_value = [dbo].[_paUSysSetUserEnabled]
- @UID = 0,
- @Enabled = 0
- RETURN @return_value
- END
- GO
- --------------------------------------------------
- --- 過程:paUserEnableGuest
- --- 目的:啟用匿名用戶
- --- 返回:0 - 不成功
- --- 依賴:dbo.USysUsers/dbo._paUSysSetUserEnabled
- --------------------------------------------------
- CREATE PROCEDURE [dbo].[paUserEnableGuest]
- AS
- BEGIN
- DECLARE @return_value int
- EXEC @return_value = [dbo].[_paUSysSetUserEnabled]
- @UID = 0,
- @Enabled = 1
- RETURN @return_value
- END
- GO
- --------------------------------------------------
- --- 過程:paUserDisableUser
- --- 目的:停用用戶
- --- 返回:0 - 不成功
- --- 依賴:dbo.USysUsers/dbo._paUSysSetUserEnabled
- --------------------------------------------------
- CREATE PROCEDURE [dbo].[paUserDisableUser](@UID int)
- AS
- BEGIN
- DECLARE @return_value int
- EXEC @return_value = [dbo].[_paUSysSetUserEnabled]
- @UID = @UID,
- @Enabled = 0
- RETURN @return_value
- END
- GO
- --------------------------------------------------
- --- 過程:paUserEnableUser
- --- 目的:啟用用戶
- --- 返回:0 - 不成功
- --- 依賴:dbo.USysUsers/dbo._paUSysSetUserEnabled
- --------------------------------------------------
- CREATE PROCEDURE [dbo].[paUserEnableUser](@UID int)
- AS
- BEGIN
- DECLARE @return_value int
- EXEC @return_value = [dbo].[_paUSysSetUserEnabled]
- @UID = @UID,
- @Enabled = 1
- RETURN @return_value
- END
- GO
- --------------------------------------------------
- --- 表 :USysUserExtender
- --- 用途:存儲用戶擴展屬性
- --- 依賴:dbo.USysUsers
- --------------------------------------------------
- CREATE TABLE [dbo].[USysUserExtender](
- [UID] [int] NOT NULL,
- [RealName] [nvarchar](50) NULL,
- [Gender] [nchar](1) NULL,
- [Birthday] [datetime] NULL,
- [Email] [nvarchar](256) NULL,
- [Company] [nvarchar](50) NULL,
- [Post] [nvarchar](10) NULL, /* 職位/崗位/職務(wù) */
- [Title] [nvarchar](10) NULL, /* 稱謂/頭銜/職稱 */
- [Mobile] [varchar](50) NULL,
- [Tel] [varchar](50) NULL,
- [Province] [nvarchar](10) NULL,
- [City] [nvarchar](10) NULL,
- [ZipCode] [varchar](6) NULL,
- CONSTRAINT [PK_USysUserExtender] PRIMARY KEY CLUSTERED
- (
- [UID] ASC
- )
- )
- GO
- --- 設(shè)置 USysUserExtender 與 USysUsers 關(guān)系
- --- 級聯(lián)更新、級聯(lián)刪除
- ALTER TABLE [dbo].[USysUserExtender]
- WITH
- CHECK ADD CONSTRAINT [FK_USysUserExtender_USysUsers]
- FOREIGN KEY([UID]) REFERENCES [dbo].[USysUsers] ([UID])
- ON UPDATE CASCADE
- ON DELETE CASCADE
- GO
- --------------------------------------------------
- --- 函數(shù):_ftUSysUsers
- --- 用途:獲取用戶列表
- --- 依賴:dbo.USysUsers/dbo.USysUserExtender
- --------------------------------------------------
- CREATE FUNCTION [dbo].[_ftUSysUsers]
- (
- @System bit = NULL,
- @Enabled bit = NULL
- )
- RETURNS TABLE
- AS
- RETURN
- (
- SELECT
- u.UID,
- u.UserName,
- u.Password,
- u.IsSystem,
- u.Description,
- u.CreateTime,
- e.RealName,
- e.Gender,
- e.Birthday,
- e.Email,
- e.Company,
- e.Post,
- e.Title,
- e.Mobile,
- e.Tel,
- e.Province,
- e.City,
- e.ZipCode
- FROM
- dbo.USysUsers AS u LEFT OUTER JOIN
- dbo.USysUserExtender AS e ON u.UID = e.UID
- WHERE
- ((u.Enabled = @Enabled) OR (@Enabled IS NULL)) AND
- ((u.IsSystem = @System) OR (@System IS NULL))
- )
- GO
- --------------------------------------------------
- --- 視圖:vUsersAll
- --- 用途:所有用戶
- --- 依賴:dbo.USysUsers/dbo.USysUserExtender/dbo._ftUSysUsers
- --------------------------------------------------
- CREATE VIEW [dbo].[vUsersAll]
- AS
- SELECT * FROM [dbo].[_ftUSysUsers](NULL, NULL)
- GO
- --------------------------------------------------
- --- 視圖:vUsersSystem
- --- 用途:所有系統(tǒng)用戶
- --- 依賴:dbo.USysUsers/dbo.USysUserExtender/dbo._ftUSysUsers
- --------------------------------------------------
- CREATE VIEW [dbo].[vUsersSystem]
- AS
- SELECT * FROM [dbo].[_ftUSysUsers](1, NULL)
- GO
- --------------------------------------------------
- --- 視圖:vUsersSystemCurrent
- --- 用途:當前系統(tǒng)用戶
- --- 依賴:dbo.USysUsers/dbo.USysUserExtender/dbo._ftUSysUsers
- --------------------------------------------------
- CREATE VIEW [dbo].[vUsersSystemCurrent]
- AS
- SELECT * FROM [dbo].[_ftUSysUsers](1, 1)
- GO
- --------------------------------------------------
- --- 視圖:vUsersSystemDisabled
- --- 用途:當前禁用的系統(tǒng)用戶
- --- 依賴:dbo.USysUsers/dbo.USysUserExtender/dbo._ftUSysUsers
- --------------------------------------------------
- CREATE VIEW [dbo].[vUsersSystemDisabled]
- AS
- SELECT * FROM [dbo].[_ftUSysUsers](1, 0)
- GO
- --------------------------------------------------
- --- 視圖:vUsersGeneral
- --- 用途:所有一般用戶
- --- 依賴:dbo.USysUsers/dbo.USysUserExtender/dbo._ftUSysUsers
- --------------------------------------------------
- CREATE VIEW [dbo].[vUsersGeneral]
- AS
- SELECT * FROM [dbo].[_ftUSysUsers](0, NULL)
- GO
- --------------------------------------------------
- --- 視圖:vUsersGeneralCurrent
- --- 用途:當前一般用戶
- --- 依賴:dbo.USysUsers/dbo.USysUserExtender/dbo._ftUSysUsers
- --------------------------------------------------
- CREATE VIEW [dbo].[vUsersGeneralCurrent]
- AS
- SELECT * FROM [dbo].[_ftUSysUsers](0, 1)
- GO
- --------------------------------------------------
- --- 視圖:vUsersGeneralDisabled
- --- 用途:當前禁用的一般用戶
- --- 依賴:dbo.USysUsers/dbo.USysUserExtender/dbo._ftUSysUsers
- --------------------------------------------------
- CREATE VIEW [dbo].[vUsersGeneralDisabled]
- AS
- SELECT * FROM [dbo].[_ftUSysUsers](0, 0)
- GO
復(fù)制代碼 |
本帖子中包含更多資源
您需要 登錄 才可以下載或查看,沒有帳號?注冊
x
|