SQL Server 2005基础知识详细整理(2)-木庄网络博客

当前第2页返回上一页

53.定义存储过程的语法：
             Create proc[edure] 存储过程名
              @参数1 数据类型=默认值 output，
               ...................
              @参数n 数据类型=默认值 output
              As
              SQL语句
              Go （注：参数可选，参数分输入参数、输出参数，输入参数允许有默认值）

54.创建不带参数的存储过程：
例：

代码如下:

           Creat procedure pro_stu
           As
           Declare @myAvg float
           Select @myAvg=AVG（score）
           From score
           Print '平均分：'+convert（varchar（5），@myAvg）
           If（@myAvg>70）
              Print '本班考试成绩：优秀'
            Else
               Print '本班考试成绩：较差'
            Print '-----------------------------------------------------------'
            Print '参加本次考试没有通过的学员：'
               Select name，scode，score
                   From students inner Join score on
                   Students.scode=score.studentid     where score<60
     GO

55.调用存储过程：   语法：   EXEC 过程名[参数]    例：EXEC proc_stu

56.输入参数的默认值:
例：

代码如下:

         Create procedure proc_stuMn
            @scorePass int=60        ---及格线默认为60分
         As
            Print '及格线：' +convert(varchar(5),@scorePass)
            Print '参加本次考试没有通过的学员：'
          Select name，scode，score
                From students
                Inner Join score
                On students.scode=score.studentid
                 Where score<@scorePass
         Go

57.调用带参数默认值的存储过程：
             EXEC proc_stu    -------采用默认值
             EXEC proc_stu 55    ------不采用默认值
             EXEC proc_stu @scorePass=55

58.带输出参数的存储过程：

代码如下:

        Create procedure proc_stuM
        @notpassSum int output，
        @scorePass int=60
        As
           Print'及格线：'+convert（varchar（5），@scorePass）
           Print '参加本次考试没有通过的学员：'
        Select name，scode，score      from students
              Inner Join score On   students.scode=score.studentid
                   Where score<@scorePass
         Select @notpassSum=count（studentid）
               From score where score<@scorePass
         GO

59.调用带输出参数的存储过程：（注：调用时必须带output关键字，返回结果将存放在变量@sum 中）

代码如下:

    Declare @sum int
           EXEC proc_stuM @sum output，60
           Print '--------------------------------------------------'
           If @sum>=3
                 Print '未通过人数：'+convert（varchar（5），@sum ）+'人，超过60%，及格分数线还应下调'
           Else
                  Print '未通过人数：'+convert（varchar（5），@sum ）+'人，已控制在60%以下，及格分数线适中'
           GO

60.错误处理：
使用Raiserror语句：

代码如下:

           Create procedure pro_stuM
                  @notpassSum int output，
                  @scorePass int=60
            As
               If（not @scorePass between 0 and 100）
                   Begin
                   Raiserror（'及格线错误，请指定0—100之间的分数，统计中断退出'，16，1） --------引发系统错误，指定错误的严重级别16，调用状态为1（默认），并影响@@ERROR 系统变量的值
                   Return   --------立即返回，退出存储过程
             End
               ...............其他语句同上例
             Go

61.使用raiserror语句

代码如下:

          Declare @sum int，@t int
          EXEC proc_stum @sum output, 604   ----笔试及格线误输入604分
          Set @t=@@error
          Print '错误号：'+convert（varchar（5），@t ）
          If @t<>o
          Return   ------退出批处理，后续语句不再执行
          Print '-----------------------------------'      -----如果执行了raiseerror语句，系统全局@@error 将不等于0，表示出现了错误.
           If @sum>=3
             Print '未通过人数：'+convert(varchar(5),@sum)+'人，超过了60%，及格分数线还应下调'
           Else   print '未通过人数：'+convert(varchar(5),@sum)+'人，已控制在60%以下，及格分数线适中'              GO

62.调用存储过程：
例：

代码如下:

               Declare @s int
               Exec proc_stuinfo '张三' ，@s ， 30    ———错误！应为（exec proc_stuinfo '张三'，@s output，30）
                Exec proc_stuinfo '张三' ，@s     ————错误！应为（exec proc_stuinfo '张三'，@s output）
                Exec proc_stuinfo @sAge=22，@stuName='李四'，@m=@s output（正确！）

63.带输出参数的存储过程：
例：

代码如下:

           Declare @sum int
           Exec proc_stuM @sum output，60
           Print '-----------------------------------------'
           If @sum>=3
              Print'未通过人数：'+convert（varchar（5），@sum ）+'人，超过60%，及格分数线还应下调'
           Else
               Print'未通过人数：'+convert（varchar（5），@sum ）+'人，已控制在60…60%以下，及格分数线适中'       GO

64.创建触发器语法：

代码如下:

Create trigger trigger_name
                       On table_name
                       [with encryption] --------表示加密触发器定义的SQL文本
                       For [delete,insert,update]    -------指定触发器类型
                       As
                         T_SQL语句
                       GO

65.Insert 触发器
例：

代码如下:

       Create trigger trig_transInfo
       On transInfo
        For insert
        As
        Declare @type char（4），@outMoney Money
        Declare @myCardID char（10），@balance Money
        Declare @currentMoney money
        Select @type=transType,@outMoney=transMoney,@myCardID=cardID from inserted
              If(@type='支取')
                   Update bank set currentMoney=currentMoney-@outMoney
                   Where cardID=@myCardID
              Else
                   Update bank set currentMoney=currentMoney+@outMoney
                   Where cardID=@myCardID

66. 插入一条语句
例：

代码如下:

          Insert into transInfo（transType，transMoney，cardID）
          Values （'支取'，200，'10011001'）

67. Delete触发器示例：
            Create trigger tri_delete_transInfo
            On transInfo
            For delete
            As
                Print '开始备份数据，请稍候......'
                If not exists（select * from sysobjects where name='backupTable'）
                Select * into backupTable from deleted        ------------从deleted表中获取被删除的交易记录。
                 Else
                    Insert into backupTable select * from deleted
                    Print '备份数据成功，备份表中的数据为：'
                    Select * from backupTable
                    Select * from transInfo
                GO

68. Update 触发器：

代码如下:

            Create trigger trig_update_bank
            On bank
            For update
            As
                Declare @beforeMoney Money，@afterMoney Money
                Select @beforeMoney=currentMoney from deleted   -------（从deleted表中获取交易前的金额，从inserted表中获取交易后的余额）
                Select @afterMoney=currentMoney from inserted
                If ABS（@afterMoney-@beforeMoney）>20000 ------交易金额是否>2万
                       Begin
                          Print '交易金额：'+convert（varchar（8），
                          ABS（@afterMoney-@beforeMoney）)
                        Raiserror ('每笔交易不能超过2万元,交易失败',16,1)
                        Rollback transaction         ------------回滚事务,撤销交易
                        End
            Go

69. 列级update触发器

代码如下:

         Create trigger trig_update_transIfo
         ON transInfo
         For update
         As
             If update（transDate）
             Begin                -------检查是否修改了交易日期列transDate
             Print '交易失败......'
             Raiserror（'安全警告：交易日期不能修改，由系统自动产生'，16，1）
             Rollback transaction          ----------回滚事务，撤销交易
             End
         GO

70.数据库阶段数据管理的主要特点：1.采用数据模型表示复杂的数据结构；2.有较高的数据独立性；3.为用户提供了方便的用户接口；4.提供数据控制功能；5.增加系统的灵活性。

71. DB(数据库)：存储在计算机内有组织的、统一管理的相关数据的集合。
DBMS(数据库管理系统)：位于用户与操作系统之间的数据管理软件。
DBS(数据库系统)：是实现有组织的、动态的存储大量关联数据，以方便用户访问计算就硬件、软件和株距资源组成的计算机系统。

72.物理数据独立性和逻辑数据独立性：
物理数据独立性：内模式的修改，只需对模式/内模式映象做相应修改，而不需修改概念模式。逻辑数据独立性：概念模式的修改，只需对外模式/模式映象做相应修改，而不
需修改外模式。

73. DBMS的主要功能：
数据定义（DDL）：三级结构、两级映象、完整性约束等。数据操纵（DML）：检索、更新。数据保护：数据恢复、并发控制、完整性控制、安全性控制等。数据维护：数据载入、转换、存储、、性能监控等。数据字典（DD）：存储三级结构定义的数据库称为DD，即系统数据库。DBMS主要功能：数据库定义功能、数据存取功能、数据库运行管理功能、数据库的建立和维护功能。

74.--1、创建存储过程--

代码如下:

if exists (select * from sysobjects where name='Sum_wage')
drop procedure Sum_wage
GO
create procedure Sum_wage
@PWage int,
@AWage int,
@total int
as
while (1=1)
begin
if (select count(*) from ProWage)>2*(select count(*) from ProWage where Wage>=@PWage)
update ProWage set @total=@total+@AWage,Wage=Wage+@AWage
else
break
end
print'一共加薪：'+convert(varchar,@total)+'元'
print'加薪后的程序员工资列表：'
select * from ProWage
--调用存储过程1--
exec Sum_wage @PWage=2000,@AWage=100,@total=0
exec Sum_wage @PWage=2200,@AWage=100,@total=0
exec Sum_wage @PWage=3000,@AWage=100,@total=0
exec Sum_wage @PWage=4000,@AWage=100,@total=0
exec Sum_wage @PWage=5000,@AWage=100,@total=0
exec Sum_wage @PWage=6000,@AWage=100,@total=0

75.

代码如下:

if exists (select * from sysobjects where name='Avg_wage')
drop procedure Avg_wage
GO
create procedure Avg_wage
@PWage int,
@AWage int,
@total int
as
while (1=1)
begin
if ((select Avg(Wage) from ProWage)<=@PWage)
update ProWage set @total=@total+@AWage,Wage=Wage+@AWage
else
break
end
print'一共加薪：'+convert(varchar,@total)+'元'
print'加薪后的程序员工资列表：'
select * from ProWage
--调用存储过程--
exec Avg_wage @PWage=3000,@AWage=200,@total=0
exec Avg_wage @PWage=4500,@AWage=200,@total=0

76.代码：

代码如下:

INSERT INTO studentinformation(S_name,S_no,S_grade,S_address)
VALUES('花无影',25,'软件0802班','河北')

select studentinformation.S_name,studentinformation.S_no
into information2
from studentinformation
select S_no,S_grade,S_name
from studentinformation
where S_school='软件学院'
order by S_no
select S_no,S_name,S_class
from studentinformation
where S_sex='男'and S_name<>'花无影'
order by S_no
SELECT top 20 percent sname as 姓名,sno as 学号,saddress as 地址
FROM yuan
WHERE sschool ='软件学院'
ORDER BY sno asc,sname desc
select charindex('data','my data is set',1)    （data的索引位置）
select len('java xuexibiji')      （字符串长度）
select lower('BAO DING DIANLI ZHI YE JI SHU XUE YUAN')   （转换成小写）
select replace('软件学院','软件学院','软件工程学院') （替换）
select stuff('a b c d e f g h i j k l m n',3,6,'今天的学习内容')   （从第三字符个开始替换，替换6个字符）
select getdate()   （当前日期）
select dateadd(dd,250,'04/12/2009')   （250天后的日期）
select datediff(mm,'04/20/2005','08/05/2010')   （相差的月数）
select datename(dw,'04/12/2009')    （返回星期几）
select datepart(dd,'04/12/2009')    (返回当天是几号)
select abs(-10)   绝对值
select ceiling(-32.8)    返回大于或等于该数的最小整数
select power(102,3)   取数值表达式的幂值
select round(3.141592653,4)     (?)将数值表达式四舍五入成指定精度
select sign(1000)   正数返回1，负数返回-1，0返回0.
select sqrt(6.25)      //返回该数据的平方根
select convert(varchar(100),45678932) //转变数据类型
select current_user      //返回当前用户的名字
select datalength('软件学院')    //返回字符长度
select host_name()　　　//返回用户登录的计算机名
select system_user      //返回登录的用户名
select user_name(1)     //从任意数据库中返回dbo(jizhu)
create proc pro_stu
@scorepass float
as
print '学号'+convert(varchar(5),@scorepass)
select name
from stuent
where student.score=@scorepass
go
exec pro_stu @scorepass=85

76.导出SQL Server数据库中数据：

代码如下:

declare @objectId int
set @objectId=object_id('sun')
if @objectId is not null
begin
    drop proc sun
end
go
create proc sun (@tablename varchar(256))
as
begin
    set nocount on
    declare @sqlstr varchar(4000)
    declare @sqlstr1 varchar(4000)
    declare @sqlstr2 varchar(4000)
    select @sqlstr='select ''insert '+@tablename
    select @sqlstr1=''
    select @sqlstr2='('
    select @sqlstr1='values (''+'
    select @sqlstr1=@sqlstr1+col+'+'',''+' ,@sqlstr2=@sqlstr2+name +',' from (select case
    when a.xtype =173 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
    when a.xtype =104 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(1),'+a.name +')'+' end'
    when a.xtype =175 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
    when a.xtype =61 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
    when a.xtype =106 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'
    when a.xtype =62 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
    when a.xtype =56 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(11),'+a.name +')'+' end'
    when a.xtype =60 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
    when a.xtype =239 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
    when a.xtype =108 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'
    when a.xtype =231 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
    when a.xtype =59 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
    when a.xtype =58 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
    when a.xtype =52 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(12),'+a.name +')'+' end'
    when a.xtype =122 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
    when a.xtype =48 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(6),'+a.name +')'+' end'
    when a.xtype =165 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
    when a.xtype =167 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
    else '''NULL'''
    end as col,a.colid,a.name
    from syscolumns a where a.id = object_id(@tablename) and a.xtype <>189 and a.xtype <>34 and a.xtype <>35 and a.xtype <>36
    )t order by colid
    select @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+') '+left(@sqlstr1,len(@sqlstr1)-3)+')'' from '+@tablename
    print @sqlstr
    exec( @sqlstr)
    set nocount off
end
go

更多SQL内容来自木庄网络博客

标签：SQL

返回前面的内容