SQL存储过程与自定义函数

--exec sp_databases
--exec sp_helpdb master
--exec sp_renamedb 原名,新名
--go

--系统命令的改变
/*exec sp_configure 'show advanced options',1
go
reconfigure
exec sp_configure 'xp_cmdshell',1
go
reconfigure
exec xp_cmdshell 'mkdir d:\15041',no_output
go
*/
create database stuDB1504
go
use stuDB1504
create table stuInfo
(
stuName varchar(10) not null,
stuNo char(6) not null primary key,
stuAge int not null
) 
create table stuMarks
(
ExamNo char(8) not null primary key,
stuNo char(8) not null,
writtenExam int,
LabExam int
)
go
use stuDB1504
insert into stuInfo values('花千骨','s25301',100)
insert into stuInfo values('东方','s25302',100)
insert into stuInfo values('秦始皇','s25303',100)
insert into stuInfo values('唐明皇','s25304',100)
insert into stuInfo values('素素','s25318',100)
insert into stuMarks values('S817213','s25318',80,58)
insert into stuMarks values('S817216','s25303',50,90)
insert into stuMarks values('S817217','s25302',77,58)
insert into stuMarks values('S817218','s25301',45,82)
go

常用的系统存储过程

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO 
EXEC xp_cmdshell 'mkdir d:\bank', NO_OUTPUT

comshell扩展存储过程

--Purpose: xp_cmdshell扩展存储过程的使用
USE master
GO
/*---创建数据库bankDB，保存在D:\bank---*/
EXEC xp_cmdshell 'mkdir d:\bank', NO_OUTPUT  --创建文件夹D:\bank 


--创建建库bankDB
IF exists(SELECT * FROM sysdatabases WHERE name='bankDB')
  DROP DATABASE bankDB
GO

CREATE DATABASE bankDB
 ON
 (
  NAME='bankDB_data',
  FILENAME='d:\bank\bankDB_data.mdf',
  SIZE=3mb,
  FILEGROWTH=15%
 )
 LOG ON
 (
  NAME= 'bankDB_log',
  FILENAME='d:\bank\bankDB_log.ldf',
  SIZE=1mb,
  FILEGROWTH=15%
 )
GO

EXEC xp_cmdshell 'dir D:\bank\' --查看文件

不带参数的存储过程

USE stuDB
GO
/*---检测是否存在：存储过程存放在系统表sysobjects中---*/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'proc_stu' )
  DROP PROCEDURE  proc_stu
GO
/*---创建存储过程----*/
CREATE PROCEDURE proc_stu
  AS
select * from stuMarks    
DECLARE @writtenAvg float,@labAvg float --笔试和机试平均分变量
    SELECT @writtenAvg=AVG(writtenExam), @labAvg=AVG(labExam) FROM stuMarks
    print '笔试平均分：'+convert(varchar(5),@writtenAvg)  
    print '机试平均分：'+convert(varchar(5),@labAvg)
    IF (@writtenAvg>70 AND @labAvg>70)
       print '本班考试成绩：优秀'
    ELSE
       print '本班考试成绩：较差'
    print '--------------------------------------------------'
    print '           参加本次考试没有通过的学员：'
    SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM  stuInfo
      INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
         WHERE writtenExam<60 OR labExam<60 
GO

/*---调用存储过程----*/
EXEC proc_stu  --调用存储过程的语法：EXEC 过程名 [参数]

带参数的存储过程

USE stuDB
GO
/*---检测是否存在：存储过程存放在系统表sysobjects中---*/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'proc_stu' )
  DROP PROCEDURE  proc_stu
GO
/*---创建存储过程----*/
CREATE PROCEDURE proc_stu 
  @writtenPass int,
  @labPass int
  AS
    print '笔试及格线：'+convert(varchar(5),@writtenPass)
    print '机试及格线：'+convert(varchar(5),@labPass)
    print '--------------------------------------------------'
    print '           参加本次考试没有通过的学员：'
    SELECT stuName,stuInfo.stuNo,writtenExam,labExam 
    FROM  stuInfo
      INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
    WHERE writtenExam<@writtenPass OR labExam<@labPass 
GO

/*---调用存储过程----*/
--假定本次考试机试偏难，机试的及格线定为55分，笔试及格线定为60分。
EXEC proc_stu 60,55  --或这样调用：EXEC proc_stu @labPass=55,@writtenPass=60

declare @op1 int,@op2 int
set @op1=60
set @op2=55
EXEC proc_stu @op1,@op2

带默认参数的存储过程

USE stuDB
GO
/*---检测是否存在：存储过程存放在系统表sysobjects中---*/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'proc_stu' )
  DROP PROCEDURE  proc_stu
GO
/*---创建存储过程----*/
CREATE PROCEDURE proc_stu 
  @writtenPass int=60,
  @labPass int=60
  AS
    print '笔试及格线：'+convert(varchar(5),@writtenPass)
       + '   机试及格线：'+convert(varchar(5),@labPass)
    print '--------------------------------------------------'
    print '           参加本次考试没有通过的学员：'
    SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM  stuInfo
      INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
         WHERE writtenExam<@writtenPass OR labExam<@labPass 
GO

/*---调用存储过程----*/
EXEC proc_stu   --都采用默认值：笔试和机试及格线都为60分

EXEC proc_stu 64  --机试采用默认值：笔试及格线64分，机试及格线60分。

EXEC proc_stu 60,55   --都不采用默认值：笔试及格线60分，机试及格线55分。

--错误的调用方式：EXEC proc_stu  ,55  --希望笔试采用默认值，机试及格线55分
--正确的调用方式：EXEC proc_stu @labPass=55   --笔试采用默认值，机试及格线55分

带输出参数的存储过程

USE stuDB
GO
/*---检测是否存在：存储过程存放在系统表sysobjects中---*/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'proc_stu' )
  DROP PROCEDURE  proc_stu
GO
/*---创建存储过程----*/
CREATE PROCEDURE proc_stu 
  @notpassSum int OUTPUT, --OUTPUT关键字，否则视为输入参数
  @writtenPass int=60,  --默认参数放后
  @labPass int=60       --默认参数放后
  AS
    print '笔试及格线：'+convert(varchar(5),@writtenPass)
       + '   机试及格线：'+convert(varchar(5),@labPass)
    print '--------------------------------------------------'
    print '           参加本次考试没有通过的学员：'
    print '  学号      姓名   笔试成绩   机试成绩  '

         --SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM  stuInfo
      --INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
        -- WHERE writtenExam<@writtenPass OR labExam<@labPass

    /*定义变量*/
    DECLARE @stuNo char(6),@stuName varchar(20),@writtenExam int, @labExam int

    /*定义只读游标*/
    DECLARE user_csr CURSOR READ_ONLY FOR 
      SELECT stuInfo.stuNo,stuName,writtenExam,labExam FROM  stuInfo
          INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
             WHERE writtenExam<@writtenPass OR labExam<@labPass

    /*打开游标*/
    OPEN user_csr
    /*执行第一次数据读取操作*/
    FETCH NEXT FROM user_csr INTO @stuNo,@stuName,@writtenExam,@labExam
    /*循环游标读取操作*/
    WHILE @@FETCH_STATUS=0
    BEGIN
        PRINT  ' ' + @stuNo + '    ' + @stuName + '     ' + convert(varchar(3),@writtenExam)+'         '+convert(varchar(3),@labExam)
        FETCH NEXT FROM user_csr INTO @stuNo,@stuName,@writtenExam,@labExam
    END 
    /*关闭游标*/
    CLOSE user_csr
    /*释放游标*/
    DEALLOCATE user_csr

    /*--统计并返回没有通过考试的学员人数--*/
    SELECT @notpassSum=COUNT(stuNo)FROM stuMarks
      WHERE writtenExam<@writtenPass OR labExam<@labPass 

GO

/*---调用存储过程----*/
DECLARE @sum int
EXEC proc_stu @sum OUTPUT ,64   --机试及格线采用默认值：笔试及格线64分，机试及格线60分。
print '--------------------------------------------------'
IF @sum>=3
  print '未通过人数：'+convert(varchar(5),@sum)+ '人,超过60%,及格分数线还应下调'
ELSE
  print '未通过人数：'+convert(varchar(5),@sum)+ '人,已控制在60%以下，及格分数线适中'
GO

带返回值的存储过程

USE stuDB
GO
/*---检测是否存在：存储过程存放在系统表sysobjects中---*/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'proc_stu' )
  DROP PROCEDURE  proc_stu
GO
/*---创建存储过程----*/
CREATE PROCEDURE proc_stu 
  @writtenPass int=60,  --默认参数放后
  @labPass int=60       --默认参数放后
  AS
    declare @notpassSum int

    /*--统计并返回没有通过考试的学员人数--*/
    SELECT @notpassSum=COUNT(stuNo)FROM stuMarks
      WHERE writtenExam<@writtenPass OR labExam<@labPass 

    IF @notpassSum>=5
      begin
        print '未通过人数：'+convert(varchar(5),@notpassSum)+ '人,超过60%'
        return 0;
      end
    ELSE
      begin
        print '未通过人数：'+convert(varchar(5),@notpassSum)+ '人,已控制在60%以下'
        return 1;
      end
GO


/*---检测是否存在：存储过程存放在系统表sysobjects中---*/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'proc_stu2' )
  DROP PROCEDURE  proc_stu2
GO
/*---创建存储过程----*/
CREATE PROCEDURE proc_stu2 
  AS
    declare @result int
    exec @result =  proc_stu 64,60
    IF @result=0
        print '及格分数线还应下调'
    else
        print '及格分数线适中'
GO

exec dbo.proc_stu2

临时表

//建表
CREATE TABLE tb1(
    ID1 char(2) NOT NULL,
    ID2 char(4) NOT NULL,
    col int,
    PRIMARY KEY(ID1,ID2)
)
GO
INSERT tb1 SELECT 'aa','0001',1
    UNION SELECT 'aa','0003',2
    UNION SELECT 'aa','0004',3
    UNION SELECT 'bb','0005',4
    UNION SELECT 'bb','0006',5
    UNION SELECT 'cc','0007',6
    UNION SELECT 'cc','0009',7
GO
select * from tb1

--重排编号处理
SELECT ID=IDENTITY(int,0,1),* INTO #tempTb FROM tb1 ORDER BY ID1,ID2

UPDATE t1 SET ID2=RIGHT(10001+tmp1.ID-tmp2.ID,4)
FROM tb1 t1,#tempTb tmp1,(SELECT ID1,ID=MIN(ID) FROM #tempTb GROUP BY ID1) tmp2
WHERE t1.ID1=tmp1.ID1 AND t1.ID2=tmp1.ID2 AND tmp1.ID1=tmp2.ID1

DROP TABLE #tempTb

SELECT * FROM tb1

自定义函数1

CREATE FUNCTION dbo.fun_hello
( @name VARCHAR(8) )
RETURNS VARCHAR(20)
AS
BEGIN
    RETURN @name+'，您好!'
END
GO


select dbo.fun_hello('欧阳夏丹') as '问好'

自定义函数2

CREATE FUNCTION dbo.getSectionUser
( @uid int )
RETURNS VARCHAR(15)
AS
BEGIN
    DECLARE @uname varchar(15)
    select @uname=Uname from bbsUsers where UID=@uid
    RETURN @uname
END
GO



select Sname, dbo.getSectionUser(SmasterID) as '版主' from bbsSection