存储过程&函数（培训教程）

格式：waitfor {delay 'time'| time 'time'}

waitfor time '5:26'
print 'a'

WAITFOR DELAY '00:00:02'
select * from employees

WAITFOR DELAY '00:00:02'
SELECT EmployeeID FROM Northwind.dbo.Employees

-------------------
function int getNum()
{

}

--定义函数应该注意几点：
--1、function关键字
--2、函数的返回类型
--3、函数名
--4、函数的功能体要不要从外界获取参数

create function zz
(@cubeLength decimal(4,1),@cubeWidth decimal(4,1),@cubeHeight decimal(4,1))
returns decimal(4,1)
as
begin
 return(@cubeLength*@cubeWidth*@cubeHeight)
end

go
print dbo.zz(2,4,4)

--自定义函数
格式：
  create function 函数名 --定义函数名
 (形参表)   --从外界接受的形参
 returns type  --返回的类型
 as
 begin
  语句体  --函数功能实现
 end

--自定义函数的调用
 操作对象.函数名

create table a
(
 ano  int primary key,
 aname  varchar(10) not null,
 asex  varchar(2)
)
create table b
(
 bno int primary key,
 bscore  int
)

drop table a
drop table b

alter table b
add constraint pkbno foreign key(bno) references a(ano)

insert into a values(1001,'张三','男')
insert into a values(1002,'小张','女')
insert into a values(1003,'李四','男')

insert into b values(1001,80)
insert into b values(1002,76)
insert into b values(1003,50)

create function ab(@score int)
returns @scoreInfo Table
(
 ano  int ,
 aname  varchar(10),
 asex  varchar(2),
 bscore  int
)
as
begin
 insert @scoreInfo
 select a.ano,a.aname,a.asex,b.bscore
 from a,b
 where a.ano=b.bno and b.bscore>@score
 return
end

select * from ab(70)

--规则
 create rule ruleName
 as 参数 条件

 1、create rule mm
    as
    @score between 0 and 100

 2、create rule btwn
    as
    @btwn in('男','女')

 3、create rule lik
    as
    @lik like '张'

--约束。
主键约束，唯一性约束，外键约束，检查性约束，默认值约束
create table stu
(
 id int identity,
 stuId char(3) primary key,
 stuName varchar(20)
)

drop table stu
insert into stu values('101','张三')
insert into stu(stuName)values('李四')
select * from stu

create table stu
(
 id int identity,
 stuId char(3) unique,
 stuName varchar(20)
)

create table stu
(
 id int identity,
 stuId char(3),
 stuName varchar(20),
 stuSex char(2) check (stusex='男'or stusex='女'),
 --check (stusex in('男','女'))
 stuAge int
)
drop table stu
insert into stu values('101','张三','张')
select * from stu

create table stu
(
 id int identity,
 stuId char(3) unique,
 stuName varchar(20),
 stuClass varchar(50) default '计算机一班'
)
insert into stu(stuid,stuName) values('101','张三')
insert into stu values('102','张三','计算机二班')

create table class
(
 id int identity,
 className varchar(20) primary key
)
insert into class values('计算机一班')
insert into class values('计算机二班')

select * from class
create table stu
(
 id int identity,
 stuName varchar(20),
 stuClass varchar(20) foreign key references class(className)
)

insert into stu values('张三','计算机三班')

select * from stu

--绑定
 exec 规则名 表.绑定列

--使用存储过程的理由
 
 1、可以在单个存储过程中执行一系列sql语句
 2、可以实现存储过程的套用，简化程序设计的复杂性
 3、存储过程创建后就在服务器上编译，减少网络负担
 3、一致性比较好

--存储过程的分类
 1、用户自定义的存储过程
 2、系统存储过程
 3、临时存储过程
 4、远程存储过程
 5、扩展存储过程

--存储过程的创建
 存储过程的格式
 
 create proc proName
 (参数1[output]，参数2[output],...)
 as
 语句体

--ex1
 --在存储过程中实现查询
 create proc query
 as
 select stuNo from stu
 select stuName from stu

 drop proc query
 
 exec query
 
--ex2
 --在存储过程中创建关联表的查询 (注意在存储过程中不允许出现use命令)
 use master
 go
 create proc del
 as
 if exists(SELECT * FROM sysobjects WHERE name = 'b')
 begin
  drop table b
 end
 if exists(SELECT * FROM sysobjects WHERE name = 'a')
 begin
  drop table a
 end
 
 exec del

 
 drop proc del

--ex3
 --利用存储过程创建一个数据库
--数据库名：www.sina.com
--如果这个数据库名被使用，那么删除后再创建
create proc createDatabase
as
begin
 if exists (select * from master.dbo.sysdatabases where name='www.sina.com')
 begin
  drop database [www.sina.com]
 end

 create database [www.sina.com]
end

select * from stu

exec insertRecord '101','小李',20

 

 create proc createdata
 as
 create database zz

--ex4
 --利用存储过程创建一个数据表
 create proc createTable
 as
 create table mm
 (
  sno int primary key,
  sname varchar(20)
 )
 drop proc createTable

--ex5
 --利用存储过程实现数据的插入（带参数的存储过程）

 create table b
(
 bno int primary key,
 bscore  int
)

 create proc addData 
 (
  @bno int,
  @bscore int
 )
 as
 if exists(SELECT * FROM sysobjects WHERE name = 'b')
 begin
  if exists(select * from b where bno=@bno)
  begin
   print '表中已经存在此条记录'
  end
  else
  begin
   insert into b values(@bno,@bscore) 
  end
 end
 else
 begin
  print '数据库中没有这个表'
 end
 drop proc addData
 drop table b
 exec  addData 1004,20

 create table author
 (
  author_id int primary key,
  author_name varchar(8) not null,
  author_address varchar(50),
  author_telphone varchar(50)
 )

 create proc add_author
 (
  @author_id int,
  @author_name varchar(8),
  @address varchar(50)='无',
  @telphone varchar(50)='无'
 )
 as
 insert into author
 values(@author_id,@author_name,@address,@telphone)
 select * from author

 exec add_author 1002,'张三'
 drop proc add_author

--ex6
 --存储过程的使用（带有返回参数）

 create proc proName
 (
  @a int ,
  @b int ,
  @c int output
 )
 as
 select @c=@a+@b
 
 declare @var int
 exec proName 100,200,@var output
 select @var

--ex7
 --查找指定格式和条件的记录
create table book
(
 book_id int,
 book_name varchar(50),
 book_price float
)
insert into book values(1001,'三国',90)
insert into book values(1002,'日子',60)
insert into book values(1003,'月子',74)
create proc queryBook
(
 @book_id int,
 @book_name varchar(50) output,
 @book_price float output
)
as
select @book_name=book_name ,@book_price=book_price from book
where book_id=@book_id

declare @book_name varchar(50)
declare @book_price float
exec queryBook 1001,@book_name output,@book_price output
select '书名'=@book_name,'价格'=@book_price
 

--触发器

 触发器是特殊的存储过程，他在指定的表中的数据发生变化时自动生效。唤醒并调用
触发器以响应 insert ,update 或 delete 语句.

--触发器结构

create trigger triggerName

on Table | view

[with encryption]

FOR INSERT, UPDATE, DELETE

AS

语句体

--ex1
if exists (select * from sysobjects where name='t1')
begin
 drop trigger t1
end
go
create trigger t1
on score

for insert ,update ,delete
as
 select * from score
go

insert into score values(1004,60)

--插入表与删除表
 在除法器执行时，会产生两个临时表：inserted表和deleted表
 他们的结构和触发器所在的结构相同，sql server 2000自动创建和管理这些表
 
 在对具有触发器的表进行操作时，其操作过程如下：
 
 1.执行insert 操作，插入到触发器表中的新行被插入到inserted表中
 2.执行delete 操作，从触发器表中删除的行被插入到deleted表中
 3.执行update 操作，先从触发器表中删除旧行，然后再插入新行。其中被删除的旧行被插入到deleted表中
   插入的新行被插入到inserted表中

--ex2
if exists (select * from sysobjects where name='t2')
begin
 drop trigger t2
end
go
create trigger t2
on score
for insert ,delete,update
as
  select * from inserted
 select * from deleted
go

insert into score values(1002,40)

update score set score=50 where id=1002

--ex3
 insert 和update 触发器
 当向表中插入或者更新记录时，insert 或者update 触发器被执行。
 一般情况下，这两种触发器常用来检查插入或者修改后的数据是否满足要求。

if exists (select * from sysobjects where name='score')
begin
 drop table score
end

go

create table score
(
 id int,
 score int
)
go
create trigger t3
on score
for insert
as
declare @score int
select @score=score from inserted
if @score<0 or @score>100
begin
 rollback
 select '成绩必须在0到100之间'
end

insert into score values(1001,50)
select * from score
update score set score=200 where id=1001

create trigger t4
on score
for update
as
declare @score int
select @score=score from inserted
if @score<0 or @score>100
begin
 rollback
 print '成绩必须在0到100之间'
end

--ex4
 delete 触发器

 delete 触发器通常用于下面的情况

 1.防止那些确实要删除，但是可能会引起数据一致性问题的情况，一般是为那些用作其他表的外键记录
 
 2.用于级联删除操作

if exists (select * from sysobjects where name='stu')
begin
 drop table stu
end
go
create table stu
(
 id int primary key,
 name varchar(20)
)
go

if exists (select * from sysobjects where name='score')
begin
 drop table score
end
go

create table score
(
 id int primary key,
 score int
)
go

alter table score
add constraint pk_id foreign key(id) references stu(id)

insert into stu values(1001,'张三')
insert into stu values(1002,'李四')
insert into stu values(1003,'王五')
insert into stu values(1004,'杨六')
select * from stu

insert into score values(1001,90)
insert into score values(1002,78)
insert into score values(1003,56)
insert into score values(1004,78)
select * from score

create trigger t5
on stu
for delete
as
declare @id int
select @id=id from deleted
delete from score where id=@id
delete from stu where id=@id

--ex5
if exists (select * from sysobjects where name='class')
begin
 drop table class
end
go
create table class
(
 class_id int primary key ,
 name varchar(30),
 stuNum int
)

if exists (select * from sysobjects where name='student')
begin
 drop table student
end
go
create table student
(
 stu_id int primary key,
 stu_name varchar(30),
 stu_class_id int
)
insert into class values(101,'软工一班',6)
insert into class values(102,'软工二班',0)
insert into class values(103,'软工三班',0)
select * from class
insert into student values(1001,'小张',101)
insert into student values(1002,'小李',101)
insert into student values(1003,'小王',101)
insert into student values(1004,'张三',101)
insert into student values(1005,'张四',101)
insert into student values(1006,'张五',101)
select * from student

create trigger t6
on student
for delete
as
declare @class_id int
select @class_id=stu_class_id from deleted
update class set stuNum=stuNum-1 where class_id=@class_id

drop trigger t6

delete from student where stu_id=1002

create trigger t7
on student
for insert
as
declare @mm int
select @mm=stu_class_id from inserted
update class set stuNum=stuNum+1 where class_id=@mm
go

insert into student values(1007,'周润法',102)

//分页的存储过程
CREATE PROCEDURE proPage
  @pageid int ,
  @pagesize int
AS
 declare @sno1 char(4)
 declare @sno2 char(4)
if (@pageid=1)
begin
 set rowcount @pagesize
 select * from enterpriseBaseStationTable order by id
end
else
begin
 declare @int1 int
 select @int1=@pagesize*(@pageid-1)
 set rowcount @int1
 select  @sno1=id from enterpriseBaseStationTable order by id

 select @int1=@pagesize*@pageid
 set rowcount @int1
 select @sno2=id from enterpriseBaseStationTable order by id

 select * from enterpriseBaseStationTable where id>@sno1 and id<=@sno2
endGO