科大讯飞 SQL数据库复习资料

1、 称 ( 1 , 2 , 3 数,) 值 值 列 列 值 值 = = 例 ： = = = = 值 = 。 * 列 值* 和 * * 例 ： 若 * * * * * 如 = = = A、不带参数：CREATE PROC sp_InsertUserBEGIN-SQL语句SELECT * FROM UserEND执行存储过程：EXEC sp_InsertUserB、带参数：CREATE PROC sp_InsertUseruserId varchar(50),userName varchar(10)BEGIN-SQL语句INSERT INTO User VALUES(userId,userName)

2、END执行存储过程：EXEC sp_InsertUser A101,feipengEXEC sp_InsertUser userId=A101,userName=feipengEXEC sp_InsertUser userName=feipeng, userId=A101C、带输出参数：CREATE PROC sp_InsertUseruserId varchar(50),userName varchar(10),userCount OUTPUTBEGIN-SQL语句INSERT INTO User VALUES(userId,userName)SELECT userCount = COUNT

3、(*) FROM UserEND执行存储过程：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优点：A、模块化设计 像其他程序语言的函数一样，单独出来，可以调用它n次，并且可以独立于源代码，单独修改储存过程。B、更快执行 如果执行大量SQL代码或重复执行，储存过程比SQ

4、L批代码执行要快。C、减少网络流量 一条SQL语句就可以执行上千条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触发器CREATE TRIGGER 触

5、发器名称ON 表名称AFTER INSERT,DELETE,UPDATEASBEGIN- Insert statements for trigger hereEND两张逻辑表：inserted、deleted删除操作：deleted插入操作：inserted更新操作：deleted、inserted游标示例代码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