ch10 vptf.sql

以Microsoft SQL Server为代表的数据库产品具有超大容量的数据存储、高效率的数据查询算法、方便易用的向导和工具及友好亲切的用户接口

 

USE AdventureWorks
GO 
CREATE VIEW vw_EmpHireDate(EmployeeName, EmployeeID, HireDate)
AS 
SELECT c.FirstName + ' ' + c.LastName, e.EmployeeID, e.HireDate
FROM HumanResources.Employee e JOIN Person.Contact c 
ON e.ContactID = c.ContactID
GO
SELECT *
FROM vw_EmpHireDate




USE AdventureWorks
GO
sp_helptext vw_EmpHireDate







USE AdventureWorks
GO
CREATE VIEW vw_EmpHireDate_Encrypted(EmployeeName, EmployeeID, HireDate)
WITH ENCRYPTION
AS 
SELECT c.FirstName + ' ' + c.LastName, e.EmployeeID, e.HireDate
FROM HumanResources.Employee e JOIN Person.Contact c 
ON e.ContactID = c.ContactID
GO
sp_helptext vw_EmpHireDate_Encrypted






USE AdventureWorks
IF OBJECT_ID('vw_EmpHireDate_Encrypted', 'VIEW') IS NOT NULL
	DROP VIEW vw_EmpHireDate_Encrypted




USE AdventureWorks
GO
CREATE VIEW vw_young_employees
AS
SELECT EmployeeID, BirthDate
FROM HumanResources.Employee
WHERE BirthDate > '1980-1-1'
WITH CHECK OPTION






USE AdventureWorks
SELECT *
FROM vw_young_employees



USE AdventureWorks
UPDATE vw_young_employees
SET BirthDate = '1985-1-1'
WHERE EmployeeID = 99





USE AdventureWorks
UPDATE vw_young_employees
SET BirthDate = '1970-1-1'
WHERE EmployeeID = 99













USE AdventureWorks
GO
CREATE PROCEDURE HumanResources.GetEmployeeFullName 
AS 
SELECT LastName, FirstName, Department
FROM HumanResources.vEmployeeDepartment
GO



 




USE AdventureWorks 
GO 
CREATE PROCEDURE HumanResources.GetEmployeeInfo
@LastName VARCHAR(50),
@FirstName VARCHAR(50)
AS 
SELECT LastName, FirstName, JobTitle, Department
FROM HumanResources.vEmployeeDepartment
WHERE LastName = @LastName AND FirstName = @FirstName
GO









USE AdventureWorks 
GO 
CREATE PROCEDURE dbo.ComputePlus
@FirstPara DECIMAL(10, 2),
@SecondPara DECIMAL(10, 2),
@PlusResult DECIMAL (10, 2) OUTPUT
AS 
SET @PlusResult = @FirstPara + @SecondPara
GO






USE AdventureWorks
EXEC HumanResources.GetEmployeeFullName






USE AdventureWorks
EXEC HumanResources.GetEmployeeInfo 'Glimp', 'Diane'







USE AdventureWorks 
DECLARE @LastName VARCHAR(50)
DECLARE @FirstName VARCHAR(50)
SET @LastName = 'Glimp'
SET @FirstName = 'Diane'
EXEC HumanResources.GetEmployeeInfo @LastName, @FirstName





USE AdventureWorks 
DECLARE @result DECIMAL(10, 2)
EXEC dbo.ComputePlus 123, 568, @result OUTPUT
PRINT @result






USE AdventureWorks
DROP PROCEDURE HumanResources.GetEmployeeInfo
GO




USE AdventureWorks
SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.ComputePlus')) 
       AS [dbo.ComputePlus存储过程的定义文本]
GO

-- 触发器


USE AdventureWorks
SELECT *
FROM sys.triggers
GO





USE AdventureWorks
EXEC sp_helptext 'Production.uBillOfMaterials'
GO




USE ElecTravelCom
CREATE TABLE accountData(
accountID INT not null IDENTITY PRIMARY KEY,
accountType CHAR(128) not null,
accountAmount MONEY not null)
GO






USE ElecTravelCom
CREATE TABLE auditAccountData(
audit_log_id UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
audit_log_loginname VARCHAR(128) DEFAULT SYSTEM_USER,
audit_log_username VARCHAR(128) DEFAULT CURRENT_USER,
audit_log_actionType CHAR(16) NOT NULL,
audit_log_amount MONEY NOT NULL,
audit_log_actionTime DATETIME DEFAULT GETDATE())
GO







USE ElecTravelCom
GO
CREATE TRIGGER t_accountData_insert
ON dbo.accountData
WITH ENCRYPTION
FOR INSERT
AS
DECLARE @insertActionAmount MONEY
SELECT @insertActionAmount = accountAmount FROM inserted
INSERT INTO auditAccountData (audit_log_actionType, audit_log_amount)
VALUES ('INSERT', @insertActionAmount)
GO










USE ElecTravelCom
GO
CREATE TRIGGER t_accountData_delete
ON dbo.accountData
WITH ENCRYPTION
FOR DELETE
AS
DECLARE @deleteActionAmount MONEY
SELECT @deleteActionAmount = accountAmount FROM deleted
INSERT INTO auditAccountData (audit_log_actionType, audit_log_amount)
VALUES ('DELETE', @deleteActionAmount)
GO




USE ElecTravelCom
INSERT INTO accountData VALUES('存款', 2500)
INSERT INTO accountData VALUES('支票', 2337)
INSERT INTO accountData VALUES('存款', 199)
INSERT INTO accountData VALUES('存款', 2476300)
INSERT INTO accountData VALUES('支票', 5000)
INSERT INTO accountData VALUES('支票', 8938)
INSERT INTO accountData VALUES('支票', 1319.95)
INSERT INTO accountData VALUES('存款', 5513218)




USE ElecTravelCom
SELECT *
FROM auditAccountData
GO





USE ElecTravelCom
DELETE FROM accountData WHERE accountID = 1
DELETE FROM accountData WHERE accountID = 2
DELETE FROM accountData WHERE accountID = 3
DELETE FROM accountData WHERE accountID = 4
DELETE FROM accountData WHERE accountID = 5
DELETE FROM accountData WHERE accountID = 6






USE ElecTravelCom
SELECT *
FROM auditAccountData
ORDER BY audit_log_actionTime
GO






USE ElecTravelCom
GO
CREATE TRIGGER safetyAction 
ON DATABASE 
FOR DROP_TABLE, ALTER_TABLE 
AS 
PRINT N'禁止删除或修改当前数据库中的表!' 
ROLLBACK





USE ElecTravelCom
DROP TABLE auditAccountData
GO







-- 用户定义函数



USE ElecTravelCom
GO
CREATE FUNCTION dbo.ISOweek (@DATE DATETIME)
RETURNS INT
AS
BEGIN
     DECLARE @ISOweek INT
     SET @ISOweek= DATEPART(wk,@DATE)+1-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
-- 特殊情况: 1月份的1-3日可能属于前一年。
     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
-- 特殊情况：12月份的29-31日可能属于下一年。
     IF ((DATEPART(mm,@DATE)=12) AND ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28)) SET @ISOweek=1
     RETURN(@ISOweek)
END





USE ElecTravelCom
SET DATEFIRST 1
SELECT dbo.ISOweek(CONVERT(DATETIME,'08/08/2008',101)) AS N'标准周序号'
GO














USE AdventureWorks
GO
CREATE FUNCTION Sales.fn_SalesByStore (@storeid INT)
RETURNS TABLE
AS
RETURN (
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS N'年销售总额'
    FROM Production.Product AS P JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
                                 JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    WHERE SH.CustomerID = @storeid
    GROUP BY P.ProductID, P.Name)





SELECT *
FROM Sales.SalesOrderHeader




USE AdventureWorks
SELECT * 
FROM Sales.fn_SalesByStore (491)
























USE AdventureWorks
GO
CREATE FUNCTION dbo.fn_FindReports(@InEmpID INT)
RETURNS @retFindReports TABLE 
(   EmployeeID int primary key NOT NULL,
    Name nvarchar(255) NOT NULL,
    title nvarchar(50) NOT NULL,
    EmployeeLevel int NOT NULL,
    Sort nvarchar (255) NOT NULL )
-- 列出员工和其直接或间接汇报的上司的清单 
AS
BEGIN
   WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
    (SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName), e.Title, e.EmployeeID, 1,
            CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
     FROM HumanResources.Employee AS e JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
     WHERE e.EmployeeID = @InEmpID
   UNION ALL
     SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) + c.FirstName + ' ' + c.LastName),
            e.Title, e.EmployeeID, EmployeeLevel + 1,
            CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + LastName)
     FROM HumanResources.Employee as e JOIN Person.Contact AS c ON e.ContactID = c.ContactID
                                       JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID )
-- 把数据复制到函数的返回值中
   INSERT @retFindReports
   SELECT EmployeeID, Name, Title, EmployeeLevel, Sort
   FROM DirectReports 
   RETURN
END






SELECT *
FROM HumanResources.Employee






USE AdventureWorks
SELECT EmployeeID, Name, Title, EmployeeLevel, Sort
FROM dbo.fn_FindReports(109)
ORDER BY Sort
GO








USE AdventureWorks
GO
SELECT OBJECT_DEFINITION(OBJECT_ID('Sales.fn_SalesByStore')) 
       AS N'用户定义函数的定义文本'