过程化SQL

过程化sql的基本结构

1. 基本结构是块（Block）

1 declare 2 ...

3 /*定义部分*/ 4 ...

5 //执⾏部分 6 begin

7 sql语句、过程化SQL的流程控制语句 8 exception

9 异常处理部分10 end;

2. 增加了变量、常量等定义语句

变量定义

变量名 数据类型 ：= 初值表达式a float := 0;b int not null;常量定义

常量名 数据类型 constant := 常量表达式errorMsg string constant := \"nested sql error\";

3. 增加了变量赋值语句

变量名称 ：= 表达式set 变量名称 = 表达式set sno = sno +1;

4. 增加了流程控制语句

条件控制语句

IF condition THENSequence_of_statements;END IF;

IF condition THENSequence_of_statements1;ELSE Sequence_of_statements2;END IF;循环控制语句1. 简单循环loopLOOP

Sequence_of_statements;END LOOP;

2. while - loop

WHILE condition LOOPSequence_of_statements;END LOOP;

每次执⾏循环都要判断while条件，注意和if不⼀样3. for - loop

FOR count IN [REVERSE] bound1 … bound2 LOOPSequence_of_statements;END LOOP;错误处理

存储过程

存储过程类似于函数块分为命名块和匿名块

命名块可以被反复调⽤，保存在数据库中，所以过程和函数就是命名块。存储过程的创建1. 创建存储过程

create procedure 过程名（[参数1，参数2，...]） AS<过程化sql块>;

例⼦：从账户1转指定数额的款项到账户2中

1 create procedure

2 transfer(inAccount int,outAccount int,amount float)

3 /*定义存储过程transfer，其参数为转⼊账户、转出账户、转账额度(数据类型有待商榷)*/ 4 as declear /*定义变量*/ 5 totalDepositOut float; 6 totalDepositln float; 7 inAccountnum int;

8 begin /*当账户为outAccount时，检测转出账户的余额total*/ 9 select total into totalDepositOut from account10 where accountnum = outAccount;11 if totalDepositOut is null then

12 /*如果转出账户不存在或账户中没有存款*/13 rollback;/*回滚事务*/14 return;15 end if;

16 if totalDepositOut < amount then17 rolback;18 return;19 end if;20

21 select Accountnum into inAccountnum from account22 where accountnum = inAccount;

23 if inAccountnum is null then/*如果转⼊账户不存在*/24 rollback;25 return;26 end if;27

28 update account set total = total - amount

29 where accountnum = outAccount;/*修改转出账户余额，减去转出额*/30

31 update account set total = total +amount

32 where accountnum = inAccount;/*修改转⼊余额，增加转⼊额*/33

34 commit; /*提交转账事物*/35 36 end;

在sqlserver上的代码：

1 create database ZYY_ACCOUNT; 2 3 create TABLE account ( 4 accountnum int primary key, 5 total float 6 );

7 insert into account

8 values(01003813828,10000),(01003815868,10000); 9 select * from account;10 11 drop proc transfer12 go

13 create proc transfer(@accountin int,@accountout int, @money float)14 as15 begin

16 /*定义变量*/

17 declare @totalDepositOut float;18 declare @totalDepositln float;19 declare @inAccountnum int;

20 /*当账户为outAccount时，检测转出账户的余额total*/

21 set @totalDepositOut = (select total from account where accountnum = @accountout);22 if @totalDepositOut is null 23 return;

24 if @totalDepositOut < @money 25 return;

26 /*检测转⼊账户是否存在*/

27 set @inAccountnum = (select accountnum from account where accountnum = @accountin)28 if @inAccountnum is null 29 return;30 31 update account set total = total - @money

32 where accountnum = @accountout;/*修改转出账户余额，减去转出额*/33 update account set total = total +@money

34 where accountnum = @accountin;/*修改转⼊余额，增加转⼊额*/ 35 end36 go37 38 exec transfer 1003815868,1003813828,10000;39 40 select *from account;

带有游标的存储过程！

1 /*1.统计离散数学的成绩分布情况，即按照各分段统计⼈数*/ 2 --插⼊统计数据

3 insert into Student values

4 ('202090001','tom_1','男',20,'IS'), 5 ('202090002','tom_2','男',20,'IS'), 6 ('202090003','tom_3','男',20,'IS'), 7 ('202090004','tom_4','⼥',20,'IS'), 8 ('202090005','tom_5','⼥',20,'IS'), 9 ('202090006','tom_6','⼥',20,'IS');

10 insert into Course values(8,'离散数学',null,null);11 insert into SC values12 ('202090001',8,40),13 ('202090002',8,50),14 ('202090003',8,60),15 ('202090004',8,70),16 ('202090005',8,80),17 ('202090006',8,90);

18 --创建⼀个表⽤来存放成绩分布情况

19 create table GradeTJ(scoreLow60 int,scoreBetween60And80 int,scoreUp80 int);20 --创建⼀个存储过程21 go

22 create proc TongJi(@courseNumber int)23 as

24 declare @container int;

25 --因为返回值不唯⼀，需要⽤到游标

26 declare my_cursor cursor for select Grade from SC where Cno = @courseNumber;27 declare @count1 int;set @count1 = 0;28 declare @count2 int;set @count2 = 0;29 declare @count3 int;set @count3 = 0;30 begin

31 open my_cursor;--打开游标

32 fetch my_cursor into @container;--获取my_cursor的下⼀条数据，赋值给@container33 while(@@FETCH_STATUS = 0)--@@fetch_status 指针：0 FETCH 语句成功;

34 --1 FETCH 语句失败或此⾏不在结果集中;2 被提取的⾏不存在。 35 begin

36 if @container < 60 set @count1 = @count1+1;

37 if (@container>=60 and @container <80) set @count2 = @count2+1;38 if (@container >= 80) set @count3 = @count3 + 1;

39 fetch my_cursor into @container;--没有这条语句就变成死循环了40 end

41 close my_cursor;--关闭游标

42 deallocate my_cursor;--释放游标引⽤

43 insert into GradeTJ values(@count1,@count2,@count3);44 end45 go

46 drop proc TongJi;

47 --delete from GradeTJ;48 exec TongJi 8;

2. 执⾏存储过程

call/perform procedure 过程名（[参数1，参数2，...]）;

从账户01003815868转10000元到01003813828账户中：

CALL PROCEDURE transfer(01003813828,01003815868,10000);

　　3. 修改存储过程

只能修改名字，alter关键字4. 删除存储过程

drop procedure 过程名（）;

函数

函数和和过程不同的是函数必须指定返回的类型

CREATE FUNCTION函数名 ([参数1，参数2,…]) RETURNS <类型> AS <过程化SQL块>;