SQL Server -- 自定义函数(学习总结,备忘)
SQL Server自定义函数,以前只在书上看过,没有动手去敲一敲,今天刚好接触到,看了几篇博文学习了下。做好备忘很重要!! (@_@)Y
Learn from:http://www.cnblogs.com/lideng/archive/2013/04/15/3022418.html
自定义函数分为:标量值函数或表值函数两种。
- 标量值函数:如果 RETURNS 子句指定一种标量数据类型,则函数为标量值函数。
- 表值函数:如果 RETURNS 子句指定 TABLE,则函数为表值函数。
表值函数又分为两种:内嵌表值函数(行内函数)或多语句函数
- 如果 RETURNS 子句指定的 TABLE 不附带列的列表,则该函数为内嵌表值函数。
- 如果 RETURNS 子句指定的 TABLE 类型带有列及其数据类型,则该函数是多语句表值函数
自定义函数的基本格式如下:
Create function 函数名(参数) Returns 返回值数据类型 [with {Encryption | Schemabinding }] [as] begin SQL语句(必须有return 变量或值) End
- 如果需要对函数体进行加密,可使用WITH ENCRYPTION;
- 如果需要将创建的函数与引用的数据库绑定,可以使用WITH SCHEMABINDING(注:函数一旦绑定,则不能删除、修改,除非删除绑定)
@_@|| 基本知识认知后,接下来弱弱地试试,
创建一张数据表MyTable1(id, username, user_sex)
id: int
username : nvarchar(20)
user_sex: nchar(2)
然后在表中添加一条数据 (1, ‘lmei‘, ‘女‘)
@_@?? 思考一个问题:char和nchar的区别,varchar和nvarchar的区别,而text和ntext之间又有什么不同呢? @_@??
^_^解答 from:http://www.cnblogs.com/ebaidu/archive/2007/08/14/854778.html
很多开发者进行数据库设计的时候往往并没有太多的考虑char, varchar类型,有的是根本就没注意,因为存储价格变得越来越便宜了,忘记了最开始的一些基本设计理论和原则,这点让我想到了现在的年轻人,大手一挥一把人民币就从他手里溜走了,其实我想不管是做人也好,做开发也好,细节的把握直接决定很多东西。当然还有一部分人是根本就没弄清楚他们的区别,也就随便选一个。在这里我想对他们做个简单的分析,当然如果有不对的地方希望大家指教。
1、CHAR。CHAR存储定长数据很方便,CHAR字段上的索引效率级高,比如定义char(10),那么不论你存储的数据是否达到了10个字节,都要占去10个字节的空间,不足的自动用空格填充,所以在读取的时候可能要多次用到trim()。
2、VARCHAR。存储变长数据,但存储效率没有CHAR高。如果一个字段可能的值是不固定长度的,我们只知道它不可能超过10个字符,把它定义为 VARCHAR(10)是最合算的。VARCHAR类型的实际长度是它的值的实际长度+1。为什么“+1”呢?这一个字节用于保存实际使用了多大的长度。从空间上考虑,用varchar合适;从效率上考虑,用char合适,关键是根据实际情况找到权衡点。
3、TEXT。text存储可变长度的非Unicode数据,最大长度为2^31-1(2,147,483,647)个字符。
4、NCHAR、NVARCHAR、NTEXT。这三种从名字上看比前面三种多了个“N”。它表示存储的是Unicode数据类型的字符。我们知道字符中,英文字符只需要一个字节存储就足够了,但汉字众多,需要两个字节存储,英文与汉字同时存在时容易造成混乱,Unicode字符集就是为了解决字符集这种不兼容的问题而产生的,它所有的字符都用两个字节表示,即英文字符也是用两个字节表示。nchar、nvarchar的长度是在1到4000之间。和char、varchar比较起来,nchar、nvarchar则最多存储4000个字符,不论是英文还是汉字;而char、varchar最多能存储8000个英文,4000个汉字。可以看出使用nchar、nvarchar数据类型时不用担心输入的字符是英文还是汉字,较为方便,但在存储英文时数量上有些损失。
所以一般来说,如果含有中文字符,用nchar/nvarchar,如果纯英文和数字,用char/varchar
把他们的区别概括成:
CHAR,NCHAR 定长,速度快,占空间大,需处理
VARCHAR,NVARCHAR,TEXT 不定长,空间小,速度慢,无需处理
NCHAR、NVARCHAR、NTEXT处理Unicode码
回归正题@_@, 体验自定义函数~~
- 新建多语句表值函数
--多句表格值函数 create function 函数名(参数) returns 表格变量名table (表格变量定义) [with {Encryption | Schemabinding }] as begin SQL语句 end --多句表格值函数包含多条SQL语句,至少有一条在表格变量中填上数据值
示例:新建一个表值函数,如下
create function GetInfo(@user_id int) returns @table table(username nvarchar(50),user_sex nchar(2)) as begin insert into @table select username,user_sex from MyTable1 where id = @user_id return --函数中最后一条语句必须是返回语句。
end
然后到查询分析器code
select * from GetInfo(1)
输出结果是一张表格
username | user_sex | |
1 | lmei | 女 |
- 新建标量值函数
CREATE FUNCTION Foo(@a int ) --传入了一个int类型的参数 RETURNS int --注意这里返回的是一个数据类型 AS BEGIN declare @b int set @b = 3 return @a + @b END
然后到查询分析器里code
select dbo.foo(2)
输出结果为:5
请注意“dbo.”。创建函数的时候不需要加dbo.,但在访问的时候,标量函数要加.dbo的,否则的话会报错“不是可以识别的内置函数名称”。
更多的操作请见:http://www.cnblogs.com/lideng/archive/2013/04/15/3022418.html
附录:SQL编程基本语法(Learn form:http://tech.it168.com/a2012/0212/1310/000001310463_all.shtml)
- 定义语句:
--简单赋值 declare @a int set @a=5 print @a --使用select语句赋值 declare @user1 nvarchar(50) select @user1=‘张三‘ print @user1 declare @user2 nvarchar(50) select @user2 = Name from ST_User where ID=1 print @user2 --使用update语句赋值 declare @user3 nvarchar(50) update ST_User set @user3 = Name where ID=1 print @user3
- 表格相关的一些语句
--创建临时表1 create table #DU_User1 ( [ID] [int] NOT NULL, [Oid] [int] NOT NULL, [Login] [nvarchar](50) NOT NULL, [Rtx] [nvarchar](4) NOT NULL, [Name] [nvarchar](5) NOT NULL, [Password] [nvarchar](max) NULL, [State] [nvarchar](8) NOT NULL ); --向临时表1插入一条记录 insert into #DU_User1 (ID,Oid,[Login],Rtx,Name,[Password],State) values (100,2,‘LS‘,‘0000‘,‘临时‘,‘321‘,‘特殊‘); --从ST_User查询数据,填充至新生成的临时表 select * into #DU_User2 from ST_User where ID<8 --查询并联合两临时表 select * from #DU_User2 where ID<3 union select * from #DU_User1 --删除两临时表 drop table #DU_User1 drop table #DU_User2 --创建临时表 CREATE TABLE #t ( [ID] [int] NOT NULL, [Oid] [int] NOT NULL, [Login] [nvarchar](50) NOT NULL, [Rtx] [nvarchar](4) NOT NULL, [Name] [nvarchar](5) NOT NULL, [Password] [nvarchar](max) NULL, [State] [nvarchar](8) NOT NULL, ) --将查询结果集(多条数据)插入临时表 insert into #t select * from ST_User --不能这样插入 --select * into #t from dbo.ST_User --添加一列,为int型自增长子段 alter table #t add [myid] int NOT NULL IDENTITY(1,1) --添加一列,默认填充全球唯一标识 alter table #t add [myid1] uniqueidentifier NOT NULL default(newid()) select * from #t drop table #t --给查询结果集增加自增长列 --无主键时: select IDENTITY(int,1,1)as ID, Name,[Login],[Password] into #t from ST_User select * from #t --有主键时: select (select SUM(1) from ST_User where ID<= a.ID) as myID,* from ST_User a order by myID --定义表变量 declare @t table ( id int not null, msg nvarchar(50) null ) insert into @t values(1,‘1‘) insert into @t values(2,‘2‘) select * from @t
- 循环语句
--while循环计算1到100的和 declare @a int declare @sum int set @a=1 set @sum=0 while @a<=100 begin set @sum+=@a set @a+=1 end print @sum
- 条件语句
--while循环计算1到100的和 declare @a int declare @sum int set @a=1 set @sum=0 while @a<=100 begin set @sum+=@a set @a+=1 end print @sum
- 游标
declare @ID int declare @Oid int declare @Login varchar(50) --定义一个游标 declare user_cur cursor for select ID,Oid,[Login] from ST_User --打开游标 open user_cur while @@fetch_status=0 begin --读取游标 fetch next from user_cur into @ID,@Oid,@Login print @ID --print @Login end close user_cur --摧毁游标 deallocate user_cur
- 触发器
--创建触发器 Create trigger User_OnUpdate On ST_User for Update As declare @msg nvarchar(50) --@msg记录修改情况 select @msg = N‘姓名从“‘ + Deleted.Name + N‘”修改为“‘ + Inserted.Name + ‘”‘ from Inserted,Deleted --插入日志表 insert into [LOG](MSG)values(@msg) --删除触发器 drop trigger User_OnUpdate
- 存储过程
--创建带output参数的存储过程 CREATE PROCEDURE PR_Sum @a int, @b int, @sum int output AS BEGIN set @sum=@a+@b END --创建Return返回值存储过程 CREATE PROCEDURE PR_Sum2 @a int, @b int AS BEGIN Return @a+@b END --执行存储过程获取output型返回值 declare @mysum int execute PR_Sum 1,2,@mysum output print @mysum --执行存储过程获取Return型返回值 declare @mysum2 int execute @mysum2= PR_Sum2 1,2 print @mysum2
- 自定义函数
--新建标量值函数 create function FUNC_Sum1 ( @a int, @b int ) returns int as begin return @a+@b end --新建内联表值函数 create function FUNC_UserTab_1 ( @myId int ) returns table as return (select * from ST_User where ID<@myId) --新建多语句表值函数 create function FUNC_UserTab_2 ( @myId int ) returns @t table ( [ID] [int] NOT NULL, [Oid] [int] NOT NULL, [Login] [nvarchar](50) NOT NULL, [Rtx] [nvarchar](4) NOT NULL, [Name] [nvarchar](5) NOT NULL, [Password] [nvarchar](max) NULL, [State] [nvarchar](8) NOT NULL ) as begin insert into @t select * from ST_User where ID<@myId return end --调用表值函数 select * from dbo.FUNC_UserTab_1(15) --调用标量值函数 declare @s int set @s=dbo.FUNC_Sum1(100,50) print @s --删除标量值函数 drop function FUNC_Sum1
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。