过程化SQL

 

过程化sql的基本结构

1. 基本结构是块（Block）

    declare 
    ...
    /*定义部分*/
    ...
    //执行部分
    begin
        sql语句、过程化SQL的流程控制语句
    exception
        异常处理部分
    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中

    create procedure
    transfer(inAccount int,outAccount int,amount float)
    /*定义存储过程transfer，其参数为转入账户、转出账户、转账额度(数据类型有待商榷)*/
    as declear  /*定义变量*/
        totalDepositOut float;
        totalDepositln float;
        inAccountnum int;
    begin  /*当账户为outAccount时，检测转出账户的余额total*/
        select total into totalDepositOut from account
        where accountnum = outAccount;
        if totalDepositOut is null then
        /*如果转出账户不存在或账户中没有存款*/
            rollback;/*回滚事务*/
            return;
        end if;
        if totalDepositOut < amount then
            rolback;
            return;
        end if;
        
        select Accountnum into inAccountnum from account
        where accountnum = inAccount;
        if inAccountnum is null then/*如果转入账户不存在*/
            rollback;
            return;
        end if;
        
        update account set total = total - amount
        where accountnum = outAccount;/*修改转出账户余额，减去转出额*/
        
        update account set total = total +amount
        where accountnum = inAccount;/*修改转入余额，增加转入额*/
        
        commit; /*提交转账事物*/
        
    end;

    

   在sqlserver上的代码：

    create database ZYY_ACCOUNT;
    ​
    create TABLE account (
    accountnum int primary key,
    total float
    );
    insert into account
    values(01003813828,10000),(01003815868,10000);
    select * from account;
    ​
    drop proc transfer
    go
    create proc transfer(@accountin int,@accountout int, @money float)
    as
    begin
        /*定义变量*/
        declare @totalDepositOut float;
        declare @totalDepositln float;
        declare @inAccountnum int;
        /*当账户为outAccount时，检测转出账户的余额total*/
        set @totalDepositOut = (select total from account where accountnum = @accountout);
        if @totalDepositOut is null 
        return;
        if @totalDepositOut < @money    
        return;
        /*检测转入账户是否存在*/
        set @inAccountnum = (select accountnum from account where accountnum = @accountin)
        if @inAccountnum is null    
        return;
    ​
        update account set total = total - @money
        where accountnum = @accountout;/*修改转出账户余额，减去转出额*/
        update account set total = total +@money
        where accountnum = @accountin;/*修改转入余额，增加转入额*/  
    end
    go
    ​
    exec transfer 1003815868,1003813828,10000;
    ​
    select *from account;

    

   带有游标的存储过程！

    /*1.统计离散数学的成绩分布情况，即按照各分段统计人数*/
    --插入统计数据
    insert into Student values
    ('202090001','tom_1','男',20,'IS'),
    ('202090002','tom_2','男',20,'IS'),
    ('202090003','tom_3','男',20,'IS'),
    ('202090004','tom_4','女',20,'IS'),
    ('202090005','tom_5','女',20,'IS'),
    ('202090006','tom_6','女',20,'IS');
    insert into Course values(8,'离散数学',null,null);
    insert into SC values
    ('202090001',8,40),
    ('202090002',8,50),
    ('202090003',8,60),
    ('202090004',8,70),
    ('202090005',8,80),
    ('202090006',8,90);
    --创建一个表用来存放成绩分布情况
    create table GradeTJ(scoreLow60 int,scoreBetween60And80 int,scoreUp80 int);
    --创建一个存储过程
    go
    create proc TongJi(@courseNumber int)
    as
    declare @container int;
    --因为返回值不唯一，需要用到游标
    declare my_cursor cursor for select Grade from SC where Cno = @courseNumber;
    declare @count1 int;set @count1 = 0;
    declare @count2 int;set @count2 = 0;
    declare @count3 int;set @count3 = 0;
    begin 
    open my_cursor;--打开游标
    fetch  my_cursor into  @container;--获取my_cursor的下一条数据，赋值给@container
    while(@@FETCH_STATUS = 0)--@@fetch_status 指针：0   FETCH   语句成功;
                             --1   FETCH   语句失败或此行不在结果集中;2   被提取的行不存在。 
    begin
    if @container < 60  set @count1 = @count1+1;
    if (@container>=60 and @container <80) set @count2 = @count2+1;
    if (@container >= 80) set @count3 = @count3 + 1;
    fetch my_cursor into  @container;--没有这条语句就变成死循环了
    end
    close my_cursor;--关闭游标
    deallocate my_cursor;--释放游标引用
    insert into GradeTJ values(@count1,@count2,@count3);
    end
    go
    drop proc TongJi;
    --delete from GradeTJ;
    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块>;