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 类型带有列及其数据类型,则该函数是多语句表值函数

 

自定义函数的基本格式如下:

SQL Server -- 自定义函数(学习总结,备忘)
Create function 函数名(参数)
Returns 返回值数据类型
[with {Encryption | Schemabinding }]
[as]
begin
SQL语句(必须有return 变量或值)
End
SQL Server -- 自定义函数(学习总结,备忘)
  • 如果需要对函数体进行加密,可使用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码

 

回归正题@_@, 体验自定义函数~~

  • 新建多语句表值函数
SQL Server -- 自定义函数(学习总结,备忘)
--多句表格值函数
   create function 函数名(参数)
   returns 表格变量名table (表格变量定义)
   [with {Encryption | Schemabinding }]
  as
   begin
    SQL语句
   end
--多句表格值函数包含多条SQL语句,至少有一条在表格变量中填上数据值
SQL Server -- 自定义函数(学习总结,备忘)

 

示例:新建一个表值函数,如下

SQL Server -- 自定义函数(学习总结,备忘)
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
SQL Server -- 自定义函数(学习总结,备忘)

 然后到查询分析器code

select * from GetInfo(1)

输出结果是一张表格

  username user_sex
   1    lmei

 

 

 

  • 新建标量值函数
SQL Server -- 自定义函数(学习总结,备忘)
CREATE FUNCTION Foo(@a int )  --传入了一个int类型的参数
RETURNS int       --注意这里返回的是一个数据类型
AS  
BEGIN 
    declare @b int
    set @b = 3
    return @a + @b
END
SQL Server -- 自定义函数(学习总结,备忘)

然后到查询分析器里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

  • 定义语句:
SQL Server -- 自定义函数(学习总结,备忘)
--简单赋值

  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
View Code
  • 表格相关的一些语句
SQL Server -- 自定义函数(学习总结,备忘)
  --创建临时表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
View Code
  • 循环语句
SQL Server -- 自定义函数(学习总结,备忘)
  --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
View Code
  • 条件语句
SQL Server -- 自定义函数(学习总结,备忘)
  --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
View Code
  • 游标
SQL Server -- 自定义函数(学习总结,备忘)
 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
View Code
  • 触发器
SQL Server -- 自定义函数(学习总结,备忘)
  --创建触发器

  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
View Code
  • 存储过程
SQL Server -- 自定义函数(学习总结,备忘)
  --创建带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
View Code
  • 自定义函数
SQL Server -- 自定义函数(学习总结,备忘)
 --新建标量值函数

  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
View Code

 

SQL Server -- 自定义函数(学习总结,备忘),布布扣,bubuko.com

SQL Server -- 自定义函数(学习总结,备忘)

上一篇:InnoDB的多版本并发控制(MMVC)


下一篇:[转]java gridbag 说明