SQL函数和存储过程模板示例 (sql中的函数)

学习Sql数据库，函数和存储过程都是非常重要的，下面就将为您示例SQL函数和存储过程模板，供您参考，希望对您学习SQL函数和存储过程能有所启迪。

–标量值函数

— ================================================— Template generated from Template Explorer using:— Create Scalar Function (New Menu).SQL—— Use the Specify Values for Template Parameters— command (Ctrl-Shift-M) to fill in the parameter— values below.—— This block of comments will not be included in— the definition of the function.— ================================================SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO— =============================================— Author: — Create date: — Description: — =============================================CREATE FUNCTION (— Add the parameters for the function here<@Param1, sysname, @p1> )RETURNS ASBEGIN#p#— Declare the return variable hereDECLARE <@ResultVar, sysname, @Result>

— Add the T-SQL statements to compute the return value hereSELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>

— Return the result of the functionRETURN <@ResultVar, sysname, @Result>

ENDGO

–内联表值函数

— ================================================— Template generated from Template Explorer using:— Create Inline Function (New Menu).SQL—— Use the Specify Values for Template Parameters— command (Ctrl-Shift-M) to fill in the parameter— values below.—— This block of comments will not be included in— the definition of the function.— ================================================SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO— =============================================— Author: — Create date: — Description: — =============================================CREATE FUNCTION (— Add the parameters for the function here<@param1, sysname, @p1> ,<@param2, sysname, @p2> )RETURNS TABLE #p#ASRETURN(— Add the SELECT statement with parameter references hereSELECT 0)GO

–多语句表值函数

— ================================================— Template generated from Template Explorer using:— Create Multi-Statement Function (New Menu).SQL—— Use the Specify Values for Template Parameters— command (Ctrl-Shift-M) to fill in the parameter— values below.—— This block of comments will not be included in— the definition of the function.— ================================================SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO— =============================================— Author: — Create date: — Description: — =============================================CREATE FUNCTION (— Add the parameters for the function here<@param1, sysname, @p1> ,<@param2, sysname, @p2> )RETURNS #p#<@Table_Variable_Name, sysname, @Table_Var> TABLE(— Add the column definitions for the TABLE variable here , )ASBEGIN— Fill the table variable with the rows for your result set

RETURNENDGO

–多语句表值函数

DECLARE @MergeDate DatetimeDECLARE @MasterId IntDECLARE @DuplicateId Int

SELECT @MergeDate = GetDate()

DECLARE merge_cursor CURSOR FAST_FORWARD FOR SELECT MasterCustomerId, DuplicateCustomerId FROM DuplicateCustomers WHERE IsMerged = 0

OPEN merge_cursor

FETCH NEXT FROM merge_cursor INTO @MasterId, @DuplicateId

WHILE @@FETCH_STATUS = 0BEGINEXEC MergeDuplicateCustomers @MasterId, @DuplicateId

UPDATE DuplicateCustomersSETIsMerged = 1,MergeDate = @MergeDateWHEREMasterCustomerId = @MasterId ANDDuplicateCustomerId = @DuplicateId

FETCH NEXT FROM merge_cursor INTO @MasterId, @DuplicateIdEND

CLosE merge_cursorDEallOCATE merge_cursor

【编辑推荐】

SQL中表变量是否必须替代临时表

SQL中表变量的不足

详解SQL中循环结构的使用

SQL循环执行while控制

SQL循环语句的妙用

请问在SQL中用存储过程将查询结果写入表中,这个存储过程应该怎么写.

CREATE PROCEDURE [dbo].[SP_CREATENEW]ASBEGIN insert into 表3(字段一, 字段二, 字段三) select 表1.学号, 表1.姓名, 表2.成绩 from 表1 inner join 表2 on 表1.学号= 表2.学号END如果你要实现每半个小时执行一次的话，你可以开启sqlserver 代理，新建作业，在常规输入名称，在步骤里新建步骤，输入exec SP_CREATENEW，在计划里新建计划，每隔30分执行一次

sql多条件查询语句存储过程怎么写啊?

ALTER PROCEDURE [dbo].[BasicOption_Edit]@BoId int,@UploadFormat nvarchar(50),@UploadMaxSize int,@UploadMark int,@SiteName nvarchar(50),@SiteUrl nvarchar(50),@TemUrl nvarchar(50),@SeoWords nvarchar(100),@PageDesc nvarchar(100),@MessYes intASBEGIN TRANSACTIONUPDATE BasicOption SET UploadFormat=@UploadFormat,UploadMaxSize=@UploadMaxSize,UploadMark=@UploadMark,SiteName=@SiteName,SiteUrl =@SiteUrl , TemUrl=@TemUrl, SeoWords=@SeoWords, PageDesc=@PageDesc,MessYes=@MessYesWHERE BoId=@BoIdIF @@error <> 0 ROLLBACK TRANSACTIONCOMMIT TRANSACTION举个例子吧。

to_date(sysdate, 'yyyy-MM-dd') oracle 数据存储过程获取系统时间是什么类型的？