DB2 存储过程 09

蓝色部分为转载格式标准

 

语法说明

1、procedure-name: 存储过程的名字，在同一个数据库的同一模式下，不能存在存储过程名相同参数数目相同的存储过程，即使参数的类型不同也不行。

 

2、(IN | OUT | INOUT parameter-name data-type,...) ：传入参数
    IN：输入参数
 OUT：输出参数
 INOUT：作为输入输出参数
    parameter-name：参数名字，在此存储过程中唯一的标识符。
 data-type:参数类型，可以接收SQL类型和创建的表。不支持LONG VARCHAR, LONG VARGRAPHIC, DATALINK, REFERENCE和用户自定义类型。

3、SPECIFIC specific-name：唯一的特定名称（别名），可以用存储过程名代替，这个特定名称用于dorp存储过程，或者给存储过程添加注视用，但不能调用存储过程。如果不指定，则数据库会自动生成一个yymmddhhmmsshhn时间戳的名字。推荐给出别名。

4、DYNAMIC RESULT SETS integer：指定存储过程返回结果的最大数量。存储过程中虽然没有return语句，但是却能返回结果集。

5、CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA： 指定存储过程中的SQL访问级别
    CONTAINS SQL： 表示存储过程可以执行中，既不可读取 SQL 数据，也不可修改 SQL 数据。
    READS SQL DATA： 表示存储过程可以执行中，可读取SQL，但不可修改 SQL 数据。
    MODIFIES SQL DATA： 表示存储过程可以执行任何 SQL 语句。可以对数据库中的数据进行增加、删除和修改。

 

6、DETERMINISTIC or NOT DETERMINISTIC：表示存储过程是动态或者非动态的。动态的返回的值是不确定的。非动态的存储过程每次执行返回的值是相同的。

7、CALLED ON NULL INPUT：表示可以调用存储过程而不管任何的输入参数是否为NULL，并且，任何的OUT或者INOUT参数可以返回一个NULL或者非空值。检验参数是否为NULL是在过程中进行的。

8、INHERIT SPECIAL REGISTERS：表示继承专用寄存器。

9、OLD SAVEPOINT LEVEL or NEW SAVEPOINT LEVEL：建立存储点。OLD SAVEPOINT LEVEL是默认的存储点。

10、LANGUAGE SQL：指定程序的主体用的是SQL语言。

11、EXTERNAL ACTION or NO EXTERNAL ACTION：表示存储过程是否执行一些改变理数据库状态的活动，而不通过数据库管理器管。默认是 EXTERNAL ACTION。如果指定为NO EXTERNAL ACTION ,则数据库会确定最最佳优化方案。

12、PARAMETER CCSID：指定所有输出字符串数据的编码,默认为UNICODE编码数据库为PARAMETER CCSID UNICODE ，其他的数据库默认为PARAMETER CCSID 3 ASCII。

13、SQL-procedure-body：存储过程的主体

示例：
-- 用存储过程导出 del 日志文件。
 

create or replace procedure pro_cz()
language sql
begin
declare i int default 1;
declare temp varchar(3000);
declare AA varchar(10);
set temp='export to c:\employee123456.del of  del  select * from employee1';
call sysproc.admin_cmd(temp);
END

 


 
存储过程返回结果集 游标的使用

 create procedure d()
  language sql
  result set 2 --（返回连个结果集）
  p1:begin
     declare c1 cursor with return to caller for  
     select id from trs;
   -- 指定改结果集的调用者
     declare c2 cursor with return to caller for
     select name from trs;
     open c1;
     open c2;
  end

 


 
存储过程，针对数据库 表结构的修改  第一种，没有加reorg ：
 

create or replace procedure  proce_alter
(schema varchar(300),
 tablename varchar(300),
 columnname varchar(200),
 type varchar(200),
 dataLength int,
 out state varchar(50))
language sql
begin atomic  
 declare v_sql varchar(200);
 declare undo handler for sqlexception  SET state='Error: Unexpected exit, need to check';
  if(dataLength=0) then
  set v_sql = 'alter table '|| schema||'.'||tablename||'  alter column '||columnname||' set data type  '||type;
  else
  set v_sql = 'alter table '|| schema||'.'||tablename||'  alter column '||columnname||' set data type  '||type|| '('||dataLength||')';
  end if;
  prepare s1 from v_sql;
 execute s1;
 set state = 'SUCCESS';
end

 


第二种 加上reorg 之后的 语句

create or replace procedure  proce_alter
(schema varchar(300),
 tablename varchar(300),
 columnname varchar(200),
 type varchar(200),
 dataLength int,
 out state varchar(60))
 --out v_sql varchar(200))
language sql
begin
 declare v_sql1 varchar(200);
 declare v_sql varchar(200);
 declare errormessage int default 0;
 declare continue handler for sqlexception  SET errormessage=1;
  if(dataLength=0) then
  set v_sql = 'alter table '|| schema||'.'||tablename||'  alter column '||columnname||' set data type  '||type;
  else
  set v_sql = 'alter table '|| schema||'.'||tablename||'  alter column '||columnname||' set data type  '||type|| '('||dataLength||')';
  end if;
  prepare s1 from v_sql;
  execute s1;
 set v_sql1 ='reorg table '||schema||'.'||tablename;
 call  sysproc.admin_cmd(v_sql1);
 if (errormessage=1) then
  SET state='Error : Unexpected exit, need to check';
 elseif (errormessage=0) then
  set state = 'SUCCESS';
  end if;
end

若有问题，请批评指正。