DB2 存储过程，触发器，函数

SP：

CREATE PROCEDURE PROCESS_ADDNOTICE(IN RECORDOID BIGINT,
                                           IN STARTERID BIGINT,
                                           OUT RESULTSTATUS INTEGER,
                                           OUT RESULTMSG VARCHAR(4000))
begin

--第三步 游标定义 
--声明变量

--第四步 业务变量定义
--声明表字段变量 
declare tempOid bigint default -1;
declare tempOperateTime timestamp;
declare tempOperator bigint;
declare tempYearly varchar(32);
declare tempNoticeName varchar(256); 
declare tempReceiveUnitId bigint; 
declare tempReceiveUnitName varchar(1024);
declare tempFileId bigint;
declare tempJobId varchar(256);
declare tempJobName varchar(1024) default '';
declare tempContext varchar(1024);
declare tempStartDate timestamp;
declare tempEndDate timestamp;
declare tempindex bigint;
declare tempvalue varchar(1024);
declare tempJobIdT varchar(256);
--第二步 异常变量、异常处理定义
--输出的错误信息和错误标志状态
declare SQLCODE integer default 0; 
declare tempSQLCODE integer default 0;
--出错处理
declare continue handler for sqlexception,not found,sqlwarning
begin
set tempSQLCODE = SQLCODE;
end;

--第五步 业务数据查询
--查询申请源数据
select  
C_OID,C_OPERATETIME,C_OPERATOR,C_YEARLY,C_NOTICENAME,C_RECEIVEUNITID,C_FILEID,C_JOBCODE,C_CONTEXT,C_STARTDATE,C_ENDDATE
into tempOid,tempOperateTime,tempOperator,tempYearly,tempNoticeName,tempReceiveUnitId,tempFileId,tempJobId,tempContext,tempStartDate,tempEndDate from TB_INF_ADDNOTICE where C_OID=RecordOID;

select C_NAME into tempReceiveUnitName from TB_ORG_ORGUNIT where C_OID_ORGUNIT = tempReceiveUnitId;
--select C_NAME into tempJobName from TB_ORG_ASSIGNEDJOB where C_OID_ASSIGNEDJOB = tempJobId;
--设置选拔岗位名称
set tempJobIdT = tempJobId; 
while tempJobIdT is not null do 
set tempindex = locate('|',tempJobIdT); 
set tempvalue = (select a.C_NAME from tb_org_assignedjob a where char(a.C_OID_ASSIGNEDJOB) = SUBSTR(tempJobIdT,1,8)); 
set tempJobName = Concat(tempJobName,Concat(',',tempvalue)); 
if tempindex > 0 then 
set tempJobIdT = SUBSTR(tempJobIdT,integer(tempindex+1)); end if; 
if tempindex <= 0 then 
set tempJobIdT = null; 
end if; 
end while; 
----增加公告信息
insert into TB_PRE_POST (C_OID,C_YEAR,C_OPERATOR,C_OPERATETIME,C_CAPTION,C_JOBIDS,C_JOBNAMES,C_UNITIDS,C_UNITNAMES,C_BLOBID,C_STARTDATE,C_ENDDATE,C_TYPE,C_STATUS) values  
(tempOid,tempYearly,tempOperator,tempOperateTime,tempNoticeName,tempJobId,SUBSTR(tempJobName,2),char(tempReceiveUnitId),tempReceiveUnitName,tempFileId,tempStartDate,tempEndDate,'1','2');
if tempSQLCODE<>0
then
set ResultStatus=tempSQLCODE;
set ResultMSG='插入公告信息时出错,错误代码:'||char(tempSQLCODE);
rollback;
return -1;
end if;
--如果执行到此
set ResultStatus=1;
set ResultMSG='执行成功';
return 1;
end

Trigger：

CREATE TRIGGER TB_TRI_S_INUNIT 
BEFORE INSERT 
ON TB_INF_INUNIT 
REFERENCING NEW AS NEWROW FOR EACH ROW 
begin ATOMIC 
set NEWROW.c_unitid = ( select a.c_unitid from tb_inf_employee a where a.c_employeeid = NEWROW.c_empoid); 
set NEWROW.c_jobid = ( select a.c_jobid from tb_inf_employee a where a.c_employeeid = NEWROW.c_empoid); 
end

function：

create function get_positionName(c_empid  bigint) returns varchar(1024) 
begin atomic
    declare positionName varchar(1024) default '';
    declare tempName varchar(1024) default ' ';
    declare col_counter smallint default 1;
    declare num_of_cols smallint;
    set num_of_cols = (
      select count(*)
        from TB_HNYC.TB_PER_EMPLOYEEPOSITION);
    while col_counter <= num_of_cols do
      set tempName = (
        select c_positionName
          from (
            select ROW_NUMBER() over() as a, TB_HNYC.TB_PER_EMPLOYEEPOSITION.*
              from TB_HNYC.TB_PER_EMPLOYEEPOSITION) as temp
          where a=col_counter
            and c_przt!='8'
            and c_przt!='2'
            and c_IFCURRENTPOST = '1'
            and c_empoid = c_empid
          order by c_jobtype asc,c_operatetime desc);
      if tempName is not null then
        set positionName = positionName||','||tempName;
      end if;
      set col_counter = col_counter + 1;
    end while;
    if length(positionName)>0 then
      return right(positionName,length(positionName)-1);
    end if;
  end