科大訊飛 SQL數(shù)據(jù)庫復(fù)習(xí)資料

1、 稱 ( 1 , 2 , 3 數(shù),) 值 值 列 列 值 值 = = 例 ： = = = = 值 = 。 * 列 值* 和 * * 例 ： 若 * * * * * 如 = = = A、不帶參數(shù)：CREATE PROC sp_InsertUserBEGIN-SQL語句SELECT * FROM UserEND執(zhí)行存儲(chǔ)過程：EXEC sp_InsertUserB、帶參數(shù)：CREATE PROC sp_InsertUseruserId varchar(50),userName varchar(10)BEGIN-SQL語句INSERT INTO User VALUES(userId,userName)

2、END執(zhí)行存儲(chǔ)過程：EXEC sp_InsertUser A101,feipengEXEC sp_InsertUser userId=A101,userName=feipengEXEC sp_InsertUser userName=feipeng, userId=A101C、帶輸出參數(shù)：CREATE PROC sp_InsertUseruserId varchar(50),userName varchar(10),userCount OUTPUTBEGIN-SQL語句INSERT INTO User VALUES(userId,userName)SELECT userCount = COUNT

3、(*) FROM UserEND執(zhí)行存儲(chǔ)過程：DECLARE uc INTEXEC sp_InsertUser A101,feipeng,uc OUTPUTEXEC sp_InsertUseruserId=A101,userName=feipeng,userCount=uc OUTPUTEXEC sp_InsertUser userName=feipeng, userId=A101,userCount=uc OUTPUT優(yōu)點(diǎn)：A、模塊化設(shè)計(jì) 像其他程序語言的函數(shù)一樣，單獨(dú)出來，可以調(diào)用它n次，并且可以獨(dú)立于源代碼，單獨(dú)修改儲(chǔ)存過程。B、更快執(zhí)行 如果執(zhí)行大量SQL代碼或重復(fù)執(zhí)行，儲(chǔ)存過程比SQ

4、L批代碼執(zhí)行要快。C、減少網(wǎng)絡(luò)流量 一條SQL語句就可以執(zhí)行上千條SQL代碼。 BEGIN TRANDECLARE SumError INTSET SumError = 0INSERT INTO User VALUES(A1001,feipeng)SET SumError = SumError + ERRORDELETE FROM Class WHERE ClassId = B110SET SumError = SumError + ERRORIF SumError 0BEGINROLLBACK TRANENDELSEBEGINCOMMIT TRANEND觸發(fā)器CREATE TRIGGER 觸

5、發(fā)器名稱ON 表名稱AFTER INSERT,DELETE,UPDATEASBEGIN- Insert statements for trigger hereEND兩張邏輯表：inserted、deleted刪除操作：deleted插入操作：inserted更新操作：deleted、inserted游標(biāo)示例代碼declare cur_Employees SCROLLcursor forselect EmployeeID,BirthDatefrom HumanResources.Employee where EmployeeID 6FOR READ ONLYopen cur_Employeesdeclare EmployeeID intdeclare BirthDate datetimefetch NEXT from cur_Employees into EmployeeID,BirthDatewhile fetch_status = 0beginprintconvert(char(3),EmployeeID)+|+convert(varchar,BirthDate)fetch next from cur_Employees into EmployeeID,Birt