SQL server数据库保存位置 sql server如何保存数据库

先上代码

--///【存储过程】
--1.新建存储过程

/*---检查是否存储过程是否已存在---*/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'cp_petowner' )
    begin
      DROP PROCEDURE  cp_petowner --放弃创建
      print '创建存储过程,名称为[cp_petowner]重名，放弃创建！'
    end

 
--开始创建
 
GO
CREATE PROCEDURE cp_petowner
AS   
select * from petowner
          
GO
--查询
EXEC cp_petowner
----------------------------------------------------------

GO
----------------------------------------------------------

--2.创建一个带参数的存储过程
CREATE PROCEDURE cp_petowner_VarCharValue
@ownername varchar(50)
AS   
select * from petowner where name=@ownername

GO
--查询
EXEC cp_petowner_VarCharValue @ownername='小强'
----------------------------------------------------------

--3.创建一个带参数且有返回值的存储过程
GO
CREATE PROCEDURE cp_petowner_VarCharValue_outMoney
@ownername varchar(50),
@money int OUTPUT
AS   
begin
select @money=[money] from petowner where name=@ownername

end
GO
--查询
 
DECLARE @money int 
EXEC cp_petowner_VarCharValue_outMoney '小强',@money OUTPUT
select @money
----------------------------------------------------------

--【查询的时候】
--你可以这样写
--EXECUTE  过程名  [参数]
--还可以
--EXEC    过程名  [参数]

----------------------------------------------------------
--【创建的时候】
--你可以这样写
--create proc proc_test1
--还可以
--create procedure proc_test1

 

 

---

 

 

存储过程

自定义-存储过程、系统存储过程、扩展性存储过程
缺点：语法变的更复杂
特点：预编译、批处理(T-SQL)
优点1：提高查询效率(预编译、T-SQL减少网络流通)
优点2：存储过程(T-SQL)只能通过参数化方式调用
优点3：模块化编程(功能,存储过程也可以调用存储过程)
形参：普通参数，引用参数,输出参数

---

为什么使用存储过程

使用存储过程增加程序的安全性

 

---

什么是存储过程

预先存储好的SQL程序

数据操作语句、变量、逻辑控制语句等

保存在SQL Server中

通过名称和参数执行，也可返回结果

 

---

 

 

存储过程的优点

1.执行速度更快

2.允许模块化程序设计

3.提高系统安全性

4.减少网络流通量

---

存储过程的分类

系统存储过程

1.系统存储过程的名称一般以“sp_”开头

2.由SQLServer创建、管理和使用

3.存放在Resource数据库中

4.类似C#语言类库中的方法

扩展存储过程

1.扩展存储过程的名称通常以“xp_”开头

2.使用编辑语言（如C#)创建的外部存储过程

3.以DLL形式单独存在

用户自定义存储过程

1.由用户在自己的数据库中创建的存储过程

2.类似C#语言中用户自定义的方法

---

常用的系统存储过程

系统存储过程	说 明
sp_databases	列出服务器上的所有数据库
sp_helpdb	报告有关指定数据库或所有数据库的信息
sp_renamedb	更改数据库的名称
sp_tables	返回当前环境下可查询的对象的列表
sp_columns	返回某个表列的信息
sp_help	查看某个表的所有信息
sp_helpconstraint	查看某个表的约束
sp_helpindex	查看某个表的索引
sp_stored_procedures	列出当前环境中的所有存储过程
sp_password	添加或修改登录帐户的密码
sp_helptext	显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本

 

 

---

 

 

调用存储过程

调用存储过程的语法

EXECUTE  过程名  [参数]

或

EXEC    过程名  [参数]

如果执行存储过程的语句是批处理中的第一个语句，则可以不指定 EXECUTE 关键字

---

xp_cmdshell

可以执行DOS命令下的一些操作

以文本行方式返回任何输出

EXEC xp_cmdshell DOS命令 [NO_OUTPUT]

示例:

创建数据库bankDB，要求保存在D:\bank

USE master
GO
EXEC xp_cmdshell 'mkdir D:\bank', NO_OUTPUT
IF EXISTS(SELECT * FROM sysdatabases  WHERE name='bankDB')
   DROP DATABASE bankDB
GO
CREATE DATABASE bankDB
 (
  …
)
GO
EXEC xp_cmdshell 'dir D:\bank\'   -- 查看文件

xp_cmdshell 'mkdir D:\bank'

创建文件夹D:\bank

xp_cmdshell 'dir D:\bank\'

查看文件夹D:\bank

---

 

 

如何创建存储过程

定义存储过程的语法

CREATE  PROC[EDURE]  存储过程名 
             @参数1  数据类型 = 默认值 OUTPUT,
              … … ,
             @参数n  数据类型 = 默认值 OUTPUT
 AS
            SQL语句
GO

存储过程的参数

1.和C#语言的方法一样，参数可选

2.参数分为输入参数、输出参数

3.输入参数允许有默认值

---

创建、执行无参的存储过程

创建存储过程，查询Java Logic最近一次考试平均分以及未通过考试的学员名单

第一步：获得“Java Logic”的课程编号

第二步：获得“Java Logic”最近一次的考试时间

第三步：查询得到平均成绩

第四步：查询这次考试成绩低于60分的学生

CREATE PROCEDURE usp_GetAvgResult
AS     … …
    SELECT @subjectNo=subjectNo FROM Subject 
    WHERE SubjectName = 'Java Logic'

--获得课程编号 

    SELECT  @date=max(ExamDate) FROM Result INNER JOIN  Subject
      ON Result.SubjectNo=Subject.SubjectNo
      WHERE Result.SubjectNo=@subjectNo
--获得考试时间

--考试平均分

    SELECT @avg=AVG(StudentResult) FROM Result 
    WHERE ExamDate=@date and SubjectNo=@subjectNo
    … …
--显示考试成绩的等级
    IF (@avg>70)       PRINT '考试成绩：优秀'
    ELSE                    PRINT '考试成绩：较差'


……
    SELECT StudentName,Student.StudentNo,StudentResult 
    FROM  Student  INNER JOIN Result 
    ON Student.StudentNo=Result.StudentNo
    WHERE StudentResult<60 AND … … 
--查询未通过的学员 

GO
--执行存储过程 

EXEC usp_GetAvgResult

 

---

 

查询获得各学期课程信息

使用无参存储过程完成数据查询

需求说明：

利用存储过程查询各学期 开设的课程名称和每门课程 的课时

/*---检查是否存储过程是否已存在---*/
IF EXISTS (SELECT * FROM sysobjects
                                     WHERE name = 'usp_grade_subject' )
  DROP PROCEDURE  usp_grade_subject
GO
/*---创建存储过程----*/
CREATE PROCEDURE usp_grade_subject
AS 
   SELECT GradeName,SubjectName,ClassHour FROM Grade 
   INNER JOIN Subject
   ON Grade.GradeId=Subject.GradeId
   ORDER BY Subject.GradeId,SubjectNo
GO
/*---调用执行存储过程---*/
EXEC usp_grade_subject

 

---

 

 

存储过程参数

存储过程的参数分两种：

输入参数

向存储过程传入值

输出参数

存储过程执行后，传出执行结果

---

带输入参数的存储过程

假定C# OOP课程最近一次考试的试题偏难，考试及格线定为50分

上述存储过程添加2个输入参数

@score：考试及格线

@subName：课程名称

编写存储过程

CREATE PROCEDURE usp_unpass
    @subName varchar(50), 
     @score  int
--输入参数：课程名称
--输入参数：考试及格线

AS
    DECLARE @date datetime           --最近考试时间
    DECLARE @subjectNo int            --课程编号
    SELECT @subjectNo=SubjectNo FROM Subject 
    Where SubjectName = @subName
    … …
    SELECT StudentName,Student.StudentNo,StudentResult 
    FROM  Student
    INNER JOIN Result ON Student.StudentNo = Result.StudentNo
    WHERE StudentResult < @score AND ExamDate = @date 
      AND SubjectNo=@subjectNo
--查询某课程最近一次考试没有通过的学员

GO

 

调用存储过程

EXEC usp_unpass 'C# OOP' , 50

或

EXEC usp_unpass @score=50, @subName='C# OOP'

---

 

输入参数的默认值

如果试卷难易程度合适，则调用者仍须调用：

EXEC usp_unpass  'C# OOP', 60

有简便的方法吗？

使用参数的默认值

创建带参数默认值的存储过程

CREATE PROCEDURE usp_unpass
    @subName varchar(50),
    @score  int = 60 --考试及格线默认为标准的60分 
AS
    … …
GO

有默认值的参数放在存储过程参数列表的最后

调用带参数默认值的存储过程

EXEC usp_unpass 'C# OOP' --考试及格线默认为标准的60分

或

EXEC usp_unpass @subName = 'C# OOP'

如果有默认值的参数出现在没有默认值参数的前面，那么需要指定参数名为其赋值

 

---

常见错误

 

SQLServer存储过程的最大嵌套层数为32。 如果超过最大嵌套层数，会造成执行出错

 

---

查询指定学期课程信息

需求说明：

1.查询输出指定学期总课时和开设的课程名称、每门课程的课时

2.如果没有指定学期名称则查看每个学期的总课时和开设的课程名称、课时

提示：

1.为输入参数——课程名称指定默认值为NULL

2.判断输入参数是否为NULL

3.使用UNION关键字合并2个查询的记录集

关键代码

CREATE PROCEDURE usp_query_subject
  @GradeName VARCHAR(50) = NULL
AS 
  IF @GradeName IS NULL
    SELECT GradeName,SubjectName,ClassHour FROM Grade 
    LEFT JOIN Subject   ON Grade.GradeId=Subject.GradeId 
    UNION 
    SELECT GradeName,' ',SUM(ClassHour)FROM Grade
    LEFT JOIN Subject  ON Grade.GradeId=Subject.GradeId 
    GROUP BY GradeName
  ELSE
…….
GO

 

---

 

带输出参数的存储过程

 

问题：

修改上例需求，要求获得参加考试的学生人数和未通过的学生人数

分析：

调用存储过程返回二个数据值，可以使用输出参数

创建带输出参数的存储过程

CREATE PROCEDURE usp_query_num 
    @UnPassNum INT OUTPUT,   --输出没有通过的人数
    @TotalNum INT OUTPUT,     --输出参加考试的总人数
    ……
 AS
   DECLARE @date datetime
   DECLARE @subjectNo int
    … …

--统计获得没有通过考试的学员人数
   SELECT @UnPassNum = COUNT(*) FROM Result  
     WHERE ExamDate=@date AND subjectNo=@subjectNo 
     AND StudentResult<@Pass

--统计获得参加考试的学员总人数
   SELECT @TotalNum = COUNT(*) FROM Result  
    WHERE ExamDate=@date AND subjectNo=@subjectNo
GO

 

调用带输出参数的存储过程

DECLARE @UnPassNum int
DECLARE @TotalNum int

--调用时必须带OUTPUT关键字
EXEC usp_query_num @UnPassNum OUTPUT, @TotalNum OUTPUT, 
                                          'Java Logic'
DECLARE @ratio decimal(10,2)
--后续语句引用返回结果
SET @ratio = CONVERT(decimal, (@TotalNum - @UnPassNum))  / 
                  @TotalNum * 100
PRINT '未通过人数:' + CAST(@UnPassNum AS varchar(10)) +
            '人，及格率是' + CAST(@ratio AS varchar(10)) + '%'
…..

技巧：

当某个参数按照“@参数名=参数值”格式传递数据，那么该参数之后的其他参数也必须采用相同格式传递数据

 

---

查询指定学期课程数

使用输入、输出参数的存储过程完成数据查询，获得相关数据

需求说明：

查询获得指定学期开设的课程数和总课时

如果学期名称为空，则显示“学期名称不能为空”，并返回

实现思路：

1.输入参数是学期名称

2.输出参数是课程数和总课时数

3.查询获得指定学期所开设的课程、课时

4.查询该学期的总课程数、总课时数

关键代码：

CREATE PROCEDURE usp_query_subject
   @CourseNum INT OUTPUT,  
   @HourNum INT OUTPUT,  
   @GradeName VARCHAR(50)
AS 
    ……
    SELECT GradeName,SubjectName,ClassHour FROM Grade 
    LEFT JOIN Subject ON Grade.GradeId=Subject.GradeId 
    WHERE GradeName=@GradeName

    SELECT @CourseNum=COUNT(0), @HourNum=SUM(ClassHour)
    FROM  Grade
    INNER JOIN Subject ON Grade.GradeId=Subject.GradeId 
    WHERE GradeName=@GradeName
GO

 

---

处理存储过程中的错误

使用PRINT语句显示错误信息

错误信息是临时的，只能显示给用户

RAISERROR 显示用户定义的错误信息时

1.可指定严重级别

2.设置系统变量@@ERROR

3.记录所发生的错误等

---

使用RAISERROR 语句

 RAISERROR语句的用法如下：

RAISERROR (msg_id | msg_str,severity,   state WITH option[, ... n]])

msg_id：在sysmessages系统表中指定用户定义错误信息

msg_str：用户定义的特定信息，最长255个字符

severity：定义严重性级别。用户可使用的级别为0–18级

state：表示错误的状态，1至127之间的值

option：指示是否将错误记录到服务器错误日志中

 

 

示例：

完善上例。调用存储过程时，如果传入的及格线参数不在0～100之间时，将弹出错误警告，终止存储过程的执行

CREATE PROCEDURE usp_stu 
  @UnPassNum OUTPUT,
  @SubjectName NCHAR(10),
  @Pass int = 60
AS
   IF (NOT @Pass BETWEEN 0 AND 100)  --判断及格线是否不在取值范围内
    BEGIN
      RAISERROR ('及格线错误，请指定－之间的分数，统计中断退出',16,1)
      RETURN
    END
  … … 
GO

 

执行存储过程

/*---调用存储过程，测试RAISERROR语句----*/
DECLARE @UnPassNum int
EXEC usp_stu @UnPassNum OUTPUT,'Java logic',109
DECLARE @err int
SET @err = @@ERROR
IF @err <> 0
  BEGIN
    PRINT  '错误号：'+convert(varchar(5),@err )
    RETURN                           --退出批处理，后续语句不再执行
  END
ELSE
  PRINT '未通过人数：' + convert(varchar(5),@UnPassNum)
GO

 

---

 

总结

A.存储过程是一组预编译的SQL语句，包含数据操作语句、逻辑控制语句和调用函数等

B.存储过程可分为系统存储过程和用户定义的存储过程

C.存储过程的参数分为输入参数和输出参数

1.输入参数用来向存储过程中传入值

2.输出参数用于从存储过程中返回（输出）值，后面跟随“OUTPUT”关键字

---

 

笔记

--存储过程编译、执行
select *from Students
--execute
exec proc_GetProduct '参数'


--自定义-存储过程
--缺点：语法变的更复杂
--特点：预编译、批处理(T-SQL)
--优点1：提高查询效率(预编译、T-SQL减少网络流通)
--优点2：存储过程(T-SQL)只能通过参数化方式调用
--优点3：模块化编程(功能,存储过程也可以调用存储过程)
--形参：普通参数，引用参数,输出参数
访问修饰符 返回值 方法名(int account,int password)
{
return "xxx";
}

string name =方法名(xxx);

--事务
use QQDB--使用QQDB
--procedure
--语法规范：PROC_功能名称描述
--三种参数:输入参数  输出参数 output   返回值参数
--修改alter 创建create
create proc proc_test1
as
select * from UserInfos

exec proc_test1
--insert 
--报表:复杂查询
--不准用存储过程
alter proc PROC_Register
(
    @password varchar(50),--输入参数
    @nickname varchar(50)--,
    --@qq int output --输出参数
)
as
begin
    insert into UserInfos(pwd, nickname)
    values(@password,@nickname)
    --select @qq=@@IDENTITY  --获取当前最近插入的自增id
    return @@identity --返回值参数
    exec proc_test1--方法调方法
end


--T-SQL编程 if while 事务、游标


--execute执行
--定义一个参数接收输出参数
declare @qq2 int
exec PROC_Register '123456','鬼脚七3',@qq2 output
select @qq2

--定义一个参数接收返回值参数
declare @qq3 int
exec @qq3=PROC_Register '123456','鬼脚七3'
select @qq3

--返回QQ号码 程序设计QQ号码靓号


 exec sp_databases