MSSQLSERVER数据库- 存储过程
发布时间：2020-12-12 14:25:38 所属栏目：MsSql教程 来源：网络整理
导读：??????写这篇存储过程的前参看了五六篇别人写的文章，看完后学到一些以前没有发现的东西，原来存储过程里有系统存储过程，原来存储过程还可以有返回值，我将把我从别人那里看到的，重新总结一下写出来。 ????? 什么是存储过程 ??????如果你接触过其他的编程
|
??????写这篇存储过程的前参看了五六篇别人写的文章，看完后学到一些以前没有发现的东西，原来存储过程里有系统存储过程，原来存储过程还可以有返回值，我将把我从别人那里看到的，重新总结一下写出来。 ????? 什么是存储过程 ??????如果你接触过其他的编程语言，那么就好理解了，存储过程就像是方法一样。竟然他是方法那么他就有类似的方法名，方法要传递的变量和返回结果，所以存储过程有存储过程名有存储过程参数也有返回值。? 存储过程的优点： ???
??????存储过程可以分为系统存储过程、扩展存储过程和用户自定义的存储过程 ??? 系统存储过程 ??????我们先来看一下系统存储过程，系统存储过程由系统定义，主要存放在MASTER数据库中，名称以"SP"开头或以"XP"开头。尽管这些系统存储过程在MASTER数据库中，但我们在其他数据库还是可以调用系统存储过程。有一些系统存储过程会在创建新的数据库的时候被自动创建在当前数据库中。 常用系统存储过程有：
?来看一下具体的代码： exec sp_databases --查看有哪些数据库 use MySchool exec sp_tables --可以看TABLE_OWNER字段显示DBO里确认是用户自己 exec sp_columns student --除了用系统视图可以查看列，用系统存储过程也可以查看到列 exec sp_helpindex student --查看索引，可以看到索引的描述，经过测试发现主键也是索种的一种 exec sp_helpconstraint student --查看约束 exec sp_helptext 'sys.all_columns' --查看系统视图 exec sp_helptext 'sp_test' --查看用户自定义的存储过程 exec sp_stored_procedures --查看全部的存储过程 exec sp_rename 'student','stuInfo' --更改表名 use master exec sp_renamedb 'myschool','school' --更改数据库名，为了更改成功，不能使用当前数据库，需切换到其他数据库 exec sp_rename N'student.idx_cid',N'idx_cidd',N'index'; --重命名索引 exec sp_helpdb --数据库帮助，查询数据库信息 --分离数据库 use myschool exec sp_detach_db 'test'; --exec sp_attach_db --附加数据库 EXEC sp_attach_db @dbname = 'test',@filename1 = 'D:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAtest.mdf',@filename2 = 'D:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAtest_log.ldf' ? 用户自定义存储过程 创建存储过程的语法如下： CREATE PROC[EDURE] 存储过程名 @参数1 [数据类型]=[默认值] [OUTPUT] @参数2 [数据类型]=[默认值] [OUTPUT] AS SQL语句 EXEC 过程名[参数] 来看一下各种不同的存储过程的实例： --创建不带参数的存储过程
create procedure pro_student
as
select * from student;
--执行不带参数的存储过程
exec pro_student;
--修改不带参数的存储过程
alter procedure pro_student
as
select * from student where sid>3;
--执行修改后的存储过程
exec pro_student;
--删除存储过程
drop procedure pro_student;
--创建带输出参数的存储过程
create proc proc_getStudentRecord
(
@sex varchar(2) out,--输出参数
@age int output--输入输出参数
)
as
select * from student where ssex = @sex and sage = @age;
--不缓存在存储过程
use myschool;
create procedure proc_recompileStudent
with recompile
as
select * from student
exec proc_recompileStudent
--加密的存储过程
create procedure proc_encrptStudent
with encryption
as
select * from student;
exec proc_recompileStudent
存储过程返回值的方式 1、返回数字类型的存储过程（还没有想到返回字符串的方法） IF exists(select * from sys.objects where name='proc_getScore0')
drop procedure proc_getScore0
GO
create procedure proc_getScore0
(
@id int
)
AS
BEGIN
declare @score int
select @score=english from Score where id=@id
IF(@score>60)
return 0
ELSE
return 1
END
--测试调用返回数字的存储过程<BR>declare @t int
EXEC @t = proc_getScore0 2
select @t;
--这里我遇到一个小问题，如果返回值是字符串，接收的时候declare @t nvarchar也出错，那该怎么做？
--暂时没有想到
2、返回变量的存储过程 IF exists(select * from sys.objects where name='proc_getScore')
drop procedure proc_getScore
GO
CREATE PROCEDURE proc_getScore
@id int,@result varchar(50) output
AS
BEGIN
declare @score int
select @score=english from Score where id=@id
IF(@score>60)
set @result='及格'
ELSE
set @result='不及格'
END
GO
--测试一
declare @id int
declare @temp varchar(50)
set @id=3
exec proc_getScore @id,@temp output
select @temp
??????? 最后一个例子，用C#来调用具有返回值的存储过程，这里我通过调用返回变量类型的存储过程来做测试。测试在控件台下进行，以下写了两种方法，第二种更好，代码如下： using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
//方法一
//using (SqlConnection conn = new SqlConnection("server=.;database=myschool;uid=sa;pwd=123456"))
//{
// conn.Open();
// using (SqlCommand cmd = new SqlCommand("proc_getScore",conn))
// {
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.AddWithValue("@id",2);
// SqlParameter sp = cmd.Parameters.Add("@result",SqlDbType.VarChar,50);
// sp.Direction = ParameterDirection.Output;
// cmd.ExecuteNonQuery();
// Console.Write(sp.Value);
// }
//}
//方法二
using (SqlConnection conn = new SqlConnection("server=.;database=myschool;uid=sa;pwd=123456"))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("proc_getScore",conn))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] paras = {
new SqlParameter("@id",SqlDbType.Int),new SqlParameter("@result",SqlDbType.NVarChar,50)
};
paras[0].Value = 2;
paras[1].Direction = ParameterDirection.Output;
cmd.Parameters.AddRange(paras);
cmd.ExecuteNonQuery();
Console.Write(paras[1].Value);
}
}
Console.ReadLine();
}
}
}
（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |