📄 design_page 109 to page 136.txt
字号:
CREATE PROCEDURE UpdateProductWithLog
@ProductID int,
@DeductQty int
AS
DECLARE @Err int, @Rcount int, @Msg varchar(100)
UPDATE ProductList SET QtyInStock = QtyInStock - @DeductQty
WHERE ProductID = @ProductID
SELECT @Err = @@ERROR, @RCount = @@ROWCOUNT
IF @Err <> 0
RAISERROR('ERROR %d occured updating Product %d.', 10 , 1, @Err,
@ProductID) WITH LOG
IF @Rcount = 0
RETURN(-1)
ELSE
BEGIN
SELECT @Msg = STR(@DeductQty) + ' units are deducted from Product '
+ STR(@ProductID) + '.'
EXEC master.dbo.xp_logevent 50001, @Msg
RETURN(0)
END
EXEC UpdateProductWithLog 1, 30
EXEC UpdateProductWithLog 1, 3000
CREATE TABLE UpdateSecurity (
UserName char(20) NOT NULL,
ProductID int NOT NULL)
INSERT UpdateSecurity (UserName, ProductID) VALUES ('dbo', 1)
INSERT UpdateSecurity (UserName, ProductID) VALUES ('abc', 2)
CREATE PROCEDURE UpdateAllowedProduct
@ProductID int,
@DeductQty int
AS
IF ((SELECT COUNT(*) FROM UpdateSecurity WHERE ProductID = @ProductID
AND UserName = USER) > 0)
UPDATE ProductList SET QtyInStock = QtyInStock - @DeductQty
WHERE ProductID = @ProductID
ELSE
PRINT USER + ' is not permitted to update Product ' + STR(@ProductID)
GRANT EXEC ON UpdateAllowedProduct TO abc
GRANT SELECT ON UpdateSecurity TO abc
GRANT SELECT, UPDATE ON ProductList TO abc
EXEC UpdateAllowedProduct 1, 3
EXEC UpdateAllowedProduct 2, 3
EXEC UpdateAllowedProduct 1, 3
EXEC UpdateAllowedProduct 2, 3
EXEC sp_helptext UpdateAllowedProduct
ALTER PROCEDURE UpdateAllowedProduct
@ProductID int,
@DeductQty int
WITH ENCRYPTION
AS
IF ((SELECT COUNT(*) FROM UpdateSecurity WHERE ProductID = @ProductID
AND UserName = USER) > 0)
UPDATE ProductList SET QtyInStock = QtyInStock - @DeductQty
WHERE ProductID = @ProductID
ELSE
PRINT USER + ' is not permitted to update Product ' + STR(@ProductID)
EXEC sp_helptext UpdateAllowedProduct
EXEC sp_helptext Employees_View
CREATE FUNCTION fn_EmployeesView
(@Department varchar(10))
RETURNS table
AS
RETURN (SELECT EmployeeID, FirstName, LastName, DepartmentName, Salary
FROM Employees WHERE DepartmentName = @Department)
SELECT * FROM fn_EmployeesView ('IT')
DECLARE crs_EmployeeName CURSOR FOR
SELECT FirstName, LastName FROM Employees
OPEN crs_EmployeeName
FETCH NEXT FROM crs_EmployeeName
PRINT @@FETCH_STATUS
CLOSE crs_EmployeeName
DEALLOCATE crs_EmployeeName
SELECT Purchase.PurchaseNo, LEFT(PurchaseDate,11) AS PurchaseDate, SupplierName,
ProductName, UnitPrice, Quantity FROM Purchase
INNER JOIN PurchaseDetails
ON Purchase.PurchaseNo = PurchaseDetails.PurchaseNo FOR XML AUTO
DECLARE @dochandle int,
@doc varchar(8000)
set @doc = '
<ROOT>
<Purchase PurchaseNo="20011023" PurchaseDate="Oct 3 2001" SupplierName="ABC Co. ">
<PurchaseDetails ProductName="Mouse " UnitPrice="30.0000" Quantity="100"/>
<PurchaseDetails ProductName="Keyboard " UnitPrice="80.0000" Quantity="50"/>
</Purchase>
<Purchase PurchaseNo="20011024" PurchaseDate="Oct 4 2001" SupplierName="Apple Co. ">
<PurchaseDetails ProductName="Display card " UnitPrice="340.0000" Quantity="30"/>
</Purchase>
<Purchase PurchaseNo="20011025" PurchaseDate="Oct 8 2001" SupplierName="Orange Co. ">
<PurchaseDetails ProductName="SCSI Controller Card" UnitPrice="2340.0000" Quantity="20"/>
<PurchaseDetails ProductName="SCSI Harddisk " UnitPrice="1794.0000" Quantity="50"/>
</Purchase>
<Purchase PurchaseNo="20011026" PurchaseDate="Oct 8 2001" SupplierName="Apple Co. ">
<PurchaseDetails ProductName="IDE Harddisk " UnitPrice="1092.0000" Quantity="20"/>
</Purchase>
<Purchase PurchaseNo="20011027" PurchaseDate="Oct 9 2001" SupplierName="ABC Co. ">
<PurchaseDetails ProductName="Sound Card " UnitPrice="200.0000" Quantity="80"/>
</Purchase>
</ROOT>'
EXEC sp_xml_preparedocument @dochandle OUTPUT, @doc
SELECT * FROM OPENXML (@dochandle, '/ROOT/Purchase')
WITH (PurchaseNo char(8),
PurchaseDate datetime,
SupplierName char(20))
EXEC sp_xml_removedocument @dochandle
DECLARE @dochandle int,
@doc varchar(8000)
set @doc = '
<ROOT>
<Purchase PurchaseNo="20011023" PurchaseDate="Oct 3 2001" SupplierName="ABC Co. ">
<PurchaseDetails ProductName="Mouse " UnitPrice="30.0000" Quantity="100"/>
<PurchaseDetails ProductName="Keyboard " UnitPrice="80.0000" Quantity="50"/>
</Purchase>
<Purchase PurchaseNo="20011024" PurchaseDate="Oct 4 2001" SupplierName="Apple Co. ">
<PurchaseDetails ProductName="Display card " UnitPrice="340.0000" Quantity="30"/>
</Purchase>
<Purchase PurchaseNo="20011025" PurchaseDate="Oct 8 2001" SupplierName="Orange Co. ">
<PurchaseDetails ProductName="SCSI Controller Card" UnitPrice="2340.0000" Quantity="20"/>
<PurchaseDetails ProductName="SCSI Harddisk " UnitPrice="1794.0000" Quantity="50"/>
</Purchase>
<Purchase PurchaseNo="20011026" PurchaseDate="Oct 8 2001" SupplierName="Apple Co. ">
<PurchaseDetails ProductName="IDE Harddisk " UnitPrice="1092.0000" Quantity="20"/>
</Purchase>
<Purchase PurchaseNo="20011027" PurchaseDate="Oct 9 2001" SupplierName="ABC Co. ">
<PurchaseDetails ProductName="Sound Card " UnitPrice="200.0000" Quantity="80"/>
</Purchase>
</ROOT>'
EXEC sp_xml_preparedocument @dochandle OUTPUT, @doc
SELECT * FROM OPENXML (@dochandle, '/ROOT/Purchase/PurchaseDetails')
WITH (ProductName char(20),
UnitPrice money,
Quantity int)
EXEC sp_xml_removedocument @dochandle
DECLARE @dochandle int,
@doc varchar(8000)
set @doc = '
<ROOT>
<Purchase PurchaseNo="20011023" PurchaseDate="Oct 3 2001" SupplierName="ABC Co. ">
<PurchaseDetails ProductName="Mouse " UnitPrice="30.0000" Quantity="100"/>
<PurchaseDetails ProductName="Keyboard " UnitPrice="80.0000" Quantity="50"/>
</Purchase>
<Purchase PurchaseNo="20011024" PurchaseDate="Oct 4 2001" SupplierName="Apple Co. ">
<PurchaseDetails ProductName="Display card " UnitPrice="340.0000" Quantity="30"/>
</Purchase>
<Purchase PurchaseNo="20011025" PurchaseDate="Oct 8 2001" SupplierName="Orange Co. ">
<PurchaseDetails ProductName="SCSI Controller Card" UnitPrice="2340.0000" Quantity="20"/>
<PurchaseDetails ProductName="SCSI Harddisk " UnitPrice="1794.0000" Quantity="50"/>
</Purchase>
<Purchase PurchaseNo="20011026" PurchaseDate="Oct 8 2001" SupplierName="Apple Co. ">
<PurchaseDetails ProductName="IDE Harddisk " UnitPrice="1092.0000" Quantity="20"/>
</Purchase>
<Purchase PurchaseNo="20011027" PurchaseDate="Oct 9 2001" SupplierName="ABC Co. ">
<PurchaseDetails ProductName="Sound Card " UnitPrice="200.0000" Quantity="80"/>
</Purchase>
</ROOT>'
EXEC sp_xml_preparedocument @dochandle OUTPUT, @doc
SELECT * FROM OPENXML (@dochandle, '/ROOT/Purchase/PurchaseDetails')
WITH (PurchaseNo char(8),
ProductName char(20),
UnitPrice money,
Quantity int)
EXEC sp_xml_removedocument @dochandle
DECLARE @dochandle int,
@doc varchar(8000)
set @doc = '
<ROOT>
<Purchase PurchaseNo="20011023" PurchaseDate="Oct 3 2001" SupplierName="ABC Co. ">
<PurchaseDetails ProductName="Mouse " UnitPrice="30.0000" Quantity="100"/>
<PurchaseDetails ProductName="Keyboard " UnitPrice="80.0000" Quantity="50"/>
</Purchase>
<Purchase PurchaseNo="20011024" PurchaseDate="Oct 4 2001" SupplierName="Apple Co. ">
<PurchaseDetails ProductName="Display card " UnitPrice="340.0000" Quantity="30"/>
</Purchase>
<Purchase PurchaseNo="20011025" PurchaseDate="Oct 8 2001" SupplierName="Orange Co. ">
<PurchaseDetails ProductName="SCSI Controller Card" UnitPrice="2340.0000" Quantity="20"/>
<PurchaseDetails ProductName="SCSI Harddisk " UnitPrice="1794.0000" Quantity="50"/>
</Purchase>
<Purchase PurchaseNo="20011026" PurchaseDate="Oct 8 2001" SupplierName="Apple Co. ">
<PurchaseDetails ProductName="IDE Harddisk " UnitPrice="1092.0000" Quantity="20"/>
</Purchase>
<Purchase PurchaseNo="20011027" PurchaseDate="Oct 9 2001" SupplierName="ABC Co. ">
<PurchaseDetails ProductName="Sound Card " UnitPrice="200.0000" Quantity="80"/>
</Purchase>
</ROOT>'
EXEC sp_xml_preparedocument @dochandle OUTPUT, @doc
SELECT * FROM OPENXML (@dochandle, '/ROOT/Purchase/PurchaseDetails')
WITH (PurchaseNo char(8) '../@PurchaseNo',
ProductName char(20) './@ProductName',
UnitPrice money './@UnitPrice',
Quantity int './@Quantity')
EXEC sp_xml_removedocument @dochandle
DECLARE @dochandle int,
@doc varchar(8000)
set @doc = '
<ROOT>
<Purchase PurchaseNo="20011023" PurchaseDate="Oct 3 2001" SupplierName="ABC Co. ">
<PurchaseDetails ProductName="Mouse " UnitPrice="30.0000" Quantity="100"/>
<PurchaseDetails ProductName="Keyboard " UnitPrice="80.0000" Quantity="50"/>
</Purchase>
<Purchase PurchaseNo="20011024" PurchaseDate="Oct 4 2001" SupplierName="Apple Co. ">
<PurchaseDetails ProductName="Display card " UnitPrice="340.0000" Quantity="30"/>
</Purchase>
<Purchase PurchaseNo="20011025" PurchaseDate="Oct 8 2001" SupplierName="Orange Co. ">
<PurchaseDetails ProductName="SCSI Controller Card" UnitPrice="2340.0000" Quantity="20"/>
<PurchaseDetails ProductName="SCSI Harddisk " UnitPrice="1794.0000" Quantity="50"/>
</Purchase>
<Purchase PurchaseNo="20011026" PurchaseDate="Oct 8 2001" SupplierName="Apple Co. ">
<PurchaseDetails ProductName="IDE Harddisk " UnitPrice="1092.0000" Quantity="20"/>
</Purchase>
<Purchase PurchaseNo="20011027" PurchaseDate="Oct 9 2001" SupplierName="ABC Co. ">
<PurchaseDetails ProductName="Sound Card " UnitPrice="200.0000" Quantity="80"/>
</Purchase>
</ROOT>'
EXEC sp_xml_preparedocument @dochandle OUTPUT, @doc
SELECT * FROM OPENXML (@dochandle, '/ROOT/Purchase/PurchaseDetails')
WITH (PurchaseNo char(8) '../@PurchaseNo',
PurchaseDate datetime '../@PurchaseDate',
SupplierName char(20) '../@SupplierName',
ProductName char(20),
UnitPrice money,
Quantity int)
EXEC sp_xml_removedocument @dochandle
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -