Umhlahlandlela wesinyathelo ngezinyatheloUkusebenzisa i-TRY ... I-CATCH yokuphatha iziphambeko ze-SQL Server

Thola amaphutha ngaphandle kokuphazamisa ukusebenza

I-TRY ... Isitatimende se-CATCH ku-Transact- SQL ithola futhi isebenze izimo zephutha kwizicelo zakho zezinqolobane. Lesi sitatimende siyitshe legumbi le-SQL Server ukuphatha iphutha futhi yingxenye ebalulekile ekuthuthukiseni izinhlelo zokusebenza eziqinile ze-database. Zama ... I-CATCH isebenza ku-SQL Server kusukela ngo-2008, i-Azure SQL Database, i-Azure SQL Data Warehouse kanye ne-Parallel Data Warehouse.

Kwethula TRY..CATCH

Zama ... I-CATCH isebenza ngokukuvumela ukuthi ucacise izitatimende ezimbili ze-Transact-SQL: enye ofuna "ukuzama" futhi enye ongayisebenzisa ukuze 'ubambe' noma yiziphi amaphutha ongavela. Lapho i-SQL Server ihlangana nesitatimende se-TRY ... se-CATCH, senza ngokushesha isitatimende esifakwe esigatshaneni se-TRY. Uma isitatimende se-TRY sisebenza ngokuphumelelayo, i-SQL Server ivele iqhubekele phambili. Noma kunjalo, uma isitatimende se-TRY senza iphutha, i-SQL Server iqhuba isitatimende se-CATCH ukuphatha kahle iphutha.

I-syntax eyisisekelo ithatha leli fomu:

QALA TRY {sql_statement | isitatimende_block} QAPHELA THOLA UKUQALA CATCH [{sql_statement | isitatimende_block}] END END CATCH [; ]

Zama ... Isibonelo se-CATCH

Kulula ukuqonda ukusetshenziswa kwalesi sitatimende ngokusebenzisa ukusetshenziswa kwesibonelo. Cabanga ukuthi ungumlawuli wesitatimende sezinsiza zomuntu siqukethe itafula elibizwa ngokuthi "Abasebenzi," elinolwazi mayelana nabasebenzi bonke enhlanganweni yakho. Leli phebula lisebenzisa inamba ye-ID yomsebenzi jikelele njengokhiye oyinhloko . Ungase uzame ukusebenzisa isitatimende ngezansi ukufaka isisebenzi esisha ku-database yakho:

BHEKA kubasebenzi (i-id, kuqala_name, last_name, extension) VALUES (12497, 'Mike', 'Chapple', 4201)

Ngaphansi kwezimo ezivamile, lesi sitatimende singeza umugqa kufulawa labasebenzi. Kodwa-ke, uma isisebenzi se-ID 12497 sesivele sikhona ku-database, ukufaka umugqa kungaphambene nomgomo oyinhloko oyinhloko futhi kubangele iphutha elilandelayo:

Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY inkinga 'PK_employee_id'. Ayikwazi ukufaka ukhiye wephinda kunto 'dbo.employees'. Isitatimende sinqanyuliwe.

Ngenkathi lephutha likunikeza ulwazi oludinga ukuxazulula inkinga, kunezinkinga ezimbili nalo. Okokuqala, umlayezo u-cryptic. Ihlanganisa amaphutha amakhodi, izinombolo zomugqa nolunye ulwazi olungaqondakali kumsebenzisi ojwayelekile. Okwesibili, futhi okubaluleke nakakhulu, kubangela ukuthi isitatimende siphume futhi singabangela ukuqhuma kwesicelo.

Okunye ukugoqa isitatimende ku-TRY ... isitatimende se-CATCH, njengoba kuboniswe ngezansi:

QAPHELA QAPHELA KUBASEBENZI (id, kuqala_name, last_name, extension) VALUES (12497, 'Mike', 'Chapple', 4201) QAPHELA QAPHELA QALA I-CATCH PRINT 'Iphutha:' + ERROR_MESSAGE (); I-EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Employee Mail', @recipients = 'hr@foo.com', @body = 'Kuvele iphutha ekwakheni irekhodi lomsebenzi omusha.', @subject = 'Iphutha lokuphindwa kwabasebenzisi be-ID'; QAPHELA ISIKHATHI

Kulesi sibonelo, noma yiziphi amaphutha ezenzekayo kubikwa kokubili umsebenzisi owenza umyalo nekheli le-hr@foo.com e-mail. Iphutha eliboniswe kumsebenzisi livela ngezansi:

Iphutha: Ukuphulwa kwe-PRIMARY KEY inkinga 'PK_employee_id'. Ayikwazi ukufaka ukhiye wephinda kunto 'dbo.employees'. Imeyili elayishiwe.

Okubaluleke kakhulu, ukukhishwa kwesicelo kuyaqhubeka ngokujwayelekile, okuvumela umlimi ukuba aphathe kahle iphutha. Ukusetshenziswa kwe-TRY ... Isitatimende se-CATCH siyindlela enhle yokubona ngokuthe xaxa nokuphatha amaphutha ezenzeka kwizicelo zesisekelo se-SQL Server.

Ukufunda Okuningi

Uma ufuna ukufunda kabanzi mayelana nolimi olufundwayo olufundwayo, funda isingeniso ku-SQL .