MSSQL Transaction[事务] and Procedure[存储过程]

--事务分三种
--1.显示事务
--我们手动begin transaction ...... commit transaction/rollback transaction
--上面这种写法叫做“显示事务”

--2.隐式事务
SET IMPLICIT_TRANSACTIONS { ON | OFF }隐式事务


--3.自动提交事务，SQL Server默认使用的是自动提交事务.
--我们每次执行一条sql语句的时候，sql server都会自动帮我们打开一个事务
--如果该sql语句执行不出错，则sql server自动提交该事务commit
--如果该sql语句执行出错了，那么sql server则自动回滚该事务。rollback

--1：创建第一个简单的存储过程 
 CREATE PROC usp_helloworld
 AS
 BEGIN
    PRINT 'hello world!'
 END
 
 EXEC usp_helloworld
 
 --2:带参数的存储过程
CREATE PROC usp_showmsg 
@msg nvarchar(300)  ---多个参数使用,隔开。
AS
BEGIN
    PRINT @msg
END


 EXEC usp_showmsg N'XXX'
 
 EXEC usp_showmsg @msg=N'11111111111'
 
 
 
 --3：简单的加法存储过程
 CREATE PROC usp_add
 @n int,
 @m int
 AS
 BEGIN
    PRINT @n+@m
 END
 
 EXEC usp_add 100,200
 
 DROP PROC usp_add
 
--4：带默认值的存储过程
ALTER PROC usp_add 
@num1 int = 100,  --为参数加默认值
@num2 int = 200
AS
BEGIN
    PRINT @num1 + @num2
END



--5：部分带默认值的存储过程
ALTER PROC usp_add 
@num1 int = 100,  --为参数加默认值
@num2 int
AS
BEGIN
    PRINT @num1 + @num2
END

exec usp_add @num2=50

--6：存储过程中的输出参数
SELECT * FROM TblStudent

ALTER PROC usp_selectByAge
@age int,
@count int OUTPUT  -- 输出参数
AS
BEGIN
    --1. 根据@age进行查询
    SELECT * FROM TblStudent where tSAge>@age
    
    --2.返回一共查询出了多少条数据
    SET @count=(SELECT COUNT(*) FROM TblStudent where tsage>@age)
END


DECLARE @c int
EXEC usp_selectByAge @age=20,@count=@c OUTPUT
PRINT @c


--7：经典写法：转账使用存储过程来编写
--存储过程
CREATE PROC usp_transfer
@from char(4),
@to char(4),
@money money,
@state bit OUTPUT --1表示转账成功，0表示转账失败！ 
AS
BEGIN
    DECLARE @balance money,@sum int=0
    SET @state=0
    --1.查询@from中的余额
    SET @balance=(SELECT balance FROM bank WHERE bank.cId=@from)
    --2.检查余额是否充足
    IF @balance-@money>10 BEGIN  
        ----3.开始事务进行转账
        --打开事务
        BEGIN TRANSACTION
            --1.减钱
            UPDATE bank SET balance=bank.balance-@money where bank.cId=@from
            SET @sum=@sum+@@error
            --2.加钱
            UPDATE bank SET balance=bank.balance+@money where bank.cId=@to
            SET @sum=@sum+@@error
            
            IF @sum=0 BEGIN  
                SET @state=1 --设置转账成功
                COMMIT TRAN
            END
            ELSE
                ROLLBACK TRAN
            
    END
END
--调用存储过程
DECLARE @ok bit
EXEC usp_transfer @from='0002',@to='0001',@money=800,@state=@ok OUTPUT
SELECT @ok

SELECT * FROM bank



--8：经典：分页存储过程
SELECT * FROM Customers order by Customers.CustomerID asc

--创建存储过程
--参数：
    --每页大小pagesize
    --当先用户要查看第几页 
    --总页数
    --总条数
CREATE PROC usp_get_customers_by_page
@pagesize int=5, --每页多少条
@pageindex int=1,  --用户当前要看第几页 
@recordcount int OUTPUT, --总记录条数，输出参数
@pagecont int OUTPUT     --总页数，输出参数 
AS
BEGIN
    --1. 分页查询语句
    SELECT
        T.CustomerID,
        T.CompanyName,
        T.City,
        T.[Address],
        T.Phone
    FROM (SELECT *,rn=ROW_NUMBER()OVER(ORDER by CustomerID ASC) FROM Customers) AS T
    WHERE T.rn BETWEEN @pagesize*(@pageindex-1)+1 and @pagesize*@pageindex
    
    --2.返回总条数
    SET @recordcount=(SELECT COUNT(*) FROM Customers)
    
    --3.计算总页数，并返回
    SET @pagecont=CEILING(@recordcount*1.0/@pagesize);
END

--调用存储过程
DECLARE @pc int,@rc int
EXEC usp_get_customers_by_page 7,13,@rc OUTPUT,@pc OUTPUT
PRINT @pc
PRINT @rc



--9：增删改查：存储过程
--insert
CREATE PROC usp_insert_newperson
@name nvarchar(50),
@age int
AS
BEGIN
    INSERT into NewPerson VALUES(@name,@age)
END
------------------------------
--delete
CREATE PROC usp_delete_newperson
@autoId int
AS
BEGIN
    DELETE from NewPerson where NewPerson.autoId=@autoId
END
------------------------------
--update
CREATE PROC usp_update_newperson
@autoId int,
@name nvarchar(50),
@age int
AS
BEGIN
    UPDATE NewPerson set uName=@name,age=@age WHERE NewPerson.autoId=@autoId
END
---------------------------------------
--select
CREATE PROC usp_select_newperson
AS
BEGIN
    SELECT * FROM NewPerson
END
-------------------------------------
CREATE view vw_newperson
AS 
    SELECT * FROM NewPerson
    
    
--10：其它-----
SELECT * FROM Contacts
SELECT * FROM deleted
    
INSERT into NewPerson (NewPerson.uName,age) 
OUTPUT INSERTED.autoId  VALUES('王子斌',20)

----inserted-------------------    deleted-----------------------

SELECT * FROM Customers

DELETE from Customers where Customers.CustomerID='CHOPS'


SELECT TOP 0 * INTO CustomersBak FROM Customers

SELECT * FROM CustomersBak

ALTER TRIGGER tri_customers_after_delete ON Customers
AFTER DELETE
AS
BEGIN
    INSERT INTO CustomersBak SELECT * FROM DELETED
END

SELECT * FROM Customers


DELETE from Customers where Customers.CustomerID IN('CACTU','CENTC','ERNSH')



CREATE TABLE A20140222 ( A VARCHAR(10))


DECLARE @SQL varchar(500)
SET @SQL='CREATE TABLE T_'+CONVERT(VARCHAR(10),GETDATE(),112)+' ( A VARCHAR(10))'
PRINT @SQL
EXEC(@SQL )

PRINT CONVERT(VARCHAR(10),GETDATE(),112)