存储过程，触发器，分页练习

-------------------------------------游标和循环--------------------------------

DECLARE CATEGORY_CUR SCROLL CURSOR For  SELECT*FROM dbo.Categories   FOR UPDATE OF CategoryName  OPEN CATEGORY_CUR  GO

 UPDATE Categories  SET CategoryName='家电'  where CURRENT OF CATEGORY_CUR      SELECT*FROM Categories   DECLARE @VAR INT SET @VAR=0 WHILE @VAR<100000 BEGIN INSERT INTO dbo.Categories VALUES('测试数据') SET @VAR=@VAR+1 END        SELECT Count(*)FROM dbo.Products    delete from dbo.Categories   exec sp_helpdb

------------------------------------存储过程以及分页----------------------------

create procedure prSelctCategories AS BEGIN SELECT*FROM Categories END

EXECUTE prSelctCategories

Create Procedure prSelct @id int as select*from Categories where CategoryID=@id

execute prSelct 2763993

create procedure pr @id int, @name varchar(20) as Select*from Categories where CategoryID=@id and CategoryName=@name

execute pr 2763993,'测试数据'

GO

create procedure rp @id int, @content varchar(20) output as select @content= CategoryName from Categories where CategoryID=@id

GO

DECLARE @content varchar(20) execute rp 2763993,@content output PRINT @content

DECLARE @PRODEUCTNAME VARCHAR(20),@CATEGORYID INT,@PRUDUCTNUM INT,@VAR INT SET @VAR=0 SET @CATEGORYID=2763985 WHILE @VAR<10000 BEGIN INSERT INTO Products VALUES('洗衣粉',@CATEGORYID,@VAR,DEFAULT,DEFAULT) SET @VAR=@VAR+1 END

select*from Products delete from Products GO

create procedure sp_proPage @pageindex int=1, @pagesize  int=10 as select top(@pagesize)*from products where productID not in( select top ((@pageindex-1)*@pagesize) productID from Products order by ProductID)

GO execute sp_proPage 3,5000

Go

Create procedure proc_Page @pageIndex int,   --当前页 @pageSize int,   --每页多少条数据 @pageCount int output --总共多少页 as declare @firstIndex int --firstIndex开始 declare @lastIndex int --lastIndex结束 declare @total int set @firstIndex=(@pageIndex-1)*@pageSize+1 set @lastIndex=@pageSize*@pageIndex select @total=COUNT(*)from Products if(@total%@pageSize<>0)  begin set @pageCount=@total/@pageSize+1  end else  begin  set @pageCount=@total/@pageSize  end --先对主键进行排序，然后用ROW_NUMBER进行编号  select*from (select ROW_NUMBER()over(order by ProductID)as PID,* from  Products)as PTB where PID between @firstIndex and @lastIndex

declare @pageCount int execute proc_Page 1,10,@pageCount output print @pageCount

--------------------------------------事务的使用--------------------------------------

create table Bank (    ID INT NOT NULL PRIMARY KEY,    MONERYS MONEY )  INSERT INTO Bank VALUES(1,'2000')  INSERT INTO Bank VALUES(2,'5000')  GO  CREATE PROCEDURE PROC_MON  @TOID int,  @FROMID INT,  @MONEYS MONEY  AS SET XACT_ABORT on BEGIN TRAN  DECLARE @Counterror int=0 UPDATE Bank SET MONERYS=MONERYS-@MONEYS WHERE ID=@FROMID --转账账户减去金额  set @Counterror=@Counterror+@@ERROR  UPDATE Bank SET MONERYS=MONERYS+@MONEYS WHERE ID=@TOID   --转账账户加上金额  set @Counterror=@Counterror+@@ERROR IF  @Counterror<>0  --判断是否出现错误  begin rollback tran  --如果有错开始回滚  RETURN 0  END  else       --如果没有错误  begin commit tran --执行事务  RETURN 1 END GO  execute PROC_MON 1,2,2000  select*from bank  

 -----------------------------------------------------触发器操作-------------------------------------------- ----

DML触发器：DML事件即指在表或视图中修改数据的insert、update、delete语句。 ----DDL触发器: DDL事件即指在表或索引中的create、alter、drop语句也。 GO create trigger BankUpdateTrigger on Bank for update as print'the table was updated!'

Update Bank set MONERYS=5 where ID=1

select*from Bank

execute sp_helptext BankUpdateTrigger     --查看触发器内容 select * from sysobjects where xtype='TR' --产看数据库多少触发器 disable trigger BankUpdateTrigger on Bank  --禁用触发器 enable  trigger BankUpdateTrigger on Bank --开启触发器 GO Alter trigger BankUpdateTrigger on Bank for update as if exists(select*from Bank where monerys=5) rollback         --触发器回滚，保持数据完整性

----------------------------------操作日志表----------------------------------------------------------

create table reapterLog (  LogID INT IDENTITY(1,1)PRIMARY KEY NOT NULL, )