工作需要首次接触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;