精华内容
下载资源
问答
  • 创建,执行,查看,修改,删除

    • 存储过程是一种数据库对象,是为了实现某个特定任务,将一组预编译的SQL语句以一个存储单元的形式存储在服务器上,供用户调用。存储过程在第一次执行时进行编译,然后将编译好的代码保存在高速缓存区中便于以后调用,这样可以提高代码的执行效率。
    • 存储过程是指封装了可重用代码的模块或例程。存储过程可以接受输入参数、向客户端返回表格或标量结果和消息、调用数据定义语言 (DDL) 和数据操作语言 (DML) 语句,然后返回输出参数 。

    符号表示

    【N】存储过程的名字
    【SQL】sql语句
    【@P】@不可少,P为参数名
    【T】数据类型
    【V】具体的值
    【@B】@不可少,B为变量名

    创建存储过程

    类型 sql语句 说明
    无参数 create procedure【N】《with encryption|with recompile》as 【SQL】 创建一个名为N的存储过程,with encryption表示加密,
    with recompile表示重新编译
    带输入参数 create procedure【N】【@P】【T】…《with encryption|with recompile》as 【SQL】 创建一个名为N的存储过程,带有类型为T的输入参数P
    带输出参数 create procedure【N】【@P】【T】output as【SQL】 创建一个名为N的存储过程,带有类型为T的输出参数P
    带默认值 create procedure【N】【@P】【T=V】(output)…《with encryption|with recompile》as 【SQL】 创建一个名为N的存储过程,带有类型为T的参数P,P的默认值是V

    procedure可用proc替换
    输入与输出参数可以混合使用

    执行存储过程

    类型 sql语句 说明
    无参数 execute【N】 执行名为N的存储过程
    带输入参数1 execute【N】【@P=V】 执行名为N的存储过程,并将V传入参数P
    带输入参数2 execute【N】【V1,V2】 执行名为N的存储过程,并按V1,V2顺序依次传入参数P1,P2
    带参数 declare【@B1】【T1】【@B2】【T2】
    set【@B2=V2】
    execute【N】【@B1】output,【@B2】
    print【@B1】
    声明两个变量B1,B2,类型依次为T1,T2
    给B2赋值为V2
    执行名为N的存储过程,按顺序将B1,B2依次传入参数P1,P2。
    最后打印输出B1

    execute可用exec替换

    查看存储过程

    sql语句 说明
    exec sp_helptext【N】 查看存储过程内容
    exec sp_depends【N】 查看存储过程处理对象的信息
    exec sp_help【N】 查看存储过程本身的信息

    修改存储过程

    sql语句 说明
    alter proc【N】《with encryption|with recompile》as【SQL】 修改名为N的存储过程

    删除存储过程

    sql语句 说明
    drop proc【N1,N2…】 删除名为N1,N2的存储过程

    例子

    1.基于test1数据库,创建存储过程实现:指定某课程名,统计该课程的平均分、最高分和最低分,将结果返回给用户

    course表(课程表:课程号,课程名,先行课号,学分)
    在这里插入图片描述
    sc表(选课表:学号,课程号,成绩)
    在这里插入图片描述

    创建存储过程DISPLAY_GRADE

    create proc DISPLAY_GRADE 
    @course_name nchar(20),@avg float output,@max float output,@min float output as
    select @avg=AVG(sc.sgrade) from sc,course where course.cname=@course_name and sc.cno=course.cno
    select @max=MAX(sc.sgrade) from sc,course where course.cname=@course_name and sc.cno=course.cno
    select @min=MIN(sc.sgrade) from sc,course where course.cname=@course_name and sc.cno=course.cno
    

    在这里插入图片描述

    执行1

    declare @avg float,@max float,@min float
    exec DISPLAY_GRADE '数据库',@avg output,@max output,@min output
    print @avg
    print @max
    print @min
    

    或执行2

    declare @course_name nchar(20),@avg float,@max float,@min float
    set @course_name='数据库'
    exec DISPLAY_GRADE @course_name,@avg output,@max output,@min output
    print @avg
    print @max
    print @min
    

    在这里插入图片描述


    2.查看存储过程DISPLAY_GRADE的内容

    exec sp_helptext DISPLAY_GRADE
    

    在这里插入图片描述
    如果之前设置是加密(with encryption),则无法查看

    在这里插入图片描述
    感谢阅读!

    展开全文
  • 2.掌握用户自定义存储过程的创建、修改、删除和执行。 3.掌握触发器的创建、删除、修改及其使用方法。 4.掌握触发器的功能。   一、利用shiyan14.sql完成下列内容。 shiyan14.sql脚本: /****** Object: ...

    实验目标:

    1.通过实验掌握存储过程的概念、功能。

    2.掌握用户自定义存储过程的创建、修改、删除和执行。

    3.掌握触发器的创建、删除、修改及其使用方法。

    4.掌握触发器的功能。

     

    一、利用shiyan14.sql完成下列内容。

    shiyan14.sql脚本:

    /****** Object:  Table [dbo].[c]    Script Date: 2017/5/16 10:51:01 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[c](
    [cno] [char](2) NOT NULL,
    [cn] [char](10) NOT NULL,
    [ct] [tinyint] NULL,
    PRIMARY KEY CLUSTERED 
    (
    [cno] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF
    GO


    /****** Object:  Table [dbo].[s]    Script Date: 2017/5/16 10:51:02 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[s](
    [sno] [char](2) NOT NULL,
    [sn] [char](8) NOT NULL,
    [sex] [char](2) NULL,
    [age] [tinyint] NULL,
    [dept] [char](10) NULL,
    PRIMARY KEY CLUSTERED 
    (
    [sno] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[sc]    Script Date: 2017/5/16 10:51:02 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[sc](
    [sno] [char](2) NOT NULL,
    [cno] [char](2) NOT NULL,
    [score] [tinyint] NULL,
    PRIMARY KEY CLUSTERED 
    (
    [sno] ASC,
    [cno] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]


    GO
    SET ANSI_PADDING OFF
    GO

    /****** Object:  Table [dbo].[t]    Script Date: 2017/5/16 10:51:02 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[t](
    [tno] [char](2) NOT NULL,
    [tn] [char](8) NOT NULL,
    [sex] [char](2) NULL,
    [age] [tinyint] NULL,
    [prof] [char](10) NULL,
    [sal] [smallint] NULL,
    [comm] [smallint] NULL,
    [dept] [char](10) NULL,
    PRIMARY KEY CLUSTERED 
    (
    [tno] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]


    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[tc]    Script Date: 2017/5/16 10:51:02 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tc](
    [tno] [char](2) NOT NULL,
    [cno] [char](2) NOT NULL,
    [evaluation] [char](20) NULL,
    PRIMARY KEY CLUSTERED 
    (
    [tno] ASC,
    [cno] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF
    GO

    INSERT [dbo].[c] ([cno], [cn], [ct]) VALUES (N'c1', N'程序设计  ', 60)
    INSERT [dbo].[c] ([cno], [cn], [ct]) VALUES (N'c2', N'微机原理  ', 80)
    INSERT [dbo].[c] ([cno], [cn], [ct]) VALUES (N'c3', N'数字逻辑  ', 60)
    INSERT [dbo].[c] ([cno], [cn], [ct]) VALUES (N'c4', N'数据结构  ', 80)
    INSERT [dbo].[c] ([cno], [cn], [ct]) VALUES (N'c5', N'数据库    ', 60)
    INSERT [dbo].[c] ([cno], [cn], [ct]) VALUES (N'c6', N'编译原理  ', 60)
    INSERT [dbo].[c] ([cno], [cn], [ct]) VALUES (N'c7', N'操作系统  ', 60)
    INSERT [dbo].[s] ([sno], [sn], [sex], [age], [dept]) VALUES (N's0', N'王青山  ', N'男', 19, N'计算机    ')
    INSERT [dbo].[s] ([sno], [sn], [sex], [age], [dept]) VALUES (N's2', N'钱尔    ', N'男', 18, N'信息      ')
    INSERT [dbo].[s] ([sno], [sn], [sex], [age], [dept]) VALUES (N's3', N'张晓明  ', N'男', 18, N'信息      ')
    INSERT [dbo].[s] ([sno], [sn], [sex], [age], [dept]) VALUES (N's4', N'李思    ', N'男', 21, N'自动化    ')
    INSERT [dbo].[s] ([sno], [sn], [sex], [age], [dept]) VALUES (N's5', N'周武    ', N'男', 19, N'计算机    ')
    INSERT [dbo].[s] ([sno], [sn], [sex], [age], [dept]) VALUES (N's6', N'吴丽    ', N'女', 20, N'自动化    ')
    INSERT [dbo].[s] ([sno], [sn], [sex], [age], [dept]) VALUES (N's7', N'范思萌  ', N'女', 18, N'信息      ')
    INSERT [dbo].[s] ([sno], [sn], [sex], [age], [dept]) VALUES (N's8', N'孙珊    ', N'女', 20, N'信息      ')
    INSERT [dbo].[s] ([sno], [sn], [sex], [age], [dept]) VALUES (N's9', N'张海涛  ', N'男', 19, N'信息      ')
    INSERT [dbo].[sc] ([sno], [cno], [score]) VALUES (N's0', N'c1', 90)
    INSERT [dbo].[sc] ([sno], [cno], [score]) VALUES (N's0', N'c2', 85)
    INSERT [dbo].[sc] ([sno], [cno], [score]) VALUES (N's2', N'c1', NULL)
    INSERT [dbo].[sc] ([sno], [cno], [score]) VALUES (N's2', N'c5', 67)
    INSERT [dbo].[sc] ([sno], [cno], [score]) VALUES (N's2', N'c6', 80)
    INSERT [dbo].[sc] ([sno], [cno], [score]) VALUES (N's3', N'c1', 75)
    INSERT [dbo].[sc] ([sno], [cno], [score]) VALUES (N's3', N'c2', 70)
    INSERT [dbo].[sc] ([sno], [cno], [score]) VALUES (N's3', N'c4', 85)
    INSERT [dbo].[sc] ([sno], [cno], [score]) VALUES (N's4', N'c1', 93)
    INSERT [dbo].[sc] ([sno], [cno], [score]) VALUES (N's4', N'c2', 85)
    INSERT [dbo].[sc] ([sno], [cno], [score]) VALUES (N's4', N'c3', 83)
    INSERT [dbo].[sc] ([sno], [cno], [score]) VALUES (N's5', N'c2', 89)
    INSERT [dbo].[t] ([tno], [tn], [sex], [age], [prof], [sal], [comm], [dept]) VALUES (N't1', N'李力    ', N'男', 47, N'教授      ', 1800, 3000, N'计算机    ')
    INSERT [dbo].[t] ([tno], [tn], [sex], [age], [prof], [sal], [comm], [dept]) VALUES (N't2', N'王平    ', N'女', 28, N'讲师      ', 850, 1200, N'信息      ')
    INSERT [dbo].[t] ([tno], [tn], [sex], [age], [prof], [sal], [comm], [dept]) VALUES (N't3', N'刘伟    ', N'男', 30, N'讲师      ', 900, 1200, N'计算机    ')
    INSERT [dbo].[t] ([tno], [tn], [sex], [age], [prof], [sal], [comm], [dept]) VALUES (N't4', N'张雪    ', N'女', 51, N'教授      ', 1900, 3000, N'自动化    ')
    INSERT [dbo].[t] ([tno], [tn], [sex], [age], [prof], [sal], [comm], [dept]) VALUES (N't5', N'张兰    ', N'女', 39, N'副教授    ', 1300, 2000, N'信息      ')
    INSERT [dbo].[tc] ([tno], [cno], [evaluation]) VALUES (N't1', N'c1', NULL)
    INSERT [dbo].[tc] ([tno], [cno], [evaluation]) VALUES (N't1', N'c4', NULL)
    INSERT [dbo].[tc] ([tno], [cno], [evaluation]) VALUES (N't2', N'c5', NULL)
    INSERT [dbo].[tc] ([tno], [cno], [evaluation]) VALUES (N't3', N'c1', NULL)
    INSERT [dbo].[tc] ([tno], [cno], [evaluation]) VALUES (N't3', N'c5', NULL)
    INSERT [dbo].[tc] ([tno], [cno], [evaluation]) VALUES (N't4', N'c2', NULL)
    INSERT [dbo].[tc] ([tno], [cno], [evaluation]) VALUES (N't4', N'c3', NULL)
    INSERT [dbo].[tc] ([tno], [cno], [evaluation]) VALUES (N't5', N'c5', NULL)
    INSERT [dbo].[tc] ([tno], [cno], [evaluation]) VALUES (N't5', N'c7', NULL)

    ALTER TABLE [dbo].[sc]  WITH CHECK ADD FOREIGN KEY([cno])
    REFERENCES [dbo].[c] ([cno])
    GO
    ALTER TABLE [dbo].[sc]  WITH CHECK ADD FOREIGN KEY([sno])
    REFERENCES [dbo].[s] ([sno])
    GO

    ALTER TABLE [dbo].[tc]  WITH CHECK ADD FOREIGN KEY([tno])
    REFERENCES [dbo].[t] ([tno])
    GO
    ALTER TABLE [dbo].[tc]  WITH CHECK ADD FOREIGN KEY([cno])
    REFERENCES [dbo].[c] ([cno])
    GO
     

    1.创建并使用存储过程,注意创建存储过程前一定要先用IF EXISTS判断一下是否已创建同名的存储过程,若有先删除在创建。

    ① 创建一存储过程利用学生姓名查询该生选修的课程名、成绩、以及任课教师姓名(涉及的表可能有s、sc、t、c、tc),并调用存储过程查询王青山的选修的课程名、成绩以及任课教师姓名,结果如图所示。

    ② 创建一存储过程利用系名查询某系的学生的最大年龄和最小年龄。调用存储过程查询信息系的学生最大年龄和最小年龄。(涉及的表可能有s)

    ③ 创建一存储过程利用学生姓名和课程名检索该生该课程的成绩,涉及的表可能有s,sc,c。调用该存储过程查询王青山程序设计的成绩,结果如下图所示。

     

    创建存储过程:

    if exists(select name from sysobjects where name='pro_qscore' and type='p')

    drop proc pro_qscore

    go

    CREATE proc [dbo].[pro_qscore]

       @sname_in char(10),

       @cname_in char(10),

       @grade_out tinyint output

    as

       select @grade_out=score

       from s,sc,c

       where s.sn=@sname_in

       and  s.sno=sc.sno

       and sc.cno=c.cno

       and c.cn=@cname_in

    Go

    调用存储过程:

    declare @sname_in char(10),@cname_in char(10),@grade_out tinyint

    set @sname_in='王青山'

    set @cname_in='程序设计'

    exec pro_qscore @sname_in,@cname_in,@grade_out output

    select @sname_in 姓名,@cname_in 课程,@grade_out 成绩 

     

    ④ 查询某系的教师人数、平均年龄和学生人数。

     

     

    创建存储过程:

    if exists(select name from sysobjects where name='pro_dept_count' and type='p')

    drop proc pro_dept_count

    go

    create proc pro_dept_count

       @dept_in char(10)

    as

       select a.dept 系别,平均年龄,学生人数

       from (select dept,count(tno) as 教师人数,avg(age) as 平均年龄 from t group by dept) a,(select dept,count(sno) 学生人数 from s group by dept) b

       where a.dept=b.dept and a.dept=@dept_in

    go

    调用存储过程:

    declare @dept_in char(10)

    set @dept_in='计算机'

    exec pro_dept_count @dept_in

     

     

     

    ⑤ 利用教师姓名和课程名检索该教师该课的课程名、课时数、选课人数、平均成绩、最高成绩、并查询教师“张雪”的“微机原理”课程的情况记录及教师“张朋”的“数据库”课程,结果如图所示。

     

     

     

    创建存储过程:

    if exists(select name from sysobjects where name='pro_tname_cname' and type='p')

    drop proc pro_tname_cname

    go

    create proc pro_dept_count

       @tname_in char(10),

       @cname_in char(10),

       @student_sum int output,

       @grade_avg int output,

       @grade_max int output

    as

       select @student_sum=count(sno),@grade_avg=avg(Score),@grade_max=max(score)

       from t,c,sc,tc

       where sc.cno=c.cno and c.cno=tc.cno

       and tc.tno=t.tno and t.tn=@tname_in

       and c.cn=@cname_in

       group by tn,cn

    Go

    调用存储过程:

    declare @tname_in char(10), @cname_in char(10),

       @student_sum int,@grade_avg int,@grade_max int

    set @tname_in='张雪'

    set @cname_in='微机原理'

    exec pro_dept_count @tname_in,@cname_in,@student_sum output,@grade_avg output,@grade_max output

    if (@student_sum is null)

    print'没有学生选微机原理这门课程,故总课时数,选课人数,平均成绩等统统为0'

    else

    select @tname_in 教师姓名,@cname_in 学生姓名,@student_sum 选课总人数,@grade_avg 平均成绩,@grade_max 总成绩

     

     

    2.创建并使用触发器,注意创建触发器前一定要先用IF EXISTS判断一下是否已创建同名的触发器,若有先删除在创建。

     

    ① 为表sc创建一触发器:当插入或修改一个记录时,确保此记录的成绩在0~100分之间。(思路:新的数据行就会被插入inserted表,通过检查该表判断插入的行是否在0~100之间,如果在正常插入,否则不做插入,回滚至插入的状态。)

    l 插入数据行('s5','c1','101'),是否弹出如图所示的错误。请问该行数据是否插入到sc表中

     

    l 将s5的c1成绩更新至101,能否更新成功?

     

     

    if exists(select name from sysobjects where name='score_sc_tri' and type='tr')

    drop trigger score_sc_tri

    go

    create trigger score_sc_tri

    on sc for insert,update

    as

      declare @score_in tinyint

      select @score_in=score from inserted

      if @score_in<0 or @score_in>100

      begin

         print '成绩超出0-100!请重新输入.'

     rollback transaction

      end

      go

    insert into sc values ('s5','c1','101') /*触发器激活,插入失败*/

    Update sc set score=101 where sno='s5' and cno='c1'

    /*触发器激活,更新失败*/

     

    ② 为数据库表T创建一触发器:当职称从“讲师”晋升为“副教授”时,津贴自动增加500元,从“副教授”晋升为“教授”时,岗位津贴自动增加900元。

     

    更新前:

     

    更新后:

     

     

    if exists (select name from sysobjects where name='ut' and type='tr')

    drop trigger ut

    go

    create trigger ut

    on t for update

    as

    declare @prof_old char(10)

    declare @prof_new char(10)

    declare @tno char(2)

    select @prof_old=prof from deleted

    select @prof_new=prof from inserted

    select @tno=tno from deleted

    if @prof_old='讲师' and @prof_new='副教授'

    begin

       update t set comm=comm+500 where tno=@tno

    end

    if @prof_old='副教授' and @prof_new='教授'

    begin

       update t set comm=comm+900 where tno=@tno

    end

    go

    update t set prof='副教授' where tn='刘伟'

     

     

     

    展开全文
  • 用户自定义存储过程的创建

    千次阅读 2010-11-03 14:34:00
    T-SQL语法 CREATE PROC[EDURE] 存储过程名 [{@参数 数据类型} [= 默认值] [OUTPUT]], ......, {@参数 数据类型} [= 默认值] [OUTPUT] ] AS SQL 语句   不带参数的存储过程...

    T-SQL语法

     

    不带参数的存储过程

    查看本次考试的平均分以及未通过考试的学员名单

     

    执行结果

     

     

    带输入参数的存储过程

    输入参数可以在调用时向存储过程传递参数,此类参数可以用来在存储过程中传入值。

    实例:由于考试难度不同,及格线也可能随时变化。我们添加两个输入参数,分别表示及格分数和机试及格分数

     

    执行结果

     

     

    带输出参数的存储过程

    输出参数:输出参数后面有OUTPUT标记,执行存储过程后,将把返回值存放在输出参数中。

    实例:希望返回未通过考试的学员人数。

     

    执行结果:

     

    处理错误信息

    RAISERROR返回用户定义的错误时,可以指定严重级别,设置系统变量记录所发生的错误。

    RAISERROR语法如下:

    1.msg_id:在sysmessages系统表中指定的用户定义错误信息

    2.msg_str:用户定义的特定信息,最长255个字符

    3.severity:与特定信息相关联,表示用户定义的严重级别。用户可以使用级别的0~18级。19~25级是为sysadmin固定角色的成员预留的,并且需要指定WITH LOG选项。20~25被认为是致命的错误。

    4.state:表示错误的状态,是1~127的值。

    5.option:指示是否将错误记录到服务器错误日志中。

    实例:当用户传入的及格线不再0~100之间时,将弹出错误警告,终止存储过程的执行。 

     

    执行结果:

     

    展开全文
  • 1.创建存储过程: USE SIPC GO CREATE PROC ASelectDepartment AS select * from B_Department 2.删除存储过程: DROP PROC|PROCEDURE 过程存储过程的参数:从存储过程外部向存储过程传递参数,...
    1.创建存储过程:
    		USE SIPC
    		GO
    		CREATE PROC  ASelectDepartment
    		AS
    		select * from B_Department
    
    2.删除存储过程:
    		DROP PROC|PROCEDURE  过程名
    
    存储过程的参数:从存储过程外部向存储过程传递参数,可以采用传值或者传引用;而在内部用相同的方法来声明
    参数声明:名称   数据类型  默认值   方向
    				@参数名 [AS] 数据类型 [=默认值|NULL]  [varying] [OUTPUT|OUT]
    				在声明cursor类型时,必须同时使用[varying] [OUTPUT|OUT]
    		变量总是被初始化成NULL值,而参数则不然,如果不为参数指派默认值,那么就等于必须提供参数,在调用存储过程时就必须为其提供一个初始值
    		注意:被作为输出结果变量不能同存储过程中的内部变量同名
    				  如果对存储过程的调用是批中的第一件事,那么可以省略,但还是强烈建议不这么做
    3.修改存储过程为有参数的:
    		出国存储过程A调用存储过程B,然后删除重建存储过程B,此时就看不到这两者的依懒性了;如果仅仅是修改存储过程B,其依赖性任然存在
    		ALTER PROC [dbo].[ASelectDepartment]
    			@Dept_Name nvarchar(100)=NULL
    		AS 
    		IF @Dept_Name is NULL
    			select * from B_Department
    		ELSE
    			select * from B_Department b
    			where Dept_Name=@Dept_Name
    4.SQL Server中执行带参数的存储过程的方法是:
    	EXEC 存储过程名字 '参数1','参数2',数值参数
    	EXEC 是一个关键字。
    	字符串参数使用单引号括起来,数值参数不需要使用单引号
    	不带参数的存储过程执行直接加名字就可以
    5.RETURN 
    		存储过程完成时,SQL SERVER会默认返回一个0;
    		要从存储过程向调用它的代码传递返回值,可以简单地使用RETURN:     RETURN [<整数返回值>]        注意返回值一定是整数
    		想要取到存储过程运行后的返回值,只需要在EXEC语句执行时,将之赋值到一个变量中,如下:
    		DECLARE @ReturnVal INT    --声明返回值
    		EXEC @ReturnVal=ARETURN	
    		SELECT @ReturnVal			
    		注:请确保你总是显示的定义了返回值,这样你就可以根据需要返回相应的值,而不是某些偶然性的东西
    6.错误处理
    		SQL SERVER中发生4中常见类型的错误:
    		错误1:在运行时创建错误,同时终止错误发生之后的代码
    		错误2:直接产生错误提示,这时会返回一个非0值,但并不生成任何错误
    		错误3:在运行时创建错误,但是在SQL SERVER中继续执行下去,这样就可以捕获该错误,并选择自己的应对方法
    		错误4:本质上符合逻辑的错误,但是SQL SERVER会忽略它
    7.@@ERROR  每当执行了一条新语句时,它的值都将被重设
    	大小写敏感与否取决于服务器的配置方式
    	@符号的数量决定了变量的独立性以及同其他变量的区别
    8.手工生成错误:使用RAISERROR命令
    	RAISERROR (<消息ID|消息字符串>,<严重级别>,<状态> [,<参数>[,<...n>]]) [WITH option[,...n]]
    	e1.消息ID在系统master库中,的视图sysMessages中
    		消息字符串的默认消息ID为50000,可以通过WITH  SETERROR来覆盖它
    	e2.严重级别,20或者更高的严重级别错误将会终止用户连接
    	e3.状态是一种特殊值,它是某些用于识别代码中的多个位置上确实可能发生同样错误的东西
    	e4.错误参数   
    			所有的占位符都以%号打头
    			例如:
    			RAISERROR('这是一个%s,is %+010d',1,1,'String',33333)
    			占位符类型标识:D   有符号整数
    									  O   无符号八进制数
    									  P    指针
    									  S    字符串
    									  U	无符号整数
    									  X或x    无符号十六进制数
    			同时还有一些选项可以作为这些占位符的前缀,以表示额外的标识或宽度信息
    						- (破折号或减号)		左对齐,只在使用固定宽度时起作用
    						+ (加好)						如果参数是有符号的数值类型,则表示正负类型
    						0									告诉SQL SERVER数值左方补0,直到达到指定宽度
    						# (磅号)						只用于8进制和16进制,依赖于是8进制还是16进制,告诉SQL SERVER使用合适的前缀(0或0x)
    						'   '								对于正数在左边用空格补齐
    			宽度:上例中的10就是宽度,必须是整数值,也可以指定*,这时SQL SERVER会根据你所设置的精度,自动确定其宽度
    			精度:确定最大输出数字的个数
    			长/度:当参数类型是整数,八进制,十六进制,可以通过h(短)或l(长)来设置
    			上面例子的结果:
    			这是一个String,is +000033333
    			消息 50000,级别 1,状态 1
    9.WITH <option>
    	当产生错误时,可以匹配三个选项;
    	WITH LOG:  告诉SQL SERVER将错误记录到SQL SERVER错误日志和Windows的应用程序日志中,当严重级别为19或者更高时,需要使用该选项	
    	WITH SETERROR:  默认情况下,RAISERROR命令不会用产生的错误值来对@ERROR赋值,但是SETERROR对它进行了重载,并将@@ERROR的值设置为等同于错误的ID
    	WITH NOWALT: 立刻通知错误的客户端
    10.添加自己定义地错误消息
    	利用系统的存储过程sp_addmessage,该存储过程在master库下,语法如下:
    	sp_addmessage [@msgnum=]		<消息ID>,
    							 [@severity=]		<严重级别>,
    							 [@msgtext=]		<'消息字符串'>
    							 [,[@lang=] <'语言'>]
    							 [,[@with_log=]  [TRUE|FALSE]]
    							 [,[@replace=]  'replace']
    	参数介绍:
    	@lang   		如果从sys.syslanguage中选择语言列表,这等同于其别名
    	@with_log    如果为TRUE ,当产生错误时,消息会被记录到SQL SERVER错误日志,以及Windows的应用程序日志当中去
    	@replace    如果是正在编辑一个现有的消息,而不是创建新的消息,则必须将@replace 参数设置为 'replace'    如果没有进行设置,若消息已经存在,则会得到一个错误
    	使用方法:
    	sp_addmessage
    			@msgnum=60000,
    			@severity=10,
    			@msgtext='It is not a valid other date.order date must be within 7 days of current date.',
    			@lang='us_english',
    			@with_log=false,
    			@replace='replace'
    	删除自定义消息: sp_dropmessage <msgnum>,<lang>
    			sp_dropmessage 60000,'us_english'
    11.使用存储过程的好处:
    	增强安全性;
    	提高性能;  当指定了WITH RECOMPILE,则跳过优化的编译     EXEC   存储过程名字   WITH RECOMPILE    仅仅跳过这一次优化执行,如果想永久跳过,可以写到存储过程中在AS之前
    	允许创建需要可调用过程行为的过程      简而言之:存储过程调用存储过程,2005中最多可以进行32层嵌套
    
    12.用户定义函数UDF
    	很像存储过程,是一系列T-SQL语句的有序集合,它可以被预优化和编译,并且可以在工作中被作为一个单一的单元来调用;
    	它和存储过程的不同之处在于结果是如何被返回的;
    	
    	UDF分为:
    	x1.返回标量值的UDF
    		除了BLOB,CURSOR,timestamp之外,可以返回任何有效的SQL Server数据类型(包括用户定义地数据类型);
    		可以在查询中作为内联执行函数;
    	x2.返回表的UDF
    13.返回标量值的UDF
    	CONVERT() 函数是把日期转换为新数据类型的通用函数。
    	CONVERT() 函数可以用不同的格式显示日期/时间数据。
    	
    	CONVERT(data_type(length),data_to_be_converted,style)
    	data_type(length) 规定目标数据类型(带有可选的长度)。data_to_be_converted 含有需要转换的值。style 规定日期/时间的输出格式。
    		Style ID	Style 格式
    		100 或者 0	mon dd yyyy hh:miAM (或者 PM)
    		101	mm/dd/yy
    		102	yy.mm.dd
    		103	dd/mm/yy
    		104	dd.mm.yy
    		105	dd-mm-yy
    		106	dd mon yy
    		107	Mon dd, yy
    		108	hh:mm:ss
    		109 或者 9	mon dd yyyy hh:mi:ss:mmmAM(或者 PM)
    		110	mm-dd-yy
    		111	yy/mm/dd
    		112	yymmdd
    		113 或者 13	dd mon yyyy hh:mm:ss:mmm(24h)
    		114	hh:mi:ss:mmm(24h)
    		120 或者 20	yyyy-mm-dd hh:mi:ss(24h)
    		121 或者 21	yyyy-mm-dd hh:mi:ss.mmm(24h)
    		126	yyyy-mm-ddThh:mm:ss.mmm(没有空格)
    		130	dd mon yyyy hh:mi:ss:mmmAM
    		131	dd/mm/yy hh:mi:ss:mmmAM
    	
    	如果用下面的方式查询,如果有一大堆这样的时间需要翻来覆去的转换,无疑太可怕了:
    	SELECT * FROM Orders WHERE CONVERT(varchar(12),OrdersDate,101)=CONVERT(varchar(12),GETDATE(),101)
    	所以编写一个简单的UDF(标量值函数):
    	CREATE FUNCTION dbo.DayOnly(@Date datetime)
    	RETURNS varchar(12)
    	AS
    	BEGIN
    		RETURN CONVERT(varchar(12),@Date,101)
    	END
    	使用:
    	SELECT * FROM Orders WHERE dbo.DayOnly(OrdersDate)=dbo.DayOnly(GETDATE())
    	你也可以用函数封装子查询,让他感觉像单值一样
    14.返回表的UDF
    	USE [SIPC]
    	GO
    	/****** Object:  UserDefinedFunction [dbo].[fnContactList]    Script Date: 09/21/2018 09:38:37 ******/
    	SET ANSI_NULLS ON
    	GO
    	SET QUOTED_IDENTIFIER ON
    	GO
    	-- =============================================
    	-- Author:		<Author,,Name>
    	-- Create date: <Create Date,,>
    	-- Description:	<Description,,>
    	-- =============================================
    	CREATE FUNCTION [dbo].[fnContactList]()
    	RETURNS 
    		TABLE
    	AS	
    		RETURN (SELECT DB_Name,Dept_Code+','+Dept_Name AS Name FROM B_Department) 
    	GO
    	使用:
    	SELECT * FROM fnContactList()
    15.检查函数是否是确定性的:可以使用函数
    	SELECT OBJECTPROPERTY(OBJECT_ID('DayOnly'),'IsDeterministic')

     

    展开全文
  • 二者本质上没有什么区别。当存储过程和函数被执行的时候,SQL Manager会到procedure cache中去取相应的查询语句,如果在procedure cache里没有相应的查询语句,SQL Manager就会对存储过程和函数进行编译。
  • 存储过程是一组预编译的SQL语句,它可以包含数据操纵语句、变量、逻辑控制语句等。 存储过程允许带参数: 输入参数:可以在调用时向存储过程传递参数,此类参数可用来向存储过程中传入值(可以有默认值) 输出参数...
  • SQL Server用户自定义函数和存储过程有类似的功能,都可以创建捆绑SQL语句,存储在server中供以后使用。这样能够极大地提高工作效率,通过以下的各种做法可以减少编程所需的时间: 重复使用编程代码,减少编程...
  • 自定义git checkout命令可自动管理每个分支的存储,因此您可以轻松地在分支之间切换,而不必每次都进行WIP提交或手动存储和弹出更改。 如果您经常发现自己在多个分支上工作并在它们之间切换,那么您可能熟悉以下...
  • 数据库 用户自定义函数

    千次阅读 2020-02-04 19:03:19
    文章目录用户自定义函数用户自定义函数和存储过程比较创建用户自定义函数管理用户自定义函数 用户自定义函数 用户自定义函数和存储过程比较 比较项 存储过程 用户自定义函数 参数 允许有多个输入输出参数 ...
  • MySQL自定义函数与存储过程

    千次阅读 2017-04-11 11:22:58
    自定义函数 自定义函数 (user-defined function UDF)是一种对mysql扩展的途径,其用法和内置函数相同。 自定义函数的两个必要条件:1、参数(不是必有的,例如select version())2返回值 (必有的)。函数可以...
  • MySql存储过程自定义函数

    千次阅读 2018-05-12 20:15:23
    存储过程(Stored Procedure)是一组为了完成特定功能的 SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。...
  • MYSQL中创建存储过程和函数分别使用CREATE PROCEDURE和CREATE FUNCTION 使用CALL语句来调用存储过程存储过程也可以调用其他存储过程 函数可以从语句外调用,能返回标量值 创建存储过程 语法 CREATE ...
  • 这一篇《我的MYSQL学习心得(十)》将会讲解MYSQL的存储过程和函数 MYSQL中创建存储过程和函数分别使用CREATE PROCEDURE和CREATE FUNCTION 使用CALL语句来调用存储过程存储过程也可以调用其他存储过程 函数可以...
  • SQL之用户自定义函数

    2013-07-28 09:56:25
    用户自定义函数(User Defined Functions)是SQL Server 的数据库对象,它不能用于执行一系列改变数据库状态的操作,但它可以像系统函数一样在查询或存储过程等的程序段中使用,也可以像存储过程一样通过EXECUTE ...
  • 当我们在控制台的时候,输入boot可以启动Linux内核,那么我们以boot为例子来解析一下uboot命令的执行过程,为下一步分析uboot怎样启动Linux来做准备。一、我们搜索boot命令grep -wnR "boot" common 得到:common/...
  • 在该路径下创建文件夹并把希望通过CMD命令快捷打开的软件的快捷方式放到自己创建的”CMD快捷命令”(该文件夹自己命名,也可以不用直接存储在该目录下,建议使用,方便查看且工整) (C:\ProgramData\Microsoft\Windows\...
  • 一、存储过程1.1、定义存储过程是用户定义的一系列sql语句的集合,完成一系列的数据操作,从而节省网络传输所需要的时间。1.2、优点增强SQL语句的功能和灵活性实现较快的执行速度减少网络流量1.3、语法①、创建...
  • Splunk自定义命令开发

    2021-02-05 11:26:29
    自定义命令如何运作的? 使用什么语言开发自定义命令? 内置Python所在位置 如何在后台使用内置Python 内置Python版本 内置Python使用的包和库所在位置 是否有现成的SDK等? SDK下载 SDK存放位置 开发的...
  • 本博文源于mysql基础,主要对自定义函数和存储过程进行学习与练习。涉及自定义函数的创建使用与存储过程的创建使用。
  • SQL用户自定义函数

    2013-12-17 16:05:06
    用户自定义函数(User Defined Functions)是SQL Server 的数据库对象,它不能用于执行一系列改变数据库状态的操作,但它可以像系统函数一样在查询或存储过程等的程序段中使用,也可以像存储过程一样通过EXECUTE ...
  • 用户自定义函数不能用于执行一系列改变数据库状态的操作,但它可以像系统函数一样在查询或存储过程等的程序段中使用,也可以像存储过程一样通过EXECUTE 命令来执行。 在SQLServer 中根据函数返回值形式的不同将用户...
  • 在做项目的计费功能的时候, 首先需要在数据库里定义 一条费率规则,规则下包括若干个条件 在做到了查费率这一步的时候犯了难,要把商品的 若干个属性 都写到sql里查?...存储过程是由过程化SQL语句书写的
  • mssql用户自定义函数

    2012-07-25 14:57:24
    用户自定义函数(User Defined Functions)是SQL Server的数据库对象,它不能用于执行一系列改变数据库状态的操作,但它可以像系统函数一样在查询或存储过程等的程序段中使用,也可以像存储过程一样通过EXECUTE命令...
  • 自定义存储过程 语法 CREATE PROCEDURE sp_name ([ proc_parameter ]) [ characteristics ..] routine_body proc_parameter 指定存储过程的参数列表,列表形式如下: [ IN | OUT | INOUT...
  • 1 MySQL存储过程和函数 过程和函数,它们被编译后保存在数据库中,称为持久性存储模块(Persistent Stored Module,PSM),可以反复调用,运行速度快。...这里指自定义函数,因为是用户自己使用过程化 SQL...
  • 1 MySQL存储过程和函数过程和函数,它们被编译后保存在数据库中,称为持久性存储模块(Persistent Stored Module,PSM),可以反复调用...1.2 函数这里指自定义函数,因为是用户自己使用过程化 SQL 设计定义的。函数和

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 176,623
精华内容 70,649
关键字:

修改用户自定义存储过程的命令是