T-SQL应用，视图、存储过程、触发器、游标、临时表等

--*********************批处理*********************
--[在一个批处理中存有一个语法错误，则所有的语句都无法通过编译]
USE flowershopdb
SELECT * FROM tb_category
SELECT * FROM tb_detailed
SELECT * FROM tb_product
SELECT * FROM tb_shopping
SELECT * FROM tb_user
INSERT tb_user(u_name,u_pass) VALUES(‘user3‘,‘12‘)
DELETE tb_user WHERE u_id=4
UPDATE tb_user SET u_name=‘sa‘,u_pass=‘11‘ WHERE u_id=2
GO

--*********************视图*********************
CREATE VIEW v_cd
AS
--内连接[不建议使用联合查询]
SELECT c.c_id,d.d_id,c.c_name,d.d_name,d.d_remark FROM tb_category c 
INNER JOIN tb_detailed d
ON c.c_id=d.c_id
GO

SELECT * FROM v_cd


--*********************变量*********************
--局部变量
--如果事先不对变量赋值，变量将保持NULL的状态

--声明
DECLARE @name nvarchar(10)
DECLARE @age int
DECLARE @num int
--或者
DECLARE @name nvarchar(10),
        @age int,
        @num int

--赋值
--1 set
SET @name = ‘哈哈‘
--2 select
SELECT @age = 5
SELECT @num=c_id FROM tb_category WHERE c_id=1000

--打印
PRINT @name
PRINT @age
PRINT @num

--变量作为条件
SELECT * FROM tb_category WHERE c_id>@num
GO

--全局变量
PRINT  ‘SQLServer的版本‘+@@VERSION 
PRINT  ‘服务器名称: ‘+@@SERVERNAME
PRINT ‘最后一次放生的错误号‘+CONVERT(varchar(5),@@ERROR)
GO



--*********************结构语句*********************
--IF ELSE语句
DECLARE @price MONEY 
SELECT  @price=AVG(p_price) FROM tb_product
IF (SELECT  p_price FROM tb_product WHERE p_id=3000)>@price
    BEGIN
        SELECT ‘高价‘
        SELECT p_name,p_price FROM tb_product WHERE p_id=3000
    END
ELSE
    BEGIN
        SELECT ‘低价‘
        SELECT p_name,p_price FROM tb_product WHERE p_id=3000
    END
GO

--IF EXISTS()语句 --条件
IF EXISTS(SELECT AVG(p_price) FROM tb_product)
PRINT ‘haha‘

IF EXISTS(SELECT p_price FROM tb_product WHERE p_price>100)
PRINT ‘haha‘


DECLARE @price MONEY 
SELECT  @price=AVG(p_price) FROM tb_product
IF EXISTS(SELECT  p_name,p_price FROM tb_product WHERE p_price>@price)
    BEGIN
        SELECT ‘高价‘,p_name,p_price FROM tb_product WHERE p_price>@price
    END
ELSE
    BEGIN
        SELECT ‘低价‘,p_name,p_price FROM tb_product WHERE p_price<@price
    END
GO

--WHILE()语句 [WHILE(1=1)条件永远为真]


--*********************事务*********************
BEGIN TRANSACTION 
/*--定义变量，用于累计事务执行过程中的错误--*/
DECLARE @errorSum INT 
SET @errorSum=0  --初始化为0，即无错误
/*--转账product：百合花多10支,剑兰少10支*/
UPDATE tb_product SET p_number=p_number+10
   WHERE p_name LIKE ‘百合%‘
SET @errorSum=@errorSum+@@error
UPDATE tb_product SET p_number=p_number-10
   WHERE p_name LIKE ‘剑兰%‘
SET @errorSum=@errorSum+tb_product  --累计是否有错误
IF @errorSum>0
BEGIN
    ROLLBACK TRANSACTION
    SELECT ‘失败‘
END
ELSE
BEGIN
    COMMIT TRANSACTION
    SELECT ‘成功‘
End

--*********************分页语句*********************
DECLARE @pageSize int,
        @pageIndex int
SELECT  @pageSize=3,
        @pageIndex=1
BEGIN
SELECT TOP (@pageSize) p_id,p_price,p_name FROM tb_product 
WHERE p_id NOT IN
    (
    SELECT TOP ((@pageIndex-1)*@pageSize) p_id FROM tb_product ORDER BY p_id DESC 
    ) 
ORDER BY p_id DESC
END

--*********************存储过程*********************
--PROCEDURE 简写 PROC
--EXECUTE 简写 EXEC

--系统存储过程
--列出服务器上的所有数据库。
EXEC sp_databases
--显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本
EXEC sp_helptext ‘v_cd‘          --视图
EXEC sp_helptext ‘proGetPageData‘--存储过程
EXEC sp_helptext ‘f‘             --触发器
--查看某个表的所有信息
EXEC sp_help ‘tb_product‘


--自定义存储过程

--编写分页存储过程[采用子查询]
--@pageSize 页容量[一页显示几条数据]
--@pageIndex 第几页
ALTER PROCEDURE proGetPageData
@pageSize int=3,   
@pageIndex int=1
AS
DECLARE @sqlStr varchar(300)
SET @sqlStr=‘select top ‘+
             str(@pageSize)+‘ * from tb_product where p_id not in(select top ‘+
             str((@pageIndex-1)*@pageSize)+
             ‘ p_id from tb_product order by p_id)order by p_id‘
PRINT @sqlStr
EXEC(@sqlStr)

--调用存储过程[三种方式]
EXEC proGetPageData                             --调用含有默认参数的
EXECUTE proGetPageData 3,1                     --调用传参的
EXEC proGetPageData @pageSize=3,@pageIndex=2 --调用传值的
EXEC proGetPageData @pageSize=10             --pageSize自定义，pageIndex采用默认值


--采用输出参数的存储过程
--@pageCount 总页数 @rowCount 总行数
ALTER PROC [dbo].[proGetPageData2]
@pageIndex int=1,
@pageSize int=3,
@pageCount int OUTPUT, 
@rowCount int OUTPUT
AS
BEGIN
    DECLARE @sqlStr nvarchar(300),@sqlCount nvarchar(300)
    SET @sqlCount = ‘SELECT @rowCount=COUNT(p_id),@pageCount=CEILING((COUNT(p_id)+0.0)/‘+ CAST(@pageSize AS VARCHAR)+‘) FROM tb_product‘
    PRINT @sqlCount
    EXEC SP_EXECUTESQL @sqlCount,N‘@rowCount INT OUTPUT,@pageCount INT OUTPUT‘,@rowCount OUTPUT,@pageCount OUTPUT
    SET @sqlStr=‘select top ‘+
                 str(@pageSize)+‘ * from tb_product where p_id not in(select top ‘+
                 str((@pageIndex-1)*@pageSize)+
                 ‘ p_id from tb_product order by p_id)order by p_id‘
    PRINT @sqlStr
    EXEC(@sqlStr)
END

DECLARE @pc int
DECLARE @rc int
EXEC [proGetPageData2] 1,3,@pc OUTPUT,@rc OUTPUT
SELECT @pc,@rc

--简单查询语句存储过程
CREATE PROCEDURE proGetPageData_user
@u_id int  
AS
BEGIN
    SELECT * FROM tb_user WHERE u_id=@u_id 
END

EXEC proGetPageData_user 1


--*********************函数*********************
--系统函数
SELECT LEN(‘111‘)
SELECT DATALENGTH(‘111‘)
SELECT GETDATE()

--自定义函数

--分页函数[子查询]
--内联表值函数[返回值为表格]
CREATE FUNCTION funGetPageData(@pageSize int,@pageIndex int) 
RETURNS TABLE
AS
RETURN
(
    SELECT TOP (@pageSize) p_id,p_price,p_name FROM tb_product 
    WHERE p_id NOT IN(SELECT TOP ((@pageIndex-1)*@pageSize) p_id FROM tb_product ORDER BY p_id DESC ) 
    ORDER BY p_id DESC
)
--标量值函数[返回值为int]
CREATE FUNCTION demo(@a int,@b int)
RETURNS int
AS
BEGIN
    RETURN (@a+@b)
END

--调用函数
SELECT * FROM dbo.funGetPageData(3,1)
PRINT dbo.demo(1,2)


--*********************触发器*********************
--tb_product表更新触发器
--IF UPDATE(列名)检查是否更新了某一列，用于 insert 或 update，不能用于 delete。例：
CREATE TRIGGER f
ON tb_product
FOR UPDATE
AS
    IF UPDATE(p_name) or UPDATE(p_remark)
        PRINT ‘tb_product表name和remark更新了‘

--游标
declare @d_name varchar(max)
declare @d_remark varchar(max)
declare @d_price varchar(20)
declare @d_number varchar(4)
declare @count int
set @count = 0
declare yb cursor 
for(select p_name,p_remark,p_price,p_number from tb_product)
open yb
fetch next from yb into @d_name,@d_remark,@d_price,@d_number
while (@@FETCH_STATUS = 0)
begin
    print ‘花名: ‘+@d_name
    print ‘备注: ‘+@d_remark
    print ‘价格: ‘+@d_price
    print ‘编号: ‘+@d_number
    print ‘--------------------------‘
    set @count = @count + 1
    fetch next from yb into @d_name,@d_remark,@d_price,@d_number
end
close yb
deallocate yb
print ‘总数：‘+cast(@count as varchar)

select count(*) from tb_product

--临时表
select * from tb_product
create table #wyk(pname varchar(200))
insert into #wyk values(‘啊啊‘)
select * from #wyk
drop table #wyk