Sql Server函数和存储过程

SQL Server函数是一种封装一条或多条SQL语句的结构。
SQL Server函数分为系统函数和用户自定义函数两种。

标量值函数：标量值函数的返回值是基本数据类型的单个值或单个值得表达式。
函数体既可以是一条语句，也可以是多条语句。

创建标量值函数
语法：
CREATE FUNCTION [schema_name.]function_name([{@parameter_name parameter_data_type[=default_value]}[...n]])RETURNS return_data_type AS
BEGIN
Function_body
RETURN scalar_expression
END
语法说明：
1、[]中的内容都是可选的。
2、schema_name指定函数的架构名。
3、@parameter_name指定函数的参数名。
4、parameter_data_type指定参数的数据类型。
5、default_value指定参数的默认值。
6、RETURNS关键字指定函数的返回类型。
7、function_body指定函数体。
8、RETURN语句指定函数返回值或表达式。
示例如下：
USE Bank
GO
CREATE FUNCTION getAccountName
(
@account_id int ---参数

)
RETURNS varchar(20)--返回varchar(20)
AS
BEGIN
DECLARE @accountName varchar(20)
select @accountName=account_name from Account where account_id=@account_id
RETURN @accountName--返回值
END
GO
使用标量值函数
在使用标量值函数时，先传入函数要求的参数，然后通过SELECT语句将标量值函数的返回
值赋给变量，也可以直接输出
--直接输出标量
select dbo.getAccountName(1) as 账户名称
* 注意：在调用函数的过程中，必须在函数名前添加“dbo.”。否则，无法辨认该函数是内部函数，还是自定义函数。
--将标量值函数的返回值存入变量
DECLARE @accountName varchar(20)
select @accountName=getAccountName(1)
print '账户名称为：'+@accountName
说明：变量值函数可以被另外的标量值函数或表值函数调用。

表值函数：表值函数的返回结果为数据表。表值函数功能强大，甚至在大多数情况下可以替代视图。视图无法定义参数，而表值函数却
可以作为带参数的视图使用。表值函数可以分为多语句表值函数和内联表值函数。
1、多语句表值函数：
多语句表值函数要求返回类型为TABLE类型，其与标量值函数的区别是需要在函数定义的时候，在RETURNS关键字后面指定返回的表的结构。
语法：
CREATE FUNCTION [schema_name.]function_name ([{@parameter_name parameter_data_type [=default_value]}[,...n]])
RETURNS @table_var_name TABLE(table_definition) AS
BEGIN
Function_body
RETURN
END
多语句表值函数的定义语法与标量值函数的定义语法相比有两点区别：一是函数声明中的RETURNS后面
是TABLE类型且必须指定结构;二是函数体中的RETURN后面无需在写返回的值或表达式。
示例如下：
USE Bank
GO
--函数执行完毕后返回TABLE类型的变量@deposeitTable
create function getDeposit()
returns @depositTable table
(
accountName varchar(20),
balance float
)
AS
BEGIN
--为TABLE类型的变量赋值
insert into @depositTable
select account_name,balance from account,all_purpose_card where account.ACCOUNT_ID=all_purpost_card.ACCOUNT_ID
return --无需在写值或表达式，直接返回变量@depositTable
END
GO
调用表值函数时，可以将其作为普通表使用
示例如下：
--使用表值函数getDeposit
select * from getDeposit()

使用带参数的表值函数：
USE Bank
GO
--参数要求传入账户名称
create function getDeposit(@accountName varchar(20))
returns @depositTable table
(
accountName varchar(20),
balance float
)
AS
BEGIN
--在子查询中使用参数@accountName
insert into @depositTable
select account_name,balance from account,all_purpose_card where account.ACCOUNT_ID=all_purpost_card.ACCOUNT_ID andaccount_name=@accountName
return
END
GO
调用多语句表值函数：
select * from getDeposit(‘鲁迅’)

内联表值函数：
内联表值函数是多语句表值函数的一种特殊形式，与多语句表值函数的创建方式基本相同。二者
的区别在于内联表值函数只能有一条SELECT语句，且无须定义返回TABLE类型的变量结构，可以
在RETURN关键字后面直接返回SELECT语句的结果。
语法：
CREATE FUNCTION [schema_name.]function_name ([{@paramet_name parameter_data_type [=default_value]}[,...n]])
RETURNS TABLE AS
Function_body
RETURN [select_stmt]
RETURNS关键字后面不需要定义TABLE 类型的变量，也无需表结构的定义。在RETURN语句后面
直接使用SELECT语句查询数据行，并返回结果。
创建一个内联表值函数getAccount,并接受一个参数，依据传入的参数返回账号信息，示例如下：
USE Bank
GO
create function getAccount(@account_id int)
returns table
AS
return
(
select * from Account where account_id=@account_id
)
调用内联表值函数：
select * from getAccount(5)

存储过程的优点：
1、允许模块化程序设计
2、执行速度更快
3、减少网络流量
4、可以作为安全机制使用
                                          常用的系统存储过程
           
   系统存储过程名称                             说明
   sp_databases                                 列出服务器上所有的数据库
   sp_hepdb                                     报告有关指定数据库或所有数据库的信息
   sp_renamedb                                  重命名数据库
   sp_tables                                    返回当前环境下任何能够在FROM子句中出现的对象
   sp_columns                                   查看某个表的列表信息
   sp_help                                      查看某个表的所有信息
   sp_helpcoonstraint                           查看某个表的约束
   sp_helpindex                                 查看某个表的索引
   sp_stored_procedure                          列出当前环境中所有的存储过程
   sp_password                                  添加或修改登录账户的密码     
   sp_helptext                                  显示默认值、未加密的存储过程、用户定义的存储过程、触发器或者视图的实际文本
   sp_addrole                                   向当前数据库中创建新的数据角色
   sp_adduser                                   向当前数据添加新的用户
   sp_cmdshell                                  使用DOS命令操作文件和目录
   sp_logevent                                  将用户定义的消息记入SQL Server日志文件盒Windows事件查看器

用户自定义的存储过程
语法：
CREATE PROC[EDURE] proc_name
[{@parameter_name data_type}=[默认值]] [OUTPUT,..,n]
AS
procedure_body
语法说明：PROCEDURE可以省略为PROC,proc_name指存储过程名称，存储过程名称后面是
参数列表，为可选项。如果有参数，则需要指定参数的数据类型；如果有默认值，则需要
指定参数的默认值。也可以为参数指定OUTPUT关键字，表示参数为传出参数。AS关键字
后面的procedure_body表示存储过程的主体是存储过程的核心。
1、创建不带参数的存储过程
USE Bank
GO
if exists(select * from sysobjects where name='proc_min_balance')
drop procedure proc_min_balance
GO
create proc_min_balance
AS
select ACCOUNT_NAME from ACCOUNT where ACCOUNT_ID=
(
select top 1 ACCOUNT_ID from ALL_PURPOSE_CARD order by BALANCE

)
GO
调用存储过程通过EXEC或EXECUTE命令执行
USE Bank
GO
exec proc_min_balance
2、带输入参数的存储过程
在其他语言中，如果方法有参数，则在调用时需要传递实际参数值。
--创建带参数的存储过程，依据传入的数据添加账户信息
USE Bank
GO
if exists(select * from sysobjects where name='proc_Account_Insert')
drop procedure proc_Account_Insert
GO
create proc proc_Account_Insert
@Account_Name varchar(20),
@code varchar(18),
@open_time datetime
AS
insert into ACCOUNT values(@Account_Name,@code,@open_time)
if(@@ERROR=0)
print 'ok'
else
print 'error' 
GO
执行带参数的存储过程，需要将实际参数值传入存储过程中
exec proc_Account_Insert '诸葛亮','546646265656651','2011-01-02'
   
   
带输出参数的存储过程
如果需要存储过程返回一个值或多个值，可以使用输出参数。输出参数必须在存储过程定义
时使用OUTPUT关键字进行声明
提示：存储过程也可以通过RETURN返回值，但通常只会返回一些执行状态值。
示例如下：
USE Bank
GO
if exists(select * from sysobjects where name='proc_getDay')
drop procedure proc_getDay
GO
create proc proc_getDay
@day int output,--输出参数，返回天数
@date datetime
AS
select @day=datediff(day,@date,CETDATE())
GO
在调用带传出函数的存储过程是，需要首先定义对应的变量作为实际参数，并且必须
在实际参数后使用OUTPUT关键字。执行存储过程成功后，就可以通过变量得到存储
过程传出的参数值。
--先定义变量，与传出参数类型保持一致
declare @day int
--执行存储过程时，将变量作为实际参数，并使用OUTPUT关键字进行说明
exec proc_getDay @day output, '2012-01-03'
--执行完毕后，通过变量得到存储过程传出的值
print '距当前天数：'+cast（@day as varchar(4)）
GO