SQL Server 2005 自定义函数语汇小结
由于工作的需要，了解下SQL Server 2005 函数的写法，现在总结一下：
对于SQL Server 2005 数据库而言，函数与存储过程在语法方面是有很大的相同点，
最大的不同就是函数有返回值，直接使用returns ,而存储过程则使用output来声明输出变量
一、下面先说明下，如何创建函数
1、创建没有返回值与没有参数的函数
CREATE FUNCTION my_function()
AS
BEGIN
DECLARE @variable varchar(255) --声明字符型变量
DECLARE @variable int --声明整形型变量
...(do something)
SET @variable = '12345' --对变量variable赋值
END
2、创建没有返回值有参数的函数
CREATE FUNCTION my_function(@user_Name varchar(128),@password int(6))
AS
BEGIN
DECLARE @variable_1 varchar(255) --声明字符型变量
...(do something)
SET @variable_1 = @user_Name + convert(varchar(255),@password) --将变量@user_Name与@password连接赋给@variable_1，其中convert()函数是将int型转为varchar型
END
3、创建有返回值与有参数的函数
CREATE FUNCTION my_function(@user_Name varchar(128),@password int(6))
returns varchar(255)--设置返回值，记住是returns 而不是return
AS
BEGIN
DECLARE @result varchar(5)
DECLARE @fagle varchar(5)
SET @result = select https://www.doczj.com/doc/435957486.html,er_Name from USERS as users where https://www.doczj.com/doc/435957486.html,er_Name = @user_Name and users.password = @password
IF @result = ''
BEGIN
SET @fagle = 'NO'
END
ELSE
BEGIN
SET @falge = 'YES'
END
return @result --返回结果
END
二、删除一个函数语法
DROP FUNCTION my_function
三、执行一个函数语法
select dbo.my_function(...) --根据有没参数来处理
go
--注：在SQL Server 2005 中，有内部函数与外部函数，数据库系统自带函数，如sum()，count()等等，这些称为内部函数，而我们自定义的函数称为外部函数。
--在执行函数语法中，也有些区别，如执行内部函数：select sum(total) from ...，那么执行外部函数则需要在函数名前加dbo. + 自定义函数名，如
--select dbo.my_function()
--go
四、下面提供二个例子
1、日期判断，判断传进来的日期是否在上个月日到本月日之间
--请注意convert()函数的用法
create function isNewContract(@date varchar(32))
returns varchar(32)
AS
begin
declare @begin_time varchar(32)
declare @end_time varchar(32)
declare @temp varchar(4)
declare @result varchar(32)
declare @month varchar(2)
set @begin_time=convert(varchar,DATEPART(year, getdate())) + '-'
set @end_time=convert(varchar,DATEPART(year, getdate())) + convert(varchar, DATEPART(month, getdate())) + convert(varchar, '14')
set @temp=convert(varchar(2),(month(getDate())-1 ))
if(@temp < 10 )
begin
set @begin_time = @begin_time + convert(varchar(1),'0') + @temp + '-' + convert(varchar, '15')
end
if(@date >= @begin_time and @date <= @end_time)
begin
set @result = '是'
end
else
begin
set @result = @begin_time
end
return @result
end
2、查询多条记录合并成一条记录返回，并写入EXECL表中，进行分行换行显示，其中使用游标进行循环处理
--在数据库中，EXECL默认换行符ACSII码为'10',在合并字符串之前，需要先转为EXECL识别
的换行符，具体使用chat()内部函数处理
create function getExportReportCollect(@projectId int ,@month varchar(64))
returns varchar(4096) --设置返回值
AS
begin
declare @num varchar(2)
declare @end varchar(10)
declare @Result varchar(4096) --用于返回查询结果
declare city_cursor cursor for --声明游标变量
select reported.id
from dbo.Investment_Budget_Reported as reported
left join dbo.Investment_Budget_Contract as con
on reported.contractId = con.id
where reported.[month] = @month and reported.projectId = @projectId
set @Result=''
set @num = '1'
set @end = '10' --导入EXECL表中，换行符的ACSII码
declare @Field int --声明临时存放CityID的变量
open city_cursor --打开游标
fetch next from city_cursor into @Field --将实际ID赋给变量
while(@@fetch_status = 0) --循环开始
begin
if((select [content] from dbo.Investment_Budget_Reported where id = @Field )is not null) BEGIN
if(@Result = '')
select @Result = @Result + @num + '、' + [content] from dbo.Investment_Budget_Reported where id = @Field
else
select @Result = @Result + @num + '、' + [content] from dbo.Investment_Budget_Reported where id = @Field
set @Result = @Result + char(@end) --让换行符转为EXECL认识的换行符
set @num = @num + 1
END
fetch next from city_cursor into @Field --下一个reportId
end
close city_cursor --关闭游标
deallocate city_cursor --释放游标引用
return @Result
end
基于自定义函数的Function-Based索引创建 发表日期：2008-2-9 新浪微博QQ空间QQ微博百度搜藏腾讯朋友QQ收藏百度空间人人网开心网 - 留言版上的第2330号问题是: 在oralce中给自建函数创建索引，结果不成功。source:Create Index IDX_T_SP_TWOTYPESTA T_0_f On T_SP_TWOTYPESTA T_0(f_dateadd(yearmonth,12,2)); err:the function is not deterministic. 我们看一下这是为什么? 随便一个测试可以再现这个问题,我门创建一个函数(本范例函数用于进行16进制向10进制转换): CREA TE OR REPLACE FUNCTION h2ten ( p_str IN V ARCHAR2, p_from_base IN NUMBER DEFAULT 16 ) RETURN NUMBER IS l_num NUMBER DEFAULT 0; l_hex V ARCHAR2 (16) DEFAULT '0123456789ABCDEF'; BEGIN FOR i IN 1 .. LENGTH (p_str) LOOP l_num := l_num * p_from_base + INSTR (l_hex, UPPER (SUBSTR (p_str, i, 1))) - 1; END LOOP; RETURN l_num; END h2ten; 此时创建索引,获得如下错误信息: SQL> create table t as select username,'a' hex from dba_users; Table created SQL> create index i_t on t (h2ten(hex)); create index i_t on t (h2ten(hex)) ORA-30553: The function is not deterministic
SQL Server 2005 自定义函数语汇小结 由于工作的需要，了解下SQL Server 2005 函数的写法，现在总结一下： 对于SQL Server 2005 数据库而言，函数与存储过程在语法方面是有很大的相同点， 最大的不同就是函数有返回值，直接使用returns ,而存储过程则使用output来声明输出变量 一、下面先说明下，如何创建函数 1、创建没有返回值与没有参数的函数 CREATE FUNCTION my_function() AS BEGIN DECLARE @variable varchar(255) --声明字符型变量 DECLARE @variable int --声明整形型变量 ...(do something) SET @variable = '12345' --对变量variable赋值 END 2、创建没有返回值有参数的函数 CREATE FUNCTION my_function(@user_Name varchar(128),@password int(6)) AS BEGIN DECLARE @variable_1 varchar(255) --声明字符型变量 ...(do something) SET @variable_1 = @user_Name + convert(varchar(255),@password) --将变量@user_Name与@password连接赋给@variable_1，其中convert()函数是将int型转为varchar型 END 3、创建有返回值与有参数的函数 CREATE FUNCTION my_function(@user_Name varchar(128),@password int(6)) returns varchar(255)--设置返回值，记住是returns 而不是return AS BEGIN DECLARE @result varchar(5) DECLARE @fagle varchar(5) SET @result = select https://www.doczj.com/doc/435957486.html,er_Name from USERS as users where https://www.doczj.com/doc/435957486.html,er_Name = @user_Name and users.password = @password IF @result = '' BEGIN SET @fagle = 'NO' END ELSE BEGIN SET @falge = 'YES' END return @result --返回结果 END 二、删除一个函数语法
SQL Server用户自定义函数 用户自定义函数不能用于执行一系列改变数据库状态的操作，但它可以像系统函数一样在查询或存储过程等的程序段中使用，也可以像存储过程一样通过EXECUTE 命令来执行。在 SQL Server 中根据函数返回值形式的不同将用户自定义函数分为三种类型： (1) 标量函数 标量函数返回一个确定类型的标量值，其返回值类型为除 TEXT 、 NTEXT 、IMAGE 、 CURSOR 、 TIMESTAMP 和 TABLE 类型外的其它数据类型。函数体语句定义 在 BEGIN-END 语句内。在 RETURNS 子句中定义返回值的数据类型，并且函数的最后一条语句必须为 Return 语句。创建标量函数的格式： Create Function 函数名（参数） Returns 返回值数据类型 [With {Encryption|Schemabinding}] [AS] BEGIN SQL 语句 ( 必须有 Return 子句 ) END 举例： ******************************************************************* CREATE FUNCTION dbo.Max ( @a int, @b int ) RETURNS int AS BEGIN DECLARE @max int IF @a>@b SET @max=@a ELSE SET @max=@b Return @max END *******************************************************************调用标量函数可以在 T-SQL 语句中允许使用标量表达式的任何位置调用返 回标量值（与标量表达式的数据类型相同）的任何函数。必须使用至少由两部分组成名称的函数来调用标量值函数，即架构名 . 对象名，如 dbo.Max(12,34) 。 (2) 内联表值函数 内联表值型函数以表的形式返回一个返回值，即它返回的是一个表。内联表 值型函数没有由 BEGIN-END 语句括起来的函数体。其返回的表是由一个位于RETURN 子句中的 SELECT 命令从数据库中筛选出来。内联表值型函数功能相当 于一个参数化的视图。
函数 在Transact - SQL语言中，函数被用来执行一些特殊的运算以支持SQL Server的标准命令。 (1 )?行集函数：行集函数可以在transact?SQL语句中当作表引用。 (2).聚合函数：用于一组值执行计算并返回一个单一的值。 (3 ).标量函数：用于对传递给它的一个或者多个参数值进行处理和计算，并返回一个单一的值. (一)、标量函数的分类 1 ?配置函数：返回当前的配置信息 2 ?游标函数：返回有关游标的信息 3 ?日期和时间函数：用于对日期和时间类型的输入值进行操作，返回一个了子符串，数字或日期和时间值 4 ?数学函数：用于对作为函数参数提供的输入值执行操作，返冋一个数字值 5 ?元数据函数：返回有关数据库和对象和信息 6 ?字符串函数：对字符串输入值执行操作，并返回一个字中或数字值 7 ?系统函数：执行系统操作 8 ?系统统计函数：返回系统的统计信息 9 ?文本和图像函数：对于文本或图像输入值或列执行操作，返冋有关这些值的信息。 (二)、具体讲解： 1 ?系统函数 用于返回有关SQL Server系统，用户，数据库和数据库对彖的信息。系统函数可以让用户在得到信息后，使用条件语句，根据返回的信息进行不同的操作。与其它函数- 样，可以在select语句的select和where子句经及表达式中使用系统函数。 例：返回taihang数据库的yuan表中的第二列的名称。 use taihang select col_name ( object_id ( * yuan *),2) 注：col_name为系统函数,object_id :返回对彖的id。 2 ?日期和时间类型 日期和时间函数用于对日期和时间数据进行各种不同的处理和运算，并返回一个字符串，数字值或日期和时间值。 dateadd ( datepart , number ,date) dated iff ( datepart ,date1 ,date2) datename ( datepart ,date) datepart ( datepart ,date) day (date) getdate () month (date) year (date) 例1：从getdate函数返回的日期中提取月份数 select datepart ( month , getdate ()) as * month number * 注:datepart为系统函数 例2：从03/12/ 1998中返回月份、天数和年份数
oracle 自定义函数入门 博客分类： oracle 用户定义函数是存储在数据库中的代码块，可以把值返回到调用程序。调用时如同系统函数一样，如max(value)函数，其中，value被称为参数。函数参数有3种类型。 IN 参数类型：表示输入给函数的参数。 OUT 参数类型：表示参数在函数中被赋值，可以传给函数调用程序。 IN OUT参数类型：表示参数既可以传值也可以被赋值。 １、语法格式： SQL语法方式创建的语法格式为： CREATE OR REPLACE FUNCTION function_name /*函数名称*/ ( Parameter_name1,mode1 datatype1,/*参数定义部分*/ Parameter_name2,mode2 datatype2, Parameter_name3,mode3 datatype3 … ) RETURN return_datatype/*定义返回值类型*/ IS/AS BEGIN Function_body/*函数体部分*/ RETURN scalar_expression /*返回语句*/ END function_name; 说明： function_name:：用户定义的函数名。函数名必须符合标示符的定义规则，对其所有者来说，该名在数据库中是唯一的。 parameter：用户定义的参数。用户可以定义一个或多个参数。 mode：参数类型。 datatype：用户定义参数的数据类型。 return_type:：用户返回值的数据类型。
函数返回scalar_expression表达式的值，function_body函数体由pl/sql语句构成。２、示例 函数代码： create or replace function T01001_count return number is count_T01001 number; begin select count(*) into count_T01001 from T01001; return(count_T01001); end T01001_count; --记得一定要打分号 调用： declare i number; begin i:=T01001_count(); dbms_output.put_line(to_char(i)); end;--记得一定要打分号 注意： (1)如果函数没有参数，那么函数名后不应该要括号； (2)创建函数的时候end后面一定要记得写函数名 --没有参数的函数 create or replace function get_user return varchar2 is v_user varchar2(50); begin select username into v_user from user_users; return v_user; end get_user; --测试 方法一
Sql函数说明 一旦成功地从表中检索出数据，就需要进一步操纵这些数据，以获得有用或有意义的结果。这些要求包括：执行计算与数学运算、转换数据、解析数值、组合值和聚合一个范围内的值等。 下表给出了T-SQL函数的类别和描述。 函数的组成 函数的目标是返回一个值。大多数函数都返回一个标量值(scalar value)，标量值代表一个数据单元或一个简单值。实际上，函数可以返回任何数据类型，包括表、游标等可返回完整的多行结果集的类型。本章不准备讨论到这个深度，第12章将讲解如何创建和使用用户自定义函数，以返回更复杂的数据。 函数己经存在很长时间了，它的历史比SQL还要长。在几乎所有的编程语言中，函数调用的方式都是相同的： Result=Function() 在T-SQL中，一般用SELECT语句来返回值。如果需要从查询中返回一个值，就可以把SELECT当成输出运算符，而不用使用等号： SELECT Function() 一个论点
对于SQL函数而言，参数表示输入变量或者值的占位符。函数可以有任意个参数，有些参数是必须的，而有些参数是可选的。可选参数通常被置于以逗号隔开的参数表的末尾，以便于在函数调用中去除不需要的参数。 在SQL Server在线图书或者在线帮助系统中，函数的可选参数用方括号表示。在下列的CONVERT()函数例子中，数据类型的length和style参数是可选的: CONVERT (data-type [(length)], expression[,style]) 可将它简化为如下形式，因为现在不讨论如何使用数据类型： CONVERT(date_type, expression[,style]) 根据上面的定义，CONVERT()函数可接受2个或3个参数。因此，下列两个例子都是 这个函数的第一个参数是数据类型Varchar(20)，第2个参数是另一个函数GETDATE()。GETDATE()函数用datetime数据类型将返回当前的系统日期和时间。第2条语句中的第3个参数决定了日期的样式。这个例子中的101指以mm/dd/yyyy格式返回日期。本章后面将详细介绍GETDATE()函数。即使函数不带参数或者不需要参数，调用这个函数时也需要写上一对括号，例如GETDATE()函数。注意在书中使用函数名引用函数时，一定要包含括号，因为这是一种标准形式。 确定性函数 由于数据库引擎的内部工作机制，SQL Server必须根据所谓的确定性，将函数分成两个不同的组。这不是一种新时代的信仰，只和能否根据其输入参数或执行对函数输出结果进行预测有关。如果函数的输出只与输入参数的值相关，而与其他外部因素无关，这个函数就是确定性函数。如果函数的输出基于环境条件，或者产生随机或者依赖结果的算法，这个函数就是非确定性的。例如，GETDATE()函数是非确定性函数，因为它不会两次返回相同的值。为什么要把看起来简单的事弄得如此复杂呢？主要原因是非确定性函数与全局变量不能在一些数据库编程对象中使用(如用户自定义函数)。部分原因是SQL Server缓存与预编译可执行对象的方式。例如，即席查询可以使用任何函数，不过如果打算构建先进的、可重用的编程对象，理解这种区别很重要。 以下这些函数是确定性的： ●?AVG()(所有的聚合函数都是确定性的) ●?CAST() ●?CONVERT() ●?DATEADD() ●?DATEDIFF() ●?ASCII() ●?CHAR() ●?SUBSTRING() 以下这些函数与变量是非确定性的： ●?GETDATE()
SQL自定义函数split分隔字符串 一、F_Split：分割字符串拆分为数据表 CreateFUNCTION[dbo].[F_Split] ( @SplitString nvarchar(max), --源字符串 @Separator nvarchar(10)=''--分隔符号，默认为空格 ) RETURNS@SplitStringsTable TABLE--输出的数据表 ( [id]int identity(1,1), [value]nvarchar(max) ) AS BEGIN DECLARE@CurrentIndex int; DECLARE@NextIndex int; DECLARE@ReturnText nvarchar(max); SELECT@CurrentIndex=1; WHILE(@CurrentIndex<=len(@SplitString)) BEGIN SELECT@NextIndex=charindex(@Separator,@SplitString,@CurrentIndex); IF(@NextIndex=0OR@NextIndex ISNULL) SELECT@NextIndex=len(@SplitString)+1; SELECT@ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@Cu rrentIndex); INSERTINTO@SplitStringsTable([value]) VALUES(@ReturnText); SELECT@CurrentIndex=@NextIndex+1; END
实验五 T-SQL中的函数定义和调用 一、实验目的 1. 熟悉SQL Server常用的内置函数的功能和调用(包括日期时间函数、字符串处理函数和 数学函数等)。 2. 熟练掌握标量函数、内嵌表值函数、多语句表值函数的定义和调用。 3. 正确区分两种表值函数的应用场合。 二、实验环境 SQL Server 2000个人版及其交互查询工具isqlw(即查询分析器)。 三、实验内容和要求 以下各个题除非独立的程序设计，否则均以图书管理数据库为应用背景。 1. 定义函数ISPRIME，其功能是判断某个整数是否是素数。ISPRIME接受一个INT参数num， 返回BIT类型的判断结果，若参数num是素数返回1；否则返回0。最后，通过对100～200之间的所有整数用ISPRIME函数检验，打印其中的所有素数。(最好在设计ISPRIME 函数时使用内置函数SQRT缩小检查范围，即逐一判断2～sqrt(num)之间是否存在被num 整数的数。另外，在打印时最好分若干行输出素数(提示：用CAST函数进行类型转换)) 2. 定义一个内嵌表值函数book_info，该函数根据调用时传递的书名，返回该书的有关信 息。book_info函数的调用语法是SELECT * FROM book_info('Web站点安全')。 3. 定义一个多语句表值函数reader_history，接受某位读者的借书证号，返回该读者的 借阅历史，包括借书证号、姓名、专业名、所借书籍的ISBN号、借书时间和还书时间，其中还书时间是借书之日起的第30天。(提示：可以考虑使用系统函数DATEADD来计算还书时间。请大家通过联机帮助文档来了解函数DATEADD的语法格式) 4. 定义一个函数，返回某个专业(调用时传递专业名)借书最多的同学的学号。 要求：提交源程序并标识必要的注释。保证程序能正确编译和运行，并根据以下要求认真填写实验报告。
创建用户定义函数，它是返回值的已保存的Transact-SQL 例程。用户定义函数不能用于执行一组修改全局数据库状态的操作。与系统函数一样，用户定义函数可以从查询中唤醒调用。也可以像存储过程一样，通过EXECUTE 语句执行 创建用户定义函数，它是返回值的已保存的 Transact-SQL 例程。用户定义函数不能用于执行一组修改全局数据库状态的操作。与系统函数一样，用户定义函数可以从查询中唤醒调用。也可以像存储过程一样，通过 EXECUTE 语句执行。 用户定义函数用 ALTER FUNCTION 修改，用 DROP FUNCTION 除去。 语法 标量函数 CREATE FUNCTION [ owner_name.] function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS scalar_return_data_type [ WITH < function_option> [ [,] ...n] ] [ AS ] BEGIN function_body RETURN scalar_expression END 内嵌表值函数 CREATE FUNCTION [ owner_name.] function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS TABLE [ WITH < function_option > [ [,] ...n ] ] [ AS ] RETURN [ ( ] select-stmt [ ) ] 多语句表值函数 CREATE FUNCTION [ owner_name.] function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS @return_variable TABLE < table_type_definition > [ WITH < function_option > [ [,] ...n ] ] [ AS ] BEGIN function_body RETURN END
Sqlserver 自定义函数传参有返回值 alter function函数名(@STAFFTITLE int) returns varchar(20) --设置返回值不要把returns写错成return了 AS BEGIN DECLARE@RETURNV ALUE varchar(20) --返回值接收变量 IF@STAFFTITLE= 1 BEGIN SET@RETURNV ALUE='初级职称' END ELSE IF@STAFFTITLE= 2 BEGIN SET@RETURNV ALUE='中级职称' END ELSE IF@STAFFTITLE= 3 BEGIN SET@RETURNV ALUE='高级职称' END ELSE IF@STAFFTITLE= 4 BEGIN SET@RETURNV ALUE='高级专家（教授级高工以上）' END RETURN@RETURNV ALUE--返回结果 END 执行查询函数操作 SELECT dbo.StaffTitles(1) 在sql里执行函数 SELECT StaffID,StaffName,StaffUnit,ISNULL((SELECT dbo.StaffTitles(StaffTitle)),'')as StaffTitle,StaffIdnum,StaffWork,StaffAmount ,StaffPrice,StaffAdPrice,(StaffQuantity*Sta
ffPrice*StaffAmount) as StaffSumPrice,'' as StaffAdSumPrice FROM BERS_STAFF_COSTS_DETAIL
实验八（上）用户自定义函数和触发器 一、实验目的 1、掌握SQLServer中用户自定义函数的使用方法。 2、掌握SQL Server中触发器的使用方法。 二、实验内容和要求 1.创建一个返回标量值的用户定义函数RectangleArea：输入矩形的长和宽就能计算 矩形的面积。自选2种实例调用该函数。 create function RectangleArea(@a int,@b int)returns int as begin return @a*@b end declare @area int execute @area=RectangleArea 3,5 print('矩形面积是：') print @area declare @area int execute @area=RectangleArea 7,8 print('矩形面积是：') print @area 2.创建一个用户自定义函数（内嵌表值函数），功能为产生某个系的学生选修信息， 内容为学号，姓名，课程名，成绩。调用这个函数，显示信息系有选课学生的信息。create function Search (@sdept char(10))returns table as return( select sc.sno 学号,student.sname 姓名,https://www.doczj.com/doc/435957486.html,ame 课程名,sc.grade 成绩,
student.sdept 系别from sc,student,course where https://www.doczj.com/doc/435957486.html,o=https://www.doczj.com/doc/435957486.html,o and sc.sno = student.sno and sdept=@sdept ) select*from Search('cs') 3.创建一个作用在P表上的触发器P_checks，确保用户在插入或更新P表的WEIGHT 值时，所提供的WEIGHT值介于20与40之间，否则给出错误提示并回滚此操作。 请测试该触发器，测试方法自定。 create trigger P_checks on p for insert as begin declare @weight int select @weight=weight from inserted if @weight<10 or @weight>20 begin RAISERROR('weight 必须在~20之间！',16,1) ROLLBACK TRANSACTION end end insert into p(pno,pname,color,weight)values('p7','刀片','红',40) insert into p(pno,pname,color,weight)values('p7','刀片','红',15) select*from p
Oracle之自定义函数 数据库中函数包含四个部分：声明、返回值、函数体和异常处理。--没有参数的函数create or replace function get_user return varchar2 is v_user varchar2(50); begin select username into v_user from user_users; return v_user; end get_user; --测试方法一select get_user from dual; 方法二SQL> var v_name varchar2(50) SQL> exec :v_name:=get_user; PL/SQL 过程已成功完成。SQL> print v_name V_NAME ------------------------------ TEST 方法三SQL> exec dbms_output.put_line('当前数据库用户是：'||get_user); 当前数据库用户是：TEST PL/SQL 过程已成功完成。--带有IN参数的函数create or replace function get_empname(v_id in number) return varchar2 as v_name varchar2(50); begin select name into v_name from employee where id = v_id; return v_name; exception when no_data_found then raise_application_error(-20001, '你输入的ID无效！'); end get_empname; 附：函数调用限制 1、SQL语句中只能调用存储函数(服务器端),而不能调用客户端的函数
create function fGetPy(@Str varchar(500)='') returns varchar(500) as begin declare@strlen int,@return varchar(500),@ii int declare@n int,@c char(1),@chn nchar(1) select@strlen=len(@str),@return='',@ii=0 set@ii=0 while@ii<@strlen begin select@ii=@ii+1,@n=63,@chn=substring(@str,@ii,1) if@chn>'z' select@n=@n+1 ,@c=case chn when@chn then char(@n)else@c end from( select top27*from( select chn='吖' union all select'八' union all select'嚓' union all select'咑' union all select'妸' union all select'发' union all select'旮' union all select'铪' union all select'丌'--because have no 'i' union all select'丌' union all select'咔' union all select'垃' union all select'嘸' union all select'拏' union all select'噢' union all select'妑' union all select'七' union all select'呥' union all select'仨' union all select'他' union all select'屲'--no'u' union all select'屲'--no'v' union all select'屲' union all select'夕' union all select'丫'
自己构造了getsingleresult函数 用到了IF ELSE多嵌套 用len函数判断某行文本数是否为0 用select查询的集合作为衍生表来成为下个select语句的数据源表 把同一行的几个列字符串拼接起来 给函数设置了3个变量，@id1 @id2 @ id3 ，主要是为了能够保证每次只有一行和3个参数的条件匹配，这样函数就没次只处理一行，不会报错，若果通过一个或者两个参数的条件条件即可确定每次处理的是一行，那么就没必要设置那么多函数参数了， SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE function [dbo].[get_singleresult] (@id1 nvarchar(50),@id2 nvarchar(50),@id3 nvarchar(50)) returns nvarchar(1000) as begin declare @str nvarchar(1000) if ((select len (cs1.BRIEFNAME) from T_CHECKRESULT_SUM1 cs1 where SGLCHECKID=@ID1 and CHECKUNITNAME=@ID2 and CHECKITEMNAME=@id3)=0) begin if((select len(cs1.ITEMRESULT) from T_CHECKRESULT_SUM1 cs1 where SGLCHECKID=@ID1 and CHECKUNITNAME=@ID2 and CHECKITEMNAME=@id3)=0) begin select @str='['+cs1.CHECKITEMNAME+'：未见异常'+'],' from T_CHECKRESULT_SUM1 cs1 where SGLCHECKID=@ID1 and CHECKUNITNAME=@ID2 and CHECKITEMNAME=@id3 end
在Hive中创建使用自定义函数 目录 在Hive中创建使用自定义函数 (1) 实际情况 (2) 网传的四种做法 (2) 适宜做法 (3) 原理（hive源代码） (4)
实际情况 Hive提供了不少的函数，但是针对比较复杂的业务，还需要我们手动去定义一些函数来进行一些数据分析处理。Hive的自定义函数包括UDF、UDAF和UDTF，定义模板请参考https://www.doczj.com/doc/435957486.html,/blog/1472371。 网传的四种做法 1.在HIVE会话中add 自定义函数的jar文件，然后创建function，继而使用函数； 2.在进入HIVE会话之前先自动执行创建function，不用用户手工创建； 3.把自定义的函数写到系统函数中，使之成为HIVE的一个默认函数，这样就不需要 create temporary function； 4.在HIVE_HOME的bin目录下新建一个.hiverc的文件,把写好的注册语句写在里面。1.在HIVE会话中add 自定义函数的jar文件，然后创建function，继而使用 小结：这种方式的弊端是：每次打开新的会话，就要重新执行一遍如上的add jar和create temporary function的命令。 2.在进入HIVE会话之前先自动执行创建function HIVE命令有个参数-i：在进入会话，待用户输入自己的HQL之前，先执行-i的参数。我们只需要把add jar和create temporary function的命令写到一个文件中，并把这个文件传到-i的参数，如此一来省去了每次要手工创建的工作。 3.把自定义的函数写到系统函数中，使之成为HIVE的一个默认函数 （1）编写自己的UDF/UDAF/UDTF，并把代码放到 $HIVE_HOME/src/ql/src/java/org/apache/hadoop/hive/ql/udf路径下 （2）修改 $HIVE_HOME/src/ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegis try.java 以HIVE自带函数Trim()举例，自定义函数操作一样。 第一步： 写UDF代码UDFTrim.java并放到 $HIVE_HOME/src/ql/src/java/org/apache/hadoop/hive/ql/udf/UDFTrim.java 第二步：
函数：的一个特性就是具有返回值，如f（x）=x+2，函数除了提供最基本的计算功能外，更主要的功能则是对表数据的格式化，以及对系统信息的查询。 语法类似于存储过程，可以看作是一个带着返回值的特殊存储过程。 自定义函数分类： 1标量函数 2 内嵌表值函数 3 多语句表值函数 标量函数： 是一个简单的数值，例如：int、char、decimal等，但是不能够返回text、ntext、image、cursor和timestamp作为参数。标量函数的函数体要以begin开始，end结束。 内嵌与多语句表值函数： 返回的都是一个table的数据类型，即一个表。其中，内嵌返回的是一个用select语句的查询值，而多语句则返回的是用户自定义的table变量，是包含在begin和end之间的函数体。 标量函数的创建和使用： 语法：CREATE FUNCTION [schema_name.] function_name ([{@parameter_name [AS] [type_schema_name.] parameter_data_type=[default]} [,…n] ] ) RETURNS return_data_type [WITH
CREATE FUNCTION 创建用户定义函数，它是返回值的已保存的 Transact-SQL 例程。用户定义函数不能用于执行一组修改全局数据库状态的操作。与系统函数一样，用户定义函数可以从查询中唤醒调用。也可以像存储过程一样，通过 EXECUTE 语句执行。 用户定义函数用 ALTER FUNCTION 修改，用 DROP FUNCTION 除去。 语法 标量函数 CREATE FUNCTION [ owner_name.] function_name ([ { @parameter_name[AS] scalar_parameter_data_type[ = default ] } [ ,...n ] ] ) RETURNS scalar_return_data_type [ WITH < function_option> [ [,] ...n] ] [ AS ] BEGIN function_body RETURN scalar_expression END 内嵌表值函数 CREATE FUNCTION [ owner_name.] function_name ([ { @parameter_name[AS] scalar_parameter_data_type[ = default ] } [ ,...n ] ] ) RETURNS TABLE [ WITH < function_option > [ [,] ...n ] ] [ AS ] RETURN [ ( ] select-stmt [ ) ] 多语句表值函数
CREATE FUNCTION [ owner_name.] function_name ([ { @parameter_name[AS] scalar_parameter_data_type[ = default ] } [ ,...n ] ] ) RETURNS @return_variable TABLE < table_type_definition > [ WITH < function_option > [ [,] ...n ] ] [ AS ] BEGIN function_body RETURN END < function_option > ::= { ENCRYPTION | SCHEMABINDING } < table_type_definition > ::= ( { column_definition | table_constraint } [ ,...n ] ) 参数 owner_name 拥有该用户定义函数的用户 ID 的名称。owner_name必须是现有的用户 ID。 function_name 用户定义函数的名称。函数名称必须符合标识符的规则，对其所有者来说，该名称在数据库中必须是唯一的。 @parameter_name 用户定义函数的参数。CREATE FUNCTION 语句中可以声明一个或多个参数。函数最多可以有 1,024 个参数。函数执行时每个已声明参数的值必须由用户指定，除非该参数的默认值已经定义。如果函数的参数有默认值，在调用该函数时必须指定"default"关键字才能获得默认值。这种行为不同于存储过程中有默认值的参数，在存储过程中省略参数也意味着使用默认值。 使用 @ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个函数的参数仅用于该函数本身；相同的参数名称可以用在其它函数中。参数只能代替常量；而不能用于代替表名、列名或其它数据库对象的名称。 scalar_parameter_data_type
数据库辅导：SQL自定义函数实现时间转换 数据库里面显示2个时间差 select datediff(minute ,'2008-11-20 19:20:00','2008-11-20 20:50:00') 这样子得出来的是 90 分钟 但我要显示成 1:30分该怎么做,? 解决: select convert(varchar(5),dateadd(ss, datediff(ss ,'2008-11-20 19:20:00','2008-11-20 20:50:00') ,108),108) 又问: 要是我的时间差大于24小时，那显示出来的就不正确了阿， 应该要显示小时是大于 24的一个数，而不是又回过去了阿。。 下面用自定义函数的方法解决 Create function [dbo].[f_second_Time] (@second int) returns varchar(16) as BEGIN /* 根据传入的秒数返回秒数所对应的时间,格式为天\时\分\秒 Create by cjs 2008-11-20 使用: select dbo.[f_second_Time](214580) 注意:可查询的秒数为35999999,即9999时59分59秒, 这与返回中cast(@Hours as varchar(4))有关,如果需要更大的精度 可以在返回时改成如cast(@Hours as varchar(8)) */ declare @Secs int declare @Mins int declare @Hours int declare @Days int set @Secs=@second%60 --秒 set @Mins=(@second-(@second%60))/60 set @Hours=(@Mins-(@Mins%60))/60 set @Mins=@Mins-@Hours*60 ---'分钟 --如果要转成日,用下面注释的部分,即把/*去掉 /* set @Days=(@Hours-(@Hours%24))/24 ---'日 set @Hours=@Hours-@Days*24 ---'小时 return cast(@Days as varchar(6)) +'日'+ cast(@Hours as varchar(4)) +'时'+ cast(@Mins as varchar(4)) +'分'+ cast(@Secs as varchar(4)) +'秒'