ebook45-4

Transcription

1

2 4 T-SQL SQL Server S Q L 4.1 CAST CONVERT C A S T C O N V E RT C A S T CAST ( e x p r e s s i o n AS d a t a _ t y p e) e x p r e s s i o n SQL Server D a t a _ t y p e C A S T l u e = 50 I N T E G E R PRINT 'The value is ' + CAST(@intValue AS VA R C H A R ( 1 0 ) ) C O N V E RT C A S T D AT E T I M E D AT E T I M E C O N V E RT (data_type [ (length)], expression [, style]) d a t a _ t y p e l e n g t h S t y l e D AT E T I M E 4-1 A N S I C O N V E RT (VARCHAR(16), GETDATE( ), 102) ( y y ) ( y y y y ) 4-1 DAT E T I M E / (*) mon dd yyyy h h : m i A M U S A m m / d d / y y A N S I y y. m m. d d B r i t i s h / F r e n c h d d / m m / y y G e r m a n d d. m m. y y I t a l i a n d d - m m - y y dd mon yy mon dd,yy h h : m i : s s (*) mon dd yyyy U S A m m - d d - y y H h : m i : s s : m m m A M

3 4 T- S Q L 57 ( y y ) ( y y y y ) / J A PA N y y / m m / d d I S O y y m m d d (*) E u r o p e + dd mon yyyy h h : m i : s s : m m m ( 2 4 h ) h h : m i : s s : m m m ( 2 4 h ) - ( 2 4 h ) (*) ODBC yyyy-mm-dd hh:mi:ss (*) O D B C y y y y - m m - d d h h : m i : m s. m m m ( 2 4 h ) 4.2 P R I N T 8000 A S C I I C H A R VA R C H A R C O N V E RT 4-1 P R I N T C O N V E RT I N VA R C H A R 4-1 CHAR(60) /* define the variables */ = 15 = COUNT(*) FROM sales WHERE qty /* Concatenate the string using CONVERT to format the variables */ = 'There are ' + CONVERT ( VA R C H A R,@ i n t N u m O r d e r s ) + ' orders with a quantity greater than ' + CONVERT ( x t /* CODE RESULTS - DO NOT RUN CODE AFTER THIS LINE */ (1 row(s) aff e c t e d ) (1 row(s) aff e c t e d ) (1 row(s) aff e c t e d ) There are 13 orders with a quantity greater than 15

4 S E L E C T S E L E C T SET NOCOUNT ON S E L E C T SET NOCOUNT OFF SET NOCOUNT OFF SET NOCOUNT ON 4-2 SET NOCOUNT CHAR(60) /* define the variables */ = 15 = COUNT(*) FROM sales WHERE qty /* Concatenate the string using CONVERT to format the variables */ = 'There are ' x t SET NOCOUNT OFF + CONVERT ( VA R C H A i n t N u m O r d e r s ) + ' orders with a quantity greater than ' + /* CODE RESULTS - DO NOT RUN CODE AFTER THIS LINE */ There are 13 orders with a quantity greater than 15 P R I N T S E L E C T S E L E C T P R I N T ( ) R A I S E R R O R sysmessages SQL Server s y s m e s s a g e s s y s m e s s a g e s 4-2 sysmssages E r r o r I N T S e v e r i t y S M A L L I N T D l e v e l S M A L L I N T D e s c r i p t i o n WARCHAR (510) M s g L a n g i d S M A L L I N T N U L L s y s m e s s a g e s

5 4 T- S Q L 59 SELECT error, severity, description FROM master.. s y s m e s s a g e s ORDE BY error sysmessages s y s m e s s a g e s New message added 504 Stored procedure %.* s not found 1 7 D B O S A 7111 Can t log text value because log is out of space 18 SQL Server 516 Attempt to get system date/time failed 19 S A There is insufficient system memory to run this query Stored procedure %.* s is corrupted. Must re-create procedure SQL Server s y s m e s s a g e s sp_addmessage(), sp_altermessage() s p _ d r o p m e s s a g e () 1. s y s m e s s a g e s s p _ a d d m e s s a g e () s y s m e s s a g e s

6 s y s m e s s a g e s sp_addmessage 50001, 12, 'A number greater than 0 was expected. Please retry' /* CODE RESULTS - DO NOT RUN CODE AFTER THIS LINE */ New message added. m e s s a g e _ i d M E S S A G E _ I D s y s m e s s a g e s sp_addmessage 50001, 12, 'The specified value for %s was invalid.' /* CODE RESULTS - DO NOT RUN CODE AFTER THIS LINE */ Msg 15043, Level 16, State 1 You must specify 'REPLACE' to overwrite an existing message. R E P L A C E 4-5 ( U.S. English) Windows NT Application Log 4-5 R E P L A C E s y s m e s s a g e s sp_addmessage 50001, 12, 'The specified value for %s was invalid.', US_English, TRUE, REPLACE /* CODE RESULTS - DO NOT RUN CODE AFTER THIS LINE */ Replacing message. New message added. s y s m e s s a g e s m e s s a g e _ i d 2. s y s m e s s a g e s s y s m e s s a g e s s p _ a l t e r m e s s a g e () Windows NT Event Log 4-6 sp_altermessage 50001, WITH_LOG, FA L S E 4-6 s y s m e s s a g e s /* CODE RESULTS - DO NOT RUN CODE AFTER THIS LINE */ Message altered. 3. s y s m e s s a g e s s p _ d r o p m e s s a g e () s y s m e s s a g e s

7 4 T- S Q L sp_dropmessage s y s m e s s a g e s /* CODE RESULTS - DO NOT RUN CODE AFTER THIS LINE */ Message dropped R A I S E R R O R R A I S E R R O R WITH LOG Wi n d o w s NT Event Log Windows NT Application Log SQL Server Error Log RAISERROR ({msg_id msg_str} {, s e v e r i t y, state} [, argument [, n]]) [WITH o p t i o n [, n] ] M s g _ i d s y s m e s s a g e s m s g _ i d m s g _ i d SQL Server m s g _ i d s e v e r i t y SQL Server s t a t e s t a t e a rg u m e n t o p t i o n Option L O G N O WA I T S E T E R R O R L O G SQL Server Windows NT E R R O R m s g _ i d 19 R A I S E R R O R 4-8 s y s m e s s a g e s RAISERROR(14056, 16, 1) 4-8 s y s m e s s a g e s /* Build a string to display the current value of the global */ /* You do not normally have to do this; this */

8 62 /* step is for the purposes of this example. */ = 'The value is ' + C O N V E RT ( c h e r r o r ) /* CODE RESULTS - DO NOT RUN CODE AFTER THIS LINE */ Server: Msg 14056, Level 16, State 1, Line 1 The subscription could not be dropped at this time. The value is s y s m e s s a g e s 4-7 %.* s 4-9 s y s m e s s a g e s s y s m e s s a g e s R A I S E R R O e r r o r m e s s a g e _ i d e r r o r WITH SETERROR CHAR(255) 4-9 s y s m e s s a g e s /* Raise the error, giving the error text, severity, and state. */ RAISERROR('Undefined error raised using the WITH SETERROR option',1,2) WITH SETERROR /* Build a string to display the current value of the global */ /* You do not normally have to do this; this */ /* step is for the purposes of this example. */ = 'Using WITH SETERROR sets the error number ' + generated to ' + CONVERT ( c h e r r o r ) /* CODE RESULTS - DO NOT RUN CODE AFTER THIS LINE */ Undefined error raised using the WITH SETERROR option (1 row(s) aff e c t e d ) Using WITH SETERROR sets the error number generated to CASE C A S E C A S E CASE C A S E

9 4 T- S Q L 63 t i t l e s t y p e t i t l e _ i d C A S E t i t l e _ i d C A S E B o o k Ty p e 4-10 SELECT title_id, FROM titles 4-10 C A S E CASE SUBSTRING(title_id,1,2) END AS BookTy p e WHEN 'BU' THEN 'Business' WHEN 'MC' THEN 'Modern Cooking' WHEN 'PC' THEN 'Popular Computing' WHEN 'PS' THEN 'Psychology' WHEN 'TC' THEN 'Traditional Cooking' The following shows the output from the query: title_id BookType PC1035 Popular Computing PS1372 Psychology B U 1111 Business PS7777 Psychology TC4203 Traditional Cooking PS2091 Psychology PS2106 Psychology PC9999 Popular Computing TC3218 Traditional Cooking PS3333 Psychology PC8888 Popular Computing MC2222 Modern Cooking BU7832 Business TC7777 Traditional Cooking BU1032 Business MC3021 Modern Cooking MC3026 Modern Cooking BU2075 Business (18 row(s) affected) C A S E

10 64 select distinct 'WHEN ''' SUBSTRING(title_id,1,2) + ''' THEN' from titles /* CODE RESULTS - DO NOT RUN CODE AFTER THIS LINE */ WHEN 'BU' THEN WHEN 'MC' THEN WHEN 'PC' THEN WHEN 'PS' THEN WHEN 'TC' THEN (5 row(s) affected) CASE C A S E C A S E C A S E 1. C A S E C A S E C A S E SELECT sum(qty) AS To t a l, FROM 4-12 d a t e n a m e ( w e e k d a y, ord_date) AS We e k d a y sales GROUP BY datename(weekday, ord_date) SELECT sum(qty) AS To t a l, 4-13 C A S E CASE datepart(weekday, ord_date) WHEN 1 THEN 'Sunday' WHEN 2 THEN 'Monday' WHEN 3 THEN 'Tu e s d a y ' WHEN 4 THEN 'We d n e s d a y ' WHEN 5 THEN 'Thursday' WHEN 6 THEN 'Friday'

11 4 T- S Q L 65 END AS We e k d a y FROM sales WHEN 7 THEN 'Saturday' GROUP BY datepart(weekday, ord_date) 4-12 Total Weekday Monday 115 Saturday 45 Sunday 40 Thursday 78 Tu e s d a y 85 We d n e s d a y (6 row(s) aff e c t e d ) 4-13 Total Weekday Sunday 130 Monday 78 Tu e s d a y 85 We d n e s d a y 40 Thursday 115 Saturday (6 row(s) aff e c t e d ) 2. C A S E C A S E C A S E 4-14 y t d _ s a l e s W H E N E L S E E L S E S E L E C T title_id, price, ytd_sales, "DiscountPrice" = C A S E 4-14 C A S E WHEN ytd_sales < 1000 THEN C O N V E RT (SMALLMONEY, price *.50) WHEN ytd_sales < 3000 THEN

12 66 C O N V E RT (SMALLMONEY, price *.85) E N D ELSE price FROM titles 4-14 title_id price ytd_sales DiscountPrice BU B U BU BU MC MC MC3026 (null) (null) ( n u l l ) PC PC PC9999 (null) (null) ( n u l l ) PS PS PS PS PS TC TC TC (18 row(s) aff e c t e d ) y t d _ s a l e s p i r c e D i s c o u n t P r i c e C A S E 3. C A S E C A S E S E L E C T C A S E S E L E C T C A S E 4-15 S E L E C T S E L E C s t o r e s W H E N E L S E C A S

13 4 T- S Q L 67 SELECT DISTINCT st.stor_name, 'Sales Rating' = C A S E 4-15 C A S E WHEN (SELECT SUM(sa.qty) FROM sales sa THEN 'Poor' WHEN (SELECT SUM(sa.qty) FROM sales sa THEN 'Av e r a g e ' WHEN (SELECT SUM(sa.qty) FROM sales sa THEN 'Good' ELSE 'Excellent' E N D, 'Sales Total' = (SELECT SUM(sa.qty) FROM sales sa FROM stores st, sales sa ORDER BY 'Sales To t a l ' stor_name WHERE st.stor_id = sa.stor_id) < 10 WHERE st.stor_id = sa.stor_id) < 80 WHERE st.stor_id = sa.stor_id) < 100 WHERE st.stor_id = sa.stor_id) Sales Rating Sales Total Eric the Read Books P o o r 8 Fricative Bookshop Av e r a g e 6 0 B o o k b e a t G o o d 8 0 News & Brews G o o d 9 0 B a r n u m ' s E x c e l l e n t Doc-U-Mat: Quality Laundry and Books E x c e l l e n t (6 row(s) affected) 4. C A S E C A S E 4-16 U P D AT E C A S E s a l e s p a y t e r m s ( 2 ) q t y p a y t e r m s U P D AT E C A S E U P D ATE sales SET payterms = C A S E 4-16 C A S E WHEN (SELECT SUM(qty) FROM sales s1

14 68 WHERE sales.stor_id = s1.stor_id) < 10 THEN 'On Invoice' WHEN (SELECT SUM(qty) FROM sales s1 WHERE sales.stor_id = s1.stor_id) < 100 THEN 'Net 30' ELSE 'Net 60' E N D G O SELECT stor_id, SUBSTRING(ord_num,1,5) ord_num, ord_date, qty, payterms, title_id FROM sales G O (21 row(s) aff e c t e d ) stor_id ord_num ord_date qty payterms title_id Sep :00AM 5 On Invoice B U a Sep :00AM 3 On Invoice P S A2976 May :00AM 50 Net 60 P C QA744 Sep :00AM 75 Net 60 P S D4482 Sep :00AM 10 Net 30 P S P2121 Jun :00AM 40 Net 30 T C P2121 Jun :00AM 20 Net 30 T C P2121 Jun :00AM 20 Net 30 T C N9140 Sep :00AM 20 Net 60 P S N9140 Sep :00AM 25 Net 60 M C P3087 May :00AM 20 Net 60 P S P3087 May :00AM 25 Net 60 P S P3087 May :00AM 15 Net 60 P S P3087 May :00AM 25 Net 60 P S QQ229 Oct :00AM 15 Net 30 B U TQ456 Dec :00AM 10 Net 30 M C X999 Feb :00AM 35 Net 30 B U LL Sep :00AM 15 Net 30 M C LL Sep :00AM 10 Net 30 B U P723 Mar :00AM 25 Net 30 B U QA879 May :00AM 30 Net 30 PC1035 (21 row(s) affected)

15 4 T- S Q L (distributed transaction) ( 3 ) (Distributed Transaction Coordinator D T C ) 3 D T C D T C BEGIN DISTRIBUTED TRANSACTION D T C ( ) ( ) ( ) (commit tree) 1. REQUEST T R A N S A C T I O N R E L A T I O N S H I P 2. AGREE TO RELATIONSHIP DTC 3. PREPARE TRANSACTION DTC 4. TRANSACTION PREPARED 5A.COMMT TRANSACTION 6. TRANSACTION COMMITTED 5B.ROLLBACK TRANSACTION F A I L U R E 5C. ROLLBACK F A I L U R E 4-1 D T C ( ) ( 4-1 4

16 70 ) A 6 5 B 5 C 5 A 6 5 B 5 C 5 B 5 C ( ) D T C SQL Enterprise Manager Server Manager D T C Tr a n s a c t i o n F o rg e t ( ) S Q L Enterprise Manager D T C DTC D T C ( 16 ) B E G I N DISTRIBUTED TRAN(DISTRIBUTED TRANSACTION) D T C C O M M I T T R A N S R O L L B A C K s p _ a d d s e r v e r () s p _ a d d r e m o t e l o g i n () Enterprise Manager S e r v e Remote Server M a n a g e Remote Servers

17 4 T- S Q L j o b _ i d j o b s j o b s D T C C R E ATE PROCEDURE prupdatejobs(@insjob_id varchar(50)) AS U P D ATE jobs SET job_desc WHERE job_id 4-18 C R E ATE PROCEDURE smallint, varchar(30)) AS varchar(255) /* Start a Transaction */ BEGIN DISTRIBUTED TRANSACTION UpdateJobs /* Change Local Jobs Table */ U P D ATE jobs SET job_desc where job_id /* Make a string with the remote server name and */ /* stored procedure to execute */ /* Syntax for remote procedure call is NTSQL2.pubs..prUpdateJobs */ + '.pubs..prupdatejobs ' /* Update remote server. @chvjob_desc /* Commit the MS DTC transaction */ COMMIT TRANSACTION E X E C U T E 4-19 j o b s job_id 5 j o b _ d e s c C E O exec prupdatejobs 5,'CEO', NTSQL This command did not return data, and it did not return any rows

18 72 N T S Q L 2 D T C 4-20 D T C D T C j o b _ i d j o b _ d e s c C E O EXEC prupdatejobs 5,'Chief Executive Officer', NTSQL Msg 8501, Level 16, State 1 DTC on server 'NTSQL' is unavailable Msg 8524, Level 16, State 1 The current transaction couldn't be exported to remote site DTC It has been rolled back. D T C b o o k i n v b o o k i n 4-21 p u b s C R E ATE TABLE bookinv ( ) title_id quantity location lastaudit tid 4-21 CONSTRAINT p1_constraint PRIMARY KEY NONCLUSTERED, integer, char(2), datetime I N S E RT INTO bookinv SELECT DISTINCT title_id, FROM titles C O A L E S C E ( C O N V E RT ( i n t, R A N D ( y t d _ s a l e s ) * ), 1 0 ), S U B S T R I N G ( t y p e, 1, 2 ), p u b d a t e b o o k _ i d b o o k i n v

19 4 T- S Q L t i t l e _ i d S t o r I D U p d a t e J o b s t i t l e s y t d _ s a l e s s a l e s e r r o e r r o r : CREATE PROCEDURE prupdatebookinv(@tidtitles tid, int) AS 3 : 4: varchar(255) 5 : 6: /* Get quantity of available books in inventory */ 7: = (SELECT quantity FROM bookinv 8: WHERE title_id t l e s ) 9 : 10: < 0 11: BEGIN 12: /* If available quantity is less than zero, then raise error */ 13: = 'There are only ' 14: + CONVERT ( v a r c h a r ( 1 0 i n t Av a i l Q t y ) 15: + ' available, remote transaction denied' 16: RAISERROR (@chvoutputmsg, 16, -1) 17: END 18: ELSE 19: BEGIN 20: /* If available quantity is greater than zero, */ 21: /* update the inventory table */ 22: UPDATE bookinv SET quantity = quantity 23: WHERE title_id t l e s 24: = 'There were ' 25: + CONVERT ( v a r c h a r ( 1 0 i n t Av a i l Q t y ) 26: + ' available, remote transaction succeeds' 27: 28: END

20 : CREATE PROCEDURE prcreatesale tid, int, char(4), varchar(30)) AS 5: 6: varchar(255), varchar(20) 8 : 9: /* Make a string with the remote server name and */ 10: /* stored procedure to execute. */ 11: 12: + '.pubs..prupdatebookinv ' 1 3 : 14: /* Generate an order number. */ 15: + SUBSTRING(@tidTi t l e I d, 1, 2 ) 1 6 : 17: /* Start a transaction. */ 18: BEGIN DISTRIBUTED TRANSACTION UpdateJobs 1 9 : 20: /* Update local titles table. */ 21: UPDATE titles SET ytd_sales = ytd_sales 22: WHERE title_id 2 3 : 24: /* Add entry to local sales table. */ 25: INSERT sales(stor_id, ord_num, ord_date, qty 26:, payterms, title_id) 27: VALUES 28:, 'Net 2 9 : 30: /* Update remote server. */ @intqty 32: 33: > 0 34: BEGIN 35: ROLLBACK TRAN 36: PRINT 'TRANSACTION WAS ROLLED BACK' 37: END 38: ELSE 39: BEGIN 40: COMMIT TRAN 41: PRINT 'TRANSACTION WAS COMMITTED' 42: END

21 4 T- S Q L E X E C U T E b o o k i n v 100 B U 1032 t i t l e s s a l e s 40 EXEC prcreatesale 'BU1032',100,'6380','NTSQL2' 4-24 /* Enter only the EXEC statement - sample output follows. */ Msg 50000, Level 16, State 1 There are only 40 available; remote transaction denied. TRANSACTION WAS ROLLED BACK EXEC prcreatesale 'BU1032',20,'6380','NTSQL2' /* Enter only the EXEC statement - sample output follows. */ There were 40 available; remote transaction succeeds. TRANSACTION WAS COMMITTED 4-26 * Run these two queries against the local database. */ SELECT stor_id, convert(char(10),ord_num), ord_date, qty, title_id G O FROM SALES SELECT title_id, ytd_sales FROM titles G O /* Sample data for local database after a fresh install of pubs */ stor_id ord_date qty title_id Sep :00AM 5 B U a Sep :00AM 3 P S A2976 May :00AM 50 P C 8888

22 QA Sep :00AM 75 P S D4482 Sep :00AM 10 P S P2121 Jun :00AM 40 T C P2121 Jun :00AM 20 T C P2121 Jun :00AM 20 T C N Sep :00AM 20 P S N Sep :00AM 25 M C P3087a May :00AM 20 P S P3087a May :00AM 25 P S P3087a May :00AM 15 P S P3087a May :00AM 25 P S QQ2299 Oct :00AM 15 B U TQ456 Dec :00AM 10 M C X999 Feb :00AM 35 B U LL922 Sep :00AM 15 M C LL930 Sep :00AM 10 B U P723 Mar :00AM 25 B U QA879.1 May :00AM 30 P C 1035 (21 row(s) aff e c t e d ) title_id ytd_sales BU B U BU BU MC MC MC3026 (null) PC PC PC9999 (null) PS PS PS PS PS TC TC TC (18 row(s) aff e c t e d )

23 4 T- S Q L /* Run this query against the remote database. */ SELECT * FROM bookinv /* Results for a bookinv created against a fresh install of pubs*/ title_id quantity location lastaudit BU b u Jun :00AM B U b u Jun :00AM BU b u Jun :00AM BU b u Jun :00AM MC m o Jun :00AM MC m o Jun :00AM MC U N Dec :35AM PC p o Jun :00AM PC p o Jun :00AM PC p o Dec :35AM PS p s Oct :00AM PS p s Jun :00AM PS p s Oct :00AM PS p s Jun :00AM PS p s Jun :00AM TC t r Oct :00AM TC t r Jun :00AM TC t r Jun :00AM (18 row(s) affected) MS DTC S AVE TRAN S AVE BEGIN DISTRIBUTED TRANSACTION DTC D T C DTC Query Manager,

24 78 BEGIN DISTRIBUTED T R A N S A C T I O N 4.7 T- S Q L C A S E P R I N T s y s m e s s a g e s R A I S E R R O R