过程化SQL

2023-11-26 15:53:34

过程化sql的基本结构

基本结构是块（Block）

 1  declare 
 2  ...
 3  /*定义部分*/
 4  ...
 5  //执行部分
 6  begin
 7      sql语句、过程化SQL的流程控制语句
 8  exception
 9      异常处理部分
10  end;

增加了变量、常量等定义语句
- 变量定义
  
  变量名数据类型：= 初值表达式
  
  a float := 0;
  
  b int not null;
- 常量定义
  
  常量名数据类型 constant := 常量表达式
  
  errorMsg string constant := "nested sql error";
增加了变量赋值语句
- 变量名称：= 表达式
- set 变量名称 = 表达式
  
  set sno = sno +1;
增加了流程控制语句
- 条件控制语句
  - IF condition THEN
    
    Sequence_of_statements;
    
    END IF;
  - IF condition THEN
    
    Sequence_of_statements1;
    
    ELSE Sequence_of_statements2;
    
    END IF;
- 循环控制语句
  1. 简单循环loop
    
    LOOP
    
    Sequence_of_statements;
    
    END LOOP;
  2. while - loop
    
    WHILE condition LOOP
    
    Sequence_of_statements;
    
    END LOOP;
    
    每次执行循环都要判断while条件，注意和if不一样
  3. for - loop
    
    FOR count IN [REVERSE] bound1 … bound2 LOOP
    
    Sequence_of_statements;
    
    END LOOP;
- 错误处理

存储过程

存储过程类似于函数

块分为命名块和匿名块

命名块可以被反复调用，保存在数据库中，所以过程和函数就是命名块。

存储过程的创建

创建存储过程

 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 account
10      where accountnum = outAccount;
11      if totalDepositOut is null then
12      /*如果转出账户不存在或账户中没有存款*/
13          rollback;/*回滚事务*/
14          return;
15      end if;
16      if totalDepositOut < amount then
17          rolback;
18          return;
19      end if;
20      
21      select Accountnum into inAccountnum from account
22      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 transfer
12  go
13  create proc transfer(@accountin int,@accountout int, @money float)
14  as
15  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  end
36  go
37  ?
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 values
12  (‘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的下一条数据，赋值给@container
33  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  end
45  go
46  drop proc TongJi;
47  --delete from GradeTJ;
48  exec TongJi 8;

执行存储过程

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

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

 CALL PROCEDURE transfer(01003813828,01003815868,10000);

修改存储过程

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

drop procedure 过程名（）;

函数

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

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

过程化SQL

码农公寓

过程化sql的基本结构

存储过程

存储过程的创建

函数

相关文章