初识DB2存储过程基本语法

工作需要首次接触DB2存储过程（随笔）

存储案例分析语法知识

CREATE OR REPLACE PROCEDURE PRO_DAT_BATCH() --创建名为PRO_DAT_BATCH的存储过程
BEGIN										--存储过程开始标志
  DECLARE sqlcode INTEGER DEFAULT 0; 		--sqlcode 为 存储过程代码号，默认为0，出现异常时sqlcode发生改变，不同的异常代码号不同。
  DECLARE code1 VARCHAR(50); 				--DECLARE 定义变量 code1 变量名  VARCHAR（50）变量类型
  DECLARE @dayBatchId varchar(50);
  DECLARE @exeBatchId varchar(50);
  DECLARE @proName varchar(64);
  DECLARE v_sql VARCHAR(1024);
  DECLARE v_count INTEGER;
  --EXIT会在执行异常后执行执行 FOR SQLEXCEPTION 后的语句或块而整个停止下来；CONTINUE选项会在异常后继续执行
  DECLARE EXIT HANDLER FOR SQLEXCEPTION code1 =0;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET code1 = to_char(sqlcode); 
  SET code1 = '0';							--给code1赋值为‘0’
  COMMIT;									--执行的语句有的成功有的不成功，加上类似于事务，全部执行成功或失败，不会造成数据混乱。
  UPDATE DAY_BATCH							--更新名为DAY_BATCH 的表， ID =@dayBatchId值的字段的sql—code为‘0’...
  SET SQL_CODE   = '0',
      BATCH_STS  = '1',
      START_DATE = CURRENT_TIMESTAMP
  WHERE ID = @dayBatchId;
  
    BEGIN 	--存储过程中的BeginEnd和其它语言中的花括号，本身没有事务作用，主要有两个作用
			--1、使语句结果清晰
			--2、语句块作用，比如在 if 后面使用。
    --声明游标：下面有游标说明
    DECLARE @dayBatchList CURSOR WITH HOLD FOR SELECT ID, PRO_NAME
                                               FROM DAY_BATCH
                                               WHERE STATUS = 1
                                                 AND EXE_SORT_NUM > 0
                                               ORDER BY EXE_SORT_NUM;
    --打开游标
    OPEN @dayBatchList;
    UPDATE DAY_BATCH SET REMARKS=RTRIM(CHAR(v_count)) WHERE ID = @dayBatchId;
    WHILE (v_count > 0 AND code1 = '0') DO --while循环语句  DO **执行代码**  END WHILE;
    FETCH @dayBatchList INTO @exeBatchId,@proName; --提取游标里的数据
    --执行存储过程
    SET v_sql = 'CALL ' || @proName;
    UPDATE DAY_BATCH SET PRO_NAME=@exeBatchId || ':' || v_sql WHERE ID = @dayBatchId;
    --动态sql
    PREPARE s1 FROM v_sql;
    EXECUTE s1; --执行
    SET v_count = v_count - 1;
    UPDATE DAY_BATCH SET REMARKS=REMARKS || ',' || v_sql WHERE ID = @dayBatchId;
    END WHILE; --结束WHILE
	CALL INSERT_BEACH_CONTROL_CABIN_AGE(); -- 调用名字为INSERT_BEACH_CONTROL_CABIN_AGE 的存储过程。
    COMMIT;
    CLOSE @dayBatchList; --关闭游标
    UPDATE DAY_BATCH SET REMARKS=REMARKS || ',' || RTRIM(CHAR(v_count)) WHERE ID = @dayBatchId;
  END; --存储过程结束
  ----小知识点
  ROLLBACK;--回滚
  SELECT DISTINCT FROM    DISTINCT--过滤重复的结果集
  CURRENT_TIMESTAMP --为当前时间
  --判断表A和表B是否满足on中的条件，满足就update 不满足就 insert 
  MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...) 
WHEN MATCHED THEN
	[UPDATE sql]
WHEN NOT MATCHED THEN
	[INSERT sql]
	  -- if exists的意思是“是否存在”，判断某个存储过程是否存在，如果存在就删除，如果不存在就创建。
  if (exists (select * from sys.objects where name = 'proc_get_student'))

游标概念：游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。

游标有两种类型：显式游标和隐式游标。在前述程序中用到的SELECT…INTO…查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。

游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。
言简意赅就是想要一条一条处理数据的时候就可以用游标。

游标的使用分成以下4个步骤：

1．声明游标
在DECLEAR部分按以下格式声明游标：

DECLARE 游标名 CURSOR WITH HOLD FOR SELECT语句;

参数是可选部分，所定义的参数可以出现在SELECT语句的WHERE子句中。如果定义了参数，则必须在打开游标时传递相应的实际参数。
SELECT语句是对表或视图的查询语句，甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。在SELECT语句中可以使用在定义游标之前定义的变量。
2．打开游标
在可执行部分，按以下格式打开游标：
OPEN 游标名;
打开游标时，SELECT语句的查询结果就被传送到了游标工作区。
3．提取数据
在可执行部分，按以下格式将游标工作区中的数据取到变量中。提取操作必须在打开游标之后进行。
FETCH 游标名 INTO 变量名1，变量名2…;

FETCH @dayBatchList INTO @exeBatchId,@proName;

或
FETCH 游标名 INTO 记录变量;
游标打开后有一个指针指向数据区，FETCH语句一次返回指针所指的一行数据，要返回多行需重复执行，可以使用循环语句来实现。控制循环可以通过判断游标的属性来进行。
下面对这两种格式进行说明：
第一种格式中的变量名是用来从游标中接收数据的变量，需要事先定义。变量的个数和类型应与SELECT语句中的字段变量的个数和类型一致。
第二种格式一次将一行数据取到记录变量中，需要使用%ROWTYPE事先定义记录变量，这种形式使用起来比较方便，不必分别定义和使用多个变量。
定义记录变量的方法如下：
变量名 表名|游标名%ROWTYPE；
其中的表必须存在，游标名也必须先定义。
4．关闭游标
CLOSE 游标名;
显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。

prepare动态sql：

declare v_sql       varchar(4000) default '';--动态sql  可以看到这里是声明了一个变量，字符串，默认值是空字符串
    --程序开始
    --表--
    if upper(v_lx) = 'T' then
      if exists(select 1 from sysibm.tables where table_schema = 'PAS' and table_name= ltrim(rtrim(replace(upper(v_ccmc),'PAS.','')))) then
        set v_sql='drop table '||v_ccmc;        
        prepare s1 from v_sql;          而在这里声明了这个变量是一个动态sql，然后就去执行了，不如不声明的话就有可能会出现把变量本身作为参数了
        execute s1;
      end if;
    end if;

动态sql：

假设我们声明两个个变量 declare v_sql varchar(4000) default ‘’;(动态sql语句,默认值为空字符串)，declare v_cmcc varchar(40);(传进来的值为jxdx_ckzh)

,给变量赋值set v_sql=‘drop table’||v_ccmc

那么我们用execute去执行这条sql 执行的语句就是 drop table jxdx_ckzh

不声明为动态sql的话:

直接用execute去执行v_sql ：excute v_sql

执行的就是 drop table v_cmcc 删除的就是这个表

对存储过程错误处理的例子：

有几种错误处理的声明形式： 
§ 如果任何错误（不是 NOT FOUND ） , 设置 l_error 为 1 后继续执行： 
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
SET l_error=1; 
§ 如果发生任何错误（不是 NOT FOUND）， 执行 ROLLBACK和产生一条错误消息后退出当前块或存储过程。 
DECLARE EXIT HANDLER FOR SQLEXCEPTION 
BEGIN 
ROLLBACK; 
SELECT 'Error occurred – terminating'; 
END; 
§ 如果 MySQL 1062错误 (重复的健值 )发生，执行 SELECT语句（向调用程序发一条消息）后继续执行 
DECLARE CONTINUE HANDER FOR 1062 
SELECT 'Duplicate key in index'; 
§ 如果 SQLSTATE 2300错误 (重复的健值 )发生，执行 SELECT语句（向调用程序发一条消息）后继续执行 
DECLARE CONTINUE HANDER FOR SQLSTATE '23000' 
SELECT 'Duplicate key in index'; 
§ 当游标或者 SQL 选择语句没有返回值时，设置 l_done=1 后继续执行 
DECLARE CONTINUE HANDLER FOR NOT 
FOUND 
SET l_done=1; 
§ 此例除了用 SQLSTATE 变量而不是命名条件以外，跟前一个例子一样 
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000 ' 
SET l_done=1; 
§ 此例除了用 MySQL 的错误码变量而不是命名条件或者 SQLSTATE 变量以外，跟前两个例子一样
DECLARE CONTINUE HANDLER FOR 1329 
SET l_done=1;