过程化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 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;

   • 错误处理

存储过程

存储过程类似于函数

块分为命名块和匿名块

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

存储过程的创建

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

    

   
    ?

    

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块>;

过程化SQL

原文：https://www.cnblogs.com/zyyComeOn/p/14398241.html