SQL Server 存储过程

  在C语言的程序设计中,会把一个重复使用的功能提取出来,做成一个的函数,这样就可以减少冗余代码,且更方便维护、调用。在面向对象的设计语言中,会把一个重复使用的功能提取出来,做成一个类,同样也是为了减少冗余代码,且更方便维护、调用。在数据库实现一些重复的事物处理时,也有跟C语言、面向对象的设计语言里面类似的想法。把一大块重复用到的SQL语句封装成一个代码块,方便调用,减少数据库开发人员的工作量。

  对于SQL SERFVER数据库也学了有一阵子了,自己也对自己所学做了一些总结。

存储过程的概念

  

   我们首先学习数据库设计的一些知识点和用SQL语句建库。

存储过程Procedure是一组为了完成特定功能的SQL语句集合,经编译后存储在数据库中,用户通过指定存储过程的名称并给出参数来执行。
存储过程中可以包含逻辑控制语句和数据操纵语句,它可以接受参数、输出参数、返回单个或多个结果集以及返回值。
由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中,所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息,所以在一定程度上也可以减少网络流量、减少网络负担。

1、创建存储过程
create proc InsertUserInfo(@name varchar(50), @pwd varchar(10), @age
int, @gender char(5))
as
insert into UserInfo(UserName, Pwd, Age, Gender) 
values(@name, @pwd, @age, @gender)
select * from UserInfo

  设计数据库步骤:需求分析阶段,概要设计阶段,详细设计阶段,

 

 

   建数据库的SQL语句如下(包含了如果有该数据库先删除在建立数据库)

 

2、执行存储过程

use master
GO
if exists(select * from sysdatabases where name=’Wages’)
DROP database Wages
CREATE DATABASE Wages
 ON
 (
  NAME=’Wages_data’,
  FILENAME=’e:\project\Wages_data.mdf’,
  SIZE=5mb,
  FILEGROWTH=15%
 )
 LOG ON
 (
  NAME= ‘Wages_log’,
  FILENAME=’e:\project\Wages_log.ldf’,
  SIZE=3mb,
  FILEGROWTH=15%
 )
GO

存储过程的优点

execute InsertUserInfo ‘阿诺施瓦辛格’, ‘anuoshiwa’, 60, ‘男’
execute InsertUserInfo ‘周润发’, ‘zhourunfa’, 65, ‘男’

为了创建良好的数据库需满足三大范式。

A、 存储过程允许标准组件式编程
存储过程创建后可以在程序中被多次调用执行,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,但对应用程序源代码却毫无影响,从而极大的提高了程序的可移植性。

 

下面是创建表的SQL语句(包含了如果有该表先删除在建表)

B、 存储过程能够实现较快的执行速度
如果某一操作包含大量的T-SQL语句代码,分别被多次执行,那么存储过程要比批处理的执行速度快得多。因为存储过程是预编译的,在首次运行一个存储过程时,查询优化器对其进行分析、优化,并给出最终被存在系统表中的存储计划。而批处理的T-SQL语句每次运行都需要预编译和优化,所以速度就要慢一些。

USE Wages
GO
if exists(select * from sysobjects where name=’WageInfo’)
DROP table WageInfo
CREATE TABLE WageInfo 
(
  CompanyID INT primary key IDENTITY(1,1),
  CompanyName varchar(50) NOT NULL,
  Poste varchar(50) NOT NULL,
)
GO
if exists(select * from sysobjects where name=’StudentInfo’)
DROP table StudentInfo
CREATE TABLE StudentInfo
(
  ID  INT primary key IDENTITY(1,1),
  Name  VARCHAR(50) NOT NULL,
  CompanyID INT ,
  PosteDate  DATETIME ,
  Wage  int,
)
GO

C、 存储过程减轻网络流量
对于同一个针对数据库对象的操作,如果这一操作所涉及到的T-SQL语句被组织成一存储过程,那么当在客户机上调用该存储过程时,网络中传递的只是该调用语句,否则将会是多条SQL语句。从而减轻了网络流量,降低了网络负载。

下面是创建约束

D、 存储过程可被作为一种安全机制来充分利用
系统管理员可以对执行的某一个存储过程进行权限限制,从而能够实现对某些数据访问的限制,避免非授权用户对数据的访问,保证数据的安全。

语法如下

 

alter table 表名

 

add constraint 约束名 约束类型 具体的约束说明

系统存储过程

示例

exec sp_databases; –查看数据库
exec sp_tables; –查看表
exec sp_columns student;–查看列
exec sp_helpIndex student;–查看索引
exec sp_helpConstraint student;–约束
exec sp_stored_procedures;
exec sp_helptext
‘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;

创建外键约束

 

alter table StudentInfo
add constraint pk_CompanyID foreign key(CompanyID) references
WageInfo(CompanyID)
GO

系统存储过程示例:
–表重命名
exec sp_rename ‘stu’, ‘stud’;

插入数据语句如下

–列重命名
exec sp_rename ‘stud.name’, ‘sName’, ‘column’;
exec sp_help ‘stud’;

insert into WageInfo(CompanyName, Poste)values
(‘微软’,’职员’),
(‘百度’,’职员’),
(‘腾讯’,’职员’),
(‘爱奇艺’,’职员’),
(‘华为’,’职员’)
insert into StudentInfo(Name, CompanyID, PosteDate, Wage)values
(‘张三’,1,’2016-05-05′,2500),
(‘李四’,2,’2016-05-06′,2600),
(‘王五’,3,’2016-05-07′,3000),
(‘赵二’,4,’2016-05-08′,4000),
(‘钱一’,5,’2016-05-09′,5000)
insert into StudentInfo(Name, CompanyID, PosteDate,
Wage)values(‘钱二’,null,’2016-05-09′,NULL)

–重命名索引
exec sp_rename N’student.idx_cid’, N’idx_cidd’, N’index’;
exec sp_help ‘student’;

然后我们学习了变量,变量分全局变量和局部变量。

–查询所有存储过程
select * from sys.objects where type = ‘P’;
select * from sys.objects where type_desc like ‘%pro%’ and name like
‘sp%’;

创建变量语法如下是

澳门金沙vip, 

declare @变量名 数据类型

 

局部变量有两种赋值方法

用户自定义存储过程

set @变量名=value

1、 创建语法

select @变量名=value

create proc | procedure pro_name
[{@参数数据类型} [=默认值] [output],
{@参数数据类型} [=默认值] [output],
….
]
as
SQL_statements

区别是select可一次对多个变量赋值,set只能一次对一个变量赋值。

 

全局变量只能用不能自己创建和赋值!!!

2、 创建不带参数存储过程

输出语句

–创建存储过程
if (exists (select * from sys.objects where name =
‘proc_get_student’))
drop proc proc_get_student
go
create proc proc_get_student
as
select * from student;

print 和select

–调用、执行存储过程
exec proc_get_student;

use MySchool

 

go

3、 修改存储过程

select * from StuInfos

–修改存储过程
alter proc proc_get_student
as
select * from student;

1.班级表   班级编号 (主键)   班级名   (长度固定3位)   班级人数 (默认30)

 

  if exists(select * from sysobjects where name=’Classes’)

4、 带参存储过程

drop table Classes

–带参存储过程
if (object_id(‘proc_find_stu’, ‘P’) is not null)
drop proc proc_find_stu
go
create proc proc_find_stu(@startId int, @endId int)
as
select * from student where id between @startId and @endId
go

  go  

exec proc_find_stu 2, 4;

 create table Classes  

 

 (   clsid int identity(1,1), –班级编号  

5、 带通配符参数存储过程

‘ clsname varchar(3), –班级名称   clsnums int –班级人数   )   

–带通配符参数存储过程
if (object_id(‘proc_findStudentByName’, ‘P’) is not null)
drop proc proc_findStudentByName
go
create proc proc_findStudentByName(@name varchar(20) = ‘%j%’, @nextName
varchar(20) = ‘%’)
as
select * from student where name like @name and name like @nextName;
go

go   

exec proc_findStudentByName;
exec proc_findStudentByName ‘%o%’, ‘t%’;

‘alter table Classes

 

add constraint pk_clsid_Classes   primary key(clsid)  

6、 带输出参数存储过程

 alter table Classes

if (object_id(‘proc_getStudentRecord’, ‘P’) is not null)
drop proc proc_getStudentRecord
go
create proc proc_getStudentRecord(
@id int, –默认输入参数
@name varchar(20) out, –输出参数
@age varchar(20) output–输入输出参数
)
as
select @name = name, @age = age from student where id = @id
go

add constraint ck_clsname_Classes   check(len(clsname)=3)  


declare @id int,
@name varchar(20),
@temp varchar(20);
set @id = 7;
set @temp = 1;
exec proc_getStudentRecord @id, @name out, @temp output;
select @name, @temp;
print @name + ‘#’ + @temp;

 alter table Classes

 

add constraint df_clsnums_Classes   default 30 for clsnums   

7、 不缓存存储过程

go  

–WITH RECOMPILE 不缓存
if (object_id(‘proc_temp’, ‘P’) is not null)
drop proc proc_temp
go
create proc proc_temp
with recompile
as
select * from student;
go

 insert into Classes select ‘t10’,25 union select ‘t11’,18 union select
‘t12’,23

exec proc_temp;

2.外键约束   为学生表添加一个clsid列来表示其班级   alter table StuInfos
add clsid int   外键约束   

 

alter table StuInfos with nocheck –不对表现有的数据作限制操作

8、 加密存储过程

add constraint fk_StuInfos_Classes    foreign key(clsid) references
Classes(clsid)   

–加密WITH ENCRYPTION
if (object_id(‘proc_temp_encryption’, ‘P’) is not null)
drop proc proc_temp_encryption
go
create proc proc_temp_encryption
with encryption
as
select * from student;
go

删除约束   

exec proc_temp_encryption;
exec sp_helptext ‘proc_temp’;
exec sp_helptext ‘proc_temp_encryption’;

alter table StuInfos drop constraint fk_StuInfos_Classes

 

3.建表同时建约束,老师表   编号     (主键 标识列)   名称     (非空)

9、 带游标参数存储过程

性别     (男或女,默认男)   年龄     (12-60)   电话     (唯一)   班级编号
(外键)   

if (object_id(‘proc_cursor’, ‘P’) is not null)
drop proc proc_cursor
go
create proc proc_cursor
@cur cursor varying output
as
set @cur = cursor forward_only static for
select id, name, age from student;
open @cur;
go
–调用

if exists(select * from sysobjects where name=’TeaInfos’)

declare @exec_cur cursor;
declare @id int,
@name varchar(20),
@age int;
exec proc_cursor @cur = @exec_cur output;–调用存储过程
fetch next from @exec_cur into @id, @name, @age;
while(@@FETCH_STATUS = 0)
begin
print ‘id: ‘ + convert(varchar, @id) + ‘, name: ‘ + @name + ‘, age: ‘ +
convert(char, @age);
fetch next from @exec_cur into @id, @name, @age;
end
close @exec_cur;
deallocate @exec_cur;

drop table TeaInfos  

 

 go  

10、 分页存储过程

 create table TeaInfos  

—存储过程、row_number完成分页
if (object_id(‘proc_page’, ‘P’) is not null)
drop proc proc_page
go
create proc proc_page
@startIndex int,
@endIndex int
as
select count(*) from Student
select * from (
select row_number() over(order by StuId) as rowId, * from Student
) temp
where temp.rowId between @startIndex and @endIndex
go
exec proc_page 2, 4

 ( id int primary key identity(1,1), –编号

 

name varchar(10) not null, –名称

 

sex char(2) check(sex=’男’ or sex=’女’) default ‘男’, –性别

 

age int check(age>=12 and age<=60), –年龄

 

tel char(11) unique, –电话

 

clsid int foreign key references Classes(clsid) –班级   )

本文转自: http://www.cnblogs.com/hoojo/archive/2011/07/19/2110862.html

  go

 

一:操作变量 –1– 声明变量@temp值为1并打印出来 声明变量关键字:

declare   declare @temp int –声明   

set @temp=1       –赋值  

 print @temp       –输出   

  declare @temp int=1 –声明的同时赋值  

 print @temp         –输出   

 

赋值方式一:用set 输出方式一:用print   declare @temp int –声明   

select @temp=1 –赋值   select @temp   –输出

赋值方式二:用select 输出方式二:用select —

2– 声明三个变量存储’姓名、性别、年龄’,再打印信息,格式如下:
姓名:杰达姆,性别:男,年龄:18 –声明

declare @name varchar(10),         @sex char(2),         @age int

–赋值 select @name=’杰达姆’,@sex=’男’,@age=18

–输出 print  ‘姓名:’+@name+’,性别:’+@sex+’,年龄:’   +cast(@age as
varchar)   

–convert(varchar,@age)

–两个转型函数:   1.convert — 语法:  convert(类型,数据)   2.cast   

— 语法:  case(数据 as 类型)

–3– select * from StuInfo

打印张秋丽的信息(来自于student数据库的数据),格式如下: 姓名:张秋丽
性别:男 年龄:18

–声明 declare  @name varchar(10)=’张秋丽’, @sex char(2), @age int

–赋值:来自于表内 select @sex=stuSex,@age=stuAge from stuinfo  where
stuName=@name

–输出 print ‘姓名:’+@name print ‘性别:’+@sex print ‘年龄:’+cast(@age as
varchar)

–4– 查询李文才的左右同桌的信息 declare @seat int select @seat=stuSeat
from stuinfo where stuname=’李文才’ select * from stuinfo  where
stuSeat=@seat-1 or stuSeat=@seat+1

–5– 查询比张秋丽大10岁以上的学生信息 declare @age int  select
@age=stuAge from stuinfo where stuName=’张秋丽’ select * from stuinfo
where stuAge>=10+@age 

总结: set和select的区别  

 1. set一次只能为一个变量赋值,而select能同时为多个变量赋值  

 2. set只能赋一个固定的值,而select能动态的赋值(值来自于表内)
select的作用  

 1. 查询SQL语句,如: select * from 表名