-------------------------------求一个存储过程---------------------

[掐死温柔]

小弟DB2没用过,不知道DB2下面的存储过程跟MSSQL有什么不同没?
哪位有现成的COPY一个完整的CASE出来吧,谢谢.

[掐死温柔]

有没有人知道啊?

[mgan]

DROP PROCEDURE pro_del@
CREATE PROCEDURE pro_del (
IN P_CYCLE_TYPE VARCHAR(20) ,--接口周期（即任务周期）
OUT P_RESULT_CODE INT , --返回值
OUT P_RESULT_STATE CHAR(5) )
LANGUAGE SQL
BEGIN
DECLARE sqlcode int;
DECLARE SQLSTATE CHAR(5);
DECLARE Ssql varchar(200);
set ssql='CREATE TABLE YUTEST3A3(CODE INT) in tmp22';
execute immediate ssql;
END@

一个简单的

[ab5669]

CREATE PROCEDURE update_salary
(IN employee_number CHAR(6), IN rating INT)
LANGUAGE SQL
BEGIN
DECLARE SQLSTATE CHAR(5);
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE EXIT HANDLER FOR not_found
SIGNAL SQLSTATE '02444';

CASE rating
WHEN 1 THEN
UPDATE employee
SET salary = salary * 1.10, bonus = 1000
WHERE empno = employee_number;
WHEN 2 THEN
UPDATE employee
SET salary = salary * 1.05, bonus = 500
WHERE empno = employee_number;
ELSE
UPDATE employee
SET salary = salary * 1.03, bonus = 0
WHERE empno = employee_number;
END CASE;
END @


摘自DB2的安装目录 IBM\SQLLIB\samples\sqlproc

[wuya8115]

给你个框框，我也才在学

ECHO 删除存储过程：tc.bp_fl_indexmanagetypequery
@
DROP PROCEDURE tc.bp_fl_indexmanagetypequery
@

ECHO 创建存储过程：tc.bp_fl_indexmanagetypequery
@
CREATE PROCEDURE tc.bp_fl_indexmanagetypequery
(
IN v_Init CHAR(1) -- 查询方式（0：取数据，1：初始化格式）
,IN v_CorpID INT --公司ＩＤ
,IN v_UserID INT -- 操作员ID
,IN v_FlagID char(8) -- 时间段id

,OUT v_RetVal INTEGER --出错代码
,OUT v_MessageError VARCHAR(400) --错误描述

)
LANGUAGE SQL
DYNAMIC RESULT SETS 2
------------------------------------------------------------------------
-- Type : SQL 存储过程
-- Project Name :
-- SP Name : bp_fl_indexmanagetypequery
-- IN : v_Init(显示类型：1:只显示标题 0: 输出数据 ... )
-- OUT: v_RetVal(出错代码 0表示成功 小于0表识系统错误，大于0代表用户定义错误)
-- v_MessageError(出错描述)
-- Developer: lihuix Create Date: 20060912 09:10
-- Last Modified by: Last Update Date:

------------------------------------------------------------------------
-- Description:
-- Example:
------------------------------------------------------------------------
BEGIN
-----------------------------声明变量开始------------------------
DECLARE SQLCODE INTEGER; -- 系统SQL出错编码
DECLARE SQLSTATE CHAR(5); -- 系统SQL出错状态码
DECLARE v_strState CHAR(5) DEFAULT ''; -- 自定义SQL出错状态码
DECLARE v_strCaptionSQL VARCHAR(2000); -- 标题输出原SQL串
DECLARE v_strCapPreSQL VARCHAR(2000); -- 标题输出预编译SQL串

DECLARE v_strSQL VARCHAR(2000); -- 用于处理原SQL串
DECLARE v_strPreSQL VARCHAR(2000); -- 用于处理预编译SQL串
DECLARE v_TempSql VARCHAR(500) DEFAULT ''; --临时sql
-----------------------------声明变量结束------------------------

---------------------------- 声明自定义游标开始--------------------
DECLARE cur_Query CURSOR WITH RETURN FOR v_strPreSQL;

---------------------------- 声明自定义游标结束--------------------

--声明标题输出游标
DECLARE cur_Caption CURSOR WITH RETURN FOR v_strCapPreSQL;


---------------------------- 异常处理------------------------------

DECLARE EXIT handler FOR sqlexception
BEGIN
SET v_RetVal = SQLCODE;
SET v_strState = SQLSTATE;
SET v_MessageError = '处理失败,其中SQLSTATE:'||v_strState;
END;

---初始化返回参数
SET v_RetVal = 0;
SET v_MessageError = ''; --如果成功需要提示，则在此给定。

--设置隔离级别
-- SET CURRENT ISOLATION = UR;

--声明标题输出的临时表
DECLARE GLOBAL TEMPORARY TABLE temp_caption(
caption varchar(20),
width varchar(20) ,
property varchar(20),
format varchar(20),
fixcol char(1),
align char(10),
datatype integer
) NOT LOGGED WITH REPLACE;
-----------------------------插入标题属性信息------------------------


insert into SESSION.temp_caption values('bigtypeid ','0','id ','','0' , 'left' , 1);
insert into SESSION.temp_caption values('序号 ','40','orderby ','','0' , 'left' , 1);
insert into SESSION.temp_caption values('维度 ','100','name ','','0' , 'left' , 0);


SET v_strCaptionSQL='SELECT caption,width,property,format,fixcol,align,datatype FROM SESSION.temp_caption';
-----------------------------程序主体-------------------------------


IF v_Init = '1' then
SET v_strPreSQL='SELECT '''' AS id,'''' as orderby,'''' AS name WHERE 1=2' ;

ELSE
SET v_strPreSQL='SELECT distinct a.bigtype as id,a.orderby as orderby,b.bigtypename as name FROM tc.fl_basetype a,tc.f_basetypeinfo b
where a.flagid=@flagid and a.bigtype=b.bigtype order by a.orderby' ;
END IF;



-----------------------------对于Grid初始化的处理--------------------


-----------------------------有效性判断-----------------------------

-----------------------------程序主体-------------------------------
PREPARE v_strCapPreSQL FROM v_strCaptionSQL;
PREPARE v_strPreSQL FROM v_strSQL;
GOTO ExitProc;

-----------------------------程序结束-------------------------------
ExitProc:
-- 返回标题输出,要求列名全部为小写
OPEN cur_Caption;

-- 返回数据输出,要求列名全部为小写
OPEN cur_Query;
END
@

[掐死温柔]

谢谢.

[cctaiyang]

接分

[掐死温柔]

结贴.