DB2 存储过程

一、什么是存储过程

受 DB2 服务器控制的一段可执行程序

可以通过SQL的CALL语句来完成对存储过程的调用

在存储过程中可以包含业务逻辑

存储过程可以在本地或远程进行调用

存储过程可以接收或传递参数,生成结果集

二、存储过程特征

包含使用sql语句的过程构造

存储在数据库中且在db2 服务器上运行;

可以由正在使用的sql的应用程序根据名称来调用;

允许应用程序分2部分允许,在客户机上运行应用程序,在服务器上运行存储过程

存储过程在应用程序中的优势

减少了客户机与服务器直接的网络使用率

增强了硬件和软件功能

提高了安全性

减少了开发成本并且提高了可靠性

集中处理了公共例程的安全性、管理和维护

通过sql pl 当前的语句集合和语言特性,可以用sql开发综合的、高级的程序

例如函数、存储过程和触发器。这样便可以将业务逻辑封装到易于维护的数据库对象中,从而提高数据库应用程序的性能。

SQL PL 支持本地和全局变量,包括声明和赋值,还支持条件语句和迭代语句、控制语句的转移、错误管理语句以及返回结果集的方法。

三、什么时候使用存储过程

使用存储过程的合适时机:

应用程序的性能无法满足预期时

客户端数量较多且应用程序中SQL代码分散时

应用程序需要进行繁重的数据库操作,同时这些操作并不需要进行太多的客户交互

应用程序代码更改频繁

需要对客户应用代码进行访问控制时

客户应用需要在一次操作中执行多条 SQL 语句

五、数据类型



db2查看连接存活时间 db2怎么看存储过程_db2查看连接存活时间


字符型:char varchar

日期型 date

数字型 number decilmal integer

详细请看屌丝大哥 db2数据类型介绍的那一课

六、Db2 存储过程基本语法

6.1 存储过程结构

CREATE OR REPLACE PROCEDURE <过程名>

( [ IN | OUT | INOUT ]  参数名  数据类型  默认值 )

LANGUAGE  SQL

BEGIN

      业务逻辑代码

END;

IN(输入参数)

只是将实参传递给存储过程,但在存储过程中不能对其进行修改。换句话说,对于存储过程而言它是只读的。

OUT(输出参数)

在存储过程结束时向调用者返回。一般在过程中都会被赋值。

INOUT(输入输出参数)

上述两种参数类型的结合体。它可以帮助调用者将实参传递给进程,另外它也能够作为输出参数被修改和赋值。

复合语句实例

复合语句是指包含在BEGIN和END间的语句。它一般包括如下语句类型:

声明语句

赋值语句

控制语句

条件处理语句


db2查看连接存活时间 db2怎么看存储过程_数据库_02




说明:

1. 复合语句可以嵌套使用。

2. BEGIN语句可以和标签组合使用,这样可以更清晰的标识语句块的范围。

6.2 变量声明与变量赋值

变量声明语法:

DECLARE 变量名  数据类型 初始值;

Delcare DiaoSiName varchar(20);

变量赋值语法 :set 变量名=值;

例如:给屌丝姓名变量赋值。

Set DiaoSiName = ‘奶娃’;

变量声明

DECLARE  my_var  INTEGER  DEFAULT  6;

条件声明

DECLARE  not_found  CONDITION  FOR  SQLSTATE  ‘02000’;

游标声明

DECLARE  c1  CURSOR  FOR  select  *  from  staff;

异常处理器声明

DECLARE  EXIT  HANDLER  FOR  SQLEXCEPTION  …;

语法

SET  lv_name  =  expression;
SET  lv_name  =  NULL;

示例

(1) SET  salary  =  salary  +  salary  *  0.1;
(2) SET  init_salary  =  NULL;
(3) SET  salary  =  (select  salary  from  employee  where empno  =  lv_emp_num);

注: 如果 SELECT 语句返回记录超过一行,示例 3 将会返回SQLERROR。

db2查看连接存活时间 db2怎么看存储过程_SQL_03





db2查看连接存活时间 db2怎么看存储过程_数据库_04



模块 - 规格说明(Module Specification)

模块可以发布type, SP, UDF以供外部使用。


CREATE  OR  REPLACE  MODULE  myMod; 
   
        

     ALTER  MODULE  myMod  PUBLISH 
   
    

                 TYPE  myRowTyp  AS  ANCHOR  ROW  myTab; 
   
        

     ALTER  MODULE  myMod  PUBLISH 
   
    

                 FUNCTION  myFunc(val1  ANCHOR  myTab.col1) 
   
    

                     RETURNS myRowTyp; 
   
        

     ALTER  MODULE  myMod  PUBLISH 
   
    

                 PROCEDURE  myProc(OUT  param1  ANCHOR  myTab.col2);


模块 - 实现(Module Implementation)


下面的代码是模块的实现部分:

ALTER  MODULE  myMod  ADD  VARIABLE  pkgVar  ANCHOR  myTab.col1; 
  
      

    ALTER  MODULE  myMod  ADD  FUNCTION   
    myFunc(val1  ANCHOR  myTab.col1)  RETURNS  myRowTyp 
  
   

    BEGIN 
  
   

        DECLARE  var1  myRowTyp; 
  
   

        SELECT  *  INTO  var1  FROM  myTab  WHERE  col1  <  val1  AND  col1  >  pkgVar; 
  
   
     RETURN  var1;   
   

    END 
  
      

    ALTER  MODULE  myMod  ADD  PROCEDURE  myProc(OUT  param1  ANCHOR  myTab.col2) 
  
   

    BEGIN 
  
   

        DECLARE  varRow  myRowTyp; 
  
   
     SET  param1  =  varRow.col2  –  pkgVar;   
   

    END

模块 - 其他语句

删除整个模块


DROP  MODULE  myMod;


保留规格说明内容,删除实现


ALTER  MODULE  myMod  DROP  BODY;


删除模块中的存储过程(SP)


ALTER MODULE myMod DROP PROCEDURE myProc;


将模块的执行权限赋给joe


GRANT EXECUTE ON MODULE myMod TO joe;

七、控制语句

IF语句


格式:


IF  条件1   THEN  statement1;


ELSEIF  条件2  THEN  statement2;


ELSE  statement3;


   END  IF;


注:条件成立时为TRUE (真),不成立时为FALSE(假) 和 NULL


IF语句例子


IF  rating  =  1  THEN 
     
      
        UPDATE  EMPLOYEE  SET  salary  =  salary*1.10      
      

            WHERE  empno  =  i_num;(如果满足于...时,薪水调整1.1倍) 
     
      

       ELSEIF  rating  =  2  THEN 
     
      
        UPDATE  EMPLOYEE  SET  salary  =  salary*1.05      
      

            WHERE  empno  =  i_num; 
     
      

       ELSE 
     
      
        UPDATE  EMPLOYEE  SET  salary  =  salary*1.03      
      

            WHERE  empno  =  i_num; 
     
      

       END  IF;

CASE语句(1 of 2)

简单CASE语句


db2查看连接存活时间 db2怎么看存储过程_SQL_05


稍加变形的CASE语句


db2查看连接存活时间 db2怎么看存储过程_数据库_06



LOOP语句

语法


[LABEL]  LOOP 
         
          

               SQL-procedure-statements; 
         
          
                END  LOOP  [LABEL];


示例


fetch_loop:  LOOP 
         
          
            FETCH  c1  INTO  v_firstname,  v_lastname;          
          

                SET  counter  =  counter  +  1; 
         
          

                IF  counter  =  51  THEN 
         
          

                     LEAVE  fetch_loop; 
         
          

                END  IF; 
         
          

           END  LOOP  fetch_loop;


FOR语句


语法


[LABEL]  FOR  for-loop-name  AS  [cursor-name  CURSOR  FOR] 
        
         

                select-statement   
        
         

          DO 
        
         

                SQL-procedure-statements; 
        
         
               END  FOR  [LABEL];         
         

          示例 
        
         

          DECLARE  fullname  CHAR(40); 
        
         

          FOR  v1  AS  c1  CURSOR  FOR  SELECT  firstnme,  midinit,  lastname  FROM  employee 
        
         

          DO 
        
         

                       SET  fullname=lastname||‘,’||firstnme||’,’||midinit; 
        
         

              INSERT  INTO  tname  VALUE  (fullname); 
        
         

          END  FOR;