SQL存储过程与自定义函数

迷南。 2022-06-04 02:56 258阅读 0赞

--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