db2存储过程

一. 使用存储过程的好处

  1. 减少客户机与服务器之间的网络使用率,以及数据库锁定保持的时间

      应用程序通常在执行每个SQL语句都要跨网络两次,存储过程可以将SQL语句集中在一起,

    从而使得对于每一组SQL语句只需要跨网络两次。存储过程中集中在一起的SQL语句越多,

    网络的使用率和数据库锁定保持的时间就越低。通过减少网络使用率和数据库锁定的时间长短,

    就可以提高网络的总体性能并减少锁定争用问题。

 

二. 用于存储过程的语言

  在DB2的"开发中心",可以用Java或SQL来创建存储过程。

 

三. SQL存储过程

  1. 使用SQL过程语言来编写存储过程具有下列优点:

    a. 可以通过在"开发中心"中使用集成调试器来调试SQL存储过程

    b. 借助SQL存储过程,可以调用其它SQL过程,最多可以嵌套16层调用

    c. SQL存储过程运行速度快,因为它是作为已编译的例程来运行的

  2. SQL存储过程具有大小和参数限制,这取决于正在运行的DB2版本:

    a. 对于DB2 Windows版和UNIX版,在版本7和版本8中,SQL存储过程的最大大小是64KB

 

四. Java存储过程

   1. 使用Java语言来编写存储过程具有下列优点:

    a. 在Java的安全性限制之内,可以使用Java存储过程进行文件的输入/输出。SQL存储过程不支持

      文件的输入/输出。

 

五. 存储过程的基本语法 

create procedure db2Inst.proce1(
    in "inParam" integer,
    out "outParam" varchar(10)
)
specific "proce1"
language sql
dynamic result sets 1
not deterministic
external action
modifies sql data
old savepoint level

begin
    L1: begin
        /*变量定义*/
        declare inum integer default 0;

        /*变量赋值*/
        set inum = 20;

        /*分支语句*/
        if 条件1 then
            ...
        elseif 条件2 then
            ...
        else 
            ...
        end if;

        /*多分支语句case*/
        case 变量名
            when 变量值1 then 
                ...
            when 变量值2 then 
                ...
            else 
                ...
        end case;

        /*for循环*/
        for 变量名 as 游标名或select 表达式
        do
            ...
        end for;

        /*while循环*/
        while 条件表达式 do
            ...
        end while;

        /*loop语句*/
        insLoop:
        loop
            ...
            leave insLoop;/*中断循环*/
            iterate insLoop;/*下一个循环*/
        end loop;

        /*游标的使用方式一*/
        /*定义游标*/
        declare 游标名 cursor for select 语句;
        /*打开游标*/
        open 游标名;
        /*取值*/
        fetch 游标名 into 变量列表;
        /*关闭游标*/
        close 游标名;

        /*goto语句*/
        goto fail;
            ...
        success: return 0
        fail: return -200
        
    end L1;

    /*游标的使用方式二*/
    /*游标的定义如果放在中间段,要用"begin...end;"段分割标志分割开*/
    L2: begin
        declare v_notfound integer default 0;
        declare stmt statement;/*声明放游标的值 */        
        declare cur cursor with return for stmt;/*声明动态游标存储变量*/
        declare continue handler for not found set v_notfound = 1;

        prepare stmt from qrySql;
        open cur;
        fetch cur into 变量列表;
        close cur;
    end L2;
    
    /*临时表*/
    L3: begin
        /*定义临时表*/
        declare global temporary table session.tableName(
                columnName integer
        )
        not logged with replace;/*不记录日志,没有则替换*/
        /*操作临时表*/
        insert into session.tableName values(columnValue);
    end L3;

    /*取得行号*/
    select rownumber() over() as rownum from tableName;

    /*解决like后跟一个字段*/
    select * from table1 a
    left join table2 b on locate(a.columnName,b.columnName)>0;

end
View Code


六. 存储过程属性说明

procedureName:存储过程的名字,在同一个数据库的同一模式下,不能存在
    存储过程名相同,参数数目相同的存储过程,即使参数的类型不同也不行
    
(in|out|inout paramName dataType, ...):传入参数
    in:输入参数,out:输出参数,inout:作为输入输出参数
    dataType:参数类型,可以接收SQL类型和创建的表,不支持long varchar,
        long vargraphic,datalink,reference和用户自定义类型。

specific specificName:唯一的特定名称(别名),可以用存储过程名代替。
    用于给存储过程添加注释用,但不能调用存储过程。如果不指定,则数据库
    会自动生成一个yymmddhhmmsshhn时间戳的名字。

language sql:指定过程的主体用的是SQL语言

dynamic result sets integer:指定存储过程返回结果的最大数量,若小于实际返回数量,则db2返回警告

deterministic or not deterministic:表示存储过程是动态或者非动态的。
    动态的返回的值是不确定的,非动态每次返回值都是相同的

external action or no external action:表示存储过程是否执行改变数据库状态的活动,
    而不通过数据库管理器。默认是external action。如果指定为no external action,
    则数据库会确定最佳优化方案。

contains sql, reads sql data, modifies sql data:指定存储过程中的SQL访问级别
    contains sql:表示存储过程可以执行中,既不可读取SQL数据,也不可修改SQL数据。
    reads sql data:表示存储过程可以执行中,可读取SQL,但不可修改SQL数据。
    modifies sql data:表示存储过程可以执行任何SQL语句。可以对数据库中的数据进行
        增加、删除和修改。
    
old savepoint level or new savepoint level:建立存储点(存储某个时候的数据),
    old savepoint level是默认的存储点

called on null input:表示可以调用存储过程而不管任何的输入参数是否为NULL,并且,
    任何的out或者inout参数可以返回一个NULL或者非空值。检验参数是否为NULL是在过程中进行的。

inherit special registers:表示继承专用寄存器

parameter ccsid:指定所有输出字符串数据的编码,默认为unicode编码数据库为:parameter ccsid unicode,
    其他的数据库默认为:parameter ccsid 3 ascii
View Code

 

七. 存储过程调用另一存储过程,返回结果集

  1. 存储过程1:返回2个结果集

create procedure db2Inst.proc1()
language sql
result sets 2

P1:begin
    declare c1 cursor with return to caller for select语句;
    declare c2 cursor with return to caller for select语句;

    open c1;
    open c2;

    ...
end P1
View Code

  2. 存储过程2:调用存储过程1,取得2个结果集

create procedure db2Inst.proc2()
language sql

P1:begin
    /*建立一个结果集数组*/
    declare loc1,loc2 result_set_locator varying;
    /*调用存储过程1,返回结果集*/
    call proc1;
    /*将返回结果集和结果集数组关联*/
    associate result set locator(loc1,loc2) with procedure proc1;
    /*将结果集数组进行分配*/
    allocate cursor1 cursor for result set loc1;
    allocate cursor2 cursor for result set loc2;

    /*直接从结果集中取值*/
    fetch cursor1 into 变量列表;
    close cursor1;
    
    ...
end P1
View Code

 

八. 结果集的返回类型  

create procedure db2Inst.proc3()
language sql
dynamic result sets 2
reads sql data

begin
    declare rs1 cursor with return to client for select语句;
    declare rs2 cursor with return to caller for select语句;
    open rs1;
    open rs2;
end
View Code

  1. with return to client:表示由发出最初call语句的客户应用接收结果集,即使结果集由嵌套层次中的

    15层深的嵌套存储过程发出也是如此。

  2. with return to caller:表示由存储过程的调用者接收结果集,而不考虑调用者是否是另一个存储过程,

    还是客户应用。

 

九. 异常处理

  1. 声明异常处理器的语法如下,它会位于变量声明和游标声明之后:

    declare handlerType handler for condition

  2. handlerType异常处理器类型有以下几种:

    a. continue:在处理器操作完成之后,会继续执行产生这个异常语句之后的下一条语句

    b. exit:在处理器操作完成之后,存储过程会终止,并将控制返回给调用者

    c. undo:在处理器操作执行之前,DB2会回滚存储过程中执行的SQL操作。在处理器操作

      完成之后,存储过程会终止,并将控制返回给调用者。

  3. 异常的种类(condition)

    异常处理器可以处理基于特定SQLSTATE值的定制异常,或者处理预定义异常的类。

    预定义的3种异常:

      a. not found:标识导致SQLCODE值为+100或者SQLSTATE值为0200的异常。

        这个异常通常在select没有返回行的时候出现。

      b. sqlexception:标识导致SQLCODE值为负的异常

      c. sqlwarning:标识导致警告异常或者导致+100以外的SQLCODE正值的异常

  4. 异常处理器示例

    declare exit handler for sqlexception, sqlwarning set stmt = "aborted";

    declare undo handler for not found;

posted on 2016-07-04 17:37  大饼酥  阅读(5663)  评论(0编辑  收藏  举报

导航