sql server 的T-SQL 学习笔记（八）

/******************* 函数 和 存储过程 ************************/-- 表值函数和标量值函数-- 系统存储过程-- 函数  -- 函数是一种用户封装有一条或者多条SQL语句的结构  -- 函数分为系统函数和用户自定义函数  -- 用户自定义函数按照返回值划分分为标量值函数 和 表值函数    -- 标量值函数--返回值为单个值或者单个值的表达式    -- begin end可以省略 但是如果是多条语句则需要添加上begin end,    -- 创建标量值函数  -- 表值函数返回的是一个表    -- 内联表值函数      -- 特殊形式      -- 只能够有一条select语句      -- 不需要定义返回table的结构    -- 多语句表函数      -- 返回类型是table      -- -- 创建标量值函数(返回的是一个值或者是一个值的表达式)  if exists(select  * from sys.objects where name = 'getAccountNameById')    drop function getAccountNameById  go  -- create function 必须是批处理中仅有的语句 所以这里使用go断开   create function getAccountNameById(@accountId int = 1)  returns varchar(20)  as     begin       declare @accountName varchar(20)      select @accountName = ACCOUNT_NAME from ACCOUNT where ACCOUNT_ID = @accountId      return  @accountName    end  go  -- 使用函数(需要在前面加上dbo.) (标量函数返回值是一个值，select使用类似于输出语句)  select dbo.getAccountNameById(3)  -- 创建多语句表值函数    -- 定义一个多语句表值函数 其功能是返回所有账户的一卡通存款    if exists (select * from sys.objects where name = 'getAccountInfo')       drop function getAccountInfo    go    create function getAccountInfo()      returns @newAccount table(        accountName varchar(10),        card_no varchar(20),        balance int       )    as     begin       insert into @newAccount        select ac.ACCOUNT_NAME,ap.CARD_NO,ap.BALANCE from ACCOUNT as ac inner join ALL_PURPOSE_CARD as ap           on ac.ACCOUNT_ID = ap.ACCOUNT_ID      return     end    go    -- 使用多语句表值函数(表值函数返回的是表，因此使用的时候类似于查找表中数据)    select ga.card_no as '账户',ga.accountName as '姓名',ga.balance as '金额' from dbo.getAccountInfo() as ga    -- 内联表值函数      -- 多语句表值函数特殊形式      -- 不需要定义table结构       -- 函数内部只有一条select语句      if exists(select * from sys.objects where name = 'getAccountInner')         drop function getAccountInner      go      create function getAccountInner()      returns table      as         return (          select ac.ACCOUNT_NAME ,ap.CARD_NO,ap.BALANCE from ACCOUNT as ac inner join ALL_PURPOSE_CARD as ap            on ac.ACCOUNT_ID = ap.ACCOUNT_ID        )      go       -- 调用内联表值函数(函数直接返回的是select语句的结构)      select * from getAccountInner()      go  /**************** 存储过程 ***********************/  -- 存储过程分类    -- 系统存储过程    -- 用户自定义存储过程  -- 存储过程定义时候不能够定义返回类型，返回值通过传出参数实现  -- 存储过程可以包含逻辑控制语句和数据操作语句，也可以接受参数、输出参数、返回结果集  -- 存储过程是SQL语句和流程控制语句的预编译集合  -- 特点    -- 允许模块化程序设计(存储过程可以相互调用)    -- 减少网络流量     -- 执行速度过快 (预编译)    -- 可以作为安全机制使用(防止SQL注入)  -- 系统存储过程    -- sp_ 开头  -- 扩展存储过程    -- xp_ 开头  -- 存放于master当中，可以在任何数据库当中运行 结果反映在当前数据库当中   -- 使用系统存储过程  use bank    exec sp_databases  -- 查看数据库名称    exec sp_renamedb 'newBank','bank'  -- 使用时候必须确保数据库为单个用户  go    -- 创建存储过程(不带参数)  if exists(select * from sys.objects where name = 'update_getAccount')    drop proc update_getAccount  go  create proc update_getAccount  as     select ACCOUNT_NAME from ACCOUNT where ACCOUNT_ID in (      select ACCOUNT_ID from ALL_PURPOSE_CARD where BALANCE in(        select MIN(BALANCE) from ALL_PURPOSE_CARD       )    )  go  -- 使用存储过程(不带参数)  exec update_getAccount  go  /*******************************/  -- 带参数的存储过程  if exists(select * from sys.objects where name = 'up_insertAccount')    drop proc up_insertAccount  go  create proc up_insertAccount    @accountName varchar(20),    @codeNo varchar(20),    @openTime datetime   as     insert into ACCOUNT values (@accountName,@codeNo,GETDATE())  go  -- 使用带参数的存储过程  exec up_insertAccount '陈聪','1234567890987655','2017-7-23 15:16:02'  go  /************** 带有默认值的存储过程 ********************/   if exists(select * from sys.objects where name = 'up_insertAccount')    drop proc up_insertAccount  go  create proc up_insertAccount    @accountName varchar(20) = '张三',    @codeNo varchar(20),    @openTime datetime   as     insert into ACCOUNT values (@accountName,@codeNo,GETDATE())  go  -- 使用带参数的存储过程  exec up_insertAccount default,'1234567890987655','2017-7-23 15:16:02'  exec up_insertAccount @codeNo = '12345678909876543',@openTime = '2017-7-23 15:18:57'  go  /************* 带有输入输出参数的存储过程 ********************/  -- 使用output关键字来声明有输出参数的存储过程  if exists(select * from sys.objects where name = 'up_getDay')    drop proc up_getDay  go  create proc up_getDay    @day int output,    @time datetime  as     select @day = DATEDIFF(DD,GETDATE(),@time)  go  -- 使用存储过程 -- 这里的leftday用来接收执行存储过程的返回值  -- 参数需要保持一致  declare @leftday int   exec up_getDay @leftday output,'2017-10-1'  print '距离2017-10-1还有 ' + cast(@leftday as varchar(10))