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%’;

创建变量语法如下是

 

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

 2. 赋值操作,   如: select 变量名=值  

 3. 输出操作,   如: select 变量名 二:控制流程结构:if,else

–1– 声明变量number,并赋值,然后判断是偶数还是奇数,结果如下:
当前值为11,它是一个奇数 declare @number int set @number=12
if(@number%2=0) print ‘该数为偶数’ else print ‘该数为奇数’ –

-2– 根据输入的年龄显示人的生长阶段
未成年人<18,青年人<30,成年人<60,老年人<100,超人>=100

declare @age int set @age=21

if(@age<18) print ‘未成年人’ else if(@age<30)

print ‘青年人’

else if(@age<60)

print ‘成年人’

else if(@age<100)

print ‘老年人’

else print ‘超人’

select * from StuInfo select * from StuExam

–3– 统计笔试平均分,如果平均分超过70分打印’优秀’
同时显示前三名学员的考试信息否则      打印’较差’显示后三名学生信息

declare @avgScore int select @avgScore=AVG(writtenExam) from StuExam
if(@avgScore>=70) begin print ‘本班成绩优秀’

select top 3 * from StuExam order by writtenExam desc end else begin
print ‘本班成绩较差’ select top 3 * from StuExam order by writtenExam
end

三:循环结构 –1– 输出九九次’我爱你’ declare @i int=1  while(@i<=99)
begin print ‘第’+convert(varchar,@i)+’我爱你’ set @i+=1 end

–2– 计算1-100的累加和,结果格式:1-100的累加和为:xxx declare  @i int=1,
@sum int=0 while(@i<=100) begin set @sum+=@i set @i+=1 end print
‘1-100的累加和为:’+convert(varchar,@sum)

–3– 不停的提高学生笔试成绩2分,让所有学生的笔试成绩都及格

declare @count int –用来记录不及格的人数 while(1=1) begin
–计算不及格的人数 select @count=COUNT(*) from StuExam   where
writtenExam<60 –判断 if(@count=0)   break –退出死循环 else   update
StuExam set writtenExam+=2 end select * from StuExam

四:多分支语句

–1– 请声明变量@name表示学生姓名,对该学生年龄进行划等级 具体如下:
12岁前显示:’小学生’ 12-17显示’初中生’  18-22显示’高中生’
23-28显示’大学生’ 28以上’超人’ 结果格式:xxx是一个xxx

declare  @name varchar(20)=’小强’, @age int=23, @result varchar(10)

–多分支 set @result=case     when @age<12 then ‘小学生’     when
@age<17 then ‘初中生’     when @age<22 then ‘高中生’     when
@age<28 then ‘大学生’     else ‘超人’    end

–输出 print @name+’是一个’+@result

–2– 显示学号、笔试成绩、等级,数据如下:  笔试成绩:90以上的–A等     
   80以上的– B等         70以上的– C等         60以上的– D等     
   60以下的– E等 stuNo   writtenExam   grade s25303  60            D等
s25302  40            E等 s25301  77            C等 s25318  45          
 E等 select  stuNo, writtenExam, grade=case    when writtenExam>=90
then ‘A等’    when writtenExam>=80 then ‘B等’    when
writtenExam>=70 then ‘C等’    when writtenExam>=60 then ‘D等’  
 else ‘E等’     end from StuExam –3–
请根据平均分和下面的评分规则,编写T-SQL语句查询学员的成绩 优 :90分以上
   良 :80-89分     中 :70-79分     差 :60-69分     极差 :60分以下
        

select AVG(writtenExam),A=case      when Avg(writtenExam)>90 then
‘优’      when Avg(writtenExam)>80 and Avg(writtenExam)<89 then
‘良’      when Avg(writtenExam)>70 and Avg(writtenExam)<79 THEN
‘中’      when Avg(writtenExam)>60 and Avg(writtenExam)<69 then
‘差’      when Avg(writtenExam)<60 then ‘极差’      end      from
stuExam    

–4– 问题: 根据如下规则对机试成绩进行反复加分,     
 直到机试平均分超过85分为止       请编写T-SQL语句实现,注:(循环+多分支)
90分以上:  不加分 80-89分:   加1分 70-79分:   加2分 60-69分:   加3分
60分以下:  加5分 declare @a int   while(1=1) begin select
@a=AVG(labExam)from dbo.stuExam if(@a<85) begin update dbo.stuExam
set labExam =case          when labExam>=90 then  labExam        
 when labExam>=80 then labExam+1          when labExam>=70 then
labExam+2          when labExam>=60 then labExam+3          else     
    labExam+5         end end else break end select AVG(labExam) AS
机试成绩平均分 from dbo.stuExam

查询

子查询:查询中再查询,通常是以一个查询作为条件来供另一个查询使用
语法:
   select 列表 from 表名 
 where >(子查询)
注意:
 使用比较运算符时,要求子查询只能返回一条或空的记录!
示例:
 要求查出比我小的学生信息
 select * from student
 where studentno in
 (select studentno from student
 where studentname=’我’)

NOT IN:确定给定的值是否与子查询或列表中的值相匹配,如果不匹配则反回真。
使用方法:
 在需要子查询返回多数据时使用。
语法:
 select 列表 from 表名 
 where 列名 not in(子查询)
示例:
 查询未参加过考试的学生信息
 select  * from student 
 where studentno not in
 (select studentno from Result)

1.EXISTS 子查询
 EXISTS: exists 关键字能够检测数据是否存在,如果存在返回真。
语法
 if exists(子查询)
 语句
示例:
 查询本校学生有没有叫张三的
 if exists( select * from student
 where studentname=’张三’ )
 begin
 print ‘有叫张三的’
 end

  1. NOT EXISTS 子查询
     NOT EXISTS: exists 关键字能够检测数据是否存在,如果不存在返回真
    语法
     if not exists(子查询)
     语句
    示例:
     查询本校学生是不是没有叫张三的
     if not exists( select * from student
     where studentname=’张三’ )
     begin
     print ‘没有叫张三的’
     end

1:非相关子查询是独立于外部查询的子查询,子查询总共执行一次,执行完毕后将值传递给外部查询。
 2:相关子查询的执行依赖于外部查询的数据,外部查询执行一行,子查询就执行一次。数据是否存在,如果不存在返回真

示例:检索出在work表中每一个部门的最高基本工资的职工资料  select * from
work a where 基本工资=(select max(基本工资) from work b where
a.部门名称=b.部门名称)

 事物视图索引

语法

begin transaction

commit transaction

rollback transaction

视图

创建视图索引

if exists(select * from sysobjects where name=’视图名’)

drop view 视图名

create view  视图名

as

SQL语句

索引

if exists(select name from sysindex where name=’索引’)

drop index 表名。索引名

create 索引类型 index 索引名

on 表名(列名)

with fillfactor=30

GO

一 存储过程的概念

存储过程是在数据库管理系统保存的,预先编译的,能实现某种功能的SQL程序,它是数据库应用中运用比较广泛的一种数据对象。

为什么需要存储过程?

1.存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
2.当对数据库进行复杂操作时,可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
3.存储过程可以重复使用,可减少数据库开发人员的工作量。 4.安全性高,可设定只有某些用户才具有对指定存储过程的使用权

存储过程的优点:

1.模块化程序设计

2.执行速度块,效率高

3.减少网络流量

4.具有良好的安全性

 

二 系统存储过程

SQL_SERVER 提供系统存储过程,它们是一组预编译的T-SQL语句,系统存储过程提供了管理数据库和更新表的机制,并充当从系统表中检索信息的快捷方式。

常用的系统存储过程

系统存储过程

说明

sp_databases

列出服务上的所有数据库

sp_helpdb

报告有关指定数据库或所有数据库的信息

sp_renamedb

更改数据库的名称

sp_tables

返回当前环境下可查询的对象的列表

sp_columns

返回某个表列的信息

sp_help

返回某个表的所有信息

sp_helpconstraint

查看某个表的约束

sp_helpindex

查看某个表的索引

sp_stored_procedures

列出当前环境中的所有存储过程

sp_password

添加或修改登录账户的密码

 

三 用户自定义的存储过程

1.创建不带参数的存储过程

Create proc usp_selectstu

As

Select StudentName,Gender,GradeId,Phone from dbo.Student

 

调用存储过程:exec
usp_selectstu

2.创建带入参数的存储过程

Create proc usp_stuInfo @gradeid int=2       (默认)

As

Select * from student where gradeId=@gradeid

调用存储过程:exec usp_stuInfo
2

3.创建带出参数的存储过程

create proc usp_selectGrade @name nvarchar(10),@gradeid int output

As

Select @gradeid=gradeid from student where  studentname=@name

print @gradeid

 

调用存储过程:

declare @id int

exec usp_selectGrade ‘李小龙’,@id output 

 

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

Create proc usp_one  @name nvarchar(10)

as

select * from dbo.Student where StudentName like @name

 

exec usp_one ‘李%’

   5、 不缓存存储过程

缓存就是数据交换的缓冲区(称作Cache),当某一硬件要读取数据时,会首先从缓存中查找需要的数据,如果找到了则直接执行,找不到的话则从内存中找。由于缓存的运行速度比内存快得多,故缓存的作用就是帮助硬件更快地运行。

Sql
Server系统内存管理在没有配置内存最大值,很多时候我们会发现运行Sql
Server的系统内存往往居高不下。这是由于他对于内存使用的策略是有多少闲置的内存就占用多少,直到内存使用虑达到系统峰值时(预留内存根据系统默认预留使用为准,至少4M),才会清除一些缓存释放少量的内存为新的缓存腾出空间。

这些内存一般都是Sql Server运行时候用作缓存的,例如你运行一个select语句,
执行个存储过程,调用函数;

  1. 数据缓存:执行个查询语句,Sql Server会将相关的数据页(Sql
    Server操作的数据都是以页为单位的)加载到内存中来,下一次如果再次请求此页的数据的时候,就无需读取磁盘了,大大提高了速度。

2.执行命令缓存:在执行存储过程,自定函数时,Sql
Server需要先二进制编译再运行,编译后的结果也会缓存起来,
再次调用时就无需再次编译。

 

create proc proc_temp with recompile as     select * from student
exec proc_temp

 

6,加密存储过程

exec sp_helptext 储存过程名      可以查看储存过程代码

create proc proc_temp_encryption

with encryption

as

    select * from student;

go

–存储过程的内容不会被轻易看到(虽然解密也是有可能的)。

–应用这个,我们可以对某些关键的存储过程进行加密。

–但此时,存储过程仍然能被execute、alter和drop。

exec proc_temp_encryption;

exec sp_helptext ‘proc_temp’

exec sp_helptext ‘proc_temp_encryption’

(注意:加密存储过程前应该备份原始存储过程,且加密应该在部署到生产环境前完成。) 

 

一 存储过程的概念

存储过程是在数据库管理系统保存的,预先编译的,能实现某种功能的SQL程序,它是数据库应用中运用比较广泛的一种数据对象。

为什么需要存储过程?

1.存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
2.当对数据库进行复杂操作时,可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
3.存储过程可以重复使用,可减少数据库开发人员的工作量。 4.安全性高,可设定只有某些用户才具有对指定存储过程的使用权

存储过程的优点:

1.模块化程序设计

2.执行速度块,效率高

3.减少网络流量

4.具有良好的安全性

 

二 系统存储过程

SQL_SERVER 提供系统存储过程,它们是一组预编译的T-SQL语句,系统存储过程提供了管理数据库和更新表的机制,并充当从系统表中检索信息的快捷方式。

常用的系统存储过程

系统存储过程

说明

sp_databases

列出服务上的所有数据库

sp_helpdb

报告有关指定数据库或所有数据库的信息

sp_renamedb

更改数据库的名称

sp_tables

返回当前环境下可查询的对象的列表

sp_columns

返回某个表列的信息

sp_help

返回某个表的所有信息

sp_helpconstraint

查看某个表的约束

sp_helpindex

查看某个表的索引

sp_stored_procedures

列出当前环境中的所有存储过程

sp_password

添加或修改登录账户的密码

 

三 用户自定义的存储过程

1.创建不带参数的存储过程

Create proc usp_selectstu

As

Select StudentName,Gender,GradeId,Phone from dbo.Student

 

调用存储过程:exec
usp_selectstu

2.创建带入参数的存储过程

Create proc usp_stuInfo @gradeid int=2       (默认)

As

Select * from student where gradeId=@gradeid

调用存储过程:exec usp_stuInfo
2

3.创建带出参数的存储过程

create proc usp_selectGrade @name nvarchar(10),@gradeid int output

As

Select @gradeid=gradeid from student where  studentname=@name

print @gradeid

 

调用存储过程:

declare @id int

exec usp_selectGrade ‘李小龙’,@id output 

 

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

Create proc usp_one  @name nvarchar(10)

as

select * from dbo.Student where StudentName like @name

 

exec usp_one ‘李%’

   5、 不缓存存储过程

缓存就是数据交换的缓冲区(称作Cache),当某一硬件要读取数据时,会首先从缓存中查找需要的数据,如果找到了则直接执行,找不到的话则从内存中找。由于缓存的运行速度比内存快得多,故缓存的作用就是帮助硬件更快地运行。

Sql
Server系统内存管理在没有配置内存最大值,很多时候我们会发现运行Sql
Server的系统内存往往居高不下。这是由于他对于内存使用的策略是有多少闲置的内存就占用多少,直到内存使用虑达到系统峰值时(预留内存根据系统默认预留使用为准,至少4M),才会清除一些缓存释放少量的内存为新的缓存腾出空间。

这些内存一般都是Sql Server运行时候用作缓存的,例如你运行一个select语句,
执行个存储过程,调用函数;

  1. 数据缓存:执行个查询语句,Sql Server会将相关的数据页(Sql
    Server操作的数据都是以页为单位的)加载到内存中来,下一次如果再次请求此页的数据的时候,就无需读取磁盘了,大大提高了速度。

2.执行命令缓存:在执行存储过程,自定函数时,Sql
Server需要先二进制编译再运行,编译后的结果也会缓存起来,
再次调用时就无需再次编译。

 

create proc proc_temp with recompile as     select * from student
exec proc_temp

 

6,加密存储过程

exec sp_helptext 储存过程名      可以查看储存过程代码

create proc proc_temp_encryption

with encryption

as

    select * from student;

go

–存储过程的内容不会被轻易看到(虽然解密也是有可能的)。

–应用这个,我们可以对某些关键的存储过程进行加密。

–但此时,存储过程仍然能被execute、alter和drop。

exec proc_temp_encryption;

exec sp_helptext ‘proc_temp’

exec sp_helptext ‘proc_temp_encryption’

(注意:加密存储过程前应该备份原始存储过程,且加密应该在部署到生产环境前完成。)