第4单元 创建数据类型和表

Transcription

1 138 MCSE 2000 SQL Stored Procedures Microsoft SQL Server 2000 ( ) Transact-SQL SQL Server (System Stored Procedures,sp_), master ( sp_prefix ) (Local Stored Procedures), (Temporary Stored Procedures), (#) ( ) (Remote Stored Procedures), SQL Server

2 139 (Extended Stored Procedures xp_), SQL Server xp SQL Server ( ) Transact-SQL SQL SQL Server 1 SQL Server syscomments syscomments 2 3( )

3 140 MCSE 2000 SQL 2000 SQL Server (ALTER ALTER VIEW) TABLE UPDATE STATISTICS ) (INSERT DELETE 4 SQL Server Transact-SQL WHERE UNION GROUPBY ORDERBY 5 ( ) SQL Server SQL Server sales Products development Products Products SQL Server

4 141 SQL Server Excution Plan Excution Context Excution Context SET SET Transact-SQL Transact-SQL Transact-SQL Transact-SQL

5 142 MCSE 2000 SQL tempdb Transact-SQL 1 CREATE PROCEDURE CREATE PROCEDURE CREATE PROCEDURE Transact-SQL CREATE PROCEDURE Transact-SQL CREATE PROCEDURE DEFUALT CREATE RULE CREATE TRIGGER CREATEVIEW CREATE PROCEDURE CREATE PROCEDURE 128MB CREATEPROC[EDURE]procedure_name[ number] [{@parameterdata_type} [VARYING][=default][OUTPUT] ][, n] [WITH {RECOMPILE I ENCRYPTION I RECOMPILE ENCRYPTION}]

6 143 [FOR REPLICATION] AS sql_statement [ n] Northwind USE Northwind CREATE PROC dbo.overdueorders AS SELECT * FROM dbo Orders WHERE RequiredDate < GETDATE () AND ShippedDate IS Null 2 ( ) 32 X Y Y X 3 sp_help sp_helptext sp_depends sp_stored_procedure sysobjects syscomments sysdepends dbo

7 144 MCSE 2000 SQL 2000 dbo (sysadmin) master sp_prefix SQL Server SETQUOTED_IDENTIFIER SETANSI_NULLS SET SET SET ARITHABORT SET ANSI WARNING SET ANSI_PADDINGS ANSI SET OBJECTPROPERTY SET tempdb sp_executesql EXECUTE sp_executesql SQLServer SQL Server Transact-SQL Transact-SQL Transact-SQL sp_executesql syscomments WITH ENCRYPTION WITH ENCRYPTION SQL Server Enterprise Manager sp_helptext syscomments

8 INSERT 1 EXECUTE [[EXEC[UTE]] { } [@retum_status = ] {procedure_name[ number] [[@parameter = ] {value I [DEFAULT]} [, n] [WITH RECOMPILE] Northwind EXEC OverdueOrders 2 INSERT INSERT Server SELECT EmployeeCustomer Northwind Customers USE Northwind CREATE PROC dbo.employeecustomer AS SELECT UPPER(SUBSTRING(LastName 1 4)+SUBSTRING(FirstName 1 1)) 'Northwind Traders' RTRIM(FirstName)+"+LastName 'Employee' Address City Region PostalCode Country, (' (206) '+'x'+Extension) NULL FROMEmployees

9 146 MCSE 2000 SQL 2000 WHERE HireDate<GETDATE() EmployeeCustomer INSERT INFO Cusmmem EXEC EmployeeCustomer Customers ALTER PROCEDURE ALTER PROCEDURE SQL Server ALTER PROCEDURE WITH ENCRYPTION ALTER PROCEDURE ALTERP ROCEDURE sysadmin db_owner db_ddladmin ALTER PROCEDURE ALTERP ROC[EDURE]procedure_name[;number] [{@parameterdata_type} [VARYING][=default][OUTPUT] ][, n) [WITH {RECOMPILE ENCRYFTION RECOMPILE ENCRYTIION } ]

10 147 [FOR REPILCATION] AS sql_statement[ n) Orders OverdueOrders USE Northwind ALTER PROC dbo.overdueorders AS SELECT CONVERT(char(8) RequireDate 1) RequireDate CONVERT(char(8) OrderDate 1) OrderDate OrderlD CustomerlD EmployeeID FROM Orders WHERE RequireDate < GETDATE() AND ShippedDate IS Null ORDER BY RequiredDate OverdueOrders EXEC OveldueOrders OverdueOrders RequiredDate OrderDate OrderlD CustomerlD EmployeelD ERNSH RANCH LINOD GREAL BOTTM 6 (21 row(s) affected) 2 DROP PROCEDURE sp_depends DROP PROCEDURE {procedure}[, n]

11 148 MCSE 2000 SQL 2000 OverdueOrders USE Northwind DROP PROC dbo.overdueorders 8.3 Employees SQL Server CREATE date_type[ = dafault] NULL = IS Null NULL ANSI NULL 1024 syscolumns Year to Year Sales

12 149 CREATE PROCEDURE dbo.[yeart to Year DateTime AS IS NULL EndingDate IS NULL BEGIN RAISERROR('NULL value are not allowes',14,1) RETURN END SELECT O.ShippedDate, O.OrderID OS.Subtotal DATENAME(yy ShippedDate) AS Year FROM ORDERS O INNER JOIN [Order Subtotals ] OS ON O.OrderID=OS.OrderID WHERE O.ShippedDate BeginningDate = value DEFAULT [[EXEC[UTE]] { } [@retum_status = ] [[@parameter=]{value [@variable[output] [DEFAULT]] [, n]

13 150 MCSE 2000 SQL 2000 [WITH RECOMPILE] AddCustomer Northwind CustomerID CompanyName USE Northwind CREATE @Fax AS nchar(5) nvarchar(40) nvarchar(30) = NULL nvarchar(30) = NULL nvarchar(60) = NULL nvarchar(15) = NULL nvarchar(15) = NULL nvarchar(10) = NULL nvarchar(15) = NULL nvarchar(24) = NULL nvarchar(24) = NULL AddCustomer Region Fax AddCustomer Region Fax

14 151 2 ( ) CREATE EXEC AddCustomer'ALFKl2','AlfredsPutterkiste','MariaAnders','SalesRepresen tative 'ObereStr.57' 'Berlin',NULL', '12209', 'Germany', ' ' ( OUTPUT ) CREATE PROCEDURE EXECUTE OUTPUT OUTPUT Transact-SQL text image Math Tutor SET SELECT SET Theresultis CREATE PROCEDURE dbo.mathtutor smallint

15 152 MCSE 2000 SQL 2000 MathTutor 5 6 SET smallint EXECUTE MathTutor OUTPUT SELECT Theresult OUTPUT MathTutor SQL 5 6 Theresult is SQL Server CREATE PROCEDURE [WITH RECOMPILE] CREATE PROCEDURE [WITH RECOMPILE] SQL Server OrderCount USE Northwind CREATE PROC nchar(10) WITH RECOMPILE AS SELECT Count( * ) FROM [Orders Qry] WHERE CustomerID EXECUTE [WITH RECOMPILE] WITH RECOMPILE EX CUTE [WITH RECOMPILE]

16 153 Customerlnfo EXEC Customerlnfo WITH RECOMPILE Sp_recompile tablename sp_recompile Northwind Customer EXEC sp_recompile Customer DBCC FREEPROCCACHE 8.4 DLL SQL Server xp_cmdshell dir EXEC master..xp_cmdshell 'dir c: ' (ODS) Microsoft Visual C++ Visual C SQL Server master master master

17 154 MCSE 2000 SQL 2000 xp_cmdhell xp_logevent SQL Server Windws 2000 sp_helptext xp_cmdshell DLL EXEC master..sp_helptext xp_cmdshell xp_cmdshell DLL xplog70.dll Sqlmap70.dll xp_sendmail SQL Server SQL Server Development Tools SQL Server C: Program Files Microsoft SQL Server 80 Tools Devtools Samples DDS 8.5 () SQL Server RETURN RETURN ( ) SQL Server SQLServer

18 155 GetOrders Qry Orders Customers GetOrders RETURN SELECT GetOrders USE Northwind CREATE PROCEDURE dbo.getorders nchar(10) SELECT OrderID,CustomerID,EmployeeID FROM [Orders Qry] WHERE RETURN Sp_addmessage Sp_addmessage SQL Server master sysmessage Windows 2000 Windows Transact-SQL Northwind INSERT USE Northwind

19 156 MCSE 2000 SQL 2000 CREATE PROCEDURE nvarchar(40)=null bit=null AS BEGIN TRANSACTION INSERT Suppliers ( CompanyNamc ContactName Address City Region PostalCode Country Phone)

20 @Phone) IF BEGIN ROLLBACK TRAN RETURN END int SELECT INSERT Products ( ProductName SupplierlD CategoryID QuantityPerUnit Discontinued) @Discontinued) BEGIN ROLLBACK TRAN RETURN END COMMIT TRANSACTION RAISERROR RAISERROR

21 158 MCSE 2000 SQL 2000 RAISERROR RAISERROR(50010,16,1) WITH LOG RAISERROR master sysmessages RAISERROR SQL Server Error Log Windows 2000 PRINT RAISERROR PRINT