ch11.sql

SQLServer2005基础教程,清华大学出版社

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 'Johnson', 'Willis'



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


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


USE AdventureWorks
DROP PROCEDURE HumanResources.GetEmployeeInfo


USE AdventureWorks
SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.ComputePlus')) AS [dbo.ComputePlus's Definition Text]

USE AdventureWorks
SELECT *
FROM sys.triggers



USE AdventureWorks
EXEC sp_helptext 'Production.uUnitMeasure'


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




USE ETravelCom
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())




USE ETravelCom
GO
CREATE TRIGGER t_accountData_insert
ON 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)






USE ETravelCom
GO
CREATE TRIGGER t_accountData_delete
ON 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)

USE ETravelCom
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 ETravelCom
SELECT *
FROM auditAccountData



USE ETravelCom
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 ETravelCom
SELECT *
FROM auditAccountData
ORDER BY audit_log_actionType



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


USE ETravelCom
DROP TABLE auditAccountData









USE ETravelCom
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 ETravelCom
SET DATEFIRST 1
SELECT dbo.ISOweek(CONVERT(DATETIME,'08/08/2008',101)) AS N'标准周序号'










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)























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





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