写这篇存储过程的前参看了五六篇别人写的文章,看完后学到一些以前没有发现的东西,原来存储过程里有系统存储过程,原来存储过程还可以有返回值,我将把我从别人那里看到的,重新总结一下写出来。
什么是存储过程
如果你接触过其他的编程语言,那么就好理解了,存储过程就像是方法一样。竟然他是方法那么他就有类似的方法名,方法要传递的变量和返回结果,所以存储过程有存储过程名有存储过程参数也有返回值。
存储过程的优点:
- 存储过程的能力大大增强了SQL语言的功能和灵活性。
- 可保证数据的安全性和完整性。
- 通过存储过程可以使没有权限的用户在控制之下间接地存取数据库,从而保证数据的安全。
- 通过存储过程可以使相关的动作在一起发生,从而可以维护数据库的完整性。
- 在运行存储过程前,数据库已对其进行了语法和句法分析,并给出了优化执行方案。这种已经编译好的过程可极大地改善SQL语句的性能。
- 可以降低网络的通信量。
- 使体现企业规则的运算程序放入数据库服务器中,以便 集中控制。
存储过程可以分为系统存储过程、扩展存储过程和用户自定义的存储过程
系统存储过程
我们先来看一下系统存储过程,系统存储过程由系统定义,主要存放在MASTER数据库中,名称以"SP"开头或以"XP"开头。尽管这些系统存储过程在MASTER数据库中,但我们在其他数据库还是可以调用系统存储过程。有一些系统存储过程会在创建新的数据库的时候被自动创建在当前数据库中。
常用系统存储过程有:
- exec sp_databases; --查看数据库
- exec sp_tables; --查看表
- exec sp_columns student;--查看列
- exec sp_helpIndex student;--查看索引
- exec sp_helpConstraint student;--约束
- exec sp_helptext 'sp_stored_procedures';--查看存储过程创建定义的语句
- exec sp_stored_procedures;
- exec sp_rename student, stuInfo;--更改表名
- exec sp_renamedb myTempDB, myDB;--更改数据库名称
- exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
- exec sp_helpdb;--数据库帮助,查询数据库信息
- exec sp_helpdb master;
- exec sp_attach_db --附加数据库
- exec sp_detach_db --分离数据库
来看一下具体的代码:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
exec sp_databases
use
MySchool
exec sp_tables
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
@dbname = 'test' ,
@filename1
= 'D:\Program
Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test.mdf' ,
@filename2
= 'D:\Program
Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test_log.ldf'
|
用户自定义存储过程
在创建一个存储过程前,先来说一下存储过程的命名,看到好几篇讲存储过程的文章都喜欢在创建存储过程的时候加一个前缀,养成在存储过程名前加前缀的习惯很重要,虽然这只是一件很小的事情,但是往往小细节决定大成败。看到有的人喜欢这样加前缀,例如proc_名字。也看到这加样前缀usp_名字。前一种proc是procedure的简写,后一种sup意思是user procedure。我比较喜欢第一种,那么下面所有的存储过程名都以第一种来写。至于名字的写法采用骆驼命名法。
创建存储过程的语法如下:
CREATE PROC[EDURE] 存储过程名
@参数1 [数据类型]=[默认值] [OUTPUT]
@参数2 [数据类型]=[默认值] [OUTPUT]
AS
SQL语句
EXEC 过程名[参数]
来看一下各种不同的存储过程的实例:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
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、返回数字类型的存储过程(还没有想到返回字符串的方法)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
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
EXEC @t
= proc_getScore0 2
select @t;
|
2、返回变量的存储过程
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
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#来调用具有返回值的存储过程,这里我通过调用返回变量类型的存储过程来做测试。测试在控件台下进行,以下写了两种方法,第二种更好,代码如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
|
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;
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();
}
}
}
|
分享到:
相关推荐
资源里包含SQL文档:创建数据库和存储过程.sql 生成数据.sql 测试.sql 比较完善的大量数据分表功能代码,包含创建数据库、生成数据、测试结果。适合初学分表者使用。 数据按照每个月创建一个数据表,通过存储过程...
一个查看MSSQLServer数据库空间使用情况的存储过程 SpaceUsed
C#Hibernate mssqlserver数据库海量读写快速存储
MSSQLserver数据库学习全程笔记及学习样例,从简单的数据库建立、建表到各种数据查询方法、视图建立与使用、存储过程及索引的建立与使用,从简单到深入到精深!无限全面,简直是数据库学习的极品和必备!
⽬录 正⽂ 正⽂ 系统数据库说明 系统数据库说明 ----1.resource /* 包含SQLServer运⾏所需的关键系统表、元数、系统存储过程,它只包含系统相关的信息不包含⽤户相关的信息,在安装补丁的过程中将更改该数据库 */ ...
不能作为生产服务器使用) 企业评估版(包括企业版的全部功能,但安装起120天后该版本将停止运行) 版(在设备上进行数据存储,能使用任何版本 2000复制数据,以使数据与主数据库保持同步) MSSQLServer培训资料全文...
涵盖数据库编程,数据库后台管理两大方面。包括触发器/存储过程/函数/游标/视图/索引/函数,等多种数据库对象的创建示例。后台管理包括数据库备份/还原/导出/日志压缩等。
用于管理数据库MSSQLServer和MSAccess的控制台:-快速,轻松地访问表,视图,存储过程和触发器-简单但功能强大的查询编辑器-能够保存更频繁使用的连接和脚本的能力内置WPF,可移植且无需安装。
进入到设置界面后备份类型选择完整,添加按钮可选择备份文件的存储位置,在这里我按照MSSQL默认的目录,点击确定 即: C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\ 当然,还是按照...
本程序采用MSSQLserver数据库存储,请运行程序前手动修改程序开头处的数据库链接信息 2.需要bs4、requests、pymssql库支持 3.支持多线程 from bs4 import BeautifulSoup import re,requests,pymysql,threading,os,...
由于数据库存储容量相当 大,而且比较稳定,适合较长时间的保存,也不容易丢失。这无疑是为存储量比较大的 学校提供了一个方便、快捷的操作方式。本系统具有运行速度快、安全性高、稳定性好 的优点,并且具备完善的...
包含我的CSC 410课程Web数据库编程的多个项目的存储库,由John Kirchmeyer博士教授。 网页列表及其作用: Default.html页面-主页以及其他页面的链接 Lab 1a.html一个简单HTML网页 Lab 1b.aspx从文本框中获取输入并...
cursor之诊断) 数据挖掘 SQL Server 2005:你应该知道的13件事情 存储过程和触发器 错误提示:OLE DB error: OLE DB or ODBC error: Query timeout expired HYT00 SQL Server 2005——你应该知道的...
服务器端代理是保存在SQLServer主机中的一个只读存储过程,他的作用是接受客户端请求,读取在线事物日志块并通过网络传给客户端软件,由客户端软件来读取这些原始的数据块来完成Log Explore所提供的所有功能。...
服务器端代理是保存在SQLServer主机中的一个只读存储过程,他的作用是接受客户端请求,读取在线事物日志块并通过网络传给客户端软件,由客户端软件来读取这些原始的数据块来完成Log Explore所提供的所有功能。...
服务器端代理是保存在SQLServer主机中的一个只读存储过程,他的作用是接受客户端请求,读取在线事物日志块并通过网络传给客户端软件,由客户端软件来读取这些原始的数据块来完成Log Explore所提供的所有功能。...
将数据库表中的数据生成Insert脚本的存储过程!!! 2分法-通用存储过程分页(top max模式)版本(性能相对之前的not in版本极大提高) 分页存储过程:排序反转分页法 优化后的通用分页存储过程 sql语句 一些Select检索高级...
服务器端代理是保存在SQLServer主机中的一个只读存储过程,他的作用是接受客户端请求,读取在线事物日志块并通过网络传给客户端软件,由客户端软件来读取这些原始的数据块来完成Log Explore所提供的所有功能。...
原路径:C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA 迁移后路径:D:\SQLSERVER .LDF文件:数据库日志文件 .MDF文件:数据库文件 登录账户: windwos身份验证:windwos本地账户,具有...